MySQL进阶——索引

索引

• 索引概述

• 索引结构

• 索引分类

• 索引语法

• SQL性能分析

• 索引使用

• 索引设计原则

概述

介绍

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

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

优缺点分析

优点

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

• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

缺点

• 索引列需要占用空间。

• 索引大大提高了查询效率，同时也降低了更新表的速度。如：对表进行Insert、update、delete时，效率降低。

索引结构

MySQL的索引是在存储引擎层实现，不同的存储索引有不同的结构。

• B+Tree索引：常见的索引类型，大部分引擎都支持B+树索引

• Hash索引：底层数据结构是用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询

• R-tree（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据结构，通常使用较少

• Full-text（全文索引）：是一种通过建立倒排索引，快速匹配文档的方式，类似Lucene，Solr，Es

B+树索引

普通的B+树：

MySQL索引数据结构对经典的B+树进行了优化。在原本的B+树的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序针织的B+树，提高区间访问性能

Hash索引

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储hash表中。

如果出现了hash冲突，采用链表的方式解决。

Hash索引的特点

1. Hash索引只能用于对等比较（=，in），不支持范围查询（between,>,<,.....)

2. 无法利用索引完成排序操作

3. 查询效率高，通常只需要一次检索就行，效率通常比B+树高。

存储引擎支持

在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+树引擎在指定条件下自动构建的。

经典面试题：为什么InnoDB存储引擎采用B+树索引

• 相对二叉树，层次更少，搜索效率高。

• 对于B树，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针也跟着减少，要同样保存大量数据，只能增加树的高度，导致性能下降。

• 相对Hash索引，B+树支持范围匹配及排序操作。

为什么采用B+树索引，而不采用hash索引？

1. 范围查询效率： B+树索引支持按范围查询，例如范围查询、排序等，而哈希索引通常只支持精确匹配查询。在数据库中，范围查询是非常常见的操作，因此B+树索引更适合处理这样的场景。

2. 顺序访问性能： B+树索引在范围查询时可以利用数据的有序性，因此对于顺序访问的效率更高。而哈希索引则不保证数据的有序性，因此无法像B+树索引那样高效地进行顺序访问。

3. 内存利用率： 哈希索引在进行哈希冲突处理时需要保留额外的空间，这可能会导致在内存利用率上的一定程度的浪费。相比之下，B+树索引可以更好地利用内存空间。

4. 支持部分索引： B+树索引支持前缀查询，而哈希索引不支持这种操作。在某些情况下，只需要索引字段的前几个字符来进行查询，这时B+树索引会更加高效。

5. 并发性能： B+树索引通常对并发访问更友好，因为B+树的结构使得并发插入和删除更容易实现。而哈希索引在并发操作时可能需要更多的锁和同步操作。

尽管哈希索引在某些特定场景下可能更快，比如对于单个唯一键的查找，但总体来说，由于B+树索引的优势，MySQL选择采用B+树索引作为主要的索引结构。

索引分类

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

• 聚集索引：将数据存储与所有放到了一块，索引结构的叶子节点保存了行数据。（必须要，而且只能有一个）

• 二级索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键（可以存储多个）

聚集索引的选取规则：

1. 如果存储主键，主键索引就是聚集索引。

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

3. 如果没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

来一个索引查找示例：

select * from user where name ='Arm';

执行过程（这个过程被称为回表查询）：

1. 先根据二级索引查到主键值

2. 再根据主键值去查询想要的数据

思考

索引语法

• 创建索引

  create[unique|fulltext] index index_name on table_name(index_col_name,...);
  #unique和fulltext是可选的关键字，指定索引类型（二选一）
  #(index_col_name,...)指的是：包含再索引中的列的列表。
  #（可以指定一个或多个列，列名纸巾用逗号分开）

• 查看索引

  show index from table_name;

• 删除索引

  drop index index_name on table_name;

SQL性能分析

SQL执行频率

MySQL客户端连接成功后，通过show[session|global] status 命令可以提供服务器状态信息。

通过下面的指令，可以查看当前数据库的Insert、Update、Delete、Select的执行频率。

show global status like '表名模糊信息';

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句。

MySQL的慢查询的日志默认没有开启:

show variables like 'slow_query_log';

要开启的话，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息：

#开启MySQL慢查询日志查询开关
show_query_log=1
#设置慢查询日志的时间为2秒，SQL语句超过2秒，则视为慢查询，记录慢查询日志
long_query_time=2

如果配置完成后，重启MySQL服务器进行测试，查看慢日志文件中记录的信息

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

profile详情

show profiles 命令能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看的当前MySQL是否支持profile操作：

select @@have_profiling;

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

set profiling =1;

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

比如说我们查询profiling之后，要看某条语句的详情：

show profile for query QUery_ID;

explain执行计划

explain或desc命令获取MySQL如何执行Select语句执行过程中如何连接和连接顺序。

语法

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

在这些信息中，重点关注的是type，然后再是possible_keys和key

