MySQL(九)-索引(一) | HoleLin's Blog

参考文献

高性能MySQL(第三版)
极客时间–MySQL实战45讲
数据库索引设计与优化
MySQL 优化之 index merge(索引合并)

索引

概念

关键字与数据的映射关系称为索引(包含关键字和对应的记录在磁盘中的地址).关键字是从数据当中提取的用于标识、检索数据的特定内容.
索引的出现是为了提高查询的效率;
索引用来快速地寻找那些具有特定值的记录.如果没有索引,一般来说执行查询时遍历整张表.
索引的原理很简单,就是把无序的数据变成有序的查询
- 把创建了索引的列的内容进行排序
- 对排序结果生成倒排表
- 在倒排表内容上拼上数据地址链
- 在查询的时候,先拿到倒排表内容,再取出数据地址链,从而拿到具体数据
索引检索为什么快？
- 关键字相对于数据本身,数据量小
- 关键字是有序的,二分查找可快速确定位置

索引操作

创建索引

在创建表的时候对字段进行指定索引

CREATE TABLE user_index (
	id INT auto_increment PRIMARY KEY,
	first_name VARCHAR ( 16 ),
	last_name VARCHAR ( 16 ),
	uuid VARCHAR ( 20 ) UNIQUE,
	id_card VARCHAR ( 18 ),
	information text 
);

通过alter table 表名 add 索引更改表结构来添加索引

-- 创建一个first_name和last_name的复合索引,并命名为name
alter table user_index add key name (first_name,last_name);
-- 创建一个id_card的唯一索引,默认以字段名作为索引名
alter table user_index add UNIQUE KEY (id_card);
-- 鸡肋,全文索引不支持中文
alter table user_index add FULLTEXT KEY (information);

查看索引

通过desc 表名展示表结构来查看索引信息

mysql> desc user_index;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int         | NO   | PRI | NULL    | auto_increment |
| first_name  | varchar(16) | YES  | MUL | NULL    |                |
| last_name   | varchar(16) | YES  |     | NULL    |                |
| uuid        | varchar(20) | YES  | UNI | NULL    |                |
| id_card     | varchar(18) | YES  | UNI | NULL    |                |
| information | text        | YES  | MUL | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
6 rows in set (0.07 sec)

通过show create table 表名展示建表语句来查看索引信息

