关于SQL 优化的问题

最新推荐文章于 2024-11-11 08:41:40 发布

XD_fybdw

最新推荐文章于 2024-11-11 08:41:40 发布

阅读量291

点赞数

分类专栏：数据库文章标签：数据库索引索引优化

本文链接：https://blog.csdn.net/XD_fybdw/article/details/80674625

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、首先当我们面对一个有 SQL 性能问题的数据库时，我们应该从何处入手来进行系统的分析，使得能够尽快定位问题 SQL 并尽快解决问题？？？

1.1通过 show status 命令了解各种 SQL 的执行频率
这里写图片描述

Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。
 Com_select：执行 select 操作的次数，一次查询只累加 1。
 Com_insert：执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
 Com_update：执行 UPDATE 操作的次数。
 Com_delete：执行 DELETE 操作的次数。
上面这些参数对于所有存储引擎的表操作都会进行累计。下面这几个参数只是针对
InnoDB 存储引擎的，累加的算法也略有不同。
 Innodb_rows_read：select 查询返回的行数。
 Innodb_rows_inserted：执行 INSERT 操作插入的行数。
 Innodb_rows_updated：执行 UPDATE 操作更新的行数。
 Innodb_rows_deleted：执行 DELETE 操作删除的行数。
通过以上几个参数，可以很容易地了解当前数据库的应用是以插入更新为主还是以查询
操作为主，以及各种类型的 SQL 大致的执行比例是多少。对于更新操作的计数，是对执行
次数的计数，不论提交还是回滚都会进行累加

1.2定位执行效率较低的 SQL 语句
通过查看日志得到
1.3 通过 EXPLAIN 分析低效 SQL 的执行计划
查询到效率低的 SQL 语句后，可以通过 EXPLAIN 或者 DESC 命令获取 MySQL
如何执行 SELECT 语句的信息；
这里写图片描述

possible_keys：表示查询时，可能使用的索引。
 key：表示实际使用的索引。
 key_len：索引字段的长度。
 rows：扫描行的数量。
 Extra：执行情况的说明和描述。

1.4 确定问题并采取相应的优化措施
根据分析结果进行优化，是否需要创建索引进行SQL语句的优化还是需要进行应用层优化

二、SQL语句的优化

2.1大批量插入数据
用 load 命令导入数据的时候，可以提高导入的速度

比如给User表导入数据
load data infile '/d:film_test.txt' into table user;

2.2优化 INSERT 语句
比如一次插入多值，可以减少与数据可的交互，因而提高效率

比如给user表一次插入多个值
insert int user values(1,"zhang")(2,"liu"）（3，"wang")

2.3优化 GROUP BY 语句
默认情况下，MySQL 对所有 GROUP BY col1，col2….的字段进行排序。如果查询包括 GROUP BY 但用户想要避免排序结果的消耗，则可以指定 ORDER BY NULL
禁止排序
2.4优化 ORDER BY 语句
MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。
WHERE 条件和 ORDER BY 使用相同的索引，并且 ORDER BY 的顺序和索引顺序相同，并且ORDER BY 的字段都是升序或者都是降序。
2.5优化 OR 条件
对于含有 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。MySQL 在处理含有 OR字句的查询时，实际是对 OR 的各个字段分别查询后的结果进行了 UNION。
三、应用层的优化
3.1可以考虑使用连接池
连接池的好处，先创建好，可以直接分配给应用使用，因此大大减少了创建新连接所耗费的资源。连接返回后，本次访问将连接交还给“连接池”，以供新的访问使用。在连接池这里也可以优化，比如：设置超时时间，超过超时时间则自动关闭连接，可以大大节省资源。
3.2避免应用实现业务逻辑的时候，多次通过SQL访问数据库

Select * from user a, product b where a.id=b.userid ;
可以进行优化减少对与数据库的交互
Select * from user a inner join product b on a.id=b.userid;

3.3数据层缓存的优化，(MyBatis框架的缓存机制)
MyBatis默认情况下是没有开启缓存的,除了局部的 session 缓存,可以增强变现而且处理循环依赖也是必须的。要开启二级缓存,你需要在你的 SQL 映射文件中添加一行:

 <cache/>
这个简单语句的效果如下：
1）映射语句文件中的所有 select 语句将会被缓存。
2）映射语句文件中的所有 insert,update 和 delete 语句会刷新缓存。
3）缓存会使用 Least Recently Used(LRU,最近最少使用的)算法来收回。
4）根据时间表(比如 no Flush Interval,没有刷新间隔), 缓存不会以任何时间顺序来刷新。
5）缓存会存储列表集合或对象(无论查询方法返回什么)的 1024 个引用。
6）缓存会被视为是 read/write(可读/可写)的缓存,意味着对象检索不是共享的,而且可以安全地被调用者修改,而不干扰其他调用者或线程所做的潜在修改。所有的这些属性都可以通过缓存元素的属性来修改。

3.4利用MySql的主从复制分流大批量的查询操作
为了性能考虑可以读写分离
比如电商平台，进行读写分离，主服务器进行添加、删除、修改（但是主服务器进行的修改需要立即同步到从服务器，这里的同步机制还未了解！！！），从服务器负责读。
3.5负载均衡
负载均衡（Load Balance）是实际应用中使用非常普遍的一种优化方法，它的机制就是利用某种均衡算法，将固定的负载量分布到不同的服务器上，以此来减轻单台服务器的负载，达到优化的目的。负载均衡可以用在系统中的各个层面中，从前台的 Web 服务器到中间层的应用服务器，最后到数据层的数据库服务器，都可以使用。