MySQL查询优化方案汇总（索引相关）

被时间投喂过

索引相关

类型隐式转换 大坑

1. **字段filed1是varchar类型，且加了索引，如果 where filed1 = 123; type 可能是all，因为123是数字类型，mysql内部会用函数做隐式转换，用了函数，索引就失效了。**

复制代码

大数据深度分页，用主键

1. select field1,field2 from table limit 100000,10;
2. select field1,field2 from table where id > 100000 limit 10;

复制代码

避免使用MySQL函数

1. MySQL内置了很多函数，使用函数可能导致索引失效，尽量让MySQL只做简单的增删改查。

复制代码

避免类型的隐式转换

1. varchar等字符串类型的字段被加了索引，把这字段当做where条件，及时目标值是数字，也要加引号，否则类型的隐式转换，会引起索引失效的问题。

复制代码

避免使用函数或表达式，尽量只让数据库做纯粹的增删改查。

1. 用函数的前提是知道初始值，然后在操作数据，很多函数都是要传参的，所以mysql只能全表查，然后每次迭代将数据丢给函数处理。
2. 表达式也是如此，例如where number + 1 = 10，都会让索引失效。

复制代码

避免使用不等值做排除法

1. 避免使用<>、!=、not in、is not null、这些都会使索引失效。

复制代码

避免使用null值

1. 避免字段使用null值，一是影响索引（mysql建索引建的是非null的值，大量的null值影响了正常的B+tree结构），而且造成聚合函数统计（如count(该字段)）不准确的问题。

复制代码

索引无关

减少大字段查询，避免使用*，不说磁盘io的损耗，连网络带宽都跟着损耗。
如果只select仅需字段，可能会触发覆盖索引机制，不用回表，提高性能。

1. select * from table;
2. select field1,field2 from table;

复制代码

查询是否存在

1. select count(*) from table where...;
2. select field from table where ... limit 1;

复制代码

冗余优化

1. 想要查询一篇文章的浏览量，不用count(浏览记录)。
2. 新建一个在文章表中建立一个浏览量的字段，这使得查询的时间复杂度从O(n)变成O(1)；

复制代码

避免join，适用于大表关联小表。

1. 如果想要join的两个表，一个很大，一个很小，应尽量避免join。
2. 可将小表数据全部取出来组装成数组，放入编程语言的内存，用编程语言的内存匹配的方式去关联。

复制代码

哪些场景下索引会失效

• 使用not in、is not null、、!=、这种排除法时会导致索引失效，覆盖索引除外。
  
• 最左匹配原则，左边的字段缺少时会出现，覆盖索引除外。
  
• 最左匹配原则，左边的字段有区间查询，导致右边的字段无法使用索引。
  
• like左边或两边加百分号。
  
• 类型的隐式转换，如varchar的字段，使用where varchar_field = 123，包括join表，用on连接的字段。
  
• where条件有函数，或表达式。
  
• where语句包含or，or中存在非索引列。
  
• 大数据量对二级索引字段排序，如果select * 或者其它字段，这个过程涉及回表，可能无法使用索引，因为数据量大，走索引的每条数据都需要回表，代价会很大。
  
• order by字段，如果排序与索引顺序不一致，则可能导致索引失效，如果order by的每个字段，都按照索引的顺序，或者反顺序，则仍旧会走索引。
  

那些查询适合创建索引？

<ul>需要唯一性约束兜底的字段。
经常被查询或者作为where条件的字段，=、>、