索引原理与慢查询优化

索引原理与慢查询优化

索引原理与慢查询优化

一索引原理

1 为何要用索引

	创建索引的目的是为了优化查询速度，但是一张表一旦创建了索引，会降低读写速度。

2 什么是索引

	索引是MySQL数据库的一种数据结构，在MySQL里称之为key。

3 索引的原理

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

**磁盘I/O与预读**

	搜索的效率不仅仅与算法有关，还受限于磁盘的I/O。考虑到磁盘的I/O是时间成本非常高昂的操作，计算机系统做了优化，当一次I/O时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内。

4 索引的数据结构

	每次查找数据时把磁盘I/O次数控制在一个很小的数量级，最好是常数数量级。通过B+树得到一个高度可控的多路搜索树。

4.1 B+树：

	通过二叉查找树，再由平衡二叉树，B树演化而来。

# 如上图，是一颗b+树，关于b+树的定义可以参见[B+树](http://zh.wikipedia.org/wiki/B%2B树)，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项(深蓝色所示) 和指针(黄色所示) ，如磁盘块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并不真实存在于数据表中。

4.2 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，显然成本非常非常高。

4.3 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次I/O就可以查找到某一键值的行记录。

	数据库中的B+树索引可以分为聚集索引(**clustered index**) 和辅助索引(**secondary index**) 。

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

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

1 聚集索引

# InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引(clustered index) 就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。
    
# 如果未定义主键，MySQL取第一个唯一索引(unique) 而且只含非空列(NOT NULL) 作为主键，InnoDB使用它作为聚簇索引。
    
# 如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

# 由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。

1.1 聚集索引的优点1：

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

# 使用索引测试表格S1
mysql> desc s1;  # 最开始没有主键
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | 0       |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.09 sec)

mysql> explain select * from s1 order by id desc limit 10;  # Using filesort，需要额外二次排序
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | s1    | ALL  | NULL          | NULL | NULL    | NULL | 2926044 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
1 row in set (0.00 sec)

mysql> alter table s1 add primary key(id);  # 添加主键
Query OK, 0 rows affected (21.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from s1 order by id desc limit 10;  # 基于主键的聚集索引在创建完毕后就已经完成了排序，无需二次排序
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | s1    | index | NULL          | PRIMARY | 4       | NULL |   10 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

1.2 聚集索引的优点2：

范围查询(range query) ，即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可。

# 范围查询对比
mysql> alter table s1 drop primary key;
Query OK, 2999999 rows affected (24.81 sec)
Records: 2999999  Duplicates: 0  Warnings: 0

mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | 0       |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.07 sec)

mysql> explain select * from s1 where id > 1 and id < 1000000;  # 没有聚集索引，预估需要检索的rows数如下
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | s1    | ALL  | NULL          | NULL | NULL    | NULL | 2987134 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> alter table s1 add primary key(id);  # 添加主键
Query OK, 0 rows affected (21.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from s1 where id > 1 and id < 1000000;  # 有聚集索引，预估需要检索的rows数如下
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | s1    | range | PRIMARY       | PRIMARY | 4       | NULL | 1495887 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.04 sec)

2 辅助索引

	辅助索引即除聚集索引之外的其他索引，也称非聚集索引。它与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。

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

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

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

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

三 MySQL索引管理

1 功能

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

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

2 MySQL常用的索引

2.1 普通索引：

	index：加速查找。

2.2 唯一索引：

	主键索引 primary key ：	# 加速查找+约束(非空、不重复) 

	唯一索引 unique ：		# 加速查找+约束(不可重复)

2.3 联合索引：

	primary key (id, name) : # 联合主键索引

	unique (id, name) : 	# 联合唯一索引

	index (id, name) : 		# 联合普通索引

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

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

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

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

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

3 索引的两大类型hash与btree

# 我们可以在创建索引的时候，为其指定索引类型，分两类：
    1) hash类型的索引：查询单条快，范围查询慢；
    2) 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 创建/删除索引的语法

4.1 创建索引的三种方法

4.1.1 方法一：创建表时

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

4.1.2 方法二：CREATE ，在已存在的表上建立索引

CREATE  [UNIQUE | FULLTEXT | SPATIAL]  INDEX 索引名
	ON 表名  (字段名[(长度)]  [ASC | DESC]);

4.1.3 方法一：ALTER TABLE ，在已存在的表上创建索引

ALTER TABLE  [UNIQUE | FULLTEXT | SPATIAL]  INDEX 索引名
	ON 表名  (字段名[(长度)]  [ASC | DESC]);

4.2 删除索引的语法

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
);


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

#方式三
alter table t1 add index ix_sex(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

四测试索引

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

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

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

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

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

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

mysql> create index xxx on s1(id);
Query OK, 0 rows affected (16.63 sec)
Records: 0  Duplicates: 0  Warnings: 0

4 在索引建立完毕后，以该字段为查询条件时

mysql> select * from s1 where id = 333333333;
Empty set (0.00 sec)

PS：

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

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

# 3.注意：没有为email加索引，因而以该字段为查询条件，速度依然很慢
mysql> select * from s1 where email="xxxx";
Empty set (4.18 sec)

5 总结

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

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

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

五正确使用索引

1 索引未命中

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

1.1 范围问题

或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!=、between...and...、like

1.1.1 大于号、小于号

mysql> select count(*) from s1 where id=1000;  
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)  -- 【明确要找1000这个id，在索引树中可以快速找到】

mysql> select count(*) from s1 where id>1000;
+----------+
| count(*) |
+----------+
|  2998999 |
+----------+
1 row in set (2.39 sec)  -- 【同上，也会利用索引树，但是没有明确指定到底是哪个id，而是指定了一个范围，这个范围包含所有大于1000的id，即MySQL会拿着1001去搜索树中找一次，然后拿着1003...再拿着1009...整体下来，如果范围很大，则跟全表扫描没多大区别】

mysql> select count(*) from s1 where id>1000 and id<2000;
+----------+
| count(*) |
+----------+
|      999 |
+----------+
1 row in set (0.00 sec)  -- 【如果范围很小，查询速度依然很快】

1.1.2 不等于!=

mysql> select count(*) from s1 where id=1000;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from s1 where id!=1000;
+----------+
| count(*) |
+----------+
|  2999998 |
+----------+
1 row in set (2.47 sec)  -- 【不等于1000的id，是一个很大的范围】

1.1.3 between...and...

mysql> select count(*) from s1 where id between 1 and 300000;
+----------+
| count(*) |
+----------+
|   300000 |
+----------+
1 row in set (0.24 sec)  -- 【范围大，查询速度很慢】

mysql> select count(*) from s1 where id between 1 and 3;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)  -- 【范围小，查询速度很快】

1.1.4 like

mysql> select * from s1 where email="xxxx";
Empty set (4.34 sec)  -- 【没有给email字段加索引，查询速度很慢】

mysql> create index exxx on s1(email);
Query OK, 0 rows affected (27.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from s1 where email="xxxx";
Empty set (0.00 sec)  -- 【给email字段加索引，查询速度加快】

mysql> select * from s1 where email like "xxxx";
Empty set (0.00 sec)  -- 【like指定的是一个明确的值，查询速度依然很快】

mysql> select * from s1 where email like "xxxx%";
Empty set (0.00 sec)  -- 【like匹配的字符中有%，但是处于末尾，查询速度依然很快】

mysql> select * from s1 where email like "%xxxx";
Empty set (5.30 sec)  -- 【like匹配的字符中有%，但是处于开头，查询速度慢】

1.2 尽量选择区分度高的列作为索引

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

# 先把表中的索引都删除，研究一下区分度的问题
mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | MUL | 0       |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.07 sec)

mysql> drop index xxx on s1;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index exxx on s1;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | 0       |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.07 sec)

# 以name字段测试
mysql> select * from s1 where name='xxx';
Empty set (4.43 sec)  -- 【没有为name加索引前，查询速度慢】

mysql> create index nxxx on s1(name);  -- 【给name字段加索引】
Query OK, 0 rows affected (24.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(*) from s1 where name='xxx';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)  -- 【为name加索引后，查询速度似乎变快了】

mysql> select count(*) from s1 where name='egon';
+----------+
| count(*) |
+----------+
|  2999999 |
+----------+
1 row in set (3.61 sec)  -- 【如果查询条件为name='egon'，查询速度又变慢了，为什么？】

原因分析：

	我们编写存储过程为表s1批量添加记录，name字段的值均为egon，也就是说name这个字段的区分度很低(gender字段也是一样的) 

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

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

# 现在我们得出一个结论：为区分度低的字段建立索引，索引树的高度会很高。他带来的影响如下：

# 1) 如果条件是name='xxx',那么肯定是可以第一时间判断出'xxx'是不在索引树中的(因为树中所有的值均为'egon’) ，所以查询速度很快

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

1.3 `=` 和 `in` 可以乱序

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

1.4 索引列不能参与计算

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

mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | 0       |       |
| name   | varchar(20) | YES  | MUL | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

mysql> drop index nxxx on s1;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index xxx on s1(id);
Query OK, 0 rows affected (4.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | MUL | 0       |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

mysql> select count(*) from s1 where id=3000;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)  -- 【id字段有索引，于是查询速度很快】

mysql> select count(*) from s1 where id*3=3000;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.72 sec)  -- 【索引字段id参与了计算，无法拿到一个明确的值去索引树中查找，每次都得临时计算一下】

1.5 and/or

1.5.1 and 与 or 的逻辑

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

1.5.2 and 的工作原理

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

1.5.3 or 的工作原理

	条件:
		a = 10 or b = 'xxx' or c > 3 or d = 4
	索引:
		制作联合索引(d,a,b,c)
	工作原理:
		对于连续多个or: MySQL会按照条件的顺序，从左到右以此判断，即a->b->c->d

# 代码演示
mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | MUL | 0       |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.05 sec)

mysql> create index nxxx on s1(name);  -- 【为name字段添加索引，但是该字段的区分度低】
Query OK, 0 rows affected (5.95 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(*) from s1 where name='xxx';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)  -- 【name='xxx'可以很快地从索引树中区分出该字段不存在，因而速度很快】

mysql> select count(*) from s1 where name='xxx' and gender='male';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)  -- 【gender是非索引字段，但name='xxx'不成立，因而根本不会管gender的条件，而且name=‘xxx'的速度很快，整体速度还是很快】

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

# 对比下列各个查询的速度
mysql> select count(*) from s1 where name='xxx' and gender='male';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from s1 where name='egon' and gender='male';
+----------+
| count(*) |
+----------+
|  2999999 |
+----------+
1 row in set (4.96 sec)

mysql> create index gxxx on s1(gender);
Query OK, 0 rows affected (6.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(*) from s1 where name='egon' and gender='male';
+----------+
| count(*) |
+----------+
|  2999999 |
+----------+
1 row in set (2.43 sec)

mysql> select count(*) from s1 where name='egon' and gender='xxx';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.04 sec)

mysql> select count(*) from s1 where name='egon' and gender='male' and id=333;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from s1 where name='egon' and gender='male' and id>333;
+----------+
| count(*) |
+----------+
|  2999666 |
+----------+
1 row in set (4.72 sec)

mysql> select count(*) from s1 where name='egon' and gender='xxx' and id>333;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.05 sec)
-- ==================================<分割线>==================================
mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | MUL | 0       |       |
| name   | varchar(20) | YES  | MUL | NULL    |       |
| gender | char(6)     | YES  | MUL | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

mysql> select count(*) from s1 where name='egon' and gender='xxx' and id>333;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from s1 where name='egon' and gender='male' and id>333;
+----------+
| count(*) |
+----------+
|  2999666 |
+----------+
1 row in set (4.96 sec)

mysql> select count(*) from s1 where name='egon' and gender='male' and id>333 and email='xxx';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (5.23 sec)  -- 【前三个条件都成立，但都无法利用索引达到加速的目的，第一个和第二个都是因为区分度低，而第三个是因为使用了一个很大的范围，而第四个字段email的区分度高，但是未加索引，所以查询速度很慢】

mysql> create index exxx on s1(email);  -- 【为name字段添加索引，该字段的区分度很高】
Query OK, 0 rows affected (7.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(*) from s1 where name='egon' and gender='male' and id>333 and email='xxx';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.03 sec)  -- 【速度较之前提升明显】

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

mysql> select count(*) from s1 where name='egon' and gender='male' and id>333 and email='xxx';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.03 sec)

mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | MUL | 0       |       |
| name   | varchar(20) | YES  | MUL | NULL    |       |
| gender | char(6)     | YES  | MUL | NULL    |       |
| email  | varchar(50) | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

mysql> drop index xxx on s1;  -- 【无用索引反而会降低查询速度，删除无用索引】
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index nxxx on s1;  -- 【无用索引反而会降低查询速度，删除无用索引】
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index gxxx on s1;  -- 【无用索引反而会降低查询速度，删除无用索引】
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | 0       |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

mysql> select count(*) from s1 where name='egon' and gender='male' and id>333 and email='xxx';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)  -- 【查询速度很快】

1.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 的顺序可以任意调整。

