怎么在Mysql中使用索引

共计 20970 个字符，预计需要花费 53 分钟才能阅读完成。

自动写代码机器人，免费开通

本篇文章为大家展示了怎么在 Mysql 中使用索引，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

为何要有索引?

一般的应用系统，读写比例在 10:1 左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

什么是索引？

索引在 MySQL 中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能

非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。

索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。

索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

 30
 10 40
 5 15 35 66
1 6 11 19 21 39 55 100

你是否对索引存在误解？

索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要。一些开发人员总是在事后才想起添加索引 —- 我一直认为，这源于一种错误的开发模式。如果知道数据的使用，从一开始就应该在需要处添加索引。开发人员往往对数据库的使用停留在应用的层面，比如编写 SQL 语句、存储过程之类，他们甚至可能不知道索引的存在，或认为事后让相关 DBA 加上即可。DBA 往往不够了解业务的数据流，而添加索引需要通过监控大量的 SQL 语句进而从中找到问题，这个步骤所需的时间肯定是远大于初始添加索引所需的时间，并且可能会遗漏一部分的索引。当然索引也并不是越多越好，我曾经遇到过这样一个问题：某台 MySQL 服务器 iostat 显示磁盘使用率一直处于 100%，经过分析后发现是由于开发人员添加了太多的索引，在删除一些不必要的索引之后，磁盘使用率马上下降为 20%。可见索引的添加也是非常有技术含量的。

二、索引的原理

一、索引原理

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询 (、、between、in)、模糊查询(like)、并集查询(or) 等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果 1000 条数据，1 到 100 分成第一段，101 到 200 分成第二段，201 到 300 分成第三段 …… 这样查第 250 条数据，只要找第三段就可以了，一下子去除了 90% 的无效数据。但如果是 1 千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是 lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

二、磁盘 IO 与预读

前面提到了访问磁盘，那么这里先简单介绍一下磁盘 IO 和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在 5ms 以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘 7200 转，表示每分钟能转 7200 次，也就是说 1 秒钟能转 120 次，旋转延迟就是 1 /120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘 IO 的时间约等于 5 +4.17 = 9ms 左右，听起来还挺不错的，但要知道一台 500 -MIPS（Million Instructions Per Second）的机器每秒可以执行 5 亿条指令，因为指令依靠的是电的性质，换句话说执行一次 IO 的时间可以执行约 450 万条指令，数据库动辄十万百万乃至千万级数据，每次 9 毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：

考虑到磁盘 IO 是非常高昂的操作，计算机操作系统做了一些优化，当一次 IO 时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次 IO 读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为 4k 或 8k，也就是我们读取一页内的数据时候，实际上才发生了一次 IO，这个理论对于索引的数据结构设计非常有帮助。

三、索引的数据结构

前面讲了索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘 IO 次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+ 树应运而生（B+ 树是通过二叉查找树，再由平衡二叉树，B 树演化而来）。

如上图，是一颗 b + 树，关于 b + 树的定义可以参见 B + 树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块 1 包含数据项 17 和 35，包含指针 P1、P2、P3，P1 表示小于 17 的磁盘块，P2 表示在 17 和 35 之间的磁盘块，P3 表示大于 35 的磁盘块。真实的数据存在于叶子节点即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如 17、35 并不真实存在于数据表中。

###b+ 树的查找过程

如图所示，如果要查找数据项 29，那么首先会把磁盘块 1 由磁盘加载到内存，此时发生一次 IO，在内存中用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间因为非常短（相比磁盘的 IO）可以忽略不计，通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存，发生第二次 IO，29 在 26 和 30 之间，锁定磁盘块 3 的 P2 指针，通过指针加载磁盘块 8 到内存，发生第三次 IO，同时内存中做二分查找找到 29，结束查询，总计三次 IO。真实的情况是，3 层的 b + 树可以表示上百万的数据，如果上百万的数据查找只需要三次 IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次 IO，那么总共需要百万次的 IO，显然成本非常非常高。

