MySQL索引

一、索引

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

试想一下，如果没有索引的话，数据库查询数据的情形：

select * from user where age = 20

数据库会根据age这个字段，从头到尾，依次遍历，这无疑是非常耗费时间的。
所以，我们需要创建一个索引，（先了解索引的数据结构，再创建索引。）

二、索引的数据结构

1.B+树索引

上面提到索引是一种数据结构，那么索引到底是哪种数据结构呢？
二叉树？

有熟悉数据结构的同学肯定想到了使用树这个结构来构建索引，因为树可以提高搜索速度。但是，只答对了一半。
让我们假设使用树结构
如果数据过多，比如几千万条，那么树的深度是否就会很深，树的深度越深，就代表遍历时的次数越多，这显然不是最好的解决办法。

B树？
数据结构了解的多的同学可能会想到，既然深度太深会影响性能，那么我使用B树，每个节点按顺序多存几个数据不就行了？其实这已经很接近答案了。

以上图最大度数为5（度数是一个节点的子节点个数）的B树为例。当存储图中这些数据的时候，深度才为2，这可以大大提高检索速度。
B+树！！
最后是众望所归，B+树 是B树的升级版

上图是B+树的数据结构，可以看到它和B树十分相似。
区别在于：
①B树的每个节点都会存储原始数据（就是图中绿色的小方块），而B+树只有叶子节点才会存放数据
②B+树在叶子节点上增加了一个指向相邻叶子节点的指针，形成了带有顺序指针的B+树
③所有数据都会在叶子节点存一份

对B树，B+树有兴趣的同学可以去了解这两种树的具体存储数据的方式。

2.哈希索引

除了B+树索引外，还有一种常见的索引——hash索引，hash索引存储

相信大家有了解过HashMap这种数据结构。
以上图为例，以name为索引，数据库会计算name的hash值，并存放在hash表的槽位中，槽位中存放的数据除了name之外还有当前name这行数据的地址值。
出现了hash碰撞的情况也和hashmap类似，也是创建链表。

哈希索引的特点：
1.hash索引只能用于对等比较（=，in），不支持范围查询（between，>，<）
2.无法利用索引完成排序操作，因为hashmap这种数据结构是无序的
3.查询效率高，通常只需要一次查询（无碰撞的情况下）

目前只有Memory引擎支持Hash索引，但是InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

3.索引的优缺点

优点

• 提高数据检索的效率，降低数据库的IO成本
• 通过索引列队数据进行排序，降低数据排序的成本，降低CPU消耗
  缺点
• 索引列也需要占空间（但是磁盘空间这么便宜，不差这一点）
• 索引虽然大大提高了查询效率，但是降低了表的更新速度，比如update，insert，delete，原因是表中数据更新了，索引也要更新。（但是一般的数据库系统中还是查询操作偏多，所以索引还是得用）

4.索引结构的分类和存储引擎的支持

讲一下存储引擎和其支持的索引类型情况，直接放上黑马的两张图

5.思考题

为什么InnoDB使用B+树作为索引结构？

• 相对于二叉树，层级更少，搜索效率高;
• 对于B-tree,无论是叶子节点还是非叶子节点，都会保存数据，这样导致一
  页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的
  高度，导致性能降低;

三、索引的分类

注意是索引的分类不是索引数据结构的分类哈
主键索引、唯一索引、常规索引、全文索引

直接上黑马图！

唯一索引：当你为某个字段创建了唯一约束时，数据库会自动为其创建唯一索引,比如：当前商品，一个用户只能购买一件。我们将用户id及商品id列设置成唯一索引。那么就可以避免一个用户出现重复购买的情况。

在InnoDB存储引擎中，根据索引存储形式，又可以分为以下两种：

聚集索引的选取规则：

• 如果存在主键，则主键索引就是聚集索引
• 如果不存在主键，则第一个唯一索引就是聚集索引
• 如果既没有主键也没有唯一索引，则InnoDB会为我们自动生成一个rowId作为隐藏的聚集索引

1.聚集索引

聚集索引就是以主键创建索引，在B+树的叶子节点底层挂载的是主键对应的这一行的数据

2.二级索引

二级索引是除了聚集索引之外的统称，它是以表中的字段创建的索引，叶子节点下面挂载的是该索引对应的行的主键值。

如果执行下面的sql语句

select * from user where name = 'Arm'

数据库会现在二级索引种去查找‘Arm’对应的主键id，查到主键id之后再去聚集索引中去查找主键id对应的行数据。这个过程有一个专业的名词——回表查询

3.思考题

