MySQL索引

索引 

1、索引概述

索引是帮助MySQL高效获取数据的数据结构。

2、优劣势

优势：

（1）提高数据检索的效率，降低数据库的IO成本。

（2）通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势：

（1）索引占用空间。索引也是一张表，表中记录主键和索引字段，指向实体类的记录。

（2）对表进行更新也需要更新索引，降低了更新表的速度。

3、数据结构

BTREE索引（MySQL默认引擎InnoDB支持），如不指定则使用 B+树（多路搜索树）

在B+树的基础上，增加一个指向相邻叶子节点的链表指针，便于区间查询。

B+树数据全部存放在叶子节点，查询任何数据都要从root走到叶子节点，查询速度稳定。

4、索引分类

 （1）单值索引：一个索引只包含一个列，一个表可以有多个单列索引。

 （2）唯一索引：索引列的值必须唯一，但可以为null。

 （3）复合索引：一个索引包含多个列。

5、创建、查看、删除、添加索引

创建索引：

  主键默认创建一个主索引

create index <索引名称> on <表名称>(<索引列名称>);

查看索引：

show index from <表名>[\G];

删除索引：

drop index <索引名称> on <表名>;

添加索引：

-- 添加主键
alter table <表名称> add primary key (列名称);

-- 唯一索引
alter table <表名称> add unique<索引名称> (列名称);

-- 普通索引
alter table <表名称> add index<索引名称> (列名称)

-- 添加全文索引
alter table <表名称> add fulltextkey (列名称);

6、索引设计原则

 （1）、为查询频次较高、数据量较大的表建立索引。

 （2）、尽量挑选 where 条件字段中常用的字段作为索引。

 （3）、使用唯一索引，区分度越高、使用索引的效率越高。

 （4）、使用适当索引，索引过多维护成本越高，效率降低。

 （5）、使用短索引，索引创建也是存储在硬盘中，短索引能使固定大小的硬盘存放更多索引，

              提高访问索引的IO效率。

 （6）、利用最左前缀。如果索引使用多列，查询从索引的最左列开始，并且不跳过索引中的列。

7、索引失效情况

 使用 explain 查看当前查询语句使用索引情况。

 查看索引使用情况：

show status like 'Handler_read%';

show global status like 'Handler_read%';

（1）违反最左前缀原则。如设置了从左向右ABC三个字段的组合索引，通过B、C、BC查询都不会使用索引，通过AC查询只会使用A索引，不会使用C索引。

 （2）使用表达式。如substring();

 （3）范围查询右边的列不会使用索引。

 （4）字符串不加单引号。

 （5）查询条件使用了or，且条件中有字段没有索引

 （6）以%开头的like模糊查询。尾部模糊查询不会。使用覆盖索引可以解决，（不使用select *，而使用select 索引字段）。

 （7）MySQL评估使用索引比全表扫描更慢，不使用索引。

 （8）is NULL,is NOT NULL 索引可能失效。

 （9）in 索引生效，not in 可能索引失效。