张利源 blog

MySQL高级部分，着重MySQL索引优化

卸载之前的版本

查看已安装的MySQLrpm -qa|grep -i mysql

逐个卸载即可yum remove mysql-community-server-5.6.36-2.el7.x86_64

MySQL Linux版的安装

  1. 下载MySQL安装包wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

  2. 安装MySQL源yum -y localinstall mysql57-community-release-el7-11.noarch.rpm

  3. 在线安装MySQLyum -y install mysql-community-server

  4. 启动MySQLsystemctl start mysqld

  5. 设置开机启动systemctl enable mysqld，systemctl daemon-reload

  6. 查看默认密码vim /var/log/mysqld.log

  7. 修改密码ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

  8. 设置远程登录GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;

  9. 停掉防火墙systemctl stop firewalld，放行3306端口最好。

  10. 配置MySQL的默认编码

    查看MySQL的编码show variables like '%char%';(我已经修改过了)

    修改

    1
    2
    3
    4
    5
    vim /etc/my.cnf

    # 添加如下配置
    character_set_server=utf8
    init_connect='SET NAMES utf8'

  11. 重启MySQLsystemctl restart mysqld

MySQL逻辑架构

  • 第一层：所包含的服务并不是MySQL所独有的技术。它们都是服务于C/S程序或者是这些程序所需要的 ：连接处理，身份验证，安全性等等。
  • 第二层：这是MySQL的核心部分，通常叫做SQL Layer。在 MySQL数据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断， sql解析，行计划优化， query cache 的处理以及所有内置的函数(如日期,时间,数学运算,加密)等等。
  • 第三层：通常叫做StorEngine Layer ，也就是底层数据存取操作实现部分，由多种存储引擎共同组成。它们负责存储和获取所有存储在MySQL中的数据。就像Linux众多的文件系统 一样。
    每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交互的。这个接口隐藏了各个存储引擎不同的地方。对于查询层尽可能的透明。这个API包含了很多底层的操作。如开始一个事务，或者取出有特定主键的行。

存储引擎不能解析SQL(innoDB除外，它会解析外键定义，因为MySQL服务本身没有实现该功能)，互相之间也不能通信。仅仅是简单的响应服务器的请求。

MySQL存储引擎

查看存储引擎show engines;

查看当前存储引擎show variables like '%storage_engine%';

MyISAM和InnoDB对比

对比项 MyISAM InnoDB
主外键 不支持 支持
事务 不支持 支持
行表锁 表锁，即使操作一条记录也会锁住整个表，不适合高并发操作。 行锁，操作时只锁住某一行，不对其他的行有影响。
缓存 只缓存索引，不缓存真实数据 不仅缓存索引还缓存真实数据，对内存要求高，而且内存大小对性能有决定性的影响
表空间 小 大
关注点 性能 事务

MySQL执行加载顺序

sql语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select distinct <select_list>

from

left_table <join_type> join right_table

on <join_condition>

where <where_condition>

group by <group_by_list>

having <having_condition>

order by <order_by_conddition>

limit <limit_num>

MySQL服务器处理后的顺序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
from left_table

on join_condition

join_type join right_table

where where_condition

group by group_by_list

having having_condition

select

distinct select_list

order by order_by_condition

limit limit_num
  • 先确定要查询的表
  • 然后where条件过滤数据
  • group by进行分组
  • having条件过滤每个组中的数据
  • 确定要查询的字段
  • 对于查出来的信息进行排序
  • 限制查询数据的量

七种JOIN理论

MySQL并不支持full outer join,这里我们可以使用UNION,UNION用于合并两个或多个SELECT语句的结果集。

UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

索引

索引是什么

索引是帮助MySQL高效获取数据的数据结构。

排序+查找

目的： 提高查找效率，可以类比字典。

优劣

优势：

  • 提高数据的检索效率，降低数据库的IO成本。
  • 通过索引列对数据进行排序，降低数据的排序成本，降低了CPU的消耗。

