MySQL进阶之SQL优化


  苦逼程序员      182   
  2020-08-25      数据库      

1.使用show status like 'Com%';可以查到各种语句执行的次数。

Com_select:执行select操作的次数。

Com_insert:执行insert的次数

Com_update:执行update的次数

Com_delete:执行delete的次数

对于事务型的应用,Com_commit和Com_rollback可以了解事务的提交和回滚。如果回滚频繁,可能应用存在问题。

使用show status like 'Slow%';可以查到慢查询次数。其中 Slow_queries:慢查询次数。

2.定位执行效率较低的SQL语句

2.1 使用——log-slow-queries[=file_name]选项启动MySQL服务,会记录超过long_query_time秒的sql日志到文件。

2.2 查找到低效率的SQL后,通过EXPLAIN分析低效sql的执行。关键点在于type:表的连接类型,key:实际使用索引,rows:扫描行的数量。可以根据extra进行优化。

3.索引问题

1.mysql可以对字段的前4个字符进行索引。

2.最左前缀:对于创建的符合索引,只要查询的条件用到了最左边的列,索引一般就会被使用。

3.like的查询,只有%号不在第一个字符,索引才可能被用上。(假如like的条件已经含有全部搜索条件,也没有必要用索引)

4.存在索引但不使用索引

1.因为MySQL估计索引比全表扫描更慢

2.用or分割开的条件,如果前面的列有索引,后面的列没有索引,那么涉及的索引都不会被用到。

3.如果不是索引列的第一部分(最左)

4.如果like以%开始

5.如果列类型是字符串,不加引号

5.优化内存空间,对数据库数磁盘碎片进行整理

optimize table test;

执行期间会表锁定,需要在数据库不繁忙的时候执行。

6.优化insert语句

1.一次插入多行,可以使用多个值表的insert语句,例如:insert into test values(1,2),(1,3);

2.索引文件和数据文件分在不同的磁盘存放,利用建表选项。

7.优化group by子句

可以使用order by null避免进行排序。(避免filesort)

8.优化子查询

使用join代替子查询操作。

因为join不需要在内存中创建临时表来完成这个需要2个步骤的工作。

9.优化or条件

对于含有OR 的查询子句,如果要利用索引,则OR 之间的每个条件列都必须用到索引;

如果没有索引,则应该考虑增加索引。

10.使用sql提示

使用use index提供希望mysql参考的索引,节省mysql筛选索引的时间

例如:explain select * from test use index(ind_test) where id= 1;

使用force index参照上面的,这个不是让mysql作为参考,而是强制使用。

11.表结构优化

使用PROCEDURE ANALYSE()函数确定要优化的列。

会返回字段现有数据的最小值和最大值,以及最小和最大长度。最后还会给出合理的建议字段。

12.拆表

垂直拆表,将不常用的列放入另一个表。

水平拆表:表的数据具有独立性,如按照年进行分表。

缺点,查询所有数据需要联合操作。

13.逆规范化

1.增加冗余列,避免查询时的连接操作。

2.增加派生列,避免使用函数多次计算。

3.重新组表,如果许多用户需要查看两个表连接出来的结果,可以组成一个表来减少连接。

4.分表,拆分表

注意:逆规范化虽然带来了速度,但是也对数据完整性提出了要求,一般在应用层进行维护,但是易出疏漏,特别是需求变更时。这里不建议使用触发器,对数据的任何修改触发对复制列或者派生列的相应修改。

14.使用中间表

数据量较大的表,统计时可以创建临时使用的中间表,使用中间表可以提高统计的效率,并且不会对线上应用产生负面影响。

作者:怀老师

ps:以上是MySQL进阶之SQL优化全部内容,希望文章能够帮你解决MySQL进阶之SQL优化所遇到的游戏开发问题。
本文收录在 游戏编程 🕹️ - 学习数据库专题,分享走一走~

 优化   

猜你喜欢 全系列


您可以在登录后,发表评论