MySQL 索引

表属性关系的规范化

目的：为了构造合适的数据模式即逻辑结构。

范式	含义	示例
1NF(第一范式)	表中的每一列都是不可分割的基本数据项。	在员工关系表(职工号，姓名，电话号码)组成的表，由于一个人可能有一部办公电话和一部移动电话)，需要将其规范化为1NF可以将电话号码分为“办公电话”和“移动电话”两个属性，即职工(职工号，姓名，办公电话，移动电话)。
2NF(第二范式)	建立在1NF基础上，每一个非主属性完全函数依赖于码。	在选课关系表(学号，课程号，成绩，学分)，码为(学号，课程号)，但由于非主属性“学分”仅依赖于“课程号”，对码(学号，课程号)只是部分依赖，而不是完全依赖，因此会导致数据冗余以及更新异常等问题，需要将其分为两个关系模式：选课表(学号，课程号，分数)和课程表(课程号，学分)，新关系通过选课表中的外码(”课程号“)引用课程表。
3NF(第三范式)	建立在2NF基础上，每一个非主属性既不部分依赖于码也不传递依赖于码。	在学生住宿关系表(学号，院系，院系宿舍)，由于学号函数依赖于院系，院系函数依赖于院系宿舍，可得学号传递依赖于院系宿舍。需要将其拆分为选专业表(学号，院系)和院系表(院系，院系宿舍)解决。

事务的标准特征

特性	含义
原子性(Atomicity)	一个事务必须被视为一个不可分割的最小工作单元。整个事务的操作要么全部成功提交，要么全部失败回滚。
一致性(Consistency)	数据库总是从一个一致性状态转移到另一个一致性状态。即使在事务执行过程中系统崩溃，那么已执行语句也不会被提交保存在数据库中。
隔离性(Isolation)	一个事务所做的修改在最终提交以前，对其他事务不可见。
持久性(Durability)	一旦事务提交，则其所做的修改就会被永久保存在数据库中。此时即使系统崩溃，修改的数据也不会丢失。

事务的隔离级别

隔离级别	含义	脏读	不可重复读	幻读	加锁读
-	-	A事务将某记录修改，然后B事务也读取该记录，此后A因为某种原因撤销对该记录的修改，导致B所读取到的数据是无效的。	A事务读取某一记录，B事务也读取并修改了该记录，A为了对读取值进行检验而再次读取该记录，得到不一样的结果。	A事务读取某范围记录时，B事务在该范围插入新记录，A事务再次读取产生幻行。	读取的每一行数据都加锁，可能导致大量的超时和锁争用。
READ UNCOMMITED(未提交读)	事务中的修改，即使还未提交，对其他事务也是可见的。	是	是	是	否
READ COMMITED(提交读)	事务开始时，只能看见已经提交的事务所做的修改(事务中的修改除非已提交，否则对其他事务不可见)。	否	是	是	否
REPEATABLE READ(可重复读)	同一个事务中多次读取同样的记录结果是一致的。	否	否	是	否
SERIALIZABLE(可串行化)	强制事务串行执行。	否	否	否	是

MySQL默认隔离级别是可重复读，而其他大多数数据库(Oracle)是提交读。

---

数据存取原理

Topic	原理
主存存取	将地址信号放到地址总线上传给主存，主存读到地址信号后，解析信号并定位到指定存储单元，然后将此存储单元数据放到数据总线上，供其它部件读取。写主存的过程类似，系统将要写入单元地址和数据分别放在地址总线和数据总线上，主存读取两个总线的内容，做相应的写操作。
磁盘存取	将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。为了读取这个扇区的数据，需要将磁头斜切向运动(沿磁盘半径方向运动)到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。

数据查找算法性能分析

查找算法	特性
顺序查找	大数据量下性能糟糕，O(n)。
二分查找	要求被检索的数据有序，O(log2n)。
二叉树	不可能同时将两列都按顺序进行组织，O(log2n)。
红黑树	树的层级较深，I/O渐进复杂度为O(log2n)。
B-Tree	巧妙利用了磁盘预读原理，根节点常驻内存，节点按页分配和置入内存，I/O渐进复杂度为O(logdN)。
B+Tree	节点出度dmax=floor(pagesize/(keysize+datasize+pointsize))，节点去掉了data域，因此可以拥有更大的出度，I/O渐进复杂度为O(logdN)。

