1、查询截取分析
- 1、分析步骤
- 2、查询优化
  - 1、小表驱动大表
  - 2、order by 关键字优化
    - 1、Order by 子句，尽量使用Index 方式排序，避免使用FileSort方式排序
id int primary key not null auto_increment,
- 3、开启日志之后，哪些SQL会记录到日志中
- 3、日志分析工具mysqldumpslow
  - 1、参数说明
  - 2、示例
4、批量数据脚本
6、Show profile
7、全局查询日志
2、MySql锁机制

1、查询截取分析

1、分析步骤

（1）观察，至少跑1天，看看生产的慢SQL情况。
（2）开启慢查询日志，设置阙值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
（3） explain+慢SQL分析
（4）show profile
（5）运维经理or DBA,进行SQL数据库服务器的参数调优。

总结|

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

2、查询优化

1、小表驱动大表
优化原则：小表驱动大表，即小的数据集驱动大的数据集
- select * from A where id in (select id from B)
- 等价于
- for select id from B
- for select * from A where A.id = b.id
当B表的数据集必须小于A表的数据集时，用in优于exists
- select * from A where exists (select 1 from B where B.id = A.id)
- 等价于
- for select * from A
- for select * from B where B.id = A.id
当A表的数据集小于B表的数据集时，用Exists优于in。
注意：A表于B表的ID字段应建立索引。
EXISTSSELECT .. FROM table WHERE EXISTS (subquery)
- 该语法可以理解为:将主查询的数据，放到子查询中做条件验证，根据验证结果(TRUE或FALSE)来决定主查询的数据结果是否得以保留。
- 1 EXISTS (subquery)只返回TRUE或FALSE, 因此子查询中的SELECT *也可以是SELECTI或select’X’, 官方说法是实际执行时会忽略SELECT清单，因此没有
区别
- 2 EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
- 3 EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析
  2、order by 关键字优化
  1、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);

select * from tblA;

查询
```sql
mysql> explain select * from tblA where age > 20 order by age;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tblA order by birth;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

复合索引，如果asc和desc 混着用也会导致出现filesort

MySQL支持两种方式的排序，FileSort和Index，Index效率高,它指MySql扫描索引本身完成排序。FileSort方式效率较低。
ORDER BY满足两种情况，会使用Index方式排序
- ORDER BY语句使用索引最左前列
- 使用WHERE子句与Order by子句条件列组合满足索引最左前列 ```sql mysql> explain select * from tblA order by age asc, birth asc; +——+——————-+———-+——————+———-+———————-+————————+————-+———+———+—————+——————-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +——+——————-+———-+——————+———-+———————-+————————+————-+———+———+—————+——————-+ | 1 | SIMPLE | tblA | NULL | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | 100.00 | Using index | +——+——————-+———-+——————+———-+———————-+————————+————-+———+———+—————+——————-+ 1 row in set, 1 warning (0.00 sec)

<a name="QItSM"></a>
#### 2、尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
<a name="oX7EU"></a>
#### 3、如果不再索引列上，filesort有两种算法：

- mysql就要启动双路排序和单路排序
   - 双路排序
      - MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
      - 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
   - 取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。
   - 单路排序
      - 从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机I0变成了顺序I0,但是它会使用更多的空间，因为它把每一行都保存在内存中了。
   - 结论及引申出的问题
      - 由于单路是后出的，总体而言好过双路
      - 但是用单路有问题
         - 在sort_buffer中， 方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort buffer的容量，导致每次只能取sort_buffer容量 大小的数据，进行排序(创建tmp文件，多路合并)，排完再取取.sort_buffer容 量大小，再....从而多次I/O。本来想省-次I/O操作，反而导致了大量的I/O操作，反而得不偿失。
- 优化策略
   - 增大sort_buffer_size参数的设置
   - 增大max_length_for_sort_data参数的设置
   - Why
- 提高Order By的速度
   - 1. Order by时select *是一个大忌只Query需要的字段， 这点非常重要。在这里的影响是:
      - 1.1当Query的字段大小总和小于max_ _length_ for_ sort_ data 而且排序字段不是TEXT|BLOB类型时，会用改进后的算法一-单路排序，否则用老算法一多路排序。
      - 1.2两种算法的数据都有可能超出sort_ buffer的容量， 超出之后，会创建tmp文件进行合并排序，导致多次I/O， 但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size.
   - 2.尝试提高sort_buffer_size
      - 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
   - 3.尝试提高max_length_for_sort_data
      - 提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率.
<a name="mIiqR"></a>
#### 4、总结

- 为排序使用索引
   - MySq|两种排序方式:文件排序（using filesort）或扫描有序索引排序
   - MySq|能为排序与查询使用相同的索引
- order by能使用素引最左前缀
   - ORDER BY a
   - ORDER BY a,b
   - ORDERBYa, b, c
   - ORDER BY a DESC, b DESC,c DESC
- 如果WHERE使用素引的最左前缀定义为常量.则order by能使用索引
   - WHERE a=const ORDERBY b,c
   - WHERE a = const AND b = const ORDER BY c
   - WHERE a = const ORDER BY b, c
   - WHERE a = const AND b > const ORDERBY b, c
- 不能使用索引进行排序
   - ORDER BY aASC, b DESC, cDESC /* 排序不一致 */
   - WHERE g = const ORDER BY b,c /* 丢失a索引*/
   - WHERE a = const ORDER BY c /*丢失b索引*/
   - WHERE a=const ORDERBY a,d /*d不是索引的一部分*/
   -  WHERE a in (..) ORDERBY b, c /*对于排序来说,多个相等条件也是范围查询*/
<a name="Kyejg"></a>
### 3、group by 关键字优化

- groupby实质是先排序后进行分组，遵照索引建的最佳左前缀
- 当无法使用索引列，增大max_length_for_sort_data 参数的设置+增大sort_buffer_size参数的设置
- where高于having，能写在where限定的条件就不要去having限定了。
<a name="lsFEp"></a>
## 3、慢查询日志
<a name="B8tkk"></a>
### 1、什么是慢查询日志

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

收集超过5秒的sq|，结合之前explain进行全面分析。
<a name="ZGoA6"></a>
### 2、怎么收集
<a name="SrIdx"></a>
#### 1、注意：

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

- 默认关闭
```sql
mysql> show variables like 'slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | OFF                               |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.00 sec)

开启
```
set global slow_query_log=1;
```
使用上面命令开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效
永久生效

如果要永久生效，就必须修改配置文件my.cnf (其它系统变量也是如此)修改my.cnf文件，[mysqld]下增加或修改参数slow_query_log fẞslow_query_log_file后, 然后重启MySQL服务器。也即将如下两行配置进行my.cnf文件

[mysqld]
slow_query_log=1
slow_query_log_file=/Users/djy/WorkFile/mysql/daijunyi_slow.log

3、开启日志之后，哪些SQL会记录到日志中

由参数long_query_time控制，默认情况下long_query_time的值为10秒
```
mysql> show variables like 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
```
可以使用命令修改，也可以使用my.cnf参数里面修改。
假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说
在mysql源码里面是判断大于long_query_time,而非大于等于。

设置命令

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

注意：设置了这个时间之后一定要退出连接，不然在当前连接中的sql不会被记录下来
但是设置完之后查询会发现还是10秒，这个时候需要退出mysql，重新连接一下，或者查询global可以查询出来

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

通过一句慢语句进行测试

select sleep(10);

日志中就会记录下这条慢的日志

# Time: 2021-08-21T09:20:54.506465Z
# User@Host: root[root] @ localhost []  Id:    10
# Query_time: 10.002973  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1629537644;

查看全局有多少条慢查询


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

my.cnf中的永久配置

[mysqld]
slow_query_log=1
slow_query_log_file=/Users/djy/WorkFile/mysql/daijunyi_slow.log
long_query_time=3
log_output=file

3、日志分析工具mysqldumpslow

1、参数说明

djydeMacBook-Pro:~ DD$ 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

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

2、示例
得到返回记录集最多的10个SQL
- mysqldumpslow -s -r -t 10 /Users/djy/WorkFile/mysql/daijunyi_slow.log;
得到访问次数最多的10个SQL
- mysqldumpslow -s -c -t 10 /Users/djy/WorkFile/mysql/daijunyi_slow.log;
得到按照时间排序的前10条里面含有左连接的查询语句
- mysqldumpslow -s -t -t 10 -g “left join” /Users/djy/WorkFile/mysql/daijunyi_slow.log;
另外建议在使用这些命令时结合|和more使用，否则有可能出现爆屏情况
- mysqldumpslow -s -r -t 10 /Users/djy/WorkFile/mysql/daijunyi_slow.log; | more
  4、批量数据脚本
往表里插入1000万数据

1、创建表
```sql create DATABASE bigData; use bigData; 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;

