MySQL架构与Explain优化SQL语句

MySQL

逻辑架构

连接层
Connectors

最上层是一些客户端和连接服务,包含本次stock通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信.主要完成一些类似于连接处理,授权认证,及相关的安全方案.在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程,同样在该层上可以实现基于SSL的安全连接.服务器也会为安全接入的每个客户端验证他所具有的操作权限

业务层
Management Services&Utilities
数据库自带的工具类(JDK)
Connection Pool
数据库连接池
SQL Interface
用来区分语句.DDL,DML,Stored等
Parser
将输入的代码格式化(关键词替换为大写)
Optimizer
MySQL自带的优化器,(MySQL自认为最优)
Caches&Buffers
缓存查询过的SQL,执行相同SQL第二次会较快

第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化及部分内置函数的执行.所有跨存储引擎的功能也在这一层实现,如过程,函数等.在该层,服务器会解析查询并创建响应的内部解析树,并对其完成响应的优化如确定查询表的顺序,是否利用索引等,虽有生成响应的执行操作.如果是Select语句,服务器还会查询内部的缓存.如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统性能.

存储引擎
Pluggabie Storage Engines
支持数十种引擎,但是在90%情况下一般使用MyISAM和InnoDB

存储引擎真正负责了MySQL中数据存储和提取,服务器通过API与存储引擎进行通信.不同的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取.

存储层
File System
Files & Logs

主要是将数据存储在运行于裸设备的文件系统之上,并完成与存储引擎的交互

和其他数据库相比,MySQL有点与众不同,它的架构可以在多种不同场景中应用并发挥良好作用.主要体现在存储引擎的架构上,插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离.这种架构可以根据业务的需求和实际需要选择合适的存储引擎

存储引擎

MyISAM:偏向读取

• 不支持主外键
• 不支持事务
• 表锁,即使操作一条记录会锁住整个表,不适合高并发
• 只缓存索引,不缓存真是数据
• 表空间较小
• 关注性能

InnoDB:偏向写

• 支持主外键
• 支持事务
• 行锁,操作时只锁住一行,不对其他行有影响(适合高并发)
• 不仅缓存索引的真实数据,对内存要求较高,而且内存大小对性能有决定性影响
• 表空间较大
• 关注事务

Join查询

SQL加载顺序

手写SQL的顺序

	SELECT DISTINCT
		<select_list>
	FROM
		<left_table> <join_type> 
		JOIN <right_rable> ON <join_condition>
	WHERE
		<where_condition>
	GROUP BY
		<group_by_list>
	HAVING
		<having_condition>
	ORDER BY
		<order_by_condition>
	LIMIT
		<limit_number>

机读顺序

	FROM 
		<left_table>
		ON <join_condition>
		<join_type> JOIN <right_table>
	WHERE
		<where_condition>
	GROUP BY
		<group_by_list>
	HAVING
		<having_condition>
	SELECT
	DISTINCT
		<select_list>
	ORDER BY
		<order_by_condition>
	LIMIT
		<limit_number>

7种JOIN

##索引
MySQL官方对索引的定义为:索引(index)是帮助MySQL高校获取数据的数据结构(其实就是一种数据结构)
一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上,我们平常说的索引,如没有特别说明都是BTREE结构的

在数据之外,数据库系统还维护这满足特定查找算法的数据结构,这些数据结构以某种方式指向数据
这样就可以在这些数据结构上数显高级查找算法.这种数据结构就是索引

索引的优势:

• 提高数据检索效率,降低IO成本.
• 降低数据排序成本,降低CPU消耗.

索引的劣势:

• 实际上索引也是一张表,保存着主键与索引字段,并指向实体记录.
• 虽然索引会大大提高查询速度,同时却会降低更新表速度,如insert,update和delete.因为更新表时,Mysql不仅要保存数据,还要保存一下索引文件每次更新了索引的字段,都会调整因更新所带来的键值变化后的索引信息
• 索引是提高效率的一个因素,如果有大量的表,就需要花时间研究建立优秀的索引

数据库结构
把Mysql比喻成一个图书馆
frm: 图书馆这个建筑
myd: 图书馆的书
myi: 图书馆的管理员

索引的分类

1. 主键索引
   建表时的主键会自动添加主键索引
   不允许重复,不允许空值

2. 唯一索引
   用来建立索引的列的值必须是唯一的,允许空值
   CREATE INDEX UNIQUE index ON table(col)

3. 普通索引
   用表中的普通列构建的索引，没有任何限制
   CREATE INDEX index ON table(col)

4. 组合索引
   用多个列组合构建的索引，这多个列中的值不允许有空值
   CREATE INDEX index ON table(col1,col2,col3)

EXPLAIN(执行计划)

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的.分析你的查询语句或是表结构的性能瓶颈

结构如下

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

