mysql——索引篇

sqyaa.

已于 2024-05-10 22:02:10 修改

阅读量833

点赞数 17

分类专栏： Java知识 MySQL 文章标签：笔记

于 2023-12-14 18:04:51 首次发布

本文链接：https://blog.csdn.net/m0_74106420/article/details/134998793

版权

Java知识同时被 2 个专栏收录

89 篇文章 1 订阅

订阅专栏

MySQL

7 篇文章 0 订阅

订阅专栏

逻辑架构

索引是神魔

编辑优缺点编辑编辑

索引背后的数据结构 hash索引，B树，b+：

3. Change Buffer 的使用场景：

其他优化

逻辑架构

connectors, 连接层-服务层-引擎层-存储层

连接池：提供多个客户端与服务器交互的线程，

存储引擎：与底层文件进行交互。真正负责mysql的提取和存储

索引是神魔

本质：排好序的快速查找的数据结构

创建索引减少磁盘io次数加快查询速率

优缺点

索引背后的数据结构 hash索引，B树，b+：

查找都是索引操作，一般来说索引非常大，尤其是关系型数据库，当数据量比较大的时候，索引的大小有可能几个G甚主更多，为了减少索引在内存的占用，数据库索引是存储在外部磁盘上的。当我们利用索引查询的时候，不可能把整个索引全部加载到内存，只能 逐一加载，那么MySQL衡量查询效率的标准就是磁盘I0次数。

innoDB存储引擎中页的大小是16kb,16kb/(8+8B).=1000个key值，深度为3的B+树就可以维护10^3*10^3*10^3=10亿条数据记录1，但是在实际这些节点不一定会被全部填满，因此在数据库中，B+树的高度一般是2~4层。InnoDB存储引擎在设计时是将根节点常驻内存的，所以在查找某一键值的行记录最多只需要1~3层的磁盘io操作。

java中LInkedList是根据下标进行插入，所以在中间插入时间复杂度是O（n）,

顺序表擅长根据下标进行查询，适合尾插，链表擅长中间位置的插入和删除。

一个普通的二又搜索树, 时间复杂度是 O(N),因为会存在不平衡的极端情况(单枝树)，如果引入平衡机制(AVL树,红黑树)可以达到 O(logN)。

哈希表只能精准查询，不能进行模糊查询，不能将进行范围查询，数据的存储没有顺序，不能orderby .where name=‘张%’，也不能进行范围查询where id>100 and id<120。从效率上将，hash比树快。如果索引的重复项很多效率会降低

，比如根据性别

MySQL 中的 Memory 存储引擎支持 Hash 存储，如果我们需要用到查询的临时表时，就可以选择 Memory 存储引擎，把某个字段设置为 Hash 索引，比如字符串类型的字段，进行 Hash 计算之后长度可以缩短到几个字节。当字段的重复度低，而且经常需要进行等值查询的时候，采用 Hash 索引是个不错的选择。
另外，InnoDB本身不支持 Hash索引，但是提供自适应 Hash 索引(Adaptive Hash index)。什么情况下才会使用自适应 Hash 索引呢?如果某个数据经常被访问，当满足一定条件的时候，就会将这个数据页的地址存放到Hash 表中。这样下次查询的时候，就可以直接找到这个页面的所在位置。这样让 B+ 树也具备了 Hash 索引的优点。
InnoDB：

特点：支持事务、行级锁、外键约束等特性，是 MySQL 的默认存储引擎。
适用场景：适合处理事务处理和并发读写操作的场景，对于要求数据完整性和安全性较高的应用很有用。
MyISAM：

特点：不支持事务和行级锁，但查询速度快，适合于读密集的场景。
适用场景：适合用于读取频繁、写入不频繁的应用，例如数据仓库和日志记录。
MEMORY（或 HEAP）：

特点：将表数据保存在内存中，速度非常快，但一旦 MySQL 重启或崩溃，表中的数据就会丢失。
适用场景：适合用于临时数据或缓存等对数据持久性要求不高的场景。

红黑树,是可以精准匹配, 也能范围査询,也能模糊匹配
"孙%' 可以一定程度匹配字符串比较大小,就是先根据第一个字符比较的~%孙' 这个就没办法了.(仍然需要遍历了)
红黑树, 也是二叉树.每一个节点,最多两个子树,树的分叉少(度), 此时, 表示同样数量的结果集合,树的高度就会更高,有一万个元素使用二叉平衡树，log 2 1w，三叉树log 3 1w,.......，底数越大高度越小分叉越多查询的时候IO次数就越，n叉搜索树

