MySQL 必知概念

Delete、Drop 和 Truncate

• delete、truncate 仅仅删除表里面的数据，drop会把表的结构也删除
• delete 是 DML 语句，操作完成后，可以回滚，truncate 和 drop 是 DDL 语句，删除之后立即生效，不能回滚
• 执行效率：drop > truncate > delete

MyISAM 与 InnoDB

• InnoDB 支持事务，MyISAM 不支持
• InnoDB 支持外键，MyISAM 不支持
• InnoDB 是聚集索引，数据文件是和索引绑定一起的
• MyISAM 是非聚簇索引，索引和数据文件是分离的，索引保存的是数据的指针
• InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描
• MyISAM 用一个变量保存整个表的行数，执行上述语句时只需要读出改变量即可，速度很快
• InnoDB 支持表、行（默认）级锁，MyISAM 支持表级锁

Join 语句

left join、right join、inner join 的区别：

left join（左连接）：

• 返回包括左表中的所有记录和右表中联结字段相等的记录
• 左表是驱动表，右表是被驱动表

right join（右连接）：

• 返回包括右表中的所有记录和左表中联结字段相等的记录
• 右表是驱动表，左表是被驱动表

innner join（等值连接）：

• 只返回两个表中联结字段相等的行
• 数据量比较小的表作为驱动表，大表作为被驱动表

join 查询在有索引条件下：

• 驱动表有索引不会使用到索引
• 被驱动表建立索引会使用到索引、

所以在以小表驱动大表的情况下，给大表建立索引会大大提高查询效率

Join 原理：

Simple Nested-Loop：

• 驱动表中的每一条记录与被驱动表中的记录进行比较判断（笛卡尔积）
• 对于两表联结来说，驱动表只会被访问一遍，但驱动表却要被访问到好多遍

Index Nested-Loop：

• 基于索引进行连接的算法
• 他要求被动表驱动表上有索引，可以通过索引来加速查询

Block Nested-Loop：

• 它使用 Join Buffer 来减少内部循环读取表的次数
• Join Buffer 用以缓存联接需要的列

选择 Join 算法优先级：

• Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join

当不使用 Index Nested-Loop Join 的时候，默认使用 Block Nested-Loop Join

分页查询优化

select * from table 
where 
type = 2 
and level = 9 
order by id asc 
limit 190289,10;

延迟关联：

• 通过 where 条件提取出主键，再将该表与原数据表关联，通过主键 id 提取数据行，而不是通过原来的二级索引提取数据行

• select a.* from table a,
  (
      select id from table 
      where type = 2 
      and level =9 
      order by id asc 
      limit 190289,10
  ) b 
  where a.id = b.id;
  

书签方式：

• 找到 limit 第一个参数对应的主键值， 在根据这个主键值再去过滤并 limit

• select * from table 
  where 
  id > (
      select * from table 
      where type = 2 
      and level = 9 
      order by id asc 
      limit 190289, 1
  	) 
  limit 10;
  

事务