MySQL调优补充

MySQL调优

1. MySQL调优主要调什么

我们知道，计算机存储数据分为RAM（内存存储）和ROM（磁盘存储），而持久化数据都是存储在ROM上。我们的MySQL是一种存储数据的服务器，针对的就是数据的持久化保存和读取。所以MySQL调优，主要就是优化从ROM上读取数据的速度。因此，MySQL调优主要分为两个部分：硬件调优和SQL调优。

针对硬件调优，这部分内容由运维去完成。我们的调优主要针对SQL调优。

2. 磁盘IO与预读

计算机存储设备一般分为两种：内存储器(main memory)和外存储器(external memory)。

内存储器为内存，内存存取速度快，但容量小，价格昂贵，而且不能长期保存数据(在不通电情况下数据会消失)。

外存储器即为磁盘读取，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

事实1 ： 不同容量的存储器，访问速度差异悬殊。

• 磁盘(ms级别) << 内存(ns级别)， 100000倍
• 若内存访问需要1s，则一次外存访问需要一天
• 为了避免1次外存访问，宁愿访问内存100次…所以将最常用的数据存储在最快的存储器中

事实2 ： 从磁盘中读 1 B，与读写 1KB 的时间成本几乎一样

从以上数据中可以总结出一个道理，索引查询的数据主要受限于硬盘的I/O速度，查询I/O次数越少，速度越快

3. 索引

索引的本质就是一种排好序的数据结构。就像字典中的目录，通过不断缩小想要获取数据的范围来筛选出最终想要的结果。

索引有以下五种类型：

• 普通索引 index：加速查找

• 唯一索引

  • 主键索引：primary key：加速查找+约束（不为空且唯一）
  • 唯一索引：unique：加速查找+约束（唯一）

• 联合索引

  • primary key(id,name)：联合主键索引
  • unique(id,name)：联合唯一索引
  • index(id,name)：联合普通索引

• 全文索引 fulltext：用于搜索很长的一篇文章的时候，效果最好

• 空间索引 spatial：了解就好

4. 索引的数据结构

4.1 Hash索引

Hash索引是比较常见的一种，单条查询效率很高，时间复杂度为1。但Hash索引不是最常用的数据库索引类型，因为Hash索引存储的就是数据的Hash码，可能发生Hash碰撞，而且不适合做范围查找，因为相似的两条数据计算出来的Hash码相差可能很大，会被存储在磁盘上不同的位置，不方便读取。

4.2 二叉树索引

二叉树索引是一种比较常见的索引数据结构。二叉树索引具有以下特点：

• 二叉树的时间复杂度为O(n)

• 一个节点只能有两个子节点。不能超过2

• 左子节点小于本节点，右子节点大于本节点

  

MySQL并没有选择二叉树来做索引，因为二叉树随着数据量的增加，树的高度会越来越高，那么随之产生的磁盘IO次数也就是越来越多，这会降低读取数据的效率。在极端情况下，二叉树会出现坍塌，形成链式结构，这种情况下IO次数是最多的。

平衡二叉树(AVL)和红黑树都是属于二叉树，因此都不考虑使用

4.3 B树

为了减少磁盘IO次数，那么降低树的高度就是需要解决的问题。于是出现了B树，也被称为B-树(其中**-**不是减号)，也称为B-Tree。B树是一种平衡多路查找树。

当总节点数量相同时，B树的高度远远小于AVL树和红黑树，磁盘IO次数大大减少。B树的优势除了树高小，还有对访问局部性原理的利用。所谓局部性原理，是指当一个数据被使用时，其附近的数据有较大概率在短时间内被使用。B树将键相近的数据存储在同一个节点，当访问其中某个数据时，数据库会将该整个节点读到缓存中；当它临近的数据紧接着被访问时，可以直接在缓存中读取，无需进行磁盘IO；换句话说，B树的缓存命中率更高。

B树中每个节点（包括叶节点和非叶节点）都存储真实的数据，当一条数据较大时，节点存储的总数据条数就会减少，这会降低B树的缓存命中率。在B树中进行范围查询时，首先找到要查找的下限，然后对B树进行中序遍历，直到找到查找的上限，这种查询方式，遍历的节点较多。

4.4 B+树

B+树是应文件系统所需而产生的B树的变形树。B+树的中间节点包含的元素不保存数据，只用来存储索引；所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接。

B+树比B树更适合数据库索引

• B+树的磁盘读写代价更低

  B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了；

• B+树查询效率更加稳定

  由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当；

• B+树便于范围查询（最重要的原因，范围查找是数据库的常态）

  B树在提高了IO性能的同时并没有解决元素遍历的效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低；

5. 索引是如何存储的

