SQL性能优化的小技巧四

落日映苍穹つ 2024-04-03 11:26 66阅读 0赞

十七、复合索引最左特性  
1、创建复合索引

ALTER TABLE employee ADD INDEX idx\_name\_salary (name,salary)  
1  
2、满足复合索引的最左特性，哪怕只是部分，复合索引生效

SELECT \* FROM employee WHERE NAME='哪吒编程'  
1  
3、没有出现左边的字段，则不满足最左特性，索引失效

SELECT \* FROM employee WHERE salary=5000  
1  
4、复合索引全使用，按左侧顺序出现 name,salary，索引生效

SELECT \* FROM employee WHERE NAME='哪吒编程' AND salary=5000  
1  
5、虽然违背了最左特性，但MySQL执行SQL时会进行优化，底层进行颠倒优化

SELECT \* FROM employee WHERE salary=5000 AND NAME='哪吒编程'  
1  
6、理由

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

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

十八、优化like语句  
模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效。

1、反例

select \* from citys where name like '%大连' (不使用索引)  
1  
select \* from citys where name like '%大连%' (不使用索引)  
1  
2、正例

select \* from citys where name like '大连%' (使用索引) 。  
1  
3、理由

首先尽量避免模糊查询，如果必须使用，不采用全模糊查询，也应尽量采用右模糊查询， 即like ‘…%’，是会使用索引的；  
左模糊like ‘%...’无法直接使用索引，但可以利用reverse + function index的形式，变化成 like ‘…%’；  
全模糊查询是无法优化的，一定要使用的话建议使用搜索引擎。  
十九、使用explain分析你SQL执行计划  
1、type

system：表仅有一行，基本用不到；  
const：表最多一行数据配合，主键查询时触发较多；  
eq\_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型；  
ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取；  
range：只检索给定范围的行，使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range；  
index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小；  
all：全表扫描；  
性能排名：system > const > eq\_ref > ref > range > index > all。  
实际sql优化中，最后达到ref或range级别。  
2、Extra常用关键字

Using index：只从索引树中获取信息，而不需要回表查询；  
Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。需要回表查询。  
Using temporary：mysql常建一个临时表来容纳结果，典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时；  
二十、一些其它优化方式  
1、设计表的时候，所有表和字段都添加相应的注释。

2、SQL书写格式，关键字大小保持一致，使用缩进。

3、修改或删除重要数据前，要先备份。

4、很多时候用 exists 代替 in 是一个好的选择

5、where后面的字段，留意其数据类型的隐式转换。

未使用索引

SELECT \* FROM user WHERE NAME=110  
1  
（1） 因为不加单引号时，是字符串跟数字的比较，它们类型不匹配  
（2）MySQL会做隐式的类型转换，把它们转换为数值类型再做比较

6、尽量把所有列定义为NOT NULL

NOT NULL列更节省空间，NULL列需要一个额外字节作为判断是否为 NULL的标志位。  
NULL列需要注意空指针问题，NULL列在计算和比较的时候，需要注意空指针问题。

7、伪删除设计

8、数据库和表的字符集尽量统一使用UTF8

（1）可以避免乱码问题。  
（2）可以避免，不同字符集比较转换，导致的索引失效问题。

9、select count(\*) from table；

这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。

10、避免在where中对字段进行表达式操作

（1）SQL解析时，如果字段相关的是表达式就进行全表扫描  
（2）字段干净无表达式，索引生效

11、关于临时表

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

12、索引不适合建在有大量重复数据的字段上，比如性别，排序字段应创建索引

13、去重distinct过滤字段要少

带distinct的语句占用cpu时间高于不带distinct的语句  
当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据  
然而这个比较、过滤的过程会占用系统资源，如cpu时间  
14、尽量避免大事务操作，提高系统并发能力

15、所有表必须使用Innodb存储引擎

Innodb「支持事务，支持行级锁，更好的恢复性」，高并发下性能更好，所以呢，没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎。

16、尽量避免使用游标

因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。  
[尚学堂给同学们带来全新的Java300集课程啦!java零基础小白自学Java必备优质教程\_手把手图解学习Java，让学习成为一种享受\_哔哩哔哩\_bilibili][Java300_java_Java_Java_bilibili]

[Java300_java_Java_Java_bilibili]: https://www.bilibili.com/video/BV1qL411u7eE/?spm_id_from=333.337.search-card.all.click