存储引擎类别和特性

类别	特性
Memory	支持使用非唯一哈希索引
MyISAM	使用前缀压缩技术存储、通过数据的物理位置引用被索引的行、号外(不支持事务、只支持表级锁、不支持外键、崩溃后无法安全恢复、适合读多写少，对原子性要求低场景)
InnoDB	自适应哈希索引、原格式聚簇存储、二级索引通过主键引用被索引的行、号外(支持事务和行级锁、支持外键、支持真正的热备份(不用停止对所有表的写入来备份数据)、支持崩溃恢复、适合读少写多场景)

聚簇和非聚簇表对比图

查询执行路径图

索引类型，含义，限制和适用场景

索引类别	描述	限制	适用场景
哈希索引	基于哈希表实现，对于所有的索引列计算一个哈希码，同时在哈希表中保存指向每个数据行的指针。如果多个列的哈希值相同，以链表的方式存放多个数据行指针。	不存储字段值，不能使用索引来避免读取行。 不是按照索引值的顺序排序的，无法用于排序。 不支持部分索引列匹配查找。	精确匹配索引所有列
BTree索引	基于B-Tree/B+Tree实现，节点内存放索引值和指向数据的指针/行记录主键	不能跳过索引中的列。 如果不是按照索引的最左列开始查找，则无法使用索引。 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。	全值匹配、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配第一列且范围匹配第二列
空间数据索引	无需前缀查询，从所有的维度来索引数据，因此可以有效地使用任意维度来组合查询。	必须使用GIS相关函数如MBRCONTAINS()等来维护数据	地理数据存储
全文索引	查找的是文本中的关键字，而不是直接比较索引中的值	有许多需要注意的细节，如停用词、词干和复数、布尔搜索等，更类似搜索引擎做的事，非简单的WHERE匹配	MATCH AGAINST操作
聚簇索引	不是一种单独的索引类型，是一种数据存储方式。将数据行和相邻的键值紧凑地存储在一起。	无法同时将数据行存储在两个不同的地方，所以一个表只能有一个聚簇索引。 插入速度严重依赖插入顺序，按主键插入数据到表速度最快，否则在插入完毕后需要使用OPTIMIZE TABLE命令重新组织表。 插入新行或主键被更新导致移动行时，可能面临页分裂问题(页变得稀疏并被不规则填充，导致数据碎片和表占用更多的磁盘空间)。 行稀疏或页分裂导致数据存储不连续时可能导致全表扫描变慢。 二级索引的叶节点包含引用行的主键列，需要回表查询。	将聚集性数据保存在一起，加快数据访问速度。
覆盖索引	一个索引包含所有需要查询的字段的值	不能选择索引中不存在的列。不能在索引中执行LIKE %xxx操作。	只访问索引的查询

索引规约

评价一个索引是否适合某个查询（三星系统）：

• 索引将相关的记录放到一起
• 索引中的数据顺序和查找中的排列顺序一致
• 索引中的列包含了查询中需要的全部列

