MySQL: 索引的使用，分类及模型

索引的使用

创建索引，优化query

SELECT [column1],[column2],…. FROM [TABLE_NAME] ORDER BY [sort];

创建sort字段的索引

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];

创建(columnX, sort)字段的联合索引

索引的分类

分类方式1：普通索引，唯一性索引，全文索引，空间索引

普通索引

由KEY或INDEX定义的索引，可以创建在任何数据类型，数据列可以有重复值，可以有null

唯一性索引

由UNIQUE定义的索引，数据列不允许重复，允许有null值

全文索引
由FULLTEXT定义的索引
InnoDB在MySQL5.6以后支持全文索引，只能创建在CHAR、VARCHAR或者TEXT类型字段的索引；MyISAM可以创建BLOB列的索引

空间索引
由SPATIAL定义的索引，只能创建在空间数据类型的字段上，共四种：GEOMETRY、POINT、LINESTRING、POLYGON。空间索引所在字段的值不能为空，表的存储引擎为MyISAM。

分类方式2：单列索引，多列索引

单列索引
只在一个字段上创建索引

多列索引
在多个字段上创建索引。多列索引中只有查询条件中使用了这些字段的第一个字段多列索引才会被使用。

分类方式3：主键索引，非主键索引

主键索引

数据列不允许有重复值和null值，一个表只能有一个主键；
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

主键索引和非主键索引的区别
假设有如下的建表语句

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

即ID是主键索引，k是非主键索引。

• 如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；* 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表（回到主键索引树搜索）。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

索引的常见模型

哈希表

优点：新数据入库的时候，追加索引很快
缺点：做区间查询的时候比较慢，例如查找id在10-20之间的学生，就必须全部扫一遍
总结：适用于只有等值查询的场景，例如一些 NoSQL 引擎。

有序数组

优点：
查询效率高，在等值查询和范围查询中性能都很好。等值查询直接二分法可以找，范围查询因为索引是有序的所以找起来也比较方便
缺点：
数据更新的时候比较麻烦，在数据中间插入数据就必须挪动后面的所有的元素，成本太高
总结：
适用于静态的存储引擎，例如要保存 2017 年某个城市的所有人口信息，这种一般不会修改的数据。

N叉树

优点：
新数据入库的时候，加入索引比较快，查询效率比较快。
总结：各方面都比较折中，应用比较广泛。

常见数据库引擎的索引模型

InnoDB

InnoDB的索引模型是B+树。先介绍一下B树再介绍B+树。

B树
B树相比平衡二叉树来说更矮胖，从磁盘中查找数据（先读取到内存、后查找）的过程中，可以减少磁盘 IO 的次数，从而提升查找速度，常用于文件系统及数据库。

B树的特点：

• 一个节点的关键字数量=子树数量-1
• 所有叶子节点在同一层
• 所有节点的关键字左小右大
• 一个M阶的B树最多有M个子树，最少有2个子树

B+树

• 关键字和子树数量相同
• 所有数据都包含在叶子节点中，非叶子节点仅用作索引
• 叶子节点用指针连在一起

优点：

• 查询性能稳定，每次都会查到叶子节点
• 叶子节点形成有序链表，范围查询更方便

B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数，同时访问起来比较稳定。

性能优化

性能优化主要考虑避免全表扫描和有效地添加索引

避免全表扫描

1. 尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0

2. 尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描

3. 能用union all就避免使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

例如select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20

4. 能用between就不要用in和not in，否则会导致全表扫描

如：select id from t where num in(1,2,3) 对于连续的数值，可以修改为：select id from t where num between 1 and 3

5. 模糊查询例如select id from t where name like '李%'也会导致全表扫描，尽可能使用全文索引

全文索引比 like + % 快 N 倍，但是可能存在精度问题。InnoDB 5.7.6之后才支持全文索引，MyISAM都支持全文索引。
MySQL 5.7.6之前只支持英文的全文索引；MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中文、日文、韩文分词。

6. 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段

7. 用>=替代>

高效: SELECT * FROM EMP WHERE DEPTNO >=4
低效: SELECT * FROM EMP WHERE DEPTNO >3
两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录，而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。


参考

建立有效的索引

1. 查询频繁的字段适合建立索引；经常排序、分组的字段适合建立索引
2. 更新频繁的字段不适合建立索引
3. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
4. 组合索引里面需要考虑最左前缀匹配原则：mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。