Mysql索引实战

• 一：概述

• 1.1 索引如何提高查询效率：

• 二：结构

• 2.1 主要索引结构
• 2.2 详解B+Tree

• 2.2.1 二叉树
  2.2.2 红黑树

• 2.2.3 B-Tree
• 2.2.4 B+Tree
• 2.2.5 为什么InnoDB存储引擎选择使用B+tree索引结构？

• 三：索引分类

• 3.1 按照作用分
  3.2 按照存储形式分

• 3.3 什么是回表查询

• 四：索引语法

• 4.1 创建索引
• 4.2 查看索引
• 4.3 删除索引

• 五：SQL性能分析

• 5.1 SQL执行频率
• 5.2 慢SQL日志
• 5.3 profile性能分析
• 5.4 Explain执行计划

• 六：索引使用

• 6.1 最左前缀法则
• 6.2 范围查询
  6.3 索引列的运算操作

• 6.4 字符串不加 ' '
• 6.5 模糊查询

• 七：索引使用原则

• 7.1 or连接的条件
• 7.2 sql提示
• 7.3 覆盖索引
  
  

一：概述

索引(index)是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护看满足特足查我算法的数据结构，这些

数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

1.1 索引如何提高查询效率：
二：结构

2.1 主要索引结构

B+Tree索引

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

Hash索引

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

R-tree（空间索引）

空间索引是MYISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少

Full-text（全文索引）

是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene,Sol,ES

2.2 详解B+Tree

B+Tree作为innodb引擎最常用的存储结构，是我们本次要讲解的重点。

想要了解B+Tree，我们将会从二叉树、红黑树、B-Tree、B+Tree的顺序，逐渐的进行分析，从而带大家深入了解B+Tree。

2.2.1 二叉树

这里我们不对二叉树基础概念进行讲解，主要说二叉树存储时存在的问题 图中给出了两个二叉树，如果插入的数据非常不平衡的话，例如顺序插入、将会形成一个链表，查询的性能大大降低，

2.2.2 红黑树

为了解决数据不平衡的问题，出现了红黑树，红黑树的节点是不断变化的，可以做到数据平衡，不会产生数据非常不平衡的问题。

2.2.3 B-Tree

B-Tree又叫做多路平衡查找树。

一颗最大度为 5阶的B-Tree为例（每个节点最多存储4个key，5个指针）
B-Tree分裂的方式是向上分裂，当满足对应阶数在进行插入数据的时候，会将中间的数据向上分裂。

具体可以查看结构变化

2.2.4 B+Tree

以一颗最大度数(max-degree)为4(4阶)的b+tree为例：
所有的元素都存储到叶子节点

所有的叶子节点形成了一个单向链表

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序

指针的B+Tree,提高区间访问的性能。

2.2.5 为什么InnoDB存储引擎选择使用B+tree索引结构？

相对于二叉树，层级更少，搜索效率高；

对于B-tree,无论是叶子节点还是非叶子节点，都会保存数据，这样导致一

页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的

高度，导致性能降低；B+tree由于每次都是去叶子节点上查找数据，更加的稳定。

相对Hash索引，B+tree支持范围匹配及排序操作；

三：索引分类

3.1 按照作用分

按照作用可以分为4种，分别是

3.2 按照存储形式分

3.3 什么是回表查询

已上面的聚集索引和二级索引为例：

select from user where name =‘Arm’;

当我们执行这条sql的时候，会先去二级索引找到对应的主键索引值，然后通过这个索引值去聚集索引内查询对应的数据，这个过程就叫做回表查询。

四：索引语法

4.1 创建索引

CREATE UNIQUE FULLTEXT INDEX index_name ON table_name (index_col_name,...）

4.2 查看索引

SHOW INDEX FROM table name

4.3 删除索引

DROP INDEX index name ON table name

五：SQL性能分析

5.1 SQL执行频率

MySQL客户端连接成功后，通过show[session]global]status命令可以提供服务器状态信息。通过如下指令，可以查看当前表

INSERT、UPDATE、DELETE、SELECT的访问频次：

SHOW GLOBAL STATUS LIKE ‘Com_____’

5.2 慢SQL日志

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

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息：

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

5.3 profile性能分析

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

# 查看是否开启
SELECT @@have profiling
# 默认orofiling:是关闭的，可以通过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;

5.4 Explain执行计划

通过Explain性能分析博客

之前对Explain进行过研究，并且写了总结博客，大家可以查看这篇博客。

六：索引使用

6.1 最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

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

6.2 范围查询

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

6.3 索引列的运算操作

不要在索引列上进行运算，会使索引失效

6.4 字符串不加 ’ ’

字符串不加 ’ ’ 存在类型转换，会使索引失效

6.5 模糊查询

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

七：索引使用原则

7.1 or连接的条件

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

7.2 sql提示

• use index: 建议
  explain select*from tb user use index(idx user pro)where profe
  
  ignore index: 忽略
  explain select*from tb_user
  ignore index(idx_user_pro)where profession=软件工程’ssion=‘软件工程’：
  
  force inde: 强制
  explain select*from tb_user force index(idx_user_pro)where profession=‘软件工程’

7.3 覆盖索引

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