MySQL 性能优化(三) 索引优化

一：如何建立合适的索引列

1：在where 从句，group by 从句，order by 从句，on 从句中出现的列

2：索引字段越小越好，因为字段小，索引节点就小，每个页存放的索引节点就多，然后在遍历时，IO效率据更大

3：离散度大的列放在联合索引的前面

select * from payment where staff_id = 2 and customer_id = 500;

是使用index(staff_id ,customer_id )好还是 index(customer_id ，staff_id )好？

由于customer_id 离散度比较大，所以使用index(customer_id ，staff_id )会更好

二：索引优化sql的方法

1：增加索引会加快查询速度，但是对写入速度会造成影响，但是过多的索引有时候会影响到写入速度也会影响到查询，如果索引越多，那么分析的过程就越慢，这样同样会减少查询的效率。那么既然知道增加索引也要知道删除不必要的索引。比如重复索引和冗余的索引。

重复索引：相同的列以相同的顺序建立的额索引，主键已经是唯一索引了

冗余索引：在information_scheme 库查询，但是这个sql只能解决一部分问题，因为它只查询索引的前缀，并没有检查哪些索引是包含主键的，可以使用pt-duplicate-key-checher工具 ，不仅可以查询出重复索引还能给出建议

SELECT
    a.TABLE_SCHEMA AS '数据名',
    a.TABLE_NAME AS '表名',
    a.INDEX_NAME AS '索引1',
    b.INDEX_NAME AS '索引2',
    a.COLUMN_NAME AS '重复列名'
FROM
    STATISTICS a
JOIN STATISTICS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
AND a.COLUMN_NAME = b.COLUMN_NAME
WHERE
    a.SEQ_IN_INDEX = 1
AND a.INDEX_NAME <> b.INDEX_NAME;

三：索引维护的方法

如果因为一些业务变更，一些索引可能不用了，但是还存储在表中，那么它会影响到写入的速度，所以需要进行删除。

目前mysql还没有记录索引的使用情况，但是在其他的一些数据库是可以通过一些方式进行查询，所以使用mysql只能通过慢查询日志配合pt-index-usage 工具来分析

注意：如果是在一主多从的情况下，在不同从上我们的业务是不一样的话，那么就要查询所有的主从慢查询日志进行统一分析。因为一条索引可能在一个从上不使用，但是在另一个从上却使用；又或者在主上不使用，但是在从上是使用的，那么这种索引也是需要保留的。