【116期】MySQL索引优缺点、何时需要,不需要创建索引、索引及sql语句的优化

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 【116期】MySQL索引优缺点、何时需要,不需要创建索引、索引及sql语句的优化

点击上方“Java面试题精选”,关注公众号

面试刷图,查缺补漏

号外:****往期面试题,10篇为一个单位归置到本公众号菜单栏-面试题,有需要的欢迎翻阅

阶段汇总集合:

一、什么是索引?

索引是对数据库表中的一列或多列值进行排序的一种结构,使用索引可以快速访问数据库表中的特定信息。

二、索引的作用?

索引相当于图书上的目录,可以根据目录上的页码快速找到所需的内容,提高性能(查询速度)

三、优点:

  • 通过创建唯一性索引,可以保证数据库表中的每一行数据的唯一性。
  • 可以加快数据的检索速度
  • 可以加速表与表之间的连接
  • 在使用分组和排序进行检索的时候,可以减少查询中分组和排序的时间
  • 四、缺点

  • 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
  • 索引需要占用物理空间,数据量越大,占用空间越大
  • 会降低表的增删改的效率,因为每次增删改索引,都需要进行动态维护
  • 五、什么时候需要创建索引

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中排序的字段创建索引将大大提高排序的速度(索引就是排序加快速查找
  • 查询中统计或者分组的字段;
  • 六、什么时候不需要创建索引

  • 频繁更新的字段不适合创建索引,因为每次更新不单单是更新记录,还会更新索引,保存索引文件
  • where条件里用不到的字段,不创建索引;
  • 表记录太少,不需要创建索引;
  • 经常增删改的表;
  • 数据重复且分布平均的字段,因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据,因此他建立索引就没有太大的效果,例如性别字段,只有男女,不适合建立索引。更多面试题,欢迎关注公众号 Java面试题精选
  • 七、索引的分类:

  • 普通索引:最基本的索引,它没有任何限制
  • 唯一索引:索引列的值必须唯一,且不能为空,如果是组合索引,则列值的组合必须唯一。
  • 主键索引:特殊的索引,唯一的标识一条记录,不能为空,一般用primary key来约束。
  • 联合索引:在多个字段上建立索引,能够加速查询到速度
  • 八、索引和sql语句的优化

    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

    出处:cnblogs.com/l199616j/p/11232392.html

    与其在网上拼命找题?** 不如马上关注我们~**

    【116期】MySQL索引优缺点、何时需要/不需要创建索引、索引及sql语句的优化

    原文始发于微信公众号(Java面试题精选):

    本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

    转载声明:转载请注明出处,本技术博客是本人原创文章

    本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

    原文链接:blog.ouyangsihai.cn >> 【116期】MySQL索引优缺点、何时需要,不需要创建索引、索引及sql语句的优化


     上一篇
    【115期】TCP协议面试10连问,总会用得到,建议收藏~ 【115期】TCP协议面试10连问,总会用得到,建议收藏~
    点击上方“Java面试题精选”,关注公众号 面试刷图,查缺补漏 号外:****往期面试题,10篇为一个单位归置到本公众号菜单栏-面试题,有需要的欢迎翻阅 阶段汇总集合: 先亮出这篇文章的思维导图 TCP 作为传输层的协议,是一个软件工程
    2021-04-05
    下一篇 
    【117期】面试官——熟悉JVM吗?为什么新生代内存需要有两个Survivor区? 【117期】面试官——熟悉JVM吗?为什么新生代内存需要有两个Survivor区?
    点击上方“Java面试题精选”,关注公众号 面试刷图,查缺补漏 号外:****往期面试题,10篇为一个单位归置到本公众号菜单栏-面试题,有需要的欢迎翻阅 阶段汇总集合: 在JVM的新生代内存中,为什么除了Eden区,还要设置两个Surviv
    2021-04-05