劣势：

  • 实际索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也是占空间的。一般而言，索引表占用空间是数据表的1.5倍。
  • 虽然索引大大提高了查询速度，同时却降低了更新表的速度。

分类

![](https://cdn.jsdelivr.net/gh/zhangliyuangit/img/索引 (1).png)

基本语法

  • 创建

    CREATE [UNIQUE] INDEX indexName ON myTable(columnname(length));

    ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length))

  • 删除

    DROP INDEX [indexName] ON myTable;

  • 查看

    SHOW INDEX FROM tableName;

索引结构

举个例子，在b树中查询数据如下

假如我们查询值等于10的数据。查询路径磁盘块1->磁盘块2->磁盘块5。

第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，10<15，走左路，到磁盘寻址磁盘块2。

第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<10，到磁盘中寻址定位到磁盘块5。

第三次磁盘IO：将磁盘块5加载到内存中，在内存中从头遍历比较，10=10，找到10，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。

看到这里一定觉得B树就很理想了，但是前辈们会告诉你依然存在可以优化的地方：

  1. B树不支持范围查询的快速查找，你想想这么一个情况如果我们想要查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。

  2. 如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

B+树：改造B树

B+树，作为B树的升级版，在B树基础上，MySQL在B树的基础上继续改造，使用B+树构建索引。B+树和B树最主要的区别在于非叶子节点是否存储数据的问题

  • B树：非叶子节点和叶子节点都会存储数据。
  • B+树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

B+树结构

B+树的最底层叶子节点包含了所有的索引项。从图上可以看到，B+树在查找数据的时候，由于数据都存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据。所以在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系，但是从另一方面来说，由于数据都被放到了叶子节点，所以放索引的磁盘块锁存放的索引数量是会跟这增加的，所以相对于B树来说，B+树的树高理论上情况下是比B树要矮的。也存在索引覆盖查询的情况，在索引中数据满足了当前查询语句所需要的全部数据，此时只需要找到索引即可立刻返回，不需要检索到最底层的叶子节点。

举个例子：

  • 等值查询：
    假如我们查询值等于9的数据。查询路径磁盘块1->磁盘块2->磁盘块6。

  1. 第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，9<15，走左路，到磁盘寻址磁盘块2。

  2. 第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<9<12，到磁盘中寻址定位到磁盘块6。

  3. 第三次磁盘IO：将磁盘块6加载到内存中，在内存中从头遍历比较，在第三个索引中找到9，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。（这里需要区分的是在InnoDB中Data存储的为行数据，而MyIsam中存储的是磁盘地址。）

过程如图：

  • 范围查询：

    假如我们想要查找9和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7。

  1. 首先查找值等于9的数据，将值等于9的数据缓存到结果集。这一步和前面等值查询流程一样，发生了三次磁盘IO。

  2. 查找到15之后，底层的叶子节点是一个有序列表，我们从磁盘块6，键值9开始向后遍历筛选所有符合筛选条件的数据。

  3. 第四次磁盘IO：根据磁盘6后继指针到磁盘中寻址定位到磁盘块7，将磁盘7加载到内存中，在内存中从头遍历比较，9<25<26，9<26<=26，将data缓存到结果集。

  4. 主键具备唯一性（后面不会有<=26的数据），不需再向后查找，查询终止。将结果集返回给用户。

可以看到B+树可以保证等值和范围查询的快速查找，MySQL的索引就采用了B+树的数据结构。

索引适用情况

哪些情况下需要创建索引

  • 主键自动创建唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其他表关联的字段，外键关系建立索引
  • 频繁更新的字段不适合创建字段
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高访问排序速度
  • 查询中统计或分组的字段

性能分析

MySQL Query Optimizer

  • MySQL中专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的系统信息，为客户端请求的Query提高它认为最优的执行计划。
  • 当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算等。然后分析Query中的Hint信息，看到显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint信息或者Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后得出最后的执行计划。

Explain

