MySQL学习--------order by和group by优化，查询截取分析

最新推荐文章于 2023-10-24 11:44:45 发布
最新推荐文章于 2023-10-24 11:44:45 发布
阅读量296 收藏
点赞数
分类专栏： mysql 文章标签： 数据库 mysql 索引 mysql优化
原文链接：https://blog.csdn.net/oneby1314/article/details/107961443
版权

第 3 章 查询截取分析

1、查询优化

1.1、MySQL 优化原则

mysql 的调优大纲

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

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

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

image-20200805101726632

结论：

  1. 永远记住小表驱动大表
  2. 当 B 表数据集小于 A 表数据集时，使用 in
  3. 当 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)

结论

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

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

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

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

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

Order By 排序索引优化的总结

image-20200805111725731

1.3、GROUP BY 优化

group by关键字优化

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

2、慢查询日志

2.1、慢查询日志介绍

慢查询日志是什么？

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

2.2、慢查询日志开启

怎么玩？

说明：

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

查看是否开启及如何开启

  • 查看慢查询日志是否开启：
    • 默认情况下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 参数解释

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

常用参数手册

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

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

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

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

3.得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/Heygo-slow.log

 

 4.另外建议在使用这些命令时结合  | 和more使用，否则有可能出现爆屏情况

  • mysqldumpslow -s r -t 10 /var/lib/mysql/Heygo-slow.log | more
wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw==

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 是什么？

  1. 是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量
  2. 官网：http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
  3. 默认情况下，参数处于关闭状态，并保存最近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%10 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)
  • 参数备注：
  1. ALL：显示所有的开销信息
  2. BLOCK IO：显示块IO相关开销
  3. CONTEXT SWITCHES：上下文切换相关开销
  4. CPU：显示CPU相关开销信息
  5. IPC：显示发送和接收相关开销信息
  6. MEMORY：显示内存相关开销信息
  7. PAGE FAULTS：显示页面错误相关开销信息
  8. SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息
  9. SWAPS：显示交换次数相关开销的信息

日常开发需要注意的结论

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

举例

奇了怪了。。。老师的慢 SQL 我怎么复现不了，下面是老师的例子

image-20200805143307302

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

作者：OneBy1314

本文链接：https://blog.csdn.net/oneby1314/article/details/107961443

