MySQL高级③（1.查询截取分析，2.MySQL锁机制，3.主从复制）

博主： Luo
发布时间：2021 年 02 月 28 日
459次浏览
2 条评论
57668字数
分类：数据库有关

MySQL高级③

该文档是：MySQL高级学习
博客连接：https://www.loveuluo.cn
日期：2020-02-28

四、查询截取分析

1、查询优化

1.1、MySQL 优化原则

mysql 的调优大纲

慢查询的开启并捕获
explain+慢SQL分析
show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
SQL数据库服务器的参数调优

永远小表驱动大表，类似嵌套循环 Nested Loop

EXISTS 语法：
- SELECT ... FROM table WHERE EXISTS(subquery[代表子查询])
- 该语法可以理解为：将查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。
EXISTS(subquery) 只返回TRUE或FALSE，因此子查询中的SELECT *也可以是SELECT 1或其他，官方说法是实际执行时会忽略SELECT清单，因此没有区别
EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析

结论：

永远记住小表驱动大表
当 B 表数据集小于 A 表数据集时，使用 in
当 A 表数据集小于 B 表数据集时，使用 exist

in 和 exists 的用法

tbl_emp 表和 tbl_dept 表

mysql> select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
|  8 | s9   |     51 |
+----+------+--------+
8 rows in set (0.00 sec)

mysql> select * from tbl_dept;
 ±—±---------±-------+
 | id | deptName | locAdd |
 ±—±---------±-------+
 |  1 | RD       | 11     |
 |  2 | HR       | 12     |
 |  3 | MK       | 13     |
 |  4 | MIS      | 14     |
 |  5 | FD       | 15     |
 ±—±---------±-------+
 5 rows in set (0.00 sec)

in 的写法

mysql> select * from tbl_emp e where e.deptId in (select id from tbl_dept);
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
+----+------+--------+
7 rows in set (0.00 sec)

exists 的写法

mysql> select * from tbl_emp e where exists (select 1 from tbl_dept d where e.deptId = d.id);
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
+----+------+--------+
7 rows in set (0.00 sec)

1.2、ORDER BY 优化

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序

创建表

建表 SQL

create table tblA(
    #id int primary key not null auto_increment,
    age int,
    birth timestamp not null
);

insert into tblA(age, birth) values(22, now());
 insert into tblA(age, birth) values(23, now());
 insert into tblA(age, birth) values(24, now());

create index idx_A_ageBirth on tblA(age, birth);

tblA 表中的测试数据

mysql> select * from tblA;
+------+---------------------+
| age  | birth               |
+------+---------------------+
|   22 | 2020-08-05 10:36:32 |
|   23 | 2020-08-05 10:36:32 |
|   24 | 2020-08-05 10:36:32 |
+------+---------------------+
3 rows in set (0.00 sec)

tbl 中的索引

mysql> SHOW INDEX FROM tblA;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tblA  |          1 | idx_A_ageBirth |            1 | age         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| tblA  |          1 | idx_A_ageBirth |            2 | birth       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

CASE1：能使用索引进行排序的情况

只有带头大哥 age

mysql> EXPLAIN SELECT * FROM tblA where age>20 order by age;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT * FROM tblA where birth>‘2016-01-28 00:00:00’ order by age;
 ±—±------------±------±------±--------------±---------------±--------±-----±-----±-------------------------+
 | id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra                    |
 ±—±------------±------±------±--------------±---------------±--------±-----±-----±-------------------------+
 |  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index |
 ±—±------------±------±------±--------------±---------------±--------±-----±-----±-------------------------+
 1 row in set (0.00 sec)

带头大哥 age + 小弟 birth

mysql> EXPLAIN SELECT * FROM tblA where age>20 order by age,birth;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql 默认升序排列，全升序或者全降序，都扛得住

mysql> EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth ASC;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
|  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM tblA ORDER BY age DESC, birth DESC;
 ±—±------------±------±------±--------------±---------------±--------±-----±-----±------------+
 | id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra       |
 ±—±------------±------±------±--------------±---------------±--------±-----±-----±------------+
 |  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using index |
 ±—±------------±------±------±--------------±---------------±--------±-----±-----±------------+
 1 row in set (0.01 sec)

CASE2：不能使用索引进行排序的情况

带头大哥 age 挂了

mysql> EXPLAIN SELECT * FROM tblA where age>20 order by birth;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.01 sec)

小弟 birth 居然敢在带头大哥 age 前面

mysql> EXPLAIN SELECT * FROM tblA where age>20 order by birth,age;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql 默认升序排列，如果全升序或者全降序，都 ok ，但是一升一降 mysql 就扛不住了

mysql> EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth DESC;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

结论

MySQL支持二种方式的排序，FileSort和Index，Index效率高，它指MySQL扫描索引本身完成排序，FileSort方式效率较低。
ORDER BY满足两情况（最佳左前缀原则），会使用Index方式排序
- ORDER BY语句使用索引最左前列
- 使用where子句与OrderBy子句条件列组合满足索引最左前列
尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

如果未在索引列上完成排序，mysql 会启动 filesort 的两种算法：双路排序和单路排序

双路排序
- MySQL4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据。读取行指针和将要进行orderby操作的列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输
- 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
单路排序
- 取一批数据，要对磁盘进行两次扫描，众所周知，I/O是很耗时的，所以在mysql4.1之后，出现了改进的算法，就是单路排序。
- 从磁盘读取查询需要的所有列，按照将要进行orderby的列，在sort buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
结论及引申出的问题：
- 由于单路是改进的算法，总体而言好过双路
- 在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排…… 从而会导致多次I/O。
- 结论：本来想省一次I/O操作，反而导致了大量的/O操作，反而得不偿失。
更深层次的优化策略：
- 增大sort_buffer_size参数的设置
- 增大max_length_for_sort_data参数的设置

遵循如下规则，可提高Order By的速度

Order by时select *是一个大忌，只Query需要的字段，这点非常重要。在这里的影响是：
- 当Query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT|BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。
- 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。
尝试提高 sort_buffer_size不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
尝试提高max_length_for_sort_data提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

Order By 排序索引优化的总结

1.3、GROUP BY 优化

group by关键字优化

group by实质是先排序后进行分组，遵照索引的最佳左前缀
当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
where高于having，能写在where限定的条件就不要去having限定了
其余的规则均和 order by 一致

2、慢查询日志

2.1、慢查询日志介绍

慢查询日志是什么？

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
long_query_time的默认值为10，意思是运行10秒以上的SQL语句会被记录下来
由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

2.2、慢查询日志开启

怎么玩？

说明：

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件

查看是否开启及如何开启

查看慢查询日志是否开启：
- 默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的
- 可以通过设置slow_query_log的值来开启
- 通过SHOW VARIABLES LIKE '%slow_query_log%';查看 mysql 的慢查询日志是否开启

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | OFF                           |
| slow_query_log_file | /var/lib/mysql/Heygo-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)

如何开启开启慢查询日志：
- set global slow_query_log = 1;开启慢查询日志
- 使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。

mysql> set global slow_query_log = 1;
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW VARIABLES LIKE ‘%slow_query_log%’;
 ±--------------------±------------------------------+
 | Variable_name       | Value                         |
 ±--------------------±------------------------------+
 | slow_query_log      | ON                            |
 | slow_query_log_file | /var/lib/mysql/Heygo-slow.log |
 ±--------------------±------------------------------+
 2 rows in set (0.00 sec)

如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）
- 修改my.cnf文件，[mysqld]下增加或修改参数：slow_query_log和slow_query_log_file后，然后重启MySQL服务器。
- 也即将如下两行配置进my.cnf文件
```
[mysqld]
slow_query_log =1
slow_query_log_file=/var/lib/mysql/Heygo-slow.log
```
- 关于慢查询的参数slow_query_log_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log（如果没有指定参数slow_query_log_file的话）

那么开启慢查询日志后，什么样的SQL参会记录到慢查询里面？

这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，命令：SHOW VARIABLES LIKE 'long_query_time%';查看慢 SQL 的阈值

mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

可以使用命令修改，也可以在my.cnf参数里面修改。
假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。

2.3、慢查询日志示例

案例讲解

查看慢 SQL 的阈值时间，默认阈值时间为 10s

mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

设置慢 SQL 的阈值时间，我们将其设置为 3s

mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

为什么设置后阈值时间没变？
- 需要重新连接或者新开一个回话才能看到修改值。
- 查看全局的 long_query_time 值：show global variables like 'long_query_time';发现已经生效

mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE ‘long_query_time%’;
 ±----------------±----------+
 | Variable_name   | Value     |
 ±----------------±----------+
 | long_query_time | 10.000000 |
 ±----------------±----------+
 1 row in set (0.00 sec)

mysql> show global variables like ‘long_query_time’;
 ±----------------±---------+
 | Variable_name   | Value    |
 ±----------------±---------+
 | long_query_time | 3.000000 |
 ±----------------±---------+
 1 row in set (0.00 sec)

记录慢 SQL 以供后续分析

怼个 select sleep(4); 超过 3s ，肯定会被记录到日志中

mysql> select sleep(4); 
+----------+
| sleep(4) |
+----------+
|        0 |
+----------+
1 row in set (4.00 sec)

慢查询日志文件在 /var/lib/mysql/ 下，后缀为 -slow.log

[root@Heygo mysql]# cd /var/lib/mysql/
[root@Heygo mysql]# ls -l
总用量 176156
-rw-rw----. 1 mysql mysql       56 8月   3 19:08 auto.cnf
drwx------. 2 mysql mysql     4096 8月   5 10:36 db01
-rw-rw----. 1 mysql mysql     7289 8月   3 22:38 Heygo.err
-rw-rw----. 1 mysql mysql      371 8月   5 12:58 Heygo-slow.log
-rw-rw----. 1 mysql mysql 79691776 8月   5 10:36 ibdata1
-rw-rw----. 1 mysql mysql 50331648 8月   5 10:36 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 8月   3 19:08 ib_logfile1
drwx------. 2 mysql mysql     4096 8月   3 19:08 mysql
srwxrwxrwx. 1 mysql mysql        0 8月   3 22:38 mysql.sock
drwx------. 2 mysql mysql     4096 8月   3 19:08 performance_schema

查看慢查询日志中的内容

[root@Heygo mysql]# cat Heygo-slow.log 
/usr/sbin/mysqld, Version: 5.6.49 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 200805 12:58:01
# User@Host: root[root] @ localhost []  Id:    11
# Query_time: 4.000424  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1596603481;
select sleep(4);

查询当前系统中有多少条慢查询记录：show global status like '%Slow_queries%';

mysql> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+
1 row in set (0.00 sec)

配置版的慢查询日志

在 /etc/my.cnf 文件的 [mysqld] 节点下配置

slow_query_log=1；
slow_query_log_file=/var/lib/mysql/Heygo-slow.log 
long_query_time=3；
log_output=FILE

日志分析命令 mysqldumpslow

mysqldumpslow是什么？

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

查看 mysqldumpslow的帮助信息

[root@Heygo mysql]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

–verbose    verbose
 –debug      debug
 –help       write this text to standard output

-v           verbose
 -d           debug
 -s ORDER     what to sort by (al, at, ar, c, l, r, t), ‘at’ is default
 al: average lock time
 ar: average rows sent
 at: average query time
 c: count
 l: lock time
 r: rows sent
 t: query time
 -r           reverse the sort order (largest last instead of first)
 -t NUM       just show the top n queries
 -a           don’t abstract all numbers to N and strings to ‘S’
 -n NUM       abstract numbers with at least n digits within names
 -g PATTERN   grep: only consider stmts that include this string
 -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
 default is '*’, i.e. match all
 -i NAME      name of server instance (if using mysql.server startup script)
 -l           don’t subtract lock time from total time

mysqldumpshow 参数解释

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

常用参数手册

得到返回记录集最多的10个SQL

mysqldumpslow -s r -t 10 /var/lib/mysql/Heygo-slow.log

得到访问次数最多的10个SQL

mysqldumpslow -s c- t 10/var/lib/mysql/Heygo-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句
```
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/Heygo-slow.log
```
另外建议在使用这些命令时结合 | 和more使用，否则有可能出现爆屏情况
```
mysqldumpslow -s r -t 10 /var/lib/mysql/Heygo-slow.log | more
```

3、批量数据脚本

创建表

建表 SQL

CREATE TABLE dept
(
    deptno int unsigned primary key auto_increment,
    dname varchar(20) not null default "",
    loc varchar(8) 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;

设置参数

创建函数，假如报错：This function has none of DETERMINISTIC………

由于开启过慢查询日志，因为我们开启了bin-log，我们就必须为我们的function指定一个参数。

log_bin_trust_function_creators = OFF ，默认必须为 function 传递一个参数

mysql> show variables like 'log_bin_trust_function_creators'; 
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

通过 set global log_bin_trust_function_creators=1;我们可以不用为 function 传参

mysql> set global log_bin_trust_function_creators=1; 
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ‘log_bin_trust_function_creators’;
 ±--------------------------------±------+
 | Variable_name                   | Value |
 ±--------------------------------±------+
 | log_bin_trust_function_creators | ON    |
 ±--------------------------------±------+
 1 row in set (0.00 sec)

这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法在配置文件中修改‘

windows下：my.ini --> [mysqld] 节点下加上 log_bin_trust_function_creators=1
linux下：/etc/my.cnf --> [mysqld] 节点下加上 log_bin_trust_function_creators=1

创建函数，保证每条数据都不同

随机产生字符串的函数

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 $$

随机产生部门编号的函数

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

创建存储过程

创建往emp表中插入数据的存储过程

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 $$

创建往dept表中插入数据的存储过程

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 $$

调用存储过程

向 dept 表中插入 10 条记录

DELIMITER ;
CALL insert_dept(100, 10);

mysql> select * from dept;
+--------+---------+--------+
| deptno | dname   | loc    |
+--------+---------+--------+
|    101 | aowswej | syrlhb |
|    102 | uvneag  | pup    |
|    103 | lps     | iudgy  |
|    104 | jipvsk  | ihytx  |
|    105 | hrpzhiv | vjb    |
|    106 | phngy   | yf     |
|    107 | uhgd    | lgst   |
|    108 | ynyl    | iio    |
|    109 | daqbgsh | mp     |
|    110 | yfbrju  | vuhsf  |
+--------+---------+--------+
10 rows in set (0.00 sec)

向 emp 表中插入 50w 条记录

DELIMITER ;
CALL insert_emp(100001, 500000);

mysql> select * from emp limit 20;
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| id | empno  | ename | job      | mgr | hiredate   | sal     | comm   | deptno |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
|  1 | 100002 | ipbmd | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    101 |
|  2 | 100003 | bfvt  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    107 |
|  3 | 100004 |       | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    109 |
|  4 | 100005 | cptas | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    101 |
|  5 | 100006 | ftn   | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    108 |
|  6 | 100007 | gzh   | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    102 |
|  7 | 100008 | rji   | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    100 |
|  8 | 100009 |       | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    106 |
|  9 | 100010 | tms   | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    100 |
| 10 | 100011 | utxe  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    101 |
| 11 | 100012 | vbis  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    104 |
| 12 | 100013 | qgfv  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    104 |
| 13 | 100014 | wrvb  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    105 |
| 14 | 100015 | dyks  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    109 |
| 15 | 100016 | hpcs  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    101 |
| 16 | 100017 | fxb   | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    108 |
| 17 | 100018 | vqxq  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    102 |
| 18 | 100019 | rq    | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    102 |
| 19 | 100020 | l     | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    106 |
| 20 | 100021 | lk    | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    100 |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
20 rows in set (0.00 sec)

4、Show Profile

Show Profile 是什么？

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量
官网：http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
默认情况下，参数处于关闭状态，并保存最近15次的运行结果

分析步骤

查看是当前的SQL版本是否支持Show Profile

show variables like ‘profiling%’; 查看 Show Profile 是否开启

mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set (0.01 sec)

开启功能 Show Profile ，默认是关闭，使用前需要开启

set profiling=on; 开启 Show Profile

mysql> set profiling=on; 
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like ‘profiling%’;
 ±-----------------------±------+
 | Variable_name          | Value |
 ±-----------------------±------+
 | profiling              | ON    |
 | profiling_history_size | 15    |
 ±-----------------------±------+
 2 rows in set (0.00 sec)

运行SQL

正常 SQL

select * from tbl_emp;
select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id;
select * from tbl_emp e left join tbl_dept d on e.deptId = d.id;

慢 SQL

select * from emp group by id%10 limit 150000;
select * from emp group by id%10 limit 150000;
select * from emp group by id%20 order by 5;

查看结果

通过 show profiles; 指令查看结果

mysql> show profiles;
+----------+------------+----------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                |
+----------+------------+----------------------------------------------------------------------+
|        1 | 0.00052700 | show variables like 'profiling%'                                     |
|        2 | 0.00030300 | select * from tbl_emp                                                |
|        3 | 0.00010650 | select * from tbl_emp e inner join tbl_dept d on e.'deptId' = d.'id' |
|        4 | 0.00031625 | select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id     |
|        5 | 0.00042100 | select * from tbl_emp e left join tbl_dept d on e.deptId = d.id      |
|        6 | 0.38621875 | select * from emp group by id%20 limit 150000                        |
|        7 | 0.00014900 | select * from emp group by id%20 order by 150000                     |
|        8 | 0.38649000 | select * from emp group by id%20 order by 5                          |
|        9 | 0.06782700 | select COUNT(*) from emp                                             |
|       10 | 0.35434400 | select * from emp group by id%10 limit 150000                        |
+----------+------------+----------------------------------------------------------------------+
10 rows in set, 1 warning (0.00 sec)

诊断SQL

show profile cpu, block io for query SQL编号; 查看 SQL 语句执行的具体流程以及每个步骤花费的时间

mysql> show profile cpu, block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000055 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000024 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000046 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000018 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.000089 | 0.000000 |   0.000000 |            0 |             0 |
| end                  | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)

参数备注：

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

日常开发需要注意的结论

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

举例

5、全局查询日志

永远不要在生产环境开启这个功能。

配置启用全局查询日志

在mysql的my.cnf中，设置如下：

# 开启
general_log=1

# 记录日志文件的路径
general_log_file=/path/logfile

# 输出格式
log_output=FILE

编码启用全局查询日志

执行如下指令开启全局查询日志

set global general_log=1;
set global log_output='TABLE';

此后，你所执行的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看

select * from mysql.general_log;
1
mysql> select * from mysql.general_log;
+---------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------------+
| event_time          | user_host                 | thread_id | server_id | command_type | argument                                      |
+---------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------------+
| 2020-08-05 14:41:07 | root[root] @ localhost [] |        14 |         0 | Query        | select * from emp group by id%10 limit 150000 |
| 2020-08-05 14:41:12 | root[root] @ localhost [] |        14 |         0 | Query        | select COUNT(*) from emp                      |
| 2020-08-05 14:41:30 | root[root] @ localhost [] |        14 |         0 | Query        | select * from mysql.general_log               |
+---------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------------+
3 rows in set (0.00 sec)

五、MySQL 锁机制

1、概述

1.1、锁的定义

锁的定义

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

1.2、锁的分类

锁的分类

从数据操作的类型（读、写）分
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响，加了后只读不能修改
- 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
从对数据操作的颗粒度
- 表锁
- 行锁

2、表锁

表锁的特点

偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发最低

2.1、表锁案例

表锁案例分析

创建表

建表 SQL：引擎选择 myisam

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;

mylock 表中的测试数据

mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

手动加锁和释放锁

查看当前数据库中表的上锁情况：show open tables;，0 表示未上锁

mysql> show open tables;
+--------------------+----------------------------------------------------+--------+-------------+
| Database           | Table                                              | In_use | Name_locked |
+--------------------+----------------------------------------------------+--------+-------------+
| performance_schema | events_waits_history                               |      0 |           0 |
| performance_schema | events_waits_summary_global_by_event_name          |      0 |           0 |
| performance_schema | setup_timers                                       |      0 |           0 |
| performance_schema | events_waits_history_long                          |      0 |           0 |
| performance_schema | events_statements_summary_by_digest                |      0 |           0 |
| performance_schema | mutex_instances                                    |      0 |           0 |
| performance_schema | events_waits_summary_by_instance                   |      0 |           0 |
| performance_schema | events_stages_history                              |      0 |           0 |
| mysql              | db                                                 |      0 |           0 |
| performance_schema | events_waits_summary_by_host_by_event_name         |      0 |           0 |
| mysql              | user                                               |      0 |           0 |
| mysql              | columns_priv                                       |      0 |           0 |
| performance_schema | events_statements_history_long                     |      0 |           0 |
| performance_schema | performance_timers                                 |      0 |           0 |
| performance_schema | file_instances                                     |      0 |           0 |
| performance_schema | events_stages_summary_by_user_by_event_name        |      0 |           0 |
| performance_schema | events_stages_history_long                         |      0 |           0 |
| performance_schema | setup_actors                                       |      0 |           0 |
| performance_schema | cond_instances                                     |      0 |           0 |
| mysql              | proxies_priv                                       |      0 |           0 |
| performance_schema | socket_summary_by_instance                         |      0 |           0 |
| performance_schema | events_statements_current                          |      0 |           0 |
| mysql              | event                                              |      0 |           0 |
| performance_schema | session_connect_attrs                              |      0 |           0 |
| mysql              | plugin                                             |      0 |           0 |
| performance_schema | threads                                            |      0 |           0 |
| mysql              | time_zone_transition_type                          |      0 |           0 |
| mysql              | time_zone_name                                     |      0 |           0 |
| performance_schema | file_summary_by_event_name                         |      0 |           0 |
| performance_schema | events_waits_summary_by_user_by_event_name         |      0 |           0 |
| performance_schema | socket_summary_by_event_name                       |      0 |           0 |
| performance_schema | users                                              |      0 |           0 |
| mysql              | servers                                            |      0 |           0 |
| performance_schema | events_waits_summary_by_account_by_event_name      |      0 |           0 |
| db01               | tbl_emp                                            |      0 |           0 |
| performance_schema | events_statements_summary_by_host_by_event_name    |      0 |           0 |
| db01               | tblA                                               |      0 |           0 |
| performance_schema | table_io_waits_summary_by_index_usage              |      0 |           0 |
| performance_schema | events_waits_current                               |      0 |           0 |
| db01               | user                                               |      0 |           0 |
| mysql              | procs_priv                                         |      0 |           0 |
| performance_schema | events_statements_summary_by_thread_by_event_name  |      0 |           0 |
| db01               | emp                                                |      0 |           0 |
| db01               | tbl_user                                           |      0 |           0 |
| db01               | test03                                             |      0 |           0 |
| mysql              | slow_log                                           |      0 |           0 |
| performance_schema | file_summary_by_instance                           |      0 |           0 |
| db01               | article                                            |      0 |           0 |
| performance_schema | objects_summary_global_by_type                     |      0 |           0 |
| db01               | phone                                              |      0 |           0 |
| performance_schema | events_waits_summary_by_thread_by_event_name       |      0 |           0 |
| performance_schema | setup_consumers                                    |      0 |           0 |
| performance_schema | socket_instances                                   |      0 |           0 |
| performance_schema | rwlock_instances                                   |      0 |           0 |
| db01               | tbl_dept                                           |      0 |           0 |
| performance_schema | events_statements_summary_by_user_by_event_name    |      0 |           0 |
| db01               | staffs                                             |      0 |           0 |
| db01               | class                                              |      0 |           0 |
| mysql              | general_log                                        |      0 |           0 |
| performance_schema | events_stages_summary_global_by_event_name         |      0 |           0 |
| performance_schema | events_stages_summary_by_account_by_event_name     |      0 |           0 |
| performance_schema | events_statements_summary_by_account_by_event_name |      0 |           0 |
| performance_schema | table_lock_waits_summary_by_table                  |      0 |           0 |
| performance_schema | hosts                                              |      0 |           0 |
| performance_schema | setup_objects                                      |      0 |           0 |
| performance_schema | events_stages_current                              |      0 |           0 |
| mysql              | time_zone                                          |      0 |           0 |
| mysql              | tables_priv                                        |      0 |           0 |
| performance_schema | table_io_waits_summary_by_table                    |      0 |           0 |
| mysql              | time_zone_leap_second                              |      0 |           0 |
| db01               | book                                               |      0 |           0 |
| performance_schema | session_account_connect_attrs                      |      0 |           0 |
| db01               | mylock                                             |      0 |           0 |
| mysql              | func                                               |      0 |           0 |
| performance_schema | events_statements_summary_global_by_event_name     |      0 |           0 |
| performance_schema | events_statements_history                          |      0 |           0 |
| performance_schema | accounts                                           |      0 |           0 |
| mysql              | time_zone_transition                               |      0 |           0 |
| db01               | dept                                               |      0 |           0 |
| performance_schema | events_stages_summary_by_host_by_event_name        |      0 |           0 |
| performance_schema | events_stages_summary_by_thread_by_event_name      |      0 |           0 |
| mysql              | proc                                               |      0 |           0 |
| performance_schema | setup_instruments                                  |      0 |           0 |
| performance_schema | host_cache                                         |      0 |           0 |
+--------------------+----------------------------------------------------+--------+-------------+
84 rows in set (0.00 sec)

添加锁(read读锁，write写锁)

lock table 表名1 read/write, 表名2 read/write, ...;

释放表锁

unlock tables;

2.1.1、读锁示例

读锁示例

结论

当前 session 和其他 session 均可以读取加了读锁的表
当前 session 不能读取其他表，并且不能修改加了读锁的表
其他 session 想要修改加了读锁的表，必须等待其读锁释放

2.1.2、写锁示例

写锁示例

结论

当前 session 可以读取和修改加了写锁的表
当前 session 不能读取其他表
其他 session 想要读取加了写锁的表，必须等待其读锁释放

案例结论

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
MySQL的表级锁有两种模式：
- 表共享读锁（Table Read Lock）
- 表独占写锁（Table Write Lock）

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

对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作
简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

2.2、表锁分析

表锁分析

查看哪些表被锁了，0 表示未锁，1 表示被锁

show open tables;

【如何分析表锁定】可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定，通过 show status like 'table%'; 命令查看

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1；
Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况；

mysql> show status like 'table%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Table_locks_immediate      | 500440 |
| Table_locks_waited         | 1      |
| Table_open_cache_hits      | 500070 |
| Table_open_cache_misses    | 5      |
| Table_open_cache_overflows | 0      |
+----------------------------+--------+
5 rows in set (0.00 sec)

此外，Myisam的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞

3、行锁

行锁的特点

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁

疑问点解答：

如果把自动提交关闭，那么当客户端1修改了第一行不commit的时候，那么客户端2也去改第一行的话会阻塞并且如果读取的话会读到之前的数据。如果客户端2也关闭了自动提交，那么如果此时客户端1commit了，客户端2不commit无法读取到最新数据，以前能读取到是因为默认有自动提交，就算执行查询语句的时候也会自动提交。(InnoDB默认行锁)

3.1、事务复习

行锁支持事务，复习下老知识

事务（Transation）及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
持久性（Durability）：事务院成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务处理带来的问题

更新丢失

（Lost Update）：

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题一一最后的更新覆盖了由其他事务所做的更新。
例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。
如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。

脏读

（Dirty Reads）：

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。
一句话：事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

不可重复读

（Non-Repeatable Reads）：

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性

幻读

（Phantom Reads）

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读一句话：事务A读取到了事务B体提交的新增数据，不符合隔离性。
多说一句：幻读和脏读有点类似，脏读是事务B里面修改了数据，幻读是事务B里面新增了数据。

事物的隔离级别

脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。
数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。
同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
查看当前数据库的事务隔离级别：show variables like 'tx_isolation'; mysql 默认是可重复读

3.2、行锁案例

行锁案例分析

创建表

建表 SQL

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);

test_innodb_lock 表中的测试数据

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | 3    |
|    4 | 4000 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

test_innodb_lock 表中的索引

mysql> SHOW INDEX FROM test_innodb_lock;
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_innodb_lock |          1 | test_innodb_a_ind      |            1 | a           | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_innodb_lock |          1 | test_innodb_lock_b_ind |            1 | b           | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

操作同一行数据

session1 开启事务，修改 test_innodb_lock 中的数据

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b=‘4001’ where a=4;
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1  Changed: 1  Warnings: 0

session2 开启事务，修改 test_innodb_lock 中同一行数据，将导致 session2 发生阻塞，一旦 session1 提交事务，session2 将执行更新操作

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b=‘4002’ where a=4;

# 在这儿阻塞着呢~~~

# 时间太长，会报超时错误哦

mysql> update test_innodb_lock set b=‘4001’ where a=4;
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

操作不同行数据

session1 开启事务，修改 test_innodb_lock 中的数据

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b=‘4001’ where a=4;
 Query OK, 0 rows affected (0.00 sec)
 Rows matched: 1  Changed: 0  Warnings: 0

session2 开启事务，修改 test_innodb_lock 中不同行的数据
由于采用行锁，session2 和 session1 互不干涉，所以 session2 中的修改操作没有阻塞

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b=‘9001’ where a=9;
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1  Changed: 1  Warnings: 0

无索引导致行锁升级为表锁

session1 开启事务，修改 test_innodb_lock 中的数据，varchar 不用 ’ ’ ，导致系统自动转换类型，导致索引失效

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set a=44 where b=4000;
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1  Changed: 1  Warnings: 0

session2 开启事务，修改 test_innodb_lock 中不同行的数据
由于发生了自动类型转换，索引失效，导致行锁变为表锁

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b=‘9001’ where a=9;

# 在这儿阻塞着呢~~~

3.3、间隙锁

什么是间隙锁

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

间隙锁的危害

因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

间隙锁示例

test_innodb_lock 表中的数据

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | 3    |
|    4 | 4000 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

session1 开启事务，执行修改 a > 1 and a < 6 的数据，这会导致 mysql 将 a = 2 的数据行锁住（虽然表中并没有这行数据）

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b=‘Heygo’ where a>1 and a<6;
 Query OK, 3 rows affected (0.00 sec)
 Rows matched: 3  Changed: 3  Warnings: 0

session2 开启事务，修改 test_innodb_lock 中不同行的数据，也会导致阻塞，直至 session1 提交事务

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b=‘9001’ where a=9;

# 在这儿阻塞着呢~~~

3.4、手动行锁(悲观锁)

如何锁定一行

select xxx ... for update 锁定某一行后，其它的操作会被阻塞，直到锁定行的会话提交
session1 开启事务，手动执行 for update 锁定指定行，待执行完指定操作时再将数据提交

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_innodb_lock  where a=8 for update;
 ±-----±-----+
 | a    | b    |
 ±-----±-----+
 |    8 | 8000 |
 ±-----±-----+
 1 row in set (0.00 sec)

session2 开启事务，修改 session1 中被锁定的行，会导致阻塞，直至 session1 提交事务

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b=‘XXX’ where a=8;

# 在这儿阻塞着呢~~~

3.5、行锁分析

案例结论

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。
当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。
但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候（索引失效，导致行锁变表锁），可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

行锁分析

如何分析行锁定

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';

mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 212969 |
| Innodb_row_lock_time_avg      | 42593  |
| Innodb_row_lock_time_max      | 51034  |
| Innodb_row_lock_waits         | 5      |
+-------------------------------+--------+
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：系统启动后到现在总共等待的次数；

对于这5个状态变量，比较重要的主要是

Innodb_row_lock_time_avg（等待平均时长）
Innodb_row_lock_waits（等待总次数）
Innodb_row_lock_time（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

3.6、行锁优化

优化建议

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

4、页锁

开销和加锁时间界于表锁和行锁之间：会出现死锁；
锁定粒度界于表锁和行锁之间，并发度一般。
了解即可

六、主从复制

1、复制的基本原理

复制的基本原理

slave会从master读取binlog来进行数据同步，主从复制的三步骤

master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件（binary log events）
slave将master的binary log events拷贝到它的中继日志（relay log）
slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的

2、复制的基本原则

每个slave只有一个master
每个slave只能有一个唯一的服务器ID
每个master可以有多个salve

3、复制最大问题

因为发生多次 IO，存在延时问题

4、一主一从常见配置

前提：mysql 版本一致，主从机在同一网段下

ping 测试

Linux 中 ping Windows

[root@Heygo 桌面]# ping 10.206.207.131
PING 10.206.207.131 (10.206.207.131) 56(84) bytes of data.
64 bytes from 10.206.207.131: icmp_seq=1 ttl=128 time=1.27 ms
64 bytes from 10.206.207.131: icmp_seq=2 ttl=128 time=0.421 ms
64 bytes from 10.206.207.131: icmp_seq=3 ttl=128 time=1.12 ms
64 bytes from 10.206.207.131: icmp_seq=4 ttl=128 time=0.515 ms
^C
--- 10.206.207.131 ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3719ms
rtt min/avg/max/mdev = 0.421/0.835/1.279/0.373 ms
[root@Heygo 桌面]#

Windows 中 ping Linux

C:\Users\Heygo>ping 192.168.152.129

正在 Ping 192.168.152.129 具有 32 字节的数据:
 来自 192.168.152.129 的回复: 字节=32 时间<1ms TTL=64
 来自 192.168.152.129 的回复: 字节=32 时间<1ms TTL=64
 来自 192.168.152.129 的回复: 字节=32 时间=1ms TTL=64
 来自 192.168.152.129 的回复: 字节=32 时间<1ms TTL=64

192.168.152.129 的 Ping 统计信息:
 数据包: 已发送 = 4，已接收 = 4，丢失 = 0 (0% 丢失)，
 往返行程的估计时间(以毫秒为单位):
 最短 = 0ms，最长 = 1ms，平均 = 0ms

主机修改 my.ini 配置文件（Windows）

主从都配置都在 [mysqld] 节点下，都是小写，以下是老师的配置文件

以下两条为必须配置

配置主机 id

server-id=1

启用二进制日志

log-bin=C:/Program Files (x86)/MySQL/MySQL Server 5.5/log-bin/mysqlbin

以下为非必须配置

启动错误日志

log-err=C:/Program Files (x86)/MySQL/MySQL Server 5.5/log-bin/mysqlerr

basedir="C:/Program Files (x86)/MySQL/MySQL Server 5.5/"

tmpdir="C:/Program Files (x86)/MySQL/MySQL Server 5.5/"

datadir="C:/Program Files (x86)/MySQL/MySQL Server 5.5/Data/"

主机，读写都可以

read-only=0

设置不要复制的数据库

binlog-ignore-db=mysql

设置需要复制的数据

binlog-do-db=需要复制的主数据库名字

从机修改 my.cnf 配置文件（Linux）

【必须】从服务器唯一ID

server-id=2

【可选】启用二进制文件

修改配置文件后的准备工作

因修改过配置文件，主机+从机都重启 mysql 服务

Windows

net stop mysql
net start mysql

Linux

service mysqld restart

主机从机都关闭防火墙

Windows 手动关闭防火墙
关闭虚拟机 linux 防火墙

service iptables stop

在 Windows 主机上简历账户并授权 slave

创建用户，并授权

GRANT REPLICATION SLAVE ON *.* TO '备份账号'@'从机器数据库 IP' IDENTIFIED BY '账号密码';
GRANT REPLICATION SLAVE ON *.* TO 'Heygo'@'192.168.152.129' IDENTIFIED BY '123456';

刷新权限信息

flush privileges;
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查询 master 的状态，将 File 和 Position 记录下来，在启动 Slave 时需要用到这两个参数

show master status;
1
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | mysql        |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

在 Linux 从机上配置需要复制的主机

从机进行认证

CHANGE MASTER TO 
MASTER_HOST='主机 IP',
MASTER_USER='创建用户名',
MASTER_PASSWORD='创建的密码',
MASTER_LOG_FILE='File 名字',
MASTER_LOG_POS=Position数字;

CHANGE MASTER TO 
MASTER_HOST='10.206.207.131',
MASTER_USER='Heygo',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;

启动从服务器复制功能

start slave;

查看从机复制功能是否启动成功：Slave_SQL_Running:Yes 和 Slave_IO_Running:Yes 说明从机连接主机成功

show slave status\G;

如何停止从服务复制功能

stop slave;

最后修改：2021 年 02 月 28 日 09 : 25 PM

如果觉得我的文章对你有用，请随意赞赏

2 条评论

站元素主机
July 2nd, 2021 at 09:53 am

感谢分享赞一个

回复
云图床
May 31st, 2021 at 02:08 pm

谢谢博主的分享，不过我看博主的文章图片是储存在本地的，不如把网站图片托管到云图床这样能节省网站的压力，云图床免费空间、免费流量、服务器放在国内速度不错、主要稳定，云图床非常受国内摄影师、动漫爱好者、站长、等各行业人士喜欢，云图床域名在国内正规备案放心使用云图床地址：https://cloudimge.com/

回复

发表评论取消回复

评论 *

私密评论

名称 *

邮箱 *

地址

站元素主机
感谢分享赞一个
云图床
谢谢博主的分享，不过我看博主的文章图片是储存在本地的，不如把...
测试
滴！学生卡！打卡时间：下午7:30:47，请上车的乘客系好安全带~
垃圾的猴子
啊这啊这
Luo
大佬又发文章了好屌

MySQL高级③（1.查询截取分析，2.MySQL锁机制，3.主从复制）

Luo • 2021 年 02 月 28 日

MySQL高级③

