【MySQL数据库】索引

二、索引简介：

1、是什么

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

可以得到索引的本质：索引是数据结构。

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

索引会影响到sql语句里面的查找和排序。

order by 有双路复用和单路复用两种算法。

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

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

**我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。**其中聚集索引、次要索引、复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引等。

2、优势

• 提高检索效率，降低数据库的IO成本

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

3、劣势

• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。
• 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新i添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
• 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

4、MySQL索引分类

• 单值索引：一个索引只包含单个列，一个表可以有多个单列索引（身份证号，银行卡号）。一张表尽量别超过五个索引。同时只能用一个索引。

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

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

5、基本语法：

# 创建
create [unique] index indexName on mytable(columnname(length));
alter mytable add [unique] index [indexName] on (columnname(length));
# 删除
drop index [indexName] on mytable;
# 查看
show index from table_name\G
# ALTER命令
# 四种方式来添加数据表的索引：

#1、该语句添加一个主键，这意味着索引值必须是唯一的，且不能为null
alter table tbl_name add primary key (column_list);
#2、这条语句创建索引的值必须是唯一的（除了null外，null可能会出现多次）
alter table tbl_name add unique index_name (column_list)
#3、添加普通索引，索引值可出现多次
alter table tbl_name add index index_name (column_list)
#4、该语句指定了索引为FULLTEXT，用于全文索引
alter table tbl_name add fulltext index_name (column_list);

6、MySQL索引结构

• btree索引
• hash索引
• fulltext全文索引
• r-tree索引

7、哪些情况需要创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引（手机号，微信号）
3. 查询中与其它表关联的字段，外键关系建立索引
4. 频繁更新的字段不适合创建索引（因为每次更新不单单是更新了记录还会更新索引）
5. where 条件里用不到的字段不创建索引
6. 单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引）
7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
8. 查询中统计或者分组字段

8、哪些情况不要创建索引

1. 表记录太少
2. 经常增删改的表
   1. Why？提高了查询速度，同时会降低更新表的速度，如对表进行INSERT、UPDATE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
3. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。
4. 注意：如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

三、性能分析：

1、MySQL Query Optimizer

• MySQL中有专门负责优化select语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为最优的，这部分最耗费时间）
• 当客户端向MySQL请求一条query，命令解析器模块完成请求分类，区分出是select并转发给MySQL query optimizer时，MySQL query optimizer 首先会对整条query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析query中的hint信息（如果有），看显示hint信息是否可以完全确定该query的执行计划。如果没有hint或hint信息还不足以完全确定执行计划，则会读取所设计对象的统计信息，根据query进行写相应的计算分析，然后再得出最后的执行计划。

2、MySQL常见瓶颈

• CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候。
• io：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候。
• 服务器硬件的性能瓶颈：top，free，iostat和vmtat和vmstat来查看系统的性能状态。

3、Explain

1）是什么？

Explain可以查看sql语句的执行计划

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

2）能干嘛？

获取如下信息：

1. 表的读取顺序
2. 数据读取操作的操作类型
3. 哪些索引可以使用
4. 哪些索引被实际使用
5. 表之间的引用
6. 每张表有多少行被优化器查询

四、使用Explain

1、使用Explain

explain + SQL语句

explain select * from 表名;

2、explain各字段解释

1、id

select查询的序列号，包含一组数字，表示查询中执行操作表的语句的执行顺序。

• id如果相同，可以认为是一组，从上往下顺序执行；
• id如果不同，id值越大，优先级越高，越先执行；下面这个图，执行顺序是3->2->1

2、select_type

​ 有哪些：

• simple：简单的select查询，查询中不包含子查询或者union
• primary：查询中若包含任何复杂的子部分，最外层查询则被标记为primary（常常表示最外层加载的语句）
• subquery：在select或where列表中包含了子查询
• derived：在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放在临时表里。
• union：若第二个select出现在union之后，则标记为union；若union包含在from子句的子查询中，外层select将被标记为：derived（衍生）。
• union result：从union表获取结果的select

3、table

显示这一行的数据是关于哪张表

4、type 重点！！！

• all、index、range、ref、eq_ref、const，system、null
• 访问类型排列
• 显示查询使用了何种类型，从最好到最差依次是：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：**Full Index Scan 全索引扫描，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
  • all：全表扫描！！
  • 备注：一般来说，得保证查询至少达到range级别，最好能到达ref。

5、possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

6、key

实际使用的索引。如果为NULL，则没有使用索引，查询中若使用了覆盖索引，则该索引仅出现在key列表中。

7、key_len

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

8、ref

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

9、row

根据表统计信息索引选用情况，大致估算出找到所需的记录所需要读取的行数。（每张表有多少行被优化器查询。）越少越好

10、Extra

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

1. Using fliesort：（九死一生）说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为”文件排序“。
2. Using temporary：（十死无生，火烧眉毛，千钧一发）新建了一个内部的临时表。使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。要么别建索引，要么group by就要和索引一样。
3. USING index：（点赞）覆盖索引，很好的情况。表示相应的select操作使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
   1. 覆盖索引：
      1. 理解一：就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说，查询列要被所建的索引覆盖。
      2. 理解二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。
4. Using Where：使用了where过滤
5. Using join buffer：使用了连接缓存
6. impossible where：不可能，做不到，where子句的值始终为false
7. select tables optimized away：在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
8. distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

3、热身Case

案例一：搜索（某部门）的（男员工）（工资大于1000）的员工信息。

