Mysql数据库Sql语句优化汇总

• Mysql数据库Sql语句优化汇总
  • 一.基础优化
  • 二.索引相关
  • 三.其他优化

Mysql数据库Sql语句优化汇总

一.基础优化

1.selct * from table 改为 select field_1, field_2, field_3 from table，只查询需要的字段，减小通信数据包的大小

2.sql后面加上 limit 限制，防止查询的数据量太大，导致系统OOM

3.使用批量 insert 代替for循环单条 insert

4.设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率

5.mysql库主从读写分离

6.大表要分库分表，减少单表数据量，提高查询效率

7.合理评估，是否需要增加缓存机制（增加 redis 缓存）

8.选择合适的数据类型，像整形尽可能选择占用空间小的类型，使用 TINYINT 或 SMALLINT 代替 INT

9.能用整形的就不用使用字符串

10.使用 DATETIME 代替 TIMESTAMP

TIMESTAMP 使用4个字节存储空间， DATETIME 使用8个字节存储空间。因而， TIMESTAMP 只能表示1970 - 2038年，比 DATETIME 表示的范围小得多，而且 TIMESTAMP 的值因时区不同而不同。

11.通常来讲，没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。

二.索引相关

1.where 语句不要使用函数，否则无法使用索引

2.前缀索引

如果列长度很长，可以开始的部分字符，提高索引效率

3.避免多个范围查询

多个索引字段的范围查询， MYSQL只会使用其中的一个索引，无法同时使用它们

4.覆盖索引

如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。覆盖索引是非常有用的工具，可以极大的提高性能。

5.合理的使用组合索引，提高查询效率

6.组合索引最左匹配原则

以组合索引(a,b,c)为例，建立这样的索引相当于建立了索引a、ab、abc三个索引。对于其他顺序,不能使用该组合索引。

7.模糊查询只有在 like ‘xxx%’ 的时候才会使用到索引

8.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

三.其他优化

1.偏移量非常大的 Limit 分页

当需要分页操作时，通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY字句。如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作。

一个常见的问题是当偏移量非常大的时候，比如：LIMIT 10000 20这样的查询，MySQL需要查询10020条记录然后只返回20条记录，前面的10000条都将被抛弃，这样的代价非常高。

优化这种查询一个最简单的办法就是尽可能的使用覆盖索引扫描，而不是查询所有的列。然后根据需要做一次关联查询再返回所有的列。对于偏移量很大时，这样做的效率会提升非常大。考虑下面的查询：

SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;

如果这张表非常大，那么这个查询最好改成下面的样子：

SELECT film.film_id,film.description
FROM film INNER JOIN (
SELECT film_id FROM film ORDER BY title LIMIT 50,5
) AS tmp USING(film_id);

这里的延迟关联将大大提升查询效率，让MySQL扫描尽可能少的页面，获取需要访问的记录后在根据关联列回原表查询所需要的列。

有时候如果可以记录上次取数据的位置，那么下次就可以直接从该记录的位置开始扫描，这样就可以避免使用 OFFSET ，比如下面的查询：

SELECT id FROM t LIMIT 10000, 10;

改为：

SELECT id FROM t WHERE id > 10000 LIMIT 10;

其他优化的办法像关联到一个冗余表，冗余表中只包含主键列和需要做排序的列。

2.主键id

主键id，mysql的官方推荐最好使用自增id。应避免使用uuid，使用自增id可以减少页分裂和碎片的产生。

引用:
1.https://zhuanlan.zhihu.com/p/59818056

2.https://blog.csdn.net/madongyu1259892936/article/details/98978068