mysql分层
一般来说，MySQL逻辑可分为四个层次，分别为：连接层，服务层，引擎层，存储层
-
连接层 提供与客户端连接的服务。
当客户端发出一个请求后（如增删改查的SQL语句），首先到达该层，将服务器与客户端建立连接。 -
服务层 服务层分两个作用：
提供各种用户使用的接口。 如select、insert等
提供SQL优化器（MySQL Query Optimizer）。 -
引擎层 引擎层提供各种数据存储的方式。MySQL的存储引擎有很多，比较常用的比如有
InnoDB,MyISAM。 -
存储层
mysql引擎对比
| 项 | innodb | Myislam |
|---|---|---|
| 主外键 | 是 | 否 |
| 事务 | 是 | 否 |
| 锁 | 支持行级锁 | 支持表级锁 |
| 存储 | 存储索引，不存数据 | 存索引，存数据，有内存要求 |
| 索引 | 聚簇索引、能缓存索引，也能缓存数据 | 非聚簇索引、只能缓存索引 |
mysql执行顺序
- from 计算笛卡尔积
- on 过滤
- join
- where
- group by 分组
- having 过滤
- select 指定列
- distinct 去重
- order by
- limit
建立索引时机
explain 字段介绍（sql查询计划）
1. id
select查询的序列号，包含一组数字，表示查询中执行select字句或操作表的顺序。
其中id的取值分为三种情况：
- id相同，执行顺序由上往下
- id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id相同不同，同时存在
2. select_type
- SIMPLE
简单的select查询，查询中不包含子查询或者UNION。 - PRIMARY
查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY。 - SUBQUERY
在select或where列表中包含了子查询。 - DERIVED
在from列表中包含的子查询被标记为DERIVER(衍生)，Mysql会递归执行这些子查询，把结果放在临时表中。 - UNION
若第二个select出现在UNION之后，则被标记为UNION；
若union包含在from字句的查询中，外层select将被标记为：DERIVER - UNION RESULT
从UNION表获取结果的select。
3. table
- 表名
4. type
- ALL
- INDEX
- RANGE
- ref
- eq_ref
- const
- system
索引失效
小表驱动大表
- select * from A where id in (select id from B) 当 A表的数据多余B表，用in
- select * from A where exists (select 1 from B where B.id = A.id) 当 A表的数据 少于B表，用exists
为排序使用索引
mysql 两种排序方式
- 文件排序（Using filesort）
- 索引排序（using index）
key a_b_c (a,b,c) \
-
order by 使用索引最左前缀原则
-order by a
-order by a,b
-order by a,b,c
-order by a desc,b desc,c desc (同升 or 同降) -
如果where 使用的最左前缀索引是常量，则order by 能使用索引
- where a = const order b,c
- where a= const and b = const order c
- where a= const and b > order b,c
- 不能使用索引进行排序
- order by a asc,b desc ,c desc [都升，或者都降 才有效]
- where d = const order by b,c [缺少a ，d不是索引一部分]
- where a = const order c [中间断开，缺少b]
- where a= const order a,d [d不是索引]
- where a in() order b,c [ a是范围，范围之后全失效]
group by 优化
- group by 本质是先排序 再分组，遵循 索引的最左前缀原则
- 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size
- where 优先级大于 having ，能where 不要having