create TABLE dept( id INT UNSIGNED PRIMARY KEY auto_increment, deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, dname VARCHAR(20) NOT NULL DEFAULT “”, ioc VARCHAR(13) NOT NULL DEFAULT “”) ENGINE=INNODB DEFAULT CHARSET=utf8;

<a name="MtuV2"></a>
### 2、设置参数log_bin_trust_funcation_creators
创建函数，假如报错: This function has none of DETERMINISTI.....

- 由于开启过慢查询日志，因为我们开启了bin-log, 我们就必须为我们的function指定-一个参数。
```sql
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;

这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法:

windows下my.ini[mysqld]加上log_bin_trust_function_creators=1
linux下/etc/my.cnf 下my.cnf[mysqld]加上log_bin_trust_function_creators=1

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

随机产生字符串

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
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 $$

随机产生部门id

DELIMITER $$
CREATE FUNCTION rand_num() returns INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i=FLOOR(100+RAND()*10);
RETURN i;
END $$

4、创建存储过程

1、创建往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 $$

2、创建往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,ioc)VALUES((START+i),rand_string(10),rand_string(8));
UNTIL i=max_num 
END REPEAT;
COMMIT;
END $$

5、调用存储过程

1、dept调用

DELimiter ;
CALL insert_dept(300001,200000);

2、emp调用

DELimiter ;
CALL insert_emp(300001,200000);

6、Show profile

1、简介

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量
默认情况下，参数处于关闭状态，并保存最近15次的运行结果
2、官网
官网: http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
3、步骤
1、查看是否支持：看看当前的mysql版本是否支持
```
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.01 sec)
```
2、开启功能，默认是关闭，使用前需要开启
```sql mysql> set profiling=on; Query OK, 0 rows affected, 1 warning (0.00 sec)

<a name="dyBKs"></a>
#### 3、运行SQL 

- 5.7以及之后的版本，执行一些sql语句的标准变了
- 可以再my.cnf中添加,并且重启生效
```shell
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

执行语句，发现都很慢

mysql> select * from emp group by id%10 limit 15000;
+----+-------+--------+----------+-----+------------+---------+--------+--------+
| id | empno | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+----+-------+--------+----------+-----+------------+---------+--------+--------+
|  1 |     0 | IFKpAI | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 475923 |
|  2 |     1 | dAwiY8 | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 332160 |
|  3 |     2 | 5Pr8t1 | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 191178 |
|  4 |     3 | FuLkQH | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 |  72216 |
|  5 |     4 | ZDudTf | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 475228 |
|  6 |     5 | A5SoGf | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 484269 |
|  7 |     6 | XCb5nY | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 267972 |
|  8 |     7 | HBHItx | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 241053 |
|  9 |     8 | TxmG94 | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 130580 |
| 10 |     9 | z9xvnx | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 |  91458 |
+----+-------+--------+----------+-----+------------+---------+--------+--------+
10 rows in set (11.03 sec)

mysql> select * from emp group by id%20 order by 5;
+----+-------+--------+----------+-----+------------+---------+--------+--------+
| id | empno | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+----+-------+--------+----------+-----+------------+---------+--------+--------+
|  1 |     0 | IFKpAI | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 475923 |
|  2 |     1 | dAwiY8 | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 332160 |
|  3 |     2 | 5Pr8t1 | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 191178 |
|  4 |     3 | FuLkQH | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 |  72216 |
|  5 |     4 | ZDudTf | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 475228 |
|  6 |     5 | A5SoGf | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 484269 |
|  7 |     6 | XCb5nY | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 267972 |
|  8 |     7 | HBHItx | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 241053 |
|  9 |     8 | TxmG94 | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 130580 |
| 10 |     9 | z9xvnx | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 |  91458 |
| 11 |    10 | Rcooqv | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 115297 |
| 12 |    11 | L2cpwv | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 |  80357 |
| 13 |    12 | ypXmcL | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 109835 |
| 14 |    13 | m3gN1m | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 298187 |
| 15 |    14 | PY9v1h | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 |  39184 |
| 16 |    15 | u0b8iS | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 369262 |
| 17 |    16 | VYHfwY | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 353548 |
| 18 |    17 | slAsze | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 226268 |
| 19 |    18 | n3yqU5 | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 | 148614 |
| 20 |    19 | zy4TLV | SALESMAN |   1 | 2021-08-22 | 2000.00 | 400.00 |  55272 |
+----+-------+--------+----------+-----+------------+---------+--------+--------+
20 rows in set (10.87 sec)

4、查看结果，show profiles;

mysql> show profiles;
+----------+-------------+----------------------------------------------+
| Query_ID | Duration    | Query                                        |
+----------+-------------+----------------------------------------------+
|        1 |  0.00145400 | show variables like 'profiling'              |
|        2 | 10.39941400 | select * from emp group by id%10 limit 15000 |
|        3 | 10.42492700 | select * from emp group by id%20 order by 5  |
+----------+-------------+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

5、诊断SQL, show profile cpu,block io for query上一步前面的问题SQL数字号码;

以上语句加上 show profiles中查看到的query_id 查询出来

mysql> show profile cpu,block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000104 | 0.000064 |   0.000006 |            0 |             0 |
| checking permissions | 0.000015 | 0.000014 |   0.000002 |            0 |             0 |
| Opening tables       | 0.000077 | 0.000076 |   0.000001 |            0 |             0 |
| init                 | 0.000006 | 0.000003 |   0.000002 |            0 |             0 |
| System lock          | 0.000006 | 0.000006 |   0.000002 |            0 |             0 |
| optimizing           | 0.000004 | 0.000002 |   0.000001 |            0 |             0 |
| optimizing           | 0.000007 | 0.000006 |   0.000001 |            0 |             0 |
| statistics           | 0.000031 | 0.000029 |   0.000001 |            0 |             0 |
| preparing            | 0.000023 | 0.000022 |   0.000003 |            0 |             0 |
| statistics           | 0.000009 | 0.000006 |   0.000002 |            0 |             0 |
| preparing            | 0.000014 | 0.000013 |   0.000001 |            0 |             0 |
| executing            | 0.001096 | 0.001046 |   0.000025 |            0 |             0 |
| end                  | 0.000009 | 0.000005 |   0.000004 |            0 |             0 |
| query end            | 0.000006 | 0.000005 |   0.000001 |            0 |             0 |
| removing tmp table   | 0.000006 | 0.000005 |   0.000002 |            0 |             0 |
| query end            | 0.000003 | 0.000003 |   0.000001 |            0 |             0 |
| removing tmp table   | 0.000004 | 0.000002 |   0.000001 |            0 |             0 |
| closing tables       | 0.000006 | 0.000006 |   0.000001 |            0 |             0 |
| freeing items        | 0.000020 | 0.000008 |   0.000010 |            0 |             0 |
| cleaning up          | 0.000008 | 0.000008 |   0.000008 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set, 1 warning (0.00 sec)

mysql> show profile cpu,block io for query 2;
+--------------------------------+-----------+-----------+------------+--------------+---------------+
| Status                         | Duration  | CPU_user  | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+-----------+-----------+------------+--------------+---------------+
| starting                       |  0.000078 |  0.000070 |   0.000007 |            0 |             0 |
| Executing hook on transaction  |  0.000004 |  0.000003 |   0.000002 |            0 |             0 |
| starting                       |  0.000008 |  0.000007 |   0.000001 |            0 |             0 |
| checking permissions           |  0.000007 |  0.000005 |   0.000001 |            0 |             0 |
| Opening tables                 |  0.000040 |  0.000039 |   0.000001 |            0 |             0 |
| init                           |  0.000007 |  0.000005 |   0.000002 |            0 |             0 |
| System lock                    |  0.000010 |  0.000009 |   0.000001 |            0 |             0 |
| optimizing                     |  0.000005 |  0.000004 |   0.000001 |            0 |             0 |
| statistics                     |  0.000023 |  0.000022 |   0.000001 |            0 |             0 |
| preparing                      |  0.000014 |  0.000013 |   0.000002 |            0 |             0 |
| Creating tmp table             |  0.000046 |  0.000044 |   0.000001 |            0 |             0 |
| executing                      | 10.399013 | 10.435982 |   0.197495 |            0 |             0 |
| end                            |  0.000015 |  0.000007 |   0.000008 |            0 |             0 |
| query end                      |  0.000004 |  0.000003 |   0.000001 |            0 |             0 |
| waiting for handler commit     |  0.000009 |  0.000007 |   0.000001 |            0 |             0 |
| removing tmp table             |  0.000008 |  0.000007 |   0.000001 |            0 |             0 |
| waiting for handler commit     |  0.000006 |  0.000005 |   0.000001 |            0 |             0 |
| closing tables                 |  0.000009 |  0.000009 |   0.000001 |            0 |             0 |
| freeing items                  |  0.000022 |  0.000008 |   0.000013 |            0 |             0 |
| logging slow query             |  0.000075 |  0.000018 |   0.000057 |            0 |             0 |
| cleaning up                    |  0.000011 |  0.000009 |   0.000002 |            0 |             0 |
+--------------------------------+-----------+-----------+------------+--------------+---------------+
21 rows in set, 1 warning (0.00 sec)

er参数备注
- ALL:显示所有的开销信息
- BLOCK IO:显示块IO相关开销
- CONTEXT SWITCHES:上下文切换相关开销
- CPU:显示CPU相关开销信息
- IPC:显示发送和接收相关开销信息
- MEMORY:显示内存相关开销信息
- PAGE FAULTS:显示页面错误相关开销信息
- SOURCE:显示和Source_function，Source_file，Source_line相关的开销信息
- SWAPS:显示交换次数相关开销的信息
  6、日常开发需要注意的结论
converting HEAP to MyISAM查询结果太大，内存都不够用了往磁盘上搬
Creating tmp table 创建临时表，拷贝数据到临时表，用完再删除
Copying to tmp table on disk把内存中临时表复制到磁盘，危险!!!

locked

7、全局查询日志

1、配置启动

在配置文件中my.cnf中配置

#开启
general_log=1
#记录日志文件的地址
general_log_file=/User/djy/WorkFile/mysql/logfile
#输出格式
log_output=FILE

2、编码启用

命令

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

```sql mysql> set global general_log=1; Query OK, 0 rows affected (0.01 sec)