索引既然能极大提高搜索的效率,我们肯定能先想到的数据结构就是哈希表,哈希表的查询时间复杂度是O(1),但是哈希表不适合做数据库的索引,原因在于哈希表只能比较相等,无法进行范围查询,像<>这样的操作都不行

对于B树每个节点上的度是不确定的，一个节点上保存N个key就划分成N+1个区间，每个区间都可以衍生一系列子树，访问一次硬盘读取一整个节点多个key，在进行几次比较，读一次硬盘相当于1W次比较，一个节点虽然能保存N个key,但也不是无限制的，达到一定规模就会触发节点打的分裂。删除达到一定数目也会触发节点合并.

B+树的特点:

1.B+树也是一个N叉树,增加了新的特点,每个节点上包含N个Key,N个Key划分出N个区间,每个区间的最后一个key就是最大值

2.父元素的Key会在子元素中出现并且为最大值,重复出现导致了,叶子节点就包含了所有数据的全集!

那么非叶子结点的所有元素都在叶子节点中体现

3.叶子节点用类似于链表的形式相连起来,构成了B+树

B+树这个数据结构做索引好处太明显了

1.既有B树高度比较低的特点,又更适合范围查询,比如查找>6且<15的元素,结果集非常容易取得,效率很高

2.对于所有的查询,都要落在叶子节点上,中间的比较次数是差不多的,查询操作比较均衡

对B树来说,在根节点或者深度不深的元素查询快,别的地方查询慢,不均衡,B+树都是一样的,都落在叶子节点上了

3.由于所有的Key都会在叶子节点中出现,因此非叶子节点不用存表的真实记录,只要把说有的数据行放在叶子节点上即可,非叶子节点只用存索引列的值,比如id这些,非叶子节点占用的空间就很小了,有可能在内存中放进去缓存了,更进一步降低了硬盘IO,提高了查询的速度

综上,B+树是非常适合作为索引的数据结构的

有的表不只是有主键索引,还有别的非主键列也有索引,此时会构造另一个B+树,非叶子节点里面存储这一列的Key,到了叶子节点这一层不再存储完整的数据行了,而是存储主键索引的id,那么使用主键索引查询时只用查一次B+树就好了,使用非主键列索引要先查一遍另外构造的B+树,然后查一次主键列的B+树(这个操作称为回表操作)

当前B+树这个结构适用于MySQL的InnoDB这个数据引擎,不同的数据库,不同的引擎存储数据的数据结构还是有差异的
——————————————

使用场景

使用场景
要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点:
。数据量较大，且经常对这些列进行条件查询。
。该数据库表的插入操作，及对这些列的修改操作频率较低。
。会占用额外的磁盘空间。

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

索引的使用

创建索引的使用

1.create table时创建；隐式

2.alter table；

3.create index ;

mysql> CREATE TABLE books (
    ->     BOOK_ID INT,
    ->     BOOK_NAME VARCHAR(20),
    ->     INDEX IDX_BNAME (BOOK_NAME)
    -> );
Query OK, 0 rows affected (0.06 sec)


mysql> show index from books;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| books |          1 | IDX_BNAME |            1 | BOOK_NAME   | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)

ALTER TABLE table_name
ADD INDEX index_name (column_name);

查看索引show index from 表名:

2.创建索引create index 索引名字 on 表名(列名);
drop index 索引名 on 表名

主键，unique，外键会自动生成索引。

创建索引需要针对现有数据进行大规模整理，所以在实际中是一个危险操作,如果表很大，提交创建索引就有可能把服务器卡住，一般创建索引是在创建表的时候就规划好了

非得创建也不是不行: 另外再搞一个机器, 部署mysql服务器,也创建同样的表, 并且把表上的索引创建好再把之前的机器上的数据给导入到新的mysq!服务器上.(导入数据的过程就可以控制节奏)多花点时间导数据,都没事,不要影响到原来服务器正常的运转~~
当所有数据都导入完毕,就可以使用新的数据库,替换旧的数据库了~

删除索引只能删除自己手动创建的索引，不能删除自动生成的索引，如主键外键unique。

索引类型

在后文，索引分类更进一步中有介绍