Topic	约定
使用独立的列	索引列不能是表达式的一部分，也不能是函数的参数。始终将索引列单独放在比较符号的一侧。
使用覆盖索引	优先考虑利用覆盖索引来进行查询操作，避免回表操作。考虑查询列仅包含索引列，而不是统一使用*获取所有列，尽管这便于简化开发工作。
使用前缀索引	选择合适的前缀，保证较高的选择性（使用COUNT(DISTINCT LEFT(列名, 索引长度))/COUNT(*)来计算）。很长的字符列，BOLB，TEXT，VARCHAR列，必须使用前缀索引。 技巧: 1. 反范式，为值长度过大的列，创建冗余列（计算该列简单哈希值[CRC32/FNV64]/截取选择性高的子串）需搭配触发器维护该列。 2. 使用IN来避免跳过索引列，而不触发索引；使用IN多个条件等值避免多个范围查询，尽可能将需要范围查询的列放到索引的后面，以便优化器使用尽可能多的索引列。注意组合数达到上千则需要特别小心。
使用多列索引	避免为每个列都创建独立的索引，或者按照错误的顺序创建的多列索引。不考虑排除和分组时，将选择性高的列放在前面通常很好，但存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。注意那些在WHERE中最频繁出现的列，考虑到使用的频率，在创建组合索引时，建议将其作为前缀列。
使用唯一索引	唯一特性的字段，即使是组合字段，也必须建成唯一索引。避免脏数据的产生。
使用冗余索引	必须避免重复索引（注意MySQL的唯一限制和主键限制都是通过索引实现的），尽量扩展已有索引而不是创建冗余索引（注意InnoDB二级索引已包含主键列，避免创建(列,...,ID)造成冗余），必要时考虑创建冗余索引，因为扩展已有索引会导致其变得太大，从而影响其他使用该索引的查询的性能。
模糊匹配	严禁左模糊或者全模糊匹配。如果需要请走搜索引擎。
排序	必须注意利用索引的有序性。
分页	业务限制翻页数量，或利用延迟关联（使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获取需要的行）或者子查询优化超多分页场景。
JOIN查询	超过三个表禁止关联。需要join的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。

索引的维护

目的	导致的问题	做法
找到并修复损坏的表	查询返回错误的结果或者莫须有的主键冲突等问题，严重时甚至还会导致数据库崩溃。	check table命令检查是否发生了表损坏，通常能够找出大多数的表和索引的错误。repair table命令用来修复损坏的表（注意有些存储引擎并不支持这两个命令）。 如果存储引擎不支持，可以使用一个不做任何操作的alter操作来重建表，例如修改表的存储引擎为当前的引擎（alter table innodb_table1 engine=innodb）。 InnoDB引擎表出现损坏，那一定发生了严重的错误，需要立即调查原因，而不是简单地修复解决，否则很可能还会不断地损坏。常见的类似错误是由于尝试使用rsync备份InnoDB导致的。可以设置innodb_force_recovery参数进入强制恢复模式来修复数据，还可以使用开源的数据恢复工具箱（InnoDB Data Recovery Toolkit）。
更新索引统计信息	如果存储引擎向优化器提供的扫描函数信息是不准确的数据，或者执行计划本身太复杂以致无法准确的获取各个阶段的匹配的行数，那么优化器会使用索引统计信息来估算扫描行数。MySQL优化器使用的是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。如果表没有统计信息，或者统计信息不准确，优化器就很可能做出错误的决定。	analyze table命令重新生成统计信息。每种存储引擎实现索引统计信息的方式不同，所以需要进行analyze table的频率也因不同的引擎而不同，每次运行的成本也不同： 1. Memory引擎根本不存储索引统计信息； 2. MyISAM将索引统计信息存储在磁盘中，analyze table需要进行一次全索引扫描来计算索引基数。在整个过程中需要锁表； 3. 直到MySQL5.5版本，InnoDB也不在磁盘存储索引统计信息，而是通过随机的索引访问进行评估并将其存储在内存中。 可以使用show index from命令来查看索引的基数（存储引擎估算索引列有多少个不同的取值）。
减少索引和数据的碎片	B-Tree索引可能会碎片化，这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。根据设计，B-Tree需要随机磁盘访问才能定位到叶子页，所以随机访问是不可避免的。然而，如果叶子页在物理分布上是顺序紧密的，那么查询的性能会更好。否则，对于范围查询、索引覆盖扫描等操作来说，速度可能会降低很多倍，对于索引覆盖扫描这一点更加明显。表的数据存储也可能碎片化。然而，数据存储的碎片化比索引更加复杂。有三种类型的数据碎片：行碎片、行间碎片、剩余空间碎片。对于MyISAM表，这三类碎片都可能发生。但InnoDB不会出现短小的行碎片，它会移动短小的行并重写到一个碎片中。	optimize table或者导出再导入的方式重新整理数据。这对多数存储引擎都是有效的。 对于一些储存引擎如MyISAM，可以通过排序算法重建索引的方式来消除碎片。 对于那些不支持optimize table的存储引擎，可以通过一个不做任何操作的alter table操作来重新建表，如：alter table table_name engine=<engine>。

参考: 《高性能MySQL》