卷王中王
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql-connector-java-8.0.34
12-13
mysql-connector-java-8.0.34
第 3 章 查询截取分析
Oneby的博客
08-12 2721
第 3 章 查询截取分析 1、查询优化 1.1、MySQL 优化原则 mysql 的调优大纲 慢查询的开启并捕获 explain+慢SQL分析 show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况 SQL数据库服务器的参数调优 永远小表驱动大表，类似嵌套循环 Nested Loop EXISTS 语法： SELECT ... FROM table WHERE EXISTS(subquery) 该语法可以理解为：将查询的数据，放到子查询中做条件验证，根据验证结果（T
字符串切割 group by
紫蝶侠的博客
06-07 634
sql
十一、explain属性介绍
Jian Sun_的博客
09-27 712
EXPLAIN简介 　　EXPLAIN 命令是查看查询优化器如何决定执行查询的主要方法,使用EXPLAIN,只需要在查询中的SELECT关键字之前增加EXPLAIN这个词即可,MYSQL会在查询上设置一个标记,当执行查询时,这个标记会使其返回关于在执行计划中每一步的信息,而不是执行它,它会返回一行或多行信息,显示出执行计划中的每一部分和执行的次序,从而可以从分析结果中找到查询语句或是表结构的性能瓶颈。 EXPLAIN能干嘛 分析出表的读取顺序 数据读取操作的操作类型 哪些索引可以使用 哪些索引被
数据库mysql锁测试（间隙锁、临键锁）
weixin_43931625的博客
11-20 1673
数据库mysql 锁测试 数据库mysql 在可重复读、序列化隔离级别下使用的是间隙锁或者临键锁（间隙锁+行锁） 加锁规则：（隔离级别可重复、序列化） 主键索引或者唯一索引查询具体的值（如a=“value”）且value值存在时，可只加行锁，若不存在需要加间隙锁； 主键索引或者唯一索引进行范围查询（如a>"value"）时，需要加间隙锁或者临键锁； 非唯一索引需要在可重...
sql注入payload
专注企业信息安全－sec
04-12 3842
分享一些平时测试用的sql payloads 1：BOOL SQLINJECTION \ ' " %df' %df" and 1=1 and 1=2 ' and '1'='1 ' and '1'='2 " and "1"="1 " and "1"="2 ) and (1=1 ) and (1=2 ') and ('1'='1 ') and ('1'='2 %' and 1=1 and '%'...
MYSQL专题-查询优化-使用索引-安全隐患-事务与锁.docx
11-28
MYSQL专题-查询优化-使用索引-安全隐患-事务与锁.docx
MySQL-性能优化的-9-种知识.docx
12-17
MySQL-性能优化的-9-种知识.docx
mysql-connector-java-8.0.22.jar
08-14
包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-...
JavaEE源代码 mysql-connector-java-5.0.8-bin
07-09
JavaEE源代码 mysql-connector-java-5.0.8-binJavaEE源代码 mysql-connector-java-5.0.8-binJavaEE源代码 mysql-connector-java-5.0.8-binJavaEE源代码 mysql-connector-java-5.0.8-binJavaEE源代码 mysql-connector...
使用select实现sleep函数的功能
weixin_39035140的博客
01-27 2263
一、sleep和select到底哪个好 近期在阅读公司源码过程中发现了用select实现的sleep函数，经过思索和查阅相关资料，有了一定的了解， 1、首先两者都可以实现延时的效果，但是sleep延时的时间精确度上不高，因为设计CPU的调度算法、线程进程优先级，如果说对于时间上没有精确的要求，感觉sleep要笔记方便，就一行代码省事，无伤大雅。 2、也有人说sleep浪费CPU，使程序阻塞，如果说计算机资源本身就很紧缺，考虑系统的运行的效率、甚至网络传输速度，最好采用select。可以查看这个人的博客，相对
MySQL高级知识（十一）——Show Profile
weixin_30435261的博客
06-27 338
前言：Show Profile是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。 1.分析步骤 #1.开启Show Profile功能，默认该功能是关闭的，使用前需开启。 #2.根据MySQL高级知识（十）——批量插入数据脚本中的数据脚本向tb_emp_bigdata表中插入50w条...
MySQL数据库：使用show profile命令分析性能
张维鹏的博客
11-22 3847
1、show profile的作用： 能够查出最近执行的SQL语句的运行状态，包括在运行过程中执行了哪些操作，各占用了多少时间，利用proflie 功能，可以分析一个SQL具体的执行代价是怎么样的，尤其是可以分析它的最大瓶颈在哪里。目前profile 功能可提供除了内存以外的其他资源消耗统计，例如CPU、I/O、CONTEXT、SWAP等。大部分情况下，profile 的结果我们主要关注两列：S...
MySQL - Show Processlist 整理
weixin_30650859的博客
06-06 273
原文来源：MySQL 5.5 Reference Manual 部分翻译取自：《MySQL_5.1中文参考手册》 转载请注明原文链接http://www.cnblogs.com/lenagt/archive/2012/06/06/2538201.html谢谢。 --------------------------------------------------------------...
MySQL中show profile详解
qq_44161695的博客
03-25 5242
Show Profile官方文档地址：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html 当我们在优化sql的时候可以使用explain来查看执行计划，可当根据执行计划优化完毕之后我们的sql的运行时间还是比较长，这时我们可以使用show profile 更细粒度的分析，show-profile，可以提高用来分析当前会话中语句执行的资源消耗情况,可以用于sql调优的测量. 1. 基本语法： 查看是否开启：show variabl.
MySQL高级——性能分析之ShowProfile
Lurenjia915的博客
02-18 266
ShowProfile是MySQL提供用来分析当前会话中SQL语句执行的资源消耗情况的，主要用于SQL调优。默认它是关闭状态，但会保存最近 15 次的运行结果。
MySQL高级——Show Profile调优测量SQL
小志的博客
03-23 247
一、Show Profile是什么 Show Profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。 官网：http://dev.mysql.com/doc/refman/5.5/en/show-profile.html 默认情况下，参数处于关闭状态，并保存最近15次的运行结果。 二、Show Profile分析步骤 1、是否支持，看看当前的mysql版本是否支持 SHOW VARIABLES LIKE 'profiling'; 2、开启功能，默认是
ThinkPHP项目where()中加入or的使用
ColaLibLi的博客
11-09 5855
先来了解Mysql and与or的使用 AND和OR可在WHERE子语句中把两个或多个条件结合起来 使用OR关键字时： 只要符合这几个查询条件的其中一个条件，这样的记录就会被查询出来。 如果不符合这些查询条件的任何一条，这样的记录将被排除。 使用AND关键字时： 需要符合所有条件，这样的记录就会被查询出来。 如果有任何一个条件不符合，这样的记录将被排除。 下面是写法： 由thinkPHP项目where()查询数据就是以AND为基础查询数据 而OR其中也给了whereOr() 但如果需要AND，OR结合查询
Show profile 分析mysql
最新发布
huifeidewoniu_的博客
10-24 161
值得注意的是，MySQL 是在 5.0.37 版本之后才支持 Show Profile 功能的，如果你不太确。最新的 MySQL 版本是默认开启 Show Profile 功能的，但在之前的旧版本中是默认关闭该。Show Profiles 只显示最近发给服务器的 SQL 语句，默认情况下是记录最近已执行的 15。上述通过 EXPLAIN 分析执行计划，仅仅是停留在分析 SQL 的外部的执行情况，如果我们。消耗的时间最长，这是因为在该状态下，MySQL 线程开始读取数据并返回到客户端，此时。
mysql--group by与count计数
03-16
MySQL中的GROUP BY和COUNT函数通常一起使用，用于对数据进行分组和计数。 GROUP BY用于将数据按照指定的列进行分组，例如： SELECT department, COUNT(*) FROM employees GROUP BY department; 这条语句将...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值