Mysql优化之索引优化

创建表

CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) DEFAULT NULL COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';

INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES(NULL,23,'test',NOW());

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`)

查看索引

索引优化案例
1.最佳左前缀法则
2.全值匹配
3.索引列上不计算
4.范围之后全失效
5.覆盖索引多使用
6.使用不等会失效
7.使用NULL值要小心
8.模糊查询加右边
9.字符串加单引号
10.尽量不用or查询

最佳左前缀法则
查询时从索引的最左列开始并且不跳过索引中的列

过滤条件要使用索引必须按照索引建立的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用

多列索引是先按照第一列进行排序，然后在第一列排好序的基础上再对第二列排序，如果没有第一列的话，直接访问第二列，那第二列肯定是无序的，直接访问后面的列就用不到索引

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

范围之后全失效
使用范围查询后，如果范围内的记录过多，会导致索引失效，因为从自定义索引映射到主键索引需要耗费太多的时间，反而不如全表扫描来得快

建议：将可能做范围查询的字段的索引顺序放在最后
覆盖索引多使用
使用覆盖索引（Using index）会提高检索效率：只访问索引列的查询（索引列和查询列一致，尽量不使用select *）


使用不等会失效
在使用不等于(!= 或者<>)时，可能会导致索引失效

使用NULL值要小心
如果允许字段为空，则

IS NULL 不会导致索引失效
IS NOT NULL 会导致索引失效

模糊查询加右边
like查询以通配符%开始会导致索引失效，转而变成全表扫描




问题： 解决like ‘%字符串%’ 索引不生效的方法：使用覆盖索引（查询的字段尽量和索引字段匹配）

面试题一

面试题二

索引全都用到，底层会有优化器。

范围查询之后全失效。

会用到4个索引：
①.mysql底层排序
②.范围查询之后全失效。但是当前范围不失效，所有a4也用到了索引。
4.

索引2个功能，查找和排序
c3的功能在于排序，而不是查找。
5.

和上面一样
6.

中间兄弟不能断

排序有效，不受c5影响

特殊情况：c2已经是常量了

分组之前必排序

【优化总结口诀】
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！

like ‘aa%’ 相当于有常量了，可以确定一部分范围了。