搜文章
推荐 原创 视频 Java开发 iOS开发 前端开发 JavaScript开发 Android开发 PHP开发 数据库 开发工具 Python开发 Kotlin开发 Ruby开发 .NET开发 服务器运维 开放平台 架构师 大数据 云计算 人工智能 开发语言 其它开发
Lambda在线 > 运维少年 > MySQL系列-InnoDB索引介绍和管理

MySQL系列-InnoDB索引介绍和管理

运维少年 2020-08-01
系列文章说明

MySQL系列文章包含了软件安装、具体使用、备份恢复等内容,主要用于记录个人的学习笔记,主要使用的MySQL版本为5.7.28,服务器系统版本为CentOS 7.5。本章节内容为InnoDB索引管理。


索引


索引

索引的定义

   索引是一种数据结构,能够帮助我们快速的检索数据库中的数据。通俗来讲,索引就像一本书的目录一样,我们可以通过目录快速地查找我们需要的内容。

索引

索引的优缺点


索引的优点:

    1、可以加快数据的检索速度

    2、通过索引,可以在使用的过程中,使用优化隐藏器,提高系统的性能

索引的缺点:

    1、当对表中的数据进行增加、删除和修改时,索引需要动态维护,会减低增/改/删的执行效率

    2、索引需要占用物理空间


索引

索引的分类

    在MySQL InnoDB中,索引可以分为聚簇索引、辅助索引(非聚簇索引)两类。



聚簇索引

    

        索引 B+Tree 的叶子节点存储了整行数据的是主键索引,也被称之为聚簇索引。聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。一般情况下主键会默认创建聚簇索引,且一张表只允许存在一个聚簇索引 , 因为数据一旦存储,顺序只能有一种。找到了索引就找到了需要的数据,那么这个索引就是聚簇索引,所以主键就是聚簇索引,修改聚簇索引其实就是修改主键。


      一般来说,一个表一定有聚簇索引,就算不定义,InnoDB也会自动选择列生成索引:

    1)  有主键时,根据主键创建聚簇索引
    2)  没有主键时,会用一个唯一且不为空的索引列作为主键,成为此表的聚簇索引
    3) 如果以上两个都不满足那innodb自己创建一个虚拟的聚集索引


     如有下表:

CREATE TABLE world.student(`id` INT AUTO_INCREMENT NOT NULL COMMENT 'id',`name` VARCHAR(10) NOT NULL COMMENT '姓名',`age` INT NOT NULL COMMENT '年龄',PRIMARY KEY(id),INDEX idx_name(NAME))ENGINE=INNODB DEFAULT CHARSET='utf8mb4';
INSERT INTO world.`student`(NAME,age) VALUES('张三',24),('李四',20),('王五',21),('运维少年',18);

    

        则聚簇索引结构如下:      

MySQL系列-InnoDB索引介绍和管理

       聚簇索引查找过程:

MySQL系列-InnoDB索引介绍和管理


辅助索引

        索引B+Tree 的叶子节点 只 存储了主键的值和索引列的是非主键索引,也被称之为非聚簇索引。一个表可以有多个非聚簇索引 。 非聚簇 索引的存储和数据的存储是分离的,也就是说可能找到了索引但没找到数据,需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。

 

        辅组索引在student表中的数据结构:

MySQL系列-InnoDB索引介绍和管理

    辅组索引查找数据过程(不回表):

MySQL系列-InnoDB索引介绍和管理

    

        辅组索引查找数据过程(回表):

MySQL系列-InnoDB索引介绍和管理


辅助索引

单列索引

        单列索引,即使用一列作为辅助索引列,但查询条件使用到辅助索引列时,会使用索引。


辅助索引

多列索引(联合索引)

        联合索引,即多列索引,在创建索引时,将多列作为索引列,如:

alter table student add index idx_na(name,age);

        

辅助索引

前缀索引

        前缀索引是针对于,我们所选择的索引列值长度过长,会导致索引数高度增高,会导致索引应用时,需要读取更多的索引数据页,MySQL中建议索引树高度3-4层。所以可以选择大字段的前面部分字符作为索引生成条件。

        类似于书的标题不能过长,如果标题太长,生成目录的时候,目录占的位置就很多,目录页就会增加,前缀索引一般用于模糊查询的时候。


       如有下表,需要使用sno列作为索引列,通过对比可知前面6位字符可以确定一个唯一的值,所以在创建索引时,可以将前缀长度设置为6,减少索引树的高度。