###b+ 树性质

1. 索引字段要尽量的小：通过上面的分析，我们知道 IO 次数取决于 b + 数的高度 h，假设当前数据表的数据为 N，每个磁盘块的数据项的数量是 m，则有 h =㏒(m+1)N，当数据量 N 一定的情况下，m 越大，h 越小；而 m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如 int 占 4 字节，要比 bigint8 字节少一半。这也是为什么 b + 树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于 1 时将会退化成线性表。

2. 索引的最左匹配特性：当 b + 树的数据项是复合的数据结构，比如 (name,age,sex) 的时候，b+ 数是按照从左到右的顺序来建立搜索树的，比如当 (张三,20,F) 这样的数据来检索的时候，b+ 树会优先比较 name 来确定下一步的所搜方向，如果 name 相同再依次比较 age 和 sex，最后得到检索的数据；但当 (20,F) 这样的没有 name 的数据来的时候，b+ 树就不知道下一步该查哪个节点，因为建立搜索树的时候 name 就是第一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询。比如当 (张三,F) 这样的数据来检索时，b+ 树可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是 F 的数据了，这个是非常重要的性质，即索引的最左匹配特性。

四、聚集索引与辅助索引

在数据库中，B+ 树的高度一般都在 2~4 层，这也就是说查找某一个键值的行记录时最多只需要 2 到 4 次 IO，这倒不错。因为当前一般的机械硬盘每秒至少可以做 100 次 IO，2~4 次的 IO 意味着查询时间只需要 0.02~0.04 秒。

数据库中的 B + 树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），

聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是 B + 树的形式，即高度是平衡的，叶子结点存放着所有的数据。

聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息

1、聚集索引

#InnoDB 存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵 B + 树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。

同 B + 树数据结构一样，每个数据页都通过一个双向链表来进行链接。#如果未定义主键，MySQL 取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB 使用它作为聚簇索引。#如果没有这样的列，InnoDB 就自己产生一个这样的 ID 值，它有六个字节，而且是隐藏的，使其作为聚簇索引。# 由于实际的数据页只能按照一棵 B + 树进行排序，因此每张表只能拥有一个聚集索引。在多少情况下，查询优化器倾向于采用聚集索引。

因为聚集索引能够在 B + 树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。

聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的 10 位用户信息，由于 B + 树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出 10 条记录

聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

2、辅助索引

表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。

叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉 InnoDB 存储引擎去哪里可以找到与索引相对应的行数据。

由于 InnoDB 存储引擎是索引组织表，因此 InnoDB 存储引擎的辅助索引的书签就是相应行数据的聚集索引键。如下图

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

举例来说，如果在一棵高度为 3 的辅助索引树种查找数据，那需要对这个辅助索引树遍历 3 次找到指定主键，如果聚集索引树的高度同样为 3，那么还需要对聚集索引树进行 3 次查找，最终找到一个完整的行数据所在的页，因此一共需要 6 次逻辑 IO 访问才能得到最终的一个数据页。

五、MySQL 索引管理

一、功能

1. 索引的功能就是加速查找

2. mysql 中的 primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

二、MySQL 常用的索引

普通索引 INDEX：加速查找

唯一索引：- 主键索引 PRIMARY KEY：加速查找 + 约束（不为空、不能重复）- 唯一索引 UNIQUE: 加速查找 + 约束（不能重复）

联合索引：-PRIMARY KEY(id,name): 联合主键索引 -UNIQUE(id,name): 联合唯一索引 -INDEX(id,name): 联合普通索引

三、索引的两大类型 hash 与 btree

# 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash 类型的索引：查询单条快，范围查询慢
btree 类型的索引：b+ 树，层数越多，数据量指数级增长（我们就用它，因为 innodb 默认支持它）

# 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

四、创建 / 删除索引的语法

