⑩② 【MySQL索引】详解MySQL`索引`：结构、分类、性能分析、设计及使用规则。

个人简介：Java领域新星创作者；阿里云技术博主、星级博主、专家博主；正在Java学习的路上摸爬滚打，记录学习的过程~
个人主页：.29.的博客
学习社区：进去逛一逛~

---

⑩② 【MySQL索引】

1. 索引

索引：

• 什么是索引(index) ?
• 索引(index)是帮助MySQL高效获取数据的数据结构(有序)：在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据就是索引。
• 索引的优缺点？
• 优势：
• • ⚪提高数据检索效率，降低数据库IO成本；
  • ⚪通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗；
• 劣势：
• • ⚪索引列需要占用空间，比无索引结构占用的空间更大。
  • ⚪索引虽大大提高了查询效率，但与此同时却降低了更新表的速度，如对表进行INSERT UPDATE DELETE 时，效率降低。

---

2. 索引的结构

索引结构：

• MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构。
• ①B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引。
• ②Hash索引：底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询。
• ③R-tree(空间索引)：空间索引是MyISAM引擎的一个特殊索引类型，只要用于地理空间数据类型，较少使用。
• ④Full-text(全文索引)：是一种通过建立倒排索引，快速匹配文档的方式。类似于ES(Elasticsearch)。
• 

🚀B+树索引

B+Tree索引：

• 特征：
• • ①每个节点最多可存放4个元素，五个指针。
  • ②叶子节点形成链表，存储了树的所有元素。
  • ③指针指向当前元素区间内的元素。
• MySQL索引数据结构对经典的B+Tree进行了优化。在原本B+树的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
• 

🚀Hash索引

Hash索引：

• 哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
• 哈希碰撞问题
• 如果出现两个或多个键值映射到同一个槽位上，也就是出现hash碰撞时，可以通过链表解决问题。
• Hash索引特点
• • ① Hash索引只能用于对等比较（=、in），不支持范围查询（between、>、
  • ②无法利用Hash索引完成排序操作
  • ③查询效率高，通常只需要一次检索就可以完成，效率通常要高于B+Tree索引
• MySQL数据库中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。
• 

🚀思考题

• 为什么InnoDB存储引擎选择使用B+Tree索引结构？
• • ⚪相对于二叉树，层级更少，搜索效率更高；
  • ⚪对于BTree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，会导致性能下降；
  • ⚪相对Hash索引，B+Tree索引支持范围匹配和排序操作；

---

3. 索引的分类

索引分类：

• ①主键索引 —— PRIMARY

• ②唯一索引 —— UNIQUE

• ③常规索引

• ④全文索引 —— FULLTEXT

• 

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

• • ⚪聚集索引(Clustered Index)：B+Tree叶子节点下挂载这一行的数据 。
  • • ①如果存在主键，主键索引就是聚集索引。
    • ②如果不存在主键，将使用第一个唯一索引**(UNIQUE)作为聚集索引。**
    • ③如果表没有主键，也没有合适的唯一索引，InnoDB自动生成一个rowid作为隐藏的聚集索引。
  • ⚪二级索引(Secondary Index)：B+Tree叶子节点下挂载这一行的id 。
  • 

---

4. 创建、查看、删除索引

索引操作：

• 🚀创建索引

• • -- 使用UNIQUE关键字，创建唯一索引
    -- 使用FULLTEXT关键字，创建全文索引
    -- 不指定上述两者，创建常规索引
    CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名(字段1,字段2...);
    

• 🚀查看索引

• • SHOW INDEX FROM 表名;
    

• 🚀删除索引

• • DROP INDEX 字段名 ON 表名;
    

• -- 演示：
  
  -- 操作的表tb_user
  create table tb_user(
  	id int primary key auto_increment comment '主键',
  	name varchar(50) not null comment '用户名',
  	phone varchar(11) not null comment '手机号',
  	email varchar(100) comment '邮箱',
  	profession varchar(11) comment '专业',
  	age tinyint unsigned comment '年龄',
  	gender char(1) comment '性别 , 1: 男, 2: 女',
  	status char(1) comment '状态',
  	createtime datetime comment '创建时间'
  ) comment '系统用户表';
  
  -- 查询索引
  show index from tb_user;
  
  -- 查询所有，竖向显示
  show index from tb_user G;
  
  -- 1.name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。
  create index idx_user_name on tb_user(name);
  
  -- 2.phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。
  create unique index idx_user_phone on tb_user(phone);
  
  -- 3.为profession、age、status创建联合索引。
  create index idx_user_pro_age_sta on tb_user(profession,age,status);
  
  -- 4.为email建立合适的索引来提升查询效率。
  create index idx_user_email on tb_user(email);
  
  -- 删除索引idx_user_email
  drop index idx_user_email on tb_user;
  

---

5. SQL性能分析

🚀SQL执行频率

SQL执行频率：

• MySQL客户端连接成功后，通过show [session | global] status命令可以提供服务器状态信息。还可以通过show global status like 'Com_______'命令，查看当前数据库的INSERT UPDATE DELETE SELECT的访问频次。