能干嘛？

  1. 表的读取顺序
  2. 数据读取操作的操作类型
  3. 哪些索引可以使用
  4. 哪些索引被实际使用
  5. 表之间的使用
  6. 每张表有多少行被优化器查询

执行计划包含的信息

id

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。

id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行

select_type

  1. simple：简单查询
  2. primary：查询中若包含任何复杂的子查询，则最外层被标记为paimary，俗称是鸡蛋壳
  3. subquery：在select或where列表包含了子查询
  4. derived：在from列表中包含的子查询被标记为derived（衍生），mysql会递归执行这些子查询，把结果放在临时表里（临时表会增加系统负担，但有时不得不用）
  5. union：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为：derived
  6. union result：两种union结果的合并

type

从最好到最差依次是

system > const > eq_ref > ref > range > index > all(全表扫描)

一般来说，得保证查询至少达到range级别，最好是能达到ref。

  • system：表只要一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计
  • const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引，因为只匹配一行数据，所以很快，如将主键置于where列表中，mysql就能将该查询转换为一个常量。explain select * from tbl_emp where id = 1;
  • eq_ref ：对于每个索引键，表中只有一条数据与之匹配。常见于主键索引或者唯一性索引。
  • ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（可以是0，多个）explain select * from tbl_emp where name = 'z3'，name列上有索引。
  • range：检索指定范围的行，where是一个范围查询explain select * from tbl_emp where id in (1,2,3);
  • index：查询全部索引中的数据。通常比all快explain select id from tbl_emp;
  • all：全表扫描。

possible_keys\key

possible_keys表示查询可能用到哪些索引。

key表示实际用了哪些索引，

出现possible_keys有列，而key为NULL的情况，这种情况是因为表中的数据不多，MySQL认为索引对此查询帮助不大，选择了全盘扫描。

如果possible_keys为NULL，则没有相关索引。在这种情况下，可以通过检查where子句看是否可以创建一个适当的索引来提高查询性能。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引长度，在不损失精确性的情况下，长度越短越好。

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

ref

这列显示了在key记录的索引中，表查找值所用到的列或常量，常见的有：const(常量)、字段名(例：t1.id)

rows

根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

EXtra

Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底如何执行给定的查询语句。

  • using index：查询的列被索引覆盖，并且where筛选条件是索引的前导列，是性能高的表现，一般使是使用了覆盖索引（索引包含了所有查询的字段）。对于innodb来说，如果是辅助索引性能会有不少的提高。
  • using where：当我们使用全盘扫描来执行某个表的查询，并且该语句的where子句中有针对该表的搜索条件。
  • using filesort：说明MySQL会对数据适应一个外部的索引排序。而不是按照表内的索引进行读取，MySQL中无法利用索引完成排序操作称为”文件排序”。
  • using temporary：使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。
  • using join buffer：使用了连接缓存
  • impossible where：where子句的值总是false，不能用来获取任何元组
  • select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
  • distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

单表优化案例

模拟数据

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL , 
`views` INT(10) UNSIGNED NOT NULL , 
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,3,3,3,'3','3');

查询category_id 为1且comments>1的情况下，观看数量最多的文章**

1
explain select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1 --分析sql

  • type：all，全表扫描
  • using filesort：文件内部排序

优化

因为查询用到了三个字段，我们在这个三个字段上建复合索引。

1
create index idx_article_ccv on article(category_id, comments, views);

再次查看执行计划

查看comments=3的情况

type变成了range，这是可以忍受的。但是extra里使用的using filesort仍然无法接受。

我们已经建立了索引，为什么没用了？

这是因为按照BTree索引的工作原理。

先排序category_id

如果遇到相同的category_id，则再排序comments，如果遇到相同的comments则再排序views，

当comments字段在联合索引处于中间位置时，

因comments > 1条件是一个范围值(所谓range)

MySQL无法利用索引在对后面的view部分进行检索，即range类型的字段后面的索引无效。

重新创建索引

1
create index idx_article_cv on article(category_id,views);

查看执行计划

完美收工。

双表优化案例

模拟数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));

查看执行计划

1
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card

