阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL索引与SQL语句优化

194次阅读
没有评论

共计 1675 个字符,预计需要花费 5 分钟才能阅读完成。

概念
1. 普通索引:最基本的索引,它没有任何限制
2. 唯一索引:索引列的值必须唯一,且不能为空,如果是组合索引,则列值的组合必须唯一。
3. 主键索引:特殊的索引,唯一的标识一条记录,不能为空,一般用 primary key 来约束。
4. 联合索引:在多个字段上建立索引,能够加速查询到速度
5.Union:对两个结果集进行并集操作,不包括重复的行,同时进行默认规则排序
6.Union all:对两个结果集进行并集操作,包括重复行,不进行排序

优化

1、前导模糊查询不能使用索引,

如 name like‘% 静’

2、Union、in、or 可以命中索引,建议使用 in

3、负条件查询不能使用索引,可以优化为 in 查询,

其中负条件有!=、<>、not in、not exists、not like 等

4、联合索引最左前缀原则,又叫最左侧查询,

如果在(a,b,c)三个字段上建立联合索引,那么它能够加快 a |(a,b)|(a,b,c)三组的查询速度。

5、建立联合查询时,区分度最高的字段在最左边

6、如果建立了(a,b)联合索引,就不必再单独建立 a 索引。

同理,如果建立了(a,b,c)索引就不必再建立 a,(a,b)索引

7、存在非等号和等号混合判断条件时,在建索引时,要把等号条件的列前置

8、范围列可以用到索引,但是范围列后面的列无法用到索引。

索引最多用于一个范围列,如果查询条件中有两个范围列则无法全用到索引。范围条件有:<、<=、>、>=、between 等。

9、把计算放到业务层而不是数据库层。

在字段上计算不能命中索引,

10、强制类型转换会全表扫描,

如果 phone 字段是 varcher 类型,则下面的 SQL 不能命中索引。Select * fromuser where phone=13800001234

11、更新十分频繁、数据区分度不高的字段上不宜建立索引。

更新会变更 B + 树,更新频繁的字段建立索引会大大降低数据库性能。

“性别”这种区分度不太大的属性,建立索引是没有什么意义的,不能有效过滤数据,性能与全表扫描类似。

一般区分度在 80% 以上就可以建立索引。区分度可以使用 count(distinct(列名))/count(*)来计算。

12、利用覆盖索引来进行查询操作,避免回表。

被查询的列,数据能从索引中取得,而不是通过定位符 row-locator 再到 row 上获取,即“被查询列要被所建的索引覆盖”,这能够加速度查询。

13、建立索引的列不能为 null,使用 not null 约束及默认值

14、利用延迟关联或者子查询优化超多分页场景,

MySQL 并不是跳过 offset 行,而是取 offset+ N 行,然后放弃前 offset 行,返回 N 行,那当 offset 特别大的时候,效率非常低下,要么控制返回的总数,要么对超过特定阈值的页进行 SQL 改写。

15、业务上唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。

16、超过三个表最好不要用 join,

需要 join 的字段,数据类型必须一致,多表关联查询时,保证被关联的字段需要有索引。

17、如果明确知道查询结果只要一条,limit 1 能够提高效率,比如验证登录的时候。

18、Select 语句务必指明字段名称

19、如果排序字段没有用到索引,就尽量少排序

20、尽量用 union all 代替 union。

Union 需要将集合合并后在进行唯一性过滤操作,这会涉及到排序,大量的 cpu 运算,加大资源消耗及延迟,当然,使用 union all 的前提条件是两个结果集没有重复数据。

21、使用合理的分页提高效率。

select id,name from product limit 866613, 20

使用上述 SQL 语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用 limit 分页查询会越来越慢。

优化的方法如下:

可以取前一页的最大行数的 id,然后根据这个最大的 id 来限制下一页的起点。

比如此列中,上一页最大的 id 是 866612。

SQL 可以采用如下的写法:select id,name from product where id> 866612 limit 20。

 

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计1675字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中