Mysql索引优化实战二
本文档主要介绍了Mysql索引优化的实战经验，着重于分页查询优化和Join关联查询优化。
一、分页查询优化
在实际业务系统中，分页功能是非常常见的，对于大表的分页查询，执行效率往往非常低。例如，使用以下SQL语句：
```sql
select * from employees limit 10000, 10;
```
这条SQL语句看似只查询了10条记录，但实际上是先读取10010条记录，然后抛弃前10000条记录，最后读取后面的10条记录。因此，执行效率非常低。
那么，如何优化分页查询呢？一种常见的优化技巧是根据自增且连续的主键排序的分页查询。例如：
```sql
select * from employees where id > 90000 limit 5;
```
这条SQL语句通过主键排序，查询从第90001开始的5行数据。执行计划对比发现，改写后的SQL走了索引，扫描的行数大大减少，执行效率更高。
然而，这种改写方法并不是万能的。在某些场景下，主键可能不连续，导致结果不一致。另外，如果原SQL是order by非主键的字段，按照上述方法改写可能导致结果不一致。因此，这种改写方法需要满足两个条件：主键自增且连续，结果按照主键排序。
二、根据非主键字段排序的分页查询
对于非主键字段排序的分页查询，我们可以使用以下方法优化：
```sql
select * from employees ORDER BY name limit 90000, 5;
```
然而，执行计划发现，并没有使用name字段的索引。原因是扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高，所以优化器放弃使用索引。
那么，如何优化呢？我们可以让排序时返回的字段尽可能少，让排序和分页操作先查出主键，然后根据主键查到对应的记录。例如：
```sql
select * from employees e inner join (select id from employees order by name limit 90000, 5) ed on e.id = ed.id;
```
需要的结果与原SQL一致，执行时间减少了一半以上。
三、Join关联查询优化
Join关联查询也是一个常见的查询场景。例如：
```sql
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='表t1';
CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`tid` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_tid` (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='表t2';
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.tid;
```
在实际业务系统中，我们可能需要JOIN多个表，但是JOIN操作的执行效率往往非常低。那么，如何优化JOIN关联查询呢？一种常见的优化方法是使用索引。
```sql
CREATE INDEX idx_tid ON t2 (tid);
```
创建索引后，执行计划对比发现， JOIN操作的执行效率提高了很多。
本文档介绍了Mysql索引优化的实战经验，包括分页查询优化和Join关联查询优化。通过对索引的合理使用和合理的SQL语句优化，我们可以提高Mysql查询的执行效率。