单列索引：针对单个列进行索引。
唯一索引：索引列的值必须是唯一的，用于保证数据完整性。
联合索引：针对多个列进行索引，可以提高联合条件查询的性能。
全文索引：用于全文搜索的索引，比如针对文本内容的搜索。
空间索引：用于地理空间数据类型的索引，比如地图坐标等。
主键索引
多列索引

聚簇索引和非聚簇索引是数据库中常见的两种索引类型，它们在实际数据库管理系统中有着不同的实现方式和特点。

### 聚簇索引（Clustered Index）

- **定义**：聚簇索引是一种索引机制，其顺序和表中数据行的物理顺序一致。换句话说，聚簇索引重新组织表的行，使行的物理顺序与索引的逻辑顺序相匹配。
- **基本原理**：对于使用聚簇索引的表，表的数据行存储方式会根据索引的键值进行重新排列，这样相邻的记录将在磁盘上变得更加接近，从而提高范围查询的性能
- **优点**：聚簇索引的主要优点是范围查询的性能较好，因为相关的数据行存储在一起，可以减少磁盘 I/O 操作。
-**缺点**：对于频繁的插入、删除操作，可能会导致页面分裂或碎片化，进而影响性能。此外，聚簇索引通常只能有一个(一般以主键作为)，因为数据行只能以一种顺序进行组织。

### 非聚簇索引（Non-clustered Index）

- **定义**：非聚簇索引是一种独立于实际数据行物理顺序的索引结构，它包含索引列的值和指向实际行的指针。

- **基本原理**：非聚簇索引通过维护一个独立的数据结构，使得索引列的值和对应的数据行指针之间建立映射关系，从而加快数据检索速度。

- **优点**：非聚簇索引可以提高数据检索的速度，而且在大部分情况下不会影响数据行的物理存储顺序。

- **缺点**：由于要维护额外的指针结构，会占用更多的存储空间。此外，对于范围查询的性能可能会略逊于聚簇索引。

总体来说，聚簇索引适合范围查询较多的情况，而非聚簇索引适合频繁的插入、删除操作或者需要覆盖索引的情况。在实际应用中，根据具体的业务需求和数据库访问模式，可以灵活选择使用聚簇索引和非聚簇索引来优化数据库的性能。

二级索引（辅助索引，飞聚簇索引）

上面介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用，因为在B+树的数据都是按照主键进行排序的，如果想用别的列作为搜索条件如何做呢？肯定不能从头到尾沿着链表一次遍历。

答：多建立几个B+树。可能会需要回表操作；

聚簇索引和非聚簇索引

1. 聚簇索引的叶子节点存储的是我们的数据记录，非聚簇索引的叶子结点存储的是数据的位置，非聚簇索引不会影响数据标的物理存储顺序。

2. 一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索。

3. 使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新，效率会比非局促索引低。

联合索引

就是非聚簇索引，是指在多个列上创建的索引，它可以同时包含多个列的值，并提供了在这些列上进行快速检索的能力。联合索引可以加快多列条件查询的速度，比单列索引更适合于涉及多个列的查询操作。

InnoDB数据存储

InnoDB存储引擎遵从行锁，事务锁，外键约束，ACID等等特性，适合对数据完整性事务要求高的场景，是Mysql的默认存储引擎，底层主要就是B+树，但在memory存储引擎中默认是hash结构。

索引是由存储引擎实现的，Mysql的存储引擎负责对数据的读取和写入，不同存储引擎的存放格式不同，比如Memory甚至不用磁盘来存储数据。

数据库存储的结构是：页；

磁盘与内存交互的基本单位是：页；

InnoDB将数据划分为若干页，InnoDB中页的大小默认是16KB。1KB=1024B=1024字节。

以页作为磁盘和内存之间交互的基本单位，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。也就是说，在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页(Page)，数据库I /0 操作的最小单位是页。一个页中可以存储多个行记录。

记录是按照行来存储，但是数据库的读取并不以行为单位，它的读取是一次读取（1次io）一页数据，

索引分类进一步

普通索引，唯一索引，

全文索引，空间索引，

单列索引，多列索引，

从物理上分为：聚簇索引和非聚簇索引；

从作用字段个数分为：单列索引和联合（多列）索引；

从功能逻辑上分为：普通索引，唯一性索引unique(自动生成)，主键索引，全文索引；

唯一性索引允许为空

一个表最多只能有一个主键索引，多个单列索引。主键索引是特殊的唯一性索引；