MySQL系列-InnoDB索引介绍和管理

alter table xxx add index index_name(sno(2));



索引管理

     查看索引:

show index from world.student;

MySQL系列-InnoDB索引介绍和管理

参数 说明
Table 表示创建索引的数据表名,这里是 student 数据表。
Non_unique 表示该索引是否是唯一索引。若不是唯一索引,则该列的值为 1;若是唯一索引,则该列的值为 0。
Key_name 表示索引的名称。
Seq_in_index 表示该列在索引中的位置,如果索引是单列的,则该列的值为 1;如果索引是组合索引,则该列的值为每列在索引定义中的顺序。
Column_name 表示定义索引的列字段。
Collation 表示列以何种顺序存储在索引中。在 MySQL 中,升序显示值“A”(升序),若显示为 NULL,则表示无分类。
Cardinality 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL 使用该索引的机会就越大。
Sub_part 表示列中被编入索引的字符的数量。若列只是部分被编入索引,则该列的值为被编入索引的字符的数目;若整列被编入索引,则该列的值为 NULL。
Packed 指示关键字如何被压缩。若没有被压缩,值为 NULL。
Null 用于显示索引列中是否包含 NULL。若列含有 NULL,该列的值为 YES。若没有,则该列的值为 NO。
Index_type 显示索引使用的类型和方法(BTREE、FULLTEXT、HASH、RTREE)。
Comment 显示评注。

    

        删除索引:

drop index idx_name on world.student;

MySQL系列-InnoDB索引介绍和管理


    创建索引:

    常用办法1:创建表时创建

CREATE TABLE world.student(`id` INT AUTO_INCREMENT NOT NULL COMMENT 'id',`name` VARCHAR(10) NOT NULL COMMENT '姓名',`age` INT NOT NULL COMMENT '年龄',PRIMARY KEY(id),  # 聚簇索引INDEX idx_name(name) # 辅助索引)ENGINE=INNODB DEFAULT CHARSET='utf8mb4';

    

    常用办法2:使用alter语句

alter table world.student add index idx_name(name);

MySQL系列-InnoDB索引介绍和管理


什么时候创建索引?

1)按照业务语句的需求创建合适的索引,并不是将所有列都建立索引

2)并不是将所有的列都建立索引,不是索引越多越好

3)将索引建立在进程做where group by order by join on 条件的列


乱建索引的后果?

1)如果冗余索引过多,表数据变化的时候,很有可能导致索引频繁更新。会阻塞很多正常的业务请求

2)索引过多,会导致优化器选择出现偏差


索引应用规范:

1、建表时一定要有主键,主键最好是数字列,如果没有,可以自定义一个无关列,然后定义为自增长的

2、选择唯一性索引

唯一性索引的值时唯一的,可以更快速的通过该索引来确定某条记录

例如学生表中学号时具有唯一性的字段,为该字段建立唯一索引可以很快的确定某个学生的信息,如果使用姓名的化,可能存在同名现象,从而降低查询速度。

3、为经常需要where、order by、group by ,join on等操作的字段,排序会浪费很多多时间,可以建立索引,优化查询,如果经常作为条件的列,重复值特别多,可以建立联合索引

4、尽量使用前缀索引,如果索引字段的值很长,最好使用值的前缀来索引

5、限制索引的数目

    索引的数目不是越多越好,可能会产生的问题

    1)每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大

    2)修改表时,对索引的重构和更新很麻烦,越多的索引,会使表更新变得很浪费时间

    3)优化器的负担会很重,有可能会影响到优化器的选择

percona-toolkit中有个工具,专门分析索引是否有用

6、删除不再使用或者很少使用的索引

7、大表加索引,要在业务不繁忙期间操作

8、尽量少在更新值的列上建索引



索引补充

     

补充

准备表

