1. 全值匹配我最爱,查询的字段按照顺序在索引中都可以匹配到!
建立索引
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'
SQL 中查询字段的顺序,跟使用索引中字段的顺序,没有关系。优化器会在不影响 SQL 执行结果的前提下,给 你自动地优化。如下图
2. 最佳左前缀法则
查询字段与索引字段顺序的不同会导致,索引无法充分使用,甚至索引失效! 原因:使用复合索引,需要遵循最佳左前缀法则,即如果索引了多列,要遵守最左前缀法则。指的是查询从索 引的最左前列开始并且不跳过索引中的列。 结论:过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无 法被使用。
3. 不要在索引列上做任何计算
不在索引列上做任何操作(计算、函数、(自动 or 手动)类型转换),会导致索引失效而转向全表扫描。
不要在查询列上使用函数,结论:等号左边不要计算
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age=30; (推荐)
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(age,3)=30;
不要在查询列上做数据类型转换,结论:等号右边不要做转换
explain select sql_no_cache * from emp where name='30000'; (推荐)
explain select sql_no_cache * from emp where name=30000;
4. 索引列上不能有范围查询
建议:将可能做范围查询的字段的索引顺序放在最后
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=5 AND emp.name = 'abcd'; (推荐)
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid<=5 AND emp.name = 'abcd';
5. 尽量使用覆盖索引
即查询列和索引列一致,不要写 select *
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt';
explain SELECT SQL_NO_CACHE age,deptId,name FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt';(推荐)
6. 使用不等于(!= 或者<>)的时候
mysql 在使用不等于(!= 或者<>)时,有时会无法使用索引会导致全表扫描。
7. 字段的 is not null 和 is null 【当字段允许为 Null 的条件下】
is not null 用不到索引,is null 可以用到索引。
8. like 的前后模糊匹配,%不要出现在最左侧
9. 减少使用 or
使用 union all 或者 union 来替代: