MySQL索引

**

数据库索引

**

在理解数据库索引之前，先理解下面的数据结构：

1）B树

B树中每个节点包含了键值和键值对指示数据对象存储的地址指针，所以成功搜索一个对象可以不用到达树的叶节点。

成功搜索包括节点内搜索和沿某一路径的搜索，成功搜索时间取决于关键码所在的层次以及节点内关键码的数量。

在B树中查找给定关键字的方法是：首先把根结点取来，在根结点所包含的关键字K1,…,kj查找给定的关键字（可用顺序查找或二分查找法），若找到等于给定值的关键字，则查找成功；否则，一定可以确定要查的关键字在某个Ki或Ki+1之间，于是取Pi所指的下一层索引节点块继续查找，直到找到，或指针Pi为空时查找失败。

2）B+树

B+树非叶节点中存放的关键码并不指示数据对象的地址指针，非叶节点只是索引部分。所有的叶节点在同一层上，包含了全部关键码和相应数据对象的存放地址指针，且叶节点按关键码从小到大顺序链接。如果实际数据对象按加入的顺序存储而不是按关键码次序存储的话，叶节点的索引必须是稠密索引，若实际数据存储按关键码次序存放的话，叶节点索引时稀疏索引。

B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。

所以 B+树有两种搜索方法：

一种是按叶节点自己拉起的链表顺序搜索。

一种是从根节点开始搜索，和B树类似，不过如果非叶节点的关键码等于给定值，搜索并不停止，而是继续沿右指针，一直查到叶节点上的关键码。所以无论搜索是否成功，都将走完树的所有层。

B+ 树中，数据对象的插入和删除仅在叶节点上进行。

这两种处理索引的数据结构的不同之处：
a，B树中同一键值不会出现多次，并且它有可能出现在叶结点，也有可能出现在非叶结点中。而B+树的键一定会出现在叶结点中，并且有可能在非叶结点中也有可能重复出现，以维持B+树的平衡。
b，因为B树键位置不定，且在整个树结构中只出现一次，虽然可以节省存储空间，但使得在插入、删除操作复杂度明显增加。B+树相比来说是一种较好的折中。
c，B树的查询效率与键在树中的位置有关，最大时间复杂度与B+树相同(在叶结点的时候)，最小时间复杂度为1(在根结点的时候)。而B+树的时间复杂度对某建成的树是固定的。

定义：索引（Index）是帮助MySQL高效获取数据的数据结构。

本质：数据结构

索引的目的在于提高查询效率，可以类比字典

你可以简单理解为“排好序的快速查找数据结构”

即数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种种数据结构就是索引。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

3．索引的类型

**A）聚集索引，**表数据按照索引的顺序来存储的。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。

**B）非聚集索引，**表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，该层紧邻数据页，其行数量与数据表行数据量一致。

在一张表上只能创建一个聚集索引，因为真实数据的物理顺序只可能是一种。如果一张表没有聚集索引，那么它被称为“堆集”（Heap）。这样的表中的数据行没有特定的顺序，所有的新行将被添加到表的末尾位置。

索引的优点和缺点：

​ 优点：提高数据检索的效率，降低数据库的IO成本

​ 降低数据排序的成本，降低了CPU的消耗

​ 缺点：实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

​ 虽然索引大大提高了查询速度，但同时会降低更新表的速度。因为MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引文件、每次更新添加了索引列的字段

​ 索引只是提高效率的一个因素，如果MySQL中有大数据量的表，就需要花时间研究建立更好的索引，或者优化索引

索引的分类

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

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

复合索引：即一个索引包含多个列

基本语法：

有四种方式来添加数据表的索引：

MySQL索引结构

BTree索引：

一棵B+树，浅蓝色的块称之为一个磁盘块，每个磁盘块包含几个数据项（深蓝色）和指针（黄色），而真实的数据存在于叶子节点，而非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17,35

Hash索引

full-text全文索引

R-Tree索引

哪些情况适合建立索引：

1. 主键自动建立唯一索引

2. 频繁作为查询条件的字段应该创建索引

3. 查询中与其他表关联的字段，外键关系建立索引

4. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

5. 查询中统计或者分组字段

   哪些情况不适合建立索引：

   1. 频繁更新的字段不适合建立索引
   2. Where条件里用不到的字段不创建索引
   3. 表记录太少
   4. 数据重复且分布平均的表字段

性能分析：

MySQL Query Optimizer：MySQL默认的查询优化器，以它认为最优的方式执行计划

MySQL常见瓶颈：

​ CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候

​ IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

​ 服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态

Explain：

​ 是什么（查看执行计划）：使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

​ 能干嘛：

​ 表的读取顺序

​ 数据读取操作的操作类型

​ 哪些索引可以使用

​ 哪些索引被实际使用

​ 表之间的引用

​ 每张表有多少行被优化器查询

​ 怎么用：explain + SQL语句

​ 执行计划包含的信息（即下图的各列名）

​ 各字段解释：

id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

三种情况：

1. id相同，执行顺序由上至下
2. id不相同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3. id相同不相同，同时存在。 id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行 衍生=derived

select_type:查询的类型，主要用于区别普通查询、联合查询、子查询等复杂查询

有哪些：

SIMPLE：简单的select查询，查询中不包含子查询或者UNION

PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY（即最后加载的）

SUBQUERY：在select或where列表中包含了子查询

DERIVED：在from列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表里。

UNION：若第二个select出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层select将被标记为：DERIIVED

