最左前缀法则
CREATE INDEX idx_age_classid_name ON student(age,classId,name);
show index from student;
-- 1【索引部分生效】
-- 索引部分生效age key=idx_age_classid_name,key_len=5(int4字节+额外1字节标记空null)
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd';
-- 2【跳过联合索引列，索引失效】
-- 索引不生效 跳过联合索引列 key=null,key_len=null
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=1 AND student.name = 'abcd';
-- 3【联合索引左边连续部分】
-- 索引生效age+classId key=idx_age_classid_name,key_len=10 (2个int4字节+2个额外1字节标记空null)
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId = 1;
-- 和最左索引列挨着的索引列，两个索引列先后顺序可随意 key_len=10
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId = 1 AND student.age=30;
-- 4【覆盖符合索引所有列】
-- 索引生效 key_len=73 (age+classId=2个int4字节+2个额外1字节标记空null=10;name=63(CHARSET=utf8(3字节) VARCHAR(20)：20*3+1个额外1字节标记空null+动态列类型+还需要再加 2 bytes))
-- 符合索引所有列都用到，和索引列顺序无关
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND student.age=30 AND student.name = 'abcd';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId=4 AND student.name = 'abcd';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name = 'abcd' AND student.classId=4 AND student.age=30;
索引列 函数/运算操作
CREATE INDEX idx_name ON student(NAME);
-- ALTER TABLE student DROP index idx_name;
CREATE INDEX idx_sno ON student(stuno);
-- ALTER TABLE student DROP index idx_sno;
show index from student;
-- 索引失效 索引列函数参与运算
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE UPPER(student.name) = 'KFVOFK';
-- 索引失效 索引列参与
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
-- 索引生效 key_len=4(stuno非空列)
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900001;
字符串不加单引号，索引失效
CREATE INDEX idx_name ON student(NAME);
ALTER TABLE student DROP index idx_name;
-- key=null,key_len=null
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
-- key=idx_name,key_len=63
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='yOzcRc';
范围查询右边的列，不能使用查询
CREATE INDEX idx_age_classid_name ON student(age,classId,name);
-- ALTER TABLE student DROP index idx_age_classid_name;
-- 联合索引第一个参数age使用范围查询，全部索引失效。ken_len为0，全表扫描。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age>30 AND student.classId=4 AND student.name = 'abcd';
-- 联合索引第二个参数classId使用范围查询，name索引失效。key_len为10，即只用age和classId。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId>3 AND student.name = 'abcd';
-- 联合索列全部覆盖 索引生效 key_len为73
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId=4 AND student.name = 'abcd';
“不等于”导致索引失效
- 联合索引覆盖索引，“不等于生效”
- 单独索引，“不等于失效”
CREATE INDEX idx_age_name ON student(age, NAME);
-- ALTER TABLE student DROP index idx_age_name;
-- 没覆盖索引的情况下，使用“不等于”导致索引失效。
-- 因为如果使用索引，则需要依次遍历非聚簇索引B+树里所有叶节点，时间复杂度O(n)，
-- 找到记录后还要回表，加在一起效率不如全表扫描，所以查询优化器就选择全表扫描了。
-- key=null;key_len=null
EXPLAIN SELECT * FROM student WHERE age <> 20;
-- 覆盖索引，查的两个字段被联合索引给覆盖了，性能更高。
-- 虽然还是需要依次遍历非聚簇索引B+树里所有叶节点，时间复杂度O(n)，
-- 但是不需要回表了，整体效率比不用索引更高，查询优化器就又使用索引了。
-- 索引生效 key=idx_age_name;key_len=idx_age_name
EXPLAIN SELECT age,name FROM student WHERE age <> 20;
CREATE INDEX idx_name ON student(NAME);
-- ALTER TABLE student DROP index idx_name;
-- 因为“不等于”不能精准匹配，全表扫描二级索引树再回表效率不如直接全表扫描聚簇索引树。
-- 但使用覆盖索引时，联合索引数据量小，加载到内存所需空间比聚簇索引树小，且不需要回表，
-- 索引效率优于全表扫描聚簇索引树
-- 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE (student.name <> 'abc');
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc';
is not null、not like导致索引失效
CREATE INDEX idx_age ON student(age);
-- ALTER TABLE student DROP index idx_age;
-- 因为is not null、not like不能精准匹配，
-- 全表扫描二级索引树再回表效率不如直接全表扫描聚簇索引树。
-- 但使用覆盖索引时，联合索引数据量小，加载到内存所需空间比聚簇索引树小，且不需要回表，
-- 索引效率优于全表扫描聚簇索引树。
-- 覆盖索引：一个索引包含了满足查询结果的数据就叫做覆盖索引，不需要回表等操作。
-- key=idx_age;key_len=5
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
-- key=null;key_len=null
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;
CREATE INDEX idx_age ON student(age);
ALTER TABLE student DROP index idx_age;
-- 因为is not null、not like不能精准匹配，
-- 全表扫描二级索引树再回表效率不如直接全表扫描聚簇索引树。
-- 但使用覆盖索引时，联合索引数据量小，加载到内存所需空间比聚簇索引树小，且不需要回表，
-- 索引效率优于全表扫描聚簇索引树。
-- 覆盖索引：一个索引包含了满足查询结果的数据就叫做覆盖索引，不需要回表等操作。
-- key=null;key_len=null
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name not like 'abc%';
-- key=idx_name;key_len=63
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name like 'abc%';
左模糊查询导致索引失效
CREATE INDEX idx_name ON student(NAME);
-- ALTER TABLE student DROP index idx_name;
-- 因为字符串开头都不能精准匹配，全表扫描二级索引树再回表效率不如直接全表扫描聚簇索引树。
-- 但使用覆盖索引时，联合索引数据量小，加载到内存所需空间比聚簇索引树小，且不需要回表，
-- 索引效率优于全表扫描聚簇索引树。
-- key=null;key_len=null
EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';
-- key=null;key_len=null
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE '%abc%';
-- key=idx_name;key_len=63
EXPLAIN SELECT id,age,NAME FROM student WHERE NAME LIKE 'abc%';
CREATE INDEX idx_age_name ON student(age, NAME);
-- ALTER TABLE student DROP index idx_age_name;
-- 因为走非聚簇索引B+树遍历叶节点，不回表，效率会比全表扫描时高，查询优化器选择效率高的方案。
-- key=idx_age_name;key_len=68
EXPLAIN SELECT id,NAME FROM student WHERE NAME LIKE '%abc';
“OR”前后存在非索引列，导致索引失效
CREATE INDEX idx_age ON student(age);
-- ALTER TABLE student DROP index idx_age;
-- OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，
-- 只要有条件列有一个没有进行索引，就会进行全表扫描，因此所以的条件列也会失效。
-- key=null;key_len=null
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
CREATE INDEX idx_age_name ON student(age, NAME);
-- ALTER TABLE student DROP index idx_age_name;
-- OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，
-- 只要有条件列有一个没有进行索引，就会进行全表扫描，因此所以的条件列也会失效。
-- 因为age字段和name字段上都有索引，所以查询中使用了索引。你能看到这里使用到了index_merge，
-- 简单来说index_merge就是对age和name分别进行了扫描，然后将这两个结果集进行了合并。
-- 这样做的好处就是避免了全表扫描。
-- 使用了索引
EXPLAIN SELECT * FROM student WHERE age = 10 UNION SELECT * FROM student WHERE name = '10'
-- 5.7.27-log sql版本问题导致or两边索引失败 key=null;key_len=null
select version();
索引
