mysql分页查询优化

此处mysql表已经设置了
主键索引 PRIMARY KEY (id),
联合索引 KEY idx_name_age_position (name,age,position) USING BTREE

  • mysql> EXPLAIN select * from employees limit 90000,5;这条sql语句会查询约10w条数据，只拿出需要的5条其他全部丢弃，没走索引，效率很低。
  • mysql> EXPLAIN select * from employees where id > 90000 limit 5 这条sql语句虽然走了主键索引且只查了约2w条数据，效率大大提高。但在业务逻辑上只有当主键自增且连续，且结果是按照主键排序的，这样的语句才能有用。那非主键自增且不按照主键排序的呢
  • mysql> select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id; 这个是先根据你需要排序的组队name排序，使用覆盖索引查出5个id，这个效率是比较高的。执行计划的type是index，key是联合索引。
    然后再查询最外围的sql，这个外围的sql是走的主键索引。执行计划的type是eq_ref，key是主键索引。Extra从Using filesort 变成了Using Index。

in和exsits优化

原则：小表驱动大表，即小的数据集驱动大的数据集
in：当B表的数据集小于A表的数据集时，in优于exists

exists：当A表的数据集小于B表的数据集时，exists优于in
将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留

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

上面这个优化貌似没什么用。

count(*)查询优化

  • count计算的列如果有null是不会被计算在内的，所以一般用限定为非空的列来统计。
  • 在同时有主键索引和非主键索引的情况下，count的效率是非主键索引更高。二级索引相对主键索引存储数据更少，检索性能应该更高。InnoDB的主键索引连着数据data，数据较多；MyIsAm这块是分开的两个物理文件里。

查询mysql自己维护的总行数

  • 对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算。
  • 对于innodb存储引擎的表mysql不会存储表的总记录行数，查询count需要实时计算
  1. 将总数维护到Redis里（这种方式可能不准，很难
    保证表操作和redis操作的事务一致性）
  2. 增加计数表

mysql的优化，会归结到sql的优化上，sql的优化又归结到索引的优化，索引的优化又归结到执行计划显示的type能否达到range（范围查找） 甚至eq_ref（精确的匹配到几个）和eq（精确的匹配到一个）