MySQL性能调优

一、了解MySQL底层数据结构和算法

首先需要了解索引的作用

索引是帮助MySQL高效获取数据的排好序的数据结构。
对于数据来说，是存在本地磁盘的不连续的空间里的，而主键对应的位置是磁盘的索引地址（这个索引指的是磁盘的哪个扇区的哪块地址）

而常见的数据结构有

• 二叉树
• 红黑树
• Hash表
• B Trees
  可以使用一个网站来在线查看以上这些数据结构存储的过程

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

PS:此处可能会有一道面试题

为什么MySQL存储的时候使用B-Tree而不使用其他的数据结构来进行存储。

此处先注意，索引是排好序的。而且MySQL存储使用的并不是B Trees而是B Trees的变种B+Trees并且做了一定的优化进行存储，下边会说到！！

1、为什么不使用二叉树(Binary Search Tree)进行存储

其实对于DBA来说，通常建议表的主键是一个自增的序列，那么根据上边的图可以看到，对于二叉树进行有序插入的话其结果相当于一个单向链表

• 每次新增一条数据的时候，都会从头开始去判断要插入的数据与该节点数据大小，假如表中已经有了上百万条数据，插入一条新数据的时候，需要扫描整个表的数据
• 当查找表中的数据的时候，也会扫描整个表的数据
  根据以上的结果来看无论是对二叉树进行CRUD的操作都会对二叉树进行整个的扫描，比较浪费磁盘的IO

2、为什么不使用红黑树(Red/Black Tree)进行存储

由图可以看到，其实红黑树在插入数据的时候，有点类似二叉树，但是和二叉树的区别在于：插入数据的时候，如果左右两边的某个节点的数据过多，会进行一次平衡，所以有的人也习惯称红黑树为平衡二叉树。
对于上边的二叉树而言，红黑树的好处在于做了一次平衡，可以看一下两张图。
假如都是插入了13条从1开始的递增数据

• 在插入的过程中，二叉树需要扫描整个数据，而因为红黑树做了一次节点左右平衡，扫描的次数要比二叉树少一半；
• 在查询的过程中，比如查询13的数据，二叉树需要比较13次才会找到结果，而红黑树只需要比较5次就可以找到结果了。
  因为红黑树会平衡左右节点的数据量，但是会面临一个问题，假如数据量达到上万条或者上百万条，树的高度就会增加，可能会变成几十甚至上百，也就说数据量大的时候，需要查找一条数据可能需要几十甚至上百次的磁盘IO才会找到对应的数据。效率也会很低。

其实相对于二叉树而言，红黑树已经节约了大量的磁盘IO了，弊端就是随着数据量的增大，树的高度会变高，只要解决了树高度的问题，也就迎刃而解了。比如将树的高度限制在3或者4，那么无论数据量有多少，最多走3次或者4次的磁盘IO就会找到对应的数据。

3、为什么不推荐使用Hash进行存储

对于Hash存储来说，在保存数据时，会将key值进行一次hash运算，然后保存到hash数组中，如果两个key值进行hash运算的结果一致，会继续在数组的该位置后继续追加元素，最终会形成一个单向链表。也就是下边这张图，不推荐的原因是，假如查找key=Alice的值，先对key进行hash运算，假设结果是2，会从hash数组中，找到下标2的位置，然后从链表中比较值是否和Alice一致，一致的话根据对应位置存储的磁盘索引地址从磁盘中查找对应的data数据，只需要一次IO就可以找到了，但是如果要找key>Alice的值，就没办法了，因为Hash存储的数据并没有大小的概念，也就是走不了索引，那么就会走全表扫描！而B+Trees会很好的解决这种问题，下边会说到

对于Hash结构来说有这些特点

• 对索引的key进行一次hash会计算就可以定位出数据存储的位置
• 很多时候Hash索引要比B+Trees索引更高效
• 仅能满足"=","in",并不支持范围查询
• 容易产生hash冲突的问题

4、为什么使用B+Trees进行存储

注意：我此处说的是B+Trees的，而不是B Trees。B Trees是咱们常说的B树，而B+Trees是B+树。

4.1、B Trees结构

我在红黑树上边做了一个调整，拿上边的'0004'节点来举例子吧，数据是存储在磁盘上的，也就是会在磁盘上开辟一个空间用来存储0004这条数据，那假如我当初在开辟存储'0004'这条数据空间的时候，我开辟大一点，也就是下边的这张图，那也就意味着，这个位置可以用来存储更多的数据了，那也会变成上边的图片'0004'这个位置我画红色框的地方，这块可以存储多个数据的节点一般称为数据叶，而这个数据叶里边的每条数据，又可以指向磁盘中的其他的位置，也就是我上边图的'0008'的位置。
通过上边可以看到，当节点横向存储的数据越多，存储相同数据量的情况下，比如一千万条，树的高度会变低

