《高性能MySQL》读书笔记创建高性能的索引

索引的创建使用策略

前缀索引和索引的选择性

优点：提高索引的性能、节省索引空间

缺点：降低索引的选择性

索引的选择性：不重复的索引值（也称为基数，cardinality）和数据表的记录总数(＃T)的比值

索引的选择性越高，在查找的时候就可以过滤掉越多的行

对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL并不支持对这些列的完整内容进行索引

确定合适的前缀长度

先获取完整列的选择性，然后不断增加前缀长度比较，直到获取到的选择性接近完整列的选择性

方式一：select count(*) as c, left(列,长度) as pref from xxx group by pref order by c desc limit n;

方式二：select count(distinct left(列,长度)) / count(*);

方式二在数据发布不均匀的情况下可能会选择出错误的前缀长度

多列索引

索引合并：MySQL提供的策略，在一定程度上可以使用表中的多个单列索引定位指定的行

如果在explain是看到了使用索引合并，一般说明索引创建得比较糟糕，需要检查优化：

当优化器需要对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引
当优化器需要对多个索引做联合操作时（通常有多个OR条件），通常需要在算法的缓存、排序和合并操作上耗费大量CPU和内存资源，尤其是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候
更重要的是，优化器不会把这些操作计算到“查询成本”(cost)中，优化器只关心随机页面读取。这会使得查询的成本被“低估”，导致该执行计划还不如直接进行全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响并发的查询，但如果单独运行这样的查询则往往会忽略对并发性的影响

避免使用到索引合并：

通过参数optimizer_switch来关闭索引合并功能
使用IGNORE INDEX语法让优化器强制忽略掉某些索引，从而避免优化器使用包含索引合并的执行计划

选择合适的索引列顺序

考虑因素：查询局中的排序、分组、条件

索引的选择性和基数

覆盖索引

使用索引扫描结果做排序

维护并修复损坏的表

即使用正确的数据类型创建了表并加上了合适的索引，工作也没有结束：还需要维护表和索引来确保它们都能正常工作。维护表有三个主要目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片

找到并修复损坏的表

CHECK TABLE来检查是否发生了表损坏（注意，有些存储引擎不支持该命令；而有些存储引擎则支持以不同的选项来控制检查表的强度）。CHECK TABLE通常能够找出大多数的表和索引的错误

使用REPAIR TABLE命令来修复损坏的表，但同样不是所有的存储引擎都支持该命令。如果存储引擎不支持，可通过一个不做任何操作(n o-o p)的ALTER操作来重建表

……

更新索引统计信息

如果存储引擎向优化器提供的扫描行数信息不准确，或者执行计划本身太复杂以致无法准确地获取各个阶段匹配的行数，那么优化器会使用索引统计信息来估算扫描行数。MySQL的优化器使用的是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。如果表没有统计信息，或者统计信息不准确，优化器就很有可能做出错误的决定。可以通过运行ANALYZE TABLE来重新生成统计信息，以解决这个问题

InnoDB会在表首次打开，或者执行ANALYZE TABLE，或者表的大小发生非常大的变化时计算索引的统计信息

问题：如果服务器上有大量的数据表，这可能会带来严重的问题，尤其是当I/O比较慢的时候。客户端程序或者监控工具触发索引信息采样更新时可能会导致大量的锁，并给服务器带来很多额外的压力，这会让用户因为启动时间漫长而感到沮丧

可以关闭innodb_stats_on_metadata参数来避免上面提到的问题

减少索引和数据的碎片

数据碎片：

行碎片：数据被存储在多个地方的多个片段中
行间碎片：逻辑上顺序的页或行在磁盘上不是顺序存储的
剩余空间碎片：数据页中有大量的空闲空间，导致服务器读取大量不需要的数据，造成浪费

可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。这对多数存储引擎都是有效的。对于那些不支持OPTIMIZE TABLE的存储引擎，可以通过一个不做任何操作(n o-o p)的ALTER TABLE操作来重建表