MySQL系列-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;| 参数 | 说明 |
|---|---|
| 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; 创建索引：
常用办法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='张三'
| 参数 | 说明 |
| 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;2）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
end