Mysql索引详解

索引在数据库优化时非常重要，如果你对数据库的理解还停留在增删改查上，那只能说你需要好好学习了。下面来一起学习下mysql的索引。

什么是索引

索引其实是优化查询的一种数据结构，建立的索引，会保存在额外的文件中。比如Mysql中的索引是用B+树实现的，而B+树就是一种数据结构，可以优化查询速度，可以利用索引快速查找数据，所以能优化查询。

索引的优缺点

• 优点：
  最大优点就是优化查询速度。
• 缺点：
  会牺牲表的修改和插入效率。

索引的适用场景

1.表的主键、外键；一般表的主键是使用频率最高的字段；
2.表的数据量超过300；
3.经常使用到的字段；
4.经常用来排序的字段；由于建立索引时已经排好序，所以比较快；
5.经常在范围内查找的字段，因为索引已经排序了，其指定的范围是连续的；

索引的类型

• 普通索引(也称单列索引)
  最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。

create index index_name on table_name(column_name);

• 唯一索引
  与普通索引类型，不同的是：加速查询 + 列值唯一（可以有null）

create unique index index_name on table_name(column_name);

• 全文索引
  全文索引主要关联在数据类型为char、varchar和 text的字段上，以便能更快速地查询数据量较大的字符串类型的字段。

create fulltext index index_name on table_name(column_name);

• 组合索引(也称多列索引)
  面试时基本都会问这个
  将几个列作为一条索引进行检索，使用最左匹配原则。

create index index_name on table_name(column_name,column_name,...);

• 查看索引

show index from table_name;

• 删除索引

drop index index_name on table_name;

下面就使用慢查询explain对比下效率：
测试表：

这个表有几千条数据
执行一个不加索引的普通查询：

explain select * from address where name="城关镇";

查询结果：

添加一个普通索引：

create index addr_name on address(name);

查看创建结果：

show index from address;

再做一次相同的查询：

explain select * from address where name="城关镇";

结果：

看到结果了吧，速度提高了几十倍。

索引使用的注意事项(重点)

1.对于使用 like 的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能会被使用
比如：

explain select * from address where name like "%城"

索引没有被使用到;

explain select * from address where name like "城%";

索引被使用到。

2.如果列是索引，column_name is null 将使用索引。

explain select * from address where name is null;

3.如果列是字符型，传入的是数字，则不加上==""==不会使用索引。

4.当使用组合索引的时候，MySQL采用最左匹配原则。
什么是最左匹配原则呢？当创建的组合索引为table_name(A,B,C)，在进行查询的时候，查询条件中有如下组合：AB、BC、BA、AC、CA、ABC等这些组合中只用出现A字段的查询才会用到索引。
证明：
创建组合索引：

create index addr_con on address(code,name,parent);

• where code and name

explain select * from address where code="410923103000" and name="城关镇";

结论：使用到索引。

• where name and code

explain select * from address where name="城关镇" and code="410923103000";

结论：使用到索引。

• where name and parent

explain select * from address where name="城关镇" and parent="410923103000";

结论：未使用到索引。
最左匹配原则
组合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果只知道姓，电话簿将非常有用；如果不知道姓和名，电话簿则更为有用，但如果只知道名不知道姓，电话簿将没有用处。

所以说创建组合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，组合索引非常有用；仅对后面的任意列执行搜索时，组合索引则没有用处。

5.多个单列索引
多个单列索引在多条件查询时优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引全用上！ 但多个单列索引底层会建立多个B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引！