mysql 索引优化原则总结(limit where in like )

优化原则：

1. where in 和like效果等同，所以如果某个字段需要wherein或者like，请将该字段放到索引的最后

2. limit start num，当start越大时候 扫描的行数越多（即便是命中索引），越慢，解决办法是：select Id from xxx where xxx，即：仅select出主键，然后再 select * from xxx where id in(1,2,3,主键。。。)

3. 【where条件中字段的顺序】与【索引建立时字段的顺序】是否一致 是无所谓的，mysql会自动优化顺序，但是 如果存在order by，那么 order by 要求与索引字段顺序一致，且排序方向必须相同（即 所有排序字段要么是asc要么是desc，必须一样）

4. 因为mysql优化的底层逻辑是遵循左前缀原则，所以尽量让 where +order by 匹配出完整索引字段

 

 

---

 

 

 

 

Mysql的索引优化、设计原则

• 1、索引优化点
• 2、索引设计原则
• • 2.1、代码先行，索引后上
  • 2.2、联合索引尽量覆盖条件
  • 2.3、不要在小基数字段上建立索引
  • 2.4、长字符串我们可以采用前缀索引
  • 2.5、where与order by冲突时优先where
  • 2.6、基于慢sql查询做优化
• 3、in和exsits优化
• 4、count(*)查询优化
• 5、查询总行数的优化

 

1、索引优化点

• 1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低。
• 2、order by满足两种情况会使用Using index。
  • 2.1、order by语句使用索引最左前列。
  • 2.2、使用where子句与order by子句条件列组合满足索引最左前列。
• 3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
• 4、如果order by的条件不在索引列上，就会产生Using filesort。
• 5、能用覆盖索引尽量用覆盖索引
• 6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了。

2、索引设计原则

2.1、代码先行，索引后上

在开发完一个功能的主体功能之后，拿出所有的sql进行整体分析，如何去建立索引。

2.2、联合索引尽量覆盖条件

比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的 where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。

2.3、不要在小基数字段上建立索引

就比如说性别字段，最多也就那几个枚举值，建索引的意义不大。

2.4、长字符串我们可以采用前缀索引

比如一个字符串有几百位长，其实我们通常只需要取出前20位就可以了。

2.5、where与order by冲突时优先where

因为where条件是优先执行的，当where条件过滤之后的数据集都比较小了，再进行排序也不会浪费太多性能。

2.6、基于慢sql查询做优化

可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。

3、in和exsits优化

原则：小表驱动大表，即小的数据集驱动大的数据集，所以请看下面的sql语句即可。

-- in的话是先执行括号里面的，所以适应情况是B表数据小
select * from A where id in (select id from B)
-- exists是先执行括号外面的，所以适应情况是A表数据小
select * from A where exists (select 1 from B where B.id = A.id)

 

• 1
• 2
• 3
• 4

这里有几个exists的点需要注意一下：
1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会 忽略SELECT清单,因此没有区别。
2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比。
3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析。

4、count(*)查询优化

-- 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
set global query_cache_size=0;
set global query_cache_type=0;

EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;

 

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

以上四条统计sql，哪一条的执行效率最高，其实四条的效率几乎是差不了多少的，具体答案在下面：
字段有索引时：count(*)≈count(1)>count(字段)>count(主键 id)。
count(字段)比count(主键 id)快的原因：count(字段)统计走二级索引，二 级索引存储数据比主键索引少，所以count(字段)>count(主键 id)。

---

字段无索引时：count(*)≈count(1)>count(主键 id)>count(字段)。
count(主键 id)比count(字段)快的原因：字段没有索引count(字段)统计走不了索引， count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)。

---

count(1)比count(字段)快的原因：count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出 字段，所以理论上count(1)比count(字段)会快一点。
count(*)很快的原因：count(*) 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用 count(列名)或count(常量)来替代 count(*)。
为什么对于count(id)，mysql最终选择辅助索引而不是主键聚集索引？
因为二级索引相对主键索引存储数据更少，检索 性能应该更高，mysql内部做了点优化(应该是在5.7版本才优化)。
注意：根据某个字段count不会统计字段为null值的数据行。

5、查询总行数的优化

1、直接查询mysql维护的总记录数
对于myisam：对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被 mysql存储在磁盘上，查询不需要计算。
对于innodb：mysql不会存储表的总记录行数(因为有MVCC机制)，查询count需要实时计算。

---

2、show table status
这个sql可以展示总记录数，但是不太精确。

-- 展示所有表
show table status;
-- 展示某一张表
show table status like 'actor';

 

• 1
• 2
• 3
• 4

---

3、将总数维护到Redis里
插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难 保证表操作和redis操作的事务一致性。

---

4、增加数据库计数表
插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作。