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;