深入浅出数据库索引

野性酷女 2022-04-08 11:25 221阅读 0赞

1.索引的作用：提高数据查询效率  
2.常见索引模型：哈希表、有序数组、搜索树  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70 1]

![二叉树][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70 2]  
3.哈希表：键 - 值(key - value)。  
4.哈希思路：把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置  
5.哈希冲突的处理办法：链表  
6.哈希表适用场景：只有等值查询的场景  
7.有序数组：按顺序存储。查询用二分法就可以快速查询，时间复杂度是：O(log(N))  
8.有序数组查询效率高，更新效率低  
9.有序数组的适用场景：静态存储引擎。  
10.二叉搜索树：每个节点的左儿子小于父节点，父节点又小于右儿子  
11.二叉搜索树：查询时间复杂度O(log(N))，更新时间复杂度O(log(N))  
12.数据库存储大多不适用二叉树，因为树高过高，会适用N叉树  
13.InnoDB中的索引模型：B+Tree  
14.索引类型：主键索引、非主键索引  
主键索引的叶子节点存的是整行的数据(聚簇索引)，非主键索引的叶子节点内容是主键的值(二级索引)  
15.主键索引和普通索引的区别：主键索引只要搜索ID这个B+Tree即可拿到数据。普通索引先搜索索引拿到主键值，再到主键索引树搜索一次(回表)  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70 3]  
16.一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。  
17.从性能和存储空间方面考量，自增主键往往是更合理的选择。

思考题：

重建索引 k

alter table T drop index k;
    alter table T add index(k);

重建主键索引

alter table T drop primary key;
    alter table T add primary key(id);

通过两个 alter 语句重建索引 k，以及通过两个 alter 语句重建主键索引是否合理。  
重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB。

[https://time.geekbang.org/column/article/69236][https_time.geekbang.org_column_article_69236]

1、覆盖索引：如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取正行数据

2、最左前缀：联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符  
3、联合索引：根据创建联合索引的顺序，以最左原则进行where检索，比如（age，name）以age=1 或 age= 1 and name=‘张三’可以使用索引，单以name=‘张三’ 不会使用索引，考虑到存储空间的问题，还请根据业务需求，将查找频繁的数据进行靠左创建索引。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70 4]  
4、索引下推：like 'hello%’and age >10 检索，MySQL5.6版本之前，会对匹配的数据进行回表查询。5.6版本后，会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70 5]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70 6]

[https://time.geekbang.org/column/article/69636][https_time.geekbang.org_column_article_69636]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70]: /images/20220405/367641900bc7417189c6af50e2f5938b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70 1]: /images/20220405/0fa771b32fde43d0a0b0864871528b77.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70 2]: /images/20220405/74765ecaef4340f8b80af76f5b200b56.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70 3]: /images/20220405/cf27111120f74cc79caca4602a3cd06a.png
[https_time.geekbang.org_column_article_69236]: https://time.geekbang.org/column/article/69236
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70 4]: /images/20220405/2e48d614861f41a4b17293f77c29c89b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70 5]: /images/20220405/97f8faad11984082adf11a1fcadb0c76.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70 6]: /images/20220405/4df18b3af8a84961ae4ba459adf7c7ab.png
[https_time.geekbang.org_column_article_69636]: https://time.geekbang.org/column/article/69636