MySQL进阶篇学习笔记（二）-- 索引

一、索引概述

        索引是一种有序的数据结构，用来帮助MySQL高效的获取数据，除了数据之外，数据库还需要维护满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法。

        没有索引的查询语句是在整张表中逐条数据进行遍历，判定是否符合查询条件，效率极低。而有索引时需要为查询的字段创建一个索引，以二叉树结构为例（只是用来举个例子，并不是真实的索引数据结构）只需要比较三次即可：

         索引的优缺点：

         优点：提高数据的查找效率，降低数据库的I/O成本；通过索引对数据进行排序可以降低排序的成本和CPU的消耗。

         缺点：为表中的某个字段创建索引会消耗额外的空间；虽然提高了查询效率，但是却降低了增删改的效率，因为在增删改时，不仅需要改变表中数据，同时需要将索引中的数据进行修改。

         由于现在的磁盘十分廉价，而且对于数据库而查询操作占了很大一部分比例，因此索引的优点大于缺点，利大于弊。

二、索引结构

        学习过存储引擎可知，索引是在存储引擎层实现的，不同的存储引擎索引有着不同的结构，主要包含一下几种：

        各种存储引擎对上述索引的支持情况：

2.1 B-Tree（多路平衡查找数） 

        以一颗最大度数为5（5阶）的B-Tree为例（每个节点最多存储4个key，5个指针）：

         B-Tree在创建时符合中间元素向上分裂原则，每一个key的下面都挂载着数据。

2.2 B+Tree

        以一颗最大度数为4（4阶）的B+Tree为例：

相对于B-Tree而言，B+Tree所有的元素都会出现在叶子节点，非叶子节点起到索引的作用，叶子节点用来存放数据；叶子节点形成了一条单向链表（MySQL对其进行了优化，使其形成了一条双向链表）。

 2.3 哈希索引（Memory支持）

        哈希索引就是采用一定的哈希算法，将键值换成哈希值，映射到对应的位置，存储在哈希表中。如果多个键值映射到了一个相同的位置上就会产生哈希冲突，可以通过链表来解决。

        这里突然想起来一个昨天刷剑指Offer2专项训练时候的一个知识点就是设计哈希表的3个要点：1.为了快速确定一个元素在哈希表中的位置，可以使用一个数组，元素的位置为它的哈希值除以数组长度的余数；2.由于多个哈希值不同的元素可能会存入同一位置，数组的每个位置都对应一个链表，这样映射到同一位置的多个元素都会添加到同一链表中；3.为了确保链表不会太长，这样就需要计算哈希表中的元素的数目和数组长度的比值，当这个比值超过某个设置的阈值时就要对数组进行扩容并且把哈希表中所有的元素重新分配位置。

       哈希索引的特点：

        1.只能用于等值匹配（=，in），不支持范围查询（between，>,<）；

        2.无法利用索引进行排序操作；

        3.查询效率高，通常只需要检索一次即可（不出现哈希碰撞），效率通常高于B+Tree索引； 

思考题：为什么InnoDB存储引擎选择的索引结构是B+Tree，而不是二叉树、红黑树、BTree或者是哈希索引？

        二叉树在顺序插入存储的时候容易形成一个单枝的树，查询效率低下；红黑树虽然解决了单枝树的问题，但是每个节点只能挂载两个子节点，数据量大的时候树的层数较高，查询效率低下；进一步优化为BTree，BTree解决了数据量大时树的层数高的问题，但是BTree的每个节点即存储数据也存储索引，但是B+Tree的非叶子节点只存储索引，可以将省下来的空间接着存放索引，因此相同数据量的情况下B+Tree的层级更少；哈希索引只支持等值匹配，不支持范围匹配和排序操作。

三、索引分类

        索引主要可以分为一下几种：

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

 

        聚集索引选取规则：

        1.如果表中存在主键，主键索引就是聚集索引；

        2.如果不存在主键，将使用第一个唯一索引（UNIQUE）作为聚集索引；

        3.如果两种都没有，则InnoDB则会自动生成一个rowid作为因此的聚集索引；

        聚集索引（叶子几点下面挂载的是这一行对应的数据）和二级索引（叶子节点下面挂载的是这一行的id）的结构图如下：

 

四、索引语法

        创建索引：

create [unique|fulltext] index index_name on table_name(字段列表名...);

        如果不加unique或者fulltext这两个参数的话创建的就是常规索引；代码中可以看出一个索引可以关联多个字段的，称为联合索引；

        查看索引：

show index from table_name;

         删除索引：

drop index index_name on table_name;