此时有三个字段，部门id、性别、工资

此时不创建索引时，是扫描全表 type为all。且Using fliesort使用了文件内排序

创建索引为（部门id、性别、工资）：扫描全表消失了，但是Using fliesort还在。原因是：工资是一个区间，所以启用了文件排序。

所以创建索引的方式为（部门id、性别）

五、索引优化案例

1. 优化案例

1、单表

1）建表SQL

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;

2）案例

# 查询category_id为1 且 comments大于1 的情况下，views 最多的article_id。
# 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，也是最坏情况。
# 2、查看当前表的索引
show index from article;
# 3、创建索引，where后面的字段
create index idx_article_ccv on article(category_id,comments,views);
#此时type变为了range范围查找，而extra还是存在using filesort
#提需求最好提等于，不要提范围
# 4、删除上面创建的索引，新建最优索引不要category_id
#此时检索和排序都用到了索引
drop index idx_article_ccv on article;
create index idx_article_ccv on article(category_id,views);

/*结论
1、type变成了range，这是可以接受的，但是extra里面使用了Using filesort仍然时无法接受的。
2、但是我们已经建立了索引，为啥没用呢？
原因：
	先排序category_id
	如果遇到相同的category_id则再排序comments，如果遇到相同的commennts则再排序views。
	当comments字段再联合索引里处于中间位置时。因comments>1条件是一个范围值（所谓range）。MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。
*/

2、两表

1）建表SQL

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)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));

2）案例

select * from book inner join class on book.card = class.card;
# 查看sql语句
explain select * from book inner join class on book.card = class.card;
#此时，type 有 all
# 1、添加索引，给book表创建一个索引名为Y的索引
alter table book add index idx_book_c(card);
#此时book部分的搜索：type变为ref，extra部分变为using index。此时比两个all强多了
# 2、删除book上的idx_book_c索引
drop index idx_book_c on book;
# 3、在class上创建相同字段的索引，测试一下
alter table class add index idx_class_c(card);
#此时class部分的搜索：type变为index，extra变为using index

/*结论：
1、可以看到第二行的type变为了reef，rows也变成了优化比较明显。
2、这是由左连接特性决定的。left join条件用于确定如何从右表搜索行，左边一定都有。此时右边是我们的关键点，一定需要建立索引。
3、二表查询的索引，左连接建右表，右连接建左表。
*/

3、三表

1）建表SQL

#比上面二表多一个表
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)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));

2）案例

# 删掉三个表的全部索引，开始测试

select * from class left join book on class.card = book.card left join phone on book.card = phone.card;
# 查看sql语句
explain select * from book inner join class on book.card = class.card;
# 全是全表扫描，裂开
# 创建索引
alter table phone add index idx_phone_c(card);
alter table book add index idx_book_c(card);
# 后两行的type都是ref且总rows优化更好，效果不错。因此索引最好设置在需要经常查询的字段中。

总结：

• 尽可能减少Join语句中的NestedLoop的循环总次数；“永远用小结果集驱动大的结果集”。小表驱动大表。
• 优先优化NestedLoop（嵌套循环）的内层循环。
• 保证Join语句中被驱动表上Join条件字段已经被索引。
• 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置。

2. ★索引失效（应该避免）

1. 全值匹配。

2. 最佳左前缀法则。

   1. 如果索引了多列，要遵循最佳左前缀法则。指的就是从索引的最左前列开始并且不跳过索引中的列。
   2. （火车头不能没有，一定要按顺序！）
   3. 

3. 不在索引上做任何操作（计算、函数、（自动或手动）类型转换（catstr()方法、left()方法）），会导致索引失效而转向全表扫描

4. 存储引擎不能使用索引中范围条件右边的列。按索引顺序查找搜索。

5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

6. mysql在使用不等于（!= 或者 <>）的时候，无法使用索引会导致全表扫描

7. is null，is not null 也无法使用索引。关键字段写默认值，一定要避免空值。

8. like 以通配符开头（’%abc…’）mysql索引失效，会变成全表扫描的操作。

   1. 

   2. 解决like '%字符串%'时索引不被使用的方法？？

   3. 对name和age创建索引。覆盖索引，全部搜索字段建索引。按照顺序。覆盖索引：建的索引和搜索的字段的顺序和个数内容完全一模一样。

9. 字符串不加单引号索引会失效。不能发生隐式的数据类型变换。

10. 少用or，用它来连接时会索引失效

11. 小总结 设 index(a,b,c)

Where语句	索引是否被使用
where a=3	Y，使用到a
where a=3 and b=5	Y，使用到a,b
where a=3 and b=5 and c=4	Y，使用到a,b,c
where b=3 或者 where b=3 and c=4 或者 where c=4	N
where a=3 and c=5	使用到a，但是c不可以，b中间断了
where a=3 and b>4 and c=5	使用到a和b，c不能用在范围之后，b断了
where a=3 and b like ‘kk%’ and c=4	a能用，b能用，c不能用

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-12jT9HPu-1612183890090)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210201105730453.png)]

常考：

定值、范围还是排序，一般order by是给个范围

group by 基本上都需要进行排序，会有临时表产生

3. 一般性建议

1. 对于单键索引，尽量选择针对当前query过滤性更好的索引；
2. 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好；
3. 在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引；
4. 尽可能通过分析统计信息和调节query的写法来达到选择合适索引的目的。

4. 优化总结口诀

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最后，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！