睡起来眼睛一片漆黑很晕,sql语句优化的实例笔试

查询SQL尽量不要使用select *，而是具体字段

反例

select * from test

正例

select id,name,age,remark from test

理由

节省资源、减少网络开销。

可能用到覆盖索引，减少回表，提高查询效率。

避免在where子句中使用 or 来连接条件

反例

select  * from test where  id=1 or age>20

正例使用 union all

select  * from test where  id=1 
union all
select  * from test where age>20

理由

使用or可能会使索引失效，从而全表扫描；

虽然mysql是有优化器的，出于效率与成本考虑，遇到or条件，索引还是可能失效的；

尽量使用数值替代字符串类型

正例

主键（id）： primary key 优先使用数值类型 int，tinyint
性别（sex）：0代表女，1代表男；数据库没有布尔类型， mysql 推荐使用 tinyint

理由

因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；

而对于数字型而言只需要比较一次就够了；

字符会降低查询和连接的性能，并会增加存储开销；

使用varchar代替char

varchar 变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间；
char 按声明大小存储，不足补空格；
其次对于查询来说，在一个相对较小的字段内搜索，效率更高；

理由

1、char的长度是固定的，而varchar2的长度是可以变化的。

例如：存储字符串“101”，对于char(10)，表示你存储的字符将占10个字节（包括7个空字符），在数据库中它是以空格占位的，而同样的varchar2(10)则只占用3个字节的长度，10只是最大值，当你存储的字符小于10时，按实际长度存储。

2、char的效率比varchar2的效率稍高。

varchar2 比 char 节省空间，在效率上比 char 会稍微差一点，既想获取效率，就必须牺牲一点空间，这就是我们在数据库设计上常说的“以空间换效率”。

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 左边表尽量小

inner join 内连接，只保留两张表中完全匹配的结果集；
left join 会返回左表所有的行，即使在右表中没有匹配的记录；
right join 会返回右表所有的行，即使在左表中没有匹配的记录；

理由

如果 inner join 是等值连接，返回的行数比较少，所以性能相对会好一点；
使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少；
这是 mysql 优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优；

清空表时优先使用truncate

truncate table 在功能上与不带 where子句的 delete 语句相同：二者均删除表中的全部行。但 truncate table 比 delete 速度快，且使用的系统和事务日志资源少。

delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。 truncate table 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

truncate table 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE 。如果要删除表定义及其数据，请使用 drop table 语句。

避免在索引列上使用内置函数

使用索引列上内置函数，索引失效

like通配符可能会导致索引失效

MySQL优化器的最终选择，不走索引

即使完全符合索引生效的场景，考虑到实际数据量等原因，最终是否使用索引还要看MySQL优化器的判断。当然你也可以在sql语句中写明强制走某个索引。

使用explain分析你SQL执行计划

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级别。

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 后面的字段，留意其数据类型的隐式转换。（字符串和整数 "100" 100）

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 过滤字段要少

14、 尽量避免大事务操作，提高系统并发能力

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

受气了月经有影响吗 (睡起来眼睛有红血丝分泌物多)