mysql 存储引擎InnoDB（二）索引

mysql索引-进阶篇（为查询创建索引）

简介：
索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要。MySQL只能高效地使用索引的最左前缀列。下面会有详细的介绍。
大家都知道索引能让服务器快速定位到表的指定位置，但这不是索引的唯一作用。使用索引有如下三大优点：

1. 索引大大减少了服务器需要扫描的数据量。
2. 索引可以帮助服务器避免排序和临时表。
3. 索引可以讲随机I/O变为顺序IO。

一、索引类型

1. B+树索引（btree索引)
B+树索引是最为常用的，也是最有效的索引。B+树最早是从平衡二叉树演化过来的，但不是二叉树。B+树通常意味着所有的值都是按顺序存储的，并且每个叶子页到根的距离相同。
注意：B+树索引不能找到一个给定值的具体行，只能找到被查找数据行所在的页。数据库把页读到内存，在内存中搜索需要查找的数据。

下图展示了b+树索引的抽象表示（图片来源：高性能mysql第三版）：

B+树索引分为两种：

• 聚集索引
  按照每张表的主键构造一颗B+树，叶子节点存放整张表的行记录数据，也将聚集索引的叶子节点成为数据页。
• 辅助索引
  辅助索引也称非聚集索引，叶子节点不包含行记录的全部数据，每个节点包含一个书签（bookmark）,该书签存储了相应行数据的聚集索引键。

2. hash索引
InnoDB支持的hash索引是自适应的(自适应哈希索引,Adaptive Hash Index, AHI)，InnoDB会根据表的使用情况，自动为表生成hash索引，不能人为干预。
hash索引只能用于等值查询，例如：select x from xxxx where xx = xxx, 其他查询不支持（比如：where xx > xxx）

# 查看当前hash索引使用情况
show engine innodb status
# 查看AHI是否启用 默认启用ON,如若想关闭，将ON改为OFF
show variables like 'innodb_adaptive_hash_index'

3. 全文索引
全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。MySQL5.6以后，innodb支持全文索引
在相同列上同时创建全文索引和B+树索引不会有冲突，适用于Match Against操作，而不是普通的where条件操作。

使用全文索引

    # 创建文章表，指定使用InnoDB引擎，title、content添加全文索引
    CREATE TABLE articles (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        content TEXT,
        FULLTEXT (title,content)
    ) ENGINE=InnoDB CHARACTER SET utf8mb4;

    # 插入十万条数据（测试全文索引查询效率，多插点数据。这里使用存储函数）
    # 对存储过程不熟悉的，可以看下我另一篇博客
    # https://blog.csdn.net/hhy107107/article/details/81269946
    delimiter ;;
    CREATE procedure insert_more ()
    BEGIN
    DECLARE i int DEFAULT 1;
    WHILE i < 100000
    DO
    INSERT INTO articles(id, title, content) values (null, 'title', REPEAT('content',7000));
    SET i = i + 1;
    END WHILE;
    COMMIT;
    END;;
    delimiter ;
    
    # 执行插入
    call insert_more
    
    # 随便往其中一条记录的content字段的内容中加测试两个字

    # 普通查询，不走索引
    [SQL] SELECT * FROM articles WHERE content like '%测试%'
    受影响的行: 0
    时间: 44.967s
    
    # 使用全文索引
    [SQL] SELECT * FROM articles WHERE MATCH (title,content) AGAINST ('测试' IN boolean MODE);
    受影响的行: 0
    时间: 0.195s

二、组合索引

• 组合索引 一个索引包含多个列

现有一个people表, 有一个组合索引，包含三个列

create table people (
    last_name varchar(50) not null,
    first_name varchar(50) not null,
    dob date not null,
    gender enum('m', 'f') not null,
    key(last_name, first_name, dob)
)

上表建的索引，对如下类型的查询有效

1. 全值匹配
   可以用于查找姓xx,名xx,出生于xx的人
2. 匹配最左前缀
   可以用于查找姓xx的人，即只使用索引的第一列
3. 匹配列前缀
   可以匹配某一列的值的开头部分。例如可以查找姓氏为X头的人
4. 匹配范围值
   可以查找姓X和XX之间的人
5. 精确匹配某一列并范围匹配另外一列
   可以匹配姓X名字X开头的人，即last_name 全匹配，first_name 范围匹配
6. 只访问索引的查询
   查询只需要访问索引，无需访问数据行

索引查询的限制

1. 如果不是从索引的最左列开始查找，不能使用索引。
   例如，上表不能用索引查询first_name为x的人，也不能查出生于X的人。因为这两列不是最左数据列。也无法查询姓氏以X结尾的人。
2. 不能跳列索引
   也就是说不能查询姓X，出生于XX的人，如果不指定名，只能索引姓，不能索引出生日期。
3. 如果查询的某个列是范围查询，其右边的列无法索引。
   例如，where last_name = 'x' and first_name like 'J%' and dob = 'xx' 这个查询，只能索引last_name和first_name,不能索引dob

三、其他

1.索引设计原则

1. 最适合建索引的列是where子句后面的列和连接子句中指定的列。
2. 使用唯一索引。考虑某列中值的分布。对于唯一值的列，索引效果最好，具有多个重复值的列，索引效果最差。比如记录性别的列，此列值只有‘M’，‘F’，对此列索引没多大用，不管搜哪个，都回得出大约一半的行。
3. 使用短索引。例如有个char(200)的列，列值前10的值多数都是唯一的，那么就不要对整列进行索引，只对列的前10个字符索引

2.不能使用索引的情况

1. 如果mysql查询的列不是独立的，不能使用索引。索引列不能是表达式的一部分，也不能是函数的参数。
   例如：select user_id from user where user_id + 1 = 5;

3.索引提示
mysql 支持索引提示（index hint），显式的告诉优化器使用哪个索引。
当某条sql语句可以选择的索引非常多，优化器选择执行计划的时间开销可能会比较大。这时候，我们可以强制让优化器不进行执行路径的成本分析，直接使用指定的索引进行查询

# 指定使用firstName索引 firstName是索引名称
SELECT * FROM `user` force index (firstName) WHERE last_name = '姓氏' and first_name = '名字';

# 使用explain关键字查看执行计划  结果中，possible_keys为可选择的索引， key表示当前使用的索引
explain SELECT * FROM `user` force index (firstName) WHERE last_name = '姓氏' and first_name = '名字'; 

---

Mysql InnoDB介绍
https://blog.csdn.net/hhy107107/article/details/82703926

MySQL 存储过程
https://blog.csdn.net/hhy107107/article/details/81269946

---

[1]姜承尧 .MySQL技术内幕：InnoDB存储引擎 机械工业出版社
[2]高性能MySQL 第三版：电子工业出版社