【MySQL】存储引擎和连接查询
查询引擎命令:show engines
MyISAM和InnoDB对比
对比项 | MyISAM | InnoDB |
主外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
行表锁 | 表锁,即使操作一条记录也会锁住整个表,不适合高并发操作 | 行锁,操作时只锁住一行,不对其他行有影响,适合高并发 |
缓存 | 只缓存索引,不缓存真实数据 |
不仅缓存索引,还缓存真实数据,对内存要求高 而且内存大小对性能有决定性影响。 |
表空间 | 小 | 大 |
关注点 | 性能 | 事务 |
默认安装 | 是 | 是 |
阿里淘宝用的大部分数据库是以Percona为原型修改的。(AliSql+AliRedis)
Percona为MySQL数据库服务器进行了改进,在功能和性能上较MySQL有很大提升,提高了在高负载下的InnoDB的性能,为DBA提供了一些非常有用的性能诊断工具,有更多的参数和命令来控制服务器行为。
SQL性能下降、执行时间长、等待时间长可能的原因:
- 查询语句写的不好
- 索引失效(单值、复合)
- 关联查询太多join(设计缺陷或者需求需要)
- 服务器调优及各个参数设置(缓冲、线程数等)
SQL执行顺序
手写顺序:
SELECT DISTINCT 查询的表 FROM 左表 连接类型 JOIN 右表 ON 连接条件 WHERE 查询条件 GROUP BY 组合的条件 HAVING 拥有的条件 ORDER BY 排序的条件 LIMIT 限制的数量
机读顺序:
- FROM 笛卡尔积
- ON 主表保留
- JOIN
- WHERE 非聚合
- GROUP BY 改变对表的引用
- HAVING 只作用分组后
- SELECT
- DISTINCT
- ORDER BY
- LIMIT
SQL JOINs
内连接:A inner join B on A.key = B.key(AB的交集)
左连接:A left join B on A.key = B.key(A的全集,B只有交集部分)
右连接:A right join B on A.key = B.key(B的全集,A只有交集部分)
左连接不要B:A left join B on A.key = B.key where B.key is null(A-B)
右连接不要A:A right join B on A.key = B.key where A.key is null(B-A)
全连接:A full outer join B on A.key = B.key (AB的全集)
(A-B)∪(B-A):A full outer join B on A.key = B.key where A.key is null or B.key is null
画个饼图一目了然
mysql不支持 full outer join,可以用UNION写成这样(合并并去重)
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id UNION SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id WHERE t1.id IS NULL
当使用 UNION 时,MySQL 会把结果集中重复的记录删掉,而使用 UNION ALL ,MySQL 会把所有的记录返回,且效率高于 UNION。