mysql索引

Mysql 2023-04-09 09:26 378 0

#首先创建三张表：tb_emp(职工表)、tb_dept(部门表)和tb_desc(描述表)
DROP TABLE IF EXISTS tb_emp;
CREATE TABLE tb_emp (
id INT(11) NOT NULL AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
deptid INT(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

INSERT INTO tb_emp(username,deptid) VALUES (‘Tom’, ‘1’);
INSERT INTO tb_emp(username,deptid) VALUES (‘Jack’, ‘1’);
INSERT INTO tb_emp(username,deptid) VALUES (‘Mary’, ‘2’);
INSERT INTO tb_emp(username,deptid) VALUES (‘Rose’, ‘3’);

DROP TABLE IF EXISTS tb_dept;
CREATE TABLE tb_dept (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

INSERT INTO tb_dept(NAME) VALUES (‘综合部’);
INSERT INTO tb_dept(NAME) VALUES (‘研发’);
INSERT INTO tb_dept(NAME) VALUES (‘测试’);
INSERT INTO tb_dept(NAME) VALUES (‘总裁’);

DROP TABLE IF EXISTS tb_desc;
CREATE TABLE tb_desc (
id INT(11) NOT NULL AUTO_INCREMENT,
empid INT(11) DEFAULT NULL,
deptid INT(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

INSERT INTO tb_desc(empid,deptid) VALUES (1, 1);
INSERT INTO tb_desc(empid,deptid) VALUES (2, 1);
INSERT INTO tb_desc(empid,deptid) VALUES (3, 2);
INSERT INTO tb_desc(empid,deptid) VALUES (4, 3);

#注：这里强行将员工表与部门表不直接关联，通过第三张表（描述表）进行关联，主要为了进行join的分析。

1. left join

EXPLAIN SELECT e.id,e.username,d.empid FROM tb_emp e LEFT JOIN tb_desc d ON e.id=d.empid;
EXPLAIN SELECT e.id,e.username,d.empid FROM tb_emp e RIGHT JOIN tb_desc d ON e.id=d.empid;
CREATE INDEX idx_tb_desc_empid ON tb_desc(empid);

left join（左连接）：右表创建索引 right join（右连接）：左表创建索引。

1.最佳左前缀法则

DROP TABLE IF EXISTS tb_emp;
CREATE TABLE tb_emp (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age INT(11) NOT NULL,
gender VARCHAR(10) NOT NULL,
email VARCHAR(20),
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

INSERT INTO tb_emp (NAME,age,gender,email) VALUES (‘Tom’, ‘22’,‘male’,‘1@qq.com’);
INSERT INTO tb_emp (NAME,age,gender,email) VALUES (‘Mary’, ‘21’,‘female’,‘2@qq.com’);
INSERT INTO tb_emp (NAME,age,gender,email) VALUES (‘Jack’, ‘27’,‘male’,‘3@qq.com’);
INSERT INTO tb_emp (NAME,age,gender,email) VALUES (‘Rose’, ‘23’,‘female’,‘4@qq.com’);

CREATE INDEX idx_tb_emp_name_age_gender ON tb_emp(NAME,age,gender);

EXPLAIN SELECT * FROM tb_emp WHERE NAME =‘tom’;

EXPLAIN SELECT * FROM tb_emp WHERE age=22 AND gender=‘male’;

EXPLAIN SELECT * FROM tb_emp WHERE NAME =‘tom’ AND gender=‘male’;
EXPLAIN SELECT * FROM tb_emp WHERE NAME =‘tom’ AND age=22;

EXPLAIN SELECT * FROM tb_emp WHERE NAME =‘tom’ AND age=22 AND gender=‘male’;

EXPLAIN SELECT * FROM tb_emp WHERE NAME =‘tom’ AND gender=‘male’ AND age=22;

2.不要在索引列上做任何操作

#在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效从而转向全表扫描。

EXPLAIN SELECT * FROM tb_emp WHERE LEFT(NAME,3)=‘tom’;

INSERT INTO tb_emp (NAME,age,gender,email) VALUES (‘111’, ‘23’,‘female’,‘4@qq.com’);

EXPLAIN SELECT * FROM tb_emp WHERE NAME=111;

#3.范围右边全失效

存储引擎不能使用索引中范围右边的列，也就是说范围右边的索引列会失效。

EXPLAIN SELECT * FROM tb_emp WHERE NAME =‘tom’ AND age=22;

EXPLAIN SELECT * FROM tb_emp WHERE NAME =‘tom’ AND age=22 AND gender=‘male’;

EXPLAIN SELECT * FROM tb_emp WHERE NAME =‘tom’ AND age>22 AND gender=‘male’;

#4. 使用覆盖索引
EXPLAIN SELECT * FROM tb_emp WHERE NAME =‘tom’ AND age=22 AND gender=‘male’;
EXPLAIN SELECT NAME,age,gender FROM tb_emp WHERE NAME =‘tom’ AND age=22 AND gender=‘male’;

5. 使用不等于（！=或<>）会使索引失效

EXPLAIN SELECT * FROM tb_emp WHERE NAME !=‘tom’;
EXPLAIN SELECT * FROM tb_emp WHERE NAME <> ‘tom’;

#6.is null 或 is not null也无法使用索引
EXPLAIN SELECT * FROM tb_emp WHERE NAME IS NULL;
EXPLAIN SELECT * FROM tb_emp WHERE NAME IS NOT NULL;

#7.like通配符以%开头会使索引失效
EXPLAIN SELECT NAME FROM tb_emp WHERE NAME LIKE ‘%jack%’;
EXPLAIN SELECT * FROM tb_emp WHERE NAME LIKE ‘%jack%’;
EXPLAIN SELECT NAME FROM tb_emp WHERE NAME LIKE ‘%jack%’; #所以解决%出现在左边索引失效的方法：使用覆盖索引。
EXPLAIN SELECT * FROM tb_emp WHERE NAME LIKE ‘jack%’;

#8. 少用or，用or连接会使索引失效 (覆盖索引解决)
EXPLAIN SELECT * FROM tb_emp WHERE NAME =‘tom’ OR NAME =‘jack’;

DROP TABLE IF EXISTS test;
CREATE TABLE test(
id INT PRIMARY KEY AUTO_INCREMENT,
c1 VARCHAR(10),
c2 VARCHAR(10),
c3 VARCHAR(10),
c4 VARCHAR(10),
c5 VARCHAR(10)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO test(c1,c2,c3,c4,c5) VALUES(‘a1’,‘a2’,‘a3’,‘a4’,‘a5’);
INSERT INTO test(c1,c2,c3,c4,c5) VALUES(‘b1’,‘b2’,‘b3’,‘b4’,‘b5’);
INSERT INTO test(c1,c2,c3,c4,c5) VALUES(‘c1’,‘c2’,‘c3’,‘c4’,‘c5’);
INSERT INTO test(c1,c2,c3,c4,c5) VALUES(‘d1’,‘d2’,‘d3’,‘d4’,‘d5’);
INSERT INTO test(c1,c2,c3,c4,c5) VALUES(‘e1’,‘e2’,‘e3’,‘e4’,‘e5’);

CREATE INDEX idx_test_c1234 ON test(c1,c2,c3,c4);

case 1

EXPLAIN SELECT * FROM test WHERE c1=‘a1’ AND c2=‘a2’ AND c3=‘a3’ AND c4=‘a4’;
EXPLAIN SELECT * FROM test WHERE c1=‘a1’ AND c3=‘a3’ AND c2=‘a2’ AND c4=‘a4’;
EXPLAIN SELECT * FROM test WHERE c1=‘a1’ AND c3=‘a3’ AND c4=‘a4’ AND c2=‘a2’;
EXPLAIN SELECT * FROM test WHERE c1=‘a1’ AND c4=‘a4’ AND c3=‘a3’ AND c2=‘a2’;
/*
①创建复合索引的顺序为c1,c2,c3,c4。
②上述四组explain执行的结果都一样：type=ref，key_len=132，ref=const,const,const,const。
结论：在执行常量等值查询时，改变索引列的顺序并不会更改explain的执行结果，因为mysql底层优化器会进行优化，但是推荐按照索引顺序列编写sql语句。
*/

Case 2：

EXPLAIN SELECT * FROM test WHERE c1=‘a1’ AND c2=‘a2’;
EXPLAIN SELECT * FROM test WHERE c1=‘a1’ AND c2=‘a2’ AND c3>‘a3’ AND c4=‘a4’;
/*
分析：当出现范围的时候，type=range，key_len=99，比不用范围key_len=66增加了，说明使用上了索引，但对比Case1中执行结果，说明c4上索引失效。
结论：范围右边索引列失效，但是范围当前位置（c3）的索引是有效的，从key_len=99可证明。
*/

#case2.1:
EXPLAIN SELECT * FROM test WHERE c1=‘a1’ AND c2=‘a2’ AND c4>‘a4’ AND c3=‘a3’;

/*
分析：与上面explain执行结果对比，key_len=132说明索引用到了4个，因为对此sql语句mysql底层优化器会进行优化：范围右边索引列失效（c4右边已经没有索引列了），
注意索引的顺序（c1,c2,c3,c4），所以c4右边不会出现失效的索引列，因此4个索引全部用上。
结论：范围右边索引列失效，是有顺序的：c1,c2,c3,c4，如果c3有范围，则c4失效；如果c4有范围，则没有失效的索引列，从而会使用全部索引。
*/

#case 2.2:
EXPLAIN SELECT * FROM test WHERE c1>‘a1’ AND c2=‘a2’ AND c4=‘a4’ AND c3=‘a3’;
/*
分析：
如果在c1处使用范围，则type=ALL，key=Null，索引失效，全表扫描，这里违背了最佳左前缀法则，带头大哥已死，因为c1主要用于范围，而不是查询。解决方式使用覆盖索引。
结论：在最佳左前缀法则中，如果最左前列（带头大哥）的索引失效，则后面的索引都失效。
*/

#索引排序
#case 3:
EXPLAIN SELECT * FROM test WHERE c1=‘a1’ AND c2=‘a2’ AND c4=‘a4’ ORDER BY c3;
/*
分析: 利用最佳左前缀法则：中间兄弟不能断，因此用到了c1和c2索引（查找），从key_len=66，ref=const,const，c3索引列用在排序过程中
*/

#case 3.1:
EXPLAIN SELECT * FROM test WHERE c1=‘a1’ AND c2=‘a2’ ORDER BY c3;
/*
分析: 从explain的执行结果来看：key_len=66，ref=const,const，从而查找只用到c1和c2索引，c3索引用于排序。
*/

#case 3.2:
EXPLAIN SELECT * FROM test WHERE c1=‘a1’ AND c2=‘a2’ ORDER BY c4;
/*
分析: key_len=66，ref=const,const，查询使用了c1和c2索引，由于用了c4进行排序，跳过了c3，出现了Using filesort。索引排序失败
*/

#case 3.3
EXPLAIN SELECT * FROM test WHERE c1=‘a1’ AND c5=‘a5’ ORDER BY c2,c3;
/*
分析: 查找只用到索引c1，c2和c3用于排序，无Using filesort。
*/

#case 3.4
EXPLAIN SELECT * FROM test WHERE c1=‘a1’ AND c5=‘a5’ ORDER BY c3,c2;
/*
分析: 出现了Using filesort，因为索引的创建顺序为c1,c2,c3,c4，但是排序的时候c2和c3颠倒位置了。索引排序失败
*/

#case 3.5
EXPLAIN SELECT * FROM test WHERE c1=‘a1’ AND c2=‘a2’ AND c5=‘a5’ ORDER BY c3,c2;
/*
分析: 在Extra中并未出现Using filesort，因为c2为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。
*/

#case 3.6
EXPLAIN SELECT * FROM test WHERE c1=‘a1’ AND c2>‘a2’ ORDER BY c3;
EXPLAIN SELECT * FROM test WHERE c1=‘a1’ AND c2>‘a2’ ORDER BY c2,c3;
/*
分析: 出现 using filesort, c2不是常量, c2后面的索引失效, 导致c3失效; 如果条件中的索引是常量, order by 中的可以沿用, 反之需要加上索引顺序
*/

#case 3.7
EXPLAIN SELECT * FROM test WHERE c1>‘a1’ ORDER BY c1;
/*
分析：

①在c1,c2,c3,c4上创建了索引，直接在c1上使用范围，导致了索引失效，全表扫描：type=ALL，ref=Null。因为此时c1主要用于排序，并不是查询。
②使用c1进行排序，出现了Using filesort。
③解决方法：使用覆盖索引。
*/

EXPLAIN SELECT c1 FROM test WHERE c1>‘a1’ ORDER BY c1;

#case 3.8
EXPLAIN SELECT c1 FROM test WHERE c1>‘a1’ ORDER BY c2;
/*
分析: c1 范围查询失效 , order by 不能沿用c1, 排序用的c2，与索引的创建顺序不一致，导致索引排序失败
*/

#case 3.9
EXPLAIN SELECT c1 FROM test WHERE c1>‘a1’ ORDER BY c2,c1;
/*
分析: c1 范围查询失效 , order by 不能沿用c1, 排序索引列与索引创建的顺序相反，导致索引排序失败
*/

#case 4.0
EXPLAIN SELECT c1 FROM test ORDER BY c1 ASC ,c2 DESC;
/*
分析: 虽然排序的字段列与索引顺序一样，且order by默认升序，这里c2 desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。
*/

索引排序总结:

一.MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

二.order by满足两种情况会使用Using index。
1.order by语句使用索引最左前列。
2.使用where子句与order by子句条件列组合满足索引最左前列。

三.尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最佳左前缀法则。

四.如果order by的条件不在索引列上，就会产生Using filesort。
1.filesort有两种排序算法：双路排序和单路排序。
双路排序：在MySQL4.1之前使用双路排序，就是两次磁盘扫描，得到最终数据。读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。即从磁盘读取排序字段，在buffer进行排序，再从磁盘取其他字段。
如果使用双路排序，取一批数据要对磁盘进行两次扫描，众所周知，I/O操作是很耗时的，因此在MySQL4.1以后，出现了改进的算法：单路排序。
单路排序：从磁盘中查询所需的列，按照order by列在buffer中对它们进行排序，然后扫描排序后的列表进行输出。它的效率更高一些，避免了第二次读取数据，并且把随机I/O变成了顺序I/O，但是会使用更多的空间，因为它把每一行都保存在内存中了。
2.单路排序出现的问题。
当读取数据超过sort_buffer的容量时，就会导致多次读取数据，并创建临时表，最后多路合并，产生多次I/O，反而增加其I/O运算。
解决方式：
a.增加sort_buffer_size参数的设置。
b.增大max_length_for_sort_data参数的设置。

五.提升order by速度的方式：
1.在使用order by时，不要用select *，只查询所需的字段。因为当查询字段过多时，会导致sort_buffer不够，从而使用多路排序或进行多次I/O操作。
2.尝试提高sort_buffer_size。
3.尝试提高max_length_for_sort_data。

#根据索引分组: group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀法则。当无法使用索引列的时候，也要对sort_buffer_size和max_length_for_sort_data参数进行调整。注意where高于having，能写在where中的限定条件就不要去having限定了。
EXPLAIN SELECT * FROM test WHERE c1=‘a1’ AND c2=‘a4’ GROUP BY c2,c3;
EXPLAIN SELECT * FROM test WHERE c1=‘a1’ AND c4=‘a4’ GROUP BY c3,c2;
/*
在group by时交换了c2和c3的位置，结果出现Using temporary和Using filesort，极度恶劣。原因：c3和c2与索引创建顺序相反。
*/