SQL优化必要性及其实际操作

1 , SQL优化的必要性

1 ) , 硬件角度

现今 , 数据库服务器多为固态驱动 （Solid State Disk或Solid State Drive，简称SSD）, 俗称固态硬盘 。
SSD 具备随机读能力非常好 和 不存在磁盘寻道的延迟时间 的优势 ; 但是 , 也有着写入放大 和 因为存储晶体颗粒本质存在写磨损 , 以及读写寿命次数有限的缺点 。
所以 , 我们SQL优化是为了减少不必要的响应 IO 以达到保护数据库服务器的目的 。
此处有很好的数据库与SSD博文参考链接 , 所以不详述这部分 : https://blog.csdn.net/iteye_6233/article/details/82374978?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task

2 ) , 软件角度

首先 , 数据库数据保存在磁盘上 , 读写速度比不上内存 ; 其次 , 随着项目应用的使用 , 数据量会不断增大 , 处理数据的时间会变慢 。 所以 , 在数据响应方面 , 如果是大数据量的查询 , 会造成返回时间过长 , 无论在此是否做了超时异常处理 , 必会给出不好的用户体验 。

2 , 如何优化SQL

1 ) , 减少数据访问 : 创建并正确的使用索引 ; 优化SQL执行计划 .
2 ) , 返回更少的数据 : 分页 ; 只返回必要的字段(不使用 select * ；拆分大字段) .
3 ) , 减少交互次数 : 使用JDBC的batch方法处理 ; 用in合并多条sql , in中数据很大时，用exists替代in , 对于连续的数值，能用 between 不要用 in ; 适当的使用存储过程 .
4 ) , 减少CPU运算 : 使用绑定变量(JDBC中的PreparedStatement) ; 谨慎的进行大记录集排序 ; 减少比较操作 ; 用搜索代替like ; 不能使用索引定位的大量In List ; 5 ) , 尽量不要在数据库做运算：cpu计算请移至业务层 .
6 ) , 利用更多资源 : 客户端多进程访问 ; 数据库并行处理 .

3 , 实际操作

(1),表设计
控制单表数据量：单表记录控制在1000w行.（可考虑分区）
控制列数量：单表字段数上限控制在20到50之内.字段少而精可以提高并发，IO更高效 (优化InnoDB表BLOB列的存储效率) .
平衡范式与冗余：效率优先，提高性能. 适时牺牲范式增加冗余 .
MySQL ：使用业务无关的自增主键 .

(2),尽量使用索引
①,谨慎合理的添加索引
索引能改善查询的效率，但是也会增加额外的开销并减慢更新的速度(更新时会同时更新索引). 索引的数量不是越多越好，能不加的索引尽量不加. InnoDB的secondary index(非主键索引）使用b+tree来存储 ( b+tree索引最多3次IO )，因此在UPDATE、DELETE、INSERT的时候需要对b+tree进行调整，过多的索引会减慢更新的速度.

单表的索引应符合下列要求：
索引数量控制在5个左右，单个索引的字段不超过5个.在设计的时候要结合SQL和需求考虑索引的覆盖.
唯一键由3个以下字段组成，当字段都是整形时，使用唯一键作为主键.
唯一键不和主键重复，即不得在主键上建唯一索引.
较长的字段需加入前缀索引来减少索引长度，提高效率 , 即使用联合索引 .

②,提高索引的覆盖率
合理利用覆盖索引.
关于覆盖索引：InnoDB 存储引擎中，secondary index（非主键索引）中没有直接存储行地址，而存储主键值. 如果用户需要查询secondary index中所不包含的数据列时，需要先通过secondary index查找到主键值，然后再通过主键查询到其他数据列，因此需要查询两次. 覆盖索引的概念就是查询可以通过在一个索引中完成，覆盖索引效率会比较高，主键查询是覆盖索引.

合理的创建索引以及合理的使用查询语句，当使用到覆盖索引时可以获得性能提升.
比如SELECT email,uid FROM user_email WHERE uid=xxx，可以将索引添加为index(uid,email)，以提升性能.

索引字段的顺序需要考虑字段值去重之后的个数，个数多的放在前面.
合理创建联合索引（避免冗余），(a,b,c相当于 (a、(a,b、(a,b,c). 遵循最左原则.
UPDATE、DELETE语句需要根据WHERE条件添加索引.

③,索引使用需要注意的事项
参考链接 : https://blog.csdn.net/jie_liang/article/details/77340905
不建议使用%前缀模糊查询，例如LIKE “%xxx”，这样会扫全表.
不要在索引列进行数学或者函数计算.

select * from table where id +1 =10000;

这样不会使用索引，导致扫全表，改为:

select * from table where id =10000-1;

使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：使用File Sort，使用Temporary.
下面列出extra列常见的值:
❶. 使用Temporary
为了解决查询，MySQL需要创建一个临时表来容纳结果. 典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句. 使用临时表的开销是比较大的.
❷. 使用File Sort
MySQL需要额外的一次传递，以找出如何按排序顺序检索行. 出现这个说明SQL没有走索引. MySQL通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序.
❸. 使用index
从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息.
❹. 使用where
MySQL使用where条件进行过滤找到匹配行返回客户端.

禁止使用外键. 因为会产生额外开销，并且是逐行进行操作. 最关键的是在高并发的情况下很容易造成死锁.
SQL变更需要确认索引是否需要变更，并通知DBA.

(3),尽量使用查询缓存
必须SQL语句完全相同才能够命中缓存。如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果都不会被缓存。比如 函数NOW() 或者 CURRENT_DATE() 会因为不同的查询时间，返回不同的查询结果，再比如包含 CURRENT_USER 或者 CONNECION_ID() 的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

// 不会开启查询缓存
SELECT username FROM user WHERE signup_date >= CURDATE()
 
// 使用变量绑定方式，会开启查询缓存
SELECT username FROM user WHERE signup_date >= ?