MySQL索引

性能优化-MySQL索引
使用索引可以通过创建特定的数据结构，将数据按照某种规则有序地组织起来，从而加快数据的查找速度。
索引可以在数据库表的一列或多列上创建，它们包含了对应列值的引用和指针，使得数据库系统可以快速定位到需要的数据。
通过使用索引，数据库系统可以根据索引的排序和搜索算法，快速定位到符合查询条件的数据，提高查询的效率。
优点：
1. 提高数据查询的效率，降低数据库的IO成本。
2. 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。
缺点：
1. 索引会占用存储空间。
2. 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。

3.2 MySQL索引-结构
MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。
我们平常所说的索引，如果没有特别指明，都是指默认的 B+Tree 结构组织的索引。

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

3.3 MySQL索引-语法
什么字段适合添加索引？
- 作为查询条件的字段

3.3.1 创建索引
创建索引语法如下：
create  [ unique ]  index 索引名 on  表名 (字段名,... ) ;

案例：为device_data 表的iot_id和product_key字段建立一个索引
create index idx_iot_id_product_key on device_data (iot_id, product_key);
在创建表时，如果添加了主键和唯一约束，就会默认创建：主键索引、唯一索引
创建索引的选择，一般情况下，哪些字段作为查询条件出现的比较多，就使用哪些字段来创建索引

3.3.2 查看索引
查看索引的语法：
show  index  from  表名;
3.3.3 删除索引
语法：
drop  index  索引名  on  表名;
案例：删除 device_data 表中ot_id和product_key字段的索引
drop index idx_iot_id_product_key on device_data ;

注意事项：
- 主键字段，在建表时，会自动创建主键索引
- 添加唯一约束时，数据库实际上会添加唯一索引

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

在InnoDB的存储引擎中，根据索引的村相互形式，又可以分为一下两种：
聚集索引（聚簇索引）
二级索引（非聚簇索引）

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

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

3.5 索引失效检查
可以采用EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息
可以采用explain或者desc命令获取MySQL如何让执行select语句的信息
语法：
EXPLAIN   SELECT   字段列表   FROM   表名   WHERE  条件 ;
explain select 字段列表 from 表名 where 条件;
- possible_key  当前sql可能会使用到的索引
- key 当前sql实际命中的索引
- key_len 索引占用的大小
- Extra 额外的优化建议

- type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、 index、all
  - system：查询系统中的表
  - const：根据主键查询
  - eq_ref：主键索引查询或唯一索引查询
  - ref：索引查询
  - range：范围查询
  - index：索引树扫描
  - all：全盘扫描
- 索引失效场景
  - 不符合最左匹配法则

面试题：
  
23. MySQL的索引是如何创建的？是什么场景？
好的！养老系统中有一个家属端，在方便老人的家属查看老人的信息，
比如就可以实时的查看老人的健康数据，也可以查看老人的健康历史数据。
由于目前设备上报的数据都是存储到了MySQL的表中，设备上报的数据是巨大的，
一天大概能上报70万的数据，当家属查看老人的历史数据的时候，
由于表存储的数据较多，查询的效率就会很低。所以我们的解决方案就是给表中的查询字段添加了索引，
这样查询效率就得到了很大的提升

24. MySQL索引底层的实现原理是什么？
MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引，选择B+树的主要的原因是：
- 第一：阶数更多，路径更短
- 第二：磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据
- 第三：B+树便于扫库和区间查询，叶子节点是一个双向链表

25. 如何检查MySQL索引是否失效？
我们通常会使用mysql自动的执行计划explain来去查看这条sql的执行情况，
比如在这里面可以通过key和key_len检查是否命中了索引，
如果本身已经添加了索引，也可以判断索引是否有失效的情况

26. 关于索引失效，你遇到过哪些？
嗯，这个情况比较多，我说一些自己的经验，以前遇到过的
比如，索引在使用的时候没有遵循最左匹配法则，
第二个是，模糊查询，如果%号在前面也会导致索引失效。
如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。
我们之前还遇到过一个就是，如果使用了复合索引，中间使用了范围查询，右边的条件索引也会失效
所以，通常情况下，想要判断出这条sql是否有索引失效的情况，可以使用explain执行计划来分析

27. 什么是聚集索引和二级索引，什么是回表查询？
好的~，聚集索引主要是指数据与索引放到一块，
B+树的叶子节点保存了整行数据，有且只有一个，
一般情况下主键在作为聚集索引的
非聚集索引存储的值的的特点是，数据与索引分开存储。
B+树的叶子节点保存对应的主键，可以有多个，
一般我们自己定义的索引都是非聚集索引
什么是回表查询？
嗯，其实跟刚才介绍的聚簇索引和非聚簇索引是有关系的，
回表的意思就是通过二级索引找到对应的主键值，
然后再通过主键值找到聚集索引中所对应的整行数据，
这个过程就是回表

原文地址：https://blog.csdn.net/2301_78554215/article/details/143579834

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！