Mysql 之SQL优化

本文就使用mysql时，几种sql 常见优化 进行说明：

• 分页优化。在数据需要进行分页展示时，我们需要进行分页查询。在数据量叫大时，那么分页性能将会降低，特别是在查询最后的数据时。比如：

select * from employees limit 10000,10;

语义为从employees 表中，从10001 行开始，取十条数据。数据库引擎在执行该sql 时，只会查询10010 行数据，并把前10000条数据丢弃。这也就是为什么数据较大时，会较慢的原因。那么如何优化呢？

场景1：对自增主键，且主键连续的数据进行分页。对于上述sql 可以优化为

 select * from employees where id > 10000 limit 10; 

即首先计算得知将获取的页数据开始id ，使用主键索引，进行范围查询。但是，该方式适用范围有限，只适合主键自增，且连续的数据。如果不满足该条件，上述两个sql 将可能得到不同的结果。

场景2：根据非主键索引排序后分页。如下sql

select * from employees ORDER BY name limit 90000,5;

该sql 中排序字段name 为二级索引， 在之前关于explain的文章中讲到，在查询时，因为查询字段未覆盖被二级索引覆盖，所以查询时，mysql 会认为当前查询如果使用二级索引，会导致大量回表查询，可能还不如全表扫描来的快，所以会跳过二级索引直接走全表扫描进行数据分页。

优化：

 select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

 思想就是尽量减少排序时返回的字段，使用子查询通过二级索引（二级索引大小远远小于聚簇索引），首先获取到当前分页数据的主键数据，然后在使用主键获取分页数据行。

• Join关联查询优化

      首先有两个表，t1 , t2 , 二者具有相同结构，除了主键外，有字段a, b . 其中，a 为二级索引，b 无索引。假设，t1 中有10000万条数据，t2 中有100条数据。那么下面两种查询：

select * from t1 inner join t2 on t1.a= t2.a;

mysql 根据优化会进行小表驱动大表方式进行查询。针对上述sql ，会先从t2中扫描，每次一行，然后逐行取a 字段值至t1中进行关联查询。总计t2 需取数据100次，t2数据取出a 字段到t1中进行索引扫描，每次相当于最终扫描t1一次，所以总计也是100次。整个过程总计扫描200次就完成了数据关联查询。这个有索引时，使用小表中数据循环扫描大表数据的算法，即为NLJ（Nested Loop Join）算法。

与之对应的为BNL(Block Nested Loop Join ) 算法。

select * from t1 join t2 on t1.b = t2.b

使用该算法时，在执行计划中，我们可以在extra 字段中看到Using join buffer 字样。该算法执行过程为：首先取出小表t2 中的数据放入buffer 中，然后取出t1的所有数据，与buffer 中的t2 数据进行比对，最终返回join 结果。

因为整个过程，对t1 和t2 都进行了全表扫描（取出了两个表的所有数据），所以扫描次数为10100。同时，因为buffer 中数据无序，那么意味着t2的每条数据都与t1的10000条数据进行了比对，那么就是说该算法还有100*10000=100万次的buffer中内存判断。换句话说，假如该条sql 使用NLJ算法进行，那么将对数据进行100万次的磁盘扫描，那么是多么恐怖！

所以针对join 优化建议是：

1.对join 字段，建立索引

2.确保小表驱动大表的形式进行查询。

• count（）查询优化

我们在使用count 时，往往使用如下几种方式：

select count(1) FROM t1;
select count(id) from t1;
select count(name) from t1;
select count(*) from t1;

其实呢，上述几种方式在没有where 条件的情况，查询效率相当。

如果，字段有索引，那么 count(*) ~ count(1) > count(name)>count(id)；name 有索引时，count 可以走二级索引，同时因为二级索引数据要少于主键索引，所以count（name）要优于count（id）

如果，字段无索引，那么 count(*) ~ count(1) > count(id)>count(name)，name 无索引，所以count（name）无法走索引，而count(ID) 还可以走主键索引。

count(name)  执行时，需取出数据，并对每个数据进行加1 操作。count（1）只需要直接对常量1进行统计，无需取出字段。所以要优于count（name）.

count（*）为官方推荐写法，自然对其进行了大量优化。

优化建议：

1. 使用myisam 引擎。因为myisam 会维护数据表总行

2. 使用redis ，将数据总数维护至缓存，但是无法做到与数据库强一致性

3.维护数据库表，将所有表总记录数维护至某张数据表中。

在使用count 叫多的场景下，进行上述优化，还是有必要的。

以上。