一、MySQL架构
1.1 发展历程
MySQL源自MySQL AB公司的前身ISAM和mSQL项目,第一版于1996年发布,当时只支持SQL特性,没有事务支持。在200年,MySQL发布自己的源代码,及GPL许可协议,正式进入开源世界。2000年4月，MySQL已经组织了旧的存储引擎,它的名字是MyISAM。
2002年前后，InnoDB引擎出现,它被添加到MySQL 4.In 0,它于2005年发布。三年后,2008年,MySQL被Sun收购。翌年,索恩被Oracle收購。MySQL从那时起已移入Oracle,发展得更快,之后,基本上每两年或三年,一个新版本发布,发布于2018年,版本0
1.2 架构
MySQL的总体架构由三个部分组成:客户端层、服务器层和存储引擎层。如下图所示，客户输入请求,server 负责 sql的parse与执行; storage engine 去真正的做数据或索引的读取和写入。
每个客户端连接在mysql服务端生成一个线程(通过线程库管理内部),例如,一个选定的句子进入,mysql首先将查看查询缓存,以查看此选择的结果是否被缓存,否则,继续分析、优化和执行过程,否则将从缓存中获取结果集。下面的图表显示了mysql的具体实现。
1.3 存储引擎
MySQL主要由两个主要存储引擎组成:
MyISAM具有以下特点
- 表锁
- 不支持事务
- 不支持外键
- 如果索引文件和数据文件分开,索引文件只保存数据记录的地址(存储了叶节点的数据区域)
2、InnoDB
目前InnoDB是最常用的存储引擎,具有以下特点
- 行锁，间隙锁等
- 支持事务
- 支持外键
- 自InnoDB的表数据文件是(主)索引文件,索引的键是数据表的主键,树叶节点数据区域保存了完整的数据记录
除此之外，还有Memory ,Csv,Archive,Blackhole等内建引擎，以及PBXT，TokuDB等第三方引擎。平时用的最广泛的是InnoDB,因此本文就是基于此讨论。
二、索引分类
索引是一个帮助MySQL有效访问数据和加速查询的数据结构。InnoDB存储引擎目前支持三个类型的索引:B+树索引、Hash索引和全文本索引。
由于B+树的高度低,可以很好地利用局部原理和磁盘预读特性,它可以大大减少磁盘IO(每个节点可以存储多个记录,将节点大小设置为页长度,比如4k），因此B+树指数在实际生产中是最常用的。本文主要介绍了B+树的两种形式:主键索引和辅助索引。
2.1 主键索引
主键索引又称为聚集索引（Clustered Index），决定磁盘上的物理数据序列,它是由B+Tree组织的一个索引结构,树叶节点数据区域存储了完整的数据记录.因为InnoDB数据文件本身必须通过主键聚集,因此InnoDB请求表必须有一个主键,如果没有列被明确指定,并且没有列只能识别数据记录,在这种情况下,MySQL自动生成一个六字节长的隐式字段作为内部密钥。
如下面所示,MySQL使用主键构造树,并让节点存储与主键相符的行数据。
2.2 辅助索引
辅助索引即非聚集索引（Non-Clustered Index），它的数据域存储了相应的记录键的值,而不是地址。辅助索引的树，叶节储存了两个东部分支,一个是索引本身的价值,另一个是与主键相符的索引值。其组织结构如下(addtime构建了一个索引)
显而易见,辅助索引树有两个特点,首先,它的叶节点是加时,并按顺序排列,与此附加时间对应的第二个值是关键值。假设SQL命令一个辅助索引,一般情况下，将执行两个查询(除非发生索引覆盖),首先到帮助索引树获取主键的值,然后回到主键索引树,在叶节点中获取完整的数据记录行。
三、索引最佳实践
下面讨论了五种最佳做法,说明使用索引是否应该直接影响数据检索性能。
3.1最左前缀匹配
在创建联合索引时,inmysql遵循匹配最左前缀的原则,即最左优先，在检索数据时, 从组合索引的最左边开始匹配.创建联合索引(col1,col2,col3),它相当于构建三个索引: (col1), (col1, col2), (col1, col2, col3)。一个假设表中的综合指数
key IX_addid_classid_uptime（add_id, class_id, update_time）
如下给出了8个场景
场景描述 |
索引使用 |
举例 |
1全列匹配 |
Y | |
2最左前缀匹配 |
Y | |
查询条件使用索引列的准确匹配,但没有提供中间的条件 |
N(仅用于索引第一列 addid) | select * from stu where add_id=1 and update_time="2018-01-01"; |
4查询条件不指定索引第一列 |
N | select * from stu where class_id=2 and update_time="2018-01-01"; |
5与前缀字符串匹配字符串 |
Y | |
范围查询后序列字段6 |
N(仅用于索引第一列 addid) | select * from stu where add_id BETWEEN 10 and 12 order by class_id; |
7多值精确匹配 |
Y | |
8包含查询条件中的函数或表达式 |
N | select * from stu where add_id *2 = 12; |
还有五个类型的指数失败,通常需要在执行指数时给予额外的注意:
场景描述 |
1.为索引列执行操作,操作包括+,-,*,/,!(例如: |
2.隐藏转换导致索引失败 |
3. 如 " % _ " 在百分比前面. |
4. not in ,not exist |
5.查询数目是表格的大多数,超过30% |
尤其是隐式转换会导致索引无效,我们很容易忽略,例如有 `customer_id` varchar(20) NOT NULL
错误的姿势： select * from rental where customer_id=130;
正确的姿势：select * from rental where customer_id=‘130’;
3.2索引覆盖
覆盖索引（Covering Index）指一个查询语句的执行只需要从辅助索引中就可以得到查询记录，你不需要在表查询集索引中检索记录,它也可以称为指数覆盖的实施,因此,随机IO操作可以大大减少。例如,对组合索引(col1,col2,col3),如果有如下的sql: select col1,col2,col3,id from tb where col1=1 and col2=2。所以MySQL可以直接通过搜索索引获取数据,而无需回表，它可以减少对数据的访问量,同时减少了大量的随机性,可以有效地提高SQL性能,它是性能优化的重要手段.
验证SQL是否实现了索引覆盖,通过解释可以查看额外属性,如果用于使用索引的额外显示,它表明SQL已经实现索引覆盖。注意,只有当查询声明访问的列是索引的一部分时,索引才会覆盖，当然包括主键，因为主键也是辅助索引中.
3.3 Order By
有两种方法来生成有序的结果集:
- 通过扫描索引顺序取得的结果是自然顺序的
- 使用FileSort,对结果集进行排序的操作
好的sql要尽量避免FileSort的发生，利用索引进行排序操作是非常快的。如果索引本身不能覆盖所有需要查询的列，就不得不每扫描一条索引记录就回表查询一次对应的行，为保证索引排序，需注意下面几点：
索引涵盖了查询所需的所有列
索引中的列的顺序与顺序的顺序相同
命令项和查询的限制是相同的,必须满足最左前缀的要求。
3.4 Limit Offset
当需要分页操作时，它通常通过限度加偏移法实现,同时增列适当的《命令书》分项。一个常见的问题是当偏差非常大时,例如:LIMIT10,00,20或类似的,MySQL需要查询10020记录并返回20个记录,以前的10万条文章将被丢弃,无效查询特别大，资源浪费也带来业绩成本。为此，提供两种优化方案：
- 延迟关联
使用覆盖索引扫描(首先不是需要的列),然后进行相关查询并返回所有列,如下
- 两次查询
每次分页查询后返回上一次结果的主键id（记录好上次查询的位置），并基于此id配合limit pageSize进行范围查询，可以有效避免大offset导致的无效查询，案例如下
3.5选择度
索引的选择度（Selectivity），指未重复的索引值(也称为基数,Cardinality）与表记录数（TotalSize）的比值：Index Selectivity = Cardinality / TotalSize，选择性范围是(0, 1],选择性越高,指数的值就越大(唯一具有选择性1的指数)。如下，单纯用first_name字段索引的选择性很低（0.0042），将first_name和last_nam做一个联合索引，很明显,选择性是好的。
但是first_name和last_name加起来若长度很长，那有没有兼顾长度和选择性的办法？答案是肯定的，可以利用前缀索引的特性，指定索引参与字段的长度，如
Alter table employees add key (`first_name`,`last_name`(4)); 并且其选择性也比较好，为0.9007
四、总结
上述五个最佳做法只是索引的实践的一部分,下面总结并指出与索引有关的最佳做法:
(一)确保使用索引,尽可能使用综合索引
1条件中的高屏幕字段应该有索引
②条件字段禁止函数
条件字段3禁止隐式类型转换
4避免儿童查询和相关的儿童查询
(二)索引列的选择原则
1用高分级和高选择性列作为指标
②尽量使⽤字段⻓度⼩的列作为索引,使⽤数据类型简单的列（int 型，固定⻓度）
3尽可能扩大索引,选择 NOT NULL的列
4索引列不能参与计算,否则索引将失败
排序在MySQL中是一个痛苦
1避免文件排序
2大极化限度仍需要扫描大量数据,并且可以通过延迟关联或二次查询方案优化
3尽可能避免在数据库中分页。如果你不能避免分页,使用条件过滤结果可以同时收集多个页面,或者优化分页减少查询数目
在自己的程序中排序,尤其是大结果集
(4)索引不适合设置场景
表1小于一千行
2索引的选择性区分较小