转:SQL设计学习

• 存储引擎的选择
• 表设计
• 索引设计
  • 索引设计知识点
  • 设计规则
• SQL设计 
• SQL调优
• SQLReview
  • 不符合规范的SQL
  • 慢SQL
    • explain分析慢SQL
  • 复杂SQL
• 案例分析

 

存储引擎的选择

表设计

1.  有注释，简练的描述表的用途
2. 字段选择合适的数据类型：char，int，tinyint
3. 所有字段的设计中默认都加上NOT NULL，NULL值不会被count函数统计进去，NULL字段很难查询优化，NULL字段的索引需要额外空间，NULL字段的复合索引无效
4. 避免使用TEXT，BLOB类型
5. 拆分大字段和访问频率低得字段
6. 考虑冷热数据分离，比如分区表，月表，天表等
7. 使用timestamp存储时间（占用空间更小，4字节，Datetime是8个字节）
8. 禁止在数据库中存储明文密码

 

 

索引设计

索引设计知识点

1. 超过20个长度的字符串列，最好创建前缀索引而非整列索引（例如：ALTER TABLE t1 ADD INDEX(user(20))），可以有效提高索引利用率，不过它的缺点是对这个列排序时用不到前缀索引。前缀索引的长度可以基于对该字段的统计得出，一般略大于平均长度一点就可以了。
2.  哪怕是基于索引的条件过滤，如果优化器意识到总共需要扫描的数据量超过30%时（ORACLE里貌似是20%，MySQL目前是30%，没准以后会调整），就会直接改变执行计划为全表扫描，不再使用索引。
3. 多表JOIN时，要把过滤性最大（不一定是数据量最小哦，而是指加了WHERE条件后过滤性最大的那个）的表选为驱动表。此外，如果JOIN之后有排序，排序字段一定要属于驱动表，才能利用驱动表上的索引完成排序。

设计规则

1、索引的数量要控制：

(1) 单张表中索引数量不超过5个

(2) 单个索引中的字段数不超过5个

(3) 对字符串使⽤用前缀索引

(4) 建议优先考虑前缀索引，必要时可添加伪列并建立索引

2、主键准则

(1) 表必须有主键

(2) 不使用更新频繁的列作为主键

(3) 尽量不选择字符串列作为主键

(4) 不允许UUID MD5 HASH这些作为主键

(5) 默认使⽤非空的唯一键作为主键

(6) 建议选择自增

3、重要的SQL必须被索引，比如：

(1) UPDATE、DELETE语句的WHERE条件列

(2) ORDER BY、GROUP BY、DISTINCT的字段

4、多表JOIN的字段注意以下：

(1) 区分度最大的字段放在前面

(2) 核⼼SQL优先考虑覆盖索引

(3) 避免冗余和重复索引

(4) 索引要综合评估数据密度和分布以及考虑查询和更新比例

5、索引禁忌

(1) 不在低基数列上建立索引，例如“性别”

(2) 不在索引列进行数学运算和函数运算

6、尽量不使用外键

(1) 外键用来保护参照完整性，可在业务端实现

(2) 对父表和子表的操作会相互影响，降低可用性

7、索引命名：非唯一索引必须以 idx_字段1_字段2命名，唯一所以必须以uniq_字段1_字段2命名，索引名称必须全部小写

8、新建的唯一索引必须不能和主键重复

9、索引字段的默认值不能为NULL，要改为其他的default或者空。NULL非常影响索引的查询效率

10、反复查看与表相关的SQL，符合最左前缀的特点建立索引。多条字段重复的语句，要修改语句条件字段的顺序，为其建立一条联合索引，减少索引数量

11、能使用唯一索引就要使用唯一索引，提高查询效率

12、使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary

 

 

SQL设计 

 

(1) sql语句尽可能简单

(2) 避免使用触发器、函数、存储过程

(3) 避免在数据库中进⾏数学运算

(4) 不要用select *，指定查询的具体字段。当表中存在 TEXT/BLOB 大列的时候就会是灾难了。可能本来不需要读取这些列，但因为偷懒写成 SELECT * 导致内存buffer pool  被这些“垃圾”数据把真正需要缓冲起来的热点数据给洗出去了。

(5) limit分页注意效率。Limit越大，效率越低。可以改写limit，比如例子改写：

select id from tlimit 10000, 10;  =>  select id from t where id > 10000 limit10;

SELECT * FROM table ORDER BY TIME DESC LIMIT 10000,10;=》 SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC LIMIT 10.

                                                                                                         =》 SELECT * FROM table inner JOIN(SELECT id FROM table ORDER BY TIME LIMIT 10000,10) as t USING(id)

(6) 使用union all替代union

(7) 避免使⽤大表的JOIN

(8) 对库写保护：对数据的更新要打散后批量更新，不要一次更新太多数据

(9) 避免慢查询，用explain分析

(10) SQL语句不可以出现隐式转换，比如 select id from 表 where id='1'

(11) IN条件里面的数据数量要少

(12) 能不用NOT IN就不用NOT IN，会把空和NULL给查出来

(13) 在SQL语句中，禁止使用前缀是%的like

(14) 关于分页查询：程序里建议合理使用分页来提高效率limit，offset较大要配合子查询使用

(15) 使⽤预编译语句，只传参数，比传递SQL语句更高效；一次解析，多次使用；降低SQL注入概率

(16) 禁止使⽤order by rand()

(17) 禁⽌单条SQL语句同时更新多个表

 

SQL调优

 

SQLReview

SQL Review是要发现有潜在风险的SQL（慢查询），复杂SQL。

不符合规范的SQL

insert into table values()

insert delete update 没有条件，或条件中没有主键或索引

慢SQL

敏感词：in，distinct，order by，group by，union，like，join，rand()

慢SQL特征：

查询条件的前导列上使用函数

没有where条件

没有limit限制

explain分析慢SQL

     filesort

     查询的数据量

复杂SQL

体量巨大的SQL

嵌套多的SQL
 

案例分析

 

 

参考资料

15 Ways to Kill Your Mysql Application Performance

Q&A: Common (but deadly) MySQL Development Mistakes

Efficient Pagination Using MySQL

MySQL索引原理及慢查询优化

mysql sql规范