一、相关参数

  1. -- 临时方式
  2. # 查询是否开启慢查询日志
  3. show variables like '%slow_query_log%';
  4. # 开启慢查询日志
  5. set global slow_query_log = on;
  7. # 查看时间阈值
  8. show variables like '%long_query_time%';
  9. # 设置时间
  10. set long_query_time=1;
  12. # 查询有多少慢查询记录
  13. show status like 'slow_queries';
  14. # 删除慢查询日志
  15. rm ***.log
  16. # 使用指令来重新生成
  17. mysqladmin -uroot -p flush-logs slow
  19. -- 永久方式
  20. [mysql]
  21. slow_query_log=ON # 开启慢查询日志
  22. slow_query_log_file=/var/lib/mysql/***.log # 慢查询日志的目录和文件名信息 /usr/local/mysql/data/KanlinadeMacBook-Pro-slow.log 
  23. long_query_time=3 #设置慢查询的阈值为3秒，超出此设定的sql即被记录为慢查询日志
  24. log_output=FILE

二、分析工具 mysqldumpslow

根目录下执行，mysqldumpslow -help; 查看相关指令用法。

  1. # 按照时间排序查看慢查询日志前5条
  2. mysqldumpslow -s t -t 5 /usr/local/mysql/data/KanlinadeMacBook-Pro-slow.log

三、查看执行成本 show profile

  1. # 查看是否开启
  2. show variables like 'profiling';
  3. #设置开启
  4. set profiling='ON';
  5. # 查看最近查询语句
  6. show profiles;
  7. # 查看指定查询语句具体执行成本
  8. show profile cpu,block io for quert 1;

四、分析查询语句：EXPLAIN

1、能做什么

  • 表的读取顺序；
  • 数据读取操作的操作类型；
  • 哪些索引可以使用；
  • 哪些索引实际被使用；
  • 表之间的引用；

  • 每张表有多少行被优化器查询。

    2、基本语法

    EXPLAIN 语句
    或
    DESCRIBE 语句

    3、语句输出各个列的作用

    image.png

  • table:查询的每一条记录对应一个单表；

  • id:在一个大的查询语句中每个select关键字都对应一个唯一的id；
  • select_type:小查询在大查询中扮演什么角色；
  • type:mysql执行查询时对表的访问方法：system(当表中只有一条数据并且该表的存储引擎统计数据是精确的),const(主键或者唯一二级索引与常数进行匹配),eq_ref(连接查询时，被驱动表通过主键或者唯一二级索引列等值匹配的方式进行访问),ref(通过普通二级索引列与常量进行等值匹配是来查询)，index_merge(单表访问方法时再某些场景下可以使用Intersection、Union、Sort-Union这三种索引合并的方式来执行查询)，unique_subquery(将in查询转换成exists，而且子查询可以使用到主键进行等值匹配)，range(范围查询)，index(使用索引覆盖，但需要扫描全部索引记录时)，all(全表扫描)。
  • possible_keys:可能使用到的索引；
  • key:真实使用的索引；
  • key_len:实际使用到的索引长度（字节数），值越大越好

  • ref:与索引列进行等值匹配的对象信息；

    4、小结

  • EXPLAIN不考虑各种Cache；

  • EXPLAIN不能显示MySQL在执行查询时所作的优化工作；
  • EXPLAIN不会告诉你关于触发器、存储过程的信息或者用户自定义函数对查询的影响；

  • 部分统计信息是估算的，并非精确值。

    5、四种输出格式

  • 传统格式：表格形式

  • JSON格式：EXPLAIN FORMAT=JSON，多了一个衡量计划好坏的形式
  • TREE格式：根据各部分查询顺序与关系
  • 可视化输出：mysql workbench

    五、分析优化器执行计划：trace

    1、相关语句

    1. # 开启trace并设置格式为JSON
    2. set optimizer_trace="enabled=on",end_markers_in_json=on;
    3. set optimizer_trace_max_mem_size=1000000;
    可以写增删改查语句 ```sql

    mysql如何执行

    select * from information_schema.optimizer_trace \G

查询冗余索引

select * from sys.schema_redundant_indexes;

查询未使用过的索引

select * from sys.schema_unused_indexes;

查询索引的使用情况

select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schema=’dbname’;

查询表的访问量

select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.innodb_buffer_stats_by_table order by allocated limit 10

查询占用bufferPool较多的表

select object_schema,object_name,allocated,DATA from sys.innodb_buffer_stats_by_table order by allocated limit 10

查看表的全表扫描情况

select * from sys.statements_with_full_table_scans where db=’dbname’

```