1.对索引使用 左 或者 左右模糊匹配
也就是 like %xx,like %xx% 的方式,会造成索引失效,走全表扫描
因为索引 B+ 树是按照【索引值】有序排列 存储的,所以只能根据前缀进行比较
2.对索引使用函数
select ... where length(name) = 6; 索引失效,全表扫描
因为索引保存的是索引字段的原始值,不止经过函数计算的值,所以走不了索引
3.对索引使用表达式计算
跟函数同样的道理
4.对索引隐式类型转换
加入用户列表,phone 字段是字符串类型,但是查询的时候 select ... where phone = 100;
phone会使用cast函数隐式的转换成整型,而前面说了,函数会导致索引失效,所以索引失效。
但
select ... where id = '1';会隐式的将字符串转为数字,索引字段并没有使用函数,所以索引没有失效。
5.联合索引非最左匹配
多个普通字段组在一起构成的索引就是联合索引
where a = 1 and b = 2 符合最左匹配
因为联合索引的B+树中,数据是按照索引的第一列进行排序的,第一列相同,才会排第二列排序。
如果不指定第一列,就从第二列开始查,是无法走索引的。
6.where 中的 or
where 子句中,or 前面的条件如果是索引列,后面不是索引列,就会索引失效。
MySQL优化:
1.使用覆盖索引,避免回表
2.使用多个列作为条件进行查询的时候,选择联合索引替代单列索引
3.让选择性强的索引列放最前面
4.explain分析select查询语句
第一、优化 SQL 和索引
总的来说就是:避免索引失效造成全表扫描、避免无效查询、最大化利用索引
1、只返回必要的行:使用 LIMIT 语句来限制返回的数据。
2、只返回必要的列,避免select *,将需要查找的字段列出来。
3、避免索引失效
4、对于连续数值,使用BETWEEN不用IN:SELECT id FROM t WHERE num BETWEEN 1 AND 5
5、可通过开启慢查询日志来找出较慢的SQL
6、大语句拆小语句,减少锁时间;一条大sql可以堵死整个库
7、使用连接(join)来代替子查询
8、少用JOIN,多表连接时,尽量小表驱动大表,即小表 join 大表
第二、加缓存redis
第三、主从复制、主主复制、读写分离
第四、垂直分表、水平分表