MySQL 索引设计原则

MySQL 索引设计原则:

(1)对于经常查询的字段,建议创建索引。

(2)索引不是越多越好,一个表如果有大量索引,不仅占用磁盘空间,而且会影响INSERT,DELETE,UPDATE等语句的性能。

(3)避免对经常更新的表进行过多的索引,因为当表中数据更改的同时,索引也会进行调整和更新,十分消耗系统资源。

(4)数据量小的表建议不要创建索引,数据量小时索引不仅起不到明显的优化效果,对于索引结构的维护反而消耗系统资源。

(5)不要在区分度低的字段建立索引。比如性别字段,只有 “男” 和 “女” ,建索引完全起不到优化效果。

(6)当唯一性是某字段本身的特征时,指定唯一索引能提高查询速度。

(7)在频繁进行跑排列分组(即进行 group by 或 order by操作)的列上建立索引,如果待排序有多个,可以在这些列上建立组合索引。

MySQL 索引未命中情况

(1)范围问题,条件中出现这些符号或关键字:>、>=、<、<=、!= 、between...and...、like等,如果查询范围不明确,达不到优化效果。

没有指定范围不会用到索引:

select name from s1;

大于号、小于号范围过大:

不等于!=

between ...and...

like,如果以%开头,不会用到索引:

(2) 索引列不能参与计算,保持列“干净”。

比如from_unixtime(create_time) = '2014-05-29'就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp('2014-05-29')

(3)最左前缀匹配原则,非常重要的原则。不符合规范则不会命中索引。

对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了,有索引速度也慢)。

比如:a = 1 and b = 2 and c > 3 and d = 4, 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

(4)使用函数不能命中索引

select * from tb1 where reverse(email) = 'duoduo'; -

(5)类型不一致 如果列是字符串类型,传入条件是必须用引号引起来,不然...

select * from tb1 where email = 999;

(6) order by :

当根据索引排序时候,select查询的字段如果不是索引,则速度仍然很慢

select email from s1 order by email desc;

特别的:如果对主键排序,则还是速度很快:

select * from tb1 order by nid desc;

(7)text类型,必须制定长度,否则无法命中:

create index xxxx on tb(title(19))

其他注意事项

- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引(经常使用多个条件查询时)
- 尽量使用短索引
- 使用连接(JOIN)来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致

and 和 or 的索引匹配逻辑:

(1)and的逻辑,例子:

条件:a = 10 and b = 'xxx' and c > 3 and d =4

索引:制作联合索引(d,a,b,c)

工作原理::对于连续多个and:mysql会按照联合索引,从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围),加速查询,即按照 d -> a -> b -> c 的顺序

(2)or的逻辑,例子:

条件:a = 10 or b = 'xxx' or c > 3 or d =4

索引:制作联合索引(d,a,b,c)

工作原理:对于连续多个or:mysql会按照条件的顺序,从左到右依次判断,即 a-> b -> c -> d

参考文章:https://m.jb51.net/article/140749.htm

参考书籍:

《MySQL5.7 从入门到精通》刘增杰 编著

《Java Web 轻量级开发 面试教程》孟宪福 胡书敏 金华 编著

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值