Mysql索引规范及原理分析

1 Mysql存储引擎

MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎。

存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式。

MylSAM存储引擎，5.5版本之前的默认存储引擎

MylSAM拥有较高的插入、查询速度，但不支持事物，也不支持外键，但是访问速度快。可以用来存储日志记录等功能。由于目前不再使用，大家大概了解下即可。

lnnoDB存储引擎，5.5版本之后的默认存储引擎

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。是大家必须知晓的内容。后续的内容均默认使用InnoDB存储引擎。

2 什么是索引

在mysql中，索引是存储引擎用于快速找到记录的一种数据结构。

索引的本质就是通过不断的缩小想要获取数据的范围来筛选出最终想要的结果。类似我们查字典，每一个字相当于一条数据，索引相当于目录，可以根据拼音或者偏旁部首快速查询出页码，进而查询到对应的信息。

总结来说，索引对于提升系统性能有较大的帮助，需要进一步的了解它。

问题1:为什么SQL查询很慢?

解答:未创建索引。

问题2:已经添加了索引，为什么SQL仍然很慢?

解答:未创建正确索引。索引无效。mysql优化器选择其他索引。mysql优化器不走索引，选择全表扫描。

问题3:已经添加了索引，也明确了SQL使用该索引，为什么SQL依旧很慢?

解答:1.单表数据量过多。即使添加了索引，仍然性能不高。2.Mysql分页机制，比如大数据分页场景下，即使使用了索引，仍然慢响应。3.使用性别等区分度小的字段作为索引，无法提高性能。

使用索引的注意事项：

1.表必须设置主键，建议使用系统自增的id主键。

2.索引名全部使用小写英文字母，采用下划线进行分割。

3．普通索引按照"idx_字段名”的格式进行命名。比如idx_driver_id。

4．唯一索引按照"unique字段名"的格式进行命名。比如unique_driver_id。

5.一张表中的索引数量建议不超过7个。(索引过多，影响写入性能，可能存在优化器选择错误索引的情况，具体数量视场景而定)

6．根据具体业务场景合理创建联合索引,可以有效减少索引数量。比如联合索引(a,b,c)，相当于索引(a),(a,b),(a,b,c)，因为其满足索引的最左匹配原则。

7．使用联表查询时，join列的数据类型必须相同，并且均需要创建索引。10.不在区分度低的字段上建立索引。

8．合理使用覆盖索引，可以有效减少回表lO。

MySQL为什么最终要去选择B+Tree?

1. B+Tree是B TREE的变种，BTREE能解决的问题，B+TREE也能够解决（降低树的高度，增大节点存储数据量)

1. B+Tree扫库和扫表能力更强。如果我们要根据索引去进行数据表的扫描，对B TREE进行扫描，需要把整棵树遍历一遍，而B+TREE只需要遍历他的所有叶子节点即可（叶子节点之间有引用）。

2. B+TREE磁盘读写能力更强。他的根节点和支节点不保存数据区，所以根节点和支节点同样大小的情况下，保存的关键字要比B TREE要多。而叶子节点不保存子节点引用，能用于保存更多的关键字和数据。所以，B+TREE读写一次磁盘加载的关键字比B TREE更多。

3. B+Tree排序能力更强。上面的图中可以看出，B+Tree天然具有排序功能

4. B+Tree查询性能稳定。B+Tree数据只保存在叶子节点，每次查询数据，查询lo次数一定是稳定的。当然这个每个人的理解都不同，因为在BTREE如果根节点命中直接返回，确实效率更高。

B+树的数据结构是按照关键字进行比较的。

数值型:直接按照数值进行排序

字符型:按照每个字母的acsii值进行比较

(字符串也可以比较“大小”，有大小那就可以排序。 两个字符串自左向右逐个字符相比（按ASCIl值大小相比较），直到出现不同的字符或遇'\o'为止。)

3 索引最左匹配原则

当进行匹配的时候，会把字符串转换成ascll码，如abc变成97 、98、 99，然后从左往右一个字符一个字符进行对比。所以在sql查询中使用like %a时候索引会失效，因为%表示全匹配，如果已经全匹配就不需要索引，还不如直接全表扫描。

当关键字占用的空间越小，则每个节点保存的关键字个数就越多，每次加载进内存的关键字个数就越多，检索效率就越高。创建索引的关键字要尽可能占用空间小。

4 最少空间原则

主键是bigint，8bit;页号是4bit，一共12bit。一页总数:15K%12bit=1280条。

再假如每行数据大小为1Kb，每个叶子节点能存放数据就是15K%1K=15条。

一层的B+树存放数据是15

二层的B+树存放数据是1280*15

三层的B+树存放数据是1280乘以1280乘以15=2400万

因为考虑到磁盘IO性能问题，当超过3次时性能会急剧下降。如果每行数据只有250bit，单页数据可存放60条，三层B+树可存放接近1亿了，访问速度一样不会慢。

如果当前插入的数据是自增序列的话，只有右边的树形结构进行旋转变化，左侧并没有，因此Mysql主键建议用自增序列，不建议UUID自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。