该文档是：MySQL高级学习
博客连接：https://www.loveuluo.cn
日期：2020-02-28

四、查询截取分析

1、查询优化

1.1、MySQL 优化原则

mysql 的调优大纲

慢查询的开启并捕获
explain+慢SQL分析
show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
SQL数据库服务器的参数调优

永远小表驱动大表，类似嵌套循环 Nested Loop

EXISTS 语法：
- SELECT ... FROM table WHERE EXISTS(subquery[代表子查询])
- 该语法可以理解为：将查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。
EXISTS(subquery) 只返回TRUE或FALSE，因此子查询中的SELECT *也可以是SELECT 1或其他，官方说法是实际执行时会忽略SELECT清单，因此没有区别
EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析

结论：

永远记住小表驱动大表
当 B 表数据集小于 A 表数据集时，使用 in
当 A 表数据集小于 B 表数据集时，使用 exist

in 和 exists 的用法

tbl_emp 表和 tbl_dept 表

mysql> select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
|  8 | s9   |     51 |
+----+------+--------+
8 rows in set (0.00 sec)

mysql> select * from tbl_dept;
 ±—±---------±-------+
 | id | deptName | locAdd |
 ±—±---------±-------+
 |  1 | RD       | 11     |
 |  2 | HR       | 12     |
 |  3 | MK       | 13     |
 |  4 | MIS      | 14     |
 |  5 | FD       | 15     |
 ±—±---------±-------+
 5 rows in set (0.00 sec)

in 的写法

mysql> select * from tbl_emp e where e.deptId in (select id from tbl_dept);
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
+----+------+--------+
7 rows in set (0.00 sec)

exists 的写法

mysql> select * from tbl_emp e where exists (select 1 from tbl_dept d where e.deptId = d.id);
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
+----+------+--------+
7 rows in set (0.00 sec)

1.2、ORDER BY 优化

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序

创建表

建表 SQL

create table tblA(
    #id int primary key not null auto_increment,
    age int,
    birth timestamp not null
);

insert into tblA(age, birth) values(22, now());
 insert into tblA(age, birth) values(23, now());
 insert into tblA(age, birth) values(24, now());

create index idx_A_ageBirth on tblA(age, birth);

tblA 表中的测试数据

mysql> select * from tblA;
+------+---------------------+
| age  | birth               |
+------+---------------------+
|   22 | 2020-08-05 10:36:32 |
|   23 | 2020-08-05 10:36:32 |
|   24 | 2020-08-05 10:36:32 |
+------+---------------------+
3 rows in set (0.00 sec)

tbl 中的索引

mysql> SHOW INDEX FROM tblA;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tblA  |          1 | idx_A_ageBirth |            1 | age         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| tblA  |          1 | idx_A_ageBirth |            2 | birth       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

CASE1：能使用索引进行排序的情况

只有带头大哥 age

mysql> EXPLAIN SELECT * FROM tblA where age>20 order by age;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT * FROM tblA where birth>‘2016-01-28 00:00:00’ order by age;
 ±—±------------±------±------±--------------±---------------±--------±-----±-----±-------------------------+
 | id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra                    |
 ±—±------------±------±------±--------------±---------------±--------±-----±-----±-------------------------+
 |  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index |
 ±—±------------±------±------±--------------±---------------±--------±-----±-----±-------------------------+
 1 row in set (0.00 sec)

带头大哥 age + 小弟 birth

mysql> EXPLAIN SELECT * FROM tblA where age>20 order by age,birth;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql 默认升序排列，全升序或者全降序，都扛得住

mysql> EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth ASC;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
|  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM tblA ORDER BY age DESC, birth DESC;
 ±—±------------±------±------±--------------±---------------±--------±-----±-----±------------+
 | id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra       |
 ±—±------------±------±------±--------------±---------------±--------±-----±-----±------------+
 |  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using index |
 ±—±------------±------±------±--------------±---------------±--------±-----±-----±------------+
 1 row in set (0.01 sec)

CASE2：不能使用索引进行排序的情况

带头大哥 age 挂了

mysql> EXPLAIN SELECT * FROM tblA where age>20 order by birth;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.01 sec)

小弟 birth 居然敢在带头大哥 age 前面

mysql> EXPLAIN SELECT * FROM tblA where age>20 order by birth,age;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql 默认升序排列，如果全升序或者全降序，都 ok ，但是一升一降 mysql 就扛不住了

mysql> EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth DESC;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

结论

MySQL支持二种方式的排序，FileSort和Index，Index效率高，它指MySQL扫描索引本身完成排序，FileSort方式效率较低。
ORDER BY满足两情况（最佳左前缀原则），会使用Index方式排序
- ORDER BY语句使用索引最左前列
- 使用where子句与OrderBy子句条件列组合满足索引最左前列
尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

如果未在索引列上完成排序，mysql 会启动 filesort 的两种算法：双路排序和单路排序

双路排序
- MySQL4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据。读取行指针和将要进行orderby操作的列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输
- 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
单路排序
- 取一批数据，要对磁盘进行两次扫描，众所周知，I/O是很耗时的，所以在mysql4.1之后，出现了改进的算法，就是单路排序。
- 从磁盘读取查询需要的所有列，按照将要进行orderby的列，在sort buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
结论及引申出的问题：
- 由于单路是改进的算法，总体而言好过双路
- 在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排…… 从而会导致多次I/O。
- 结论：本来想省一次I/O操作，反而导致了大量的/O操作，反而得不偿失。
更深层次的优化策略：
- 增大sort_buffer_size参数的设置
- 增大max_length_for_sort_data参数的设置

遵循如下规则，可提高Order By的速度

Order by时select *是一个大忌，只Query需要的字段，这点非常重要。在这里的影响是：
- 当Query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT|BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。
- 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。
尝试提高 sort_buffer_size不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
尝试提高max_length_for_sort_data提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

Order By 排序索引优化的总结

1.3、GROUP BY 优化

group by关键字优化

group by实质是先排序后进行分组，遵照索引的最佳左前缀
当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
where高于having，能写在where限定的条件就不要去having限定了
其余的规则均和 order by 一致

2、慢查询日志

2.1、慢查询日志介绍

慢查询日志是什么？

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
long_query_time的默认值为10，意思是运行10秒以上的SQL语句会被记录下来
由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

2.2、慢查询日志开启

怎么玩？

说明：

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件

查看是否开启及如何开启

查看慢查询日志是否开启：
- 默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的
- 可以通过设置slow_query_log的值来开启
- 通过SHOW VARIABLES LIKE '%slow_query_log%';查看 mysql 的慢查询日志是否开启

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | OFF                           |
| slow_query_log_file | /var/lib/mysql/Heygo-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)

如何开启开启慢查询日志：
- set global slow_query_log = 1;开启慢查询日志
- 使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。

mysql> set global slow_query_log = 1;
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW VARIABLES LIKE ‘%slow_query_log%’;
 ±--------------------±------------------------------+
 | Variable_name       | Value                         |
 ±--------------------±------------------------------+
 | slow_query_log      | ON                            |
 | slow_query_log_file | /var/lib/mysql/Heygo-slow.log |
 ±--------------------±------------------------------+
 2 rows in set (0.00 sec)

如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）
- 修改my.cnf文件，[mysqld]下增加或修改参数：slow_query_log和slow_query_log_file后，然后重启MySQL服务器。
- 也即将如下两行配置进my.cnf文件
```
[mysqld]
slow_query_log =1
slow_query_log_file=/var/lib/mysql/Heygo-slow.log
```
- 关于慢查询的参数slow_query_log_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log（如果没有指定参数slow_query_log_file的话）

那么开启慢查询日志后，什么样的SQL参会记录到慢查询里面？

这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，命令：SHOW VARIABLES LIKE 'long_query_time%';查看慢 SQL 的阈值

mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

可以使用命令修改，也可以在my.cnf参数里面修改。
假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。

2.3、慢查询日志示例

案例讲解

查看慢 SQL 的阈值时间，默认阈值时间为 10s

mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

设置慢 SQL 的阈值时间，我们将其设置为 3s

mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

为什么设置后阈值时间没变？
- 需要重新连接或者新开一个回话才能看到修改值。
- 查看全局的 long_query_time 值：show global variables like 'long_query_time';发现已经生效

mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE ‘long_query_time%’;
 ±----------------±----------+
 | Variable_name   | Value     |
 ±----------------±----------+
 | long_query_time | 10.000000 |
 ±----------------±----------+
 1 row in set (0.00 sec)

mysql> show global variables like ‘long_query_time’;
 ±----------------±---------+
 | Variable_name   | Value    |
 ±----------------±---------+
 | long_query_time | 3.000000 |
 ±----------------±---------+
 1 row in set (0.00 sec)

记录慢 SQL 以供后续分析

怼个 select sleep(4); 超过 3s ，肯定会被记录到日志中

mysql> select sleep(4); 
+----------+
| sleep(4) |
+----------+
|        0 |
+----------+
1 row in set (4.00 sec)

慢查询日志文件在 /var/lib/mysql/ 下，后缀为 -slow.log

