更新于：2024-04-16 21:10
一、底层的数据结构
索引是排好序的数据结构；mysql的索引是文件，关于mysql底层所用的数据结构，有多种选择。分别是二叉树、红黑树、Hash、BTree、B+Tree选项，但是为什么底层只选了B+Tree结构。
1、二叉树
二叉树在顺序插入建树发时候会出现一边倒的情况发生，在使用二叉树索引查数据的时候是要对节点的数据进行I/O操作然后判断走向，这样的一边倒情况会导致使用索引和不使用索引需要磁盘的I/O次数没啥差异。
2、红黑树
红黑树虽然可以自动调整根节点两边子树的高度差避免二叉树的一边倒情况，但是每个节点最大存储两个子节点信息，数据量很大的时候树的深度也会变大，在动态调整高度差的时候很耗时间。
3、Hash
Hash是可以通过一次或多次的Hash公式计算便可以得到数据存储的位置，但是Hash只是在查询一个数据的时候是很快的，但是在做范围搜索的时候却不行，因为数据是按照Hash计算方式去分布的，数据不连续，范围搜索每个都要全部过一遍。
4、BTree
BTree在红黑树的基础上加了“度”的概念，让一个节点不在存放一个数据，而是多个数据；度的值越大存放的数据就越多，这样树的深度就越小，但是度不是越大越好；如果把所有数据都放在一个节点，那么我们读一个节点不会是一次I/O操作了而是（所有数据的存储量/磁盘块）次数。
按照磁盘的结构，操作系统对磁盘操作的最小单位是磁盘块(有附近相邻的几个磁盘扇区组成)，对磁盘块一次操作就是一次I/O操作，所以在做硬件底层优化的时候都是一个节点占用存储量是一个磁盘块，这么做的好处是在一个节点中的数据都是物理地址上连续的。当我们我们存储的单个数据比较大的时候，一个节点存放的数据个数就很少，这样树的深度也开始变大。
5、B+Tree
B+Tree是BTree的优化，这个数据结构在非叶子节点都存放索引值，指向叶子结点，而叶子节点则是存放数据的。因为索引值相比数据很小，这样我们的非叶子节点可以存放很多很多的数据索引值，换句话说就是变个法的让度变大。这样树的深度会很小（实际当中一般深度都在3-5就够用了），所需的I/O次数也会很少很少。而且叶子节点存放了一个磁盘块的数据后，因为是顺序存放，它会直接指向下一个磁盘块的物理地址，避免范围查询的时候回退到根节点重新向下查找，这也是减少I/O次数的优化方法。
二、常用的存储引擎
存储索引是在表级别的，一个数据库表在磁盘上存储的文件分别是表结构文件还有数据文件。表结构文件单独拿出来当作一个文件，由于存储方式不一样数据文件也会不太一样，常见的存储方式有MyIASM和InnoDB。
1、MyIASM
MyIASM是以前mysql老版本使用的数据文件存储方式，它是非聚集(文件不是放在一起)，它把存放数据的那部分分成了索引文件和数据文件两个文件（MYI 和 MYD）。索引底层使用的是B+Tree数据结构，MYI 文件中B+Tree的叶子节点存放的是指针，指向 MYD 中的数据，这里主键和非主键的索引结构都是相同的。
2、InnoDB
InnoDB是现在mysql默认的存储方式，它是聚集(文件整合一起)，它把索引文件和数据文件都放在一个文件中（idb），因为数据文件本身就是索引文件。该存储方式必须要有主键，并建议使用整形自增主键。在底层B+Tree的叶子节点中，存放的不再是文件指针，而是把数据库那行数据都放在叶子节点上，这是InnoDB的主键索引；非主键索引存的则不是整行的数据，而是索引值，是指向那行数据的主键的。
为什么必须要主键？ 因为整个表的数据文件本身就是按照B+Tree组织的一个索引结构文件；再细说一下，因为该数据结构的非叶子节点都是索引值，因为这些索引值就是自增行的主键，所以该存储方式必须要主键。
为什么用整形自增主键？ 第一因为要主键，而自增型保证了唯一性；第二是因为主键在搭建B+Tree结构的时候比较大小很快，比 uuid 这些字符类型的快的多；第三是在新增数据是顺着叶子节点的指针去向后面找位置插入数据，新增数据用id在非叶子节点找到它的范围后找到该范围第一个叶子节点，然后顺着指针找到相应位置插入。
为什么非主键索引叶子节点存主键索引值？ 这是保证整个索引文件里面一行数据库数据只有一份，这不仅减少文件大小还减少存储空间；另外在修改数据时，也只需要修改主键索引里面的数据即可，只要修改一次。
三、联合索引结构
把多个字段组合一起建立索引，例如(A, B, C)，这三个字段去建立联合索引，一般情况下 A、AB、AC、ABC 这四种情况下放在查询条件里面才会去使用联合索引(在AC组合中，实际只用到了A索引，C索引没有用到)，最左前缀匹配是以起点开始连续的才行，也就是 A、AB、ABC。
复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。
这篇文章 对联合索引结构讲解的比较详细，需要深入理解的可以观看阅读。
四、explain详解及索引优化
使用mysql提供的 explain 命令去执行查询语句，会得出一些信息需要分析，有 id列、select type列、table列、type列、possible_keys列、key列、key_len列、ref列、rows列、filtered列、Extra列。
1、id列
该列编号是 sql 语句中 select 的序列号，id 的顺序是按照 select 出现的顺序增长。mysql将 select 查询分为简单查询(SIMPLE)和复杂查询(PRIMARY)。复杂查询分为三类：简单子查询、派生表(from语句中的子查询)、union 查询。
2、select type列
表示对应行是简单还是复杂查询。
simple是简单查询，查询不包含子查询和union；
primary是复杂查询种最外层的select；
subquery是包含在select中的子查询(不在from子句中)；
derived是包含在from子句中的子查询，mysql会将结果放在一个临时表中，也叫派生表。
3、table列
表示 explain 的一行正在访问哪个表。
4、type列
表示关联类型或访问类型，依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL。
const，system：是mysql能对查询进行优化并将其转化成一个常量。在使用 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次且速度比较快。system是const的特例，表里只有一条元组匹配时为system。explain extended…….然后再加上show warnings。
eq_ref：当primary key 或 unique key 索引的所有部分被连接使用，索引要和某个值比较时最多只会返回一条符合条件的记录，这可能是在 const 之外最好的联接类型了。
ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
index：扫描全表索引，这通常比ALL快一些。（index是从索引中读取的，而all是从硬盘中读取）。
ALL：即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了数据优化的时候可能会把ALL类型的转变成为更优级别的。
5、possible_keys列
这一列显示查询可能使用哪些索引来查找。explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。如果该列是 NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。
6、key列
这一列表示实际查询中使用了什么索引。它显示mysql实际采用哪个索引来优化对该表的访问；如果没有使用索引，则该列是 NULL；如果想强制mysql使用或忽视 possible_keys 列中的索引，在查询中使用 force index、ignore index。
7、key_len列
这一列表示mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。 举例来说，film_actor 的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个 int 列组成，并且每个 int 是4字节。通过结果中的 key_len=4 可推断出查询使用了第一个列：film_id 列来执行索引查找。
8、ref列
这一列显示在 key 列记录的索引中，表查找值所用到的列或常量，常见的有 const(常量)，字段名(如 film.id)。
9、rows列
这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
10、Extra列
这一列展示的是额外信息：
Using index：用select去查询的列被索引覆盖，并且where筛选条件是索引的前导列，是性能高的表现。一般是使用了覆盖索引(索引包含了所有查询的字段)。对于innodb来说，如果是辅助索引性能会有不少提高。
Using where：用select要查询的列没有被索引覆盖，where筛选条件也不是索引的前导列。
Using where;Using index：用select查询的列被索引覆盖，并且where筛选条件是索引列之一但不是索引的前导列；表明不能用索引查找到符合条件的数据，但是可以用索引去查询符号条件的数据。
NULL：用select去查询的列没有被索引覆盖，并且where筛选条件也是索引的前导列；虽然用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引。
Using index condition：与Using where类似，查询的列不完全被索引覆盖，where条件中是一个前导列的范围。
Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
Using filesort：mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。
11、优化实践
1、全值匹配：在使用联合索引的时候尽量将联合索引的所有字段都用上。
2、最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
3、不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描。
4、当联合索引中有一列是范围条件，那么它右边的列不会用到索引。
5、尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句。
6、mysql在使用不等于（！=或者<>）的时候无法使用索引，会出现全表扫描。（大于可以）。
pass：mysql8.0版本上<>、<、>、<=、>=这样都可以使用索引但是索引变成了range级别。
7、is null，is not null 也无法使用索引。
8、like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作。
9、字符串不加单引号索引失效。
10、少用or、in,用它连接时很多情况下索引会失效。
假设 index(a, b, c)
| Where语句 | 索引是否被使用 |
|---|---|
| where a=3 | 是，使用到a |
| where a=3 and b=5 | 是，使用到a, b |
| where a=3 and b=5 and c=4 | 是，使用到a, b, c |
| where b=3 或 where b=3 and c=4 或 where c=4 | 否 |
| where a=3 and c=5 | 是，使用到了a, 但c不可以，b中间断了 |
| where a=3 and b>4 and c=5 | 是，使用到a和b，但c不能用在范围之后，b断了 |
| where a=3 and b like ‘kk%’ and c=4 | 是，使用到了a, b, c |
| where a=3 and b like ‘%kk’ and c=4 | 是，只用到a |
| where a=3 and b like ‘%kk%’ and c=4 | 是，只用到a |
| where a=3 and b like ‘k%kk%’ and c=4 | 是，使用到了a, b, c |
全值匹配我最爱，最左前缀牢记心；
带头大哥不能倒，中间兄弟断狗带；
索引列上少计算，范围之后全完蛋；
覆盖索引不写星，Like百分写最右；
不等空值还有or，气的索引会失效。
五、mysql锁与事务隔离级别
1、锁的分类
从性能上分为乐观锁(用版本对比来实现)和悲观锁。
从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)。读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响；写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
从对数据操作的粒度分，分为表锁和行锁。表锁（偏读） 偏向 MyISAM 存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。读锁会阻塞其他 session 的写不会阻塞读，而写锁则会把其他session的读和写都阻塞。行锁（偏写） 偏向 InnoDB 存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB 与 MYISAM 的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。
表锁是锁整个表，确实不会出现死锁，锁粒度自然也大；行锁是通过给索引上的索引项加锁来实现的，虽然会出现死锁但是比锁整个表效率要高，锁粒度也小。所以只有通过索引条件检索数据，InnoDB 才使用行级锁，不然 InnoDB 将使用表锁。
● 在不通过索引条件查询的时候，InnoDB 使用的是表锁，而不是行锁。
● 由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以即使是访问不同行的记录，如果使用了相同的索引键，也是会出现锁冲突的。
● 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
● 即便在条件中使用了索引字段，但具体是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
2、事务
行锁支持事务，事务的属性有四种，ACID。原子性，一致性，独立性，持久性。在做并发事务的时候，会出现更新丢失、脏读、不可重读以及幻读的一些问题。
更新丢失：A和B事务都选择同一行数据进行更新处理，由于两个不知道相互的存在，会让先执行的事务更新的数据被后执行的事务的更新覆盖了。
脏读：事务A读到了事务B已经修改但还没有提交的数据，并按照这个数据去做了其他操作，如果B事务回滚，则A读取的数据无效，不符合一致性要求。
不可重读：事务A先读取了事务B的数据为基础去操作，后来又去读取，但是B在这之前已经提交了修改的数据，A两次读取的结果不同，不符合独立性。
幻读：事务A用select查询id>1 and id <20的数据发现有了10条信息，然后事务B新增了id=12的数据，然后A再用相同条件去查询还是10条，A去新增id=12数据会报错说数据存在，查不到但数据库中有这个数据，就像是出现了幻觉似的。只有A去修改id=12的其他列数据之后，再用相同查询条件才能看到id=12的数据。（MVCC搞得鬼）
幻读场景：
1、银行A开启了一个事务窗口，查询当前系统中有没有 mj 用户，发现没有；
2、银行B也开启了一个事务窗口，查询当前系统中也没有 mj 用户；
3、银行A先创建 mj 用户并且提交，由于可重复读取（保证前后两次读的一样），银行B在一次事务中必须保证查询的数据一致性，因此B第二次查还是查不到；
4、结果银行B窗口认为 mj 没有被注册然后去注册了 mj 用户，再创建提交 mj 用户结果发现系统提示 mj 用户已经被注册，但是在本次事务中就是查不到 mj，就好像出现幻觉一样。
3、事务隔离级别
“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交(Read uncommitted) | 可能 | 可能 | 可能 |
| 读已提交(Read committed) | 不可能 | 可能 | 可能 |
| 可重复读(Repeatable read) | 不可能 | 不可能 | 可能 |
| 可串行化(Serializable) | 不可能 | 不可能 | 不可能 |
可重复读的隔离级别：它解决不可重复读是使用了MVCC机制，就是版本读取。select 操作不会更新版本号，是快照读（历史版本）；只有 insert、update、delete 会更新版本号，该事务才能读当前版本，这也会导致出现幻读。mysql的MVCC并非真正的MVCC，它只是借用MVCC的名号实现了读的非阻塞而已，就是其他事务新增数据时，读仍能可以执行不会排他锁阻塞，不被阻塞是因为读了以前的版本不是最新版本。
可串行化虽然解决了三个问题，但是它是串行执行不能使用并发，性能差很少使用，一般都是使用可重复读的隔离级别。但是该隔离级别给我们提供了一些情况下解决幻读的问题，是间隙锁。就是在A事务做修改操作的时候用范围，update account set name = 'lilei' where id > 10 and id <=20;这样其他session再去新增id为10~20范围的数据时是无法执行的，必须等到事务A提交才可以。
4、行锁分析
通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况，show status like'innodb_row_lock%';。
对各个状态量的说明如下：
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_waits:系统启动后到现在总共等待的次数
最后三个状态量比较重要，尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。
5、死锁
查看近期死锁日志信息，show engine innodb status\G;，大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁。
6、优化建议
尽可能低级别事务隔离；
合理设计索引，尽量缩小锁的范围；
尽可能减少检索条件，避免间隙锁；
尽量控制事务大小，减少锁定资源量和时间长度；
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
