MySQL索引篇——索引相关学习

索引是一种帮助MySQL高效获取数据的有序数据结构,底层为B+树结构,索引有以下作用:

        (1)提高运行效率

        (2)降低IO成本(对B+树来说,查询千万级别的数据最多只需要3-4次磁盘IO)

        (3)索引对数据排序,降低了CPU的消耗

一、B+树

        InnoDB存储引擎的索引使用B+树实现。它只在叶子节点存储数据(注意: InnoDB的主键索引的叶子节点存储行数据,非主键索引的叶子节点存储主键和其他带索引的列数据),且叶子节点之间用指针连接,而非叶子节点只存储指针(索引)。B+树有以下优点:

        (1)读写B+树的代价更低

          千万级的数据只需要3-4层高度就可以,且B+树有大量冗余节点,删除一个节点的时候可以从叶子节点删除,可以不用改动非叶子节点。

        (2)查询B+树更加稳定

          每次查询都必须查询到叶子节点,而B-树的数据可能在叶子节点,也可能在中间节点,或者都有

        (3)便于扫库和区间查询

          B+树的叶子节点之间是双向链表连接

二、索引有哪些

        1. 单值索引:一个索引只包含单个列,一个表可以有多个单列索引

  • 建表时,加上 key(列名) 指定
  • 单独创建,create index 索引名 on 表名(列名)
  • 单独创建,alter table 表名 add index 索引名(列名)

        2. 唯一索引:索引列的值必须唯一,但允许有 null 且 null 可以出现多次

  • 建表时,加上 unique(列名) 指定
  • 单独创建,create unique index idx 表名(列名) on 表名(列名)
  • 单独创建,alter table 表名 add unique 索引名(列名)

        3. 主键索引:设定为主键后数据库会自动建立索引,innodb 为聚簇索引,值必须唯一且不能为null

  • 建表时,加上 primary key(列名) 指定

        4. 复合索引:即一个索引包含多个列,如(name,password)

  • 建表时,加上 key(列名列表) 指定
  • 单独创建,create index 索引名 on 表名(列名列表)
  • 单独创建,alter table 表名 add index 索引名(列名列表)

        5.前缀索引:对字符类型字段的前几个字符建立的索引,而不是在整个字段上建立的索引,前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。使用前缀索引的目的是为了减少索引占用的存储空间,提升查询效率

三、索引创建原则

1.什么时候需要创建

        (1)数据量较大,且查询比较频繁的表建立索引

        (2)常作为查询条件的字段创建索引,如where、group by、分组

        (3)尽量使用联合索引,减少单列索引

        (4)字符串类型的字段,且字段较长的情况下建立索引——前缀索引

2.创建索引需要注意什么

        (1)表数据太少,不需要创建索引

        (2)索引不是越多越好,控制索引数量

        (3)选择区分度高的列,建立唯一索引(如性别就不合适)

        (4)频繁变更的字段不适合创建索引,频繁变更会更改B+树,影响数据库性能

四、索引失效

        如有一个联合索引(name,age,address),在以下几种情况下会失效

        (1)违反最左前缀原则,即跳过某一列查询

          如 name,address,跳过了age;使用age,address查询,跳过了name,= 和 in可以乱序

        (2)范围查询右边的列会造成索引失效

          如 where name = ' Mike' and id >= 1 and address = '上海' , 会造成address不能命中

        (3)like的前导模糊查询

          如 like %小米       

        (4)字符串不加单引号

          如 borad = 小米,正确 borad = '小米'

        (5)在索引列上进行运算操作

          如 where subString(name,3,2)= '小米'

        (6)全表查询比使用索引快时,索引失效

        (7)索引不会包含有NULL值的列IS NULL,IS NOT NULL无法使用索引

        只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以在数据库设计时,尽量使用NOT NULL约束以及默认值。

五、索引优化

 1. 避免使用导致索引失效的用法

        (1)like语句的前导模糊查询不能使用索引

        (2)范围条件右边的列(范围列可以用到索引),范围列之后列的索引全失效

        (3)不要在索引列上面做任何操作(计算、函数),否则会导致索引失效而转向全表扫描

        范围条件有:<、<=、>、>=、between等

        (4)强制类型转换会全表扫描

        字符串类型不加单引号会导致索引失效,因为mysql会自己做类型转换,相当于在索引列上进行了操作。

        (5)负向条件查询不能使用索引

        负向条件有:!=<>not innot existsnot like 等,优化案例:

2. 其他优化方式

        (1)union、in、or 都能够命中索引,但是建议使用 in

                因为in的综合效率最高。

        (2)联合索引最左前缀原则

        如果在(a,b,c)三个字段上建立联合索引,那么他会自动建立 a| (a,b) | (a,b,c) 组索引。

  • 建立联合索引的时候,区分度最高的字段在最左边
  • 存在非等号和等号混合判断条件时,在建立索引时,把等号条件的列前置。如 where a>? and b=?,那么即使a 的区分度更高,也必须把 b 放在索引的最前列
  • 最左前缀查询时,并不是指SQL语句的where顺序要和联合索引一致

 六、SQL优化

1. 对表的设计优化

             分库分表:进行水平分割或垂直分割

2. SQL语句优化

        (1)避免使用select *

        (2)避免索引失效的写法

        (3)用union all 代替union,因为后者多一次过滤

        (4)能用inner join就不用left join、right join,因为内连接会对两个表进行优化,默认以小标为驱动,而左右连接不会调整连接顺序

        (5)使用between and代替in

        (6)对枚举类型的字段,如性别、星期等建议使用enum而不是varchar

        (7)字段设计尽可能使用not null

        3. 定位慢查询

        (1)开源工具调试:如skywalking等,可以查看接口的执行情况

        (2)使用MySQL自带的慢日志

        在配置文件中 使用 slow-query-log = 1爱妻,设置值为2s

       4. 优化慢sql

        采用EXPLAIN或者DESC命令,可以展示sql的执行情况,如

        EXPLAIN SELECT * FROM user WHERE id = 1,该命令有以下几个字段

        (1)possible-key:可能会用的索引

        (2)key:实际命中的索引

        (3)key-len:索引占用的大小

        (4)Extra:额外的优化建议,是否存在回表等

        (5)type:这条sql的连接类型,是否有优化空间,类型有以下几种:

          const:表示通过索引一次就找到了,const用于比较primary key 或者 unique索引。因为只需匹配一行数据,所有很快。

         system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,可以忽略不计

          eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键 唯一索引扫描。

          ref:非唯一性索引扫描,其他索引

          range:只检索给定范围的行,一般就是在where语句中出现了between、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点,结束于另一个点,不用扫描全部索引

          index:扫描全部索引,index与ALL区别为index类型只遍历索引树。这通常为ALL块,应为索引文件通常比数据文件小。(Index与ALL虽然都是读全表,但index是从索引中读取,而ALL是从硬盘读取)

          ALL:全表扫描,遍历全表以找到匹配的行

参考:MySQL高级 之 explain执行计划详解_走慢一点点的博客-CSDN博客、CSView

       

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值