MySQL系列-InnoDB索引介绍和管理
MySQL系列-InnoDB索引介绍和管理
运维少年 运维少年
系列文章说明
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);
则聚簇索引结构如下：
聚簇索引查找过程：
辅助索引
索引B Tree 的叶子节点 只 存储了主键的值和索引列的是非主键索引，也被称之为非聚簇索引。一个表可以有多个非聚簇索引 。 非聚簇 索引的存储和数据的存储是分离的，也就是说可能找到了索引但没找到数据，需要根据索引上的值（主键）再次回表查询，非聚簇索引也叫做辅助索引。
辅组索引在student表中的数据结构：
辅组索引查找数据过程（不回表）：
辅组索引查找数据过程（回表）：
辅助索引 单列索引
单列索引，即使用一列作为辅助索引列，但查询条件使用到辅助索引列时，会使用索引。
辅助索引 多列索引（联合索引）
联合索引，即多列索引，在创建索引时，将多列作为索引列，如：
alter table student add index idx_na(name,age);辅助索引 多列索引（联合索引）
前缀索引是针对于，我们所选择的索引列值长度过长，会导致索引数高度增高，会导致索引应用时，需要读取更多的索引数据页，MySQL中建议索引树高度3-4层。所以可以选择大字段的前面部分字符作为索引生成条件。
类似于书的标题不能过长，如果标题太长，生成目录的时候，目录占的位置就很多，目录页就会增加，前缀索引一般用于模糊查询的时候。
如有下表，需要使用sno列作为索引列，通过对比可知前面6位字符可以确定一个唯一的值，所以在创建索引时，可以将前缀长度设置为6，减少索引树的高度。
alter table xxx add index index_name(sno(2));索引管理
查看索引：
show index from world.student;删除索引：
drop index idx_name on world.student;创建索引：
常用办法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);什么时候创建索引?
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='张三'补充索引扫描类型
1）index 全索引扫描 -- 需要扫描整个索引树
index全索引扫描一般出现在查询列为索引列时
select id from student;2）range -- 范围扫描
当查询条件为>、<、in、like 时，类型会为range
3）ref -- 辅助索引等值查询
辅助索引等值查询一般出现在使用辅助索引列做等值查询时。
4）const 主键等值查询
聚簇索引等值查询一般出现在使用聚簇索引列做等值查询时。
补充什么情况下会不走索引？
1）没有查询条件的
2）查询结果集时原表中的大部分数据，应该时15%-30%，如果超过，优化器觉得没有必要走索引了，可以使用limi分页
3）查询条件属用函数在索引列上，或者对索引列进行运算，运算包括（ -/!等）
错误的例子：select from student id-1=2;
正确的例子：select * from student id=3;
4）隐式转换导致索引失效，这一点应当引起重视，很多时候会犯这个错。如定义了char，查询时使用数字类型。
5）not in 不走辅助索引。or或in 可以改成union
6）like "%_"百分号在前面不走
补充多列索引（联合索引）
使用多列组合一个索引idx(age,name,address)
有效索引: age age,name age,name,address (只能以age开头，并且连续的)
无效索引: name address name,address
索引只能走等于和不能走范围值:
1、age= and name= and address= #索引能到address
2、age= and name> and address= # 索引能到name
3、age< and name = and address= # 索引能到a