各字段解释:
id
SELECT查询的***,表示查询中执行SELECT子句或操作表的顺序

1. id相同下,从上到下读取
2. id不同下,越大的越先执行(子查询)
3. 同时存在的情况下先执行id较大的

select_type
查询的类型,主要用于区别

1. SIMPLE : 简单的SELECT查询,查询中不包含子查询或者UNION
2. PRIMARY : 查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY
3. SUBQUERY : 在SELECT或WHERE列表中包含了子查询
4. DERIVED : 在FROM列表中包含的子查询被标记为DERIVED(衍生),MYSQL会递归执行这些子查询,把结果放在临时表里
5. UNION : 若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为DERIVED
6. UNION RESULT : 从UNION表获取结果的SELECT

table
表示这一行数据是关于那张表的

type
访问类型排列
从最好到最差依次是:system>const>eq_ref>ref>range>index>ALL
一般来说,至少要达到range级别,最好能打到ref

1. system : 表只有一行记录(等于系统表),这是const类型的特列,平时不会出现
2. const : 表示通过索引一次就找到了,const用于比较primary或者unique索引.因为只匹配一行数据,所以很快.如将逐渐置于where列表中,Mysql就能将该查询转换为一个常量
3. eq_ref : 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配.常见于主键或唯一索引扫描
4. ref : 非唯一行索引扫描,返回匹配某个单独值的所有行.本质上也是一种索引访问,他返回所有匹配某个单独值的行.然而,它可能会找到多个符合条件的行,所以他应该数据查找和扫描的混合体
5. range : 只检索给定范围的行,使用一个索引来选择行.一般就是在你的where语句中出现了between,<,>,in等的查询.这种范围扫描索引比全表扫描要好,因为他只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引
6. index : 全索引扫描.只遍历索引,通常比ALL快因为索引文件通常比数据文件较小(ALL是从硬盘中读取)
7. ALL : 全表扫描找到匹配的行.

possible_keys
理论上可能用到的索引

key
实际使用的索引,如果为null则没有使用

key_len
表示索引中使用的字节数,可以通过列计算查询中使用的索引长度.在不损失精度情况下,越短越好;
key_len显示的值为索引字段的最大可能长度,并非实际使用长度.即key_len是根据表定义计算而得
常用类型len:

1. varchar:等于字段长度*字符集+2
2. char:等于字段长度*字符集
3. int :4位
4. bigint:8位
5. float:4位
6. double:8位
7. datetime:5.5版本8位,5.6版本5位

GBK每一位占2字节,UTF8每一位占3字节
如果可以是NULL长度+1

ref
显示索引的那一列被使用了,如果可能的话,是一个常数.哪些列或常量被用于查找索引列上的值

rows
大致估算出所需要读取的行数

Extra
包含不适合在其他列中显示,但十分重要的信息

1. Using filesort:你的ORDER BY没有复用索引的建立顺序,没有保持一致
2. Using temporary:使用了临时表保存中间结果,Mysql在对查询结果排序时使用临时表.常见于排序ORDER BY和分组查询GROUP BY
3. Using index:表明响应的select操作中是用来覆盖索引,避免了访问表的数据行,效率不错;如果同时出现Using where表明索引被用来执行索引键值的查找;如果没有出现Using where表明索引用来读取数据而非执行查找动作
4. Using where:在查找使用索引的情况下，需要回表去查询所需的数据。

覆盖索引
理解方式一:就是SELECT的数据列只用从索引中就能取得,不比读取数据行,MySQL可以利用索引返回SELECT列表中的字段,而不比根据索引再次读取数据文件,欢聚话说查询列要被所建索引覆盖
理解方式二:索引是高校找到行的一个方法,但是一般数据库也能使用索引找到一个猎德数据,因此它不比读取整个行,毕竟索引叶子节点存储了他们索引的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了.一个索引包含了蛮子查询结果的数据就叫做覆盖索引
注意:
如果使用覆盖索引,一定要注意SELECT列表中只取出需要的列,不能出现SELECT *,因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降

索引失效

全值匹配
查询条件与索引相对应

最佳左前缀
如果是组合索引,想要使用索引最左边的字段必须要有,想要使用全部索引,中间字段不能断

不在索引列上做任何操作计算
计算,函数,类型转换

不能使用索引中范围条件右边的列
出现 <,>,not in,not exist,!= 后面的索引列将会失效

尽量使用覆盖索引
访问索引的查询列尽量与索引列一致,减少SELECT *

注意建表时null/not null可能会对索引造成影响

like中以%开头,会使索引失效
解决方案为家里复合索引,SELECT 索引字段 来覆盖索引

字符串单引号不能少
会触发自动类型转换

少用OR,用它来连接会索引失效
数据量小时不用考虑,数据量较大时使用union all来实现