CREATE TABLE IF NOT EXISTS goods(
  id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` VARCHAR(50) NOT NULL,
  brand VARCHAR(50) NOT NULL,
  price DOUBLE(10, 2) NOT NULL,
  INDEX (`name`,brand)
);
INSERT INTO goods(id, `name`, brand, price) VALUES (DEFAULT, '手机', '华为', 1299.00);
INSERT INTO goods(id, `name`, brand, price) VALUES (DEFAULT, '手机', '小米', 1399.00);
INSERT INTO goods(id, `name`, brand, price) VALUES (DEFAULT, '电脑', '华为', 3299.00);
INSERT INTO goods(id, `name`, brand, price) VALUES (DEFAULT, '电脑', '联想', 5299.00);

SELECT * FROM goods WHERE name LIKE '手机%';

6. 聚集索引和非聚集索引

6.1 聚集索引

聚集索引是指的是在InnoDB引擎下，主键索引的值是当前的整条数据，在使用主键来进行查询的时候，直接就通过B+树就能获取整条数据

6.2 非聚集索引

非聚集是指的是在MyISAM存储引擎下， 主键索引的值是这条数据的物理地址，在使用主键进行查询的时候 ，会先从B+树中找到数据的物理地址， 然后再通过物理地址去查询这条数据

聚集和非聚集的最大的区别是:主键索引的值是保存的地址还是整条数据

为什么使用InnoDB引擎的表必须要有主键，并且推荐使用自增类型的主键？

因为InnoDB引擎下的主键索引使用的是聚集索引，索引中就包含了整条数据的信息，通过主键查询的时候，直接查索引就能将数据取出，这样大大提升了查询效率。推荐使用自增类型的主键是因为B+树中的每一节点中存储的数据都是有顺序的，也就是需要排序，而自增索引本身就有顺序，可以省去排序的过程。

为什么非主键索引结构叶子节点存储的值是主键值？

如果存储的也是整条数据的值，那么在进行数据更新的时候，需要更新多个地方，同时，还会浪费存储空间。如果存储的是主键，那么更新的时候就只需要更新主键索引下存储的数据即可。

6.3 最左前缀原理

在建立联合索引的时候，也就是对多个字段建立索引，会选择索引的顺序，比如想在a,b,c三个字段上建立一个联合索引，以选择优先级，a、b、c,或者是b、a、c 或者是c、a、b等顺序。为什么数据库会让我们选择字段的顺序呢？这里就引出了数据库索引的最左前缀原理。

比如：索引index1:(a,b,c)有三个字段，在使用sql语句来查询的时候，会发现很多情况下不按照我们想象的来走索引。

select * from table where c = '1';         -- 不会走index1索引
select * from table where b ='1' and c ='2';  -- 也不会走index1索引

什么语句会走index1索引呢？

select * from table where a = '1';  -- 会走index1索引
select * from table where a = '1' and b = '2';  -- 会走index1索引
select * from table where a = '1' and b = '2'  and c='3';  -- 会走index1索引

索引是有序的，index1索引在索引文件中的排列是有序的，首先根据a来排序，然后才是根据b来排序，最后是根据c来排序。

最左前缀原则就是只要查询的是联合索引的最左 N 个字段，就可以利用该联合索引来加速查询。不按照最左匹配来为什么失效，其原因就在于联合索引的 B+ 树中的键值是排好序的。不过，这里指的排好序，其实是相对的，举个例子，有 (a, b, c) 联合索引，a 首先是排序好的，而 b 列是在 a 列排序的基础上做的排序，同样的 c 是在 a,b 有序的基础上做的排序。所以说，如果有 where a = xxx order by b 这种请求的话，是可以直接在这颗联合索引树上查出来的，不用对 b 列进行额外的排序；而如果是 where a = xxx order by c 这种请求的话，还需要额外对 c 列进行一次排序才行。另外，如果有对 a,b,c 的联合条件查询的话，并且 a 是模糊匹配或者说是范围查询的话，其实并不能完全踩中联合索引（a,b,c），a 列右边的所有列都无法使用索引进行快速定位了。所以这个时候就需要进行回表判断。也就是说数据库会首先根据索引来查找记录，然后再根据 where 条件来过滤记录。

思考：假设现在一张表，常用的查询条件有a和b同时查询，还有单独查询b，请问应该怎么建立索引？

SELECT * FROM table WHERE a=? AND b=?
index(a, b), index(b)

7. Explain工具

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返 回执行计划的信息，而不是执行这条SQL

注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中

Explain分析示例

DROP TABLE
IF EXISTS `actor`;

CREATE TABLE `actor` (
	`id` INT (11) NOT NULL,
	`name` VARCHAR (45) DEFAULT NULL,
	`update_time` datetime DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES(1,'a','2017-12-22 15:27:18'),(2,'b','2017-12-22 15:27:18'),(3,'c','2017-12-22 15:27:18');

CREATE TABLE `film` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR (10) DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `idx_name` (`name`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO `film` (`id`, `name`) VALUES(3, 'film0'),(1, 'film1'),(2, 'film 2');

CREATE TABLE `film_actor` (
	`id` INT (11) NOT NULL,
	`film_id` INT (11) NOT NULL,
	`actor_id` INT (11) NOT NULL,
	`remark` VARCHAR (255) DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `idx_film_actor_id` (`film_id`, `actor_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`)VALUES(1, 1, 1),(2, 1, 2),(3, 2, 1);

EXPLAIN SELECT * FROM actor;

在查询中的每个表会输出一行，如果有两个表通过 join 连接查询，那么会输出两行