SQL语句优化汇总 _mysql创建索引

查询SQL尽量不要使用 *，而是具体字段
反例
select * from test
正例
select id,name,age,remark from test
理由
节省资源、减少网络开销。
可能用到覆盖索引，减少回表，提高查询效率。
避免在where子句中使用 or 来连接条件
反例
select* from test whereid=1 or age>20
正例
使用union all
select* from test whereid=1 union allselect* from test where age>20
理由
使用or可能会使索引失效，从而全表扫描；
虽然mysql是有优化器的，出于效率与成本考虑，遇到or条件，索引还是可能失效的；
尽量使用数值替代字符串类型
正例
理由
因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；
而对于数字型而言只需要比较一次就够了；
字符会降低查询和连接的性能，并会增加存储开销；
使用代替char
理由
1、char的长度是固定的，而的长度是可以变化的。
例如：存储字符串“101”，对于char(10)，表示你存储的字符将占10个字节（包括7个空字符），在数据库中它是以空格占位的，而同样的(10)则只占用3个字节的长度，10只是最大值，当你存储的字符小于10时，按实际长度存储。
2、char的效率比的效率稍高。
比char节省空间，在效率上比char会稍微差一点，既想获取效率收获不止sql优化pdf ，就必须牺牲一点空间，这就是我们在数据库设计上常说的“以空间换效率” 。
where中使用默认值代替null
反例
select * from test where age is not null
正例
select * from test where age > 0
理由
where语句中索引列使用了负向查询，可能会导致索引失效
负向查询包括：NOT、!=、、!、NOT IN、NOT LIKE等
负向查询应尽量避免出现。否则引擎将放弃使用索引而进行全表扫描
避免在where子句中使用!=或操作符

文章插图

文章插图
inner join 、left join、right join，优先使用inner join
三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小
理由
清空表时优先使用
table在功能上与不带where子句的语句相同：二者均删除表中的全部行。但table比速度快，且使用的系统和事务日志资源少。
语句每次删除一行，并在事务日志中为所删除的每行记录一项。table通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
table删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用。如果要删除表定义及其数据，请使用 drop table语句。
避免在索引列上使用内置函数
使用索引列上内置函数，索引失效
like通配符可能会导致索引失效MySQL优化器的最终选择，不走索引
即使完全符合索引生效的场景，考虑到实际数据量等原因，最终是否使用索引还要看MySQL优化器的判断。当然你也可以在sql语句中写明强制走某个索引。
使用分析你SQL执行计划
type
Extra常用关键字
总结其它优化方式
1、设计表的时候，所有表和字段都添加相应的注释。
2、SQL书写格式，关键字大小保持一致，使用缩进。
3、修改或删除重要数据前，要先备份。
4、很多时候用代替 in 是一个好的选择
5、where后面的字段，留意其数据类型的隐式转换。（字符串和整数 “100” 100）
6、尽量把所有列定义为NOT NULL
NOT NULL列更节省空间， NULL列需要一个额外字节作为判断是否为 NULL的标志位。NULL列需要注意空指针问题，NULL列在计算和比较的时候，需要注意空指针问题。
7、伪删除设计
8、数据库和表的字符集尽量统一使用UTF8
（1）可以避免乱码问题；
（2）可以避免，不同字符集比较转换，导致的索引失效问题；
9、 count(*) from table；
这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。
10、避免在where中对字段进行表达式操作
（1）SQL解析时，如果字段相关的是表达式就进行全表扫描；
（2）字段干净无表达式收获不止sql优化pdf，索引生效；
11、关于临时表
（1）避免频繁创建和删除临时表，以减少系统表资源的消耗；
（2）在新建临时表时，如果一次性插入数据量很大，那么可以使用into 代替table，避免造成大量 log；
（3）如果数据量不大，为了缓和系统表的资源，应先 table，然后；
（4）如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除。先table，然后 drop table ，这样可以避免系统表的较长时间锁定；
12、索引不适合建在有大量重复数据的字段上，比如性别，排序字段应创建索引
13、去重过滤字段要少
14、尽量避免大事务操作，提高系统并发能力
15、所有表必须使用存储引擎
【SQL语句优化汇总】本文到此结束，希望对大家有所帮助。