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中复合主键在使用上与复合索引并无差别。只是主键代表的含义是：一张表的唯一标识，多了一个唯一的约束。

结论

1、mysql完全遵守最左前缀原则，即：使用的where条件导致索引字段排列无序，就不会走索引。当然不适用索引字段查找，更不会走索引了。

2、只要条件中的字段，遵循最左前缀原则，无论排序如何，SQL会被优化为可以走索引的形式。

3、遇到索引字段进行范围查询，同样，该字段前的索引正常使用，后面的索引字段不会再起作用。但是，这个索引仍然起作用了，只是某些索引字段没有起作用。这一点非常重要，是多个索引字段组成了一个索引，而不是每个字段都是一个索引

4、mysql中复合主键在使用上与复合索引并无差别。只是主键代表的含义是：一张表的唯一标识，多了一个唯一的约束