CREATE TABLE world.student(`id` INT AUTO_INCREMENT NOT NULL COMMENT 'id',`name` VARCHAR(10) NOT NULL COMMENT '姓名',`age` CHAR(3) NOT NULL COMMENT '年龄',`address` VARCHAR(20) NOT NULL COMMENT '地址',`phone` VARCHAR(11) NOT NULL COMMENT '手机号码',PRIMARY KEY(id),INDEX idx_info(age,NAME,address))ENGINE=INNODB DEFAULT CHARSET='utf8mb4';
INSERT INTO world.`student`(NAME,age,address,phone) VALUES('张三',24,'北京市','10086'),('李四',20,'上海市','10000'),('王五',21,'重庆市','10010'),('运维少年',18,'天津市','13800138000');


补充

如何查看SQL是否走索引?

        办法1:explain

explain 执行的语句explain select * from student where id=1;

        办法2:desc

desc 执行的语句desc select * from student where name='张三'

MySQL系列-InnoDB索引介绍和管理

参数 说明
table
此次查询涉及到的表    
type

查询类型。

全表扫 -- 没有用到索引

索引扫:(性能从上往下主键提高)

①index 全索引扫描  需要扫描整个索引树

②range 范围索引,范围查找

③ref 辅组索引等值查询

④eq_ref多表连接中,非驱动表连接条件或唯一键

const(system)主键、聚簇索引等值查询  

possible_keys 可能用到的索引    
key  最后选择的索引    
key_len 联合索引覆盖长度
rows 此次查询需要扫描的行数    
Extra

外的

using filesort : 表示此次查询用到了文件排序,说明在查询中的排序操作:order by group by distinct .. 是没有使用到索引的



补充

索引扫描类型


1)index 全索引扫描 --  需要扫描整个索引树


    index全索引扫描一般出现在查询列为索引列时

select id from student;

MySQL系列-InnoDB索引介绍和管理


2)range -- 范围扫描


    当查询条件为>、<、in、like 时,类型会为range

MySQL系列-InnoDB索引介绍和管理

MySQL系列-InnoDB索引介绍和管理

MySQL系列-InnoDB索引介绍和管理


3)ref -- 辅助索引等值查询

    

    辅助索引等值查询一般出现在使用辅助索引列做等值查询时。

MySQL系列-InnoDB索引介绍和管理


4)const 主键等值查询

    聚簇索引等值查询一般出现在使用聚簇索引列做等值查询时。

MySQL系列-InnoDB索引介绍和管理


补充

什么情况下会不走索引?

1)没有查询条件的

MySQL系列-InnoDB索引介绍和管理


2)查询结果集时原表中的大部分数据,应该时15%-30%,如果超过,优化器觉得没有必要走索引了,可以使用limi分页

MySQL系列-InnoDB索引介绍和管理

3)查询条件属用函数在索引列上,或者对索引列进行运算,运算包括(+-*/!等)

    错误的例子:select * from student id-1=2;

    正确的例子:select * from student id=3;

MySQL系列-InnoDB索引介绍和管理

4)隐式转换导致索引失效,这一点应当引起重视,很多时候会犯这个错。如定义了char,查询时使用数字类型。

MySQL系列-InnoDB索引介绍和管理

5)not in 不走辅助索引。or或in 可以改成union

MySQL系列-InnoDB索引介绍和管理

6)like "%_"百分号在前面不走

MySQL系列-InnoDB索引介绍和管理


补充

多列索引(联合索引)

使用多列组合一个索引idx(age,name,address)


有效索引: age age,name  age,name,address (只能以age开头,并且连续的)

无效索引: name address name,address

MySQL系列-InnoDB索引介绍和管理

索引只能走等于和不能走范围值:

    1、age= and name= and address=  #索引能到address

    2、age= and name> and address= # 索引能到name

    3、age< and name = and address= # 索引能到a


MySQL系列-InnoDB索引介绍和管理

MySQL系列-InnoDB索引介绍和管理


end





版权声明:本站内容全部来自于腾讯微信公众号,属第三方自助推荐收录。《MySQL系列-InnoDB索引介绍和管理》的版权归原作者「运维少年」所有,文章言论观点不代表Lambda在线的观点, Lambda在线不承担任何法律责任。如需删除可联系QQ:516101458

文章来源: 阅读原文

相关阅读

关注运维少年微信公众号

运维少年微信公众号:yunweishaonian

运维少年

手机扫描上方二维码即可关注运维少年微信公众号

运维少年最新文章

精品公众号随机推荐