全文索引是目前搜索引擎使用的关键技术，能够利用分词技术等多种算法进行智能分析文本文字中关键字的频率，查询数据量大时，使用全文索引提高查询速度。MySQL5.6.4以后才开始支持，

在 SQL 查询语句前加上 EXPLAIN，数据库系统会返回该查询的执行计划信息，而不会实际执行该查询。

哪些情况适合创建索引

1.字段的数值有唯一性的限制

比如学生表的学号，阿里巴巴规定业务上具有唯一特性的字段，即使是组合字段，也必须构成唯一索引；

2.频繁作为 WHERE 查询条件的字段

3.经常 GROUP BY和 ORDER BY的列

索引就是让数据按照某种顺序进行检索或者存储.既有group by ,也有order by，需要创建联合索引的时候，就要group by需要的索引在前，order by的索引在后面

4. UPDATE、DELETE的WHERE条件列

因为是用where先查询出来然后再进行更新或者删除。如果更新的时候用的是非索引字段，提升的效率会很明显，因为非索引字段不需要进行维护。

5.DISTINCT 字段需要创建索引

select distinct(student_id) from 'student_info';去重

当id变成索引。索引会对数据按照某种顺序进行排序。排好序当然去重会很方便。

6.多表JOIN连接操作时，创建索引注意事项

7.使用列的类型小的创建索引

类型大小指的是数据范围的大小。比如：能用BigInt就不用Int,能用MediumInt就不用Int，

因为数据类型小，占用空间小，一个数据页放下的数据就越多，从而减少磁盘io次数带来的性能损耗，也就意味着可以可以把更多的数据页缓存在内存中，从而加快读写速率。

也因为数据类型小，查询的速率就会越快。

这个建议对主键索引也是很受用

8.使用字符串前缀创建索引

java开发手册上有写：在varchar字段建立索引时，必须指定索引的长度，没必要对全字段进行建立索引，根据文本区分度创建索引。

9.区分度高(散列性高)的列适合作为索引
10.使用最频繁的列放到联合索引的左侧

select* from student_info where id=1 and course_id=2;
11.在多个字段都要创建索引的情况下，联合索引优于单值索

注意事项：

单表索引最好不要超过6个，

1.每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间越大。

2.索引会影响insert,delete,update的性能，因为表中数据更改的同时，索引也会进行调整和更新。

3.优化器在选择如何优化的时候，会根据统一信息，对每一个可以用到的索引进行评估，如果同时有很多索引都可以进行查询，会增加MySQL优化器生成执行计划时间，降低查询性能，

哪些情况不适合创建索引

1.在where中使用不到的字段（包括group by,order by）不要设置索引
2.数据量小的表最好不要使用索引。不到1000行
3.有大量重复数据的列上不要建立索引

索引的价值是进行快速的定位，如果想要定位的数据有很多，那么索引就失去了它的使用价值，比如：性别。当数据重复度大，比如高于10%，就不需要对这个字段使用索引，
4.避免对经常更新的表创建过多的索引

频繁更新表就需要对索引也进行更新。过多的索引会降低查询的性能。
5.不建议用无序的值作为索引

比如uuid,身份证号，md5，hash。无序长字符串。
6.删除不再使用或者很少使用的索引

减少索引对更新的影响。
7.不要定义冗余或重复的索引

索引会提高查询速率，但是会占用磁盘空间，降低插入和更新的速率。

有哪些情况会导致索引失效?

都有哪些维度可以进行数据库调优?

简言之:
。索引失效、没有充分利用到索引--索引建立
。关联查询太多JOIN(设计缺陷或不得已的需求)--SQL优化
。服务器调优及各个参数设置(缓冲、线程数等)--调整my.cnf
。数据过多--分库分表

关于数据库调优的知识点非常分散。不同的 DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。这里我们分为三个章节进行细致讲解。
虽然 SQL 查询优化的技术有很多，但是大方向上完全可以分成 物理査询优化 和逻辑查询优化 两大块。
。物理查询优化是通过索引和 表连接方式 等技术来进行优化，这里重点需要掌握索引的使用。
。逻辑查询优化就是通过 SQL 等价变换 提升査询效率，直白一点就是说，换一种查询写法执行效率可能更高。

’‘’

mysql提高性能最有效的方式就是设计合理的索引。加快查询的速率。

索引可以进行快速定位某条记录，从而提高数据库查询的速度，提高数据库的性能。

