MySQL-索引

十一、索引

11.1 索引概述

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构。这些数据结构以某种方式引用（指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

优势	劣势
提高数据检索的效率，降低数据库的IO成本	索引列也是要占用空间的。
通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗。	索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行insert、update、delete时，效率降低。

11.2 索引介绍

MySQL得索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持B+树索引
Hash索引	底层数据结构使用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree（空间索引）	空间索引时MyISAM引擎的一个特殊索引类型。主要用于地理空间数据类型，通常用得比较少
Full-text（全文索引）	是一种通过建立倒排索引，快速匹配文档得方式，类似于Lucene，Solr，ES

索引	Inndb	myisam	memory
B+tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-tree索引	不支持	支持	不支持
Full-text	5.6后支持	支持	不支持

11.3 索引结构

11.3.1 B树

具体演示：https://www.cs.usfca.edu/~galles/visualization/BTree.html

11.3.2 B+树

https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

11.3.3 Hash索引

11.4 索引分类

聚集索引下面挂的是这一行的数据
二级索引下面挂的是这个数据对应的主键


回表查询：先根据二级索引获得对应的主键值，再根据聚集索引将获得的主键值对应的数据查询出来。

11.5 索引语法

-- 创建索引
create [unique|fulltext] index index_name on table_name(index_col_name,...);
     	  -- 字段名
          
          
-- 查看索引
show index from table_name;


-- 删除索引
drop index index_name on table_name;

11.6 SQL性能分析

11.6.1 SQL执行频率

-- SQL执行频率
/*
MySQL客户端连接成功后，通过show[session|global] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的insert、update、delete、select的访问频次
*/

show global status like'Com_______';
-- like后面一个下划线表示一个字符，如果想知道select查询了多少次，就需要6+1个下划线

11.6.2 慢日志查询

-- 慢查询日志
/*
慢查询日志记录了所有执行时间超过指定参数（long_query_time,单位：秒，默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息：
#开启MySQL慢查询日志开关
show query_log=1

#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完成之后，通过以下指令重启MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/qiqi-slow.log
*/

-- 查看慢查询日志是否打开
show variables like 'slow_query_log';



/*
当查询的表格数据很大时，会查询的时间也会很久，当查询时间超过慢日志设置的时间时，该查询就会被记录在慢日志文件中
*/

11.6.3 profile详情

-- profile详情


-- show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。


-- 通过have_profiling参数，能够看到当前MySQL是否支持profile操作：
select @@have_profiling;


-- 查看profiling是否打开
select @@profiling;
-- 0为关闭，1为打开

-- 默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：

set [session|global] profiling=1;

-- profile详情操作

#查看每一条SQL的耗时基本情况
show profiles;

#查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

-- query_id是下面第一张图的Query_ID,表示查询的id

#查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

11.6.4 explain执行计划

-- explain或者desc命令获取MySQL如何执行select语句的信息，包括select语句执行过程中表如何连接和连接的顺序。

-- 语法：
#直接在select语句之前加上关键字explain/desc
explain select 字段列表 from 表名 where 条件;


-- explain执行计划各字段含义

#id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序
-- (id相同，执行顺序从上到下；id不同，值越大，越先执行)

#select——type：表示select的类型，常见的取值由simple（简单表，即不使用表连接或者子查询）、primary（主查询，即外层的查询表）、union（union中的第二个或者后面的查询语句）、subquery（select/where之后包含了子查询）等

#type：表示连接类型，性能由好到差的连接类型为：null、system、const、eq_ref、ref、range、index、all

-- 不访问任何表的连接类型性能是最高的，比如：
select 'A';
explain select 'A';

-- 查询系统表会出现system
-- 主键和唯一索引会出现const
explain select * from tb_user where phone = '17799990012'
-- 非唯一性的会出现ref
explain select * from tb_user where name = '白起';

-- 全表扫描会出现all


#possible_key：显示可能应用在这张表上的索引，一个或多个

#key：实际使用的索引，如果为null，则没有使用索引

#key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失准确性的前提下，长度越短越好

#rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。

#filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好

11.7 索引使用

