Mysql索引详解

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+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引！