李林超博客
首页
归档
留言
友链
动态
关于
归档
留言
友链
动态
关于
首页
Java
正文
MySQL性能优化知识总结
Leefs
2020-03-19 PM
1668℃
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://lilinchao.com/archives/759.html
上一篇
SpringBoot常用注解知识总结
下一篇
Redis事务简介
取消回复
评论啦~
提交评论
栏目分类
随笔
2
Java
326
大数据
229
工具
31
其它
25
GO
47
标签云
BurpSuite
Elastisearch
MyBatisX
VUE
CentOS
Hadoop
数据结构和算法
Eclipse
线程池
算法
Java阻塞队列
Yarn
DataWarehouse
Hbase
队列
Git
Shiro
Spring
稀疏数组
Kafka
栈
Livy
SpringCloudAlibaba
Kibana
Tomcat
Golang
人工智能
NIO
Filter
Elasticsearch
友情链接
申请
范明明
庄严博客
Mx
陶小桃Blog
虫洞