MySQL索引

目录

概述

结构

B+Tree索引

Hash索引

面试：为什么InnoDB使用B+Tree索引？

分类

两种索引的区别

索引的语法

创建索引

查看索引

删除索引

SQL性能分析

SQL执行频率

慢查询日志

profile详情

!explain执行计划

!索引的使用

!最左前缀法则

范围查询

索引失效的情况

SQL提示

!覆盖索引与回表查询

前缀索引

联合索引

索引设计原则

---

概述

index，帮助mysql高效获取数据的数据结构（有序）

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

• 优点;

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

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

• 劣势：

  • 索引列占用磁盘空间

  • 索引大大提高了查询效率，但是也降低了更新数据的速度，对表进行大量的增删改操作会降低效率

结构

• B+Tree索引：最常见的索引，大部分引擎支持

• Hash索引：底层是哈希表实现，只有精确匹配可以使用，效率高但是不支持范围查询（只有memory引擎支持）

• R-tree：空间索引是MyISAM引擎的一个特殊索引类型，较少使用

B+Tree索引

一般说的索引，是InnoDB的B+树索引：

• 二叉树与红黑树的缺点：当数据量大时，层级较深，检索较慢

• BTree：

  • 一棵最大度数为n的Btree树，每个节点最多存储n-1个key，n个指针

  • 树的度数是指一个结点的子节点个数

  • 构建过程如下：

• B+树的区别：

  • 叶子节点之间形成了单向链表——提高区间访问性

Hash索引

• 只有Memory引擎支持

• 查询效率高

• 无法利用索引进行排序操作

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

1. 算出每一行的哈希值

2. 在槽位上会记录每一行的哈希值和元素

3. 解决哈希冲突的方法：单链表

面试：为什么InnoDB使用B+Tree索引？

• 相较于二叉树，层级更少，查询效率更高

• 对于BTree，无论是叶子节点还是非叶子节点都会存储数据，这样造成一页中存储的键值减少，指针也减少，要保存大量数据，只能增加树的高度，导致性能降低；另一个方面，查询都要从根结点到叶子节点，查询效率稳定；同时区间访问的性能增加

• 相对于Hash索引，B+Tree支持范围匹配和排序

分类

分类	含义	特点	修饰符
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免某一列出现重复值	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	查找文本的关键词	可以有多个	FULLTEXT

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

• 聚集索引：

  • 将数据存储和索引放在一块，索引结构的叶子节点存储的是行数据

  • 必须有，而且只能有一个

• 二级索引;

  • 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键（所以可能会有回表查询）

  • 可以存在多个

• 聚集索引的选取规则：

  • 如果存在主键，主键索引就是聚集索引

  • 如果不存在主键，将第一个唯一索引作为聚集索引

  • 如没有主键、也没有合适的唯一索引，InnoDB会自动生成一个隐藏的rowid作为隐藏的聚集索引

两种索引的区别

• 聚集索引：将数据存储和索引放在一块，索引结构的叶子节点存储的是行数据

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

结构示意图：

• 考虑执行以下的SQL

select * from user where name = 'Arm';

查询过程——回表查询：

• 先用二级索引找到对应行的主键值

• 在到聚集索引中查询到这一行的行数据

索引的语法

创建索引

注意：主键索引不需要手动创建

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name(col_name);

规范：索引的名字：index_tablename_colname

• 在tablename的colname上创建索引index_tablename_colname

查看索引

show index from table_name;

删除索引

drop index index_name in table_name;

SQL性能分析

主要优化的查询语句的优化

SQL执行频率

show [session|global] status;

• 可以提供服务器状态的信息，以及各个语句的访问频次

SHOW GLOBAL STATUS LIKE 'Com_______'  ---后面跟的是7个字符,7个下划线

• 查询占绝大部分，可以进行优化

慢查询日志

• 记录所有执行时间超过指定的参数（long_query_time，默认是10秒）的所有的SQL语句的日志

• 查看是否开启慢查询日志：

  show variables like 'slow_query_log';

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

  #开启MySQL慢查询日志开关
  slow_query_log = 1
  ​
  #设置long_query_time
  long_query_time = 2

• 慢查询日志的存储位置：/var/lib/mysql/mysql

开启的步骤：

• 切换到/etc/my.cnf/中修改配置，注意是cnf

• 退出mysql客户端，输入service mysqld restart

profile详情

• 通过select @@have_profiling操作可以查询该数据库是否支持profile(支持不一定开启)

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

• select @@profiling查询是否开启

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

• 开启指令：使用set语句开启profile

SET [session|global] profiling = 1;

• show profiles; 语句，查看每一条语句的执行时间

mysql> show profiles;
+----------+------------+---------------------+
| Query_ID | Duration   | Query               |
+----------+------------+---------------------+
|        1 | 0.00015625 | select @@profiling  |
|        2 | 0.00007750 | select * from user  |
|        3 | 0.00018550 | SELECT DATABASE()   |
|        4 | 0.00102875 | show databases      |
|        5 | 0.00432875 | show tables         |
|        6 | 0.00045400 | select * from userl |
|        7 | 0.00038425 | select * from user  |
+----------+------------+---------------------+
7 rows in set, 1 warning (0.00 sec)

