相信大家不管是在面试中，或者是在实际的开发过程中，都有接触过SQL优化相关的事情。之前有看到过类似知识的小伙伴可能能说出点东西来，对于没有涉及过相关知识的伙伴也不用着急，看了这篇，相信也足够你去应对面试或者在实际开发工程中使用了。关注收藏，随时拿出来看看，相信对你会有很大的帮助。当然以下SQL优化策略适用于数据量较大的场景下，如果数据量较小，没必要以此为准，以免画蛇添足。
这里不仅仅针对SQL优化进行归纳和总结，我们从最初的建表，包括建表需要注意的事项以及规约，到最后的SQL语句，包括SQL语句书写需要注意的事项以及对于某些SQL语句的优化这一整个过程做一个系统的总结，当然整个过程都涉及到索引的建立与使用，这个是SQL优化的关键，所以这里我会一共分为三个部分进行总结：建表规范及优化、SQL规范及优化、索引规范及优化。当然有些是建议不作为SQL指定规范，大家适当甄别。
建表规范及优化
- （1） 库名、表名、字段名均小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用。
- （2）数据库表、表字段必须加入中文注释。
- （3）尽量统一使用UTF8字符集，在Mysql中的UTF8并非“真正的UTF-8”，而utf8mb4”才是真正的“UTF-8”，如果需要存储表情，那么选择utf8mb4来进行存储，注意它与utf-8编码的区别。
- （4）存储引擎必须使用InnoDB，因为InnoDB支持事物、行级锁、并发性能更好，CPU及内存缓存页优化使得资源利用率更高。
- （5）每张表必须设置一个主键ID，create_time，update_time。且这个主键ID使用自增主键（在满足需要的情况下尽量短），除非在分库分表环境下。
- InnoDB组织数据的方式决定了需要有一个主键，而且若是这个主键ID是单调递增的可以有效提高插入的性能，避免过多的页分裂、减少表碎片提高空间的使用率。
- 在分库分表环境下，需要统一来分配各个表中的主键值，从而避免整个逻辑表中主键重复。
- （6）单表列数目必须小于30，若超过则应该考虑将表拆分。单表列数太多使得Mysql服务器处理InnoDB返回数据之间的映射成本太高。
- （7）禁止使用外键，如果有外键完整性约束，需要应用程序控制，因为外键会导致表与表之间耦合，UPDATE与DELETE操作都会涉及相关联的表，十分影响SQL的性能，甚至会造成死锁。
- （8）必须把字段定义为NOT NULL并且提供默认值
- NULL的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化 ；
- NULL这种类型Msql内部需要进行特殊处理，增加数据库处理记录的复杂性；
- 同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多；
- NULL值需要更多的存储空，无论是表还是索引中每行中的NULL的列都需要额外的空间来标识。不要以为 NULL 不需要空间，比如char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。
- （9）禁用保留字，如DESC、RANGE、MARCH等，请参考Mysql官方保留字
- （10）在一些场景下，考虑使用TIMESTAMP代替DATETIME
- 这两种类型的都能表达"yyyy-MM-dd HH:mm:ss"格式的时间
- TIMESTAMP只需要占用4个字节的长度，可以存储的范围为(1970-2038)年，在各个时区，所展示的时间是不一样的；
- DATETIME类型占用8个字节，对时区不敏感，可以存储的范围为(1001-9999)年。
- （11）尽可能的使用 varchar/nvarchar 代替 char/nchar
- 因为首先变长字段存储空间小，可以节省存储空间；
- 对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
- （12）索引命名要规范
- 主键索引名为 pk_ 字段名，pk_即primary key；
- 唯一索引名为 uk _字段名 ，uk_即unique key；
- 普通索引名则为 idx _字段名，idx_即index 。
SQL规范及优化
SQL防范优化
- （1）写完SQL先explain查看执行计划，分析一下，尤其注意走不走索引，然后针对性的进行调整优化；
explain SELECT
id,name
FROM subactivity where id=22184;
SEQ id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 1 SIMPLE subactivity NULL const PRIMARY PRIMARY 8 const 1 100 NULL
- （2）操作delete或者update语句，加个limit，这样可以降低写错SQL的代价
- 加了limit 1，如果第一条就命中目标return，提高SQL效率， 没有limit的话，就会继续执行扫描表；
- delete执行时，如果相关字段加了索引，MySQL会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用；
- 删除数据量很大时，不加 limit限制一下记录数，容易把cpu打满，导致越删越慢的。
- （3）变更SQL操作先在测试环境执行，写明详细的操作步骤以及回滚方案，并在上生产前review。
- （4）修改或删除重要数据前，要先备份。
- （5）修改或者删除SQL，先写WHERE查一下，确认后再补充 delete 或 update。
SQL查询优化
-
（1）避免使用select *
- 使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O，内存和CPU消耗。
-
（2）多表关联查询时，小表在前，大表在后，且使用表的别名
- 执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。
-
（3）调整Where字句中的连接顺序
- MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多 的条件往前放，最快速度缩小结果集。
-
（4）用where字句替换HAVING字句。
- HAVING只会在检索出所有记录之后才对结果集进行过滤
- where是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销；
- HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。
-
（5）对于复杂的查询，可以使用中间临时表 暂存数据
-
（6）建议使用union all
- MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all。
- 如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。
-
（7）拆分复杂SQL为多个小SQL，避免大事务
- 简单的SQL容易使用到MySQL的QUERY CACHE；
- 减少锁表时间特别是使用MyISAM存储引擎的表；
- 可以使用多核CPU。
-
（8）查询数据量大的表 会造成查询缓慢，主要的原因是扫描行数过多。这个时候可以通过程序，分段分页进行查询，循环遍历，将结果合并处理进行展示。
SQL增删改优化
- （1）如果同时执行大量的插入，建议使用多个值的INSERT语句，这比使用分开INSERT语句快，一般情况下批量插入效率有几倍的差别。
- （2）命令行修改数据，使用begin + commit 事务。
- （3）当删除全表中记录时，使用truncate代替delete。
- 使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源；
- 使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外，使用truncate可以回收表的水位，使自增字段值归零。
索引规范及优化
索引建立规范
- （1）表的主键、外键必须有索引；
- （2）经常与其他表进行连接的表，在连接字段上应该建立索引；
- （3）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
- （4）尽量使用数字型字段（如性别，男：1 女：2）
- 若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销；
- 引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
索引失效场景
- （1）尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
- （2）尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
- （3）尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可以改用union或union all
- （4）尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。
- （5）尽量避免使用in 和not in，会导致引擎走全表扫描。如果是连续数值，可以用between代替，如果是子查询，可以用exists代替。
- （6）尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。可以将表达式、函数操作移动到等号右侧。
- （7）当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。
- （8）where条件仅包含复合索引非前置列会导致不会走联合索引。
- （9）尽量避免隐式类型转换，会造成不使用索引。
最后要说的是，索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
扫一扫
249
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
