MySQL的数据是怎么在磁盘上存储的?以数据页为最小单位进行存储,每一个数据页为16kb。
MySQL默认有个主键,这个主键是自增的,默认就会有主键索引。
假如没有设置自增主键怎么办?mysql会默认创建一个自增索引出来。
一、什么是索引?为什么要使用索引?
索引就是MySQL高效获取数据的数据结构。通俗点来讲,索引就好比是书的目录,可以提高数据库的查询速度。
二、索引有什么优缺点?
索引的优点:提高了查询速度,减少了磁盘的IO操作。
索引的缺点:索引也是需要占用这个磁盘空间的,也需要进行维护,多的索引会增加我们的维护成本。
三、索引的分类有哪些?
从逻辑的角度上划分其实之划分为聚集索引和非聚集索引,然后在这个基础上在进行分类。
按照物理存储分类:聚集索引(主键索引)和非聚集索引(辅助索引、二级索引)。
按照数据结构分类:B+书索引和哈希索引。
按照字段特性分类:主键索引、唯一索引、普通索引、全文索引。
按照字段的个数分类:单例索引和联合索引。
四、索引的数据结构?
索引的数据结构有B+树和哈希结构。主要是用这个B+树的,不用哈希结构的最大原因就是哈希结构不支持范围查询。
B+树特征
矮胖,B+树将所有的实际数据都存储在了叶子节点,叶子节点通过双向链表进行连接,非叶子节点只存储键值。
五、索引的实现
索引是在存储引擎层实现的,而不是在服务层实现的,所以不同的存储引擎具有不同的索引类型和实现。
MyISAM
MyISAM创建主键索引和普通索引是一样的
MyISAM中的数据文件(.MYD)和索引文件(.MYI)时分开存储的。MyISAM使用B+树构建索引树,叶子节点存储索引列的值和所在行的磁盘地址。
InnoDB
InnoDB主键索引和普通索引是不一样的
InnoDB中主键索引的叶子节点存储的是实际的数据记录,普通索引的叶子节点存储的是主键索引值,后面要用过这个主键索引值到主键索引中查询完整的记录(回表操作)。
回表
回表就是在InnoDB存储引擎中使用普通索引。比如设置ID为主键索引,age为普通索引。
SELECT name,age FROM stu WHERE age=18;
执行过程
- 叶子节点会按照age的进行排序
- 先通过普通索引(age)查询到主键(ID)
- 在通过主键回到主键索引查询完整的数据
还有一种情况就是一个索引包含了满足查询结果的数据,这就是索引覆盖,不用进行回表操作,这只是一种现象。
六、什么时候索引会失效?
索引失效我们是要看怎么定义失效。我们认为没有按照最佳的顺序走索引就是失效,还是不走索引才算失效。
- MySQL经过判断之后认为走索引还不如不走索引效率高
- 不符合最左匹配原则
- 对索引列进行了计算
- IS NOT NULL
##什么是最左匹配原则
最左匹配原则发生在联合索引中,就是最左边的字段优先。
比如我们现在将name、age设置为联合索引,首先会按照name进行排序,其次在局部对age进行排序。
SELECT name,age FROM stu WHERE age=18;
上面这个SQL语句是不会走索引的,因为不符合最左匹配原则。但是在MySQL5.6之后引入了一个索引下推的概念。
什么是索引下推?
索引下推减少了不必要的行访问和回表操作。
SELECT name,age FROM stu WHERE age=18;
在MySQL5.6之前,会直接进行全表扫描。
在MySQL5.6之后,会先对age进行过滤,在进行扫描。