企业网站优化推广方法,商丘做网站的公司,官方网站免费制作,工业设计工程研究生就业方向Mysql 慢SQL调优-索引详解 前言一、慢查询日志设置二、explain查看执行计划三、索引失效四、索引操作五、profile 分析执行耗时 前言
最新的 Java 面试题#xff0c;技术栈涉及 Java 基础、集合、多线程、Mysql、分布式、Spring全家桶、MyBatis、Dubbo、缓存、消息队列、Linu… Mysql 慢SQL调优-索引详解 前言一、慢查询日志设置二、explain查看执行计划三、索引失效四、索引操作五、profile 分析执行耗时 前言
最新的 Java 面试题技术栈涉及 Java 基础、集合、多线程、Mysql、分布式、Spring全家桶、MyBatis、Dubbo、缓存、消息队列、Linux…等等会持续更新。
如果对老铁有帮助帮忙免费点个赞谢谢你的发财手
一、慢查询日志设置
1、开启慢查询日志设置超过几秒为慢SQL语句抓取慢SQL语句 阿里的ARMS监控平台直接就能查询到慢SQL
show variables like %query%1参数log_queries_not_using_indexes 表示如果运行的SQL语句没有使用到索引是否也被当作慢查询语句记录到慢查询日志中OFF表示不记录ON表示记录2参数long_query_time 表示“多长时间的查询”被认定为“慢查询”默认值为10秒表示超过10秒的查询被认定为慢查询3参数 slow_query_log 表示是否开启慢查询日志。开启“ set global slow_query_logon”关闭“ set global slow_query_logoff ”4参数slow_query_log_file指定慢查询日志存储于哪个日志文件中默认的慢查询日志文件名为“主机名-slow.log”慢查询日志的位置为datadir参数所对应的目录位置。 慢查询日志中给出了账号、主机、运行时间、锁定时间、返回行等信息当日志文件越来越大通过vi或cat命令不能很直观地查看日志这时就可以使用MySQL内置的mysqldumpslow命令来查询
mysqldumpslow -s r -t 5 /data/mysql/mysql_slow.log查询返回记录集最多的5个慢查询SQL。
二、explain查看执行计划
2、通过explain查看SQL执行计划重点关注type、key、rows、extra指标创建索引并调整语句再查看执行计划对比调优结果。
1id反映的是表的读取顺序或查询中执行select语句的顺序。 ① id相同可以认为是一组从上往下顺序执行 ② id不同如果是子查询id序号会递增id值越大优先级越高越先被执行。2select_type表示select的类型主要用于区别普通查询、联合查询、子查询等复杂查询。 ① SIMPLE表示查询语句不包含子查询或union。 ② PRIMARY查询中若包含任何复杂的子部分最外层查询标记为primary。 ③ SUBQUERYselect中的子查询语句 ④ DEPENDENT SUBQUERYselect或where列表中的子查询。 ⑤ DERIVED衍生在from列表中包含的子查询MySQL 会将结果存放在一个临时表中。 ⑥ UNION若第二个select出现在union后则被标记为union若union包含在from子句的子查询中外层select将被标记为DERIVED。3table表名称或别名显示这一行的数据是关于哪张表的。4type表示MySQL在表中找到目标行的方式又称“访问类型”; 性能system const eq_ref ref range index ALL ① const、system表示通过索引一次就查询到了相关记录一般为主键或唯一索引查询system是const类型的特例当查询的表只有一行的情况下使用。 ②eq_ref类似ref区别在于使用的索引是唯一索引对于每个索引键表中只有一条记录与之匹配常见于多表连接中一般使用primary key或者unique key作为关联条件。 ③ ref非唯一性索引扫描返回索引过滤的数据可为多条常出现在关联查询中。 ④ range使用索引进行范围扫描一般就是在where语句中出现between、 、、in等。 ⑤ index:index与ALL的区别为index类型只遍历索引树。 ⑥ ALL扫描全表数据行。5possible_keys表示查询时可能使用到的索引。6key查询时真正使用到的索引如果没有选择索引则显示是NULL。7key_len显示索引中使用的字节数可以判断是否全部使用了组合索引。8ref表示上述表的连接匹配条件即哪些列或常量被用于查找索引列上的值。9rows显示MySQL根据表统计信息估算找到所需的记录要读取的行数越小越好。10Extra该列包含MySQL解决查询的说明和描述包含不适合在其他列中显示但是对执行计划非常重要的额外信息。 ① Using where表示查询需要通过where条件查询数据。 ② Using temporary使用临时表保存中间结果常见的情况有使用distinct关键字join语句中使用order by或group by无索引列、order by与group by字段不同、union子查询等。 ③ Using filesort表示有order by操作而且无法利用索引完成的排序出现using filesort一般是因为order by后的条件导致索引失效最好进行优化。 ④ Using join buffer表明使用了连接缓存比如说在查询的时候多表join的次数非常多就将配置文件中缓冲区的join buffer调大一些。如果出现了这个值应当注意根据查询的具体情况可能需要添加索引来改进。 ⑤ Using index表示使用了索引覆盖select要查询的字段少于或等于创建的索引字段不需要访问表。如果与Using where一起出现则表示索引用于查询过滤还需回表查询出所需数据。 ⑥ Using Index Condition表示通过使用索引对存储引擎索引出的数据进行再过滤减少回表查询的次数。
总结一下针对explain命令生成执行计划
首先关注查询类型type列如果出现all关键字代表全表扫描没有用到任何index再看key列如果key列是NULL代表没有使用索引然后看rows列该列数值越大意味着需要扫描的行数越多相应耗时越长最后看Extra列要避免出现Using filesort或Using temporary这样的字眼这是很影响性能的。 对于没有走索引的查询通过添加适当的索引注意需对照原表上的索引看看有没有需要合并成联合索引避免构建过多的索引会占用空间和影响插入/更新的效率。
三、索引失效
下面列出常见的一些索引失效的场景 索引列上加函数在查询的索引列上使用内置函数都会让索引失效。 对索引列运算与使用函数相似都是会使得索引列值发生变化从而无法使用索引。 联合索引最左匹配原则不满足最左匹配原则索引不生效。 隐式类型转换select * from t_user where tel 123; tel字段是varchar类型但数值是int类型自动类型转换会使得索引失效。 范围查询阻断后续字段不能走索引范围查询包括 、、、、in、between。 负向查询和is NULL判断可能导致索引失效负向查询包括 NOT、、!、!、! 等。 使用like模糊查询前后都加了%”%李%”不会走索引, 而使用like “李%”会走索引。 asc和desc混用select * from t order by a asc, b desc。 or如果是单例索引or会使用索引如果是组合索引or不会使用索引。
四、索引操作
1、添加PRIMARY KEY(主键索引)
ALTER TABLE 表名 ADD PRIMARY KEY ( 字段 )2、添加UNIQUE(唯一索引)
ALTER TABLE 表名 ADD UNIQUE (字段)3、添加INDEX(普通索引)
ALTER TABLE 表名 ADD INDEX 索引名 ( 字段 )4、添加联合索引
ALTER TABLE 表名 ADD INDEX 索引名 ( 字段1, 字段2, ...)5、删除索引
ALTER TABLE 表名 DROP INDEX 索引名;五、profile 分析执行耗时 1、查询profile开启状态 show variables like ‘%profil%’ 1have_profiling确定是否支持 profile 2profiling是否开启profiling 3profiling_history_size定义MySQL服务器最近接收到的SQL条数。 2、开启profiling set profilingON set profiling_history_size30 3、查看最近运行的SQL – 查询最近30条SQL
show profiles;查看数据库版本show variables like ‘%version%’;