没有所以，会进行全表的查询，查询速率很慢。

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器?基于cost开销它不是基于规则(Rule-Basedoptimizer)，也不是基于语义。怎么样开销小就怎么(CostBase0ptimizer来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

这个问题要分版本回答!!!版本不同可能我导致索引失效的场景也不同，直接给答案的都是耍流氓!!!这里回答基于最新 MySQL8 版本，MySQL8失效的以前版本也失效，MySQL8不失效的，以前可能会失效。
使用 like 并且是左边带 %,右边可以带会走索引(但是并不绝对，详细解释看下面 like 专题分析)。隐式类型转换，索引字段与条件或关联字段的类型不一致。(比如你的字段是int，你用字符串方式去查询会导致索引失效)。
在 where 条件里面对索引列使用运算或者使用函数
使用 OR 且存在非索引列
在 where 条件中两列做比较会导致索引失效.
使用 IN 可能不会走索引(MySQL环境变量 eq_range_index_dive_limit 的值对 IN 语法有很大影响，该参数表示使用索引情况下IN 中参数的最大数量。 MySQL 5.7.3 以及之前的版本中，eg_range_index_dive limit 的默认值为 10，之后的版本默认值为 200。我们拿 MVSOL8.0.19举例，eq_range index dive limit =200 表示当IN(..)中的值 >200 个时，该查询一定不会走索引。<=200 则可能用到索引。)
使用非主键范围条件查询时，部分情况索引失效，
使用 order by 可能会导致索引失效
is null is not null ≠可能会导致索引失效

索引失效是数据库性能优化中常见的问题之一。下面是一些常见的索引失效案例：

2.1 全值匹配：当查询条件中的所有列都包含在索引中，并且使用了全值匹配时，索引可以有效使用。

2.2 最佳左前缀法则：在多列索引中，只有使用最左侧列作为查询条件时，索引才能被有效使用。

2.3 主键插入顺序：对于使用自增主键的表，在插入新记录时，数据会按照主键的顺序进行插入，这样可以减少页分裂，提高插入性能。

2.4 计算、函数、类型转换：如果查询条件中对索引列进行计算、函数或类型转换，会导致索引失效。

2.5 类型转换导致索引失效：当查询条件中的索引列和查询语句中的值类型不匹配时，会导致索引失效。where name=123(✕)'123'

2.6 范围条件右边的列索引失效：当查询条件中存在范围条件，并且范围条件右边的列没有索引时，索引失效。
这是因为数据库在执行范围查询时，通常需要使用到范围条件右边的列来进行比较，
如果该列没有索引，就会导致索引无法发挥作用。

例如，如果有一个范围查询的条件类似于以下的例子：

```sql
SELECT * FROM table_name WHERE column1 = 'some_value' AND column2 > 100;
```

假设`column1`有索引，但`column2`没有索引，那么在这种情况下，
虽然可以使用`column1`的索引来筛选出符合条件的行，
但由于涉及到范围条件`column2 > 100`，
数据库无法利用索引来高效地执行范围查询，可能会导致全表扫描或性能下降。

为了避免这种情况，通常建议在涉及到范围查询的列上创建索引，
或者考虑创建包含范围查询列的复合索引。这样可以确保数据库能够有效利用索引来提高查询性能。
2.7 不等于(!= 或者<>)索引失效：不等于操作符通常无法有效使用索引，因为需要遍历所有的索引值。

2.8 is null可以使用索引，is not null无法使用索引：对于is null条件，可以使用索引进行优化，但是is not null无法使用索引。

2.9 like以通配符%开头索引失效：当like条件以通配符%开头时，索引无法被有效使用。

禁止做模糊或者全模糊。

2.10 OR前后存在非索引的列，索引失效：当OR条件中有一个条件列没有索引时，整个OR条件无法使用索引。

2.11 数据库和表的字符集统一使用utf8mb4：如果数据库和表的字符集不一致，就会进行转换。可能导致索引失效。

2.12 练习及一般性建议：为了避免索引失效，可以使用explain语句来分析查询语句的执行计划，并根据执行计划进行索引优化。此外，还可以使用合适的数据类型、避免类型转换，尽量避免使用范围查询等方法来改善查询效率。

外连接和内连接的查询优化

对于内连接，可以使用优化器判断谁是驱动表，谁是被驱动表；

如果只能有一个字段有索引，那莫有索引的表会被作为被驱动表出现。

如果俩个表都有索引，数据量小的表会作为驱动表，小表驱动大表。

当涉及到内连接和外连接时，以下是一些简单的示例：

### 内连接（Inner Join）示例：

假设我们有两个表：`students` 和 `grades`，
它们之间通过学生ID进行关联。
我们想要查询每个学生的姓名和对应的成绩，
只返回存在匹配的学生-成绩数据。

```sql
SELECT students.name, grades.grade
FROM students
INNER JOIN grades ON students.student_id = grades.student_id;
```

在这个示例中，只会返回那些在 `students` 表和 `grades` 表中
都有对应学生ID的记录，即匹配的学生-成绩数据。

### 左外连接（Left Outer Join）示例：

继续使用上面的 `students` 和 `grades` 表，
我们现在想要查询
所有学生的姓名以及他们的成绩，
如果学生没有成绩，则返回 NULL。

```sql
SELECT students.name, grades.grade
FROM students
LEFT JOIN grades ON students.student_id = grades.student_id;
```

这个查询将返回所有学生的姓名，无论他们是否有对应的成绩。
如果学生没有成绩记录，那么对应的成绩字段将包含 NULL 值。

### 右外连接（Right Outer Join）示例：

同样使用上面的 `students` 和 `grades` 表，
现在我们想要查询所有成绩以及对应的学生姓名，
如果成绩表中没有对应的学生记录，则返回 NULL。

```sql
SELECT students.name, grades.grade
FROM students
RIGHT JOIN grades ON students.student_id = grades.student_id;
```

这个查询将返回所有成绩记录，无论是否有对应的学生信息。
如果某个成绩记录没有对应的学生记录，那么学生姓名字段将包含 NULL 值。

通过这些示例，你可以更清楚地了解内连接和外连接在
实际查询中的应用以及它们之间的差异。根据具体需求选择
合适的连接类型可以更好地满足查询的要求。

保证被驱动表的JOIN字段已经创建了索引
·需要JOIN 的字段，数据类型保持绝对一致。
LEFT JOIN 时，选择小表作为驱动表，大表作为被驱动表。减少外层循环的次数。
INNERJOIN 时，MySQL会自动将小结果集的表选为驱动表。选择相信MySQL优化策略。
能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用JOIN 来代替子查询。
衍生表建不了索引

.GROUP BY优化

group by 使用索引的原则几乎跟order by一致，group by 即使没有过滤条件用到索引，也可以直接使用索引。group by先排序再分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size 参数的设置where效率高于having，能写在where限定的条件就不要写在having中了
减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。0rder by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
包含了order by、group by、distinct这些査询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

覆盖索引（Covering Index）

是一种特殊的索引优化技术，它包含了查询所需的所有列，使得数据库引擎可以仅通过索引就能够完成整个查询，而无需回表查找数据行。这样可以减少磁盘 I/O 和数据访问，从而提高查询性能。一个索引包含了满足查询结果的数据就叫做*覆盖索引。索引的字段正好是覆盖查询条件中所涉及的字段。

当一个查询涉及到的列都包含在一个索引中，并且查询语句只需要通过索引就可以获取到所需的数据，那么这个索引就是覆盖索引。通过使用覆盖索引，数据库可以避免回表（根据索引中的指针再去主表中查找数据行）查找数据行，减少额外的 I/O 操作，从而提升查询效率。

sum优点

1.避免innodb表进行二次查询，回表，减少对树的搜索次数，减少io次数：

对于Innodb表，二级索引在叶子结点中保存的是行的逐渐信息，如果使用二级索引查询数据，在查找到相应的键值后，还需通过主键进行二次查询才能找到我们真实需要的数据。

2.将随机io,变成顺序io加快查询速率。

弊端就是索引的维护需要代价

创建覆盖索引的SQL语句：
sql
CREATE INDEX idx_covering_index 
ON orders (order_date, customer_name, order_id);
在这个示例中，我们创建了一个覆盖索引 idx_covering_index，包
含了查询所需的所有列：订单日期、客户姓名和订单号。这样，当查
询涉及到这三列时，数据库引擎可以直接使用这个覆盖索引来满足
查询需求，而无需再去主表中查找数据行。

查询使用覆盖索引： 假设我们需要查询最近一周内的订单号、客
户姓名和订单日期，可以使用以下SQL查询语句：
sql
SELECT order_id, customer_name, order_date
FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 7 DAY);
由于覆盖索引包含了查询所需的所有列，数据库引擎可以直接使
用覆盖索引来获取所需的数据，而无需额外的回表操作，从而提高查询性能。

