45个数据库SQL性能优化方案

以你之姓@ 2024-03-23 13:52 84阅读 0赞

## 1 MySQL的执行流程 ##

为了更好的取优化SQL，这里先总结了一下SQL执行流程：

1、客户端请求到服务端，服务端通过数据库连接池，用JDBC连接数据库驱动。
    2、数据库端的连接池接收到服务端的连接，然后把服务端对数据库的请求交给数据库的线程。
    3、通过数据库获取SQL接口，获取到连接中的SQL语句。
    4、然后是通过数据库解析器对SQL进行解析（解析成数据库认识的语言）。
    5、然后通过SQL查询优化器获得最优执行SQL。
    6、最终把最优SQL交给数据库执行器。
    7、执行器选择当前数据库的存储引擎（图例采用InnoDB为例）。
    8、然后先去数据库的BufferPool（缓存）中查询，这里如果命中了缓存则直接返回结果。
    9、如果没有在缓存中命中结果，那么会去硬盘中读取数据，然后返回结果集并缓存起来，以便下次取用。

SQL执行流程图：

![e99cc83570e649b3983a24464296a974.png][]

## 2 数据库使用规范 ##

合理使用数据库规范也是SQL性能优化的前提。

1、表命名必须小写字母且下划线分割单词；
    2、表名称及字段名称不要用数据库的关键字；
    3、数据库表命名不超过32个字符；
    4、遵循数据库三范式；
    5、合理的反三范式使用（比如合理的冗余字段再一张表）；

## 3 45个SQL优化方案 ##

*  涉及到钱的字段用decimal

非精准浮点：float,double。
    精准浮点：decimal。
    
    Decimal：
      1、底层存储的数据转成字符串形式存储，因此能保持精度；
      2、可存比bigint更大的数据；
      3、在计算时不会丢失精度；
      4、占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节；

*  尽量不用ENUM类型

1、禁止把数字作为ENUM值；
    2、修改ENUM值需用ALTER语句；
    3、ENUM类型使用ORDER BY效率极低；

*  避免distinct过滤字段过多

1、distinct使用时CPU占用时间长
    2、对重复数据比较、过滤的过程会占用系统资源，如cpu时间。
    3、查询返回字段多时，用distinct数据库会对重复数据进行过滤。

*  where后默认值替换null

1、如果默认值替换null，往往可以解决这个问题。
    2、数据库优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效，这些都是优化器自动放弃索引的；

*  where后避免使用!=、<>

1、!=、<>可能会让索引失效。
    2、在where后用!=、<>，引擎可能会不用索引而全表扫描。

*  连接表，优先用inner join

inner join 、left join和right join三种连接表：

1、inner join
    内连接，只保留两张表中完全匹配的结果集；
    
    2、left join
    会返回左表所有的行，即使在右表中没有匹配的记录；
    
    3、right join
    会返回右表所有的行，即使在左表中没有匹配的记录；

1、用left join左表数据结果尽量小，条件尽量放到左边处理；
    2、如果返回结果相同，优选inner join；
    3、如果inner join是等值连接，返回的行数比较少，性能相对会好一点；

**性能最优原则：小表驱动大表，小的数据集驱动大的数据集；**

*  表必用Innodb存储引擎

所有表尽量用Innodb存储引擎。

1、Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。
    2、当Innodb无法满足的功能（如列存储，存储空间数据等）才不使用Innodb。
    3、mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb。

*  Innodb表必有主键

Innodb是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。表可以有多个索引，但是存储顺序只可一种，也就是主键只能有一个。

Innodb是按照主键索引的顺序来组织表的：
      1、主键最好用自增数字ID，效率最高；
      2、更新频繁的的列不适合作为主键；
      3、如果用UUID、MD5、HASH、字符串列作为主键，则无法保证数据的顺序增长；

*  数据库和表的字符集统一

数据库和表的字符集统一使用UTF8。

1、兼容性更好。
    2、不同的字符集进行比较前需要进行转换，这样会造成索引失效。
    3、如果存储emoji表情的，要采用utf8mb4字符集。
    4、可避免字符集转换产生的乱码。

*  避免使用select \*

查询语句要制定具体的字段，不要查询所有。

select *弊端：
      1、增加了回表的可能性；
      2、查询效率低；
      3、增加不必要的消耗，比如CPU、IO、内存、网络带宽；
      4、增加了使用覆盖索引的可能性；

*  group by语句效率优化

先过滤，后分组，这样可以在执行到该语句前，把不需要的记录过滤掉。

正确使用示例：

select a，avg（b） from user 
    where a ='1' or b = '2' 
    group by a;

禁用：group by 字段 having 条件;

*  用truncate清空表

1、truncate table不能用于参与了索引视图的表。
    2、truncate table比 delete快，且用到的系统和事务日志资源少。
    3、delete一行数据都要在事务日志中记录一行。truncate table通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

*  delete、update语句，加个limit或者循环分批次删除