mysql> show create table user_index;
+------------+--------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                 |
+------------+--------------------------------------------------------------------------------------------------------------+
| user_index | CREATE TABLE `user_index` (
  `id` int NOT NULL AUTO_INCREMENT,
  `first_name` varchar(16) DEFAULT NULL,
  `last_name` varchar(16) DEFAULT NULL,
  `uuid` varchar(20) DEFAULT NULL,
  `id_card` varchar(18) DEFAULT NULL,
  `information` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  UNIQUE KEY `id_card` (`id_card`),
  KEY `name` (`first_name`,`last_name`),
  FULLTEXT KEY `information` (`information`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)

通过SHOW INDEX FROM table_name\G;

mysql> SHOW INDEX FROM	t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |       97704 | NULL     | NULL   |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

删除索引

通过alter table 表名 drop key 索引名称

1
2
3

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

删除主键索引:alter table 表名 drop primary key(因为主键只有一个).这里值得注意的是,如果主键自增长,那么不能直接执行此操作(自增长依赖于主键索引)

1 2	mysql> alter table user_index drop primary key; 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

若一定需要删除,则需要取消自增长在进行删除

1 2	ALTER TABLE user_index MODIFY id INT; ALTER TABLE user_index DROP PRIMARY KEY;

强制/忽略使用索引

ALTER TABLE table_name ADD INDEX index_name ( index_fields );
-- 强制使用索引
SELECT	* FROM	table_name FORCE INDEX ( index_name ) WHERE  conditions;
-- 忽略使用索引
SELECT	* FROM	table_name IGNORE INDEX ( index_name ) WHERE conditions;

`MySQL8.0`新特性——不可见索引(`Invisible Indexes`)

MySQL8.0开始支持看不见的索引.一个看不见的索引根本不被优化器使用,但是通常是保持正常的.默认情况下索引是可见的.不可见的索引使测试在查询性能上删除索引的效果成为可能,而不需要在需要索引的情况下进行破坏性的更改.

注意：

该特性适用于除主键以外的索引（显式或隐式）默认情况下索引是可见的！
将index设置为invisible,会导致优化器在选择执行计划时,自动忽略该索引,即便使用了FORCE INDEX

1 2	mysql> ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE; --创建不可见索引：k_idx mysql> ALTER TABLE t1 ALTER INDEX i_idx VISIBLE; --修改索引为可见状态

1 2	--查看数据库中有哪些不可见索引： mysql> select table_schema,table_name,index_name,column_name,is_visible from information_schema.statistics where is_visible='no';

索引使用的数据结构

哈希表

通过Hash算法(常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法),将数据库字段数据转换成定长的Hash值,与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞(两个不同关键字的Hash值相同),则在对应Hash键下以链表形式存储.

Hash算法是通过某中确定性的算法(比如MD5,SHA1,SHA2,SHA3)将输入变为输出.相同的输入永远可以得到相同的输出.
哈希表这种结构适用于只有等值查询的场景,Memcached以及其他一些NoSQL引擎;

哈希索引

好处: 因为索引自身只需要存储对应的哈希值,所以索引的结构十分紧凑,这样使得哈希索引查找的速度非常快.
限制:
- 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行(即使用覆盖索引).不过,访问内存中的行的速度很快,所以大部分情况下这一定对性能的影响并不明显.
- 哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序.
- 哈希索引也不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的.
- 哈希索引只支持等值比较查询,包括=,IN(),<=>(注意<>[不等]和<=>[空值等于]是不同的操作),也不支持任何范围查询.

有序数组

有序数组在等值查询和范围查询场景中的性能都非常优秀
有序数组索引只适用于静态存储引擎

二叉搜索树(`Binary Search Tree, BST`)

查询数据的时间复杂度为O(log2n)
二叉搜索树的特点是每个节点左儿子小于父节点,父节点又小于右儿子;
时间复杂度为O(logN)

平衡二叉搜索树(`AVL`)

当数据特殊时会退化为链表,查询数据的时间复杂度会变为O(n),为了解决种问题,人们提出了平衡二叉搜索树(AVL),它在二叉搜索树的基础上增加了约束,每个节点的左子树和右子树的高度差不能超过1,也就是所节点的左子树和右子树任然是平衡二叉树
- 常见的平衡二叉树有平衡二叉搜索树,红黑树,数堆,伸展树.搜索时间复杂度为O(log2n)
- 数据查询的时间主要依赖磁盘I/O的次数,采用二叉树的形式,即使使用平衡二叉搜索树进行改进,树的深度也是O(log2n),当n比较大时,深度也是比较高的.

B树

如果用二叉树作为索引的实现结构,会让树变得很高,增加磁盘的I/O次数,影响查询的时间,因此一个节点就不能只有2个子节点,应该允许有M个子节点(M>2).
- B树的出现就是为了解决这个问题,B树全称Balance Tree也就是平衡的多路搜索树,它的高度小于平衡二叉树的高度.
- 在文件系统和数据库系统中的索引经常采用B树来实现.
  - B树作为平衡的多路搜索树,它的每个节点最多可以包括M个节点,M称为B树的阶,同时每个磁盘中包括了关键字和子节点的指针.如果一个磁盘块中包括了x个关键字,那么指针数就是x+1.
  - 一个M阶的B树(M>2)有以下的特性:
    - 根节点的儿子树的范围是[2,M].
    - 每个中间节点包含k-1个关键字和k个孩子,孩子的数量=关键字的数量+1,k的取值范围为[ceil(M/2),M].
    - 叶子节点包括k-1关键字(叶子节点没有孩子),k的取值范围为[ceil(M/2),M].
    - 假设中间节点的节点关键字为Key[1],Key[2]…Key[k-1],且关键字按照升序排序,Key[i]<Key[i+1].此时k-1个关键字相当于划分了k个范围,也就是对应着k个指针,即为P[1],P[2]…P[k],其中P[1]指向关键字小于Key[1]的子树,P[i]指向关键字属于(Key[i-1],Key[i])的子树,P[k]指向关键字大于Key[k-1]的子树.
    - 所有叶子节点位于同一层.

B+树

图片来源自小林Coding

B+树性质
- n棵子树的节点包含n个关键字,不用来保存数据而是保存数据的索引.
- 所有的叶子结点中包含了全部关键字的信息及指向含这些关键字记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接.
- 所有的非叶子结点可以看成是索引部分,结点中仅含其子树中的最大(或最小)关键字.
- B+ 树中,数据对象的插入和删除仅在叶节点上进行.
- B+树有2个头指针,一个是树的根节点,一个是最小关键码的叶节点.
查询方式:
- 主键索引区:PI(关联保存的时数据的地址)按主键查询,
- 普通索引区:si(关联的id的地址,然后再到达上面的地址).所以按主键查询,速度最快
- B+树基于B树做出改进,B+树和B树差异在于以下几点:
  - 有k个孩子的节点就有k个关键字,也就是孩子数量=关键字数量,而B树中,孩子数量=关键字数量+1.
  - 非叶子节点的关键字也会同时存在在子节点中,并且是在子节点中所有关键字的最大(或最小).
  - 非叶子节点仅用于索引,不保存数据记录,跟记录有关的信息都放到叶子节点中.而B树中,非叶子节点既保存索引,也保存数据.
  - 所有关键字都在叶子节点出现,叶子节点构成一个有序链表,而且叶子节点本身按照关键字的大小顺序链接.
B+树相对于B树的优点
- 更高的查询性能:B+树的非叶子节点只存储索引信息,而不存储具体数据,因此可以存储更多的索引信息.这样可以减少磁盘IO次数,提高查询性能.
- 更快的范围查询:B+树的所有叶子节点都是通过指针连接起来的一个链表,因此可以很快地进行范围查询,而B树需要在叶子节点中查找,效率较低.
- 更高的磁盘空间利用率:B+树的叶子节点只存储数据,而不存储索引信息,因此可以存储更多的数据.同时,B+树的非叶子节点只存储索引信息,也可以存储更多的索引信息.这样可以提高磁盘空间利用率.
- 更方便的数据遍历:B+树的所有叶子节点都是通过指针连接起来的一个链表,因此可以很方便地遍历数据,而B树需要在叶子节点中查找,效率较低.
B+树和B树的区别
- B树和B+树是两种不同的数据结构.
- B树是一种平衡的多路搜索树,由Rudolf Bayer和Edward McCreight于1972年提出,用于实现在有序数据集合上的查找、插入、删除等操作.每个节点可以存储多个键值和对应的子节点指针,可以支持范围查询和排序操作,但是由于非叶子节点也包含键值信息,因此B树的节点比较大,不太适合内存中的操作.
- B+树是B树的一种变种,由Brown和其他人于1972年发明,也是一种平衡的多路搜索树.与B树不同的是,B+树的所有数据都存储在叶子节点中,非叶子节点只包含键值信息和指向子节点的指针,因此B+树比B树更加扁平,可以存储更多的数据项,适合于磁盘上的存储和查询操作.在B+树中,所有叶子节点之间通过指针连接,可以支持范围查询和排序操作.
- 需要注意的是,B树和B+树虽然有一些相似的地方,但是它们的结构和实现方式是不同的,不能互相混淆.B+树通常是指实现在磁盘上的索引结构,而B树则更适合于内存中的数据结构.
Hash索引和B+树实现原理
- Hash索引底层就是Hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据
- B+树底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据.
- Hash索引和B+树区别
- hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询.因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.

hash索引不支持使用索引进行排序(ORDER BY);
- hash索引不支持模糊查询以及复合索引的最左前缀匹配.原理也是因为hash函数的不可预测.AAAA和AAAAB的索引没有相关性.
- hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询.
- hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低.
- 而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围.

可以使用B-Tree索引的查询类型

全值匹配
- 全值匹配指的是和索引中的所有列进行匹配.如索引Index(code,name),可以用于查询code为1001,姓名为张三的人
匹配最左前缀
- 可以用于查询code为1002的人,即使用索引的第一列
匹配列前缀
- 可以匹配某一列的值的开头部分,如查询code为10为前缀的所有人.
匹配范围值
- 可以用于查询code为10001和20001之间的人
精确匹配某一列并范围匹配另外一列
- 可以用于查询code为10001,name以张开头人
只访问索引的查询
- 即查询只需要访问索引,而无须访问数据行,即覆盖索引.

索引分类

从功能逻辑上来看

索引主要分为4种,普通索引,唯一索引,主键索引,全文索引.前三种索引都是一类索引,只不过对数据的约束性逐渐提升.

普通索引

普通索引是基础索引,没有任何约束,主要用于提高查询效率.
- 普通索引的查询过程: 查找到满足条件的第一个记录后,需要查找下一个记录,直到碰到第一个不满足条件的记录;

唯一索引

索引列的值必须唯一,但允许有空值

唯一索引就是在普通索引的基础上增加了数据唯一性的约束,一张表中可以有多个唯一索引.

唯一索引的查询过程: 对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索;

示例

CREATE TABLE user (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    age int(3),
    name VARCHAR(200), 
    id_card varchar(32),
    PRIMARY KEY(id), 
    -- 单值索引   
    KEY (name),
    -- 唯一索引
    UNIQUE KEY (id_card)
);

-- 单独创建索引
CREATE INDEX idx_user_id_card ON `user`(id_card);

主键索引

设定为主键后数据库会自动建立索引,Innodb为聚簇索引,一个表只能有一个主键索引,但是可以有多个唯一索引.

主键索引在唯一索引的基础上增加了不为空的约束,即NOT NULL+UNIQUE,一张表里面最多只能有一个主键索引.

示例

-- 和表一起创建
CREATE TABLE `user` (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    PRIMARY KEY(id)
);

--单独创建主键索引
ALTER TABLE `user` ADD PRIMARY KEY user(id);

--删除主键索引
ALTER TABLE `user` DROP PRIMARY KEY;

前缀索引

前缀索引是一种能使索引更小、更快的有效办法
对过长的字段,建立前缀索引
语法:alter table table_name add index index_name(field(10)),使用字段值的前10个字符建立索引,默认是使用字段的全部内容建立索引.
- 使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本.
前提:前缀的标识度高.比如密码就适合建立前缀索引,因为密码几乎各不相同.
实操的难度:在于前缀截取的长度.
- 我们可以利用select count(*)/count(distinct left(password,prefixLen));,通过从调整prefixLen的值(从1自增)查看不同前缀长度的一个平均匹配度,接近1时就可以了(表示一个密码的前prefixLen个字符几乎能确定唯一一条记录)

前缀索引影响

MySQL无法使用前缀索引做ORDER BY和GROUP BY操作,也无法使用前缀索引做覆盖扫描

使用前缀索引的方式

使用倒序存储
- 若字符串前缀没有较大的区分度,则可以使用倒序的方式来存储,查询的时候按正序的方式来查询.需要进行转换一下.
使用hash字段
- 添加一个字段来保存hash码(crc32),同时在这个字段上创建索引
倒序存储和使用hash字段这两个方法的异同点
- 相同点
  - 是都不支持查询.倒序存储的字段上创建的索引是按照倒序字符串的方式排序,无法查询出字段在[x,y]区间的数据,hash字段的方式只能支持等值查询.
- 不同点
  - 从占用的额外空间来看,倒序存储方式在主键索引上,不会消耗额外的存储空间,而hash字段方法需要增加一个字段.倒序存储方式使用4个字节的前缀长度应该是不够的,如果再长一点,这个消耗跟额外这个hash字段也差不多抵消.
  - 在CPU消耗方面,倒序方式每次写和读的时候,都需要额外调用一次reverse函数,而hash字段的方式需要额外调用一次crc32()函数.如果只从两个函数的计算复杂度来看的话,reverse函数额外消耗的CPU资源会更小些.
  - 从查询效率看,使用hash字段方式的查询性能相对更稳定一些.因为crc32算出来的值虽然有冲突的概率,但是概率很小,可以认为每次查询的平均扫描函数接近1.

全文索引(`fulltext key`)

MySQL自带的全文索引只能用于MyISAM,并且只能对英文进行全文检索 (基本不用)

按照物理实现方式划分

聚簇索引

聚簇索引并不是一个单独的索引类型,而是一种数据存储方式.
具体的细节依赖于其实现方式,但InnoDB的聚簇索引实际上同一个结构中保存了B树索引和数据行.当表有聚簇索引时,它的数据行实际上存放在索引的叶子页(leaf page)中.即聚簇表示数据行和相邻的键值紧凑地存储在一起,因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引(不过,覆盖索引可以模拟多个聚簇索引的情况)
InnoDB通过主键聚簇数据,即主键列为聚簇索引.

非聚簇索引(二级索引/辅助索引)

将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,MyISAM通过key_buffer把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,然后通过索引找到磁盘相应数据,这也就是为什么索引不在key buffer命中时,速度慢的原因;

Innodb中,在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找,非聚簇索引都是辅助索引,像复合索引、前缀索引、唯一索引,辅助索引叶子节点存储的不再是行的物理位置,而是主键值.
二级索引叶子节点保存的不是指向行的物理位置的指针,而是行的主键值.

聚簇索引与非聚簇索引的区别

聚簇索引的叶子节点存储的就是数据记录,非聚簇索引的叶子结点存储的是数据的位置,非聚簇索引不会影响数据表的物理存储顺序.
一个表只能有一个聚簇索引,因为只能有一种排序存储的方式,但可以有多个非聚簇索引,也就是多个索引目录提供数据检索.
使用聚簇索引的时候,数据查询效率高,但是对数据进行插入,删除,更新等操作,效率会比非聚簇索引低.

按照字段个数划分

又可以分为单值索引和联合索引

单值索引

即一个索引只包含单个列,一个表可以有多个单列索引

示例

CREATE TABLE `user` (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    age int(3),
    name VARCHAR(200), 
    PRIMARY KEY(id), 
    -- 单值索引   
    KEY (name)   
);

-- 单独创建单值索引
CREATE INDEX idx_user_name ON `user`(name);

复合索引

即一个索引包含多个列,存在最左匹配原则

示例

CREATE TABLE `user` (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    age int(3),
    name VARCHAR(200), 
    department_id varchar(100),
    id_card varchar(32),
    PRIMARY KEY(id), 
    -- 单值索引   
    KEY (name),
    -- 唯一索引
    UNIQUE KEY (id_card),
    -- 复合索引
    KEY (name,age,department_id) 
);

--单独创建复合索引
CREATE INDEX idx_student ON `user`(name,age,department_id);

通过创建组合索引之后, 组合索引是有顺序的, 其中组合索引的排序规则:
- 优先根据第一索引进行排序
- 如果第一索引的值相同, 才会根据第二索引进行排序
- 如果第一索引和第二索引都是相同的, 才会根据第三索引进行数据排序

索引创建的选择

创建索引和使用索引的三个原则

单行访问是很慢的.特别是在机械硬盘存储中(SSD的随机I/O要快很多,变更这一点任然成立).如果服务器从存储中读取一个数据块只是为了获取其中一行,那么就浪费了很多工作.最好读取的块中能包含尽可能多所需要的行.使用索引可以创建位置引用以提升效率.
按顺序访问范围数据是很快的,这有两个原因.第一,顺序I/O不需要多次磁盘寻道,所以比随机I/O要快很多.第二,如果服务器能够按需要顺序读取数据,那么就不在需要额外的排序操作,并且GROUP BY也无须再做排序和将行按组进行聚合计算了.
索引覆盖查询是很快的.如果一个索引包含了查询需要的所有列,那么存储引擎就不需要在回表查找行.

不需要创建索引的情况

WHERE条件(包括GROUP BY,ORDER BY)里用不到的字段不需要创建索引.
表记录太少,比如小于1000个
不为离散度低的列创建索引
- 列的离散度公式: COUNT(DISTINCT(column_name)) / COUNT(*) 列的不重复值的个数与所有数据行的比例.简而言之,如果列的重复值越多,列的离散度越低.重复值越少,离散度就越高.
- 字段中如果有大量重复数据,也不用创建索引,如性别字段
  
  如性别、支付状态等状态值字段往往只有极少的几种取值可能,这种字段即使建立索引,也往往利用不上.这是因为,一个状态值可能匹配大量的记录,这种情况MySQL会认为利用索引比全表扫描的效率低,从而弃用索引.索引是随机访问磁盘,而全表扫描是顺序访问磁盘
频繁更新的值,不要作为主键或索引
随机无序的值,不建议作为索引,例如身份证、UUID

索引选择性

不重复的索引值(也称为基数,cardinality)和数据表的记录总数(#T)的比值,范围从1/#T到1之间.索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤更多的行.唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的.

覆盖索引

如果要查询的字段都建立过索引,那么引擎会直接在索引表中查询而不会访问原始数据(否则只要有一个字段没有建立索引就会做全表扫描),这叫覆盖索引.因此我们需要尽可能的在select后只写必要的查询字段,以增加索引覆盖的几率.
这里值得注意的是不要想着为每个字段建立索引,因为优先使用索引的优势就在于其体积小.
覆盖索引的好处
- 索引条目通常远小于数据行大小,所以如果只需要读取索引,那么MySQL机会极大地减少数据访问量,这对缓存的负载非常重要,因为这种情况下响应时间大部分花费在数据拷贝上.
- 索引是按照列值顺序存储的(至少在单个页内是如此),所以对I/O密级性的范围查询会比随机从磁盘读取每一行数据的I/O要少的多.
- 由于InnoDB的聚簇索引,覆盖索引对于InnoDB特别有用,InnoDB的二级索引在叶子节点中保存了行的主键值,所有如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询.
不是所有类型的索引都可以成为覆盖索引.覆盖索引必须要存储索引列的值,而哈希索引,空间索引和全文索引等都不存储索引列的值,所有MySQL只能使用B树索引做覆盖索引.
当做索引覆盖查询是,在EXPLAIN的Extra列可以看到"Using index"的值.
索引覆盖查询还有很多陷阱可能导致无法实现优化.MySQL查询优化器会在执行前判断是否有一个索引能进行覆盖.假设索引覆盖了WHERE条件的字段,但不是整个查询涉及的字段,如果条件为假,MySQL5.5和更早的版本也总是会回表获取数据行,尽管并不需要这一行且最终会被过滤掉.

延迟关联

EXPLAN SELECT * FROM products WHERE actor='SEAN CARREY' AND title like '%APOLLO%';
==>
-- 先将索引扩展至覆盖三个数据量(actor,title,prod_id),然后改写查询
EXPLAN SELECT * FROM products  JOIN (SELECT prod_id FROM products WHERE actor='SEAN CARREY' AND title like '%APOLLO%') AS t1 ON (t1.prod_id = products.prod_id);

这种方式叫做延迟关联(deferred join),因为延迟了对列的访问.在查询的第一阶段MySQL可以使用覆盖索引,在FROM子句的子查询中找到匹配的prod_id,然后根据这些prod_id值在外层查询匹配获取需要的所有列值.

索引优化

单表查询优化(10个)

全值匹配很快捷

--建立复合索引(age, deptId, name)
CREATE INDEX idx_emp_ade ON t_emp(age, deptId, NAME);

--查找
EXPLAIN SELECT empno FROM t_emp WHERE age = 90;
EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1;
EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1 AND name = '风清扬';

--和上一条SQL语句中WHERE后字段的顺序不同,但是不影响查询结果
EXPLAIN SELECT empno FROM t_emp WHERE deptId = 1 AND name = '风清扬' AND age = 90;

全值匹配很快捷指的是,查询的字段按照顺序在索引中都可以匹配到

最佳左前缀法则

--先删除之前创建的单值索引
DROP INDEX idx_dept_id ON t_emp; 

--查询,未按照最佳左前缀法则
EXPLAIN SELECT empno FROM t_emp WHERE deptId = 1;
EXPLAIN SELECT empno FROM t_emp WHERE deptId = 1 AND name = '风清扬';

--查询,部分按照最佳左前缀法则(age字段和复合索引匹配,但name没有)
EXPLAIN SELECT empno FROM t_emp WHERE  age = 90 AND name = '风清扬';

--查询,完全按照最佳左前缀法则
EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1;
EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1 AND name = '风清扬';

过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用
在建立联合索引的时候,如何安排索引内的字段顺序?
- 第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用.

索引下推

-- 找出名字第一个字为陈,而且年龄是21岁且性别为男的记录 name和age建立了索引
select * from user where name like '陈%' and age=21 and ismale=1; 
-- 四次回表
-- name  age  ID      ID 	name	age	ismale
-- 陈一		21 	 3 ----> 3   陈一  21    1
-- 陈二		20	 3 ----> 3   陈二  20    1
-- 陈三		10	 3 ----> 3   陈三  10    2
-- 陈四		21	 3 ----> 3   陈四  21    1

-- 两次回表
-- name  age  ID      ID 	name	age	ismale
-- 陈一		21	 3 ----> 3   陈一  21    1
-- 陈二		20	 3 --X-> 3   陈二  20    1
-- 陈三		10	 3 --X-> 3   陈三  10    2
-- 陈四		21	 3 ----> 3   陈四  21    1

在MySQL5.6之前,只能从满足条件的第一条开始一个一个的回表,在主键索引上找出数据行,再对比字段值.
- 先只根据name字段过滤出满足条件的记录,然后根据ID(主键)来回表查询并过滤;
在MySQL 5.6及之后的版本中,如果查询语句中既有索引列又有非索引列的条件,优化器会将非索引列的条件筛选放到索引层之后进行,这个过程就是索引下推.这样可以减少读取的磁盘块数,提高查询效率.
- 需要注意的是,索引下推只适用于一些特定的查询场景,例如同时使用索引列和非索引列的条件查询,或者使用多个索引进行联合查询的情况.在其他情况下,索引下推可能会导致查询效率降低,因此需要根据具体情况进行选择和调整.
索引下推, 需要满足的条件
- 查询的字段有一些是不在二级索引树上可以找到
- 查询的过滤条件是在二级索引树上可以找到
ICP的使用限制如下：
- 只能用于InnoDB和MyISAM.
- 适用于range、ref、eq_ref和ref_or_null访问方式,并且需要回表进行访问.
- 适用于二级索引.
- 不适用于虚拟字段的二级索引

索引列上不计算

不在索引列上做任何操作(计算、函数、(自动 or 手动)类型转换),可能会导致索引失效而转向全表扫描

--直接查询
EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1 AND NAME = '风清扬';

--使用MySQL函数查询
EXPLAIN SELECT empno FROM t_emp WHERE LEFT(age,2) = 90 AND deptId = 1 AND name = '风清扬';

范围之后全失效

--范围查询
EXPLAIN SELECT empno FROM t_emp WHERE age > 50 AND deptId = 1 AND name = '风清扬';
EXPLAIN SELECT empno FROM t_emp WHERE age = 50 AND deptId > 1 AND NAME = '风清扬';

--未使用范围查询
EXPLAIN SELECT empno FROM t_emp WHERE age = 50 AND deptId = 1 AND name = '风清扬';

建议:将可能做范围查询的字段的索引顺序放在最后
结论:使用范围查询后,如果范围内的记录过多,会导致索引失效,因为从自定义索引映射到主键索引需要耗费太多的时间,反而不如全表扫描来得快

覆盖索引多使用

--查询所有字段
EXPLAIN SELECT * FROM t_dept WHERE id = 1;

--查询索引字段
EXPLAIN SELECT id FROM t_dept WHERE id = 1;

结论:使用覆盖索引(Using index)会提高检索效率

使用不等会失效

在使用不等于(!= 或者<>,NOT LIKE)时,有时会无法使用索引会导致全表扫描

--SQL语句中有不等于
EXPLAIN SELECT * FROM t_emp WHERE age != 90;
EXPLAIN SELECT * FROM t_emp WHERE age <> 90;

--SQL语句中没有不等于
EXPLAIN SELECT * FROM t_emp WHERE age = 90;

使用NULL值要小心

在使用IS NULL 或者 IS NOT NULL时,可能会导致索引失效,但是如果允许字段为空,则

IS NULL 不会导致索引失效

IS NOT NULL 会导致索引失效

1
2
3

EXPLAIN SELECT * FROM t_emp WHERE age IS NULL;

EXPLAIN SELECT * FROM t_emp WHERE age IS NOT NULL;

模糊查询加右边

要使用模糊查询时,百分号最好加在右边,而且进行模糊查询的字段必须是单值索引

--创建单值索引
CREATE INDEX idx_emp_name ON t_emp(NAME);

--进行模糊查询
EXPLAIN SELECT * FROM t_emp WHERE name LIKE '%风';
EXPLAIN SELECT * FROM t_emp WHERE name LIKE '风%';
EXPLAIN SELECT * FROM t_emp WHERE name LIKE '%风%';

-- 有时必须使用其他类型的模糊查询,这时就需要用覆盖索引来解决索引失效的问题
EXPLAIN SELECT name FROM t_emp WHERE name LIKE '%风';
EXPLAIN SELECT name FROM t_emp WHERE name LIKE '风%';

EXPLAIN SELECT name FROM t_emp WHERE name LIKE '%风%';

结论:对索引进行模糊查询时,最好在右边加百分号.必须在左边或左右加百分号时,需要用到覆盖索引来提升查询效率

字符串加单引号

当字段为字符串时,查询时必须带上单引号.否则会发生自动的类型转换,从而发生全表扫描

--使用了单引号
EXPLAIN SELECT card_id FROM person WHERE card_id = '1';

--未使用单引号,发生自动类型转换
EXPLAIN SELECT card_id FROM person WHERE card_id = 1;

尽量不用or查询

如果使用or,可能导致索引失效.所以要减少or的使用,可以使用 union all 或者 union 来替代

1 2	--使用or进行查询 EXPLAIN SELECT * FROM t_emp WHERE age = 90 OR NAME = '风清扬';

口诀

全职匹配我最爱,最左前缀要遵守

带头大哥不能死,中间兄弟不能断

索引列上少计算,范围之后全失效

LIKE 百分写最右,覆盖索引不写*

不等空值还有 OR,索引影响要注意

VARCHAR 引号不可丢,SQL 优化有诀窍