mysql索引的数据结构
1.索引是什么？索引的优缺点
索引是帮助mysql高效获取数据的排好序的数据结构。
所谓的排好序，就是按照从左往右依次递增的顺序排好序的。
2.索引的分类：
1.主键索引：数据列不能重复，不允许为null值，一个表只能有一个主键
2.唯一索引：数据列不能重复，不允许为null值，一个表允许多个列创建唯一的索引
创建方式：
(a). alter table 表名 add unque (字段名)创建唯一索引。
(a). alter table 表名 add unque (字段名1,字段名2，字段名3)创建唯一组合索引。
3.普通索引：基本的索引类型，没有唯一性约束，允许为null值
创建方式：
alter table 表名 add index 索引名 （字段名）创建普通索引
alter table 表名 add index 索引名 （字段名1，字段2，字段3）创建普通组合索引
4.全文索引：目前搜索引擎使用的一种关键性技术
创建方式：alert table 表名 add fulltext(字段名);创建全局索引
3.mysql的索引的底层的数据结构
3.1.B-Tree树的特点
a.叶子节点具有相同的高度，叶子节点的指针为空
b.所有的索引元素不重复
c.节点中的数据索引从左往右依次递增的
d.在根节点上面存的既有索引，还有索引所在行的数据， 这样的话，会使得存更多的页数，更多的I/O交互，会使得查询速度比较慢。
3.2.B+Tree树的特点
a.在根节点上面存的只是只是索引，不包含数据，这样的话，每页可以存更多的索引而且是可以存放更多的数据，减少了I/O的次数，提高了查询的性能
b.在叶子节点中包含了所有的索引，在叶子节点上面存放的还有索引对应的数据。这样也是可以查询更快了，防止回表的操作。
c.叶子节点之间是用双向的指针相连的，提高了区间的访问性能
mysql查看每页的sql的文件页的大小(默认的是16K)
show GLOBAL STATUS LIKE 'INNODB_page_size'
注意：真正的sql查询慢的原因是因为每次将叶子节点的数据laod到内存中的时候，每次I/O的时间是非常的消耗时间的，每次sql定位数据的时间相比于这个I/O的时间是可以忽略不计的。
mysql默认的每页的文件大小是16kb.到底可以存放多少索引数据呢？
假如mysql的主键索引类型是BigInteger是占用了8个字节的，就是占用8b,而B+树根节点之间的索引地址大概是占用了6个字节，就是占用6b这样的话每页只是存放根数据的话是161000/（8+6）就是说可以存放1170左右的每页的数据就是存放了1170个索引，当在叶子节点存放的有索引所在行的数据的话，就按照每个数据是放1k也就是说可以放16个这样的话，存放3层的数据量=11701170*16=2100万左右的也就是说当存放2100万的数据存放在B+树的话，树的高度只有3层，这样查找的情况下只需要经过3次的磁盘I/O就可以找到需要的元素了，速度是非常快的。
3.3Hash索引
对索引的key进行一次hash计算就可以定位出数据存储的位置很多时候Hash索引要比B+ 树索引更高效仅能满足 “=”，“IN”，不支持范围查询，而在sql中会有大量的范围查询的还有hash冲突问题
3.4B树和B+树的区别？
1.b树的叶子节点和根节点上面存放的既有索引元素，也有索引元素所在的行数据。B+树在叶子节点上面存放的是索引和索引元素所在行的数据，而在根节点上面存放的只有 索引元素和下一级索引元素的地址，这样的话可以用更少的树的高度，存放更多的数据量
2.在b树中可以将键和值存放在内部节点和叶子节点，b+树中内部节点都是键没有值，叶子节点同时存放键和值
3..b树的叶子节点是相互独立的，而b+树的叶子节点是一条链相连的 (在叶子节点之间会有双向的指针相连接的)
b树的优点：在b树中可以将键和值存放在内部节点和叶子节点，因此把频繁访问的数据放在靠近根节点的地方将会大大的提高热点数据的查询效率，这种特性使得b树在特定的数据重复查询多次时会更加高效。
b+树的优点：b+树中内部节点都是键没有值，因此一次读取可以从内存页面中获取更多的键，就有利于缩小查找的范围。b+树的叶子节点是一条链相连的 因此当需要进行一次的全表数据遍历的时候可以花费更短的时间。
4.MySql存储引擎
注意：MyISAM存储引擎和InnoDB存储引擎针对的是表的结构，而不是数据库的结构
4.1MyISAM存储引擎索引实现
MyISAM的索引文件(表名.MYI)，表结构文件（表名.frm），数据文件(表名.MYD)是分开的（也叫作非聚集索引）
当表结构的存储引擎是MyISAM的时候，进行索引查询的话，首先是从.MYI文件（索引文件）中找到所在的索引，在该索引的叶子节点上面存放的是该索引数据的数据文件在磁盘上的地址如果想拿到具体的数据，需要根据地址在.MYD（数据文件）文件中取得数据。
4.2InnoDB存储引擎
InnoDB存储引擎只有两个文件，表结构文件(表名.frm)，索引和数据文件（表名.ibd）也叫作聚集索引在该存储引擎的索引的叶子节点上存放的既有索引元素，也有索引元素的行数据。表数据文件本身就是按B+Tree组织的一个索引结构文件
B+树的主键索引的存放图
B+树的非主键索引的存放图
在B+树中的索引是非主键索引的时候，此时在叶子节点上存放对的数据是聚集索引的值，会根据所在的索引查到聚集索引的值，然后根据聚集索引的值再进行回表的查询，得到最终对的数据，这也就是经常把主键当做是索引的原因，这样的话可以保证一致性和节约存储空间
聚集索引和非聚集索引的概念区别：
聚集索引：该索引的叶子节点上包含了完整的数据记录
非聚集索引：在叶子节点上面，记录的是索引和索引所在行的数据的在磁盘文件的地址索引文件和数据文件是分开的。
4.3InnoDB存储引擎和MyISAM存储引擎索的区别
(1) Innodb索引是聚簇索引， MyIASM索引是非聚簇索引
Innodb索引的主键索引的叶子节点存储着行数据，因此主键索引非常的高效
MyIASM索引的主键索引的叶子节点存储着行数据的地址，需要再次寻址，才可以得到数据。
Innodb索引的非主键索引的叶子节点存储的是主键和其他索引的列数据，因此在查询时做到覆盖索引会非常的高效
(2)
MyIASM：不支持事物，不支持行级锁，不支持外键，每张表的存储在三个文件中：frm-表结构文件，MYD-数据文件，MYI-索引文件，
MyIASM可以被压缩，存储空间较小，是按照插入的顺序存储数据，使用的是表级锁，在select count(*)查询的话 MyIASM
会更快，因为内部维护了一个计数器，可以直接调取，B+Tree索引，存储的是堆表，不支持hash索引，支持全表搜索。
Innodb：支持事物，支持行级锁，支持外键，索引和数据在一个文件存储：.db文件，Innodb表需要更多的内存和存储，会在主内存中建立专用的缓冲池用于高速缓存数据和索引。是按照主键大小有序插入存储数据，使用的是行级锁定，表级锁，锁定力度小，并发能力高
B+Tree索引，存储的是索引组织表表，支持hash索引，不支持全表搜索。
为什么建议InnoDB表必须建立主键？并且是使用整型的自增主键？
如果建立主键的话在idb文件中，B+树默认的就是按照主键进行建立索引，进行组装树的结构如果没有建立主键的话，innodb会从所有的列中选中唯一不重复值得列当成索引，因为组装的索引是不能重复的，如果这些都不满足的话，在mysql中会有默认的建立一个6个字节的隐藏列rowId当做索引，进行B+树的组装的。
使用整形自增的主键的原因：
推荐使用自增的ID，不要使用UUID
因为 InnoDB作为存储引擎的话，索引是聚簇索引，也就是说主键索引的B+树叶子节点上按照顺序存储了主键索引以及全部数据。如果主键索引是自增的话，只需要按照顺序往后排列即可，如果使用的是UUID的话，由于原来的ID和新插入的ID的大小没办法判定会造成非常多的数据插入，索引移动，然后导致很多的内存碎片，进而造成插入的性能下降。
聚集索引和非聚集索引在sql查询的时候谁的速度更快一点？
聚集索引的查找速度更快一点，聚集索引的索引和数据在同一个文件内，而且在叶子节点上存放的就是所在行的数据，而非聚集索引需要去跨文件的查找的，在索引文件中只是存了数据文件的地址的，没有确切的去存放数据文件。所以说聚集索引在查询的时候是更快的。
4.4联合索引
联合索引的底层存储结构长什么样？
如果一个表创建的联合索引是（a,b,c）这三个字段，下面是走索引的情况
desc select from test1 where a='1' and b='1' and c='1'; //走索引
desc select from test1 where a='1' and b='1'; //走索引
desc select from test1 where a='1' and c='1'; //走索引
desc select from test1 where b='1' and c='1' and a='1' ; //走索引（
在Mysql的底层有优化器：执行计划生成索引选择） 所以会走索引
explain select from test1 where c='1' and a='1' //走索引
explain select from test1 where b='1' and c='1' //不走索引
发表于 2024-11-1 20:28
|
发表于 2024-11-3 00:11