[root@Heygo mysql]# cd /var/lib/mysql/
[root@Heygo mysql]# ls -l
总用量 176156
-rw-rw----. 1 mysql mysql       56 8月   3 19:08 auto.cnf
drwx------. 2 mysql mysql     4096 8月   5 10:36 db01
-rw-rw----. 1 mysql mysql     7289 8月   3 22:38 Heygo.err
-rw-rw----. 1 mysql mysql      371 8月   5 12:58 Heygo-slow.log
-rw-rw----. 1 mysql mysql 79691776 8月   5 10:36 ibdata1
-rw-rw----. 1 mysql mysql 50331648 8月   5 10:36 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 8月   3 19:08 ib_logfile1
drwx------. 2 mysql mysql     4096 8月   3 19:08 mysql
srwxrwxrwx. 1 mysql mysql        0 8月   3 22:38 mysql.sock
drwx------. 2 mysql mysql     4096 8月   3 19:08 performance_schema

查看慢查询日志中的内容

[root@Heygo mysql]# cat Heygo-slow.log 
/usr/sbin/mysqld, Version: 5.6.49 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 200805 12:58:01
# User@Host: root[root] @ localhost []  Id:    11
# Query_time: 4.000424  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1596603481;
select sleep(4);

查询当前系统中有多少条慢查询记录：show global status like '%Slow_queries%';

mysql> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+
1 row in set (0.00 sec)

配置版的慢查询日志

在 /etc/my.cnf 文件的 [mysqld] 节点下配置

slow_query_log=1；
slow_query_log_file=/var/lib/mysql/Heygo-slow.log 
long_query_time=3；
log_output=FILE

日志分析命令 mysqldumpslow

mysqldumpslow是什么？

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

查看 mysqldumpslow的帮助信息

[root@Heygo mysql]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

–verbose    verbose
 –debug      debug
 –help       write this text to standard output

-v           verbose
 -d           debug
 -s ORDER     what to sort by (al, at, ar, c, l, r, t), ‘at’ is default
 al: average lock time
 ar: average rows sent
 at: average query time
 c: count
 l: lock time
 r: rows sent
 t: query time
 -r           reverse the sort order (largest last instead of first)
 -t NUM       just show the top n queries
 -a           don’t abstract all numbers to N and strings to ‘S’
 -n NUM       abstract numbers with at least n digits within names
 -g PATTERN   grep: only consider stmts that include this string
 -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
 default is '*’, i.e. match all
 -i NAME      name of server instance (if using mysql.server startup script)
 -l           don’t subtract lock time from total time

mysqldumpshow 参数解释

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

常用参数手册

得到返回记录集最多的10个SQL

mysqldumpslow -s r -t 10 /var/lib/mysql/Heygo-slow.log

得到访问次数最多的10个SQL

mysqldumpslow -s c- t 10/var/lib/mysql/Heygo-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句
```
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/Heygo-slow.log
```
另外建议在使用这些命令时结合 | 和more使用，否则有可能出现爆屏情况
```
mysqldumpslow -s r -t 10 /var/lib/mysql/Heygo-slow.log | more
```

3、批量数据脚本

创建表

建表 SQL

CREATE TABLE dept
(
    deptno int unsigned primary key auto_increment,
    dname varchar(20) not null default "",
    loc varchar(8) 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;

设置参数

创建函数，假如报错：This function has none of DETERMINISTIC………

由于开启过慢查询日志，因为我们开启了bin-log，我们就必须为我们的function指定一个参数。

log_bin_trust_function_creators = OFF ，默认必须为 function 传递一个参数

mysql> show variables like 'log_bin_trust_function_creators'; 
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

通过 set global log_bin_trust_function_creators=1;我们可以不用为 function 传参

mysql> set global log_bin_trust_function_creators=1; 
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ‘log_bin_trust_function_creators’;
 ±--------------------------------±------+
 | Variable_name                   | Value |
 ±--------------------------------±------+
 | log_bin_trust_function_creators | ON    |
 ±--------------------------------±------+
 1 row in set (0.00 sec)

这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法在配置文件中修改‘

windows下：my.ini --> [mysqld] 节点下加上 log_bin_trust_function_creators=1
linux下：/etc/my.cnf --> [mysqld] 节点下加上 log_bin_trust_function_creators=1

创建函数，保证每条数据都不同

随机产生字符串的函数

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 $$

随机产生部门编号的函数

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

创建存储过程

创建往emp表中插入数据的存储过程

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 $$

创建往dept表中插入数据的存储过程

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 $$

调用存储过程

向 dept 表中插入 10 条记录

DELIMITER ;
CALL insert_dept(100, 10);

mysql> select * from dept;
+--------+---------+--------+
| deptno | dname   | loc    |
+--------+---------+--------+
|    101 | aowswej | syrlhb |
|    102 | uvneag  | pup    |
|    103 | lps     | iudgy  |
|    104 | jipvsk  | ihytx  |
|    105 | hrpzhiv | vjb    |
|    106 | phngy   | yf     |
|    107 | uhgd    | lgst   |
|    108 | ynyl    | iio    |
|    109 | daqbgsh | mp     |
|    110 | yfbrju  | vuhsf  |
+--------+---------+--------+
10 rows in set (0.00 sec)

向 emp 表中插入 50w 条记录

DELIMITER ;
CALL insert_emp(100001, 500000);

mysql> select * from emp limit 20;
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| id | empno  | ename | job      | mgr | hiredate   | sal     | comm   | deptno |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
|  1 | 100002 | ipbmd | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    101 |
|  2 | 100003 | bfvt  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    107 |
|  3 | 100004 |       | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    109 |
|  4 | 100005 | cptas | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    101 |
|  5 | 100006 | ftn   | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    108 |
|  6 | 100007 | gzh   | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    102 |
|  7 | 100008 | rji   | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    100 |
|  8 | 100009 |       | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    106 |
|  9 | 100010 | tms   | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    100 |
| 10 | 100011 | utxe  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    101 |
| 11 | 100012 | vbis  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    104 |
| 12 | 100013 | qgfv  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    104 |
| 13 | 100014 | wrvb  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    105 |
| 14 | 100015 | dyks  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    109 |
| 15 | 100016 | hpcs  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    101 |
| 16 | 100017 | fxb   | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    108 |
| 17 | 100018 | vqxq  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    102 |
| 18 | 100019 | rq    | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    102 |
| 19 | 100020 | l     | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    106 |
| 20 | 100021 | lk    | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    100 |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
20 rows in set (0.00 sec)

4、Show Profile

Show Profile 是什么？

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量
官网：http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
默认情况下，参数处于关闭状态，并保存最近15次的运行结果

分析步骤

查看是当前的SQL版本是否支持Show Profile

show variables like ‘profiling%’; 查看 Show Profile 是否开启

mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set (0.01 sec)

开启功能 Show Profile ，默认是关闭，使用前需要开启

set profiling=on; 开启 Show Profile

mysql> set profiling=on; 
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like ‘profiling%’;
 ±-----------------------±------+
 | Variable_name          | Value |
 ±-----------------------±------+
 | profiling              | ON    |
 | profiling_history_size | 15    |
 ±-----------------------±------+
 2 rows in set (0.00 sec)

运行SQL

正常 SQL

select * from tbl_emp;
select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id;
select * from tbl_emp e left join tbl_dept d on e.deptId = d.id;

慢 SQL

select * from emp group by id%10 limit 150000;
select * from emp group by id%10 limit 150000;
select * from emp group by id%20 order by 5;

查看结果

通过 show profiles; 指令查看结果

mysql> show profiles;
+----------+------------+----------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                |
+----------+------------+----------------------------------------------------------------------+
|        1 | 0.00052700 | show variables like 'profiling%'                                     |
|        2 | 0.00030300 | select * from tbl_emp                                                |
|        3 | 0.00010650 | select * from tbl_emp e inner join tbl_dept d on e.'deptId' = d.'id' |
|        4 | 0.00031625 | select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id     |
|        5 | 0.00042100 | select * from tbl_emp e left join tbl_dept d on e.deptId = d.id      |
|        6 | 0.38621875 | select * from emp group by id%20 limit 150000                        |
|        7 | 0.00014900 | select * from emp group by id%20 order by 150000                     |
|        8 | 0.38649000 | select * from emp group by id%20 order by 5                          |
|        9 | 0.06782700 | select COUNT(*) from emp                                             |
|       10 | 0.35434400 | select * from emp group by id%10 limit 150000                        |
+----------+------------+----------------------------------------------------------------------+
10 rows in set, 1 warning (0.00 sec)

诊断SQL

show profile cpu, block io for query SQL编号; 查看 SQL 语句执行的具体流程以及每个步骤花费的时间

mysql> show profile cpu, block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000055 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000024 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000046 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000018 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.000089 | 0.000000 |   0.000000 |            0 |             0 |
| end                  | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)

参数备注：

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

日常开发需要注意的结论

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

举例

5、全局查询日志

永远不要在生产环境开启这个功能。

配置启用全局查询日志

在mysql的my.cnf中，设置如下：

# 开启
general_log=1

# 记录日志文件的路径
general_log_file=/path/logfile

# 输出格式
log_output=FILE

编码启用全局查询日志

执行如下指令开启全局查询日志

set global general_log=1;
set global log_output='TABLE';

此后，你所执行的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看

select * from mysql.general_log;
1
mysql> select * from mysql.general_log;
+---------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------------+
| event_time          | user_host                 | thread_id | server_id | command_type | argument                                      |
+---------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------------+
| 2020-08-05 14:41:07 | root[root] @ localhost [] |        14 |         0 | Query        | select * from emp group by id%10 limit 150000 |
| 2020-08-05 14:41:12 | root[root] @ localhost [] |        14 |         0 | Query        | select COUNT(*) from emp                      |
| 2020-08-05 14:41:30 | root[root] @ localhost [] |        14 |         0 | Query        | select * from mysql.general_log               |
+---------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------------+
3 rows in set (0.00 sec)

