MySql设计规范及SQL索引优化【呕心之作】
数据库及表结构基本设计规范
1. 所有表必须使用Innodb存储引擎
没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎（mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb）。Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。
2. 优先选择符合存储需要的最小的数据类型
列的字段越大，建立索引时所需要的空间也就越大，索引的性能也越差。
3. 尽可能把所有列定义为NOT NULL
索引NULL列需要额外的空间来保存，所以要占用更多的空间
进行比较和计算时要对NULL值做特别的处理
4. 使用TIMESTAMP（4个字节）或DATETIME类型（8个字节）存储时间
TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07，TIMESTAMP 占用4字节和INT相同，但比INT可读性高；
超出TIMESTAMP取值范围的使用DATETIME类型存储
经常会有人用字符串存储日期型的数据（不正确的做法）
-
缺点1：无法用日期函数进行计算和比较
-
缺点2：用字符串存储日期要占用更多的空间
5. 同财务相关的金额类数据必须使用decimal类型
-
非精准浮点：float,double
-
精准浮点：decimal
Decimal类型为精准浮点数，在计算时不会丢失精度
占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节
可用于存储比bigint更大的整型数据
6. 数据库和表的字符集统一使用UTF8
兼容性更好，统一字符集可以避免由于字符集转换产生的乱码；如果有存储emoji表情的需要，采用utf8mb4字符集。
7. 尽量控制单表数据量的大小，建议控制在500万以内。
500万并不是Mysql数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题。
可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小
8. 禁止在表中建立预留字段
预留字段的命名很难做到见名识义。预留字段无法确认存储的数据类型，所以无法选择合适的类型。
对预留字段类型的修改，会对表进行锁定。
9. 禁止在数据库中存储图片，文件等大的二进制数据；通常文件存储于文件服务器，数据库只存储文件地址信息
10. 尽量避免使用外键约束（这个各有见解，不强制）
-
不建议使用外键约束（foreign key），但一定要在表与表之间的关联键上建立索引
-
外键会影响父表和子表的写操作从而降低性能
SQL优化细节规范：
做MySQL优化，我们要善用EXPLAIN查看SQL执行计划。
-
type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
-
key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。
-
key_len列，索引长度。
-
rows列，扫描行数。该值是个预估值。
-
extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。
1、当只需要一条数据的时候，使用limit 1，为了使EXPLAIN中type列达到const类型
2.、禁止使用SELECT * 必须使用SELECT <字段列表> 查询；避免消耗更多的CPU和IO以网络带宽资源
3、 避免使用JOIN关联太多的表
4、 减少同数据库的交互次数
5、使用合理的分页方式以提高分页的效率
如 select id,name from product limit 800000, 20； 替换为 select id,name from product where id> 800000 limit 20；
6、不建议使用%前缀模糊查询
7、对应同一列进行or判断时，使用in代替or
in 的值不要超过500个，in 操作可以更有效的利用索引，or大多数情况下很少能利用到索引。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。
8、尽量用union all代替union
9、 WHERE从句中禁止对列进行函数转换和计算
select user_id,user_project from user_base where age*2=36; 替换为 select user_id,user_project from user_base where age=36/2;
10、避免产生大事务操作
11、在明显不会有重复值时使用UNION ALL 而不是UNION
-
UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作
-
UNION ALL 不会再对结果集进行去重操作
索引设计规范
1. 限制每张表上的索引数量，建议单张表索引不超过5个
索引并不是越多越好！索引可以提高效率同样可以降低效率。
索引过多会增加mysql优化器生成执行计划的时间，同样会降低查询性能。
2. 对于联合索引来说，要遵守最左前缀法则
字段长度小、区分度最高及使用最频繁的列放在联合索引的最左侧
3. 避免建立冗余索引和重复索引（增加了查询优化器生成执行计划的时间）
-
重复索引示例：primary key(id)、index(id)、unique index(id)
-
冗余索引示例：index(a,b,c)、index(a,b)、index(a)
4. 每个Innodb表必须有个主键
Innodb是按照主键索引的顺序来组织表的
-
不要使用UUID,MD5,HASH,字符串列作为主键（无法保证数据的顺序增长）
-
主键建议使用自增ID值
5.常见索引列建议
-
出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列
-
包含在ORDER BY、GROUP BY、DISTINCT中的字段
-
并不要将符合1和2中的字段的列都建立一个索引， 通常将1、2中的字段建立联合索引效果更好
-
多表join的关联列
6、注意范围查询语句
对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。
7、尽量使用inner join，避免left join：利用小表去驱动大表
参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。
8、区分in和exists、not in和not exists
关于not in和not exists，推荐使用not exists。 更多比较移步：浅析MySQL中exists与in的使用 （写的非常好）
参考文章：
