SQL 优化的几点经验
2018-09-20 本文已影响312人
卫渐行
结合阿里阿里巴巴Java开发手册，结合自己的经验，对于sql优化有下面几点体会
- 在应用系统开发初期，由于开发数据库数据比较少，对于查询SQL语句，复杂视图的的编写等体会不出SQL语句各种写法的性能优劣，但是如果将应用系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。
建表规约
- 适当的大小规约（mysql小写；oracle大写）
- mysql在windows不区分大小写；但在Linux默认大小写；
- 禁止用保留子字，表的命名最好是“业务名称——表的作用”
- 合适的字符存储长度（不但节约库表空间，节约索引空间，提升检索效率）
- 人的年龄：1-150（255，tinyint:1）；龟的岁数：数百岁（65535,smalInt:2）
-* 恐龙化石：0-数千年（ungigned int：4）；太阳：50亿年（unsigned bigint:）
- 在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。
索引规约
- 业务上具有唯一特性的字段，必须建立索引(可能为空，空值没有索引)；
- 在varchar字段上建立索引的时候，必须指定索引的长度；没必要进行全字段索引；（length：20；90%）
- 禁止使用左模糊或者全模糊查询，如果需要请走搜索引擎； 索引文件具有B-tree的最左前缀匹配特性；
- Select t.username from user where t.username like ‘c%'
- 利用延迟管理或者子查询优化超多分页场景
- 先快速定位需要获取的id段，然后在进行关联
- 建立组合索引的时候，区分度最高的在最左边
- Select t.username from user where t.id = ’t’ and t.gender = ‘female'
- 避免对索引字段进行计算操作
- 避免在索引字段上使用not，<>，!=
- 避免在索引列上使用IS NULL和IS NOT NULL
- 避免在索引字段上使用函数
- 避免建立索引的列中使用空值。
- 如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高.
- 在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!
SQL 其他基本语句
- 不要count(列)来替换count(*)；count(null)=0
- 使用isNuLL（）来判断是否为NULL值
- null和任何值比较都为NULL；null<>null为null.而不是fasle;
- null=null返回为null;而不是false； null<>1返回为NULL，而不是true
- 尽量使用 a is not null 改为 a>0 或a>’’
- 不得使用外键与级联，一切外健概念必须在应用层解决；
- 外健与级联更新适用于单机低并发，不适合分布式，高并发集群；级联更新是强阻塞，存在于数据库更新风暴的风险，外健影响数据库插入的速度；
- 禁止使用存储过程，存储过程难以调试和扩展，更没有移植性；
- 禁止用union
- 使用 > 及 < 操作符（大于或小于操作符）时，利用>=；
SQL 语句几句忠告：
- 1: 合理使用索引
- 2: 只查询你需要的列信息
- 3: 尽量避免数据左边的函数运算
- 4: 尽量避免相关子查询（在百万级别的时候，发现left join，inner join的效率会更高）
- 5: 避免在LIKE模式开始时的通配符
参考地址：
http://www.vertabelo.com/blog/technical-articles/5-tips-to-optimize-your-sql-queries
《码出高效-阿里巴巴Java开发手册》终极版