• 🚀查看服务器状态信息

• • SHOW [SESSION | GLOBAL] STATUS;
    

• 🚀查看当前数据库的INSERT UPDATE DELETE SELECT的访问频次

• • -- 模糊匹配中有七个下划线'_'
    SHOW GLOBAL STATUS LIKE 'Com_______';
    

🚀慢查询日志

慢查询日志：

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

• 🚀MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件/etc/my.cnf中配置相应信息：

• • # /etc/my.cnf文件内：
    #开启MySQL慢查询开关
    slow_query_log=1
    
    #设置慢日志的时间为2秒，SQL语句执行超过2秒，被视为慢查询，记录慢查询日志
    long_query_time=2
    

  • # 修改MySQL配置文件/etc/my.cnf【Linux环境下】
    vi /etc/my.cnf
    
    # 1. 按i键进行编辑
    # 2. 寻找合适位置，输入上文给出的配置信息
    # 3. 按Esc键推出编辑，输入:wq并回车保存退出
    
    # 重启mysql服务器
    systemctl restart mysqld
    

• 🚀查询慢查询日志是否开启

• • -- OFF代表关闭
    -- ON表示开启
    SHOW VARIABLES LIKE 'slow_query_log';
    

• 🚀查看慢查询日志内容[Linux环境下]

• • # 慢查询日志保存在：/var/lib/mysql/localhost-slow.log 文件下
    cat /var/lib/mysql/localhost-slow.log
    

🚀SQL性能分析

– profile详情

profile详情：

• show profiles能够在做SQL优化时帮助我们了解时间都耗费在哪里了，通过have_profiling参数，能够看到当前MySQL是否支持查看profile详情。

• 🚀查看MySQL是否支持查看profile详情

• • select @@have_profiling;
    

• 🚀开启profiling (默认profiling是关闭的，可使用set语句在session/global级别开启)

• • -- 查看profiling开关是否开启
    select @@profiling;
    
    -- 开启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;
    

– explain执行计划

explain执行计划:

• EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

• 🚀查看SELECT语句执行计划（直接在select语句前加上explain / desc）

• • EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
    -- 或
    DESC SELECT 字段列表 FROM 表名 WHERE 条件;
    

• EXPLAIN执行计划 各个字段含义:

• • ⚪id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同则执行顺序从上到下 、id不同则**值越大越先执行 **）
  • ⚪select_type：表示select查询的类型，常见的有：SIMPLE（简单表，不使用表连接或子查询）、PRIMARY（主查询，即外层的查询）、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(select / where 之后包含了子查询)…
  • ⚪type：表示连接类型，性能由好到差的连接类型为：NULL、system、const、eq_ref、ref、range、index、all。
  • ⚪possible_key：显示可能引用在这张表上的索引，一个或多个。
  • ⚪Key：实际使用的索引，如果为NULL，表示没有使用索引。
  • ⚪Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
  • ⚪rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
  • ⚪filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。

• 

---

6. 索引的使用规则

最左前缀法则：

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

范围查询：

联合索引中，出现范围查询(>，

索引列运算：

不要再索引列上进行运算操作(max() avg() count()等)，否则索引会失效

字符串不加引号：

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

模糊查询：

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

使用or来连接条件：

用or分割开的条件，如果or前的条件中的列有索引，而or后面的列中没有索引，那么涉及的索引都不会被用到。即or连接的条件都需建立索引才能使得索引生效。

数据分布影响：

如果MySQL评估使用索引比全表更慢，则不使用索引。

SQL提示：

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

• use index()：建议查询时使用指定索引

• • SELECT 字段列表 FROM 表名 [USE INDEX(索引名)] WHERE 条件;
    

• ignore index()：建议查询时忽略指定索引

• • SELECT 字段列表 FROM 表名 [IGNORE INDEX(索引名)] WHERE 条件;
    

• force index()：查询时强制使用指定索引

• • SELECT 字段列表 FROM 表名 [FORCE INDEX(索引名)] WHERE 条件;
    

覆盖索引 ：

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

• explain查看查询计划时，最后一个字段Extra的显示：

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

• 思考题：

• 

• 答：为id、password字段创建联合索引，这样就实现了覆盖索引，且不需要回表查询，效率高。

前缀索引：

当字段类型为字符串(varchar,text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO,影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率 。

• 🚀选取部分前缀建立索引

• • CREATE INDEX 索引名 ON 表名(column(前缀长度));
    

  • 前缀长度：

  • • 可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

    • -- 计算tb_user表 字段email 的选择性
      select count(distinct email) / count(*) from tb_user;
      
      -- 原本基础上，设置前缀长度为5，计算选择性
      select count(distinct substring(email,1,5)) / count(*) from tb_user;
      

单列索引 和 联合索引：

• 单列索引：一个索引只包含单个列
• 联合索引：一个索引包含了多个列
• 在业务场景中，如果存在多个查询条件，考虑针对查询字段检索引时，建议建立联合索引，而非单列索引。

---

7. 索引设计原则

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

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

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

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

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

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

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

---