点击上方“Coder编程”,选择“置顶公众号”
技术文章第一时间送达!
索引设计规范
MySQL的查询速度依赖良好的索引设计,因此索引对于高性能至关重要。合理的索引会加快查询速度(包括UPDATE和DELETE的速度,MySQL会将包含该行的page加载到内存中,然后进行UPDATE或者DELETE操作),不合理的索引会降低速度。MySQL索引查找类似于新华字典的拼音和部首查找,当拼音和部首索引不存在时,只能通过一页一页的翻页来查找。当MySQL查询不能使用索引时,MySQL会进行全表扫描,会消耗大量的IO。索引的用途:去重、加速定位、避免排序、覆盖索引。
什么是覆盖索引?
InnoDB存储引擎中,secondary index(非主键索引)中没有直接存储行地址,存储主键值。如果用户需要查询secondary index中所不包含的数据列时,需要先通过secondary index查找到主键值,然后再通过主键查询到其他数据列,因此需要查询两次。覆盖索引的概念就是查询可以通过在一个索引中完成,覆盖索引效率会比较高,主键查询是天然的覆盖索引。合理的创建索引以及合理的使用查询语句,当使用到覆盖索引时可以获得性能提升。比如SELECT email,uid FROM user_email WHERE uid=xx,如果uid不是主键,适当时候可以将索引添加为index(uid,email),以获得性能提升。
索引的基本规范
1、索引数量控制,单张表中索引数量不超过5个,单个索引中的字段数不超过5个。
-
综合评估数据密度和分布
-
考虑查询和更新比例
为什么一张表中不能存在过多的索引?
InnoDB的secondary index使用b+tree来存储,因此在UPDATE、DELETE、INSERT的时候需要对b+tree进行调整,过多的索引会减慢更新的速度。
2、对字符串使用前缀索引,前缀索引长度不超过8个字符,建议优先考虑前缀索引,必要时可添加伪列并建立索引。
-
不要索引blob/text等字段,不要索引大型字段,这样做会让索引占用太多的存储空间
什么是前缀索引?
前缀索引说白了就是对文本的前几个字符(具体是几个字符在建立索引时指定)建立索引,这样建立起来的索引更小,所以查询更快。前缀索引能有效减小索引文件的大小,提高索引的速度。但是前缀索引也有它的坏处:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把它们用作覆盖索引(Covering Index)。
建立前缀索引的语法:ALTER TABLE table_name ADD KEY(column_name(prefix_length));
3、主键准则
-
表必须有主键
-
不使用更新频繁的列
-
尽量不选择字符串列
-
不使用UUID MD5 HASH
-
默认使用非空的唯一键
-
建议选择自增或发号器
4、 重要的SQL必须被索引,核心SQL优先考虑覆盖索索引
-
UPDATE、DELETE语句的WHERE条件列
-
ORDER BY、GROUP BY、DISTINCT的字段
-
多表JOIN的字段
5、区分度最大的字段放在前面
-
选择筛选性更优的字段放在最前面,比如单号、userid等,type,status等筛选性一般不建议放在最前面
-
索引根据左前缀原则,当建立一个联合索引(a,b,c),则查询条件里面只有包含(a)或(a,b)或(a,b,c)的时候才能走索引,(a,c)作为条件的时候只能使用到a列索引,所以这个时候要确定a的返回列一定不能太多,不然语句设计就不合理,(b,c)则不能走索引
-
合理创建联合索引(避免冗余),(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)
6、索引禁忌
-
不在低基数列上建立索引,例如“性别”
-
不在索引列进行数学运算和函数运算
-
不要索引常用的小型表
7、 尽量不使用外键
-
外键用来保护参照完整性,可在业务端实现
-
对父表和子表的操作会相互影响,降低可用性
-
INNODB本身对online DDL的限制
MYSQL 中索引的限制
-
MYISAM 存储引擎索引长度的总和不能超过 1000 字节
-
BLOB 和 TEXT 类型的列只能创建前缀索引
-
MYSQL 目前不支持函数索引
-
使用不等于 (!= 或者 <>) 的时候, MYSQL 无法使用索引。
-
过滤字段使用函数运算 (如 abs (column)) 后, MYSQL无法使用索引。
-
join语句中join条件字段类型不一致的时候MYSQL无法使用索引
-
使用 LIKE 操作的时候如果条件以通配符开始 (如 ‘%abc…’)时, MYSQL无法使用索引。
-
使用非等值查询的时候, MYSQL 无法使用 Hash 索引。
推荐
文末
文章收录至
Github: https://github.com/CoderMerlin/coder-programming
Gitee: https://gitee.com/573059382/coder-programming
欢迎关注并star~
我知道你 “在看”