mysql - 索引

索引的分类

主键索引、唯一索引、普通索引、组合索引、以及全文索引；

主键索引

非空唯一索引，一个表只有一个主键索引；在 innodb 中，主键索引的B+树包含表数据信息；

PRIMARY KEY(key)

唯一索引

不可以出现相同的值，可以有NULL值；

UNIQUE(key)

普通索引

允许出现相同的索引内容；

INDEX(key)

KEY(key[,...])

组合索引

对表上的多个列进行索引

INDEX idx(key1,key2[,...]);
UNIQUE(key1,key2[,...]);
PRIMARY KEY(key1,key2[,...]);

全文索引

将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术；关键词 FULLTEXT;
在短字符串中用 LIKE % ；在全文索引中用 match 和 against ；

主键选择

innodb 中表是索引组织表，每张表有且仅有一个主键；
1. 如果显示设置 PRIMARY KEY ，则该设置的key为该表的主键；
2. 如果没有显示设置，则从非空唯一索引中选择；
    2.1、只有一个非空唯一索引，则选择该索引为主键；
    2.2、有多个非空唯一索引，则选择声明的第一个为主键；
3. 没有非空唯一索引，则自动生成一个 6 字节的 _rowid 作为主键；

索引实现

存储结构

innodb存储引擎使用的是段区页的存储模式，如下

 其中页是磁盘管理的最小单位，默认16k，区的大小为1M

每个页都是一个B+树的节点，如下，非叶子节点存放的是索引信息，每个页大小16kb，假设key和指针占用16字节，叶子节点占用100字节，则3层的B+树可以存放 16kb/16 * 16kb/16 * 160 = 1.6亿条，可以看出，B+树的高度一般在2-4层

聚集索引

是一颗B+树，按照主键构建的B+树，叶子节点存放数据页，数据也是索引的一部分

如上图，如要获取主键id从18-40的数据的B+树也就是磁盘的访问遍历顺序

辅助索引

不是主键创建的索引都是辅助索引，辅助索引也是一棵B+树，其叶子节点不包含行记录的所有数据，只包含聚集索引的key，如下图，假设查找33，则其对应的主键的key是47，也就是要到聚集索引中查找key为47的值。

覆盖索引

对于在辅助索引就能找到的数据，就不需要再到聚集索引中查找，因为辅助索引的树的高度一般是比聚集索引的树低，减少了IO的访问次数。

比如，表1中age是主键

age，name 是组合索引

当查找age=10时，就会使用辅助索引直接查找，而不使用age索引。

组合索引最左匹配原则

对于组合索引，从左到右依次匹配，遇到 > < between like 就停止匹配；
 

索引失效

1、select ... where A and B 若 A 和 B 中有一个不包含索引，则索引失效；
2、索引字段参与运算，则索引失效；例如： from_unixtime(idx) = '2021-04-30';
3、索引字段发生隐式转换，则索引失效；例如： '1' 隐式转换为 1 ；
4、LIKE 模糊查询，通配符 % 开头，则索引失效；

例如： select * from user where name like'%ark';
5、在索引字段上使用 NOT <> != 索引失效；如果判断 id <> 0 则修改为 idx > 0 or idx < 0 ；
6、组合索引中，没使用第一列索引，索引失效；