1、降低写错SQL清空表数据的风险。
    2、SQL效率很可能更高SQL中加了limit 1，如果第一条就命中目标return， 没有limit的话，还会继续执行扫描表。
    3、避免长事务delete执行时,如果age加了索引，MySQL会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。（4）数据量大的话，容易把CPU打满如果你删除数据量很大时，不加 limit限制一下记录数，容易把cpu打满，导致越删越慢。（5）锁表一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作。

*  UNION ALL操作符替代UNION

1、UNION ALL替代UNION，前者只是简单的将两个结果合并后就返回。
    2、UNION连接表后会结果集排序然后去掉重复数据，在返回结果。

*  IN使用的字段不要过多

MySQL的IN中的常量全部存储在一个数组中，这个数组是排序的。如果值过多，产生的消耗也是比较大的。如果是连续的数字，可以使用between代替，或者使用连接查询替换。

*  批量插入代替普通插入

批量处理是一次事务开启和提交，而每次单条插入都会提交一次事务。

使用示例：

INSERT INTO product (id,name) VALUES(1,'123123'),(2,'123123');

*  索引列上不用内置函数

索引列上使用内置函数，会导致索引失效。

*  复合索引使用时排序优化

排序时应按照复合索引中各列的顺序进行排序，否则排序性能会比较差。

*  复合索引最左特性

复合索引不满足最左原则，索引一般会失效。

