mysql8-索引的使用规则验证
MYSQL
1、mysql如何查看执行计划
EXPLAIN SELECT * FROM USER1 WHERE id1 = 1 AND id2 = 1 AND id3 = 1 AND id4 = 1;
oracle如何查看执行计划
可以使用语句:
explain plan FOR SELECT \* FROM SIBF_JHYSF_TRANS_SERIAL; -- 先执行第一条,仅仅是生成执行计划,不会自动显示
SELECT \* FROM TABLE(dbms_xplan.display); -- 再执行第二条,显示执行计划
2、mysql8-索引的使用规则验证
1、创建数据库
2、创建表user
CREATE TABLE USER(
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -- 这里的顺序还有点问题,无符号要放在主键全面,不然会报错。。,大概是约束问题吧。
NAME VARCHAR(20),
age VARCHAR(10),
phone VARCHAR(20),
address VARCHAR(100),
email VARCHAR(100),
INDEX stu_index(NAME, age, phone, address)
)ENGINE = INNODB CHARSET = utf8;
3、查看sql执行计划
1、首先插入几条数据
INSERT INTO USER(NAME, age, phone, address, email) VALUES('aaa', 'aaa', 'bbb', 'ccc', 'ddd');
INSERT INTO USER(NAME, age, phone, address, email) VALUES('aaa', 'aaa', 'ccc', 'ccc', 'ddd');
INSERT INTO USER(NAME, age, phone, address, email) VALUES('aaa', 'aaa', 'bbb', 'cccc', 'dddd');
INSERT INTO USER(NAME, age, phone, address, email) VALUES('aaaa', 'aaaa', 'bbb', 'cccc', 'dddd');
2、使用唯一主键查找:
EXPLAIN SELECT * FROM USER WHERE id = 1;
3、使用普通索引查找:
3.1、首先使用符合最左前缀原则(也就是,mysql的索引,遇到了索引排序是乱序的条件,后面无法使用索引查找):
EXPLAIN SELECT * FROM USER WHERE age = 'aaa' AND phone = 'bbb' AND NAME = 'aaa';
此时索引虽然不按照创建时的顺序排列,但是SQL优化器会进行优化为按照最左前缀原则的索引序列。
3.2、然后使用不符合原则的SQL:
EXPLAIN SELECT * FROM USER WHERE age = 'aaa' AND phone = 'bbb';
就不会走这个索引了。
3.3、在普通索引字段上使用范围查询,看前面的索引字段会不会走索引?
是否走索引,看type,key就可以了,都不为空,说明走了索引。
EXPLAIN SELECT * FROM USER WHERE phone LIKE '%bbb%' AND age = 'aaa' AND NAME = 'aaa'; -- 走索引,后两个索引字段查询
EXPLAIN SELECT * FROM USER WHERE phone >= '%bbb%' AND age = 'aaa' AND NAME = 'aaa'; -- 走索引,索引范围
4、在普通索引字段上使用范围查询,看后面的索引字段会不会走索引?
EXPLAIN SELECT * FROM USER WHERE phone LIKE '%bbb%' AND age = 'aaa' AND NAME = 'aaa' AND address = 'ccc'; -- address不会走索引,也就是位于范围查找后的索引字段不会走索引
5、测试复合主键(a, b, c),在使用其中一部分字段(a, b)(b, c) (b)查询时,会不会走主键索引?
5.1、首先简单的新建一张表
CREATE TABLE USER1(
id1 INT,
id2 INT,
id3 INT,
id4 INT,
NAME VARCHAR(20),
PRIMARY KEY(id1, id2, id3, id4)
)ENGINE = INNODB CHARSET = utf8;
id1、id2、id3、id4为复合主键
插入一条数据,避免查询时,explain提示没有数据,而不显示执行计划。
INSERT INTO user1 VALUES(1, 1, 1, 1, 'aaa');
INSERT INTO user1 VALUES(1, 1, 2, 1, 'aaa');
5.2、使用复合主键全部字段查询
EXPLAIN SELECT * FROM USER1 WHERE id1 = 1 AND id2 = 1 AND id3 = 1 AND id4 = 1;
走索引,并使用了四个字段查询。
5.3、使用复合主键部分字段查询,满足最左前缀原则
EXPLAIN SELECT * FROM USER1 WHERE id1 = 1 AND id2 = 1;
走索引。
5.4、使用复合主键部分字段查询,不满足最左前缀原则
EXPLAIN SELECT * FROM USER1 WHERE id3 = 1 AND id4 = 1;
不走索引。
5.5、使用复合主键,当作普通索引使用,验证范围查询是否会使得后面的索引字段不能使用?
EXPLAIN SELECT * FROM USER1 WHERE id3 > 1 AND id2 = 1 AND id1 = 1
EXPLAIN SELECT * FROM USER1 WHERE id3 > 1 AND id2 = 1 AND id1 = 1 AND id4 = 1;
可见,在id3范围查找下,添加id4,或者不添加,结果一样。但是是因为走的是range吧。
使用like,可以看到使用的索引字段。
EXPLAIN SELECT * FROM USER1 WHERE id3 LIKE '%1%' AND id2 = 1 AND id1 = 1;
EXPLAIN SELECT * FROM USER1 WHERE id3 LIKE '%1%' AND id2 = 1 AND id1 = 1 AND id4 = 1;
可见,在id3范围查找下,不加id4与添加id4结果一样,均使用了一个索引,以及其中的两个索引字段。也就可以说明,mysql中复合主键在使用上与复合索引并无差别。只是主键代表的含义是:一张表的唯一标识,多了一个唯一的约束。
6、结论
1、mysql完全遵守最左前缀原则,即:使用的where条件导致索引字段排列无序,就不会走索引。当然不适用索引字段查找,更不会走索引了。
2、只要条件中的字段,遵循最左前缀原则,无论排序如何,SQL会被优化为可以走索引的形式。
3、遇到索引字段进行范围查询,同样,该字段前的索引正常使用,后面的索引字段不会再起作用。但是,这个索引仍然起作用了,只是某些索引字段没有起作用。这一点非常重要,是多个索引字段组成了一个索引,而不是每个字段都是一个索引
4、mysql中复合主键在使用上与复合索引并无差别。只是主键代表的含义是:一张表的唯一标识,多了一个唯一的约束
表中索引与代码定义的不匹配:
TL_REQMSG:索引没有建立*