通过以上步骤，你可以成功地创建一个覆盖索引，并且利用它来
优化数据库查询性能。记得根据实际业务需求和查询场景来设计
合适的覆盖索引，以提升数据库的性能。

字符串的前缀索引

字符串的前缀索引是一种索引技术，用于加速对字符串列的查询操作。通过创建字符串的前缀索引，可以提高查询效率，特别是对于大型数据集和长字符串列时尤为有效。

在MySQL等数据库管理系统中，可以使用前缀索引来创建只包含字符串列前几个字符的索引，而不是整个列的索引。这样可以减少索引的大小，节省存储空间，并且提高查询性能。

但是可能会增加额外的扫描次数。使用前缀索引就用不上覆盖索引对查询性能的优化

下面是一个示例，演示如何创建字符串的前缀索引：

1. **创建带有前缀索引的表：**
假设我们有一个包含用户信息的表 users，其中包括姓名（name）列，我们希望对姓名列创建一个前缀索引。
```sql
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);
CREATE INDEX idx_name_prefix ON users (name(10)); -- 创建名为 idx_name_prefix 的前缀索引，只包含姓名列的前10个字符
```

在上述示例中，我们创建了一个名为 idx_name_prefix 的前缀索引，只包含姓名列的前10个字符。这样就可以加快对姓名列前缀的查询操作。