在上图的表中查询数据，以下哪条sql语句执行效率高？

select * from user where id = 10;
select * from user where name = 'Arm';

答案是第一条sql效率高，原因是第一条sql直接在聚集索引中查询数据，而第二条sql先要在二级索引中查询数据对应主键id，然后再去聚集索引中查询主键id对应数据，经历了回表查询。

四、创建、删除索引的语法

#创建索引
#创建 [唯一索引|全文索引]可选值，也可以不写 INDEX 索引名称 ON 表名（字段名，可以有多个（联合索引））
CREATE [UNIQUE|FULLTEXT] INDEX INDEX_NAME ON TABLE_NAME (INDEX_COL_NAME....);

#查看索引
SHOW INDEX FROM TABLE_NAME;

#删除索引
DROP INDEX INDEX_NAME ON TABLE_NAME;
CREATE [UNIQUE|FULLTEXT] INDEX IDX_NAME ON 

五、SQL性能分析

1.查看数据库执行频次

通常情况下我们会对数据库的查询操作进行优化，而有的数据库查询操作很少，这就失去了优化的意义，所以我们可以通过一个SQL语句来查看当前数据库的SQL执行情况

#查询当前数据库SQL执行频次
SHOW [SESSION|GLOBAL] STATUS LIKE 'com_______'

注意：com后面是7个连续的下划线

从上图的查询结果就可以看出当前数据库有11次select查询操作，就可以说明当前数据库的查询操作偏多

2.慢查询日志

MySQL提供了一个慢查询日志功能，慢查询日志记录了所有执行时间超过指定参数(long_ query_ time, 单位:秒,默认10秒)的所有SQL语句的日志。
在MySQL中默认没有开启慢查询日志功能，需要我们在MySQL的配置文件（/etc/my.cnf）中去配置以下信息：

#开启慢查询日志开关
slow_query_log=1;
#设置慢查询日志的时间为2秒，SQL语句执行时间超过两秒就会视为慢查询，记录慢查询日志
long_query_time=2;
#查询是否开启了慢查询配置
show variables like 'slow_query_log';

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log（不一定是localhost，后缀是-slow.log）
以上是Linux的文件目录。
举例：

上图是执行了一个13秒的语句之后生成的慢查询日志。
日志中有显示root用户名，localhost主机名，Query_time语句执行的时间，SET timestamp什么时间执行的，最下面是执行的SQL语句。

3.profile详情

在使用慢查询日志的时候有一个弊端，比如我们设置了2秒的慢查询参数，而有一条SQL语句执行时间为1.999秒，此时慢查询日志仍然不会记录这条SQL语句，但是明显这条SQL语句很慢。

MySQL提供了一个profile功能，profile可以在我们做SQL优化时帮助我们了解时间具体耗费在哪一个部分，通过have_profiling参数可以查看当前的MySQL是否支持profile。

SELECT @@have_profiling;

结果为YES说明支持。
默认profiling是关闭的，可以通过set语句在session/ global级别开启profiling:

SET profiling = 1;

3.1profile操作

#查看每一条SQL语句的基本耗时情况
show profiles;
#查看指定的query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
#查看指定的query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

查询17号SQL的执行详情，可以看到每个部分的具体执行时间。

在profile后面添加一个cpu就可以查询到cpu的使用情况。

4.explain

上面的profile功能是通过时间的层面来判断SQL的执行功能的，这种判定只是粗略的判定，还需要explain功能来分析SQL语句的执行计划。

#直接在查询语句之前加上关键字explain|desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE　条件;

例如使用explain分析

explain select s.*,C.* from student s, course c,student_course sc 
where s.id = sc.studentid and c. id = sc. courseid;

通过explain分析出来的结果如上图所示：
explain出来的各个字段的含义：

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

explain select * from student s where s.id in 
(select student id from student course sc where sc.courseid = 
(select id from course C where c.name = 'MySQL') ) ;

• select_type
  表示SELECT的类型，常见的取值有SIMPLE ( 简单表，即不使用表连接或者子查询)、PRIMARY (主查询，即外层的查询)、UNION (UNION 中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询)等
• type
  表示连接类型，性能由好到差的连接类型为NULL、system、 const、 eq_ ref、 ref、 range、index、all 。
  从左到右，性能越低。

#NULL：通常是直接查询一个数据会出现的type
如 select 'A';
#system：通常是查询系统参数会出现的type
如 select @@have_profiling
#const：通常是通过主键索引或者唯一索引查询会出现的type
#ref：通常是使用非唯一性索引会出现的type
#index：是指用了索引，但是也对索引树做了扫描，遍历了整个索引树
#all：是指做了全表扫描

