【MySQL】MYSQL索引详解

定义

索引是帮助mysql高效获取数据的一种数据结构

分类

主键索引

对值的要求：

1. 唯一
2. 非空

可在建表时同时创建也可以后续添加：

alter table table_name modify new_id int primary key auto_increment;

删除时注意需要先取消自增：

alter table table_name modify new_id int;
alter table table_name drop primary key;

唯一索引

对值的要求：

1. 允许空字段，空字段可以有多个
2. 非空字段的值须唯一

创建：

alter table table_name add unique uni_index_name(index_column);

删除：

alter table table_name drop index uni_index_name

普通索引

用表中任意列创建，没有限制
创建：

alter table table_name add index index_name(index_column);

删除：

drop index index_name on table_name;

全文索引

在mysql5.6之前的版本仅MYISAM引擎支持，之后的版本MYISAM和INNODB都支持
创建：

alter table table_name add fulltext index index_name(index_column);

联合索引

由多个列构建的索引，对值的要求：

1. 任一列不允许有空值
2. 各列的顺序会影响使用

创建：

alter table table_name add index index_name(index_column_1, index_name_2, ...);

删除：

alter table table_name drop index index_name

使用联合索引查询时注意遵循最左原则：
用于过滤的字段必须是从联合索引最左端的字段起连续的若干字段

原理

哈希索引

只在MEMORY引擎下支持

B+树

MYISAM和INNODB采用的索引原理
特点：

1. 只有叶子节点存储数据
2. 非叶子节点起到索引的作用
3. 所有叶子节点使用链表相连

优点：
4. 减少了磁盘读写操作
数据以块（block）的形式存储在磁盘中， 同样的块可以容纳更多的节点，所以使用B+树可以一次读取更多的节点，也就更容易找到数据
5. 减少了随机i/o次数
随机i/o指时间上连续但空间上不连续的i/o操作。由于B+树所有叶子节点都用链表相连，所以在进行范围查找时找到一个就可以沿链表找到其他数据
6. 查询速度更稳定
B+树只在叶子节点存储数据，而叶子节点高度都是一样的

聚簇索引和非聚簇索引

聚簇索引就是指索引和数据放在一起的索引。对于INNODB引擎，只有主键索引是聚簇索引。因为只有通过主键才能查找到完整的数据，对于由其他非主键列构成的辅助索引，其B+树的叶子节点存储的是主键值，如需访问除辅助索引列外其他列的信息需要按主键值回表查询。
而对于MYISAM引擎，所有的索引都是非聚簇索引，因为MYISAM引擎在创建表时会将数据和索引分开保存。

优化

慢查询

全名为慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句。默认情况下MySQL并不开启慢查询日志，需要手动设置，但非必须情况不建议开启，因为会影响性能。
查看日志存储路径：

查看时间阈值（超过此时间认为语句响应过慢，默认为10秒）：

一般10秒太长了，我们将它设置短一些：

set long_query_time = 3;
# 启动慢查询日志
set global slow_query_log = 'on';

一条sql语句在经过查询优化器的各种优化后会生成一个执行计划，EXPLAIN语句可以帮助我们查看这个执行计划：

explain select id, name from demo where name = 'Alice'\G;

\G表示格式化输出。运行后会显示如下信息：

           id: 1
  select_type: SIMPLE
        table: demo
   partitions: NULL
         type: ALL    # 访问方式，ALL表示遍历
possible_keys: NULL    # 可能用到的索引
          key: NULL    # 实际上使用的索引
      key_len: NULL
          ref: NULL
         rows: 9750125    # 预计要扫描的行数
     filtered: 10.00
        Extra: Using where

索引的正确使用方法

1. 不要在列上使用函数或进行计算
2. 查询条件保证两边类型一致，否则隐式类型转换也会导致索引失效
3. MySQL只能用一个索引，因此为多个列添加索引不能提高查询效率，可使用复合索引
4. 避免嵌套查询与多表查询