【mysql】索引优化

微信扫一扫,分享到朋友圈

【mysql】索引优化

查询优化

(1) 覆盖索引
如果一个索引包含(或覆盖)所有需要查询的字段的值,称为‘覆盖索引’,不需要回表,减少树的搜索次数
(2)联合索引---最左前缀原则:
如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 。对于a,c, 只走a字段索引,不会走c字段。
(3)前缀索引:前缀索引用来作为字符串的索引,1.倒叙存储;2.使用hash字段,作为校验码;
案例:
查询一个广告文案下面对应的关键词内容,广告文案内容长度一般会有30到50这样子的中文字符。
我们采取的做法是 新增一个hash字段,将广告文案在代码层面进行md5,然后存储到hash字段中,
可能会出现hash冲突,我们在代码层面再做一层 ”数据的完全匹配“;减少索引的长度,同时复杂计算放在代码层面。
(4)尽量选择区分度高的列作为索引
(5)把复杂计算放到业务层而不是数据库层
(6)分页查询优化
(7)字段的属性要设置为not null,使用一个特殊的值作为默认值,例如0或者一个空串

索引失效的情况:

(1)索引列不能参与计算,保持列“干净”,
比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。
(2)负向条件查询不能使用索引。
select * from order where status!=0 and stauts!=1
not in/not exists都不是好习惯。
查询条件中使用了不等于操作符(<>、!=)的select语句执行慢
原因:SQL中,不等于操作符会限制索引,引起全表扫描,即使比较的字段上有索引
(3)前导模糊查询不能使用索引。
select * from order where desc like '%XX'
(4)隐式的强制类型转换会全表扫描
select name from user where phone=13800001234
你以为会命中phone索引么?大错特错了!!!
(5)OR语句使用不当
使用or分割的条件,如果or前后条件的列存在没有索引,那么涉及到的索引都不会使用,导致全表扫描。
例如:例如:where A=1 or B=2,A上有索引,B上没索引,则会全表查询。

为什么推荐使用NOT NULL

允许为null的列,查询有潜在大坑。

1.null字段的数据,只能通过 is null查询出来

2.当用count函数进行统计时,NULL 列不会计入统计。

SELECT count(name) from t

而且,null是一个属性,额外占有1个字节

分页查询优化:

select * from auth_user limit offset,len;

mysql 底层是从第一行开始找到offset+len行,再抛弃前面的offset行。

优化分页的问题其实就是offset的问题,尤其是偏移量大的时候。mysql会扫描大量不需要的行然后抛弃,只取limit的数量。所以一般最好不要用offset。解决方法有

1.通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据,而不是通过二级索引获取主键再通过主键去遍历数据页。

2.可以把limit查询转换成已知位置的查询,变成between XXX and XXX 。

3.可以记录上次查询的数据的位置,下一次查询直接从该位置开始扫描。

查询1:
SELECT `id` FROM `table` WHERE `node` = 2 ORDER BY `id` ASC LIMIT 10
假设查询1的第10条数据的id是10,那么查询第二页的SQL如下
SELECT `id` FROM `table` WHERE `node` = 2 AND `id` >10 ORDER BY `id` ASC LIMIT 10

视频公开课!从小白到专家,Istio技术实践之监控与跟踪

上一篇

一、kafka 介绍 && kafka-client

下一篇

你也可能喜欢

【mysql】索引优化

长按储存图像,分享给朋友