mysql> set global log_output=’TABLE’; Query OK, 0 rows affected (0.00 sec)

此后，在你锁编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看
```sql
select * from mysql.general_log;

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

2、MySql锁机制

1、定义

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

2、锁的分类

1、操作类型

读锁
- 读锁(共享锁):针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁
- 写锁(排它锁):当前写操作没有完成前，它会阻断其他写锁和读锁。
  2、操作粒度
行锁
表锁

3、表锁（偏读）

1、特点：
- 偏向MyISAM存储引擎，开销小，加锁快;无死锁;锁定粒度大，发生锁冲突的概率最高|并发度最低。
  
  2、加读锁
  
  1、现象：
- 当前session
  - 读：只能读加了锁的表，不能读没加锁的表，
  - 写：不能写当前锁了的表也不能写其它表，
- 其它session
  - 读：可以读
  - 写：阻塞等待
    2、建表
```sql 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;

<a name="GrZCY"></a>
#### 3、手动增加表锁
```sql
lock table 表名字 read(write), 表名字2 read(write),其它；

mysql> show tables;
+-------------------+
| Tables_in_bigdata |
+-------------------+
| dept              |
| emp               |
| mylock            |
+-------------------+
3 rows in set (0.01 sec)
mysql> lock table mylock read,emp write;
Query OK, 0 rows affected (0.00 sec)

4、解锁表

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

2、加写锁

1、现象：

当前session:
- 读：可读自己锁了的表，不可以读别的没锁的表
- 写：可以写当前自己锁的表，不可以写没加锁的表
其它session：
- 读：不可读
- 写：不可写
  2、写锁
```sql mysql> lock table mylock write,emp write; Query OK, 0 rows affected (0.00 sec)

mysql> lock table dept write; Query OK, 0 rows affected (0.00 sec)

<a name="y6ONH"></a>
#### 3、解锁
```sql
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

3、看看哪些表被加锁了

show open tables;

4、分析表锁定

1、Table_locks_immediate和Table_locks_waited

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定：

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 93    |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 37    |
| Table_open_cache_misses    | 24    |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.01 sec)

这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下:

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

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

5、总结

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

MySQL的表级锁有两种模式:
- 表共享读锁(Table Read Lock)
- 表独占写锁(Table Write Lock)

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

1、对MyISAM表的读操作(加读锁)，不会阻塞其他进程对同- -表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，

才会执行其它进程的写操作。|

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

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

4、行锁（偏写）

需要where条件中查询的是索引

1、特点
偏向InnoDB存储引擎，开销大，加锁慢;会出现死锁;锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION) ;二是采用了行级锁
2、事务ACID属性
事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。
（1）、原子性(Atomicity) :
事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
（2）、一致性(Consistent) :
在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性;事务结束时，所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
（3）、隔离性(Isolation) :
数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
（4）、持久性(Durable) :
事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
3、隔离级别
1、脏读：
一个未提交事物能读到另一个未提交事物的数据，因为如果数据回滚的话会出现问题
2、不可重复读：
一个未提交事物读取到另一个已经提交事物修改的数据。
3、虚读：
一个未提交事物读取到另一提交事物添加的数据。
”脏读”、“不可重复读”和“幻读 ”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
常看当前数据库的事务隔离级别:
```
#5.7.20之前
show variables like 'tx_isolation';
#5.7.20只有
show variables like 'transaction_isolation';
```
```
mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
```
4、案例分析
1、建表
```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); SET autocommit=0;