2. **查询使用前缀索引：**
当进行查询时，可以像平常一样编写SQL查询语句，数据库引擎会根据情况选择是否使用前缀索引来优化查询性能。
```sql
SELECT * FROM users WHERE name LIKE 'Jo%'; -- 使用前缀索引来查找以 'Jo' 开头的姓名
```
通过使用字符串的前缀索引，可以有效地提高对字符串列的查询效率，尤其是在对长字符串进行匹配时。在创建前缀索引时，需要根据实际情况选择合适的前缀长度，以兼顾索引大小和查询性能的平衡。

索引下推

索引下推利用了索引的特性，将查询条件尽可能地推到数据访问层级最低的地方，减少不必要的数据读取和处理，从而提高查询性能。

slect * from userinfo where name like '张%' and age=10;

支持ICP索引下推后，辅助索引引入age，在辅助表中查询如果已经不满足了就不必再去主表中查询了。所以主要作用就是减少了回表次数。

总结：

如果没有索引下推，当进行索引查询时，首先根据索引查询记录，再根据where进行过滤记录，

支持索引下推ICP后，Mysql会在取出索引的时候，判断是否可以用where条件过滤在进行查询，即提前执行where的部分过滤操作，在某些场景下可以大大减小回表次数，提高性能。

确保表上的列有适当的索引，以便数据库系统可以使用索引进行快速查找。
编写查询语句时，尽量将查询条件放在 WHERE 子句中，并确保这些条件与索引列相关。
避免在查询条件中使用函数、计算或类型转换等操作，这可能会导致索引失效而无法下推。
使用 EXPLAIN 命令查看查询执行计划，确认是否已经成功应用了索引下推优化
二级索引

普通索引 vs. 唯一索引

1. 查询过程：

普通索引：允许索引列中存在重复的值，可以加快查询速度，但不要求索引列的值必须唯一。
唯一索引：要求索引列的值必须是唯一的，确保数据的唯一性。在查询过程中，由于唯一索引的特性，数据库可以更快地确定是否存在特定的值。

2. 更新过程：

普通索引：在插入、更新或删除数据时，数据库管理系统只需更新普通索引，不需要检查索引列的唯一性。
唯一索引：在插入、更新或删除数据时，数据库管理系统需要确保唯一性约束不被破坏，因此对唯一索引的更新会比较耗时。

3. Change Buffer 的使用场景：

Change Buffer 是 InnoDB 存储引擎的一种技术，用于延迟执行对索引的修改操作，从而减少磁盘 I/O 操作。Change Buffer 主要用于处理非唯一索引的更新操作，对于普通索引和唯一索引都可能有所帮助，但更多地用于普通索引的更新。
普通索引：Change Buffer 可以帮助将更新操作暂时存储在内存中，延迟将这些更新应用到磁盘上的索引页，提高写入性能。减少读取磁盘的操作，并且数据读入内存需要占用buffer pool空间，这样避免了占用内存，
通过延迟更新操作，Change Buffer 可以有效地减少频繁的
磁盘写入操作，从而提高了数据库的写入性能。特别是在存在
大量短期内存更新操作的情况下，Change Buffer 技术可以
显著降低磁盘 I/O 压力，提升数据库写入性能。

