文章内容
一、为什么要创建索引？
创建索引可以大大提高系统的性能（参考索引优点）。
二、索引的优缺点
1、索引的优点
优点如下：
- 1、可以大大提高表数据的检索（查询）速度。
- 2、创建唯一索引，可以保证数据库表中每一行数据的唯一性。
- 3、可以加速表和表之间的连接（在实现数据的参考完整性方面特别有意义）。
- 4、在使用order by、group by子句进行数据检索时，可以减少分组和排序的时间。
- 5、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
2、索引的缺点
缺点如下：
- 1、创建索引和维护索引要耗费时间，这种时间随着数据 量的增加而增加。
- 2、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
- 3、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
三、索引的本质
索引是帮助MySQL高效获取数据的排好序的数据结构。
1、常用的索引的数据结构
- 1、二叉树（左边节点值比根节点小，右边节点值比根节点大，并且左右子节点都是排序树）
- 2、红黑树（红黑树是一种自平衡二叉树，主要解决二叉搜索树在极端情况下退化成链表的情况，在数据插入的时候同时调整整个树，使其节点尽量均匀分布，保证平衡性，目的在于降低树的高度，提高查询效率）
- 3、Hash表（通过一定的算法计算数据的Hash值，然后得到数据的存放位置）
- 4、B-Tree（每个子节点可以拥有多于2个子节点，M路的B树最多可拥有M个子节点,叶节点具有相同的深度，叶节点的指针为空，所有索引元素不重复，节点中的数据索引从左到右递增排列）
- 5、B+Tree（对B-Tree的优化，叶子节点包含所有索引字段，将数据全部存放于叶子节点，并将叶子节点用指针进行链表链接）
2、mysql选择B+树，为什么选择B+树？
- 1、二叉树针对单向递增的那种数据（类似于单项链表），查找起来时效低（磁盘IO过多）。
- 2、hash表（平衡二叉树）弥补了二叉树的劣势；有时候对索引的key进行一次hash计算就可以定位出数据存储的位置（这个时候Hash索引要比B+ 树索引更高效），但是在大数据量的情况下树高还是相对太高（磁盘IO过多），而且仅能满足 “=”，“IN”，不支持范围查询，存在hash冲突问题。
- 3、B树弥补了上述两个数据结构劣势，但是一个同样内存大小情况下，非页节点存储的数据较少（因为每个非页节点上有data数据），这样针对千万级的大表来说树高还是较为高（磁盘IO过多）
- 4、B+树弥补了B树的缺陷（非页节点无数据），非叶节点存储的是数据的有序范围及下一个叶节点的地址，先load第一层非页节点到内存，查找对应的下一层非叶节点位置，再load下一层非叶节点到内存，查找叶节点的位置，最后找到叶节点位置及数据；（弥补了两个缺陷，1是内存利用问题，树高问题，2是叶子节点指针问题，B+树可以通过叶子节点的指针更快查找数据）
四、MyISAM存储引擎
MyISAM索引文件和数据文件是分离的(非聚集，它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据)。
1、磁盘存储文件
每个MyISAM在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分别为：frm(存储表定义)、MYD(MYData，存储数据)、MYI(MYIndex，存储索引)。
2、磁盘数据查找
如下图（假设表一共有三列，以col1为主键），可以看出MyISAM的索引文件叶子节点仅仅保存数据记录的地址。通过地址值找到磁盘文件中data数据。
五、InnoDB存储引擎
如下图：InnoDB索引实现(聚集)，表数据文件本身就是按B+Tree组织的一个索引结构文件，聚集索引-叶节点包含了完整的数据记录；其中主键索引叶子结点存储的是一行记录数据，二级索引叶子结点存储的是主键（搜索时，先按二级索引找到主键，再通过主键索引找到数据，可以保证一致性和节省存储空间）
1、磁盘存储文件
InnoDB在磁盘上存储成2个文件：.frm(存储表定义)、ibd(数据和索引文件)
2、为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
- 1、因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有）
- 2、如果没有指定主键，则MySQL会自动选择一个没有重复数据的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐藏字段作为主键，为了不让mysql自己处理，尽量自己建一个主键
- 3、索引是有序的，按从小到大排序，使用自增主键，比较的时候是比较的数值大小，相对于UUID等其他类型更容易比较大小。
- 4、当主键是非自增的那种情况下，在插入数据时，可能会往前插数据，比如存在id为6A和7A的记录，插入数据有可能会插入一个6B，这时为了维持B+Tree的特性，索引结构就有可能会分裂调整，十分低效，而使用自增字段作为主键，不会往中间插入数据。
六、联合索引（最左前缀原则）
Mysql建索引的时候按照联合索引的从左到右的顺序排好序，比如下面的例子，先按name排好序，查询sql条件中有name=‘’才能命中索引，因为索引是排好序的数据结构，排序就按照建索引的字段排序（按name，age，sex依次排序，当name相等时age是有序的，当name和age相等时，sex是有序的，三个字段都相等时主键是有序的；联合索引可以有联合主键索引，此类索引多个字段确定唯一，不可能字段列都相等；非联合主键索引类的联合索引，叶子结点存储的是主键）。
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。如下三个例子只有例子1会走索引。
例子1：
select * from `user` where name ='a' and age =30 and sex ='1';
例子2：
select * from `user` where age =30 and sex ='1';
例子3：
select * from `user` where sex ='1';
七、explain中的列
使用方式：
explain select * from user;
执行完explain后会有如下所示几列字段：
- 1. id列：id列值越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。
- 2. select_type表示查询语句是简单还是复杂的查询
- 3. table列：表示explain的一行正在访问哪个表
- 4. type列（关联类型或访问类型）：依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL，一般来说，得保证查询达到range级别，最好达到ref；
- 5. possible_keys列：这一列表示查询时可能用哪些索引来查。
- 6. key列：这一列表示mysql实际采用哪个索引来查询。
- 7. key_len列：这一列表示mysql在索引里使用的字节数。
- 8. ref列：这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：user.id）
- 9. rows列：这一列是mysql估计要读取并检测的行数(不是结果集里的行数)
- 10. Extra列：额外信息