<a name="qvvL8"></a>
#### 2、sql操作
![image.png](https://cdn.nlark.com/yuque/0/2021/png/12971636/1629639996876-baf91209-643d-4b96-af29-e90084a3f0fc.png#clientId=u31241a91-c180-4&from=paste&height=489&id=u1626b5d2&margin=%5Bobject%20Object%5D&name=image.png&originHeight=978&originWidth=2390&originalType=binary&ratio=1&size=653310&status=done&style=none&taskId=u9e2cc5fe-d18f-48ea-bea2-3179ff8c7fb&width=1195)
<a name="EJ7qw"></a>
### 5、行锁升级为表锁

- 当索引失效的时候，行锁会变成表锁
   - 比如varchar 类型，我们再where条件中写成了init类型，导致了索引失效变成了表锁
<a name="yU3eQ"></a>
### 6、间隙锁(Next-Key锁) 
<a name="Ro8SJ"></a>
#### 1、什么是间隙锁
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)” ，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁) 。
<a name="xRCFu"></a>
#### 2、危害
因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后， 即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害
<a name="pTxMn"></a>
### 7、如何锁定某一个行
select * from ..... where ...  for update;
```sql
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_innodb_lock where b='300'  for update;
+------+------+
| a    | b    |
+------+------+
|    1 | 300  |
|    3 | 300  |
|    1 | 300  |
+------+------+
3 rows in set (0.00 sec)


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

8、如何分析锁定

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

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 58929 |
| Innodb_row_lock_time_avg      | 7366  |
| Innodb_row_lock_time_max      | 13899 |
| Innodb_row_lock_waits         | 8     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

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

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

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

Innodb_row_lock_time_avg (等待平均时长)，
Innodb_row_lock_waits (等待总次数)
Innodb_row_lock_time (等待总时长)这三项。

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

9、优化建议

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

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

5、页锁

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

2、MySql高级优化

1、查询截取分析

1、分析步骤

2、查询优化

1、小表驱动大表

2、order by 关键字优化

1、Order by 子句，尽量使用Index 方式排序，避免使用FileSort方式排序

id int primary key not null auto_increment,

3、开启日志之后，哪些SQL会记录到日志中

3、日志分析工具mysqldumpslow

1、参数说明

2、示例

4、批量数据脚本

1、创建表

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

4、创建存储过程

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

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

5、调用存储过程

1、dept调用

2、emp调用

6、Show profile

1、简介

2、官网

3、步骤

1、查看是否支持：看看当前的mysql版本是否支持

2、开启功能，默认是关闭，使用前需要开启

4、查看结果，show profiles;

5、诊断SQL, show profile cpu,block io for query上一步前面的问题SQL数字号码;

6、日常开发需要注意的结论

7、全局查询日志

1、配置启动

2、编码启用

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

2、MySql锁机制

1、定义

2、锁的分类

1、操作类型

2、操作粒度

3、表锁（偏读）

1、特点：

2、加读锁

1、现象：

2、建表

4、解锁表

2、加写锁

1、现象：

2、写锁

3、看看哪些表被加锁了

4、分析表锁定

1、Table_locks_immediate和Table_locks_waited

5、总结

1、对MyISAM表的读操作(加读锁)，不会阻塞其他进程对同- -表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，

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

4、行锁（偏写）

1、特点

2、事务ACID属性

（1）、原子性(Atomicity) :

（2）、一致性(Consistent) :

（3）、隔离性(Isolation) :

（4）、持久性(Durable) :

3、隔离级别

1、脏读：

2、不可重复读：

3、虚读：

4、案例分析

1、建表

8、如何分析锁定

9、优化建议

10、总结

5、页锁