然而，需要注意的是，这些更新操作并不会永远停留在内存中。
定期或在特定条件下，InnoDB 存储引擎会将 Change Buffer 
中的更新操作异步地合并到实际的索引页中，以确保数据的一致
性和持久性。
唯一索引：由于唯一索引的唯一性约束，对于唯一索引的更新操作需要立即检查约束条件，因此 Change Buffer 对唯一索引的更新帮助较小。
综上所述，普通索引和唯一索引在查询和更新过程中存在一些区别，而 Change Buffer 技术主要用于优化普通索引的更新操作，对于唯一索引的更新帮助相对较小。

change buffer用的是buffer pool里的内存，因此不能无限增大。change bufer的大小，可以通过参数innodb_change_buffer_max_size 来动态设置。这个参数设置为58的时候，表示change bufer的大小最多只能占用buffer pool的50%。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时寸changebuffer的使用效果最好。这种业务模型常见的就是 账单类、日志类 的系统。
反过来，假设一个业务的更新模式是写入之后马上会做査询，那么即使满足了条件，将更新先记录在changebufer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问I0的次数不会减少，反而增加了change bufer的维护代价。所以，对于这种业务模式来说，change buffer反而起到了副作用。

其他优化

当涉及到数据库查询优化时，以下是一些常见的策略和注意事项：

12.1 EXISTS 和 IN 的区分：小表驱动大表最高效，

12.2 COUNT(*) 与 COUNT(具体字段)效率：

COUNT() 会统计整个表的记录数，而 COUNT(具体字段) 则只会统计指定字段非空的记录数。一般来说，COUNT() 的效率会略低于 COUNT(具体字段)，因为 COUNT(*) 需要检查所有的字段，而 COUNT(具体字段) 只需检查指定字段。
环节1: COUNT(*)和 COUNT(1)都是对所有结果进行 COUNT，COUNT(*)和COUNT(1)本质上并没有区别(二者执行时间可能略有差别，不过你还是可以把它俩的执行效率看成是相等的)。如果有 WHERE 子句，则是对所有符合筛选条件的数据行进行统计;如果没有 WHERE 子句，则是对数据表的数据行数进行统计。
环节2:如果是 MyISAM 存储引擎，统计数据表的行数只需要0(1)的复杂度，这是因为每张 MyISAM 的数据表都有一个 meta 信息存储了 row_count 值，而一致性则由表级锁来保证。
环节3:在 InnoDB 引擎中，如果采用 COUNT(具体字段)来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引(非聚簇索引)。对于 COUNT(*)和 COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。
如果有多个二级索引，会使用 key_len 小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

如果是 InnoD8 存储引擎，因为 InnoDB 支持事务，采用行级锁和 MVCC 机制，所以无法像 MyISAM 一样，维护一个row_count变量，因此需要采用扫描全表，进行循环+计数的方式来完成统计。

12.3 关于 SELECT(*)：

在表查询中，建议明确字段，不要使用*作为查询的字段列表，推荐使用SELECT<字段列表>查询。原因:① MySQL 在解析的过程中，会通过査询数据字典将"*"按序转换成所有列名，这会大大的耗费资源和时间。② 无法使用覆盖索引

12.4 LIMIT 1 对优化的影响：

当只需要获取单个结果时，使用 LIMIT 1 可以提高查询效率。LIMIT 1 可以告诉数据库在找到第一个匹配结果后立即停止查询，从而节省资源和时间。

12.5 多使用 COMMIT：

在事务处理过程中，多使用 COMMIT 可以帮助释放数据库锁、减少事务处理时间，以及避免数据不一致等问题。合理使用 COMMIT 可以提高数据库的并发性能。

通过合理利用这些查询优化策略，可以有效地提升数据库查询的性能和效率，减少不必要的资源消耗，提升系统的响应速度和稳定性。

后续小问题总结

为甚麽尽量避免使用selelct * from ?

1.*，解析器会把*解析成各种字段，进而根据表中的每个字段进行查找，增加了解析器的成本；

2.select* 不走覆盖索引（即根据索引可以得到所需的所有数据的索引）会产生大量的回表操作；

3.实际使用过程中并不需要查询所有，反而是浪费了CPU，内存资源。

4.文本传输，大字段传输会增加网络消耗。