MyISAM存储引擎
MyISAM是 MySQL 5.5 版本之前的默认存储引擎，MySQL中很多系统表也还
是使用该存储引擎，系统临时表也会用到 MyISAM存储引擎；
特点：
a）select count(*) from table 无需进行数据的扫描
b）数据（MYD）和索引（MYI）分开存储
c）表级锁
d）不支持事务
InnoDB 存储引擎
InnoDB是 MySQL 5.5及以后版本的默认存储引擎；
1、支持事务 ACID 特性；
2、支持行级锁；
3、聚集索引（主键索引）方式进行数据存储；
4、支持外键关系保证数据完整性；（基本上不用）
索引类型
image.png
1.普通索引Normal
非唯一索引的,一般设置的索引
2.唯一索引Unique
表示唯一的，不允许重复的索引，如果该字段信息不能重复，例如注册手机号用
作索引时，可设置为 unique；
Primary Key 是拥有自动定义的 Unique 约束，每个表中可以有多个Unique 约
束，但是只能有一个 Primary Key 约束；
3.全文索引Full Text
表示全文收索，在检索长文本的时候效果较好，比如搜索一篇文章，在比较短的
文本建议使用普通的 Index即可；
4.空间索引 SPATIAL
空间索引是对地理空间位置数据类型的字段建立的索引，MYSQL 中的地理空间
位置数据类型有 4种， 分别是 GEOMETRY、 POINT、 LINESTRING、 POLYGON；
二叉树
算法动画演示:
https://www.cs.usfca.edu/~galles/visualization/BST.html
image.png
特点：
左子树的键值小于根的键值，右子树的键值大于根的键值；
对该二叉树的节点进行查找发现深度为1 的节点的查找次数为 1， 深度为 2 的查
找次数为 2，深度为 3的查找次数为 3，深度为 n 的节点的查找次数为 n，因此
查找时间复杂度依赖于节点深度，如果节点很深，则查找效率降低；
如果根节点比较大或比较小,很容易造成二叉树的不平衡,降低查询速率
为了提高二叉查找树的的查找效率，人们引入了一种新的数据结构：平衡二叉树
（也叫 AVL 树：1962 年 G. M. Adelson-Velsky 和 E. M. Landis 两个人提出
来的）
平衡二叉树
alanced binary search trees（AVL Trees ）
平衡二叉查找树（AVL树）在满足二叉查找树的条件下，还需满足任何节点的两
个子树的高度最大差为 1，所以它呈现出是一种左右平衡的状态；
https://www.cs.usfca.edu/~galles/visualization/AVLtree.html
当我们向平衡二叉树（AVL Tree）插入新的节点（或者删除新的节点），有可能
打破它原有的平衡，那么它会通过旋转使其恢复平衡；
当插入新节点，失去平衡的二叉树可以概括为四种状态：LL（左左-为右旋）、RR（右
右-为左旋）、LR（左右-左旋右旋）、RL（右旋左旋）：
image.png
LL： LeftLeft，也称“左左”，插入或删除一个节点后，根节点的左孩子（Left
Child）的左孩子（Left Child）还有非空节点，导致根节点的左子树高度比右子
树高度高 2，AVL树失去平衡；
RR：RightRight， 也称 “右右” ， 插入或删除一个节点后， 根节点的右孩子 （Right
Child）的右孩子（Right Child）还有非空节点，导致根节点的右子树高度比左
子树高度高 2，AVL树失去平衡；
LR： LeftRight，也称“左右”，插入或删除一个节点后，根节点的左孩子（Left
Child）的右孩子（Right Child）还有非空节点，导致根节点的左子树高度比右
子树高度高 2，AVL树失去平衡；
RL：RightLeft，也称“右左”，插入或删除一个节点后，根节点的右孩子（Right
Child）的左孩子（Left Child）还有非空节点，导致根节点的右子树高度比左子
树高度高 2，AVL树失去平衡；
平衡因子：将二叉树上节点的左子树高度减去右子树高度的值称为该节点的平衡因子BF(Balance Factor)。
平衡二叉查找树（AVL Tree）有什么不足？
数据所处的（高）深度决定着他的 IO 操作次数，数据量越大，树的高度会很高，
导致 IO 操作次数大、耗时大；数据量比较大的时候，树的高度依然很高；
平衡多路查找树(B-Tree)
动画演示
https://www.cs.usfca.edu/~galles/visualization/BTree.html
注意这里的B-Tree，并不是 B 减 Tree，- 只是一个连接符号，人们经常这么写
而已，实际上是 B-Tree就是指 B Tree（B树）；
image.png
Degree 表示路数,表示一个节点最多可以放(路数-1)个数据
image.png
B-Tree 相对于 AVL Tree 缩减了
节点个数， 使每次磁盘 I/O 取到内存的数据都发挥了作用， 从而提高了查询效率；
改进版平衡多路查找树(B+Tree)
https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
image.png
image.png
B-Tree 与 B+Tree有什么区别？
B+Tree是基于 B-Tree的，大部分数据结构相同，但也有一些区别：
1.B+Tree非叶节点不保存数据信息,只保存关键字和子节点的引用,所以每一个叶子节点可以存储更多的关键字,使得整体结构更加的矮,加快查询速度
2.B+Tree关键字数据保存在叶子节点中
3.B+tree叶子节点是顺序排序的,并且相邻节点具有顺序引用关系
4.B+tree由于非叶子节点不存储数据,那么每个节点就可以存储更多的元素,树的层级更少,所以查询数据更快,所有数据都存在叶子节点,所以每次查找的此数都相同,查询速度更稳定.
B+Tree优势
1、 Innodb存储引擎是以页为单位存储数据， 默认一个页的大小是 16kb （show
variables like 'innodb_page_size'），一般表的主键类型为 int（占 4 个字节）
或 BigInteger（占8 个字节），指针类型也一般为 4 或 8 个字节，如果我们都
取最大的 8 个字节，那么一页（B+Tree 中的一个节点）中大概存储
16kb/(8byte+8byte)=1024个键值，也就是说一个高度为 3 的B+Tree索引可
以存储1025 * 1025 * 1028 ~ 10亿条记录；
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都
在 2~4层，它是一个又矮又胖的数据结构；
2、所有查询都要查找到叶子节点，查询性能稳定；
3、所有叶子节点形成有序链表，便于范围查询；
MyISAM存储引擎 B+Tree索引的实现
MyISAM 索引文件和数据文件是分离的， 索引文件仅保存记录所在页的指针 （物
理位置），通过这些地址来读取页，进而读取被索引的行；
image.png
非聚集索引
像上面的 MyISAM 的索引方式，不管是主键索引（Primary Key）和辅助索引
（Secondary Key）数据与索引是分开的，不在一起，我们称为是“非聚集的”，
这种索引我们称为“非聚集索引
聚集索引
InnoDB索引文件和数据文件是存放在一起的，我们称为聚集索引，但是这里要
注意 InnoDB存储引擎所谓“聚集”是指主键索引和数据行紧凑地存储在一起，
这种情况下才是聚集索引，如果InnoDB 不是主键索引，则索引的叶子节点存储
的不是索引的数据，而是该索引行主键的值，那么此时还是非聚集索引；
InnoDB的数据文件本身要按主键聚集，所以 InnoDB要求表必须有主键
（MyISAM可以没有），如果 InnoDB 没有显式指定主键，则 MySQL系统会
自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则
MySQL自动为 InnoDB表生成一个隐含字段作为主键，这个字段长度为6 个字
节，类型为长整形，而且是自动增长。
InnoDB 也是采用 B+Tree 数据结构来实现索引，而与 MyISAM 不同的是
InnoDB索引文件和数据文件是存放在一起的，也就是叶节点包含了完整的数据
记录，通过索引直接就找到了数据；
image.png
InnoDB的辅助索引（Secondary Key）data 域存储相应记录主键的值而不是
当前索引键的值，也不是数据地址，换句话说，InnoDB 的所有辅助索引都引用主键作为 data 域，所以对于辅助索引，还需要主键值来回表查询到一条完整记
录，因此按辅助索引检索实际上进行了二次查询，效率肯定是没有按照主键检索
高的；
image.png
单列索引:指由一个字段组成的索引,它是一种特殊的联合索引.
联合索引:联合索引又称复合索引,是指由多个字段(字段是有顺序的)组成的索引
联合索引叶子节点没有存储数据，节省磁盘空间，其数据结构如下
image.png
索引键index(col1, col2, col3)如何排序？
先根据第一个字段col1排序，然后根据第二个字段col2排序，如果前两个一样，则根据第三个字段col3排序；
B+Tree索引适用于全键值匹配查询，键值范围匹配查询，键值前缀匹配查询；
其中键值前缀查询只适用于根据键值的最左前缀查询；
联合索引的最左前缀原理
1、全值匹配（全列匹配、全键值匹配）
联合索引中的每一个索引字段都是where查询的条件；
当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配，每一列都能精确匹配）时，索引可以被用到；
注意，理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引，例如我们将where中的条件顺序颠倒，也用到了索引，效果一样；
2、最左前缀匹配
这种情况指的是只使用索引的第一列进行匹配，如果使用索引的第二列查询，无法命中索引；
匹配最左列前缀字符串也可以匹配到索引(如果%放在前面不能匹配索引,放在后面,有可能会)
注：这里匹配越精确，效率也越高，如果匹配 的前缀 精度过低，mysql查询优化器可能会进行全表扫描
如果查询中有某个列的范围查询，则右边所有列都无法使用索引查找，比如 where nick = ‘cat’and phone like ‘13720010%’ and create_time = ‘2020-02-09’，该查询只能使用索引的前两列，优化的话，如果like的结果较少，可以用in；
假设phone 只有两个：13720000000 13720000001，或者只有10几个；
where nick = ‘cat’and phone in (13720000000, 13720000001) and create_time = ‘2020-02-09’;（全键值匹配的查询，更精准的定位，读取的数据更少，效率更好）
匹配范围值
select * from users where nick in ('cat', 'cat10');
select * from users where phone in ('13720010000', '13700040000');
匹配索引键的第一列范围值（必须是最左前缀），只能命中第一列的范围，范围列后面的列无法用到索引；
范围包括 >、<、！=、in、is null、between ... and 等
索引失效:
1.查询条件中有函数或表达式,where中索引列有运算
如果查询条件中有函数或表达式，则MySQL不会为该列使用索引；
explain select * from users where id + 1 = 2;
2.联合索引 最左匹配 没有 第一列字段
3.or 必须所有条件字段都是单索引字段,否则索引失效
4.like 以"%"开头
5.where中索引列使用了函数;
6.如果mysql觉得全表扫描更快时（数据少）;
7.存在索引列的数据类型隐形转换，则用不上索引，比如列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
什么时候没必要用索引
1.唯一性差;
2.频繁更新的字段不用（更新索引消耗）;
3.where中不用的字段;
4.where 子句里对索引列使用不等于（<>），使用索引效果一般
条件排序:
①MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
②order by满足两种情况会使用Using index。
1.order by语句使用索引最左前列。
2.使用where子句与order by子句条件列组合满足索引最左前列。
③尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最佳左前缀法则。
④如果order by的条件不在索引列上，就会产生Using filesort。
1.filesort有两种排序算法：双路排序和单路排序。
双路排序：在MySQL4.1之前使用双路排序，就是两次磁盘扫描，得到最终数据。读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。即从磁盘读取排序字段，在buffer进行排序，再从磁盘取其他字段。
如果使用双路排序，取一批数据要对磁盘进行两次扫描，众所周知，I/O操作是很耗时的，因此在MySQL4.1以后，出现了改进的算法：单路排序。
单路排序：从磁盘中查询所需的列，按照order by列在buffer中对它们进行排序，然后扫描排序后的列表进行输出。它的效率更高一些，避免了第二次读取数据，并且把随机I/O变成了顺序I/O，但是会使用更多的空间，因为它把每一行都保存在内存中了。
2.单路排序出现的问题。
当读取数据超过sort_buffer的容量时，就会导致多次读取数据，并创建临时表，最后多路合并，产生多次I/O，反而增加其I/O运算。
解决方式：
a.增加sort_buffer_size参数的设置。
b.增大max_length_for_sort_data参数的设置。
⑤提升order by速度的方式：
1.在使用order by时，不要用select *，只查询所需的字段。
因为当查询字段过多时，会导致sort_buffer不够，从而使用多路排序或进行多次I/O操作。
2.尝试提高sort_buffer_size。
3.尝试提高max_length_for_sort_data。
分页limit查询
SELECT a.* FROM users where nick like 'cat52%' LIMIT 3000000, 15；
以上语句的优化方式如下:
SELECT a.* FROM users a, (select id from users where nick like 'cat52%' LIMIT 3000000,15) b where a.id = b.id
索引离散性
既然索引可以加快查询速度，那么是不是只要是查询语句条件都建上索引？
答案是否定的，索引虽然加快了查询速度，但索引也有代价：
索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好；
表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描即可，一般记录在2000条以上可以酌情考虑建立索引；
1)列的离散性
公式：count（distinct col）/ count（col）
比值越大离散性越好，离散性越好则选择性越好，
索引的离散性较低不建议建索引，离散性较低也就是选择性较低（B+Tree要走很多个分叉去找数据），所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：
Index Selectivity = Cardinality / #T
选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的；
如果列的离散性很差，一般创建索引的价值也不大；
可以采用联合索引提高列的离散性；
前缀索引
ALTER TABLE employees.employees ADD INDEX idx_nick_phone (nick, phone(4));
用前缀索引兼顾了索引大小和查询速度，但其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于覆盖索引Covering index（即当索引本身包含查询所需全部数据时，不再回表访问数据文件本身）；
覆盖索引:
如果一个索引包含(或者说覆盖)所有需要查询的字段的值,就叫做覆盖索引
创建联合索引原则
1，经常用的列优先 【最左匹配原则】
2，选择性（离散性）高的列优先【离散性高原则】
3，宽度小的列优先【最少空间原则】 varchr 3 nchar（15）
InnoDB的主键选择
在使用InnoDB存储引擎时，请永远使用一个与业务无关的自增字段作为主键；
经常也有人采用像手机号、身份证号、uuid这种唯一字段作为主键；
从数据库索引优化角度看，使用InnoDB引擎而不使用自增主键绝对不是一个好主意；
InnoDB使用聚集索引，数据记录本身被存于主键索引（一颗B+Tree）的叶子节点上，这就要求同一个叶子节点内的各条数据记录按主键顺序存放，当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点位置，如果使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页；
这样就会形成一个紧凑的索引结构，近似顺序填满，每次插入也不需要移动已有数据，效率较高，索引维护开销小；
如果使用非自增主键（如手机号、身份证号等），每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：
image.png
此时MySQL不得不为了将新记录插到合适位置而移动数据，增加了开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不紧凑的索引结构；
所以尽量在InnoDB上采用自增字段做主键；
Hash索引 （不常用）
哈希索引是采用哈希算法，把键值换算成hash值，比如做如下的查询：
select * from table where Col2 = 89;
image.png
首先将89进行hash运算得到文件指针index，然后根据文件指针index映射磁盘存储引用，这样查找数据直接找到磁盘数据位置（即一次hash运算就找到数据所在位置），查找数据非常快，比B+Tree效率高，但是hash 非常大的不足：即不能排序、不能范围查找，比如我们查找Col2>34的数据，它需要给每个范围值进行hash运算然后找到数据所在磁盘位置，效率不如B+Tree，B+Tree索引是排好序的，在实际开发中经常有范围查询，所以大部分情况下都是采用B+Tree索引；
Hash这种索引结构只能针对 字段名=目标值 的场景使用；
不适合模糊查询（like）、范围查询 等场景；
索引规约
- 【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，
即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。 - 【强制】超过三个表禁止 join。需要 join的字段，数据类型必须绝对一致；多表关联查询
时，保证被关联的字段需要有索引。
说明：即使双表 join 也要注意表索引、SQL 性能。 - 【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据
实际文本区分度决定索引长度即可。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达
90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。 - 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。 - 【推荐】如果有 order by的场景，请注意利用索引的有序性。order by 最后的字段是组合
索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
正例：where a=? and b=? order by c; 索引：a_b_c
反例：索引如果存在范围查询，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引 a_b 无
法排序。 - 【推荐】利用覆盖索引来进行查询操作，避免回表。
说明：如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，这
个目录就是起到覆盖索引的作用。
正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效
果，用 explain 的结果，extra 列会出现：using index。 - 【推荐】利用延迟关联或者子查询优化超多分页场景。
说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当
offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL
改写。
正例：先快速定位需要获取的 id 段，然后再关联：
SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id - 【推荐】建组合索引的时候，区分度最高的在最左边。
正例：如果 where a=? and b=? ，如果 a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。
说明：存在非等号和等号混合时，在建索引时，请把等号条件的列前置。如：where c>? and d=? 那么
即使 c 的区分度更高，也必须把d 放在索引的最前列，即索引 idx_d_c。 - 【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。
- 【参考】创建索引时避免有如下极端误解：
1） 宁滥勿缺。认为一个查询就需要建一个索引。
2） 宁缺勿滥。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。
3） 抵制惟一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。
网友评论