目录
1 索引概述
1.1 索引介绍
索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
1.2 特点
| 优势 | 劣势 |
|---|---|
| 提高数据检索的效率，降低数据库的IO成本 | 索引列也是要占用空间的 |
| 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗 | 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低 |
2 索引结构
2.1 概述
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：
上述是MySQL中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持 情况。
B+树既可以索引查找，也可以顺序查找。
数据只存在叶节点上面，分支节点都是索引，不存储数据
3 索引分类
3.1 索引分类
在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。
3.2 聚集索引 & 二级索引
而在在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
聚集索引生成的规则：
-
如果存在主键，主键索引就是聚集索引。
-
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
-
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
使用聚集索引查找数据的时候，可以一次就查到数据。因为索引和存储的行数据是在一起的；而是用二级索引要回表查询。就是先根据查询的字段查询二级索引，然后再查询聚集索引，即使需要进行回表查询。
4 索引语法
4.1 创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ; 4.2 查看索引
SHOW INDEX FROM table_name ;4.3 删除索引
DROP INDEX index_name ON table_name ;5 索引的使用
5.1 最左前缀法
如果使用了联合索引，要遵循最左前缀法则。具体分析：
最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。
所以说，如果查询的时候，聚合索引中的第一个索引条件就不存在查询条件中，那么这个索引查询就是失效的，就是普通查询。
ps：查询条件中只要存在设定索引时的字段，就能满足最左前缀法，不一定要查询条件的书写顺序和定义索引的时候一样。重点是最左前缀法定义的字段有没有在查询条件中出现。
5.2 范围查找
-
联合索引中，出现了范围查询 > < ,这种开区间的判定时，范围右侧的列索引会失效。
-
但是使用 >= <= ,这种闭区间的判定时，索引是成立的。
-
所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <=这类范围查找，避免使用 > < 这种查询条件
5.3 索引失效
5.3.1 索引列运算
不要在索引列上进行运算操作， 索引将失效。简单说就是，查询的时候直接用字段操作，不要使用函数等
5.3.2 字符串不加引号
字符串类型字段使用时，不加引号，索引将失效。
就是说，如果该字段设定索引，且该字段的类型是字符串类型，比如VARCHAR。那么查询的时候，就一定要对条件的内容使用 ' ' 括起来。不能使用MySQL的隐形转换，这样的话该字段设定的索引在查询的时候会失效。如果是聚集索引，那么，就算满足最左前缀法，那么也会失效，前面就算满足的几个条件也不会成立。
5.3.3 模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
比如下面三个查询语句，就能看出来，只有第一个索引生效。
explain select * from tb_user where profession like '软件%';
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%';5.3.4 or连接条件
用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
5.3.5 数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引。
5.4 SQL提示
可以使用 explain 查询语句，来查看当前sql语句的执行到底使用了什么索引
explain select * from tb_user where profession like '软件%';然后后面就可以直接指定当前sql查询的时候使用什么索引（就是SQL提示），避免MySQL评估，减少时间开支。
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
-
use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。
-
ignore index ： 忽略指定的索引。
-
force index ： 强制使用索引。
例如下面：
select * from tb_user use index(idx_user_pro) where profession = '软件工 程';
select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
select * from tb_user force index(idx_user_pro) where profession = '软件工程';5.5 覆盖索引
尽量使用覆盖索引，减少select * 形式的使用。那么什么是覆盖索引呢？
覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。
简单来说就是索引中包含了要查询中的内容，如果设置了id主键，聚集索引中可以不用包含这个字段，因为二级索引的叶节点中包含主键id信息。
使用 explain select ... 查询后，有一个字段是Extra
| Extra | 含义 |
|---|---|
| Using where; Using Index | 查找使用了索引，而且需要的数据都在索引列中能找到，所以不需要回表查询数据 |
| Using index condition | 查找使用了索引，但是需要回表查询数据 |
5.6 前缀索引
当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让 索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
5.6.1 语法
create index idx_xxxx on table_name(column(n)) ;实例：为tb_user表的email字段，建立长度为5的前缀索引。
create index idx_email_5 on tb_user(email(5));这个时候如果使用 explain 查看 查询语句的话，可以看到一个字段Sub_part，可以看到这个值为5，就是前缀索引的长度
5.6.2 前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。所以要使得选择性尽量为1.
# 选择性为 1
select count(distinct email) / count(*) from tb_user ;
# 选择性为 0.9583
select count(distinct substring(email,1,5)) / count(*) from tb_user ;5.6.3 前缀索引的查询流程
5.7 单列索引 与 聚合索引
-
单列索引：即一个索引只包含单个列。
-
联合索引：即一个索引包含了多个列。
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
5.8 索引设计原则
-
针对于数据量较大，且查询比较频繁的表建立索引。
-
针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
-
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
-
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
-
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
-
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
-
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
