李林超博客
首页
归档
留言
友链
动态
关于
归档
留言
友链
动态
关于
首页
Java
正文
MySQL性能优化知识总结
Leefs
2020-03-19 PM
1607℃
0条
# MySQL性能优化知识总结 ### 一、查询MySQL性能参数 **1. MySQL中 ,可以使用SHOW STATUS语句查询一些MySQL数据库的性能参数。** SHOW STATUS语句语法如下所示: ```java SHOW STATUS LIKE 'value'; ``` value是要查询的参数值 > Connections:连接MySQL服务器的次数 > > Uptime:MySQL服务器的上线时间 > > Slow_queries:慢查询的次数 > > Com_select:查询操作的次数 > > Com_insert:插入操作的次数 > > Com_update:更新操作的次数 > > Com_delete:删除操作的次数 SQL语句示例: ```sql -- 查询MySQL服务器的连接次数 show status like 'Connections'; -- 查询MySQL服务器的慢查询次数,慢查询次数参数可以结合慢查询日志,找出慢查询语句,然后针对慢查询语句进行表结构优化或查询语句优化 show status like 'Slow_queries'; ``` **2. 分析查询语句** 通过对查询语句的分析,可以了解查询语句执行的情况,找出查询语句执行的瓶颈,从而优化查询语句。 mysql中提供了EXPLAIN语句和DESCRIBE语句,用来分析查询语句。 在查询语句前面加上EXPLAIN可以分析出所查询的表的一些特征 例如: ```mysql explain select * from ep_book; ``` ![MySQL性能优化知识总结01.png][1] **查询结果说明** + id:select识别符,select的查询序列号 + select_type:标识select语句的类型 | 类型名称 | 说明 | | ---------------------------------------- | --------------------------------------------------------- | | SIMPLE(simple) | 表示简单查询,其中不包括连接查询和子查询 | | PRIMARY(primary) | 表示主查询,或者是最外层的查询语句 | | UNION(union) | 表示连接查询的第2个或者后面的查询语句 | | UNION RESULT(union result) | 连接查询的结果 | | SUBQUERY(subquery) | 子查询的第1个select语句 | | DEPENDENT SUBQUERY(dependent subquery) | 子查询的第1个select,取决于外面的查询 | | DEPENDENT UNION(dependent union) | 连接查询中的第2个或者后面的select语句,取决于外面的查询。 | | DERIVED(derived) | 导出表的SELECT(FROM子句的子查询) | + table:表示查询的表 + partitions:如果查询是基于分区表的话,会显示查询将访问的分区 + type:表示表的连接类型 下面按照从最佳类型到最差类型的顺序给出各种连接类型 | 连接类型 | 说明 | | --------------- | ------------------------------------------------------------ | | system | 该表是仅有一行的系统表,这是const连接类型的一个特例 | | const | 数据表最多只有一个匹配行(通过唯一值为条件进行查询,如:主键id) | | eq_ref | 对于每个来自前面的表的行组合,从该表中读取一行。当一个索引的所有部分都在查询中使用并且索引是UNIQUE或者PRIMARY KEY时候,即可使用这种类型。 | | ref | 对于来自前面的表的任意行组合,将从该表中读取所有匹配的行。这种类型用于索引既不是UNION也不是primaey key的情况,或者查询中使用了索引列的左子集,即索引中左边的部分组合。ref可以用于使用=或者<=>操作符的带索引的列。 | | ref_or_null | 该连接类型如果ref,但是如果添加了mysql可以专门搜索包含null值的行,在解决子查询中经常使用该连接类型的优化。 | | index_merge | 该连接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素 | | unique_subquery | 该类型替换了下面形式的in子查询的ref。是一个索引查询函数,可以完全替代子查询,效率更高。 | | index_subquery | 该连接类型类似于unique_subquery,可以替换in子查询,但是只适合下列形式的子查询中非唯一索引。 | | range | 只检索给定范围的行,使用一个索引来选择行。key列显示使用了那个索引。key_len包含所使用索引的最长关键元素。当使用=,<>,>,>=,<,<=,is null,<=>,between或者in操作符,用常量比较关键字列时,类型为range | | index | 该连接类型与all相同,除了只扫描索引树 | | all | 对于前面的表的任意行组合,进行完整的表扫描。如果表是第一个没有标记const的表,这样不好,并且在其他情况下很差。通常可以增加更多的索引来避免使用all连接。 | 补充: eq_ref:可以用于使用“=”操作符比较带索引的列。 比较值可以为常量或者一个在该表前面所读取的表的列的表达式。 例如: ```mysql EXPLAIN SELECT * FROM user,db_company WHERE user.company_id = db_company.id; ``` + possible_keys:possible_keys列指出mysql能使用哪个索引在该表中找到行。如果该列是null,则没有相关的索引。在这种情况下,可以通过检查where子句看它是否引起某些列或者适合索引的列来提高查询性能。如果是这样,可以创建适合的索引来提高查询的性能。 + key:表示查询实际使用到的索引,如果没有选择索引,该列的值是null,要想强制mysql使用或者忽视possible_key列中的索引,在查询中使用force index、use index或者ignore index。 + key_len:表示mysql选择索引字段按照字节计算的长度,如果健是null,则长度为null。注意通过key_len值可以确定mysql将实际使用一个多列索引中的几个字段 + ref:表示使用哪个列或者常数或者索引一起来查询记录。 + rows:显示mysql在表中进行查询必须检查的行数。 + rows:显示mysql在表中进行查询必须检查的行数。 **DESCRIBE语句的使用方法与EXPLAIN语句是一样的,并且分析结果也是一样的。** ### **二、 索引对查询速度的影响** mysql中提高性能的最有效的方式就是对数据表设计合理的索引。索引提供了高效访问数据的方法,并且加快了查询的速度。因此,索引对查询的速度有着至关重要的影响。使用索引可以快速的定位到表中的某条记录,从而提高数据库查询的速度,提高数据库的性能。如果查询的时候没有使用索引,查询语句将扫描表中的所有记录。在数据量大的情况下,这样查询的速度会很慢。如果使用索引进行查询,查询语句可以根据索引快速定位到待查询的记录,从而减少查询的记录数,达到提高查询速度的目的。 ##### **1. 使用索引和不使用索引的区别** 不使用索引rows列的值是26,说明此查询语句扫描了26条记录。 ![MySQL性能优化知识总结02.png][2] 使用索引rows列的值是1,说明此查询语句扫描了1条记录。查询速度自然变快了。这种方法也可以判断自己添加的索引是否起了作用。 ![MySQL性能优化知识总结03.png][3] ##### **2. 几种索引失效的情况** 索引可以提高查询的速度,但并不是使用带有索引的字段查询时,索引都会起到作用。下面是几种比较特殊的情况。在这些情况下,有可能使用带有索引的字段查询时,索引并没有起到作用。 **1. 使用like关键字的查询语句** 在使用like关键字进行查询的查询语句中,如果匹配字符串的第一个字符为"%",索引不会起作用。只有"%"不在第一个位置,索引才会起到作用。 ![MySQL性能优化知识总结04.png][4] 使用模糊查询,”%“在后面的结果,显示索引起了作用了的。 ![MySQL性能优化知识总结05.png][5] **2. 使用多列索引的查询语句** mysql可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引,只有查询条件中使用了这些字段中第1个字段的时候,索引才会被使用。 ![MySQL性能优化知识总结06.png][6] 多列索引的第一个字段作为查询条件的时候,发现索引起了作用了的: ![MySQL性能优化知识总结07.png][7] **3. 使用or关键字的查询语句** 使用语句的查询条件中只有or关键字,且or前后的两个条件中的列都有索引时,查询中才使用索引。否则,查询将不适用索引。 ![MySQL性能优化知识总结08.png][8] 使用name索引,sex索引,查询出符合条件的。 ![MySQL性能优化知识总结09.png][9] ### 三、SQL语句优化方法 1. 任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段 2. **如果语句能够避免子查询的使用,就尽量不用子查询** 3. **计算记录条数** 和一般的观点相反,count(*) 比count(1)稍快 4. **用EXISTS替代IN** 在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。在这种情况下,使用EXISTS(或NOT EXISTS)通常将提高查询的效率。 5. **用NOT EXISTS替代NOT IN** 在子查询中,NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。 *附:参考文章链接地址https://www.cnblogs.com/biehongli/p/10194259.html* [1]: https://lilinchao.com/usr/uploads/2020/03/538811901.png [2]: https://lilinchao.com/usr/uploads/2020/03/1062312240.png [3]: https://lilinchao.com/usr/uploads/2020/03/771520912.png [4]: https://lilinchao.com/usr/uploads/2020/03/2068083790.png [5]: https://lilinchao.com/usr/uploads/2020/03/3327210373.png [6]: https://lilinchao.com/usr/uploads/2020/03/2477363409.png [7]: https://lilinchao.com/usr/uploads/2020/03/3296874415.png [8]: https://lilinchao.com/usr/uploads/2020/03/2982564525.png [9]: https://lilinchao.com/usr/uploads/2020/03/78961626.png
标签:
MySQL
非特殊说明,本博所有文章均为博主原创。
如若转载,请注明出处:
https://www.lilinchao.com/archives/759.html
上一篇
SpringBoot常用注解知识总结
下一篇
Redis事务简介
取消回复
评论啦~
提交评论
栏目分类
随笔
2
Java
326
大数据
229
工具
31
其它
25
GO
43
标签云
工具
Eclipse
VUE
GET和POST
机器学习
随笔
Azkaban
Jquery
Java编程思想
JavaSE
Sentinel
栈
SpringCloudAlibaba
Spark
链表
Zookeeper
Elasticsearch
Ubuntu
Filter
FastDFS
Scala
数据结构和算法
BurpSuite
Beego
Kibana
MyBatisX
Netty
Jenkins
正则表达式
LeetCode刷题
友情链接
申请
范明明
庄严博客
Mx
陶小桃Blog
虫洞