深入了解MySQL数据库的索引及底层结构

梦里梦外; 2022-12-23 13:26 152阅读 0赞

**导致SQL执行慢的原因？**  
1：硬件问题（网路慢，内存不足，io吞吐量小，磁盘满）  
2：SQL没有使用索引或者索引失效  
3：数据过多  
4：服务器调优  
**问题解决入口：**  
1：开启慢查询日志，设置阈值，在生产上执行一天，在看SQL比较慢的  
2：使用explain和慢SQL分析。  
3：show profile比explain更进一步的执行细节，可以查询到执行每一个SQL都干的事情，花费多长时间  
4：找DBA或者运维对mysql进行服务器的参数调优  
**什么是索引？**  
索引是帮助MySQL高效获取数据的数据结构（快速查找排好序的数据结构）  
**MySQL索引的二种结构：b+Tree索引和hash索引**  
索引其实也是一张表，该表保存了主键和索引的字段，并指向实体表的记录  
索引的缺点：索引提高了查询速度，但是降低了更新表的速度，更新表的时候，MySQL会保存数据还需要保存一个索引文件，建立索引会占用磁盘空间。  
**索引类型：  
FullText、hash、BTREE、RTREE**  
**FULLTEXT：全文索引，目前只有myisam引擎支持，其可以在create table ，alter table 、create index使用，目前只有char varchar text 上创建全局索引。  
全文索引是为了解决where name like “%word%” 针对文本的模糊查询效率低的问题。  
hash索引：哈希索引是基于哈希表实现的，只有精确的匹配索引的所有列的查询才有效，对于每一行的数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是较小的值，不同健值得行计算出来的哈希码是不一样的，哈希索引将所有的哈希码都存储的索引中，同时在哈希表中保存指向每一个数据行的指针。  
在mysql中，只有memory引擎支持哈希索引，这就是memory引擎表的默认类型，memory引擎同时致辞b-tree索引，memory支持非唯一哈希索引的  
hash冲突：避免哈希 冲突，必须在where条件中代入哈希值和对应列值。不带人就直接使用CRC32()的哈希值查询  
BTREE索引：btree又叫做多路平衡查找树。  
![在这里插入图片描述][20201125203556412.png_pic_center]  
b+tree的每一个叶节点增加一个纸箱相邻叶子节点的指针，就形成带有顺序访问指针的b+tree，提高区间访问性能。比如要查询key为18到49的所有数据记录，当找到18的时候，只需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，极大的提高区间查询的效率（无需返回到上层父节点重复遍历查找减少IO操作）  
RTREE索引**：RTREE在MySQL很少使用，只支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innnodb、ndb、archive  
相当于BTREE，RTREE的优势在于范围查找；  
**索引的种类：  
普通索引，唯一索引，组合索引，全文索引**  
**MySQL聚集索引和非聚集索引的区别？**  
**根本区别：聚集索引和非聚集索引区别在表记录排列的顺序和索引的排列顺序是否一致**  
聚集索引：定义，数据的行的物理地址顺序与列值（主键）的逻辑顺序相同，一个表中只能拥有一个聚集索引。  
第一：数据如何存储？  
聚集索引的叶子节点存储是的主键+数据文件  
第二：如何查找？  
非聚集索引：定义：表记录的排列顺序和索引的排列顺序不一致  
第一：数据如何存储？  
非主键创建的索引（二级索引）  
第二：如何查找？  
优缺点：  
**聚集索引：  
优点：聚集索引表记录的排序和索引的排列顺序一致，所以查询的效率快，因为只要找第一个索引值的记录，其余的连续性的记录在物理表中也会连续的存放，一起就可以查询出来（也就是说，主键和数据文件存在一起）  
缺点：新增比较慢，因为为了保证表中的记录的物理顺序和索引顺序一致，在记录插入的时候，会对数据页重新排序  
非聚集索引：  
索引的逻辑顺序与磁盘上行的物理存储顺序不同，非聚集索引在叶子节点存储的是主键和索引列；  
因此当我们使用非聚集索引查询数据的时候，需要拿到叶子节点上的主键在去表中查到想要查到的数据，这个过程叫做回表  
聚集索引和非聚集索引的存储区别？  
聚集索引在叶子节点存储的是数据文件  
非聚集索引在叶子节点存储的是主键和索引列**  
索引的底层数据结构（重点，面试问的比较多）：  
**哈希索引：哈希索引就是采用一定的哈希算法，只需一次哈希算法即可立刻定位到相应的位置，速度比较快（实质：利用哈希值进行快速的定位）  
哈希索引的缺点：  
1：哈希索引没办法利用索引进行排序  
2：不能进行多字段的查询  
3：存在大量重复的健值的情况下，哈希索引的效率就低（因为存在重复的键值就会出现哈希碰撞）  
4：不支持范围查询  
MySQL的常用的innodb引擎中使用的是b+tree的索引  
B+TREE索引：  
非叶子节点的子树指针与关键字个数相同  
所有的叶子节点都增加一个链指针  
所有的关键字都会在叶子节点上出现  
所有的关键字都出现在叶子节点的链表。**  
问题 一：  
在查询条件大于2以上的时候，创建多个单列索引还是创建一个联合索引好？他们之间的区别是什么？哪个效率高？  
举列说明：  
![在这里插入图片描述][2020112611361148.png_pic_center]  
建立了一张表，里面建立了三个单列索引userId,mobile,billMonth。然后进行多列查询

explain select * from `t_mobilesms_11` where userid = '1' and mobile = '13504679876' and billMonth = '1998-03'

![在这里插入图片描述][20201126113832917.png_pic_center]  
我们发现查询时只用到了userid这一个单列索引，这是为什么呢？因为这里取决于MySQL优化器的优化策略  
当多条件联合查询时，优化器会评估哪一个条件的索引效率高，会选择最佳的索引去使用。  
因此：多个单列索引在条件查询时候优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引都用上。多个索引的底层都是建立在多个B+TREE，比较占用空间，也就是会浪费搜索效率，所以多条件联合查询最好建联合索引，  
**联合索引：遵循左匹配原则，最左优先，以最左边的为起点任何连续的索引都会匹配上，如果出现不连续，就会出现不匹配  
联合索引的失效情况？**  
1：违法最左匹配原则（连续性），列如：建立索引（a,b,c），这是时候能建立索引的有（a）,(a,b),(a,b,c)三种查询  
2：在索引列上使用（计算，函数，类型转换等操作，）都会导致索引失效而进行全表扫描  
3：使用不等于 （!=）  
4：like中以通配符开头（"%abc"）  
5：字符串不加单引号索引失效  
6：or连接索引失效  
7：order by （正常索引参与排序，没有违反左匹配原则）一旦违反左匹配原则，就会导致额外的文件排序  
8：group by 违反左匹配原则，会导致产生临时表

explain select name,age from user where name = 'zhangsan' group by pos,age;

![在这里插入图片描述][20201126140617676.png_pic_center]

[20201125203556412.png_pic_center]: /images/20221120/aaab8cfc203347e2ae23604605a62d22.png
[2020112611361148.png_pic_center]: /images/20221120/aa22f83af44b4aa8815f4ebad0e78c7c.png
[20201126113832917.png_pic_center]: https://img-blog.csdnimg.cn/20201126113832917.png#pic_center
[20201126140617676.png_pic_center]: https://img-blog.csdnimg.cn/20201126140617676.png#pic_center