建设国际互联网网站,惠州做棋牌网站建设有哪些公司,成都培训网站建设,怎么在服务器建立网站索引规范
今天在建线上表的时候#xff0c;做了一个varchar的索引#xff0c;运维说varchar的索引会占用很大的内存。 于是 上网搜了一下建索引规范
一、建表规约
【强制】#xff08;1#xff09; 存储引擎必须使用InnoDB 解读#xff1a;InnoDB支持事物、行级锁、并发…索引规范
今天在建线上表的时候做了一个varchar的索引运维说varchar的索引会占用很大的内存。 于是 上网搜了一下建索引规范
一、建表规约
【强制】1 存储引擎必须使用InnoDB 解读InnoDB支持事物、行级锁、并发性能更好CPU及内存缓存页优化使得资源利用率更高。 【强制】2每张表必须设置一个主键ID且这个主键ID使用自增主键在满足需要的情况下尽量短除非在分库分表环境下。 解读由于InnoDB组织数据的方式决定了需要有一个主键而且若是这个主键ID是单调递增的可以有效提高插入的性能避免过多的页分裂、减少表碎片提高空间的使用率。而在分库分表环境下则需要统一来分配各个表中的主键值从而避免整个逻辑表中主键重复。
【强制】3必须使用utf8mb4字符集 解读在Mysql中的UTF-8并非“真正的UTF-8”而utf8mb4”才是真正的“UTF-8”。
【强制】4 数据库表、表字段必须加入中文注释 解读大家都别懒
【强制】5 库名、表名、字段名均小写下划线风格不超过32个字符必须见名知意禁止拼音英文混用。 解读约定
【强制】6单表列数目必须小于30若超过则应该考虑将表拆分 解读单表列数太多使得Mysql服务器处理InnoDB返回数据之间的映射成本太高
【强制】7禁止使用外键如果有外键完整性约束需要应用程序控制 解读外键会导致表与表之间耦合UPDATE与DELETE操作都会涉及相关联的表十分影响SQL的性能甚至会造成死锁。
【强制】8必须把字段定义为NOT NULL并且提供默认值 解读a、NULL的列使索引/索引统计/值比较都更加复杂对MySQL来说更难优化 b、NULL这种类型Msql内部需要进行特殊处理增加数据库处理记录的复杂性同等条件下表中有较多空字段的时候数据库的处理性能会降低很多 c、NULL值需要更多的存储空无论是表还是索引中每行中的NULL的列都需要额外的空间来标识
【强制】9禁用保留字如DESC、RANGE、MARCH等请参考Mysql官方保留字。
【强制】10如果存储的字符串长度几乎相等使用CHAR定长字符串类型。 解读能够减少空间碎片节省存储空间。
【建议】11在一些场景下考虑使用TIMESTAMP代替DATETIME。 解读a、这两种类型的都能表达yyyy-MM-dd HH:mm:ss格式的时间TIMESTAMP只需要占用4个字节的长度可以存储的范围为(1970-2038)年在各个时区所展示的时间是不一样的b、而DATETIME类型占用8个字节对时区不敏感可以存储的范围为(1001-9999)年。
【建议】12当心自动生成的Schema建议所有的Schema手动编写。 解读对于一些数据库客户端不要太过信任。
二、SQL规约
【建议】 (1) 为了充分利用缓存不允许使用自定义函数、存储函数、用户变量。 解读如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、Mysql库中的系统表其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间返回不同的查询结果。 【强制】2在查询中指定所需的列而不是直接使用“ *”返回所有的列 解读a读取不需要的列会增加CPU、IO、NET消耗 b不能有效的利用覆盖索引
【强制】3不允许使用属性隐式转换 解读假设我们在手机号列上添加了索引然后执行下面的SQL会发生什么explain SELECT user_name FROM parent WHERE phone13812345678; 很明显就是索引不生效会全表扫描。 【建议】4在WHERE条件的属性上使用函数或者表达式 解读Mysql无法自动解析这种表达式无法使用到索引。 【强制】5禁止使用外键与级联一切外键概念必须在应用层解决。 解读外键与级联更新适用于单机低并发不适合分布式、高并发集群;级联更新是强阻塞存在数据库更新风暴的风险外键影响数据库的插入速度。 【建议】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的情况影响查询性能。 解读1、假设有查询条件为WHERE a? and b? ORDER BY c 存在索引a_b_c则此时可以利用索引排序。2、反例在查询条件中包含了范围查询那么索引有序性无法利用如:WHERE a10 ORDER BY b; 索引a_b无法排序。 【建议】8在where中索引的列不能某个表达式的一部分也不能是函数的参数。 解读即是某列上已经添加了索引但是若此列成为表达式的一部分、或者是函数的参数Mysql无法将此列单独解析出来索引也不会生效。 【建议】 9我们在where条件中使用范围查询时索引最多用于一个范围条件超过一个则后边的不走索引。 解读Mysql能够使用多个范围条件里边的最左边的第一个范围查询但是后边的范围查询则无法使用。 【建议】 10在多个表进行外连接时表之间的关联字段类型必须完全一致 解读当两个表进行Join时字段类型若没有完全一致则加索引也不会生效这里的完全一致包括但不限于字段类型、字段长度、字符集、collection等等