• possible_key
  显示可能应用在查询这张表上的索引，一个或多个
• key
  实际用到的索引，如果为NULL，则没有使用索引
• key_len
  使用到的索引的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
• rows
  MySQL认为必须要执行查询的行数,在innodb引擎的表中，是一个估计值，可能并不总是准确的。
• filtered
  表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。

六、索引的使用规则(索引失效）

1.最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则，最左前缀法则值得是查询从索引的最左列开始，并且不跳过索引中的列，如果跳过了某一列，索引将部分失效（后面的索引失效）
以下是一个示例，说明最左前缀法则的应用。假设有一个联合索引 (column1, column2)，如果你的查询只包括 column2，而不包括 column1，那么索引 (column1, column2) 将无法用于优化查询。

-- 索引能够有效支持的查询
SELECT * FROM table_name WHERE column1 = 'value' AND column2 = 'value';

-- 索引无法有效支持的查询（违反最左前缀法则）
SELECT * FROM table_name WHERE column2 = 'value';

1.1 范围查询

联合索引中，出现范围查询 (>,<) ,范围查询右侧的列索引失效
例子：
假设你有一个包含两列的表 orders，包含订单信息，而且你创建了一个联合索引在 (order_date, order_amount) 上。这个索引的左侧列是订单日期，右侧列是订单金额。

现在，考虑以下查询：

SELECT * FROM orders WHERE order_amount > 100;

这个查询尝试查找所有订单金额大于100的订单记录。问题在于，虽然 (order_date, order_amount) 索引包含了 order_amount 列，但它是索引的右侧列。因此，这个查询无法充分利用索引，因为它没有使用索引的最左侧列 order_date。结果是，数据库可能会执行全表扫描，逐一检查每一行以查找符合条件的订单，而不是使用索引来快速定位。

1.2 索引的列运算

在索引的列上进行运算操作，索引将失效。

1.3 字符串不加引号

字符串字段使用时不加引号会出现隐式转换，索引将失效

1.4 模糊查询

如果使用头部模糊查询，索引将失效，如’%软件’

1.5 or连接的条件

如果条件中有or，只要其中一个条件没有索引，其他字段有索引也不会使用。

1.6 数据分布影响

如果数据库评估使用索引比全表查询还慢，则不适用索引

2.索引的sql提示

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

• use index
  使用use index() 建议数据库使用括号中的索引

select * from tb_user use index(idx_user_pro) where profession = '软件工程'

• ignore index
  使用 ignore index() 命令数据库忽略括号中的索引

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

• force index：
  使用 force index() 强制数据库使用括号中的索引

select * from tb_user force index(idx_user_pro) where profession = '软件工程'

3.覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列在该索引中已经能够全部找到）
试想一下，如果要查询的列在索引中能够全部找到，那么在找到需要的数据后就会直接返回；
如果要查询的列在索引中没有，那么查询到了数据，还得拿着数据对应的id去主键索引中去找，这个过程我们称为回表查询，这是我们尽量要避免的。所以我们也要 尽量避免使用select *

我们使用explain对select语句分析的时候，会在结果表中的extra字段出看到提示信息：
using index condition|NULL:查找使用了索引，但是需要回表查询数据
using where; using index:查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

4.思考题

一张表,有四个字段(id, username, password, status),由于数据量大,需要对以下SQL语句进行优化,该如何进行才是最优方案:

select id,username,password from tb_user where username = 'itcast'

答案：
我们应该简历username和password的联合索引，这样的话我们就可以查询到username和password的数据，在叶子节点处还能获得对应的主键id，不需要回表查询。

5.前缀索引

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

#创建前缀索引的语法和创建普通索引的语法类似，只是在字段的后面加了一个括号，并且在括号中填写要截取的前缀长度
create index idx_xxxx on table_ name(column(n)) ;

那么这个括号中的n的大小要如何确定才能达到最佳呢？
可以根据索引的选择性来决定,而选择性是指不重复的索引值( 基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
可以通过以下SQL语句进行确定

select count(distinct substring(email,0,n))/count(*) from tb_user;

6.单列索引和联合索引的取舍

单列索引：即一个索引只包含单个列
联合索引：即一个索引包含了多个列
在业务场景中,如果存在多个查询条件,考虑针对于查询字段建立索引时，建议建立联合索引,而非单列索引。

5.1 联合索引的B+树结构

索引结构的排序会先根据phone排，然后再根据name排序

七、索引的设计原则

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