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

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、索引不适合建在有大量重复数据的字段上,比如性别,排序字段应创建索引
  • 0
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值