其实对于B Trees来说，存储结构就是下边的这个图形，存储的时候是按照k-v的模式来进行存储数据的，注意浅绿色的15、50、69是磁盘索引的位置，下边的浅橙色data可能是数据，也可能是磁盘其他索引位置的下标。
而且B Trees有这些特点

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列
  
  但是MySQL不是用B Trees进行存储的。因为每次在读取值的时候，会将这个数据叶整条读到内存中，因为带有数据，也会降低磁盘的IO性能，而且当我查询一条比'43'数据大的数据时，需要重新读取非叶子节点来进行判断。

4.2、B+Trees结构

先看一下B+树的特点：

• 非叶子节点不存储data，只存储冗余的索引，这样就可以存储更多的索引
• 叶子节点包含所有索引字段
• 所有节点的数据索引从左到右递增排列
• 叶子节点用指针连接，提高区间访问的性能
  B+Trees是由B Trees演变而来的，因为B树的非叶子节点，可以存储数据，而根据上图可以看到B+树所有的非叶子节点不存储data了，那也就意味着所有的data数据都存储在了叶子节点，而冗余索引指的是将每一个叶子节点（数据叶）的第一个索引拿出来去维护这棵B+树。而且叶子节点有指针指向下一个叶子节点的位置，那也就意味着不需要再去重新去扫描非叶子节点就可以找到下一条数据。
  而MySQL使用的是优化后的B+Trees结构，可以看下面这张图，叶子结点的指针变成了双向指针，原来是一条单向指针，当我找比'18'数据大的数据时，可以通过指针直接找到下一个数据叶，要是去找比'20'小的数据，就需要重新读取非叶子节点到RAM使用折半或者其他算法来进行判断了。而优化后的去找比'20'小的数据时，可以直接通过指针找上一个叶子节点就可以了，不需要重新读取和比较非叶子节点。大大节省了磁盘的IO次数。
  
  上边在说红黑树的时候，提到过，当数据量变大的时候，树的高度会不可控，而B+Trees就解决了这个问题，可以通过下边的这个SQL来查询一下B+Trees的每个节点可以存储多大的数据，结果大概是16K，当我读取一个数据叶的时候也就会触发一次磁盘IO，但是因为比较的时候是在RAM中进行比较的，而在RAM中比较的时间比一次磁盘IO的时间少的多。

-- 查询innodb 数据页的数据所占的数据大小
SHOW GLOBAL STATUS like 'Innodb_page_size';

PS：这个大小不建议修改，这是MySQL官方经过一系列计算得出的最佳大小。
这里可以结算一下如果主键使用bigint类型，可以存储多少条数据
非叶子节点存储的是冗余的索引，假设使用bigint类型，一个占8字节，后边的空白位置在MySQL底层占用6个字节。

那也就意味着一个数据叶，可以存储16384÷(8+6)≈1170个索引数据，而每一个空白位置的索引，又指向其他的数据叶，也就意味着所有非叶子节点的数据叶可以存储大约1170X1170=1368900个索引，因为叶子节点的data可能存储的是数据，也可能是其他位置的索引下标，这个和存储引擎有关系，假设，每条data占用了1kb，那一个数据叶大概可以存储16条数据，那整体计算一下大约可以存储1368900X16=21902400条数据。
两千多万条数据，树的高度为3，也就是从两千多万条数据中，查找一条数据，只需要走3次的磁盘IO就可以找到了。而且，MySQL真正在存储的过程中，可能把根节点放在了常驻内存中，也就是可能只需要2次的磁盘IO就可以找到具体的数据了。
其实影响MySQL的查询速度的因素就是树的高度，假如使用B树来存储的话，因为B树的非叶子节点也携带着data，假设data也是1kb，那也就是每个节点只能存16条数据，想一下16的n次幂=21902400，n要远远大于B+树的高度3。而B+树的高度取决于非叶子节点可以存放的索引数量的多少，存储的越多，树的高度会越低，查询效率会越快。
当然上边这个只是帮助理解来举例子的，实际存储的数据量要比两千多万条数据大很多。假如继续按照上边的例子，如果数据量超过了两千多万条，一般都会去考虑分库和分表了，最差顶多是给树增加一个高度。
上边在4节遗留了一个问题，B+Trees适合范围查找，因为B+Trees的叶子节点的值必然是从左往右递增的，而且叶子节点之间存在一个双向链表用来指向前一个和后一个节点，所以在进行范围查找的时候，比如查询id>20的数据，先根据索引，找到id为20的叶子节点的位置，只需要将该位置右边的所有数据返回即可，对于高度为3的B+Trees只需要进行3次的磁盘IO就可以找到满足范围条件的数据，如果数据量过大的导致查询过慢的话可以使用分页来完成。

