高性能Mysql笔记 — 索引

index优化

  • 对于频繁作为查询条件的字段使用索引
  • 注意索引字段类型的隐式转换,数据库类型和应用类型要一致

索引的种类

  • 唯一索引,成为索引的列不能重复
  • 单列索引,一个索引只包含一列
  • 单列前缀索引,有些列较长,不宜使用全长作为索引,可以截取列前面一部分作为索引
  • 复合索引(某几列也可以是前缀索引),一个索引包含几个列,复合索引满足最左匹配原则

几个名称

聚簇索引

innodb默认会创建聚簇索引(使用主键聚集数据;如果没有主键,innodb选择一个唯一的非空索引替代;如果没有索引innodb隐式定义一个主键作为聚簇索引),就是将数据按照一个或者多个列排序,然后存储在磁盘上,使用BTree数据结构存储索引,聚簇索引的叶子节点就是数据节点
优点:

  1. 将相关数据聚集存储,减少IO。比如按照用户id聚集数据,从磁盘读取较少的数据页就可以或者某个用户的全部邮件
  2. 数据访问更快,因为索引和数据行存储在一起
  3. 覆盖索引扫描的查询可以直接使用叶子节点中的主键值

缺点:

  1. 提高的是IO密集型应用,如果数据全部放在内存中,聚簇索引就没有优势了
  2. 插入速度严重依赖插入顺序。新插入行或者更新主键的时候性能较差,建议一般使用int自增(因为聚簇索引是有序的,如果插入是随机的,则会造成频繁的页分裂,如果使用自增的话,插入总是在最后位置插入,不引起原有顺序的破坏)的主键
  3. 更新聚簇索引列的代价很高,innodb强制将更新的行移动到新的位置
  4. 聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候
  5. 二级索引,也就是非聚簇索引,需要查找两次,而且二级索引的叶子节点包含了主键列,可能导致二级索引较大

非聚簇索引

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在前。
  • 聚簇索引
  • 覆盖索引
  • 冗余和重复索引,删除重复索引
  • 未使用的索引,删除未使用的索引
  • 索引和锁,索引可以让查询锁定更少的行,因为通过索引会过滤掉一些行,只会回到表中查询过滤剩下的行,只会锁定这些行

维护索引和表

找到并修复损坏的表

  1. check table
  2. repair table
  3. alter table test engine=innodb,alter table不做任何操作
  4. 先导出数据,再导入新表
  5. 使用其他工具

更新索引统计信息

  1. analyze table,重新生成统计信息,因为优化器会根据统计信息衡量成本
  2. show index from table_name,查看table_name表的统计信息,会触发系统更新统计信息,给系统带来额外的压力
  3. infomation_schema.statistics

减少索引和数据碎片

  • 行碎片(innodb不会出现,因为innodb会移动短小的行并重写到一个片段中)
  • 行间碎片
  • 剩余空间碎片
    以上三种情况myisam都会出现

通过optimize table整理数据
innodb可以通过先删除索引再生成索引的方法消除碎片
对于不支持optimize table的引擎可以通过no-op的alter table重建表来消除碎片

explain

type

  • index:使用索引扫描来排序

extra

  • using index:覆盖索引
上一篇:ORACLE SEQUENCE用法 (自增长)


下一篇:Unity插件扩展中组件常用的几个方法