[MySQL]SQL优化之索引的使用规则
🌈键盘敲烂，年薪30万🌈
目录
一、索引失效
📕最左前缀法则
📕范围查询>
📕索引列运算，索引失效
📕前模糊匹配
📕or连接的条件
📕字符串类型不加 ''
📕数据分布
📕is not null
二、SQL提示：
①建议索引
②强制索引
③忽略索引
三、覆盖索引
四、前缀索引
一、索引失效
📕最左前缀法则
规则：
- 最左侧的列必须存在，如果不存在，索引失效，和位置无关！！！
- 从索引的最左侧开始，不能跳跃某一索引列，如果跳过某一列，那么后面的索引都会失效。
例如：
- 有联合索引 id_name_age_gender (name字段为最左列)
遵循法则：select * from user where name = 'zhang' and age = 12 and gender = 1;
遵循法则：select * from user where age = 10 and name = 'zhang';
不遵循法则：select * from user where age = 10 and gender = 1;
📕范围查询>
范围查询右侧的列索引失效
例如： > < 号会使索引失效
select * from user where age > 10;故：
尽量使用 >= 或者 <=
📕索引列运算，索引失效
对索引列进行运算，索引失效，因为运算完之后新的数据不具有索引结构
select * from user where substring(name, 0 ,2) = 'zh';
📕前模糊匹配
%在最左侧索引失效
索引失效：select * from user where name like '%hang';
索引不失效：select * from user where name like 'zhan%';
📕or连接的条件
使用or连接的字段索引都会失效
select * from user where id = 1 or name = 'zhang';📕字符串类型不加 ''
如果数据库字段类型为varchar 但是查询每加''，索引失效
select * from user where name = zhang;📕数据分布
如果MySQL经过判断之后发现全表扫描比按索引查询快，就会走全表扫描
📕is not null
如果一列数据基本没有null，is not null就会使索引失效
二、SQL提示：
例如：有两个索引 id_name_age id_name
执行：select * from user where name = 'zhang';
会走哪个索引呢❓
- 这时mysql经过优化后会选择一个索引，但是性能不一定最佳，因此可以指定索引。
①建议索引
select * from user use index(id_name) where name = 'zhang';注意：提出的建议，mysql不一定听。
②强制索引
select * from user force index(id_name) where name = 'zhang';③忽略索引
selct * from user ignore index(id_name_age) where name = 'zhang';三、覆盖索引
查询使用了索引，并且查询的字段 (select 后面的字段) 在该索引中可以全部找到。
例如：
- 回表查询
故：
尽量避免使用select * ，这很容易导致回表查询。
四、前缀索引
可以将字符串的一部分抽取出来作为前缀创建索引
例如：选取前4个字符作为name字段的索引
create index id_name on user(name(4));