Mysql优化之索引

索引其实就是一个文件，它与Mysql数据文件不一样的地方是：它是顺序的保存数据，文件小且保存的位置也不一样；
索引能加快检索，但系统每一次维护数据（写入、更新）的同时也需要维护索引，带来额外的开销。
索引按照底层实现方式分为：B树索引、R树索引、哈希索引等；
索引按照具体表现分为：主键索引（primary key）、唯一索引（unique）、单列索引（index）、全文索引（fulltext）、空间索引等；
单列索引又可分为：联合索引（也叫多列索引 | 复合索引）、前缀索引、覆盖索引；

* 查找未被使用的索引（Mysql 5.6？）

使用Sql语句查询，然后查看索引使用次数，关注使用次数少的索引，按需删除：
SELECT object_schema,object_name,index_name,b.`TABLE_ROWS`
FROM performance_schema.table_io_waits_summary_by_index_usage a
JOIN information_schema.tables b
ON a.`OBJECT_SCHEMA`=b.`TABLE_SCHEMA` AND a.`OBJECT_NAME`=b.`TABLE_NAME`
WHERE index_name IS NOT NULL
AND count_star = 0
ORDER BY object_schema,object_name;

* 删除重复和冗余的索引

可以使用 pt-duplicate-key-checker 工具查看重复的索引

* 更新索引统计信息

使用Sql语句优化：
analyze table table_name

* 减少索引碎片

使用Sql语句优化，会导致锁表，慎用：
optimize table table_name

* 导致Mysql放弃使用索引的情况

• 通过索引扫描的记录数超过表记录数的30%，就换成全表扫描；
• 联合索引（复合索引）中第一个索引列使用范围检索（< | <= | > | >= | not in | between | not between）；
• 联合索引（复合索引）中不使用最左索引列；
• 模糊查询条件列最左侧以通配符 % 开始；
• 检索时索引列使用表达式或函数；
• 单列索引使用（NOT IN 、<>、！=）操作；
• 有非索引列检索的OR条件，虽然会使用索引但是会全表扫描；
• 如果索引列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引；
• 左连接查询或右连接查询关联的字段编码格式不一样；

* 使用索引的误区

• 建表的时候不需要建索引，以后优化在增加索引；
• Where 条件后面接的字段都需要加索引；
• 简单SQL不需要索引，联合查询才需要；
• 联合索引的顺序是where条件后字段的先后顺序；
• 对于选择性小的字段上也新建索引,如状态,性别等字段等；

*  只创建你确实需要的索引；

*  每张表的主索引应该尽可能短；

*  索引的选择性不能太低，这会导致查询优化器放弃使用索引，如性别；

索引的选择性是不重复的索引值与表的记录数的比值，索引的选择性越高检索效率越高。
例：一个表只有一列，共有4行数据，分别为：a | a  | b | b，则选择性为 2/4=0.5 // 低
       如果4行数据分别为：a | b | c | d，则选择性为 4/4=1 // 高

*  索引列上不能使用表达式或函数；

例如：Sql语句 Where to_days(out_date)-to_days(current_date) <= 30 应改为 Where out_date <= date_add(...) 这样的形式

*  如果一个索引在头几个字符上有唯一的前缀，使用前缀索引比较好，长字符串也使用前缀索引；

例如一个需要加索引的列（status）其值可能为：online、offline、blocked、approval、deleted ...
应使用这样的语句为该列添加索引：create index index_name on table_name(status(2))，
而不是：create index index_name on table_name(status)；

*  使用联合索引（多列索引）时，应合理的选择第一列

1> 经常会被使用的列优先；
2> 选择性高的列优先；
3> 宽度小的列优先；

*  合理使用哈希索引

使用哈希索引有一些注意事项：
1：一般用于使用=或<=>操作符的等式比较(很快)。发现范围值的时候就会比较慢；
2：优化器不能使用hash索引来加速ORDER BY操作，因为哈希索引不是顺序的保存数据的；

* 不要给选择性低的列建单列索引，因为Mysql可能会放弃使用索引

* 尽量使用联合索引（复合索引）

* 单表的索引数量不要太多，最好不要超过5个

* 使用覆盖索引时避免“select * ...” 应查询覆盖索引中的列，因为可以直接从索引文件里提取数据