Mysql小记

索引

• 聚簇索引和非聚簇索引
  • 聚簇索引其实就是主键索引，非聚簇索引就是非主键索引
  • 聚簇索引的叶子结点上保存了整行的数据，
  • 非聚簇索引只保存了主键数据，所以通过非聚簇索引查询数据的时候需要根据索引找到主键的值，然后再通过主键的值在聚簇索引上查询到整行的值（回表）
• innodb引擎非聚簇索引一定会回表么
  • 不一定，如果索引包含了所有的查询字段的时候就不需要回表（覆盖索引）
• 根据主键id进行查询的时候select *和 select id 哪个快
  • 从查找数据行来讲其实效率是一样的，但是由于select * 查询的内容要比select id 要多（数据的拷贝和读取），所以select *要慢点
  • 但是这个问题考察的是主键索引（聚簇索引）的知识，所以可以回答一样快
• innodb
  • 其实一个innodb的表就是多个B+树组成的
• 索引下推
  • mysql 5.6之后的功能
  • 在满足了最左前缀原则后，会根据联合索引的其他条件来判断是否满足查询，减少回表操作
    mysql> select * from tuser where name like '张%' and age=10 and ismale=1;
    • tuser有联合索引(name, age)
      • 如果没有索引下推，会根据最左前缀原则，也就是说所有name like '张%'的记录都回表查询
      • 有索引下推，先根据最左前缀原则，查到name是张开头的会先判断联合索引的age是否满足大于10的条件,如果不满足会直接过滤掉，就减少了回表的操作

锁

• 用--single-transaction方法做逻辑备份的时候，如果主库上的一个表做了DDL操作（比如给某个表加了一列），这个时候从备份库会有什么现象

    Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
    Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
    /* other tables */
    Q3:SAVEPOINT sp;
    /* 时刻 1 */
    Q4:show create table `t1`;
    /* 时刻 2 */
    Q5:SELECT * FROM `t1`;
    /* 时刻 3 */
    Q6:ROLLBACK TO SAVEPOINT sp;
    /* 时刻 4 */
    /* other tables */
  

  • 流程
    Q1. 设置会话级别的事务隔离为可重复读
    Q2. 开启事务用 WITH CONSISTENT SNAPSHOT 确保可以得到一个一致性视图
    Q3. 设置保存点
    Q4. 拿到表结构
    Q5. 导数据
    Q6. 回滚到sp保存点
  • 解答
    1. 如果在Q4执行之前到达，现象： 没有任何影响，备份拿到的是修改后的表结构
    2. 如果是在"时刻2"到达，则表结构被改过，Q5执行的时候会报Table definition has changed, please retry transaction,现象： mysqldump终止
    3. 如果在"时刻2"和"时刻3之间"到达，mysqldump 占着t1的 meta data lock(MDL) 读锁，binlog被阻塞， 现象：主从延迟，直到Q6执行结束
    4. 从"时刻4"开始，mysqldump释放了MDL读锁，现象：没有影响，备份拿到的是DDL前的表结构

• 如果要删除一个表里面的前10000行数据，有以下三种方法可以做到：

  1. 直接执行 delete from T limit 10000;
  2. 在一个链接里循环执行20次 delete from T limit 500;
  3. 在20个链接中同时执行 delete from T limit 500;
     你会采用哪种方法呢？为什么？
  • 第二种方法比较好
    • 第一种方法单个语句占用的时间长，锁的时间也比较长；而且大事务还会导致主从的延迟
    • 第三种方法会认为的造成锁冲突