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 连接查询,那么会输出两行