MySql性能优化之SQL语句优化

最新推荐文章于 2021-03-10 00:09:07 发布

李芳平的博客

最新推荐文章于 2021-03-10 00:09:07 发布

阅读量179

点赞数 1

分类专栏： mysql 文章标签： mysql性能优化

本文链接：https://blog.csdn.net/a1224645904/article/details/98632563

版权

mysql 专栏收录该内容

8 篇文章 1 订阅

订阅专栏

在面试的时候被问到“数据库优化懂多少”，要是以前我定是一脸蒙逼，现在多少还算是知道那么一点点，话不多说，直接开干。

MySql语句优化的相关知识

索引优化

通过show status命令了解各种SQL的执行效率
格式：mysql>show [session|global] status;
其中： session（默认）表示当前连接；global表示自数据库启动至今
```
mysql>show status;
mysql>show global status;
```
上诉两个方法可以查询到数据库的所有状态，但是一般比较常用的为下边的两种状态
```
mysql>show status like 'Com_%'；
mysql>show global status like 'Com_%'；
```
上诉两方法可以查到143种状态，其中比较常用的为com_insert，com_select，com_update，com_delete，分别为插入数据的次数，查询的次数，更新的次数和删除的次数，通过对次数的分析，了解服务器的运行状况。
只针对InnoDB存储引擎的：
```
mysql>show status like 'innodb_rows_read%';		#select操作的次数
mysql>show status like 'innodb_rows_updated%'	#update操作的次数
mysql>show status like 'innodb_rows_inserted%'	#insert操作的次数
mysql>show status like 'innodb_rows_deleted%'	#delete操作的次数
```
其他状态的查询
```
mysql>show status like 'connections';	#连接mysql的次数
mysql>show status like 'uptime‘； 		#服务器已经工作的时间
mysql>show status like 'slow_queried';	#服务器慢查询的次数
```
检验SQL语句的执行效率（重点看select_type，rows，possible_key，key）
```
mysql>explain select * from tl where id=1000;
mysql>desc select * from t1 where id=1000;
```
以上两个语句的得到的结果一样，如果查看不方便可以在末尾加上\G，查询结果如下

每一行的解释：
添加索引
1. 索引的使用：一般在where条件后的字段中设置索引，可以对数据查询起到提速的作用。
```
mysql>alter table t1 add index in_name(name);
```
2. 使用like的查询，%在第一个字符前面则不使用索引，否则有可能使用索引
```
mysql>select * from tl where name like '%user'; 	#不会使用索引
mysql>select * from tl where name like 'user%';		#有可能使用索引
```
3. 如果字段设置了索引，则查询时使用null，会用到索引。
存在索引但是不用索引的情况
a). sql估计使用索引比全局扫描更慢，则放弃使用索引。
b).where条件中 or 前后的两个字段如果有一个不设置索引，则在查询时放弃使用索引。
c).如果一个字段类型为varchar类型，但是在查询的过程赋值为一个整型的变量，则在查询的过程中放弃使用索引。
name为varchar类型，且设置索引，使用如下查询语句
select * from tl where name=123; #该查询语句不会使用name索引
查看索引的使用情况
如果索引正在工作，Handler_read_key的值将很高，这个值代表了一行被索引值读的次数。
Handler_read_rnd_next的值高则意味着查询运行低效，应该建立索引补救。
mysql>show status like 'Handler_read%';

表优化

使用check检查表状态
mysql>check table tl
定期优化表：当你删除数据时，mysql并不会回收已删除的数据所占据的存储空间，以及索引位。而是空在那里，而是等待新的数据来弥补这个空缺，这样就有一个缺少，如果一时半会，没有数据来填补这个空缺，那这样就太浪费资源了。所以对于写比较频繁的表，要定期进行optimize，一个月一次，看实际情况而定了。
mysql>optimize table user; #对于数据很多的表应选择访问量比较少的情况下进行
大家可以看看这个详细的介绍：optimize table 表优化问题