数据库索引问题

创建索引的代价

1. 维护代价、空间占用、查询时回表的代价；
2. 多字段的联合索引，需要考虑尽可能利用索引本身完成数据查询，减少回表的成本；
3. 按需创建，足够轻量。

保证索引的有效性

1. 对于后缀的匹配查询、查询中不包含联合索引的第一列、查询条件涉及函数计算等情况无法使用索引；
2. sql本身符合索引使用条件，mysql会通过评估各种查询方式的代价，来决定是否走索引，以及走哪个索引。

性能优化EXPLAIN

1. 通过执行计划或实际的效果来确认索引是否能有效改善性能问题；
2. 通过explain/explain analyze+sql查询执行计划看不出问题时，利用optimizer_trace查看详细执行计划进一步分析。

InnoDB如何存储数据

1. MySQL把数据存储和查询操作抽象成了存储引擎，不同的存储引擎，对数据的存储和读取方式各不相同；
2. InnoDB支持事务；数据保存在磁盘，处理在内存中进行。为减少磁盘读取次数，InnoDB采用页而不是行的粒度来保存数据，数据被分成若干页，以页为单位存在磁盘中。InnoDB的页大小一般16KB.
3. 各个数据页组成一个双向链表，每个数据页中的记录按照主键顺序组成单向链表；每一个数据页中有一个页目录，方便按照主键查询记录。
4. 页目录通过槽把记录分成不同小组，每个小组有若干条记录。有了槽，然后按照逐渐搜索页中记录时，采用二分法快速搜索，无需从最小记录开始遍历整个页中的记录链表。

聚簇索引（B+树）

//页目录时最简单的索引，通过对记录进行一级分组来降低搜索的时间复杂度。
//但这样能够降低的时间复杂度数量级，非常有限。
//为了方便定位记录所在的页，引入B+树，特点：
1. 最底层的节点叫叶子节点，用来存放数据；
2. 其他上层节点叫非叶子节点，仅用来存放目录项，作为索引；
3. 非叶子节点分为不同层次，通过分层来降低每一层的搜索量；
4. 所有节点按照索引键大小排序，构成一个双向链表，加速范围查找。
注意:
1. 由于数据在物理上只会保存一份，所以包含实际数据的聚簇索引只能有一个;
2. InnoDB会自动使用主键(唯一定义一条记录的单个或多个字段)作为聚簇索引的主键(如果没有主键，就选择第一个不包含NULL值得唯一列)。

非聚簇索引(二级索引/辅助索引 B+树结构)

1. 二级索引保存的时主键，不是实际数据，获得主键后去聚簇索引获得数据行。这个过程叫回表。
2. 创建二级索引需要考虑维护代价、空间代价和回表代价
3. 索引覆盖：
//使用二级索引查找主键后再查询聚簇索引，得到数据，(这里idx_name为联合索引)如：
explain select user_id,name from t_user where user_id = '123456'
//查看执行计划，Extra中显示Using index，查询直接查的二级索引免去了回表；//因为联合索引中保存了多个索引列的值，对于页中记录先按照字段1排序，如果相同再按照字段2排序；
//即如果需要查询的时索引列索引或联合索引能覆盖的数据，纳明查询索引本身已经‘覆盖’了需要的数据，不再需要回表查询。即索引覆盖。

mysql的执行计划

1. mysql选择索引，并不是按照where条件中列的顺序进行，而是在查询数据前，对可能的方案做执行计划；
2. 会考虑IO成本[把磁盘数据加载到内存]和CPU成本[检测数据是否满足条件和排序等CPU操作]；
3. 因此即便列有索引，甚至又多个可能的索引方案，mysql也可能不走索引；
4. 有时会因为统计信息的不准确或成本估算问题，实际开销会和mysql统计出来的差距较大，导致走全表扫描，人工干预：
explain select * from user force index(idx_name) where user_id>'111111' and create_time>'2021-12-08';
5. 打开/关闭optimizer_trace
    set optimizer_trace="enabled=on";
    explain select * from user force index(idx_name) where user_id>'111111' and create_time>'2021-12-08';
    select * from information_schema.OPTIMIZER_TRACE；
    set optimizer_trace="enabled=off";

mysql执行计划各参数含义：https://www.cnblogs.com/klvchen/p/10137117.html

tidb执行计划参数含义：https://book.tidb.io/session3/chapter1/sql-execution-plan.html

创建索引注意事项

1. 无需一开始就创建索引，可以等业务场景明确后，或者数据量超过1万、查询变慢后，再针对需要查询、排序或分组的字段创建索引。
2. 尽量索引轻量级的字段，比如能索引int字段不索引varchar字段。索引字段也可以是部分前缀，在创建的时候指定字段索引长度。针对长文本，考虑使用ES数据库。
3. 在sql语句中指定查询的必要字段而不是select *，甚至可以考虑使用联合索引来包含需要搜索的字段，既能实现索引加速，又能避免回表的开销。
4. 联合索引只能匹配左边的列；
5. 条件涉及函数无法走索引；
6. 索引只能匹配列前缀；如like ‘%123’,可以看到执行计划type=ALL全表扫描了，应把%放后边走前缀匹配，type=range表索引扫描；
7. 只会查询单个字段，可以考虑创建单独的索引；

 

 

mysql文档：https://dev.mysql.com/doc/internals/en/optimizer-tracing.html