索引
一、索引检索原理
1.1 InnoDB
- Innodb是以主键为索引来组织数据的存储，Innodb中一定会有一个主键索引(如果没有显示指定主键，InnoDB会创建一个隐式的主键)
聚集索引
数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，则是聚集索引。在Innodb中它按照主键索引来组织数据，因此数据的顺序与表中的顺序是
一致的，但其他字段的索引则不满足这个特点。因此也可以认为InnoDB主键索引是聚集索引，非主键则不是。
(主键的聚集索引树的叶子节点是保存数据的，并且按照主键的顺序通过引用相互连接，这是B+树的特点)
非主键索引
- 在InnoDB中，默认数据会按照主键索引使用B+树组织好，数据都保存在叶子节点。那么此时在一个其他字段（比如name）创建一个索引，工作机制是怎么
样的呢？此时还会用name创建一个B+树结构的辅助索引树，但是这棵树的叶子节点没有保存数据，而是保存了这个记录对应的主键的值，因此按照name查找
的时候，第一步是在name这个辅助索引树中先找到对应name的叶子节点中的主键值，然后使用这个值在聚集索引树中找到叶子节点，进而找到数据。因此实
际上找了2次索引树。 - 为什么辅助索引树的叶子节点不直接保存数据？而是去找聚集索引树？
--- 降低维护成本，非索引字段变化只需要维护主键索引树。
假如数据变化，只要主键没有变化，就不需要维护辅助索引树，只需要维护主键索引树，降低维护索引的成本。
(比如name上建立了索引，address没有建立索引，现在address变化了，name和主键没有变化,只需要在主键索引树的叶子节点把数据改了就行了，name这个辅助索引
树是不需要动的，反过来假如数据直接保存在辅助索引树的叶子节点，那么address变化了，2个索引树都需要维护，而且如果我们有很多辅助索引并且都把数据保存在
辅助索引的叶子节点，那维护起来就更麻烦了)
1.2 MyIsam
- MySql使用的是B+树索引，非叶子节点是不保存数据的，叶子节点才保存数据，但是在MyISAM中索引和数据文件又是分开的，因此在索引文件的叶子节点保存的是数据
文件中对应数据的物理磁盘地址指针(或者可以这么理解)，查找时加载到叶子节点之后，根据地址去数据文件中加载数据。
二、联合索引
- MySql联合索引的关键字是多个字段组合起来，单列索引是一个特殊的联合索引。
2.1 联合索引选择规则
最左匹配原则。常用列优先在左侧
离散度高原则。离散度高优先在左侧
最少空间原则。宽度小优先在左侧
2.2 联合索引匹配
- 通过最左匹配原则，如果存在A+B的联合索引，那么单独的A字段的索引是不需要的。并且下面的查询会使用到A+B的联合索引
A字段查询
A+B字段查询
A的前缀匹配模糊查询(注意最左匹配原则，如果A字段是前后缀匹配是不能使用A字段或者A+B字段的索引的，如果是后缀匹配的话，在离散度高的时候会用到索引，离散度低则不会)
三、覆盖索引
- 查询列时可通过索引节点的关键字返回，不需要到叶子节点查询数据，则称为索引覆盖。
我们知道B+树的叶子节点只保存索引字段，并没有全部的数据，假如name字段有索引，我们select name的话，有可能扫描索引就找到结果了，不需要加载数据，但是我们select * 的
话，无疑是需要加载到叶子节点的数据。因此前者称为索引覆盖，后者则不是。
- 索引覆盖可以明显减少IO次数，将随机IO变为顺序IO，为了能够让索引覆盖，我们尽量避免select * ，使用select field(建立了索引的字段)
四、索引合并
- 把多个单列索引合并使用(将组合索引拆分为单键索引)。比如组合索引：create index on mytable(A,B);
那么联合索引可以命中：
select * from s1 where A='a' ;
select * from s1 where A='a' and B='b';
select * from s1 where A='a' ;
select * from s1 where B='b';
select * from s1 where A='a' and B='b';
- 乍一看好像索引合并更好了：可以命中更多的情况，但其实要分情况去看，如果单条件查比较多，那么还是用索引合并比较合理。如果是2个字段查询比较多，
那么组合索引比较好。
五、索引失效
5.1 like
- like的时候，使用前缀匹配，前缀模糊的时候，很可能无法命中索引
5.2 !=
- 普通索引的不等于不会走索引，特殊情况如果主键或者字段是整型会走索引的
5.3 or
5.4 order by
- 使用order by时，排序字段有索引，但是select查询的字段没有索引，那么无法命中。特殊的如果排序字段是主键的话，一定会命中索引
六、索引分类
- 索引的功能都是加速查找，除此之外部分特殊索引还有其他的附加功能，比如唯一索引的作用就是约束保证该字段的值在表中唯一。全文索引主要用于文本搜索。
| 索引分类 | 示例 |
|---|
| 普通索引 index | |
| 主键索引(唯一索引) | primary key |
| 唯一索引 | unique |
| 联合索引 | primary key(id,name) |
| 联合唯一索引 | unique(id,name) |
| 联合普通索引 | index(id,name) |
- 上面的分类更多是从作用层面，从原理层面分为hash索引和Btree索引
| 索引分类 | 示例 | 支持的存储引擎 |
|---|
| btree类型的索引 | b+树，层数越多，数据量指数级增长（我们就用它，因为Innodb默认支持它） | InnoDB 、MyISAM 、Memory |
| hash类型的索引 | 基于hash算法，查询单条快，范围查询慢 | Memory (MySql中使用不多，主流引擎都不支持它) |
七、索引相关操作语句
7.1 创建索引
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ;
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;
- 比如在t_salary表的salary字段创建索引：
CREATE TABLE `t_salary1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL COMMENT '名称',
`salary` float(6,1) DEFAULT NULL COMMENT '薪水',
`description` varchar(200) DEFAULT NULL COMMENT '描述',
PRIMARY KEY (`id`),
index slary_index (salary )
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COMMENT='t_salary1 薪水表'
或者：
CREATE INDEX slary_index ON t_salary (salary ) ;
或者：
ALTER TABLE t_salary ADD INDEX slary_index (salary ) ;
help create
help create index
7.2 删除索引
DROP INDEX 索引名 ON 表名称;
- 比如删除t_salary表的salary字段上的索引：
DROP INDEX slary_index ON t_salary;
7.3 查看索引
show index from table_name(表名);
7.4 索引大小
八、索引使用小结
避免使用select *，尽量覆盖索引。count(1)或count(列) 代替 count(*)
经常使用多个条件查询时，组合索引代替多个单列索引
尽量使用短索引。创建表时尽量时 char 代替 varchar
使用连接（JOIN）来代替子查询(Sub-Queries)
索引离散度低（重复少）不适合建索引，例：性别不适合
九、参考
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
