高性能Mysql笔记 — 索引
index优化
- 对于频繁作为查询条件的字段使用索引
- 注意索引字段类型的隐式转换，数据库类型和应用类型要一致
索引的种类
- 唯一索引，成为索引的列不能重复
- 单列索引，一个索引只包含一列
- 单列前缀索引，有些列较长，不宜使用全长作为索引，可以截取列前面一部分作为索引
- 复合索引（某几列也可以是前缀索引），一个索引包含几个列，复合索引满足最左匹配原则
几个名称
聚簇索引
innodb默认会创建聚簇索引（使用主键聚集数据；如果没有主键，innodb选择一个唯一的非空索引替代；如果没有索引innodb隐式定义一个主键作为聚簇索引），就是将数据按照一个或者多个列排序，然后存储在磁盘上，使用BTree数据结构存储索引，聚簇索引的叶子节点就是数据节点
优点：
- 将相关数据聚集存储，减少IO。比如按照用户id聚集数据，从磁盘读取较少的数据页就可以或者某个用户的全部邮件
- 数据访问更快，因为索引和数据行存储在一起
- 覆盖索引扫描的查询可以直接使用叶子节点中的主键值
缺点：
- 提高的是IO密集型应用，如果数据全部放在内存中，聚簇索引就没有优势了
- 插入速度严重依赖插入顺序。新插入行或者更新主键的时候性能较差，建议一般使用int自增（因为聚簇索引是有序的，如果插入是随机的，则会造成频繁的页分裂，如果使用自增的话，插入总是在最后位置插入，不引起原有顺序的破坏）的主键
- 更新聚簇索引列的代价很高，innodb强制将更新的行移动到新的位置
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
- 二级索引，也就是非聚簇索引，需要查找两次，而且二级索引的叶子节点包含了主键列，可能导致二级索引较大
非聚簇索引
innodb，也就是二级索引，叶子节点
辅助索引
在聚簇索引之上创建的索引称为辅助索引，辅助索引需要二次查找，叶子节点存储的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行
覆盖索引
如果索引包含所有满足查询需要的数据，那么这个索引称为覆盖索引，也就是常说的不回表操作。在explain的时候，extra字段是Using index表示就是覆盖索引
高性能的索引策略
- 独立的列：索引列不能使表达式的一部分、函数的参数
- 前缀索引和索引选择性：索引选择性是指，不重复的索引值 / 数据表的总记录数。唯一索引的选择性是1，也就是最好的选择性。一般来说接近n就可以了（n = select count(distinct cloumn) / count(id) from table_name）
- 多列索引：在where条件里使用多个条件的时候,如果每个字段创建各自的索引，mysql会使用union优化。optimizer_switch：控制索引合并
- 多列索引的顺序：BTree存储使用的是顺序存储。在不考虑分组和排序的情况下，将选择性最高的索引放在最前面，还有要考虑每个字段对应值得分布，比如a、b两个字段，a有30个，b有3000个，那么创建索引的时候肯定是a在前。
- 聚簇索引
- 覆盖索引
- 冗余和重复索引，删除重复索引
- 未使用的索引，删除未使用的索引
- 索引和锁，索引可以让查询锁定更少的行，因为通过索引会过滤掉一些行，只会回到表中查询过滤剩下的行，只会锁定这些行
维护索引和表
找到并修复损坏的表
- check table
- repair table
- alter table test engine=innodb，alter table不做任何操作
- 先导出数据，再导入新表
- 使用其他工具
更新索引统计信息
- analyze table，重新生成统计信息，因为优化器会根据统计信息衡量成本
- show index from table_name，查看table_name表的统计信息，会触发系统更新统计信息，给系统带来额外的压力
- infomation_schema.statistics
减少索引和数据碎片
- 行碎片（innodb不会出现，因为innodb会移动短小的行并重写到一个片段中）
- 行间碎片
- 剩余空间碎片
以上三种情况myisam都会出现
通过optimize table整理数据
innodb可以通过先删除索引再生成索引的方法消除碎片
对于不支持optimize table的引擎可以通过no-op的alter table重建表来消除碎片
explain
type
- index：使用索引扫描来排序
extra
- using index：覆盖索引
