性能优化-MySQL索引

为什么要使用索引？

使用索引是为了提高数据检索的效率。当数据量很大时，如果没有索引，数据库系统需要**逐条扫描数据**来找到符合条件的记录，这样会消耗大量的时间和资源。而使用索引可以通过创建特定的数据结构，将数据按照某种规则有序地组织起来，从而加快数据的查找速度

索引可以在数据库表的一列或多列上创建，它们包含了对应列值的引用和指针，使得数据库系统可以快速定位到需要的数据。通过使用索引，数据库系统可以根据索引的排序和搜索算法，快速定位到符合查询条件的数据，提高查询的效率

相同的SQL执行，左边没加索引，后边添加了索引，效率差了10倍有余（基础数据量为60万）

优点：

1. 提高数据查询的效率，降低数据库的IO成本
2. 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗

缺点：

1. 索引会占用存储空间
2. 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率

MySQL索引-结构

MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等

平常所说的索引，如果没有特别指明，都是指默认的 B+Tree 结构组织的索引

在没有了解B+Tree结构前，先回顾下之前学习过的树结构：

• 二叉树

二叉查找树：左边的子节点比父节点小，右边的子节点比父节点大

如果数据本身就是排好序的，当向二叉查找树保存数据时，就会形成一个单向链表，搜索性能会打折扣

可以选择平衡二叉树或者是红黑树来解决上述问题。（红黑树也是一棵平衡的二叉树）

但是在Mysql数据库中并没有使用二叉搜索数或二叉平衡数或红黑树来作为索引的结构

下面再来看看B+Tree(多路平衡搜索树)结构中如何避免这个问题：

B+Tree结构：

• 每一个节点，可以存储多个key（有n个key，就有n个指针）
• 节点分为：叶子节点、非叶子节点
  • 叶子节点，就是最后一层子节点，所有的数据都存储在叶子节点上
  • 非叶子节点，不是树结构最下面的节点，用于索引数据，存储的的是：key+指针
• 为了提高范围查询效率，叶子节点形成了一个双向链表，便于数据的排序及区间范围查询

MySQL索引-语法

查看索引

查看索引的语法：

show  index  from  表名;

创建索引

创建索引语法如下：

create  [ unique ]  index 索引名 on  表名 (字段名,... ) ;

示例：为device_data 表的iot_id和product_key字段建立一个索引

create index idx_iot_id_product_key on device_data (iot_id, product_key);

在创建表时，如果添加了主键和唯一约束，就会默认创建：主键索引、唯一约束索引

注意事项：

• 一般情况下，要选择经常作为查询条件的字段作为索引

删除索引

语法：

drop  index  索引名  on  表名;

示例：删除 tb_emp 表中name字段的索引

drop index idx_iot_id_product_key on device_data ;

MySQL索引-分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引

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

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

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引(聚簇索引)选取规则:

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

• 聚集索引的叶子节点下挂的是这一行的数据
• 二级索引的叶子节点下挂的是该字段值对应的主键值

分析当执行如下的SQL语句时，具体的查找过程

具体过程如下:

1. 由于是根据name字段进行查询，所以先根据name=’Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10

2. 由于查询返回的数据是*，所以此时还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row

3. 最终拿到这一行的数据，直接返回即可

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询

索引失效检查

可以采用EXPLAIN 或者 DESC命令获取 SELECT 语句的执行计划

语法：

EXPLAIN   SELECT   字段列表   FROM   表名   WHERE  条件 ;

• possible_keys 当前sql可能会使用到的索引

• key 当前sql实际命中的索引

• key_len 索引占用的大小

• Extra 额外的优化建议：

Extra	含义
Using index	查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询数据
Using where	没有找到合适的索引
Using index condition	查找使用了部分索引，但是需要回表查询数据
Using where; Using index	查找使用了部分索引，需要的数据都在索引列中能找到，不需要回表查询数据

• type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、 index、all

  • system：查询最多只有一条记录的表，几乎不会出现
  • const：根据主键查询
  • eq_ref：主键索引查询或唯一索引查询
  • ref：索引查询
  • range：范围查询
  • index：索引树扫描
  • all：全盘扫描

• 索引失效场景

  • 不符合最左匹配法则

    • 示例：

    • create index idx_location_type_physical_location_type_access_location
          on device_data(location_type, physical_location_type, access_location);
          
      explain select * from device_data where physical_location_type = 1 and access_location = '1';	# 条件语句中，最左边的字段没有匹配到索引中最左边的字段
      - 范围查询右边的列，不能使用索引
      
        - 示例：
      
        - ```sql
          create index idx_location_type_physical_location_type_access_location
          	on device_data(location_type, physical_location_type, access_location);
          	
          explain select location_type from device_data where location_type = 1 and physical_location_type > 5 and access_location = '1';	# 范围查询字段后的索引字段（access_location）失效
      

  • 不要在索引列上进行运算操作， 索引将失效

    • 示例：

    • create index idx_location_type_physical_location_type_access_location
          on device_data(location_type, physical_location_type, access_location);
      
      explain select location_type from device_data where location_type + 1 = 1 and physical_location_type = 1 and access_location = '1';
      - 字符串不加单引号，造成索引失效(类型转换)
      
        - 示例：
      
        - ```sql
          create index idx_location_type_physical_location_type_access_location
          	on device_data(location_type, physical_location_type, access_location);
          
          explain select location_type from device_data where location_type = 1 and physical_location_type = 1 and access_location = 1;
      

  • 以%开头的Like模糊查询，索引失效

    • 示例：

    • create index idx_location_type_physical_location_type_access_location
          on device_data(location_type, physical_location_type, access_location);
      
      explain select location_type from device_data where location_type = 1 and physical_location_type = 1 and access_location like '%1';	# 失效
      
      explain select location_type from device_data where location_type = 1 and physical_location_type = 1 and access_location like '1%';	# 正常
      # 回表问题
      
      ## 覆盖索引
      
      在MySQL的B+树中，索引分为聚集索引和非聚集索引（二级索引）
      
      - 聚集索引的叶子节点直接保存的是数据的整条记录
      - 非聚集索引的叶子节点则保存的是一条记录中主键的值
      
      ![7c5749b0-690a-41c2-a341-4d77780be2a2](/img/assests/7c5749b0-690a-41c2-a341-4d77780be2a2.png)
      
      因此，当通过非聚集索引进行查询时，如果查询的列不在索引中，那么它
      
      首先会通过非聚集索引查找到主键的值，然后再通过主键回到聚集索引中查询一次，这个过程称为回表
      
      ```SQL
      -- 假设有一张user表,包含id、name、age字段，判断下面的索引使用情况
      
      -- 走聚集索引, 不会产生回表 
      select * from user where id = 1;
      
      -- 走二级索引, 不会产生回表 
      select id,name from user where name = 'Geek';
      
      -- 走二级索引, 但是会产生回表 
      select * from user where name = 'Geek';