索引及其优化

1.高新能索引策略

1. 独立的列：索引不能是表达式的一部分，也不能是函数的参数
2. 前缀索引和索引选择性：索引的选择性越高则查询效率越高，选择行高的索引可以过滤掉更多行
3. 多列索引：
4. 选择合适的索引列顺序：不考虑排序和分组，将索引性最高的索引放在最前列
5. 聚簇索引：不是一种单独的索引类型而是一种数据存储方式；
6. 覆盖索引：索引包含所有需要查询的字段值，则称为覆盖索引；
7. 使用索引扫描来做排序
8. 压缩索引
9. 冗余或者重复索引

2.维护索引和表

目的：

• 找到并修复损坏的表
• 维护准确的索引统计信息
• 减少碎片

2.1找到并修复损坏的表

损坏的索引会导致查询返回错误的结果或莫须有的主键冲突等问题，严重时甚至还会导致数据库的崩溃。

可以通过运行CHECK TABLE来检查是否发生了表损坏（因不同存储引擎而异）。

可使用REPAIR TABLE来修复损坏的表，对于不支持的存储引擎，可使用空的ALTER操作来重建表：ALTER TABLE innodb_tbl ENGINE=INNODB。如果损坏的是系统区域或者数据，只能从备份恢复。

MyISAM 的表损坏（corruption）通常是系统崩溃导致的。InnoDB 一般不会出现损坏，如果发生损坏，可能是

• 硬件问题，如内存或磁盘故障（有可能）
• 数据库管理员的错误操作，如在 MySQL 外部直接操作数据文件（有可能）
• InnoDB 本身缺陷（不太可能）

如果遇到数据损坏，不能只是简单修复，更重要的是找到损坏原因，避免在此发生。

2.2更新索引统计信息

MySQL 的查询优化器通过两个 API 来icol_per_row存储引擎的索引值分布情况，两个API分别是：

• records_in_range()：传入两个边界值，查询范围内有多少记录，MyISAM 返回精确值，InnoDB 返回估算值。
• info()：返回各种类型的数据，包括索引的基数（每个键值有多少条记录）

每种存储引擎实现索引统计信息的方式不同：

• Mymory 引擎不存储索引统计信息
• MyISAM 将索引统计信息存储在磁盘中，ANALYZE TABLE 需要全索引扫描计算索引基数，此过程中需要锁表
• InnoDB 不再磁盘中存储索引统计信息，通过随机的索引访问进行评估将其存储在内存中。

2.3减少索引和数据碎片

B 树索引会碎片化，造成查询效率降低。

表的数据存储碎片化有三种类型：

• 行碎片（Row fragmentation）：数据行被存储在多个片段中，对单行的访问性能下降
• 行间碎片（Intra-row fragmentation）：逻辑上顺序的页，在磁盘上不是顺序存储的。对全表扫描和聚簇索引扫描之类的操作影响很大
• 剩余空间碎片（Free space fragmentation）：数据页中有大量空余空间，读取数据时读取大量不需要的数据，造成浪费

解决方式

• OPTIMIZE TABLE 或 导入再导出
• MyISAM 可通过排序算法重建索引
• InnoDB 可先删除索引再重新创建
• 空的ALTER操作来重建表：ALTER TABLE innodb_tbl ENGINE=INNODB（消除表的碎片）

总结三个原则：

1. 单行访问很慢。如果读取一个块只为了获取一行数据，很浪费，最好使一次块读取能获取尽可能多的行；
2. 顺序访问数据很快：1、顺序 IO 不需要磁盘寻道，所以比随机I/O要快；2、如果服务器能够按照顺序读取数据，不再需要而外的排序，且GROUP BY查询无需在做排序和将行按组进行聚合计算；
3. 索引覆盖查询很快，如果一个索引覆盖了查询所需的所有列，那存储引擎就不用回表查询找行。