SQL优化

Java工程师的修炼之道
273 阅读3分钟

SQL优化很容易理解，就是通过给查询字段添加索引或者改写SQL提高其执行效率，一般而言，SQL编写有以下几个通用的技巧：

SQL优化

  • 合理使用索引

索引少了查询慢；索引多了占用空间大，执行增删改语句的时候需要动态维护索引，影响性能 选择率高（重复值少）且被where频繁引用需要建立B树索引；一般join列需要建立索引；复杂文档类型查询采用全文索引效率更好；索引的建立要在查询和DML性能之间取得平衡；复合索引创建时要注意基于非前导列查询的情况

  • 使用UNION ALL替代UNION

UNION ALL的执行效率比UNION高，UNION执行时需要排重；UNION需要对数据进行排序

  • 避免select * 写法

执行SQL时优化器需要将 * 转成具体的列；每次查询都要回表，不能走覆盖索引。

  • JOIN字段建议建立索引

一般JOIN字段都提前加上索引

  • 避免复杂SQL语句

提升可阅读性；避免慢查询的概率；可以转换成多个短查询，用业务端处理

  • 避免where 1=1写法
  • 避免order by rand()类似写法

RAND()导致数据列被多次扫描

查看执行计划

要想优化SQL必须要会看执行计划，执行计划会告诉你哪些地方效率低，哪里可以需要优化。我们以MYSQL为例，来认识一下执行计划。

通过explain sql 可以查看执行计划，如：

image.png

image.png

索引

创建唯一索引：

CREATE UNIQUE INDEX 索引名_IDX USING BTREE ON 表名 (字段);

显示索引名

show index from 表名;

创建索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

未加索引前： image.png

加索引后执行sql计划

explain
select * from A
        where org_code = '1025' and is_valid = 1
          and B in ('1','2','3');

image.png

删除索引

ALTER TABLE 表名 DROP INDEX idx_bar;

DBA建议

  • 当这个字段的值重复度很高的时候，也就是你说的枚举，单独建索引效果没多少。。但是你这个是加的联合索引，可以加进去，不加也无妨

  • 索引和是否分表是没有关系的，索引是在表上加的，加快查询；分表是为了减小单表的数据量，因为数据量大了，查询同样也会很慢，当很大很大的时候，用索引都不会有效果

  • 建索引的时候，把barcode放前面，可以测试一下，看看只建barcode和建三个字段的联合索引，有什么区别，用explain执行看一下结果

  • update、delete、select的结果数据量如果很多，即使有索引也是会很慢的

  • 可以优化代码，选择少量多次更新，update 10000条数据是一个大事务，大事务发送到数据库上，不是一个好的选择

  • 比如像select limit 分页查询，用循环实现批量delete/update，这样就做到了少量多次

参考：

avatar
文章
阅读
粉丝