超全的数据库建表/SQL/索引规范，适合贴在工位上( 二 ) 背景因为工作岗位的原因

【建议】（6）应尽量避免在WHERE子句中使用or作为连接条件
解读：根据情况可以选择使用UNION ALL来代替OR 。
【强制】（7）不允许使用%开头的模糊查询
解读：根据索引的最左前缀原理， %开头的模糊查询无法使用索引，可以使用ES来做检索。
三、索引规约
【建议】（1）避免在更新比较频繁、区分度不高的列上单独建立索引
解读：区分度不高的列单独创建索引的优化效果很小，但是较为频繁的更新则会让索引的维护成本更高。
【强制】（2） JOIN的表不允许超过五个。需要JOIN的字段，数据类型必须绝对一致; 多表关联查询时，保证被关联的字段需要有索引
解读：太多表的JOIN会让Mysql的优化器更难权衡出一个“最佳”的执行计划（可能性为表数量的阶乘），同时要注意关联字段的类型、长度、字符编码等等是否一致。
【强制】（3）在一个联合索引中，若第一列索引区分度等于1 ，那么则不需要建立联合索引
解读：索引通过第一列就能够完全定位的数据，所以联合索引的后边部分是不需要的。
【强制】（4）建立联合索引时，必须将区分度更高的字段放在左边
解读：区分度更高的列放在左边，能够在一开始就有效的过滤掉无用数据。提高索引的效率，相应我们在Mapper中编写SQL的WHERE条件中有多个条件时，需要先看看当前表是否有现成的联合索引直接使用，注意各个条件的顺序尽量和索引的顺序一致。
【建议】（5）利用覆盖索引来进行查询操作，避免回表
解读：覆盖查询即是查询只需要通过索引即可拿到所需DATA ，而不再需要再次回表查询，所以效率相对很高。我们在使用EXPLAIN的结果， extra列会出现："using index" 。这里也要强调一下不要使用“SELECT * ” ，否则几乎不可能使用到覆盖索引。
【建议】（6）在较长VARCHAR字段,例如VARCHAR(100)上建立索引时，应指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可
解读：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，若长度为20的索引，区分度会高达90%以上，则可以考虑创建长度例为20的索引，而非全字段索引。例如可以使用SELECT COUNT(DISTINCT LEFT(lesson_code, 20)) / COUNT(*) FROM lesson;来确定lesson_code字段字符长度为20时文本区分度。
【建议】（7）如果有ORDER BY的场景，请注意利用索引的有序性。 ORDER BY最后的字段是联合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。
解读：

假设有查询条件为WHERE a=? and b=? ORDER BY c；存在索引：a_b_c ，则此时可以利用索引排序；
反例：在查询条件中包含了范围查询，那么索引有序性无法利用，如:WHERE a>10 ORDER BY b; 索引a_b无法排序。

【建议】（8）在where中索引的列不能某个表达式的一部分，也不能是函数的参数
解读：即是某列上已经添加了索引，但是若此列成为表达式的一部分、或者是函数的参数， Mysql无法将此列单独解析出来，索引也不会生效。
【建议】（9）我们在where条件中使用范围查询时，索引最多用于一个范围条件，超过一个则后边的不走索引
解读：Mysql能够使用多个范围条件里边的最左边的第一个范围查询，但是后边的范围查询则无法使用。
【建议】（10）在多个表进行外连接时，表之间的关联字段类型必须完全一致
解读：当两个表进行Join时，字段类型若没有完全一致，则加索引也不会生效，这里的完全一致包括但不限于字段类型、字段长度、字符集、collection等等。
>>>>
参考资料

《High.Performance.MySQL.3rd.Edition》
《阿里巴巴java开发手册》

【超全的数据库建表/SQL/索引规范，适合贴在工位上】作者丨浮雷
来源丨
dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn
2020 DAMS中国数据智能管理峰会即将于10月30日在上海举办，部分精彩议题先睹为快：

腾讯《腾讯游戏大数据资产管理实战：元数据管理与数据治理》
京东《京东EB级全域大数据平台建设和治理之路》
阿里《大规模容器云基础设施环境架构、管理与运维》
工商银行《DevOps转型的探索与实践》
中国银联《从自研演进看分布式数据库》
民生银行《开源数据库MySQL在民生银行的应用实践》
- 上一页
- 1
- 2
- 3
- 下一页