1 MySQL的执行流程

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

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

SQL执行流程图：

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、索引不适合建在有大量重复数据的字段上，比如性别，排序字段应创建索引

网站首页 > 技术文章正文

45个数据库SQL性能优化方案，SQL性能提升好几倍!

1 MySQL的执行流程

2 数据库使用规范

3 45个SQL优化方案

猜你喜欢

网站首页 > 技术文章 正文

45个数据库SQL性能优化方案，SQL性能提升好几倍!

1 MySQL的执行流程

2 数据库使用规范

3 45个SQL优化方案

猜你喜欢

网站首页 > 技术文章正文