[MySQL高级](五) 查询截取分析详解数据库
2023-06-13 09:20:08 时间
小表驱动大表的理解我们可以通过下面的java代码来理解一下:
// 第一种循环的方式 for(int i = 0;i 5;i++){ for(int j = 0;j 1000;j++){ // 第二种循环的方式 for(int i = 0;i 1000;i++){ for(int j = 0;j 5;j++){ }
虽然上面的Java代码都表示循环5000次,但是在mysql中,推荐使用第一种循环方式,即小表驱动大表。
CREATE TABLE `tbla` ( `id` int(11) NOT NULL, `age` int(11) DEFAULT NULL, `brith` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
各种order by条件的执行情况
![]()
MySQL支持两种方式的排序,FileSort和Index。Index效率高,它指MySQL扫描索引本身完成排序,FileSort方式效率低。 ORDER BY满足两种情况,会使用Index方式排序。第一种是ORDER BY语句使用索引最左前列,第二种是使用Where子句与ORDER BY子句条件列组合满足索引最左前列 1.2.2 FileSort算法介绍
尽可能在索引列上完成排序操作,遵照索引建的最佳做前缀。如果不在索引列上,filesort有两种算法,mysql就要启动双路排序和单路排序。
双路排序
MySQL 4.1 之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据。读取行指针和orderby列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出。从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段。 单路排序
取一批数据,要两次磁盘IO,是很耗时的,所以出现了第二种改进的算法,就是单路排序。从磁盘读取查询需要的所有列,按照order by列在buffer对他们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间。因为它把每一行都保存在内存中了。结论以及引申出的问题:
1.2.3 优化策略 增大sort_buffer-size参数的设置 增大max_length_for_sort_data参数的设置
由于单路是一次磁盘IO,总体而言效率高于双路,但是单路有问题:
1.2.4 为排序使用索引
1.2 group by 关键字优化 group by实质是先排序后进行分组,遵照索引建的最佳左前缀 当无法使用索引列,增大max_length_for_sort_data参数的设置+增大sort buffer size参数的设置 where高于having,能写在where限定的条件就不要去having限定了 2. 慢查询日志 2.1 简介 MySQL的慢查询日志是mysql提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。 具体指运行时间超过long_query_time 的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10秒以上的语句。 有它来查看哪些SQL超出了我们的最大忍耐时间值,比如一条SQL执行超过5秒钟,我们就算慢SQL,希望能收集超过5秒的SQL,结合之前EXPLAIN进行全面分析 2.2 使用 2.2.1 说明 默认情况下,MySQL数据库没有开启慢查询日志,需要我们手动来设置这个参数 当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件 2.2.2 查看是否开启及如何开启 默认情况下slow_query_log的值为OFF,表示慢查询日志是禁用的,可以通过设置slow_query_log的值来开启
SHOW VARIABLES LIKE %slow_query_log%;开启慢查询日志只对当前数据库生效,如果MySQL重启后则会失效
SET GLOBAL slow_query_log = 1;
如果要永久生效,就必须修改配置文件my.cnf,修改my.cnf,[mysqld]下增加或修改参数
slow_query_log和slow_query_log_file后,然后重启MySQL服务器,也即将如下两行配置进my.cnf文件slow_query_log=1 slow_query_log_file=/var/lib/mysql/why-solw.log关于慢查询的参数slow_query_log_file,它指定慢查询日志文件的存放路径,系统默认会给你个缺省的文件host_name_log_file.log(如果没有指定参数slow_query_log_file的话)
2.2.3 开启慢查询日志后,什么样的SQL才会记录到日志中这个是有参数long_query_time控制,默认情况下值为10秒。
命令:SHOW VARIABLES LIKE long_query_time%
可以使用命令修改,也可以在my.cnf参数里面修改。
2.2.4 使用方法 查看当前多少秒算慢
假如运行时间正好等于long_query_time的情况,并不会被记录下来,也就是说,在mysql源码里是判断大于long_query_time,而非大于等于。SHOW VARIABLES LIKE long_query_time%设置慢的阙值时间SET GLOBAL long_query_time = 3;为什么设置后重新查看仍然没改变
需要重新连接或断开当前会话才能看到修改值
或执行下面的命令SHOW GLOBAL VARIABLES LIKE long_query_time;查询当前系统中有多少条慢SQL查询记录SHOW GLOBAL STATUS LIKE %Slow_queries%;2.3 日志分析工具mysqldumpslow3. Show Profile
![]()
Show Profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优的测量,默认情况下,参数处于关闭状态,并保存最近15次的运行结果。
3.1 分析步骤 是否支持,看看当前的mysql版本是否支持SHOW VARIABLES LIKE profiling开启功能,默认是关闭的,使用前需要开启SET profiling = ON; SHOW VARIABLES LIKE profiling;运行SQL
show profiles;诊断SQLshow profile cpu,block,io for query 上一步问题SQL的数字号码
![]()
日常开发需要注意的结论converting HEAP to MyISAM 查询结果太大,内存都不够用了往磁盘上搬了 Creating tmp table 创建临时表 Copying to tmp table on disk 把内存中临时表复制到磁盘,危险 locked原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/3923.html
mysql
相关文章
- 修改MySQL用户的host属性详解数据库
- MySQL数据库笔试:挑战自我!(mysql数据库笔试题)
- MySQL读写分离技术:提高数据库性能的利器(mysql读写分离技术)
- 【极致保障】MySQL 双机热备份技术(mysql双机热备份)
- MySQL中字符类型及其应用(mysql中字符类型)
- MySQL表添加主键:一步一步指南(mysql表添加主键)
- 使用JSP连接MySQL数据库(jsp链接mysql)
- 名MySQL更改列名的步骤详解(mysql更改列)
- PHP与MySQL互动:开发动态网站的关键(php与mysql交互)
- 数据库操作快速入门:Node.js中MySQL数据库操作(nodejs的mysql)
- MySQL数据导出命令,快速备份数据。(mysql数据导出命令)
- MySQL中事务保证数据库操作正确性(mysql中事务是干啥的)
- 深度解读MySQL,我的个人理解在博客园分享(mysql个人理解博客园)
- MySQL快速入门以CMD操作绘就技能图解(cmd操作mysql图解)
- App与MySQL数据库构建坚固的联系(app和mysql数据库)
- 使用Ajax实现MySQL数据库操作(ajax操作mysql)
- MySQL中创建游标的步骤详解(mysql中创建游标)
- MySQL中如何表示分钟(mysql中分钟怎么表示)