mysql索引优化以及建表规约

最近阅读了一些阿里发出的索引规约手册，记录下

一、建表规约

1，表达是与否概念，使用is_xxx方式命名，数据类型为unsignde tinyint

2, 表名，字段名使用小写字母

3，不要用保留字

4，唯一索引使用uk_字段名；普通索引使用idx_字段名

5，小数类型使用decimal，不要用float和double

6，存储字符串长度相同，使用char

7，varchar是变长字符串，不预先分配存储空间，如果长度过大使用text，独立出一张表使用主键对应，避免影响其他字段索引

8，表名最好加上业务名称_表作用

9，库名与应用名一致

10，字段可以适当冗余，但要考虑数据同步，冗余字段不是频繁修改字段，不是长字段

11，单表超过500晚或者2GB，才考虑吧分库分表

12，合适的字符串长度，可以节约数据库表空间，节约索引存储，能提升检索速度

二、索引规约

1，业务上具有唯一特性的字段，即使是组合字段，也要建成唯一索引

说明：唯一索引影响inster速度可以忽略，但查找速度提升是很明显的，即使在应用层做了判断，没有唯一索引，跟据墨菲定律，也必然会有脏数据产生

2，超过三个表的join，禁止使用join

3，varchar字段建立索引时，必须指定索引长度

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分
度会高达 90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度
来确定。

4，不要使用左模糊或者全模糊查询
说明：索引文件具有B-TREE的最左匹配特性，左边的值未确定，就无法使用索引

5，使用order by 注意使用索引的有序性。最后的字段是组合索引的一部分，并且放在索引组合顺序的最后

例：where a=? and b= ? order by c ;索引 ： a_b_c

6，利用覆盖索引来进行查询操作，避免回表操作

7，利用延迟关联或者子查询优化超多分页场景

说明：快速定位需要获取的id段，然后关联
SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

8，sql优化目标至少range级别，要求ref级别，最好 consts

说明 ：consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。ref 指的是使用普通的索引（normal index）。range 对索引进行范围检索。

9，建立组合索引时，区分度最高的在最左边