-- 创建索引就是将表中的数据放在一个数据结构![请添加图片描述](https://img-blog.csdnimg.cn/a335888824e3482c8210a3fa3c09095e.png)
（比如B树）中，从而提高查询效率。

####11.7.1 最左前缀法

-- 索引使用规则

/*
最左前缀法：
	如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）。
*/	
-- 	比如下面的profession、age跟status是联合索引，而profession是最左列的。
	
-- 这句不会失效，因为没有跳过profession列
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

-- 这句也不会
explain select * from tb_user where profession = '软件工程' and age = 31;

-- 这句会
explain select * from tb_user where age = 31 and status = '0';

-- 而这句中status会失效，因为跳过了status左边的age列，这个可以跟只查询profession的key_len比较一下（最后两个）。
explain select * from tb_user where profession = '软件工程' and status = '0';

-- 另外，下面两句结果是一样的，说明跟条件顺序无关，出现即可。
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程';

11.7.2 范围查询

-- 范围查询
/*
联合索引中，出现范围查询(>,<),范围查询右侧的列索引失效
*/

-- 对于这句，age列索引会失效，因为用了'>'
explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';

-- 而下面一句则不会，因为用的是'<='
explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';

11.7.3 索引列运算

-- 索引列运算
/* 如果在索引列上进行运算操作，索引将会失效*/

explain select * from tb_user where phone = '17799990023';



explain select * from tb_user where substring(phone,10,2) = '23';

11.7.4 字符串不加引号

-- 字符串不加引号
/* 字符串类型字段使用时不加引号，索引将失效*/

-- 查询倒是可以查出来
select * from tb_user where phone = 17799990023;

-- 但是索引失效
explain select * from tb_user where phone = 17799990023;

11.7.5 模糊查询

-- 模糊查询
/* 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效*/


-- 尾部模糊索引
explain select * from tb_user where profession like '软件%';


-- 头部模糊索引（索引失效）
explain select * from tb_user where profession like '%工程';

11.7.6 or连接的条件

-- 用or分割开的条件，如果or前的条件中的列有所索引，而后面的列中没有索引，那么涉及的索引都不会被用到

-- 第一张图先看看表中的索引有哪些。
show index from tb_user;


-- 执行下面的语句
explain select * from tb_user where id = 10 or age = 23;
/*
可能用到的索引为primary，实际用到的为null
因为id有主键索引，而age没有单独的索引，它只有联合索引，而联合索引必须联合的字段都要有出现在条件中，所以没有用到索引。

解决办法：给age创建索引。
create index idx_user_age on tb_user(age);

总结：or连接的条件中出现没有索引的字段，则索引都失效
*/

11.7.7 数据分布影响

-- 如果MySQL评估使用索引比全表更慢，则不使用索引

/*
就是MySQL会根据数据的查询数据的多少来选择是否用索引
如果查询的内容表格中大部分都满足，那么MySQL会选择全表扫描
如果只有少部分满足，就会使用索引
*/

explain select * from tb_user where profession is null;


explain select * from tb_user where profession is not null;

11.7.8 SQL提示

-- SQL提示是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

/*
在不使用SQL提示的情况下：
	比如我给profession创建了一个联合索引，又创建了一个列索引。当我的查询条件为profession时，MySQL会根据最优原则来选择一个索引
*/

explain select * from tb_user where profession = '软件工程';


#SQL提示(写在表名后面)
-- use index:
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

-- use index只是我们给MySQL的建议，至于MySQL接不接受，MySQL还得权衡一下效率

-- ignore index
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

-- force index，强制使用索引
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

11.7.9 覆盖索引

-- 尽量使用覆盖索引，减少select*
/* 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到*/

-- 说白了就是select具体的字段，不要用通配符。


-- 下面两句的计划执行是一致的，但第三句
explain select id,profession from tb_user where profession = '软件工程' and age = 31;

explain select id,profession,age from tb_user where profession = '软件工程' and age = 31;

explain select * from tb_user where profession = '软件工程' and age = 31;


-- 在Extra列
/*
using index condition:查找使用了索引，但是需要回表查询数据

using where;using index:查找使用了索引，但是需要的数据都在索引列中都找到，所以不需要回表查询数据
*/

11.7.10 前缀索引

-- 当字段类型为字符串时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。


-- 语法：
create index idx_xxxx on table_name(column(n));

-- 前缀长度
/*可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的*/

-- 选择性计算
select count(distinct email)/count(*) from tb_user;
select count(distinct substring(email,1,5))/count(*) from tb_user;

11.7.11 单列索引和联合索引

/*
在业务场景中，如果存在多个查询条件，考虑针对查询字段建立索引时，建议建立联合索引。
*/

11.7.12 索引设计原则

1. 针对数据量较大，且查询比较频繁的表建立索引。
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
4. 如果时字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引。
5. 尽量使用联合索引、减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引越多，维护索引结构的代价就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值，就需要在创建表时用not null约束它。当优化器知道每列是否包含null值时，它可以更好地确定哪个索引最有效地用于查询