MySQL索引原理

程序员文章站 2022-06-27 15:29:33

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

- 2.2 磁盘io与预读
四、mysql索引管理

一、初识索引

1.1 为什么要有索引？

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

1.2 什么是索引？

索引在mysql中也叫是一种“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。

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

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

1.3 你是否对索引存在误解？

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

二、索引的原理

2.1 索引原理

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

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

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

2.2 磁盘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毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：

MySQL索引原理

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

三、索引的数据结构

mysql索引的数据结构-b+树介绍：https://www.cnblogs.com/nickchen121/p/11152523.html

四、mysql索引管理

4.1 功能

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

4.2 mysql常用的索引

普通索引index：加速查找
唯一索引：
- 主键索引primary key：加速查找+约束（不为空、不能重复）
- 唯一索引unique:加速查找+约束（不能重复）
联合索引：
- primary key(id,name):联合主键索引
- unique(id,name):联合唯一索引
- index(id,name):联合普通索引

4.3 各个索引应用场景

举个例子来说，比如你在为某商场做一个会员卡的系统。

这个系统有一个会员表
有下列字段：
会员编号 int
会员姓名 varchar(10)
会员身份证号码 varchar(18)
会员电话 varchar(10)
会员住址 varchar(50)
会员备注信息 text

那么这个 会员编号，作为主键，使用 primary
会员姓名 如果要建索引的话，那么就是普通的 index
会员身份证号码 如果要建索引的话，那么可以选择 unique （唯一的，不允许重复）

# 除此之外还有全文索引，即fulltext
会员备注信息 ， 如果需要建索引的话，可以选择全文搜索。
用于搜索很长一篇文章的时候，效果最好。
用在比较短的文本，如果就一两行字的，普通的 index 也可以。
但其实对于全文搜索，我们并不会使用mysql自带的该索引，而是会选择第三方软件如sphinx，专门来做全文搜索。

# 其他的如空间索引spatial，了解即可，几乎不用

各个索引的应用场景

各个索引的应用场景

4.4 索引的两大类型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 等索引；

4.5 创建/删除索引的语法

# 方法一：创建表时
    　　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 表名字;

4.6 示例

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


# 方式二
create index ix_age on t1(age);


# 方式三
alter table t1 add index ix_sex(sex);
alter table t1 add index(sex);

# 查看
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

五、测试索引

5.1 数据准备

# 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,'eva','female',concat('eva',i,'@oldboy'));
        set i=i+1;
    end while;
end$$ # $$结束
delimiter ; # 重新声明分号为结束符号

# 3. 查看存储过程
show create procedure auto_insert1\g 

# 4. 调用存储过程
call auto_insert1();

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

无索引：mysql根本就不知道到底是否存在id等于333333333的记录，只能把数据表从头到尾扫描一遍，此时有多少个磁盘块就需要进行多少io操作，所以查询速度很慢

mysql> select * from s1 where id=333333333;
empty set (0.33 sec)

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

MySQL索引原理

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

MySQL索引原理

注意：

mysql先去索引表里根据b+树的搜索原理很快搜索到id等于333333333的记录不存在，io大大降低，因而速度明显提升
我们可以去mysql的data目录下找到该表，可以看到占用的硬盘空间多了
需要注意，如下图

MySQL索引原理

5.2 小结

一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引
在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快，比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。建完以后，再查询就会很快了。
需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.myi

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

六、正确使用索引

6.1 索引未命中

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

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

MySQL索引原理

不等于！=

MySQL索引原理

between ...and...

MySQL索引原理

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)

MySQL索引原理

分析原因：

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

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

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

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

如果条件是name='xxxx',那么肯定是可以第一时间判断出'xxxx'是不在索引树中的（因为树中所有的值均为'nick’），所以查询速度很快
如果条件正好是name='nick',查询时，我们永远无法从树的某个位置得到一个明确的范围，只能往下找，往下找，往下找。。。这与全表扫描的io次数没有多大区别，所以速度很慢

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

MySQL索引原理

4、and/or

and与or的逻辑
- 条件1 and 条件2：所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立
- 条件1 or 条件2：只要有一个条件成立则最终结果就成立
and的工作原理
- 条件：a = 10 and b = 'xxx' and c > 3 and d =4
- 索引：制作联合索引(d,a,b,c)
- 工作原理：对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序
or的工作原理
- 条件：a = 10 or b = 'xxx' or c > 3 or d =4
- 索引：制作联合索引(d,a,b,c)
- 工作原理：对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d

MySQL索引原理

在左边条件成立但是索引字段的区分度低的情况下（name与gender均属于这种情况），会依次往右找到一个区分度高的索引字段，加速查询。

MySQL索引原理

经过分析，在条件为name='nick' and gender='male' and id>333 and email='xxx'的情况下，我们完全没必要为前三个条件的字段加索引，因为只能用上email字段的索引，前三个字段的索引反而会降低我们的查询效率

MySQL索引原理

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

MySQL索引原理

6、其他情况

- 使用函数
    select * from tb1 where reverse(email) = 'nick';
            
- 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    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                -- 未命中索引


- count(1)或count(列)代替count(*)在mysql中没有差别了

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

6.2 其他注意事项

避免使用select *
使用count(*)
创建表时尽量使用 char 代替 varchar
表的字段顺序固定长度的字段优先
组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
尽量使用短索引
使用连接（join）来代替子查询(sub-queries)
连表时注意条件类型需一致
索引散列值（重复少）不适合建索引，例：性别不适合

七、联合索引和覆盖索引

7.1 联合索引

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

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如图：

MySQL索引原理

可以看到这与我们之前看到的单个键的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，则意味着在查出数据后需要二次排序(如下查询语句，没有先用where userid=3先定位范围，于是即便命中索引也没用，需要二次排序)
mysql> explain select * from buy_log order by buy_date desc limit 3;
+--+-----------+-------+-----+-------------+--------+-------+----+----+---------------------------+
|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 index; 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;

7.2 覆盖索引

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 = 'nick'; #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)

7.3 合并索引

mysql> explain select count(email) from index_t where   id = 1000000  or email='eva100000@oldboy';
+--+-----------+------+--------------+--------------------------------+---------------+--------+-----+----+-----------------------------------------+
| id | select_type| table  | type                | possible_keys                              | key                   | key_len | ref    |rows | extra                                                           |
+--+-----------+------+--------------+--------------------------------+---------------+--------+-----+----+-----------------------------------------+
|  1 | simple      | index_t| index_merge | primary,email,ind_id,ind_email | primary,email | 4,51   |null|   2    |using union(primary,email); using where |
+--+-----------+------+--------------+--------------------------------+---------------+--------+-----+----+-----------------------------------------+
row in set (0.01 sec)

八、查询优化神器-explain

mysql性能分析之explain：https://www.cnblogs.com/nickchen121/p/11155917.html

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

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

上一篇：详解挂载运行的docker容器中如何挂载文件系统

下一篇：孙策死后孙权上位孙权是怎么对待大哥的后代的

MySQL索引原理

一、初识索引

1.1 为什么要有索引？

1.2 什么是索引？

1.3 你是否对索引存在误解？

二、索引的原理

2.1 索引原理

2.2 磁盘io与预读

三、索引的数据结构

四、mysql索引管理

4.1 功能

4.2 mysql常用的索引

4.3 各个索引应用场景

4.4 索引的两大类型hash与btree

4.5 创建/删除索引的语法

4.6 示例

五、测试索引

5.1 数据准备

5.2 小结

六、正确使用索引

6.1 索引未命中

6.2 其他注意事项

七、联合索引和覆盖索引

7.1 联合索引

7.2 覆盖索引

7.3 合并索引

八、查询优化神器-explain

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

十、慢日志管理

百度即将成为中国区Win10默认搜索引擎取代Bing

如何通过一张图搞懂springBoot自动注入原理

python连接mysql调用存储过程示例

Sql Server 数据库索引整理语句,自动整理数据库索引

django的ORM模型的实现原理

python连接mysql并提交mysql事务示例

原理分析SonarQube中IdentityProvider账户互斥现象

简单聊聊SQL注入的原理以及一般步骤

jsp自定义标签技术(实现原理与代码以及平台搭建步骤)

MySQL查询本周、上周、本月、上个月份数据的sql代码介绍

MySQL索引原理

一、初识索引

1.1 为什么要有索引？

1.2 什么是索引？

1.3 你是否对索引存在误解？

二、索引的原理

2.1 索引原理

2.2 磁盘io与预读

三、索引的数据结构

四、mysql索引管理

4.1 功能

4.2 mysql常用的索引

4.3 各个索引应用场景

4.4 索引的两大类型hash与btree

4.5 创建/删除索引的语法

4.6 示例

五、测试索引

5.1 数据准备

5.2 小结

六、正确使用索引

6.1 索引未命中

6.2 其他注意事项

七、联合索引和覆盖索引

7.1 联合索引

7.2 覆盖索引

7.3 合并索引

八、查询优化神器-explain

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

十、慢日志管理

百度即将成为中国区Win10默认搜索引擎 取代Bing

如何通过一张图搞懂springBoot自动注入原理

python连接mysql调用存储过程示例

Sql Server 数据库索引整理语句,自动整理数据库索引

django的ORM模型的实现原理

python连接mysql并提交mysql事务示例

原理分析SonarQube中IdentityProvider账户互斥现象

简单聊聊SQL注入的原理以及一般步骤

jsp自定义标签技术(实现原理与代码以及平台搭建步骤)

MySQL查询本周、上周、本月、上个月份数据的sql代码介绍

百度即将成为中国区Win10默认搜索引擎取代Bing