MySQL 索引

索引的优缺点

索引的优点如下：

• 索引大大减小了服务器需要扫描的数据量。
• 索引可以帮助服务器避免排序和临时表。
• 索引可以将随机 I/O 变成顺序 I/O。

索引的缺点如下：

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存索引文件。
• 建立索引会占用磁盘空间的索引文件。一般情况这个问题不算严重，但如果你在一个大表上创建了多种组合索引，且伴随大量数据量插入，索引文件大小也会快速膨胀。
• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
• 对于非常小的表，大部分情况下简单的全表扫描更高效。

一. 普通索引

作用：加速查找

# 1.创建表时直接创建索引
create table user(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)        # ix_name 索引名，括号后面指定索引所在的列
);

# 2.创建表后再创建索引
create index ix_name on user(name);
#注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length，例如使用列名称的前10个字符：
create index ix_extra on user(extra(10));

# 3.删除索引
drop ix_name on user;

# 4.查看索引
show index from user;

二. 唯一索引

作用：加速查找，约束列数据不能重复，数据可以为 null

# 1.创建表时直接创建唯一索引
create table user(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)        
);

# 2.创建表后再创建唯一索引
create  unique index ix_name on user(name);

# 3.删除唯一索引
drop unique ix_name on user;

三. 主键索引

作用：加速查找，约束列数据不能重复，数据不能为 null

# 1.创建表时直接创建主键索引
create table user(
    nid int not null auto_increment primary key,    # 指定 nid 为主键索引
    name varchar(32) not null,
    email varchar(64) not null,
    extra text      
);

# 2.创建表后添加主键索引
alter table user add primary key(nid);

# 3.删除主键索引
alter table user drop primary key;

四. 组合索引

作用：多列可以创建一个索引文件

# 1.创建组合索引
create table user(
    nid int not null auto_increment primary key,   
    name varchar(32) not null,
    email varchar(64) not null,
    extra text      
);

# 将 name 和 email 两列组合成一个索引
create index ix_name_email on user(name,email);

# 组合索引遵循最左前缀，即 where条件必须跟 name 列才会使用索引。

覆盖索引

只需要在索引表中就能获取到数据。

合并索引

# 有两个单独的索引，搜索时使用两个索引
create table user(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name(name) ,
    index ix_email(email)
);

select * from user where name = 'klvchen' or email = 'klvchen@126.com'; 

回表查询

比如你创建了 name， age 索引 name_age_index，查询数据时使用了：

select * from table where name ='陈哈哈' and age = 26;

由于附加索引中只有 name 和 age，因此命中索引后，数据库还必须回去聚集索引中查找其他数据，这就是回表，这也是你背的那条：少用 select * 的原因。

索引覆盖

结合回表会更好理解，比如上述 name_age_index 索引，有查询：

select name， age from table where name ='陈哈哈' and age = 26;

此时 select 的字段 name，age 在索引 name_age_index 中都能获取到，所以不需要回表，满足索引覆盖，直接返回索引中的数据，效率高。是 DBA 同学优化时的首选优化方式。

最左前缀原则

B+树的节点存储索引顺序是从左向右存储，在匹配的时候自然也要满足从左向右匹配。

通常我们在建立联合索引的时候，也就是对多个字段建立索引，相信建立过索引的同学们会发现，无论是 Oracle 还是 MySQL 都会让我们选择索引的顺序。

比如我们想在 a，b，c 三个字段上建立一个联合索引，我们可以选择自己想要的优先级，a、b、c，或者是 b、a、c 或者是 c、a、b 等顺序。

为什么数据库会让我们选择字段的顺序呢？不都是三个字段的联合索引么？这里就引出了数据库索引的最左前缀原理。

在我们开发中经常会遇到明明这个字段建了联合索引，但是 SQL 查询该字段时却不会使用索引的问题。

比如索引 abc_index：（a，b，c）是 a，b，c 三个字段的联合索引，下列 sql 执行时都无法命中索引 abc_index 的。

select * from table where c = '1';
select * from table where b ='1' and c ='2';

以下三种情况却会走索引：

select * from table where a = '1';
select * from table where a = '1' and b = '2';
select * from table where a = '1' and b = '2'  and c='3';

索引 abc_index：（a，b，c），只会在（a）、（a，b）、（a，b，c）三种类型的查询中使用。
其实这里说的有一点歧义，其实（a，c）也会走，但是只走 a 字段索引，不会走 c 字段。
另外还有一个特殊情况说明下，下面这种类型的也只会有 a 与 b 走索引，c 不会走。

select * from table where a = '1' and b > '2'  and c='3';

像上面这种类型的 sql 语句，在 a、b 走完索引后，c 已经是无序了，所以 c 就没法走索引，优化器会认为还不如全表扫描 c 字段来的快。

最左前缀：顾名思义，就是最左优先，上例中我们创建了 a_b_c 多列索引，相当于创建了（a）单列索引，（a，b）组合索引以及（a，b，c）组合索引。

因此，在创建多列索引时，要根据业务需求，where 子句中使用最频繁的一列放在最左边。

索引下推优化

还是索引 name_age_index，有如下 sql：

select * from table where name like '陈%' and age > 26；

该语句有两种执行可能：
命中 name_age_index 联合索引，查询所有满足 name 以"陈"开头的数据， 然后回表查询所有满足的行。

命中 name_age_index 联合索引，查询所有满足 name 以"陈"开头的数据，然后顺便筛出 age>20 的索引，再回表查询全行数据。

显然第 2 种方式回表查询的行数较少，I/O 次数也会减少，这就是索引下推。所以不是所有 like 都不会命中索引。

使用索引注意事项

• 避免使用select *
• count(1)或count(列) 代替 count(*)
• 创建表时尽量时 char 代替 varchar
• 表的字段顺序固定长度的字段优先
• 组合索引代替多个单列索引（经常使用多个条件查询时）
• 尽量使用短索引;
  例如，如果有一个 char（255）的列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和 I/O 操作。
• 使用连接（JOIN）来代替子查询(Sub-Queries)
• 连表时注意条件类型需一致
• 索引散列值（重复少）不适合建索引，例：性别不适合
• 索引不会包含有 null 值的列;
  只要列中包含有 null 值都将不会被包含在索引中，复合索引中只要有一列含有 null 值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时建议不要让字段的默认值为 null。
• 索引列排序;
  查询只使用一个索引，因此如果 where 子句中已经使用了索引的话，那么 order by 中的列是不会使用索引的。
• like 语句操作;
  一般情况下不推荐使用 like 操作，如果非使用不可，如何使用也是一个问题。like “%陈%” 不会使用索引而 like “陈%”可以使用索引。
• 不要在列上进行运算
  这将导致索引失效而进行全表扫描，例如：

   SELECT * FROM table_name WHERE YEAR(column_name)<2017;

• 不使用 not in 和 <> 操作;
  这不属于支持的范围查询条件，不会使用索引。