# 方法一：创建表时
  CREATE TABLE  表名  (  字段名 1   数据类型  [完整性约束条件…],
  字段名 2   数据类型  [完整性约束条件…],
 [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
 [索引名] (字段名[(长度)] [ASC |DESC]) 
 );

# 方法二：CREATE 在已存在的表上创建索引
 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX  索引名  
 ON  表名  (字段名[(长度)] [ASC |DESC]) ;

# 方法三：ALTER TABLE 在已存在的表上创建索引
 ALTER TABLE  表名  ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
  索引名  (字段名[(长度)] [ASC |DESC]) ;
 
#删除索引：DROP INDEX  索引名  ON  表名字;

# 方式一
create table t1(
 id int,
 name char,
 age int,
 sex enum(male , female),
 unique key uni_id(id),
 index ix_name(name) #index 没有 key

mysql  show create table t1;
| t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL,
 `name` char(1) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 `sex` enum(male , female) DEFAULT NULL,
 UNIQUE KEY `uni_id` (`id`),
 KEY `ix_name` (`name`),
 KEY `ix_age` (`age`),
 KEY `ix_sex` (`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

六、测试索引

一、准备

#1.  准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
#2.  创建存储过程，实现批量插入记录
delimiter $$ # 声明存储过程的结束符号为 $$
create procedure auto_insert1()
BEGIN
 declare i int default 1;
 while(i 3000000)do
 insert into s1 values(i, duoduo , male ,concat( duoduo ,i, @oldboy 
 set i=i+1;
 end while;
END$$ #$$ 结束
delimiter ; # 重新声明分号为结束符号
#3.  查看存储过程
show create procedure auto_insert1\G 
#4.  调用存储过程
call auto_insert1();
#等到时间长短，看机器性能

提示：创建表的时间长短，看机器的性能，请耐心等待！

二、在没有索引的前提下测试查询速度

# 无索引：mysql 根本就不知道到底是否存在 id 等于 333333333 的记录，只能把数据表从头到尾扫描一遍，此时有多少个磁盘块就需要进行多少 IO 操作，所以查询速度很慢
mysql  select * from s1 where id=333333333;
Empty set (0.33 sec)

三、在表中已经存在大量数据的前提下，为某个字段段建立索引，建立速度会很慢

四、在索引建立完毕后，以该字段为查询条件时，查询速度提升明显

PS：

1. mysql 先去索引表里根据 b + 树的搜索原理很快搜索到 id 等于 333333333 的记录不存在，IO 大大降低，因而速度明显提升

2. 我们可以去 mysql 的 data 目录下找到该表，可以看到占用的硬盘空间多了

3. 需要注意，如下图

五、总结

#1. 一定是为搜索条件的字段创建索引，比如 select * from s1 where id = 333; 就需要为 id 加上索引

#2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快
比如 create index idx on s1(id); 会扫描表中所有的数据，然后以 id 为数据项，创建索引结构，存放于硬盘的表中。
建完以后，再查询就会很快了。

#3. 需要注意的是：innodb 表的索引会存放于 s1.ibd 文件中，而 myisam 表的索引则会有单独的索引文件 table1.MYI

MySAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
而在 innodb 中，表数据文件本身就是按照 B +Tree（BTree 即 Balance True）组织的一个索引结构，这棵树的叶节点 data 域保存了完整的数据记录。
这个索引的 key 是数据表的主键，因此 innodb 表数据文件本身就是主索引。
因为 inndob 的数据文件要按照主键聚集，所以 innodb 要求表必须要有主键（Myisam 可以没有），
如果没有显式定义，则 mysql 系统会自动选择一个可以唯一标识数据记录的列作为主键，
如果不存在这种列，则 mysql 会自动为 innodb 表生成一个隐含字段作为主键，这字段的长度为 6 个字节，类型为长整型.

七、正确使用索引

一、索引未命中

并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题

1、范围问题，或者说条件不明确，条件中出现这些符号或关键字：、=、、=、!=、between…and…、like、

大于号、小于号

不等于！=

between …and…

like

2 尽量选择区分度高的列作为索引, 区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要 join 的字段我们都要求是 0.1 以上，即平均 1 条扫描 10 条记录

# 先把表中的索引都删除，让我们专心研究区分度的问题
mysql  desc s1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | char(5) | YES | | NULL | |
| email | varchar(50) | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql  drop index a on s1;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql  drop index d on s1;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql  desc s1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | char(5) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)

先把表中的索引都删除，让我们专心研究区分度的问题

我们编写存储过程为表 s1 批量添加记录，name 字段的值均为 duoduo，也就是说 name 这个字段的区分度很低（gender 字段也是一样的，我们稍后再搭理它）

回忆 b + 树的结构，查询的速度与树的高度成反比，要想将树的高低控制的很低，需要保证：在某一层内数据项均是按照从左到右，从小到大的顺序依次排开，即左 1 左 2 左 3 …

而对于区分度低的字段，无法找到大小关系，因为值都是相等的，毫无疑问，还想要用 b + 树存放这些等值的数据，只能增加树的高度，字段的区分度越低，则树的高度越高。极端的情况，索引字段的值都一样，那么 b + 树几乎成了一根棍。本例中就是这种极端的情况，name 字段所有的值均为 duoduo

# 现在我们得出一个结论：为区分度低的字段建立索引，索引树的高度会很高，然而这具体会带来什么影响呢？？？

#1：如果条件是 name= xxxx , 那么肯定是可以第一时间判断出 xxxx 是不在索引树中的（因为树中所有的值均为 duoduo），所以查询速度很快

#2：如果条件正好是 name= duoduo , 查询时，我们永远无法从树的某个位置得到一个明确的范围，只能往下找，往下找，往下找。。。这与全表扫描的 IO 次数没有多大区别，所以速度很慢

分析

3、= 和 in 可以乱序，比如 a = 1 and b = 2 and c = 3 建立 (a,b,c) 索引可以任意顺序，mysql 的查询优化器会帮你优化成索引可以识别的形式

4、索引列不能参与计算，保持列“干净”，比如 from_unixtime(create_time) = 2014-05-29 就不能使用到索引，原因很简单，b+ 树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成 create_time = unix_timestamp(2014-05-29)

5、and/or

#1、and 与 or 的逻辑
条件 1 and 条件 2: 所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立
条件 1 or 条件 2: 只要有一个条件成立则最终结果就成立

#2、and 的工作原理
条件：
a = 10 and b = xxx and c 3 and d =4
索引：
制作联合索引 (d,a,b,c)
工作原理:
对于连续多个 and：mysql 会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照 d— a- b- c 的顺序

#3、or 的工作原理
条件：
a = 10 or b = xxx or c 3 or d =4
索引：
制作联合索引(d,a,b,c)

工作原理:
对于连续多个 or：mysql 会按照条件的顺序，从左到右依次判断，即 a - b- c- d

在左边条件成立但是索引字段的区分度低的情况下（name，加速查询）

6 最左前缀匹配原则，非常重要的原则，对于组合索引 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 的顺序可以任意调整。

7、其他情况

-  使用函数
 select * from tb1 where reverse(email) =  duoduo 
 
-  类型不一致
  如果列是字符串类型，传入条件是必须用引号引起来，不然...
 select * from tb1 where email = 999;
 
#排序条件为索引，则 select 字段必须也是索引字段，否则无法命中
- order by
 select name from s1 order by email desc;
  当根据索引排序时候，select 查询的字段如果不是索引，则速度仍然很慢
 select email from s1 order by email desc;
  特别的：如果对主键排序，则还是速度很快： select * from tb1 order by nid desc;
 
-  组合索引最左前缀
  如果组合索引为：(name,email)
 name and email --  命中索引
 name --  命中索引
 email --  未命中索引

- create index xxxx on tb(title(19)) #text 类型，必须制定长度

其他注意事项

– 避免使用 select *
– count(1)或 count(列) 代替 count(*)
– 创建表时尽量时 char 代替 varchar
– 表的字段顺序固定长度的字段优先
– 组合索引代替多个单列索引（经常使用多个条件查询时）
– 尽量使用短索引
– 使用连接（JOIN）来代替子查询(Sub-Queries)
– 连表时注意条件类型需一致
– 索引散列值（重复少）不适合建索引，例：性别不适合

八、联合索引与覆盖索引

一、联合索引

联合索引时指对表上的多个列合起来做一个索引。联合索引的创建方法与单个索引的创建方法一样，不同之处在仅在于有多个索引列，如下

mysql  create table t(
 -  a int,
 -  b int,
 -  primary key(a),
 -  key idx_a_b(a,b)
 -  );
Query OK, 0 rows affected (0.11 sec)

那么何时需要使用联合索引呢？在讨论这个问题之前，先来看一下联合索引内部的结果。从本质上来说，联合索引就是一棵 B + 树，不同的是联合索引的键值得数量不是 1，而是 =2。接着来讨论两个整型列组成的联合索引，假定两个键值得名称分别为 a、b 如图

可以看到这与我们之前看到的单个键的 B + 树并没有什么不同，键值都是排序的，通过叶子结点可以逻辑上顺序地读出所有数据，就上面的例子来说，即（1,1），（1,2），（2,1），（2,4），（3,1），（3,2），数据按（a,b）的顺序进行了存放。

因此，对于查询 select * from table where a=xxx and b=xxx, 显然是可以使用(a,b) 这个联合索引的，对于单个列 a 的查询 select * from table where a=xxx, 也是可以使用（a,b）这个索引的。

但对于 b 列的查询 select * from table where b=xxx, 则不可以使用（a,b）索引，其实你不难发现原因，叶子节点上 b 的值为 1、2、1、4、1、2 显然不是排序的，因此对于 b 列的查询使用不到(a,b) 索引

联合索引的第二个好处是在第一个键相同的情况下，已经对第二个键进行了排序处理，例如在很多情况下应用程序都需要查询某个用户的购物情况，并按照时间进行排序，最后取出最近三次的购买记录，这时使用联合索引可以帮我们避免多一次的排序操作，因为索引本身在叶子节点已经排序了，如下

#=========== 准备表 ==============
create table buy_log(
 userid int unsigned not null,
 buy_date date
insert into buy_log values
(1, 2009-01-01),
(2, 2009-01-01),
(3, 2009-01-01),
(1, 2009-02-01),
(3, 2009-02-01),
(1, 2009-03-01),
(1, 2009-04-01 
alter table buy_log add key(userid);
alter table buy_log add key(userid,buy_date);
#=========== 验证 ==============
mysql  show create table buy_log;
| buy_log | CREATE TABLE `buy_log` ( `userid` int(10) unsigned NOT NULL,
 `buy_date` date DEFAULT NULL,
 KEY `userid` (`userid`),
 KEY `userid_2` (`userid`,`buy_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
#可以看到 possible_keys 在这里有两个索引可以用，分别是单个索引 userid 与联合索引 userid_2, 但是优化器最终选择了使用的 key 是 userid 因为该索引的叶子节点包含单个键值，所以理论上一个页能存放的记录应该更多
mysql  explain select * from buy_log where userid=2;
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
| 1 | SIMPLE | buy_log | ref | userid,userid_2 | userid | 4 | const | 1 | |
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
row in set (0.00 sec)
#接着假定要取出 userid 为 1 的最近 3 次的购买记录，用的就是联合索引 userid_2 了，因为在这个索引中，在 userid= 1 的情况下，buy_date 都已经排序好了
mysql  explain select * from buy_log where userid=1 order by buy_date desc limit 3;
+----+-------------+---------+------+-----------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-----------------+----------+---------+-------+------+--------------------------+
| 1 | SIMPLE | buy_log | ref | userid,userid_2 | userid_2 | 4 | const | 4 | Using where; Using index |
+----+-------------+---------+------+-----------------+----------+---------+-------+------+--------------------------+
row in set (0.00 sec)
#ps：如果 extra 的排序显示是 Using filesort，则意味着在查出数据后需要二次排序

# 对于联合索引（a,b）, 下述语句可以直接使用该索引，无需二次排序
select ... from table where a=xxx order by b;
#然后对于联合索引 (a,b,c) 来首，下列语句同样可以直接通过索引得到结果
select ... from table where a=xxx order by b;
select ... from table where a=xxx and b=xxx order by c;
#但是对于联合索引 (a,b,c)，下列语句不能通过索引直接得到结果，还需要自己执行一次 filesort 操作，因为索引（a，c) 并未排序
select ... from table where a=xxx order by c;

二、覆盖索引

InnoDB 存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。

使用覆盖索引的一个好处是：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的 IO 操作

注意：覆盖索引技术最早是在 InnoDB Plugin 中完成并实现，这意味着对于 InnoDB 版本小于 1.0 的，或者 MySQL 数据库版本为 5.0 以下的，InnoDB 存储引擎不支持覆盖索引特性

对于 InnoDB 存储引擎的辅助索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为（primary key1，priamey key2，…,key1，key2，…）。例如

select age from s1 where id=123 and name =  duoduo  #id 字段有索引，但是 name 字段没有索引, 该 sql 命中了索引，但未覆盖，需要去聚集索引中再查找详细信息。最牛逼的情况是，索引字段覆盖了所有，那全程通过索引来加速查询以及获取结果就 ok 了
mysql  desc s1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | char(6) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
rows in set (0.21 sec)
mysql  explain select name from s1 where id=1000; # 没有任何索引
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 2688336 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
row in set, 1 warning (0.00 sec)
mysql  create index idx_id on s1(id); # 创建索引
Query OK, 0 rows affected (4.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql  explain select name from s1 where id=1000; # 命中辅助索引，但是未覆盖索引，还需要从聚集索引中查找 name
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ref | idx_id | idx_id | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
row in set, 1 warning (0.08 sec)
mysql  explain select id from s1 where id=1000; # 在辅助索引中就找到了全部信息，Using index 代表覆盖索引
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ref | idx_id | idx_id | 4 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
row in set, 1 warning (0.03 sec)

覆盖索引的另外一个好处是对某些统计问题而言的。基于上一小结创建的表 buy_log, 查询计划如下

mysql  explain select count(*) from buy_log;
+----+-------------+---------+-------+---------------+--------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+--------+---------+------+------+-------------+
| 1 | SIMPLE | buy_log | index | NULL | userid | 4 | NULL | 7 | Using index |
+----+-------------+---------+-------+---------------+--------+---------+------+------+-------------+
row in set (0.00 sec)

innodb 存储引擎并不会选择通过查询聚集索引来进行统计。由于 buy_log 表有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少 IO 操作，故优化器的选择如上 key 为 userid 辅助索引

对于（a,b）形式的联合索引，一般是不可以选择 b 中所谓的查询条件。但如果是统计操作，并且是覆盖索引，则优化器还是会选择使用该索引，如下

# 联合索引 userid_2（userid,buy_date）, 一般情况，我们按照 buy_date 是无法使用该索引的，但特殊情况下：查询语句是统计操作，且是覆盖索引，则按照 buy_date 当做查询条件时，也可以使用该联合索引
mysql  explain select count(*) from buy_log where buy_date  =  2011-01-01  and buy_date    2011-02-01 
+----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+
| 1 | SIMPLE | buy_log | index | NULL | userid_2 | 8 | NULL | 7 | Using where; Using index |
+----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+
row in set (0.00 sec)

九、查询优化神器 -explain

关于 explain 命令相信大家并不陌生，具体用法和字段含义可以参考官网 explain-output，这里需要强调 rows 是核心指标，绝大部分 rows 小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化 rows。

执行计划：让 mysql 预估执行操作(一般正确)
all index range index_merge ref_or_null ref eq_ref system/const
id,email

慢：
select * from userinfo3 where name= alex

explain select * from userinfo3 where name= alex
type: ALL(全表扫描)
select * from userinfo3 limit 1;
快：
select * from userinfo3 where email= alex
type: const(走索引)

参考文中：https://www.jb51.net/article/140759.htm

十、慢查询优化的基本步骤

0. 先运行看看是否真的很慢，注意设置 SQL_NO_CACHE
1.where 条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的 where 都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain 查看执行计划，是否与 1 预期一致（从锁定记录较少的表开始查询）
3.order by limit 形式的 sql 语句让排序的表优先查
4. 了解业务方使用场景
5. 加索引时参照建索引的几大原则
6. 观察结果，不符合预期继续从 0 分析

十一、慢日志管理

慢日志
  – 执行时间 10
  – 未命中索引
  – 日志文件路径
 
  配置：
  – 内存
  show variables like %query%
  show variables like %queries%
  set global 变量名 = 值
  – 配置文件
  mysqld –defaults-file= E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini
 
  my.conf 内容：
  slow_query_log = ON
  slow_query_log_file = D:/….
 
  注意：修改配置文件之后，需要重启服务

MySQL 日志管理

========================================================

错误日志: 记录 MySQL 服务器启动、关闭及运行错误等信息
二进制日志: 又称 binlog 日志，以二进制文件的方式记录数据库中除 SELECT 以外的操作
查询日志: 记录查询的信息
慢查询日志: 记录执行时间超过指定时间的操作
中继日志：备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放
通用日志：审计哪个账号、在哪个时段、做了哪些事件
事务日志或称 redo 日志：记录 Innodb 事务相关的如事务执行时间、检查点等
========================================================

一、bin-log

1. 启用

# vim /etc/my.cnf
[mysqld]
log-bin[=dir\[filename]]
# service mysqld restart

2. 暂停

// 仅当前会话
SET SQL_LOG_BIN=0;
SET SQL_LOG_BIN=1;

3. 查看

查看全部：

# mysqlbinlog mysql.000002
# mysqlbinlog mysql.000002 --start-datetime= 2012-12-05 10:02:56 
# mysqlbinlog mysql.000002 --stop-datetime= 2012-12-05 11:02:54 
# mysqlbinlog mysql.000002 --start-datetime= 2012-12-05 10:02:56  --stop-datetime= 2012-12-05 11:02:54

按字节数：

# mysqlbinlog mysql.000002 --start-position=260
# mysqlbinlog mysql.000002 --stop-position=260
# mysqlbinlog mysql.000002 --start-position=260 --stop-position=930

4. 截断 bin-log（产生新的 bin-log 文件）

a. 重启 mysql 服务器

b. # mysql -uroot -p123 -e flush logs

5. 删除 bin-log 文件

# mysql -uroot -p123 -e  reset master

二、查询日志

启用通用查询日志

# vim /etc/my.cnf
[mysqld]
log[=dir\[filename]]
# service mysqld restart

三、慢查询日志

启用慢查询日志

# vim /etc/my.cnf
[mysqld]
log-slow-queries[=dir\[filename]]
long_query_time=n
# service mysqld restart

MySQL 5.6:

slow-query-log=1
slow-query-log-file=slow.log
long_query_time=3

查看慢查询日志

测试:

BENCHMARK(count,expr)
SELECT BENCHMARK(50000000,2*3);

上述内容就是怎么在 Mysql 中使用索引，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注丸趣 TV 行业资讯频道。

向 AI 问一下细节