5、存储引擎

对于数据库来说存储引擎是在表级别进行生效的，现在对于MySQL来说，常用的存储引擎就是InnoDB，再早些时候，常见的是MyISAM，比如我们在用Navicat进行建表的时候，可以选择使用什么存储引擎来创建表。

或者是在创建表的时候使用SQL来进行设置

CREATE TABLE `test_innodb_lock` (
  -- 此处省略若干个字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `test_myisam` (
  -- 此处省略若干个字段
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

对于已经创建好的表，数据是存储在本地的磁盘上的，一般在根目录的my.ini文件中可以看到Data的存储路径，或者可以直接执行SQL来查看Data的存储路径

show global variables like "%datadir%";

对于InnoDB或者MyISAM两种存储引擎，最大的区别就是存储的模式不一致，比如我上边的SQL（在MySQL5.7中），'test_innodb_lock'在Data目录生成的文件是两个：
test_innodb_lock.frm和test_innodb_lock.ibd
而'test_myisam'在Data目录生成的文件是三个：
test_myisam.frm和test_myisam.MYD和test_myisam.MYI
首先frm是frame框架的简写，MYD是myisam data的缩写，MYI是myisam index的缩写。

5.1、MyISAM存储引擎

因为MyISAM存储引擎会有三个文件，test_myisam.frm和test_myisam.MYD和test_myisam.MYI，test_myisam.frm存储是框架相关的内容，test_myisam.MYD存储的是数据相关的内容，test_myisam.MYI存储的是索引相关的内容。所以得出一个结论：MyISAM索引文件和数据文件是分离的（非聚集、非聚簇）

注意，上图的Col1是索引列，而对于MySQL而言，索引是通过B+Trees方式进行存储的，也就是说MYI文件中是MySQL帮我们维护的一个B+Trees索引，也就是下边的这个图片：

对于数据，是存储在MYD文件中的，就是下边的这张图：

假设有一条SQL是这样写的

select * from stu where col1 = 49; 

对于MyISAM存储引擎来说，会先从MYI的索引文件中，遍历B+Trees来找到49这条数据在磁盘的位置'0x90'，然后根据这个索引地址，去MYD文件中，找对应的数据，也就是Col1:49,Col2:22,Col3:Tom，其实整个过程，读取了两个文件，先读取MYI然后再根据索引读取MYD文件，有点类似于回表操作。

5.2、InnoDB存储引擎

对于InnoDB来说，frm文件存储的是框架相关的内容，ibd文件中维护了B+Trees的索引，而且在叶子节点存储了该索引的主键值对应的data数据，就是下边这张图，可以得出一个结论，InnoDB索引文件和数据文件是在一起的(聚集、聚簇)。

同样还是上边的那个SQL，MySQL会直接从ibd文件中，遍历B+Trees，然后找到49索引的位置，并直接从叶子节点读取到对应的data数据，不需要再读取其他文件了，查找的速度要比MyISAM要快一点。

InnoDB的存储引擎有以下及格特点

• 表本身就是按照B+Trees组织的一个索引结构文件
• 聚集索引的叶子节点包含了完整的数据记录

5.3、面试题

Q：为什么DBA建议InnoDB表必须要有主键，并且推荐使用整形的 自增 主键？
A：首先对于InnoDB来说，ibd文件必须要用B+Trees来进行存储以及维护，如果表有主键了，直接就会维护成一个B+Trees并将data数据保存在叶子节点，如果表没有主键，MySQL会扫描整个表的所有数据，并将不存在重复数据的某一列作为主键，来构成一个B+Trees，如果表中数据不存在这种特殊的列，MySQL会自己维护一个类似于rowid的一个伪列用来维护B+Trees，比较浪费性能。如果使用UUID来作为主键，虽然也会维护成B+Trees，但是在遍历树的时候，因为UUID是一个字符串，既不是整形也不是自增的，在遍历树时会遍历非叶子节点的索引数据的每一个字符，假设两个索引的UUID前边的字符都一致，只有最后一个字符不一致，MySQL也会比较前边的字符，也会浪费性能，因为字符串比较大小的速度要比纯整形数值比较要慢。而且数值要比UUID存储的空间要小很多，会节省珍贵的磁盘空间。对于自增主键这个问题，如果不使用自增主键的时候，随机插入数值可能会导致一个节点进行分裂，并做一次平衡，为了避免这种情况，DBA往往会推荐使用自增的主键，因为自增的主键在存储的时候如果节点存不下了，会开辟一个新的节点来存储，而不会去分裂原有的节点。下边是一个随机插入数值的演示图

Q：为什么 非主键 索引结构的叶子节点存储的是主键值？
A:为了解决一致性和节省存储空间，以下面这个数据为例

以Col1为主键存储的数据是5.2的图那样的，使用非主键来设置索引的话(二级索引)，结果是这样的

首先，还是会维护一个B+Trees，但是对于InnoDB的存储引擎来说，只会维护一个由主键构成的B+Trees，如果没有主键的索引，MySQL会自己维护一个由不重复的字段或者一个伪列(rowid)的隐藏主键，而二级索引仍然是按照字符串的值维护一个B+Trees，而且满足非叶子节点的值从左往右按照字符串的排序递增，但是最终的叶子节点存储的是由MySQL维护的主键的id，可能是主键，或者一个rowid，这样首先节省了存储空间，因为一旦创建一个新的二级索引，会占用一部分的空间，如果叶子节点仍然存储的是整条data数据，那么会造成数据的冗余，也就是多个索引重复对应了多个相同的data数据，如果表过大，建立的很多的索引，会造成很多的空间浪费。其次是保持了一致性，一个表建立了多个索引的时候，如果不保持一致性的话，只有当所有的索引数据都更新完毕之后，才能看做这条数据插入成功了，现在叶子节点存储主键值，只要主键的B+Trees的数据插入成功了，也可以看做这条数据新增成功了，而二级索引就可以直接引用主键维护的B+Trees的值新增的主键索引，就可以了。
对于InnoDB的二级索引在使用的时候，先从二级索引找到对应的主键值，再从主键维护的B+Trees中找对应的data数据。有点类似于MyISAM的存储结构，有一个回表的操作。

在一开始的时候，我写了这么一句话：索引是帮助MySQL高效获取数据的排好序的数据结构。现在可以慢慢理解这句话的含义了。

6、联合索引

首先要理解，联合索引实际上也是一个索引，也就是B+Trees

在数据库中有一种 联合主键索引 ，即由多个字段共同组成的一个索引，比如我有下边一个索引。
KEY 'idx_name_age_position' ('name','age','position') USING BTREE
这种索引会先按照第一个字段排序，第一个字段如果一样按照第二个字段进行排序，第二个一样的话按照第三个字段进行排序，依次类推，直到所有的字段都排序完毕。用上边那个索引的话，就是先按照name字段进行排序，name字段一样时，按照age字段进行排序，age一样时，按照position字段进行排序。
也就是下边这个示例：

左边的节点数据是当name的ASCII码一致时，按照age进行比较。中间的节点是按照name的ASCII码进行排序。右边的节点是name的ASCII码和age一致时，按照position进行排序，当联合主键所有的值都一致时，会排列在一起。并且每个联合主键索引的叶子节点的data数据，存储的都是主键的索引（5.3的QA）。在构建联合主键索引的时候，和B+Trees一样，将每个节点的最小值提取出来用来当做冗余的索引。

6.1、左前缀原则

在使用联合索引的时候，要按照建立索引的字段顺序去使用。以上边的联合索引为例：
KEY 'idx_name_age_position' ('name','age','position') USING BTREE
有3个SQL，在执行时会以下列情况调用索引

select * from employees where name = 'Bill' and age = 31; -- 会走联合索引
select * from employees where age = 30 and position = 'dev'; -- 不会
select * from employees where position = 'manager'; -- 不会

首先，要理解，联合索引组成的B+Trees的值是按照字段的先后顺序进行排序，
第一条会走索引的原因是：过滤的字段是name和age两个字段，也就是联合索引的前两个字段，在树中，已经按照name先对数据进行了排序，也就是在过滤age的时候，直接找name = 'Bill'的节点就可以了，不需要再扫描其他的节点了。

第二条不走的原因是，数据在name排好序的前提下分散开了，在某个节点上age是排好序的，但是对于整张表的数据并不是排好序的，也就不知道下一个节点的age是否还会有30的数据，所以就需要对整个表的数据进行扫描，也就失去了联合索引存在的意义。

第三条同样，在name和age前提下，在某个节点上position是排好序的，但在整张表看来是无序的，也就需要进行全表扫描，也就失去了索引使用的意义。

select * from employees where position = 'dev' and name = 'Bill' and age = 30;

注意：上边这个SQL的过滤字段并没有按照建立索引的字段顺序去使用，MySQL在编译SQL的时候，会进行优化，按照建立索引的字段顺序进行排序从而形成下边的SQL。也就是要想使用联合主键索引，必须要包含联合索引的第一个字段，数量没有要求，使用的顺序也没有要求。

select * from employees where name = 'Bill' and age = 30 and position = 'dev';

二、Explain详解和索引的最佳实践

Learning。。。