当创建一个复合索引的时，如创建(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。

使用案例：

1、复合索引全使用，按左侧顺序使用，索引生效
    SELECT * FROM test WHERE name='12321' AND salary=1000
    
    2、如下虽违背了最左特性，但数据库执行时会进行优化，也会使用到索引，自动给颠覆过来
    SELECT * FROM test WHERE salary=1000 AND NAME='12321'
    
    
    3、满足复合索引的最左特性
    SELECT * FROM test WHERE name='12312'
    
    4、创建复合索引
    ALTER TABLE test ADD INDEX idx_name_salary (name,salary)
    
    5、没有出现左边的字段，则不满足最左特性，复合索引会失效
    SELECT * FROM test WHERE salary=1000

*  必要用force index强制使用索引

根据自己业务需求，有时候可以用forceindex来强制优化器使用创建的索引。

*  like语句优化

模糊查询的一些场景，可能让索引失效，因此使用时需要注意。

1、尽量不用避免模糊查询；
    2、全模糊查询是无法优化的，一定要使用的话建议使用搜索引擎。
    3、如果必须用like，应尽量采用右模糊查询， 即like ‘…%’，是会使用索引的；
    4、左模糊like ‘%...’无法直接使用索引，但可以利用reverse + function index的形式，变化成 like ‘…%’；

*  避免产生大事务操作

大批量操作数据时，如果在一个事务中，会造成表中大批量数据进行锁定，从而导致大量的阻塞，对数据库性能影响很大。

尽量把查询语句剥离事务逻辑。

*  用预编译语句操作数据库

1、能解决动态SQL所带来的SQL注入的问题；
    2、预编译语句可以重复使用这些计划，减少SQL编译所需要的时间；
    3、只传参数，比传递SQL语句更高效。相同语句可以一次解析，多次使用，提高处理效率。

*  频繁的查询优先考虑使用覆盖索引

覆盖索引：就是包含了所有查询字段(where,select,ordery by,group by包含的字段)的索引。

覆盖索引的好处：
      1、避免Innodb表进行索引的二次查询
      2、可以把随机IO变成顺序IO加快查询效率

*  合理选择索引列的顺序

1、使用最频繁的列放到联合索引的左侧。
    2、区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）。
    3、尽量把字段长度小的列放在联合索引的最左侧（字段长度越小，一页能存储的数据量越大，IO性能也就越好）。

*  禁止表中建立过多索引

MYSQL每张表最多建16个索引。并不是索引越多越好，要合理的建索引，因为所有创建本身也需要时间和存储。

索引不宜太多，一般5个以内。

1、一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要；
    2、索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间；
    3、索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率；
    4、索引表的数据是排序的，排序也是要花时间的；
    5、insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定；

*  连表查询连表避免过多

表连接不宜太多，一般5个以内。

1、应该把连接表拆开成较小的几个执行，可读性更高
    2、如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了
    3、阿里规范中，建议多表联查三张表以下
    4、关联的表个数越多，编译的时间和开销也就越大
    5、每次关联内存中都生成一个临时

*  统一SQL语句的写法

select * from a;
    select * From A;

上面的两条sql语句，平常我们看来是一样的，实际数据库优化器认为他们是不一样的，这样数据库解析器就会解析两次，生成两个不同的执行计划。

因此相同的sql语句要统一写法，这样可以减少数据库解析次数，避免生产两个不同执行计划。

*  SQL要尽量简单化

SQL执行计划是可以被重用的，SQL越简单，被重用的概率越大，生成执行计划也是很耗时的。

嵌套子查询、3张表以内关联查询平常很常见。如果超过3层嵌套查询，查询优化器很容易给出错误的执行计划，影响SQL效率。

*  临时表注意事项

1、如果数据量不大，为了缓和系统表的资源，应先create table，然后insert；
    2、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除。先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
    3、避免频繁创建和删除临时表，以减少系统表资源的消耗；
    4、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log；

*  大SQL查询拆多个小查询

通常为了达到更好的性能以及更好的数据控制，可以把大SQL查询拆多个小查询。

*  explain分析SQL执行计划

type：
     	1、all：全表扫描；
      2、system：表仅有一行，基本用不到；
      3、eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的连接类型，除了const类型；
      4、ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取；
      5、const：表最多一行数据配合，主键查询时触发较多；
      6、性能排名：system > const > eq_ref > ref > range > index > all。
      7、实际sql优化中，最后达到ref或range级别。
      8、range：只检索给定范围的行，使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range；
      9、index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小；
     
    Extra常用关键字：
      1、Using index：只从索引树中获取信息，而不需要回表查询；
     	2、Using temporary：mysql常建一个临时表来容纳结果，典型情况如查询包含可以按不同情况列出列的GROUP BY
      3、和ORDER BY子句时；
      4、Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。需要回表查询。

*  读写分离与分库分表

当数据量达到一定的数量之后，通常用读写分离、分库、分表层面来优化了。

1、分表适用于中等数据量；
    2、分库与分表一般是结合着用，适用于大数据量的存储了，这也是现在大型互联网公司解决数据存储的方法之一。
    3、读写分离适用于较小一些的数据量；

*  深分页优化

深分页优化方案：标签记录法、延迟关联法。

使用示例：

1、标签记录法：
    SELECT id, quarantine_num, quar_product_num FROM quarantine_detail WHERE id > 100000 LIMIT 10;
    
    2、延迟关联法：
    SELECT qd2.id, qd2.quarantine_num, qd2.quar_product_num FROM quarantine_detail qd2 INNER JOIN ( SELECT qd1.id FROM quarantine_detail qd1 WHERE qd1.quarantine_num > 1 LIMIT 120000, 10 ) AS a ON a.id = qd2.id;

*  MYSQL单表数据量控制在500w以内

MySQL通常我们控制数据量小于500w，过大会造成修改表结构，备份，恢复都会有很大的问题。

1、日志数据：
    	可以用历史数据归档。
    2、业务数据：
    	分库分表等控制数据量大小。

*  TEXT或BLOB类型只能使用前缀索引

MySQL中索引字段长度是有限制的，因此TEXT或BLOB类型只能使用前缀索引。

*  把BLOB或TEXT列剥离到扩展表中

Mysql内存临时表不支持TEXT、BLOB类型，因此此类型数据在查询中，就不能用内存临时表，必须用磁盘临时表进行。而且数据库还要进行二次查询，sql性能就显得很差。

所以最好把把BLOB或TEXT列剥离到单独的扩展表中存储。

*  禁止在数据库中存储图片，文件等大的二进制数据

通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时。

通常存储文件到文件存储服务器或者MongoDB等。

*  表尽量不要建预留字段

1、对预留字段类型的修改，会对表进行锁定；
    2、预留字段的命名很难做到见名识义；
    3、预留字段无法确认存储的数据类型，所以无法选择合适的类型；

*  尽量做到冷热数据分离，减小表的宽度

Mysql限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节.

1、经常一起使用的列放到一个表中（避免更多的关联操作）。
    2、更有效的利用缓存，避免读入无用的冷数据；
    3、减少磁盘IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO）；

*  谨慎用Mysql分区表

1、谨慎选择分区键，跨分区查询效率可能更低；
    2、建议采用物理分表的方式管理大数据。
    3、分区表在物理上表现为多个文件，在逻辑上表现为一个表；

*  常见其它优化

1、SQL书写格式，关键字大小保持一致，使用缩进。
    2、很多时候用 exists 代替 in 是一个好的选择
    3、where后面的字段，留意其数据类型的隐式转换。
    4、修改或删除重要数据前，要先备份。
    5、如果排序字段没有用到索引，就尽量少排序
    6、当只需要一条数据的时候，使用limit 1limit 1
    	可以避免全表扫描，找到对应结果就不会再继续扫描了。
    
    7、所有表和字段都需要添加注释使用comment从句添加表和列的备注，从一开始就进行数据字典的维护。
    8、伪删除设计
    9、尽量避免使用游标因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
    10、尽量把所有列定义为NOT NULL
    	NOT NULL列更节省空间，NULL列需要一个额外字节作为判断是否为 NULL的标志位。NULL
    列需要注意空指针问题，NULL列在计算和比较的时候，需要注意空指针问题。
    11、索引不适合建在有大量重复数据的字段上，比如性别，排序字段应创建索引

[e99cc83570e649b3983a24464296a974.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/e76d006c39964940b725823c4bef7a32.png