数据库索引：索引并不是万能药【最小单位是数据页，不是行在InnoDB中】

qq_48704877

已于 2022-10-19 20:07:47 修改

阅读量58

点赞数

分类专栏：数据库文章标签：数据库

于 2022-10-14 00:32:32 首次发布

本文链接：https://blog.csdn.net/qq_48704877/article/details/127311738

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

InnoDB在保存数据的时候，I你弄DB采用页而不是行的粒度来保存数据，当数据被分成若干页，以页为单位保存在磁盘中，InnoDB的页大小，一般16KB

各个数据页组成一个双向链表，单个数据页中都是按照主键进行连接的单向链表，每一个数据也有一个目录，方便按照主键查询记录，数据页结构如下：

如何查找：
1: 先二分得出槽中间位是 (0+6)/2=3，看到其指向的记录是 12＜15，所以需要从 #3 槽后继续搜索记录；
2：再使用二分搜索出 #3 槽和 #6 槽的中间位是 (3+6)/2=4.5 取整 4，#4 槽对应的记录是 16＞15，所以记录一定在 #4 槽中

创建索引缺点：1:维护代价 2：空间代价 3：回表代价

`举例子：维护代价`

向带有索引的表中插入数据：在向数据库中插入10万条数据，在插入的时候索引会随着不断改变，这个时候会延长插入的时间
页中的记录是随着索引从小到大顺序存放的，存入数据就需要往页中插入数据，页满了就需要新创建一个页，把现在页的数据迁移过去，这就是页分裂，
如果删除了很多数据页比较空闲，还需要进行页合并，页合并和页分离都有IO代价，并且可能在操作过程中产生死锁

索引实践：

第一，无需一开始就建立索引，可以等到业务场景明确后，或者是数据量超过 1 万、查询变慢后，再针对需要查询、排序或分组的字段创建索引。创建索引后可以使用 EXPLAIN 命令，确认查询是否可以使用索引。我会在下一小节展开说明
。
第二，尽量索引轻量级的字段，比如能索引 int 字段就不要索引 varchar 字段。索引字段也可以是部分前缀，在创建的时候指定字段索引长度。针对长文本的搜索，可以考虑使用 Elasticsearch 等专门用于文本搜索的索引数据库
。
第三，尽量不要在 SQL 语句中 SELECT *，而是 SELECT 必要的字段，甚至可以考虑使用联合索引来包含我们要搜索的字段，既能实现索引加速，又可以避免回表的开销。

Mysql在查询数据之前，会先对可能的方案做执行计划，然后依据成本走那个执行计划，[成本：IO成本， CPU成本]

IO 成本，是从磁盘把数据加载到内存的成本。默认情况下，读取数据页的 IO 成本常数是 1（也就是读取 1 个页成本是 1）

CPU 成本，是检测数据是否满足条件和排序等 CPU 操作的成本。默认情况下，检测记录的成本是 0.2。

强制走索引：

强制走索引
EXPLAIN SELECT * FROM person FORCE INDEX(name_score) WHERE NAME >‘name84059’ AND create_time>‘2020-01-24 05:00:00’

使用optimizer_trace 查看优化器Cost成本

SET optimizer_trace="enabled=on";
SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-01-24 05:00:00';
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";

### 查询结果：
{
  "index": "name_score",
  "ranges": [
    "name84059 < name"
  ],
  "rows": 25362,
  "cost": 30435,
  "chosen": false,
  "cause": "cost"
},


`第一个误区是，考虑到索引的维护代价、空间占用和查询时回表的代价，不能认为索引越多越好。索引一定是按需创建的，并且要尽可能确保足够轻量。一旦创建了多字段的联合索引，我们要考虑尽可能利用索引本身完成数据查询，减少回表的成本。
第二个误区是，不能认为建了索引就一定有效，对于后缀的匹配查询、查询中不包含联合索引的第一列、查询条件涉及函数计算等情况无法使用索引。此外，即使 SQL 本身符合索引的使用条件，MySQL 也会通过评估各种查询方式的代价，来决定是否走索引，以及走哪个索引。`






### 索引排序问题：排序是否走索引
SQL中带order by且执行计划中Extra 这个字段中有"Using index"或者"Using index condition"表示用到索引，并且不用专门排序，因为索引本身就是有序的；
如果Extra有“Using filesort”表示的就是需要排序；

排序时：MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。sort_buffer_size（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。
上述排序中，只对原表的数据读了一遍，剩下的操作都是在 sort_buffer 和临时文件中执行的。但这个算法有一个问题，就是如果查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。所以如果单行很大，这个方法效率不够好。max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。称为 rowid 排序；
rowid排序简单的描述就是先取出ID和排序字段进行排序，排序结束后，用ID回表去查询select中出现的其他字段，多了一次回表操作，
对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。