五、MySQL 锁机制

1、概述

1.1、锁的定义

锁的定义

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

1.2、锁的分类

锁的分类

从数据操作的类型（读、写）分
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响，加了后只读不能修改
- 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
从对数据操作的颗粒度
- 表锁
- 行锁

2、表锁

表锁的特点

偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发最低

2.1、表锁案例

表锁案例分析

创建表

建表 SQL：引擎选择 myisam

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;

mylock 表中的测试数据

mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

手动加锁和释放锁

查看当前数据库中表的上锁情况：show open tables;，0 表示未上锁

mysql> show open tables;
+--------------------+----------------------------------------------------+--------+-------------+
| Database           | Table                                              | In_use | Name_locked |
+--------------------+----------------------------------------------------+--------+-------------+
| performance_schema | events_waits_history                               |      0 |           0 |
| performance_schema | events_waits_summary_global_by_event_name          |      0 |           0 |
| performance_schema | setup_timers                                       |      0 |           0 |
| performance_schema | events_waits_history_long                          |      0 |           0 |
| performance_schema | events_statements_summary_by_digest                |      0 |           0 |
| performance_schema | mutex_instances                                    |      0 |           0 |
| performance_schema | events_waits_summary_by_instance                   |      0 |           0 |
| performance_schema | events_stages_history                              |      0 |           0 |
| mysql              | db                                                 |      0 |           0 |
| performance_schema | events_waits_summary_by_host_by_event_name         |      0 |           0 |
| mysql              | user                                               |      0 |           0 |
| mysql              | columns_priv                                       |      0 |           0 |
| performance_schema | events_statements_history_long                     |      0 |           0 |
| performance_schema | performance_timers                                 |      0 |           0 |
| performance_schema | file_instances                                     |      0 |           0 |
| performance_schema | events_stages_summary_by_user_by_event_name        |      0 |           0 |
| performance_schema | events_stages_history_long                         |      0 |           0 |
| performance_schema | setup_actors                                       |      0 |           0 |
| performance_schema | cond_instances                                     |      0 |           0 |
| mysql              | proxies_priv                                       |      0 |           0 |
| performance_schema | socket_summary_by_instance                         |      0 |           0 |
| performance_schema | events_statements_current                          |      0 |           0 |
| mysql              | event                                              |      0 |           0 |
| performance_schema | session_connect_attrs                              |      0 |           0 |
| mysql              | plugin                                             |      0 |           0 |
| performance_schema | threads                                            |      0 |           0 |
| mysql              | time_zone_transition_type                          |      0 |           0 |
| mysql              | time_zone_name                                     |      0 |           0 |
| performance_schema | file_summary_by_event_name                         |      0 |           0 |
| performance_schema | events_waits_summary_by_user_by_event_name         |      0 |           0 |
| performance_schema | socket_summary_by_event_name                       |      0 |           0 |
| performance_schema | users                                              |      0 |           0 |
| mysql              | servers                                            |      0 |           0 |
| performance_schema | events_waits_summary_by_account_by_event_name      |      0 |           0 |
| db01               | tbl_emp                                            |      0 |           0 |
| performance_schema | events_statements_summary_by_host_by_event_name    |      0 |           0 |
| db01               | tblA                                               |      0 |           0 |
| performance_schema | table_io_waits_summary_by_index_usage              |      0 |           0 |
| performance_schema | events_waits_current                               |      0 |           0 |
| db01               | user                                               |      0 |           0 |
| mysql              | procs_priv                                         |      0 |           0 |
| performance_schema | events_statements_summary_by_thread_by_event_name  |      0 |           0 |
| db01               | emp                                                |      0 |           0 |
| db01               | tbl_user                                           |      0 |           0 |
| db01               | test03                                             |      0 |           0 |
| mysql              | slow_log                                           |      0 |           0 |
| performance_schema | file_summary_by_instance                           |      0 |           0 |
| db01               | article                                            |      0 |           0 |
| performance_schema | objects_summary_global_by_type                     |      0 |           0 |
| db01               | phone                                              |      0 |           0 |
| performance_schema | events_waits_summary_by_thread_by_event_name       |      0 |           0 |
| performance_schema | setup_consumers                                    |      0 |           0 |
| performance_schema | socket_instances                                   |      0 |           0 |
| performance_schema | rwlock_instances                                   |      0 |           0 |
| db01               | tbl_dept                                           |      0 |           0 |
| performance_schema | events_statements_summary_by_user_by_event_name    |      0 |           0 |
| db01               | staffs                                             |      0 |           0 |
| db01               | class                                              |      0 |           0 |
| mysql              | general_log                                        |      0 |           0 |
| performance_schema | events_stages_summary_global_by_event_name         |      0 |           0 |
| performance_schema | events_stages_summary_by_account_by_event_name     |      0 |           0 |
| performance_schema | events_statements_summary_by_account_by_event_name |      0 |           0 |
| performance_schema | table_lock_waits_summary_by_table                  |      0 |           0 |
| performance_schema | hosts                                              |      0 |           0 |
| performance_schema | setup_objects                                      |      0 |           0 |
| performance_schema | events_stages_current                              |      0 |           0 |
| mysql              | time_zone                                          |      0 |           0 |
| mysql              | tables_priv                                        |      0 |           0 |
| performance_schema | table_io_waits_summary_by_table                    |      0 |           0 |
| mysql              | time_zone_leap_second                              |      0 |           0 |
| db01               | book                                               |      0 |           0 |
| performance_schema | session_account_connect_attrs                      |      0 |           0 |
| db01               | mylock                                             |      0 |           0 |
| mysql              | func                                               |      0 |           0 |
| performance_schema | events_statements_summary_global_by_event_name     |      0 |           0 |
| performance_schema | events_statements_history                          |      0 |           0 |
| performance_schema | accounts                                           |      0 |           0 |
| mysql              | time_zone_transition                               |      0 |           0 |
| db01               | dept                                               |      0 |           0 |
| performance_schema | events_stages_summary_by_host_by_event_name        |      0 |           0 |
| performance_schema | events_stages_summary_by_thread_by_event_name      |      0 |           0 |
| mysql              | proc                                               |      0 |           0 |
| performance_schema | setup_instruments                                  |      0 |           0 |
| performance_schema | host_cache                                         |      0 |           0 |
+--------------------+----------------------------------------------------+--------+-------------+
84 rows in set (0.00 sec)

添加锁(read读锁，write写锁)

lock table 表名1 read/write, 表名2 read/write, ...;

释放表锁

unlock tables;

2.1.1、读锁示例

读锁示例

结论

当前 session 和其他 session 均可以读取加了读锁的表
当前 session 不能读取其他表，并且不能修改加了读锁的表
其他 session 想要修改加了读锁的表，必须等待其读锁释放

2.1.2、写锁示例

写锁示例

结论

当前 session 可以读取和修改加了写锁的表
当前 session 不能读取其他表
其他 session 想要读取加了写锁的表，必须等待其读锁释放

案例结论

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
MySQL的表级锁有两种模式：
- 表共享读锁（Table Read Lock）
- 表独占写锁（Table Write Lock）

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

对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作
简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

2.2、表锁分析

表锁分析

查看哪些表被锁了，0 表示未锁，1 表示被锁

show open tables;

【如何分析表锁定】可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定，通过 show status like 'table%'; 命令查看

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1；
Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况；

mysql> show status like 'table%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Table_locks_immediate      | 500440 |
| Table_locks_waited         | 1      |
| Table_open_cache_hits      | 500070 |
| Table_open_cache_misses    | 5      |
| Table_open_cache_overflows | 0      |
+----------------------------+--------+
5 rows in set (0.00 sec)

此外，Myisam的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞

3、行锁

行锁的特点

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁

疑问点解答：

3.1、事务复习

行锁支持事务，复习下老知识

事务（Transation）及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
持久性（Durability）：事务院成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务处理带来的问题

更新丢失

（Lost Update）：

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题一一最后的更新覆盖了由其他事务所做的更新。
例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。
如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。

脏读

（Dirty Reads）：

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。
一句话：事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

不可重复读

（Non-Repeatable Reads）：

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性

幻读

（Phantom Reads）

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读一句话：事务A读取到了事务B体提交的新增数据，不符合隔离性。
多说一句：幻读和脏读有点类似，脏读是事务B里面修改了数据，幻读是事务B里面新增了数据。

事物的隔离级别

脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。
数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。
同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
查看当前数据库的事务隔离级别：show variables like 'tx_isolation'; mysql 默认是可重复读

3.2、行锁案例

行锁案例分析

创建表

建表 SQL

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);

test_innodb_lock 表中的测试数据

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | 3    |
|    4 | 4000 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

test_innodb_lock 表中的索引

mysql> SHOW INDEX FROM test_innodb_lock;
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_innodb_lock |          1 | test_innodb_a_ind      |            1 | a           | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_innodb_lock |          1 | test_innodb_lock_b_ind |            1 | b           | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

操作同一行数据

session1 开启事务，修改 test_innodb_lock 中的数据

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b=‘4001’ where a=4;
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1  Changed: 1  Warnings: 0

session2 开启事务，修改 test_innodb_lock 中同一行数据，将导致 session2 发生阻塞，一旦 session1 提交事务，session2 将执行更新操作

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b=‘4002’ where a=4;

# 在这儿阻塞着呢~~~

# 时间太长，会报超时错误哦

mysql> update test_innodb_lock set b=‘4001’ where a=4;
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

操作不同行数据