mysql> drop index exxx on s1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index mxxx on s1(id,name,gender,email);  -- 【建索引时未将条件中关于范围比较的字段放到后面】
Query OK, 0 rows affected (6.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(*) from s1 where name='egon' and gender='male' and id>333 and email='xxx';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (1.47 sec)  -- 【建索引时未将条件中关于范围比较的字段放到后面，查询速度很慢】

mysql> drop index mxxx on s1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index mxxx on s1(name,gender,email,id);  -- 【建索引时将条件中关于范围比较的字段放到后面】
Query OK, 0 rows affected (11.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(*) from s1 where name='egon' and gender='male' and id>333 and email='xxx';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)  -- 【建索引时将条件中关于范围比较的字段放到后面，查询速度很快】

1.7 其他情况

-- 使用函数
	select * from tb1 where reverse(email) = 'egon';
    
-- 类型不一致
	如果列是字符串类型，传入条件必须用引号引起来，否则...
	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 类型，必须制定长度

2 其他注意事项

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

六联合索引与覆盖索引

1 联合索引

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

mysql> create table t(
    a int,
    b int,
    primary key(a),
    key idx_a_b(a,b)
	);

	那么何时需要使用联合索引呢？在讨论这个问题之前。先看一下联合索引内部的结果。从本质上来说，联合索引就是一棵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 |       |
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
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 |
+----+-------------+---------+------+-----------------+----------+---------+-------+------+--------------------------+
1 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;

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 = 'egon';  # id字段有索引，但是name字段没有索引,该sql命中了索引，但未覆盖，需要去聚集索引中再查找详细信息。
# 最牛逼的情况是，索引字段覆盖了所有，那全程通过索引来加速查询以及获取结果就ok了
mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | 0       |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

mysql> explain select name from s1 where id=1000;  -- 【没有任何索引】
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | s1    | ALL  | NULL          | NULL | NULL    | NULL | 2987134 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (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 | type | possible_keys | key    | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+
|  1 | SIMPLE      | s1    | ref  | idx_id        | idx_id | 4       | const |    1 | NULL  |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain select id from s1 where id=1000;  -- 【在辅助索引中就找到了全部信息，Using index代表覆盖索引】
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | s1    | ref  | idx_id        | idx_id | 4       | const |    1 | Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.00 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 |
+----+-------------+---------+-------+---------------+--------+---------+------+------+-------------+
1 row in set (0.00 sec)

	InooDB存储引擎并不会选择通过查询聚集索引来进行统计。由于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 |
+----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

七查询优化神器-explain

	关于explain命令，具体用法和字段含义可以参考官网[explain-output](http://dev.mysql.com/doc/refman/5.5/en/explain-output.html)，这里需要强调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(走索引)

八慢查询优化的基本步骤**

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

posted @ 2021-06-25 18:00 越关山阅读(82) 评论(0) 收藏举报

刷新页面返回顶部

上士闻道

勤而行之

索引原理与慢查询优化

索引原理与慢查询优化

一 索引原理

1 为何要用索引

2 什么是索引

3 索引的原理

4 索引的数据结构

4.1 B+树：

4.2 b+树的查找过程：

4.3 B+树性质：

二 聚集索引和辅助索引

1 聚集索引

1.1 聚集索引的优点1：

1.2 聚集索引的优点2：

2 辅助索引

三 MySQL索引管理

1 功能

2 MySQL常用的索引

2.1 普通索引：

2.2 唯一索引：

2.3 联合索引：

3 索引的两大类型hash与btree

4 创建/删除索引的语法

4.1 创建索引的三种方法

4.1.1 方法一：创建表时

4.1.2 方法二：CREATE ，在已存在的表上建立索引

4.1.3 方法一：ALTER TABLE ，在已存在的表上创建索引

4.2 删除索引的语法

四 测试索引

1 准备

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

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

4 在索引建立完毕后，以该字段为查询条件时

5 总结

五 正确使用索引

1 索引未命中

1.1 范围问题

1.1.1 大于号、小于号

1.1.2 不等于!=

1.1.3 between...and...

1.1.4 like

1.2 尽量选择区分度高的列作为索引

1.3 = 和 in 可以乱序

1.4 索引列不能参与计算

1.5 and/or

1.6 最左前缀匹配原则

1.7 其他情况

2 其他注意事项

六 联合索引与覆盖索引

1 联合索引

2 覆盖索引

七 查询优化神器-explain

八 慢查询优化的基本步骤**

公告

一索引原理

二聚集索引和辅助索引

四测试索引

五正确使用索引

1.3 `=` 和 `in` 可以乱序

六联合索引与覆盖索引

七查询优化神器-explain

八慢查询优化的基本步骤**