UNION RESULT:从UNION表获取结果的select（即两个union合并的结果集）

type：

访问类型排列：

显示查询使用了何种类型，从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL（常见的）

一般来说，得保证查询至少达到range级别，最好能达到ref

system：表只有一行记录（等于系统表），这是const类型的特例，一般不会出现，这个可以忽略不计。

const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。 如果将主键置于where列表中，MySQL就能将该查询转换为一个常量。

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

ref：非唯一性索引扫描，返回匹配某个单独值的所有行。 本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。

range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在where语句中出现了between、>、<、in等的查询。它比全表扫描要好，因为它开始于索引的某一点，结束于另一点，不用全表扫描全部索引。

index：index与ALL的区别在于index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。也就是说，虽然ALL和index都是全表扫描，但是index是从索引中读取，而ALL是从硬盘中读的。

ALL：全表扫描以找到匹配的行

possible_keys:显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。 即理论上应有多少个（由mysql推测）

key：实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中（覆盖索引：创建的索引数量和顺序和要查找的列完全吻合，则为覆盖索引，类型为index）

key_len:表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得的，而不是通过表内检索出的。

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

Extra：包含不适合在其他列中显示但十分重要的额外信息。

Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序称为“文件排序”。 （出现的话不好，尽量不要出现查找顺序与索引顺序不同）

Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于order by和分组查询group by

USING index：表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率不错！ 如果同时出现using where，表明索引被用来执行索引键值的查找。如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

Using where：表明使用了where过滤

using join buffer：表明使用了连接缓存

impossible where：即where子句的值总是false，不能用来获取任何元组（即让MySQL错乱了，比如where username=‘啊啊’ and username=‘哈哈’）

select tables optimized：在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT（*）操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

distinct:在找到第一个匹配的元组后即停止找同样值的操作

例子1：索引单表优化

CREATE TABLE IF NOT EXISTS article(
	id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	author_id INT(10) UNSIGNED NOT NULL,
	category_id INT(10) UNSIGNED NOT NULL,
	views INT(10) UNSIGNED NOT NULL,
	comments INT(10) UNSIGNED NOT NULL,
	title VARBINARY(255) NOT NULL,
	content TEXT NOT NULL
);

INSERT INTO article(author_id,category_id,views,comments,title,content)VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');


SELECT * FROM article;

#查询category_id为1且comments大于1的情况下，views最多的article_id。
SELECT id,author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;
EXPLAIN SELECT id,author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;
#结论：type是ALL，即全表查询。Extra里也出现了Using filesort，也是最坏的情况，应该优化
SHOW INDEX FROM article;

#开始优化
CREATE INDEX idx_article_ccv ON article(category_id,comments,views);
SHOW INDEX FROM article;
EXPLAIN SELECT id,author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;
#解决了全表扫描问题，但Using filesort问题没解决
DROP INDEX idx_article_ccv ON article;#删除索引
SHOW INDEX FROM article;

#重新进行优化
CREATE INDEX idx_article_cv ON article(category_id,views);
SHOW INDEX FROM article;
EXPLAIN SELECT id,author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;
#消除了Using filesort问题 type也变成了ref

例子2：索引双表优化

CREATE TABLE IF NOT EXISTS class(
	id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	card INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY(id)
);

CREATE TABLE IF NOT EXISTS book(
	bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	card INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY(bookid)
);

INSERT INTO class(card) VALUES
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20)));

INSERT INTO book(card) VALUES
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20)));

SELECT * FROM book INNER JOIN class ON book.card=class.`card`;
SELECT * FROM book LEFT JOIN class ON book.card=class.`card`;

#开始优化
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.`card`=book.card;
#type有ALL
#添加索引优化
#将索引添加在book表card列上
ALTER TABLE book ADD INDEX Y(card);
SHOW INDEX FROM book;
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.`card`=book.card;
#type变成了ref，Extra变成了Using index
DROP INDEX Y ON book;
#将索引加在右表card列上
ALTER TABLE class ADD INDEX Y(card);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.`card`=book.card;
#type还是ALL

#总结：左连接建立索引用右边表的，这是由左连接特性决定的。LEFT JOIN条件用于
#确定如何从右表搜索行，左边一定都有，所以右边是关键点，一定要建索引。
DROP INDEX Y ON class;

EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.`card`=book.card;
ALTER TABLE book ADD INDEX Y(card);
EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.`card`=book.card;
DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX Y(card);
EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.`card`=book.card;
#总结：右连接建立在左表

例子3：索引三表优化

CREATE TABLE IF NOT EXISTS phone(
	phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	card INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY(phoneid)
)ENGINE=INNODB;

INSERT INTO phone(card) VALUES
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20))),
(FLOOR(1+(RAND()*20)));

SHOW INDEX FROM class;
DROP INDEX Y ON class;

SELECT * FROM class LEFT JOIN book ON class.card=book.`card` LEFT JOIN phone ON 
book.`card` = phone.`card`;
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.`card` LEFT JOIN phone ON 
book.`card` = phone.`card`;
#全是 全表查询ALL
ALTER TABLE phone ADD INDEX z(card);
ALTER TABLE book ADD INDEX Y(card);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.`card` LEFT JOIN phone ON 
book.`card` = phone.`card`;
#type变成了ref，因此索引最好设置在需要经常查询的字段中

#总结：
#尽可能减少join语句中的循环总次数：“永远用小结果集驱动大的结果集”（即小表驱动大表）
#优先内层循环
#保证join语句中被驱动表上join条件字段已经被索引