索引使用

创建索引，就是一个将某个字段转换成一个B+树的过程。

可以测试一下在10万条数据中查一条数据，查询时间从二十几秒钟减少到几毫秒。

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。

最左前缀法则：查询从索引的最左列开始，并且不跳过索引的列。

如果跳跃了一列，索引将部分失效（后面的字段索引失效）

索引失效情况

范围索引

联合索引中，出现范围查询（>或<)，范围查询右侧的列索引失效。

如果使用>= 或 <= 的话，索引还是会存在。

索引列运算

不要再索引列上进行运算操作，索引会失效。

字符串不加引号

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

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效。

or连接的条件

用or分隔开的条件，如果or前的条件中的列有索引，而后面列中没有索引，那么涉及的索引都不会被用到。只有两侧都有索引，索引才会生效。

数据分布影响

如果MySQL评估使用索引比全表更慢，不使用索引。（MySQL自动变更的）

SQL提示

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

• use index：只是建议使用指定的索引，查询优化器仍然可以选择其他索引。

  SELECT * FROM users USE INDEX (idx_username) WHERE username = 'some_username';
  ​

• force index：会强制使用指定的索引，即使查询优化器认为其他索引更合适。

  SELECT * FROM users FORCE INDEX (idx_username) WHERE username = 'some_username';
  ​

• ignore index：告诉 MySQL 查询优化器在执行查询时忽略特定的索引。

  SELECT * FROM users IGNORE INDEX (idx_username) WHERE username = 'some_username';

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在索引中已经全部能找到），减少select *（很容易出现回表查询）

知识补充：

• using index condition：查找使用了索引，但是需要回表查询数据

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

思考面试题

如果存在一张表，有四个字段(id, username, password, status)，由于数据量巨大，需要对下面SQL语句进行优化，该怎么进行设计才是最优方案呢？
select id,username,password from user where username = 'xxx';

下面给出问题的结果：

• 我们可以通过对username和password两个字段设置联合索引实现最优方案。

• 因为使用了这种方案，我们可以直接进行一次的辅助索引就可以拿到username和password。一般查询的时候还会遵循最左前缀法则，一般查询的都是username，索引也不会失效。

前缀索引

当字段类型为字符串（varchar、text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。

此时，可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法

create index idx_xxxx on table_name(column(n));

这里的n代表的是：选取前多少个字符作为索引。

前缀长度

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值。

索引选择性越高，查询效率越高。唯一索引选择性是1，这是最好的索引选择性，性能也是最好的。

select count(distinct email) / count(*) from user;
select count(distinct substring(emial,1,5)) / count(*) from user;

如果前缀索引的索引性小于1，表示可能出现重复情况，那如果出现重复情况，会把其全部返回。

就是说，当通过前缀索引找到目标值的时候，还会比较后一个的数据，看看前缀是否相同，如果相同，还会继续比较，继续返回。

单列索引与联合索引

• 单列索引：一个索引只包含单个列

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

使用情况

如果存在多个查询条件，考虑针对查询字段建立索引时，建议建立联合索引，而不是单例索引。

比如说这样一种情况：

还是之前那个问题：
如果存在一张表，有四个字段(id, username, password, status)，由于数据量巨大，需要对下面SQL语句进行优化，该怎么进行设计才是最优方案呢？
select id,username,password from user where username = 'xxx';

如果我们只通过单列索引，那么还会需要进行回调查询。

如果是多列索引，则没有回调查询了。

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

索引设计原则

1. 针对数据量大的、且查询比较频繁的表建立索引。

2. 针对常作为查询条件（where）、order by（group by）操作的字段建立索引

3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高

4. 如果字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引

5. 尽量使用联合索引，减少单列索引。查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率

6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价越大，会影响增删改的效率

7. 如果索引列不能存储NULL值，在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含NULL值时，可以更好的确定哪个索引最有效的用于查询

总结

1. 索引概述：索引是高效获取数据的数据结构

2. 索引结构：B+树 和 Hash

3. 索引分类： 正常的索引：主键索引、唯一索引、常规索引、全文索引 InnoDB中根据索引结构分类：聚集索引（存储的行数据）、二级索引

4. 索引语法：

   #创建索引
   create[unique] index index_name on table_name(column)
   ​
   #查看索引
   show index from table_name()
   ​
   #删除索引
   drop index index_name on table_name

5. SQL性能分析：执行频次、慢查询日志、profile、explain

6. 索引使用：

   1、联合索引：最左前缀法则，避免使用>、<
   ​
   2、索引失效：不进行函数运算、字符串不加引号、like模糊匹配%加前面导致失效、or连接条件两侧有索引才会让索引生效、数据分布影响
   3、SQL提示：提示使用/忽略/强制使用某个索引
   ​
   4、覆盖索引：去避免回表查询
   ​
   5、前缀索引
   ​
   6、单列/联合索引

7. 索引设计原则

   表
   字段
   索引