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

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

MySQL索引

⑩② 【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...);
• 🚀查看索引
• • SHOWINDEXFROM 表名;
• 🚀删除索引
• • DROPINDEX 字段名 ON 表名;
• -- 演示：-- 操作的表tb_usercreatetable tb_user( id intprimarykeyauto_incrementcomment'主键', name varchar(50)notnullcomment'用户名', phone varchar(11)notnullcomment'手机号', email varchar(100)comment'邮箱', profession varchar(11)comment'专业', age tinyintunsignedcomment'年龄', gender char(1)comment'性别 , 1: 男, 2: 女',statuschar(1)comment'状态', createtime datetimecomment'创建时间')comment'系统用户表';-- 查询索引showindexfrom tb_user;-- 查询所有，竖向显示showindexfrom tb_user \G;-- 1.name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。createindex idx_user_name on tb_user(name);-- 2.phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。createuniqueindex idx_user_phone on tb_user(phone);-- 3.为profession、age、status创建联合索引。createindex idx_user_pro_age_sta on tb_user(profession,age,status);-- 4.为email建立合适的索引来提升查询效率。createindex idx_user_email on tb_user(email);-- 删除索引idx_user_emaildropindex 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的访问频次
• • -- 模糊匹配中有七个下划线'_'SHOWGLOBALSTATUSLIKE'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;-- 开启profilingset 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）
• • EXPLAINSELECT 字段列表 FROM 表名 WHERE 条件;-- 或DESCSELECT 字段列表 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 表名 [USEINDEX(索引名)]WHERE 条件;
• ignore index()：建议查询时忽略指定索引
• • SELECT 字段列表 FROM 表名 [IGNOREINDEX(索引名)]WHERE 条件;
• force index()：查询时强制使用指定索引
• • SELECT 字段列表 FROM 表名 [FORCEINDEX(索引名)]WHERE 条件;

 覆盖索引

：

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

• explain查看查询计划时，最后一个字段Extra的显示：
• • using index condition：查找使用了索引，但是需要回表查询数据。- using where；using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据。
• 思考题：
• 
• 答：为id、password字段创建联合索引，这样就实现了覆盖索引，且不需要回表查询，效率高。

前缀索引

：

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

• 🚀选取部分前缀建立索引
• • CREATEINDEX 索引名 ON 表名(column(前缀长度));- 前缀长度：- - 可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。- -- 计算tb_user表 字段email 的选择性selectcount(distinct email)/count(*)from tb_user;-- 原本基础上，设置前缀长度为5，计算选择性selectcount(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值时，它可以更好地确定哪个索引最有效地用于查询。