mysql创建索引，mysql索引优化，mysql索引创建删除
mysql创建索引，mysql索引优化，mysql索引创建删除
?
================================
?Copyright 蕃薯耀 2020-11-23
http://fanshuyao.iteye.com/
?
一、mysql创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]；
— 或
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON 表名(字段名) [USING 索引方法]；
?
二、mysql查看索引
show index from 表名;
?
三、mysql删除索引
DROP INDEX 索引名 ON 表名
— 或
ALTER TABLE 表名 DROP INDEX 索引名
?
四、索引优化
1、学生表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
stu_no VARCHAR(30) NOT NULL,
`name` VARCHAR(30),
age INT ,
mobile VARCHAR(11),
`status` VARCHAR(1) DEFAULT ‘1’ NOT NULL ,
remark VARCHAR(100)
);
SELECT COUNT(*) FROM student;
SELECT * FROM student;
?
索引
EXPLAIN SELECT * FROM student l WHERE l.`name`=’学生11’;
EXPLAIN SELECT * FROM student l WHERE l.`stu_no`=’1605782472487’;
EXPLAIN SELECT * FROM student l WHERE l.`stu_no`=’1605782472487’ AND l.`status`=’1’;
EXPLAIN SELECT * FROM student l WHERE l.`name`=’学生1’ AND l.`stu_no`=’1605782472487’;
EXPLAIN SELECT l.id,l.`stu_no`,l.`name` FROM student l WHERE l.`name`=’学生1’;
EXPLAIN SELECT l.id,l.`stu_no`,l.`name` FROM student l WHERE l.`name`=’学生1’ ORDER BY l.`name`;
— 使用id排序，出现Using filesort
EXPLAIN SELECT l.id,l.`stu_no`,l.`name` FROM student l WHERE l.`name`=’学生1’ ORDER BY l.`id`;
EXPLAIN SELECT * FROM student l WHERE l.`name`=’学生1’ AND l.`mobile`=’13400000001’ AND l.`age`=’21’;
— 打乱顺序，正常使用索引
EXPLAIN SELECT * FROM student l WHERE l.`age`=’21’ AND l.`name`=’学生1’ AND l.`mobile`=’13400000001’;
— 没用到索引
— type：ALL
EXPLAIN SELECT l.id,l.`stu_no`,l.`name` FROM student l WHERE l.`name` LIKE ‘学生1%’;
— 用到索引
— type：range，注意这里是range，不是index，即非全索引查找，是范围查找
— Using where; Using index
— 因为这里查询的字段只有name，name是建了索引的，右边%，索引还是生效，然后直接从索引取数据，而不是检索表，索引比表快
EXPLAIN SELECT l.`name` FROM student l WHERE l.`name` LIKE ‘学生1%’;
— 没用到索引
— type：ALL
EXPLAIN SELECT l.id,l.`stu_no`,l.`name` FROM student l WHERE l.`name` LIKE ‘%学生1’;
— 用到索引
— type：index
— Using where; Using index
— 因为这里查询的字段只有name，name是建了索引的，左边%，索引还是生效，然后直接从索引取数据，而不是检索表，索引比表快
EXPLAIN SELECT l.`name` FROM student l WHERE l.`name` LIKE ‘%学生1’;
— 没用到索引
— type：ALL
EXPLAIN SELECT l.id,l.`stu_no`,l.`name` FROM student l WHERE l.`name` LIKE ‘%学生%’;
— 用到索引
— type：index
— Using where; Using index
— 因为这里查询的字段只有name，name是建了索引的，左右两边%，索引还是生效，然后直接从索引取数据，而不是检索表，索引比表快
EXPLAIN SELECT l.`name`,l.`mobile` FROM student l WHERE l.`name` LIKE ‘%学生%’;
— 创建索引
CREATE UNIQUE INDEX ind_stu_stuNO ON student(stu_no);
CREATE INDEX ind_stu_name_mobile ON student(`name`,mobile);
— 删除索引
DROP INDEX ind_stu_stuNO ON student;
DROP INDEX ind_stu_name_mobile ON student;
— 查询表的索引
SHOW INDEX FROM student;
?
2、课程表
CREATE TABLE course(
id INT PRIMARY KEY AUTO_INCREMENT,
course_no VARCHAR(30) UNIQUE NOT NULL ,
`name` VARCHAR(30),
`status` VARCHAR(1) DEFAULT ‘1’ NOT NULL ,
create_time TIMESTAMP DEFAULT NOW() NOT NULL,— 只有TIMESTAMP可以设置时间的默认值
remark VARCHAR(100)
);
INSERT INTO course(course_no,`name`) VALUES(‘1001’, ‘语文’);
INSERT INTO course(course_no,`name`) VALUES(‘1002’, ‘数学’);
INSERT INTO course(course_no,`name`) VALUES(‘1003’, ‘英语’);
INSERT INTO course(course_no,`name`) VALUES(‘1004’, ‘物理’);
INSERT INTO course(course_no,`name`) VALUES(‘1005’, ‘化学’);
INSERT INTO course(course_no,`name`) VALUES(‘1006’, ‘生物’);
INSERT INTO course(course_no,`name`) VALUES(‘1007’, ‘历史’);
INSERT INTO course(course_no,`name`) VALUES(‘1008’, ‘地理’);
INSERT INTO course(course_no,`name`) VALUES(‘1009’, ‘自然科学’);
INSERT INTO course(course_no,`name`) VALUES(‘1010’, ‘体育’);
INSERT INTO course(course_no,`name`) VALUES(‘1011’, ‘哲学’);
INSERT INTO course(course_no,`name`) VALUES(‘1012’, ‘考古’);
INSERT INTO course(course_no,`name`) VALUES(‘1013’, ‘高等数学’);
INSERT INTO course(course_no,`name`) VALUES(‘1014’, ‘大学英语’);
INSERT INTO course(course_no,`name`) VALUES(‘1015’, ‘艺术’);
SELECT * FROM course;
— 查询表的索引
SHOW INDEX FROM course;
?
3、学生课程关系表
CREATE TABLE stu_course(
id INT PRIMARY KEY AUTO_INCREMENT,
stu_id INT NOT NULL ,
course_id INT NOT NULL ,
create_time TIMESTAMP DEFAULT NOW() NOT NULL— 只有TIMESTAMP可以设置时间的默认值
);
SELECT * FROM stu_course;
SELECT COUNT(1) FROM stu_course;
?
加索引前后对比
— s ref 1
— sc ALL 150646
— c eq_ref 1
EXPLAIN
SELECT s.*,c.`name` FROM student s
LEFT JOIN stu_course sc ON sc.`stu_id`=s.`id`
LEFT JOIN course c ON c.`id`=sc.`course_id`
WHERE s.`name`=’学生1’
;
— 创建索引
ALTER TABLE stu_course ADD INDEX stu_course_stuId(stu_id);
— 创建索引后
— s ref 1
— sc ref 753
— c eq_ref 1
EXPLAIN
SELECT s.*,c.`name` FROM student s
LEFT JOIN stu_course sc ON sc.`stu_id`=s.`id`
LEFT JOIN course c ON c.`id`=sc.`course_id`
WHERE s.`name`=’学生1’
;
?
小表驱动大表
— 加索引
ALTER TABLE stu_course ADD INDEX stu_course_courseId(course_id);
— 小表驱动大表
— c ALL 15
— sc ref 753
EXPLAIN
SELECT c.*,sc.`stu_id` FROM course c
LEFT JOIN stu_course sc ON sc.`course_id`=c.`id`
;
— 大表驱动小表
— sc ALL 150646
— c eq_ref 1
EXPLAIN
SELECT c.*,sc.`stu_id` FROM course c
RIGHT JOIN stu_course sc ON sc.`course_id`=c.`id`
;
— 总结：尽量使用小表驱动大表
?
?
in和EXISTS
— in和EXISTS
— EXISTS查询语法
SELECT select_list FROM a_table WHERE [NOT] EXISTS(subquery);
— in查询
— 1 sc ALL 150646
— 2 c unique_subquery 1
EXPLAIN
SELECT * FROM stu_course sc WHERE sc.`course_id` IN (SELECT id FROM course c);
— sc ALL 150646
— c unique_subquery 1
EXPLAIN
SELECT * FROM stu_course sc WHERE sc.`course_id` IN (SELECT id FROM course c WHERE c.`name`=’数学’);
— EXISTS查询
— 1 sc ALL 150646 Using where
— 2 c eq_ref 1 Using index
EXPLAIN
SELECT * FROM stu_course sc WHERE EXISTS (SELECT 1 FROM course c WHERE c.`id`=sc.`course_id`);
— sc ALL 150646 Using where
— c eq_ref 1 Using where
EXPLAIN
SELECT * FROM stu_course sc WHERE EXISTS (SELECT 1 FROM course c WHERE c.`name`=’数学’ AND c.`id`=sc.`course_id`);
— in和EXISTS总结：
— EXISTS：可以理解为：将外查询(左边)表的每一行，代入内查询(右边)作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。
— 1、通常情况下采用exists要比in效率高，因为IN不走索引。
— 2、左边为小表时(in是大表)，用in性能好。左边为大表时(EXISTS是小表)，用EXISTS性能好。
?
4、字典表
— 字典表
— drop table dict;
CREATE TABLE dict(
id INT PRIMARY KEY AUTO_INCREMENT,
`type` VARCHAR(30) NOT NULL,
`name` VARCHAR(30),
`value` VARCHAR(50),
create_time TIMESTAMP DEFAULT NOW() NOT NULL— 只有TIMESTAMP可以设置时间的默认值
);
INSERT INTO dict(`type`,`name`,`value`) VALUES(‘status’,’有效’, ‘1’);
INSERT INTO dict(`type`,`name`,`value`) VALUES(‘status’,’无效’, ‘0’);
SELECT * FROM dict;
?
?
?
================================
?Copyright 蕃薯耀 2020-11-23
http://fanshuyao.iteye.com/
你的名字
Dear 丶
我会带着你远行
向右看齐
港控/mmm°
![[09-01]JavaScript 基础语法](https://image.dandelioncloud.cn/images/20220109/357c68a0798d49ad847b9076d69e1668.png "[09-01]JavaScript 基础语法")
还没有评论，来说两句吧...