目录
1. MySQL索引的底层数据结构是?
-
为什么要设计索引?
答:加快数据的查询效率 -
设计索引的时候,使用什么数据结构,为什么?
答:B+树
问:为什么?
哈希表也能存储,但是
补充:扰动函数,让高位尽可能参与哈希值的运算,让数据更加散列
二叉树和红黑树也能存储,但是
缺点:深度太深,会频繁地在磁盘和内存间io,影响读取效率
B树也可以存储,但是
一个磁盘块的空间有限,data的存在会浪费空间,所以我们只用叶子节点来存数据,这样就构成了B+树。
B+树:
非叶子节点只负责搜索,这样存储的数据会更多,叶子节点负责存储,索引字段尽可能短,这样能存储更多的数据到B+树上。
- 聚簇索引和非聚簇索引有什么区别?
答:聚簇索引:Innodb使用的索引,数据和索引放在一起的
非聚簇索引:MyIsam使用的索引,数据和索引没有放在一起
2. ACID
2.1. 什么是ACID
2.2. MySQL的四个隔离级别
2.3. 脏读,不可重复读,幻读
2.4. 隔离级别对应的解决
3. 索引的种类
聚簇索引和非聚簇索引
聚簇索引:将数据存储与索引放到了一块,找到索引也就找到了数据
非聚簇索引:将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,myisam通过key_buffer把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,然后通过索引找到磁盘相应数据,这也就是为什么索引不在key buffer命中时,速度慢的原因
4. Mysql 中 MyISAM 和 InnoDB 的区别有哪些
- InnoDB 支持事务,MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一;
- InnoDB 支持外键,而 MyISAM 不支持。
- InnoDB 是聚集索引,MyISAM 是非聚集索引。聚簇索引的数据文件存放在主键索引的叶子节点上,因此 InnoDB 必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。而 MyISAM 是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
聚簇索引的叶节点就是数据节点,而非聚簇索引的叶节点仍然是索引节点,并保留一个链接指向对应数据块。 - InnoDB 不保存表的具体行数,执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快;
- InnoDB 最小的锁粒度是行锁,MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表,导致其他查询和更新都会被阻塞,因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一;
5. 索引失效的场景
-
对于创建的多列索引(复合索引),不是使用的第一部分就不会使用索引
alter table student add index my_index(name, age) // name左边的列, age 右边的列 select * from student where name = 'aaa' // 会用到索引 select * from student where age = 18 // 不会使用索引
-
对于使用 like 查询, 查询如果是 ‘%aaa’ 不会使用索引,而 ‘aaa%’ 会使用到索引。
select * from student where name like ‘aaa%’ // 会用到索引
select * from student where name like '%aaa' 或者 '_aaa' // 不会使用索引
-
如果条件中有 or, 有条件没有使用索引,即使其中有条件带索引也不会使用,换言之, 就是要求使用的所有字段,都必须单独使用时能使用索引。
-
如果列类型是字符串,那么一定要在条件中使用引号引用起来,否则不使用索引。
-
如果mysql认为全表扫面要比使用索引快,则不使用索引。
如:表里只有一条数据。
6. 数据库三范式
第一范式:第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据项
第二范式:第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。第二范式要求每一行都要有唯一标识存在,这个唯一属性列被称为主关键字或主键、主码。实体的属性完全依赖于主关键字。
第三范式:第三范式是在第二范式的基础上建立起来的。第三范式指:属性不依赖于其他非主属性。
举例说明:
现有一张表:
7. MySQL优化
- 选取最适用的字段属性,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小。
- 使用连接(JOIN)来代替子查询(Sub-Queries)
- 使用联合(UNION)来代替手动创建的临时表
- 事务,关闭事务自动提交,改为手动提交
- 使用外键
- 使用索引
- 优化的查询语句,绝大多数情况下,使用索引可以提高查询的速度,但如果SQL语句使用不恰当的话,索引将无法发挥它应有的作用(引出索引失效的情况)
8. count(1)和count(*)的区别
count(*) 和 count(1)和count(列名)区别
执行效果上:
- count(*)包括了所有的列,相当于行数,在统计结果的时候,不会忽略列值为NULL
- count(1)包括了忽略所有列,用1代表代码行,在统计结果的时候,不会忽略列值为NULL
- count(列名)只包括列名那一列,在统计结果的时候,会忽略列值为空(这里的空不是只空字符串或者0,而是表示null)的计数,即某个字段值为NULL时,不统计。
执行效率上:
- 列名为主键,count(列名)会比count(1)快
- 列名不为主键,count(1)会比count(列名)快
- 如果表多个列并且没有主键,则 count(1) 的执行效率优于 count(*)
- 如果有主键,则 select count(主键)的执行效率是最优的
- 如果表只有一个字段,则 select count(*)最优。
9. B+树与B树区别
区别有两点:
- B+树中只有叶子节点会带有指向记录的指针(ROWID),而B树则所有节点都带有,在内部节点出现的索引项不会再出现在叶子节点中。
- B+树中所有叶子节点都是通过指针连接在一起,而B树不会。
B+树的优点:
-
非叶子节点不会带上ROWID,这样,一个块中可以容纳更多的索引项,一是可以降低树的高度。二是一个内部节点可以定位更多的叶子节点。
-
叶子节点之间通过指针来连接,范围扫描将十分简单,而对于B树来说,则需要在叶子节点和内部节点不停的往返移动。
B树的优点:
对于在内部节点的数据,可直接得到,不必根据叶子节点来定位。
10. MySQL三种log日志及作用
11. 理解MVCC
12. mysql回表查询
12.1. 什么是回表查询
通俗的讲就是,如果索引的列在 select 所需获得的列中(因为在 mysql 中索引是根据索引列的值进行排序的,所以索引节点中存在该列中的部分值)或者根据一次索引查询就能获得记录就不需要回表,如果 select 所需获得列中有大量的非索引列,索引就需要到表中找到相应的列的信息,这就叫回表。