五、SQL性能分析工具介绍

        要进行SQL优化首先要定位出对那些SQL进行优化，这时候就要知道SQL语句的性能如何，主要针对查询语句进行优化，索引这部分的优化占据主导地位；

1.SQL执行频率

        查看增删改查操作占当前数据库操作的比例；

show global/session status like 'Com_______';#查询全局数据库/当前会话操作的执行频次

2.慢查询日志

        通过慢查询日志定位执行效率比较低的SQL语句，从而对其进行优化。慢查询日志记录了所有执行时间超过指定参数的SQL语句，默认是关闭的，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

#开启MySQL慢查询日志开关
slow_query_log=1

#设置判定时间，当SQL语句超过这个时间时，就会被视为慢查询，记录到慢查询日志中,以2秒为例
long_query_time=2

        查看慢查询日志开启/关闭状态：

show variables like 'slow_query_log';

        慢查询日志的存放位置：

/var/lib/mysql/localhost-slow.log

3.show profiles

        show profiles 能够在做SQL优化时帮助我们了解每一条语句花费的时间，通过have_profiling参数可以看到当前MySQL数据库是否支持profile操作：

select @@have_profiling;#查询为YES表示当前系统支持该操作

       查看当前profiling开启或是关闭状态：

select @@profiling;

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

set [session|global] profiling = 1;

         通过下面的指令，可以查看每一条SQL语句的基本耗时情况：

show profiles；

4.explain执行计划

        上面几种工具都是从时间的层面上判定SQL语句的性能，只是粗略的判定；EXPLAIN或者DESC命令可以获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

        语法：

explain select 字段列表名 from 表名 where 查询条件;

        explain查询出来的每个字段的含义：

 

六、索引使用规则

        索引使用规则一：最左前缀法则

        主要针对于联合索引（一个索引关联了多个字段），在查询时需要从索引最左侧的列开始查询，并且不跳过索引中的列。如果跳过了某一列，该列后面的索引将失效。必须包含索引最左侧的字段列，顺序无所谓。

        索引使用规则二：在联合索引中，出现范围查询(< , >)，范围查询右侧的列索引失效。如果业务条件允许的情况下可以将 '>' 替换为 '>='，将 '<' 替换为 '<='来避免索引失效。

        索引使用规则三：不要在索引列上进行运算操作，否则索引列失效。

        索引使用规则四：模糊匹配只支持在尾部匹配，如果在头部进行模糊匹配，索引列将失效。

        索引使用规则五：用or连接符分割的条件，如果or连接符前面的条件有索引，而后面的条件没有索引，那么涉及到的索引都不会生效。

        索引使用规则六：SQL提示，在SQL语句中 from的后面 加入一些人为的提示来达到优化操作的目的。

1.use index ：建议数据库使用某个索引，但是数据库可能不采纳建议。

select * from tb_user use index(index_name) where 条件;

2.ignore index: 告诉数据库忽略这个索引。

select * from tb_user ignore index(index_name) where 条件;

3.force index：强制数据库使用该索引。

select * from tb_user force index(index_name) where 条件;

         索引使用规则七：覆盖索引&&回表查询，在查询时尽量使用覆盖索引，即查询时使用了索引，并且要查询的字段在索引中全部都能够找到。进行减少使用 select *。

        知识补充：explain字段列中的Extra列的值的含义：

using index condition：查找使用了索引，但是需要回表查询，效率较低。

using where，using index：查找使用了索引，需要的数据在索引列中都能找到，不需要回表查询，效率较高。

        索引使用规则八：前缀索引，当字段类型为字符串时，有时候需要索引很长的字符串浪费磁盘IO，可以将字符串的一部分前缀作为索引，可以节省空间，提高效率。

create index index_name on table_name(字段名(n));#选取字段的前n个字符创建索引

        如何确定n呢？ 选择不重复的索引值（n） / 记录总数；比值越大，查询效率越高。

        索引使用规则九：单列索引，单列索引的两个字段并列时，只走前面的一个，容易涉及到回表查询，推荐使用联合索引。

七、索引设计原则

        1.为数据量大、查询操作频繁的表建立索引；

        2.针对长作为where条件、order by 条件、group by 条件的字段建立索引；

        3.尽量建立唯一索引（UNIQUE），区分度高，查找效率高；

        4.尽量使用联合索引，减少单列索引，避免回表查询；

        5.要控制索引的数量，索引并不是越多越好，维护成本也需要考虑；

        6.如果索引列不能存NULL值，则在创建表是用NOT NULL来约束它；

图片和文字来源：B站黑马程序员MySQL视频教程中PPT内容。链接：https://www.bilibili.com/video/BV1Kr4y1i7ru?p=1&vd_source=06b234411c8097b2d8b98e624fdae6f6