Skip to content

Latest commit

 

History

History
60 lines (60 loc) · 5.96 KB

mysql.md

File metadata and controls

60 lines (60 loc) · 5.96 KB

索引结构: 二叉树(没法自平衡) -> 红黑树(树高度过高) -> b-tree(非叶子节点也存储数据, 一个页里存放数据太少了, 树高度高) -> b+tree, Mysql 使用了 B+tree 的数据结构.

  1. 特点:
    1. 非叶子节点只存储键值信息
    2. 叶子节点(最下面)存放数据
    3. 所有叶子节点之间都有一个链指针, 可以范围查找
  2. 聚集索引与非聚集索引
    1. 聚集索引叶子节点存放的是数据; 默认我们新建一个表, mysql 推荐使用自增 id 作为主键, 如果表设置了主键, 则主键就是聚簇索引, 如果没有 id 字段 mysql 会自动创建自增 id 作为聚集索引;
    2. 非聚集索引叶子节存放的是索引地址; InnoDB存储引擎下, 非聚集索引就是先找到索引地址然后再去查找数据.
    3. MyISAM 只能非聚集索引, 因为 myisam 一个特点, 索引和数据是分开的, 所以没法使用聚集索引.
  3. 为什么 mysql 推荐使用自增 id 作为索引?
    1. 通过 b+tree 的结构, 树高度越低, 索引越快, 那也就是每个磁盘块也就是页尽可能的多装数据, 所以索引字段要尽量小, 这也是为什么数据都放在叶子节点int占4字节.
  4. 索引有最左匹配原则, 比如一个表, 有abc三个字段, 都是索引, 现在有7条数据:
    1. select * from table where a = 1 and b = 1 and c = 1 ; ##索引生效
    2. select * from table where a = 1 and b = 1 ; ##索引生效
    3. select * from table where b=1 and c = 1 ; ##索引不生效, 因为 *11, 索引没法放下走;
  5. 亿级数据, 必须like, 如何优化?
    1. 亿级数据, www.baidu.com; www.google.com; www.qq.com... ..., 现在查找出后缀名是com的?
      1. select * from table where url like '%com%' ; 这样会导致索引不生效,
      2. 如果我把表中数据倒过来, com.baidu.www ; com.google.www... ...
      3. select * from table where url like 'com%' ; 这样可以使索引生效;
  6. InnoDB 与 MyISAM 的区别?
    1. MyISAM: 索引与数据分开存储; 所以在通过索引搜索时候, 找到数据地址之后再去查找一次数据; 不支持事务;
    2. InnoDB: 索引的叶子节点存储的就是数据; 支持事务;
  7. mysql的隔离级别和对应的问题? 现在已经有两个事务, 事务A更新, 事务B查询
    1. 读未提交: A事务先更新, B查询, A提交, 脏读就是B在查询时候读取到A未提交的内容; 可能发生脏读、不可重复读和幻读问题, 没人用.
    2. 读已提交: 满足我们对事务的要求; A更新, B查询, 结果是没更新的数据, A提交, B查询, 结果是更新完的数据, 这样的问题就是B一次事务, 查询到的是两个结果; 解决脏读, 但可能发生不可重复读和幻读问题, 一般很少使用此隔离级别.
    3. 可重读: 这是MySQL的默认事务隔离级别, 解决了幻读的问题, A不管怎么更新, 怎么提交, 只要B的事务不结束, 读取到的就是A没提交的数据; 实现方式是 MVCC;
    4. 可串行化: 这是最高的隔离级别, 如果A开启了事务, 事务B的所有操作, 都要等待事务A完成; 实现方式是读写锁. 以上三种都是对同一记录的读-读、读-写、写-读的并发操作, 如果我们不允许读-写、写-读的并发操作, 可以使用SERIALIZABLE隔离级别.
  8. MVCC
    1. MVCC 是怎么回事呢, Innodb 下, 我们在数据库中保存的数据不仅包括数据本身还包括: trx_id(事务 id), roll_pointer(一个指针, 指向的就是undo日志中上一个版本的记录, 或者这么说我们每次对一条记录修改时, 旧版本的数据会存入到undo日志, 这个指针指向的就是这个)
      1. 对于读未提交, 直接读取记录的最新版本就好了;
      2. 串行化又是靠锁实现的.
    2. 对于读已提交和可重复读, 需要知道读取哪个版本号,
      1. 所以引出 ReadView;
      2. ReadView 中主要包含当前系统中还有哪些活跃的读写事务, 把它们的事务id放到一个列表中, 这个表里有一个逻辑: 判断表中最大的事务id和最小的事务id, 和被访问的事务id有个判断关系,
  9. mysql 锁分类?
    1. 表锁/行锁/间隙锁: 表锁粒度大所以并发度小、锁冲突概率高、开销小,
      1. 表锁粒度大所以并发度小、锁冲突概率高、开销小, lock(unlock) table read(write);##加锁(释放锁) 表名 读(写)
      2. 行锁粒度小、并发高、开销大, 因为你要找到指定数据加锁, 所以开销大
      3. 间隙锁: 比如我要操作 id 10~100 的数据, 那我间隙锁就是加到 id 小于 10 和 id 大于 100 的范围
    2. 读锁/写锁:
      1. 读锁, 这行数据可以被别人读, 不能被别人写 select ... lock in share mode
      2. 写锁, 只能自己写, 也叫排它锁 select ... for update
    3. 乐观锁/悲观锁:
      1. 乐观锁: 版本号, 写数据库之前判断版本号
      2. 悲观锁: 串行, 不允许读写、写读一起, 隔离级别串行化就是依靠悲观锁
  10. 索引不适用的条件
    1. 索引列上有函数, 不满足最左前缀, 使用了不等号, 使用了范围查询等等.
  11. 回表查询?
    1. 非聚集索引下, 先找到聚集索引, 在通过聚集索引找到数据, 需要扫描两次B+tree.
  12. 如何解决回表查询?
    1. 索引覆盖: 在一棵索引树就能得到我要的字段, 就不需要会表, 因为你需要的数据就是这棵树的数据, 比如: id, age
      1. select age from table where age = 10;不会回表
      2. select * from table where age = 10;会回表
      3. 但是如果你非要select id,age,name from table where age = 10;可以使用组合索引, idx_age_name(age,name), 你要查询的age, name都在这棵树了, 就不需要回表了.
  13. 什么时候使用索引覆盖?
    1. select count(age) from user; 如果age没有索引, explain之后就会发现回表了;
    2. 创建age字段索引之后, 再去执行这个 sql 就发现不再回表了.
  14. explain?
    1. type: 看索引是否生效, extra: 为 null 表示回表了