• show profile for query query_id ; 可以查看指定id的SQL语句在各个阶段的执行情况

mysql> show profile for query 7;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000066 |
| Executing hook on transaction  | 0.000005 |
| starting                       | 0.000009 |
| checking permissions           | 0.000008 |
| Opening tables                 | 0.000056 |
| init                           | 0.000006 |
| System lock                    | 0.000011 |
| optimizing                     | 0.000005 |
| statistics                     | 0.000017 |
| preparing                      | 0.000019 |
| executing                      | 0.000124 |
| end                            | 0.000005 |
| query end                      | 0.000005 |
| waiting for handler commit     | 0.000008 |
| closing tables                 | 0.000011 |
| freeing items                  | 0.000016 |
| cleaning up                    | 0.000014 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

!explain执行计划

explain语句可以获取：执行select语句的信息，包括在select语句执行的过程中表如何连接和连接的顺序

explain select .. from .. where ..

各个字段的含义：

• id :

  • select查询的序列号，表示查询中执行的select子句或者是操作表的顺序

  • 只有多表查询才可以真正有效果

  • id相同，执行顺序从上到下；id不同，值越大，越先执行

• select_type：

  • 查询的类型

• type：

  • 表示连接类型，性能由好到差的连接类型为：NULL，system，const，eq_ref，ref，range，index，all

  • 一般不可能为NULL，system

  • const性能较好：使用主键或者唯一索引会出现const

  • 使用非唯一索引出现ref

• possible_key：显示应用在这张表上的索引，可能会用到的索引

• key：显示实际用到的索引，没有用到就显示NULL

• key_len：表示索引中使用的字节数，该值为索引字段中存储的值的最大可能长度，并非实际使用长度

• filtered：表示查询结果占需读取行数的百分比，值越大越好

• extra：额外信息

!索引的使用

!最左前缀法则

• 使用使用了联合索引，要遵循最左前缀法则；

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

• 如果跳过某一列，索引后面的列的部分将失效

• 所以：创建联合索引时，列的排列顺序非常重要，但是，在使用索引的时候条件的排列顺序不重要

验证过程：

• 创建相关的表以及联合索引：idx_user_pro_age_sta

• 查询条件中where语句中联合索引的全部列都出现：

  • 执行计划中：

    key中实际用到了联合索引

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

• 索引的最后一个不出现

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

   

  • 执行计划中：

    key中实际用到了联合索引，但是索引的长度减少了

• 索引的第一个不出现的时候

  explain select * from tb_user where age = 31 ;

• 执行计划中并没有使用索引，因为不满足最左前缀法则索引失效了

范围查询

• 联合索引中，出现范围查询（< 或者 >），范围查询右边的列索引失效

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

  使用的索引的长度key_len减小为49，说明后面的查询没有走索引

索引失效的情况

1. 索引列进行了计算

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

   

发现并没有使用到索引，索引失效

1. 字符串类型的字段不加引号：查询没有问题，但是索引会失效

2. 模糊查询：尾部模糊匹配不会失效；仅仅存在头部模糊匹配会失效

3. or连接的条件：

   用or分隔开的条件，如果or前的条件有索引但是or后面的列没有索引，那么涉及的索引都不会用到

   只有or两边都有索引的时候才会索引生效

4. MySQL查询的时候评估全表索引性能更好就不走索引

   常见的：not null 或者 is null

SQL提示

在SQL语句中加入一些人为的提示，来达到优化操作的目的

//建议使用某个索引
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程; 

//忽略某个索引
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程; 

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

!覆盖索引与回表查询

• 尽量使用覆盖索引

• 覆盖索引的条件：

  • 查询使用了索引

  • 需要返回的列在索引中都能找到

  • 减少使用select *

• 在执行计划中关注Extra中的内容

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

  • using where ; using index：覆盖索引

• 在每一种索引（聚集索引、二级索引）都包含主键的列

• 解决方案：减少回表查询——建立联合索引，形成覆盖索引

前缀索引

• 当字段的类型为字符串的时候，有时候需要索引很长的字符串，这会让索引变得很大，浪费大量的磁盘IO，影响效率；

• 所以可以设计用字符串的前缀一部分建立索引，可以节省索引空间，提高效率

create index idx_name on table_name(col_name(n));

语法与普通索引一致，只需要指定前缀长度(n)

• 前缀长度：由索引的选择性来确定，选择性越高，查询效率越高

• 选择性的计算方法：

mysql> select count(distinct substring(email,1,9))/count(*) from tb_user;
+-----------------------------------------------+
| count(distinct substring(email,1,9))/count(*) |
+-----------------------------------------------+
|                                        0.9583 |
+-----------------------------------------------+
1 row in set (0.00 sec)

值为1时，选择性最好

联合索引

• 在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引， 而非单列索引

索引设计原则

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

• 2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引

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

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

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

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

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