优化

  1. 由于是左连接，左表是主表，因此第一次尝试在左表上加索引。

    1
    create index idx_class_card on class (card);

    再次执行计划

    结论：虽然type变为index，但是扫描行数依然是全表扫描。

  2. 删除左表索引，对右表创建索引。

    1
    2
    drop index idx_class_card on class; -- 删除索引
    create index idx_book_card on book (card); -- 创建索引

    再次执行计划

    结果：type变为ref，rows只扫描了一行。

结论：这是由于LEFT JOIN的特性决定的，由于左表数据全有，所以关键在于如果从右表进行搜索，所以右表一定要添加索引。

三表优化案例

在双表的基础上创建一张phone表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));

三表均没有建立索引

1
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card

结论： 全表扫描，且使用了连接缓存

在phone和book表新增索引

1
2
3
CREATE INDEX idx_phone_card ON phone(card)
CREATE INDEX idx_book_card ON book (card)
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card

总结

  • 语句优化应尽可能减少join语句中NestedLoop的循环总次数，即“永远用小结果集驱动大结果集”。
  • 优先优化NestedLoop的内层循环。
  • 尽量保证join语句中被驱动表的条件字段添加了索引（即LEFT JOIN在右表上添加，反之亦然）。
  • 当无法保证被驱动表的条件字段添加索引时，且内存资源充足的前提下，不妨调整join buffer以达到性能优化的目的。

索引优化

索引失效

最左前缀原则