session1 开启事务，修改 test_innodb_lock 中的数据

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b=‘4001’ where a=4;
 Query OK, 0 rows affected (0.00 sec)
 Rows matched: 1  Changed: 0  Warnings: 0

session2 开启事务，修改 test_innodb_lock 中不同行的数据
由于采用行锁，session2 和 session1 互不干涉，所以 session2 中的修改操作没有阻塞

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b=‘9001’ where a=9;
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1  Changed: 1  Warnings: 0

无索引导致行锁升级为表锁

session1 开启事务，修改 test_innodb_lock 中的数据，varchar 不用 ’ ’ ，导致系统自动转换类型，导致索引失效

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set a=44 where b=4000;
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1  Changed: 1  Warnings: 0

session2 开启事务，修改 test_innodb_lock 中不同行的数据
由于发生了自动类型转换，索引失效，导致行锁变为表锁

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b=‘9001’ where a=9;

# 在这儿阻塞着呢~~~

3.3、间隙锁

什么是间隙锁

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

间隙锁的危害

因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

间隙锁示例

test_innodb_lock 表中的数据

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | 3    |
|    4 | 4000 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

session1 开启事务，执行修改 a > 1 and a < 6 的数据，这会导致 mysql 将 a = 2 的数据行锁住（虽然表中并没有这行数据）

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b=‘Heygo’ where a>1 and a<6;
 Query OK, 3 rows affected (0.00 sec)
 Rows matched: 3  Changed: 3  Warnings: 0

session2 开启事务，修改 test_innodb_lock 中不同行的数据，也会导致阻塞，直至 session1 提交事务

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b=‘9001’ where a=9;

# 在这儿阻塞着呢~~~

3.4、手动行锁(悲观锁)

如何锁定一行

select xxx ... for update 锁定某一行后，其它的操作会被阻塞，直到锁定行的会话提交
session1 开启事务，手动执行 for update 锁定指定行，待执行完指定操作时再将数据提交

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_innodb_lock  where a=8 for update;
 ±-----±-----+
 | a    | b    |
 ±-----±-----+
 |    8 | 8000 |
 ±-----±-----+
 1 row in set (0.00 sec)

session2 开启事务，修改 session1 中被锁定的行，会导致阻塞，直至 session1 提交事务

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b=‘XXX’ where a=8;

# 在这儿阻塞着呢~~~

3.5、行锁分析

案例结论

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。
当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。
但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候（索引失效，导致行锁变表锁），可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

行锁分析

如何分析行锁定

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';

mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 212969 |
| Innodb_row_lock_time_avg      | 42593  |
| Innodb_row_lock_time_max      | 51034  |
| Innodb_row_lock_waits         | 5      |
+-------------------------------+--------+
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：系统启动后到现在总共等待的次数；

对于这5个状态变量，比较重要的主要是

Innodb_row_lock_time_avg（等待平均时长）
Innodb_row_lock_waits（等待总次数）
Innodb_row_lock_time（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

3.6、行锁优化

优化建议

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

4、页锁

开销和加锁时间界于表锁和行锁之间：会出现死锁；
锁定粒度界于表锁和行锁之间，并发度一般。
了解即可

六、主从复制

1、复制的基本原理

复制的基本原理

slave会从master读取binlog来进行数据同步，主从复制的三步骤

master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件（binary log events）
slave将master的binary log events拷贝到它的中继日志（relay log）
slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的

2、复制的基本原则

每个slave只有一个master
每个slave只能有一个唯一的服务器ID
每个master可以有多个salve

3、复制最大问题

因为发生多次 IO，存在延时问题

4、一主一从常见配置

前提：mysql 版本一致，主从机在同一网段下

ping 测试

Linux 中 ping Windows

[root@Heygo 桌面]# ping 10.206.207.131
PING 10.206.207.131 (10.206.207.131) 56(84) bytes of data.
64 bytes from 10.206.207.131: icmp_seq=1 ttl=128 time=1.27 ms
64 bytes from 10.206.207.131: icmp_seq=2 ttl=128 time=0.421 ms
64 bytes from 10.206.207.131: icmp_seq=3 ttl=128 time=1.12 ms
64 bytes from 10.206.207.131: icmp_seq=4 ttl=128 time=0.515 ms
^C
--- 10.206.207.131 ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3719ms
rtt min/avg/max/mdev = 0.421/0.835/1.279/0.373 ms
[root@Heygo 桌面]#

Windows 中 ping Linux

C:\Users\Heygo>ping 192.168.152.129

正在 Ping 192.168.152.129 具有 32 字节的数据:
 来自 192.168.152.129 的回复: 字节=32 时间<1ms TTL=64
 来自 192.168.152.129 的回复: 字节=32 时间<1ms TTL=64
 来自 192.168.152.129 的回复: 字节=32 时间=1ms TTL=64
 来自 192.168.152.129 的回复: 字节=32 时间<1ms TTL=64

192.168.152.129 的 Ping 统计信息:
 数据包: 已发送 = 4，已接收 = 4，丢失 = 0 (0% 丢失)，
 往返行程的估计时间(以毫秒为单位):
 最短 = 0ms，最长 = 1ms，平均 = 0ms

主机修改 my.ini 配置文件（Windows）

主从都配置都在 [mysqld] 节点下，都是小写，以下是老师的配置文件

以下两条为必须配置

配置主机 id

server-id=1

启用二进制日志

log-bin=C:/Program Files (x86)/MySQL/MySQL Server 5.5/log-bin/mysqlbin

以下为非必须配置

启动错误日志

log-err=C:/Program Files (x86)/MySQL/MySQL Server 5.5/log-bin/mysqlerr

basedir="C:/Program Files (x86)/MySQL/MySQL Server 5.5/"

tmpdir="C:/Program Files (x86)/MySQL/MySQL Server 5.5/"

datadir="C:/Program Files (x86)/MySQL/MySQL Server 5.5/Data/"

主机，读写都可以

read-only=0

设置不要复制的数据库

binlog-ignore-db=mysql

设置需要复制的数据

binlog-do-db=需要复制的主数据库名字

从机修改 my.cnf 配置文件（Linux）

【必须】从服务器唯一ID

server-id=2

【可选】启用二进制文件

修改配置文件后的准备工作

因修改过配置文件，主机+从机都重启 mysql 服务

Windows

net stop mysql
net start mysql

Linux

service mysqld restart

主机从机都关闭防火墙

Windows 手动关闭防火墙
关闭虚拟机 linux 防火墙

service iptables stop

在 Windows 主机上简历账户并授权 slave

创建用户，并授权

GRANT REPLICATION SLAVE ON *.* TO '备份账号'@'从机器数据库 IP' IDENTIFIED BY '账号密码';
GRANT REPLICATION SLAVE ON *.* TO 'Heygo'@'192.168.152.129' IDENTIFIED BY '123456';

刷新权限信息

flush privileges;
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查询 master 的状态，将 File 和 Position 记录下来，在启动 Slave 时需要用到这两个参数

show master status;
1
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | mysql        |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

在 Linux 从机上配置需要复制的主机

从机进行认证

CHANGE MASTER TO 
MASTER_HOST='主机 IP',
MASTER_USER='创建用户名',
MASTER_PASSWORD='创建的密码',
MASTER_LOG_FILE='File 名字',
MASTER_LOG_POS=Position数字;

CHANGE MASTER TO 
MASTER_HOST='10.206.207.131',
MASTER_USER='Heygo',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;

启动从服务器复制功能

start slave;

查看从机复制功能是否启动成功：Slave_SQL_Running:Yes 和 Slave_IO_Running:Yes 说明从机连接主机成功

show slave status\G;

如何停止从服务复制功能

stop slave;

MySQL高级③

四、查询截取分析

1、查询优化

1.1、MySQL 优化原则

1.2、ORDER BY 优化

1.3、GROUP BY 优化

2、慢查询日志

2.1、慢查询日志介绍

2.2、慢查询日志开启

2.3、慢查询日志示例

3、批量数据脚本

4、Show Profile

5、全局查询日志

五、MySQL 锁机制

1、概述

1.1、锁的定义

1.2、锁的分类

2、表锁

2.1、表锁案例

2.1.1、读锁示例

2.1.2、写锁示例

2.2、表锁分析

3、行锁

3.1、事务复习

3.2、行锁案例

3.3、间隙锁

3.4、手动行锁(悲观锁)

3.5、行锁分析

3.6、行锁优化

4、页锁

六、主从复制

1、复制的基本原理

2、复制的基本原则

3、复制最大问题

4、一主一从常见配置

2 条评论

发表评论 取消回复

MySQL高级③（1.查询截取分析，2.MySQL锁机制，3.主从复制）

MySQL高级③

四、查询截取分析

1、查询优化

1.1、MySQL 优化原则

1.2、ORDER BY 优化

1.3、GROUP BY 优化

2、慢查询日志

2.1、慢查询日志介绍

2.2、慢查询日志开启

2.3、慢查询日志示例

3、批量数据脚本

4、Show Profile

5、全局查询日志

五、MySQL 锁机制

1、概述

1.1、锁的定义

1.2、锁的分类

2、表锁

2.1、表锁案例

2.1.1、读锁示例

2.1.2、写锁示例

2.2、表锁分析

3、行锁

3.1、事务复习

3.2、行锁案例

3.3、间隙锁

3.4、手动行锁(悲观锁)

3.5、行锁分析

3.6、行锁优化

4、页锁

六、主从复制

1、复制的基本原理

2、复制的基本原则

3、复制最大问题

4、一主一从常见配置

发表评论取消回复