建表，并且在name，age，pos列上建立复合索引。

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`)

每次多带一个条件进行查询

1
2
3
explain select * from staffs where name = 'z3';
explain select * from staffs where name = 'z3' and age = 25;
explain select * from staffs where name = 'z3' and age = 25 and pos = 'dev';

查询结果如下

可以发现三次查询都用了索引，并且索引的长度在不断增长。

继续使用三列，但是将三列的位置调换

1
explain select * from staffs where age = 25 and name = 'z3' and pos = 'dev';

可以发现，即使我们调换位置，仍然用到了索引，证明全值匹配时MySQL会自动帮我们优化。

我们使用name和pos列查询

1
explain select * from staffs where name = 'z3' and pos = 'dev';

虽然用到了索引，但是len为74，证明只有name的检索用到了索引。

索引列上计算、函数、(手动or自动)类型转换
1
explain select * from staffs where left(name,3) = 'z3';

发现没有用到索引。

自动类型转换

执行如下两条SQL

1
2
explain select * from staffs where name = '2000';
explain select * from staffs where name = 2000;

执行结果

MySQL会自动的帮我们进行类型的转换，但是索引失效。

范围条件后边的列失效

我们将三个字段中的age字段变成范围

1
explain select * from staffs where name = 'z3' and age > 23 and pos = 'dev';

对比发现后面的范围查找变成了range，并且key_len变成了78，表示后面的pos字段没有用到索引。

使用不等于(!=或<>)
1
explain select * from staffs where name <> 'z3';

可以发现进行了全盘扫描，实际没有用到索引。

like通配符开头
1
2
explain select * from staffs where name like '%z3%';
explain select * from staffs where name like 'z3%';

可以发现以%开头的like查询并没有用到索引。

如果我们非要使用%开头的like查询，我们可以使用覆盖索引。

or
1
explain select * from staffs where name = 'z3' or name = 'z5';

尽量使用覆盖索引

1
2
explain select name,age,pos from staffs where name = 'z3' and age = 23 and pos = 'dev';
explain select * from staffs where name = 'z3' and age = 23 and pos = 'dev';os = 'dev';

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *。

总结口诀

1
2
3
4
5
6
全值匹配我最爱，最左前缀要遵守。
带头大哥不能死，中间兄弟不能断。
索引列上少计算，范围之后全失效。
like百分写最右，覆盖索引不写星。
不等空值还有or，索引失效要少用。
VARCHAR引号不可丢，SQL高级也不难。

查询截取分析

查询优化

小表驱动大表

EXISTS

  • SELECT ... FROM table WHERE EXISTS (subquery)
  • 该语法可以理解为，将主查询的数据，放到子查询中做条件验证，根据验证结果(TRUE或FALSE)来决定查询结果是否得以保留。

提示

  • EXISTS(subquery)只返回true或者false，因此子查询中的select *也可以是select 1或select 'x'，官方说法是实际执行时会忽略SELECT清单，因此没有区别。
  • EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题。
  • EXISTS子查询往往也可以用条件表达式/其他子查询或者JOIN来替代，何种最优需要具体问题具体分析。

ORDER BY 优化

  • ORDER BY子句，尽量使用index方法排序，避免使用filesort方法排序。

创建tblA表，在age和birth字段上建立复合索引。

使用order by进行查询

1
2
3
4
5
explain select * from tblA where age > 20 order by age;
explain select * from tblA where age > 20 order by birth;
explain select * from tblA where age > 20 order by age,birth;
explain select * from tblA where age > 20 order by birth,age;
explain select * from tblA where age = 20 order by birth;

查询结果如下

可以发现，order by 同样支持最左前缀原则，同时带头大哥为常量时，后面排序同样可以使用索引

文件排序（FileSort）分为两种：

  • 双路排序(又叫回表排序模式)：先根据相应的条件取出相应的排序字段和可以直接定位行 数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；
  • 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；

举个例子，下面有一段sql：

1
select * from user where name = "自由的辣条" order by age;

双路排序过程:
MySQL 4.1 之前使用的双路排序，通过两次扫描磁盘得到数据。读取主键id 和 order by 列并对其进行排序，扫描排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

  1. 从索引 name 找到第一个满足 name = ‘自由的辣条’ 的主键id
  2. 根据主键 id 取出整行，把排序字段 age 和主键 id 这两个字段放到 sort buffer(排序缓存) 中
  3. 从索引 name 取下一个满足 name = ‘自由的辣条’ 记录的主键 id
  4. 重复 3、4 直到不满足 name = ‘自由的辣条’
  5. 对 sort_buffer 中的字段 age 和主键 id 按照字段 age进行排序
  6. 遍历排序好的 id 和字段 age ，按照 id 的值回到原表中取出 所有字段的值返回给客户端

单路排序过程：

  1. 从索引name找到第一个满足 name = ‘自由的辣条’ 条件的主键 id
  2. 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer(排序缓存)中
  3. 从索引name找到下一个满足 name = ‘自由的辣条’ 条件的主键 id
  4. 重复步骤 2、3 直到不满足 name = ‘自由的辣条’
  5. 对 sort_buffer 中的数据按照字段 age 进行排序
  6. 返回结果给客户端

从磁盘中读取查询需要的所有列，按照 order by 列在 sort_buffer(排序缓存) 缓冲区对他们进行排序，然后扫描排序后的列表输出。因为单路排序效率更快，避免了二次读取数据，把随机IO变成了顺序IO，但是会使用更多的空间。

对比：

其实对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键 和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。

选型：

至于mysql优化器使用双路排序还是单路排序是有自己的算法判断的，如果查询的列字段大于max_length_for_sort_data变量，则会使用双路排序，反之则会使用单路排序，单路排序速度是更快的，不过比较占据内存，如果在内存空间允许的情况下想要使用单路排序的话，可以增加max_length_for_sort_data变量的大小，max_length_for_sort_data变量默认为1024字节。

max_length_for_sort_data指某个表的所有列长度总和

注意：
如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增 大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

order by关键字优化：

  1. ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。
  2. MySQL支持二种方式的排序，FileSort和Index，Index效率较高，FileSort方式效率较低。
  3. 尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

慢查询日志

  • MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
  • long_query_time的默认值是10，意思是运行10秒以上的语句。
  • 默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
  • 当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

查看是否开启慢查询日志以及文件存放的位置。

1
2
3
4
5
6
7
8
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_query_log      | OFF                          |
| slow_query_log_file | /var/lib/mysql/192-slow.log |
+---------------------+-----------------------------+
2 rows in set (0.01 sec)

开启慢查询日志

1
mysql> set global slow_query_log = 1;

查看当前阈值（默认10s）

1
2
3
4
5
6
7
mysql> show VARIABLES like 'long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 10.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

将阈值修改为3s

1
mysql> set global long_query_time=3;

查看当前系统有多慢sql

1
2
3
4
5
6
7
mysql> show global status like '%slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+
1 row in set (0.00 sec)

去对应文件查看是那条sql慢

1
2
3
4
5
6
7
8
9
[root@localhost mysql]# cat 192-slow.log 
/usr/sbin/mysqld, Version: 5.7.34 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2021-06-19T06:10:16.412412Z
# User@Host: root[root] @ localhost []  Id:    17
# Query_time: 4.010066  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1624083016;
select sleep(4)

日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

查看mysqldumpslow的帮助信息， mysqldumpslow --help

  • s：是表示按照何种方式排序
  • c：访问次数
  • I：锁定时间
  • r：返回记录
  • t：查询时间
  • al：平均锁定时间
  • ar：平均返回记录数
  • at：平均查询时间
  • t：即为返回前面多少条的数据
  • g：后边搭配一个正则匹配模式，大小写不敏感

批量插入数据脚本

  1. 创建SQL

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    create database bigData;
    use bigData;

    CREATE TABLE dept(
    	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    	deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    	dname VARCHAR(20)NOT NULL DEFAULT "",
    	loc VARCHAR(13) NOT NULL DEFAULT ""
    )ENGINE=INNODB DEFAULT CHARSET=utf8;

    CREATE TABLE emp(
        id int unsigned primary key auto_increment,
        empno mediumint unsigned not null default 0,
        ename varchar(20) not null default "",
        job varchar(9) not null default "",
        mgr mediumint unsigned not null default 0,
        hiredate date not null,
        sal decimal(7,2) not null,
        comm decimal(7,2) not null,
        deptno mediumint unsigned not null default 0
    )ENGINE=INNODB DEFAULT CHARSET=utf8;

  2. 设置参数log_bin_trust_function_creators

    1
    2
    show variables like 'log_bin_trust_function_creators';
    set global log_bin_trust_function_creators=1;

  3. 创建函数，生成随机字符串

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    delimiter $$ # 两个 $$ 表示结束
    create function rand_string(n int) returns varchar(255)
    begin
        declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
        declare return_str varchar(255) default '';
        declare i int default 0;
        while i < n do
            set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
            set i=i+1;
        end while;
        return return_str;
    end $$

  4. 随机生成部门编号

    1
    2
    3
    4
    5
    6
    7
    delimiter $$
    create function rand_num() returns int(5)
    begin
        declare i int default 0;
        set i=floor(100+rand()*10);
        return i;
    end $$

  5. 创建存储过程

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    delimiter $$
    create procedure insert_emp(in start int(10),in max_num int(10))
    begin
        declare i int default 0;
        set autocommit = 0;
        repeat
            set i = i+1;
            insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());
            until i=max_num
            end repeat;
        commit;
    end $$
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    delimiter $$
    create procedure insert_dept(in start int(10),in max_num int(10))
    begin
        declare i int default 0;
        set autocommit = 0;
        repeat
            set i = i+1;
            insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));
            until i=max_num
            end repeat;
        commit;
    end $$

  6. 插入数据

    1
    2
    3
    mysql> DELIMITER ;
    mysql> CALL insert_dept(100, 10);
    Query OK, 0 rows affected (0.01 sec)

用show prefile进行sql分析

Show Profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。

1
2
3
4
- 查看是否开启
show variables like 'profiling';
- 开启
set profiling = on;

查看结果，show profiles;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> show profiles;
+----------+------------+----------------------------------------------------------------+
| Query_ID | Duration   | Query                                                          |
+----------+------------+----------------------------------------------------------------+
|        1 | 0.00127000 | show variables like 'profiling'                                |
|        2 | 0.00024250 | SELECT DATABASE()                                              |
|        3 | 0.00024875 | show databases                                                 |
|        4 | 0.00018525 | show tables                                                    |
|        5 | 0.00024750 | show tables                                                    |
|        6 | 0.00168025 | show variables like 'profiling'                                |
|        7 | 0.00438350 | select * from tbl_emp te join tbl_dept td on te.deptId = td.id |
|        8 | 0.00046700 | select * from tbl_emp te join tbl_dept td on te.deptId = td.id |
|        9 | 0.00043650 | select * from tbl_emp te join tbl_dept td on te.deptId = td.id |
|       10 | 0.00027800 | select * from tbl_emp te join tbl_dept td on te.deptId = td.id |
|       11 | 0.00012400 | SELECT DATABASE()                                              |
|       12 | 0.00022425 | show databases                                                 |
|       13 | 0.00012100 | show tables                                                    |
|       14 | 0.48516150 | select * from emp group by id % 10 limit 150000                |
|       15 | 0.49136075 | select * from emp group by id % 20 order by 5                  |
+----------+------------+----------------------------------------------------------------+
15 rows in set, 1 warning (0.00 sec)

诊断SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> show profile cpu,block io for query 15;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000065 | 0.000059 |   0.000000 |            0 |             0 |
| checking permissions | 0.000008 | 0.000007 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000013 | 0.000013 |   0.000000 |            0 |             0 |
| init                 | 0.000027 | 0.000028 |   0.000000 |            0 |             0 |
| System lock          | 0.000009 | 0.000008 |   0.000000 |            0 |             0 |
| optimizing           | 0.000005 | 0.000004 |   0.000000 |            0 |             0 |
| statistics           | 0.000017 | 0.000017 |   0.000000 |            0 |             0 |
| preparing            | 0.000012 | 0.000012 |   0.000000 |            0 |             0 |
| Creating tmp table   | 0.000034 | 0.000035 |   0.000000 |            0 |             0 |
| Sorting result       | 0.000005 | 0.000004 |   0.000000 |            0 |             0 |
| executing            | 0.000002 | 0.000002 |   0.000000 |            0 |             0 |
| Sending data         | 0.491050 | 0.490653 |   0.000000 |            0 |             0 |
| Creating sort index  | 0.000052 | 0.000045 |   0.000000 |            0 |             0 |
| end                  | 0.000005 | 0.000004 |   0.000000 |            0 |             0 |
| query end            | 0.000008 | 0.000008 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000005 | 0.000006 |   0.000000 |            0 |             0 |
| query end            | 0.000004 | 0.000003 |   0.000000 |            0 |             0 |
| closing tables       | 0.000006 | 0.000006 |   0.000000 |            0 |             0 |
| freeing items        | 0.000024 | 0.000023 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000012 | 0.000013 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set, 1 warning (0.00 sec)

参数备注

  • ALL：显示所有的开销信息。
  • BLOCK IO：显示块lO相关开销。
  • CONTEXT SWITCHES ：上下文切换相关开销。
  • CPU：显示CPU相关开销信息。
  • IPC：显示发送和接收相关开销信息。
  • MEMORY：显示内存相关开销信息。
  • PAGE FAULTS：显示页面错误相关开销信息。
  • SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
  • SWAPS：显示交换次数相关开销的信息。

日常开发需要注意的结论

  • converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。
  • Creating tmp table 创建临时表，拷贝数据到临时表，用完再删除
  • Copying to tmp table on disk 把内存中临时表复制到磁盘，危险!
  • locked

MySQL锁机制

数据库锁理论概述

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁的分类

从对数据操作的类型（读\写）分

  • 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而互不影响。
  • 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的粒度分

  • 表锁
  • 行锁

MyISAM读写锁

表锁（偏读）

特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定颗粒度大，发生锁冲突的概览高，并发度最低。

建表SQL（使用MyISAM引擎）

1
2
3
4
5
6
7
8
9
10
11
12
create table mylock (
    id int not null primary key auto_increment,
    name varchar(20) default ''
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

select * from mylock;

手动添加表锁

1
2
3
lock table 表名字 read(write), 表名字2 read(write), 其他;
- 给mylock表加读锁
lock table mylock read;

查看表上加过的锁

1
show open tables;

in_use是1表示已经上锁。

释放锁

1
unlock tables;

加读锁——为mylock表加read锁（读阻塞写例子）

会话1 会话2
获得mylocak的读锁 连接终端
当前会话可以查询表记录 其他会话也可以查询该表的记录
当前会话不能查询其他没有锁定的表 其他会话可以查询或更新未锁定的表
当前会话中插入或者更新锁定的表都会提示错误 其他会话插入或更新锁定表会一直等待获得锁image-20210625104439348
释放锁image-20210625104559873 会话2获得锁，更新操作完成

加写锁——为mylock表加write锁（写阻塞读例子）

会话1 会话2
获得mylock的写锁定 等待会话1开启写锁后，会话2再连接终端
当前会话对锁定表的查询、更新、插入操作都可以执行 其他会话的对锁定表的查询被阻塞，需要等待锁被释放
释放锁 会话2获得锁，查询返回

案例结论

MyISAM在执行查询语句(SELECT)前，会自动给涉及到的所有表加读锁，在执行增删改操作前，会自动给涉及到的表加写锁。

MySQL的表级锁有两种模式：

  1. 表共享读锁(Table Read Lock)
  2. 表独占写锁(Table Write Lock)
锁类型 可否兼容 读锁 写锁
读锁 是 是 否
写锁 是 否 否

结合上表，所以对MyISAM表进行操作，会有以下情况：

  1. 对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。

  2. 对MyISAM表的写操作〈加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

InnoDB行锁

行锁偏向InnoDB引擎，开销大，加锁慢；会出现死锁；所得颗粒度小，发生锁冲突的概览低，并发度也最高。

InnoDB与MyISAM最大不同的两点：1.支持事务。2.采用行级锁

查看当前数据库的事务隔离级别show variables like 'tx_isolation'

1
2
3
4
5
6
7
mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

新建SQL（使用InnoDB引擎）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;

INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');

CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
会话1 会话2
取消自动提交set autocommit = 0; 取消自动提交set autocommit = 0;
更新但不提交 会话2被阻塞，只能等待
提交更新 接触阻塞，更新正常进行
更新但不提交 会话2更新其他行的记录，并没有发生阻塞

索引失效行锁变表锁

a列为int类型，b列为varchar类型，两个会话都是不自动提交事务

会话1 会话2
更新a字段，但是b字段不加引号 会话2更新其他字段，依然阻塞

证明由行锁变为表锁。

间隙锁的危害

数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | 3    |
|    4 | 4003 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b2   |
+------+------+
9 rows in set (0.00 sec)

a列并不连续

左侧执行sqlupdate test_innodb_lock set b = '600.01' where a > 1 and a < 6;不提交事务

右侧执行sqlinsert into test_innodb_lock values(2,'200');

右侧被阻塞

左侧提交，右侧插入成功。

间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”。

InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

锁定一行

for update是一种行级锁，又叫排它锁，一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行．如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁．即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。行锁永远是独占方式锁。

总结

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MylISAM相比就会有比较明显的优势了。

但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

如何分析行锁定

1
2
3
4
5
6
7
8
9
10
11
mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 223670 |
| Innodb_row_lock_time_avg      | 27958  |
| Innodb_row_lock_time_max      | 51460  |
| Innodb_row_lock_waits         | 8      |
+-------------------------------+--------+
5 rows in set (0.00 sec)

对各个状态量的说明如下:

  • Innodb_row_lock_current_waits：当前正在等待锁定的数量；
  • Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
  • Innodb_row_lock_time_avg：每次等待所花平均时间；
  • Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
  • Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

优化建议

  • 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能较少检索条件，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度
  • 尽可能低级别事务隔离

博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议

评论