MySQL执行原理

MySQL中SQL执行原理

MySQL可以分成MySQL Server和存储引擎
Server：涵盖了MySQL大多数的核心服务功能和内置函数，所有的跨存储引擎功能，存储过程、触发器、视图
server组件
连接器：连接管理、权限验证

查询缓存：命中直接返回结果
接到查询请求先到查询缓存中看之前有没有执行过这个查询语句，之前执行过的会以key-value（查询语句-查询结果）的形式存储在缓存中，如果之前执行过直接将对应的value返回给客户端，如果没有则真正开始执行查询语句

分析器：语法分析
语法分析器根据语法规则判断输入的查询语句是否符合MySQL语法，如果语法不对则会返回语法错误提醒

优化器：生成执行计划、选择索引
在表中有多个索引的时候决定使用哪个，或者在语句有多表关联的时候确定表的关联顺序

执行器：操作引擎，返回结果
执行器先判断用户对需要使用的表是否有执行权限，如果没有权限则会返回没有权限的提示，如果有权限则打开表，根据表的引擎去使用引擎提供的接口遍历整个表，把所有满足条件的行组成的记录的集作为结果集返回给客户端。到此整个完整的执行过程完成。

存储引擎
负责数据的存储提取，支持innodb（默认）、memory等存储引擎。不同的存储引擎的表数据存取方式不同，支持的功能也有一些差别。

索引

索引定义：
1、索引是存储的表中一个特定列的值数据结构；
2、索引包含一个表中列的值，并且这些值存储在一个数据结构中。
索引是一种数据结构，类似于一本书的目录。数据库的索引包含指针，指向SQL中想要查询的值所在的行，查询效率会高很多。数据库除了自身以外，还会维护一个满足特定查找算法的数据结构，这些数据结构会指向数据，这样就可以在基础的数据结构上去实现更高级的查找算法。索引会以文件的形式存在磁盘上面。
索引分类
单列索引：索引只包含一个列，一个表中可以都多个单列索引
普通索引
唯一索引：允许NULL值，且所有的值都是唯一的
主键索引：不允许NULL值，int、varchar类型的，一般在创建的时候创建，最好是和其他列不相关，或者说是与业务不相关的列
组合索引：一个表中如果包含有多个单列索引，那么他就是一种组合索引。综合来说，组合索引是由于单列索引的，但一个表中不要创建太多索引，最好不要超过5个，否则效率会降低。
全文索引：检索包含一个或多个给定单词的数据结构
索引的优点：
提高数据键锁的效率，降低数据库的IO成本；
通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗。
索引的缺点：
占用空间（索引本身也是一个表）；
降低更新表的速度（插入、删除、更新会降低速度，因为更新时MySQL不仅要保存更新的数据，还要保存索引文件。索引文件更新时会添加新的索引列的字段。）；
需要花时间研究建立最优秀的索引，或者优化。
索引适用场景：
1、主键自定建立唯一索引；
2、频繁作为查询条件的字段应该创建索引；
3、查询中与其他表关联的字段，外键关系建立索引
4、查询中排序的字段。

explain

通过explain模拟优化器执行SQL查询语句，查看SQL执行情况，帮我们找到查询效率低是什么原因，从而找到查询语句或是表的性能瓶颈。
使用方法：
explain + SQL语句
结果：

id:表示查询序列号，表示查询当中执行select的语句或者操作表的顺序，值越大优先级越高，越先被执行，id相同时由上至下。
table:输出结果的结果集
type：访问类型，即查询使用了哪种类型
possible_keys：查询中MySQL可以使用的索引，NULL表示没有可使用的索引
key：实际使用的索引
key_len：使用索引的长度
ref：索引中那一列被使用了
rows：返回请求数据的行数
Extra：其他额外的参数
explain的作用：
1、表的读取数据table；
2、数据读取操作的操作类型select_type；
3、哪些索引可以使用possible_keys；
4、哪些索引被实际使用key;
5、表之间的引用；
6、每张表有多少行被优化器查询

事务

数据库事务transaction：是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。只有使用默认的Innodb存储引擎才支持使用事务。

事务的特点ACID
原子性Atomicity:要么全执行，要么全不执行，如果执行过程中出错则回滚到执行之前
一致性Consistency：事务开始之前与事务结束之后，数据库的完整性没有被破坏。即所有写入数据完全符合预设的规则，包含数据的精确性，数据的串联型等内容。
隔离性Isolation：为了防止多个事务并发执行时由于交叉执行而导致数据不一致的情况。事务的隔离可分为多个不同的级别，例如包含读未提交，读提交、可重复度、串行化等。
持久性Durability：事务处理结束后对事务的处理是永久的，即使系统出现故障也不会丢失。

事务的操作：
begin 开始一个事务
rollback 事务回滚
commit 事务提交：提交后所有的修改就变成永久性的了

日志

MySQL提供了很多日志，例如
1、slow log：慢查询日志，超出预设的long_query_time阈值的SQL记录。打开慢查询日志对整体数据库的性能是有一定消耗的，如果不是调优需要，一般是不建议启动这个参数的，只有在需要调优的是后现打开就可以了。
查看慢日志
查看日志开关：show variables like ‘%query%’;
打开日志开关：set global slow_query_log=‘ON’;
设置阈值：set long_query_time=0-01;
执行SQL语句，查看日志内容
在表中查看日志
直接在日志中查看日志信息有时候不是很方便，也可以把日志信息保存在数据表中方便查看
修改日志存放方式：set global log_output=‘table’;
查看表中内容：select * from mysql.slow_log;

2、general log：全局查询日志，所有SQL查询的记录
查看全局查询日志
查看变量信息：show variables like ‘%general%’;
打开日志开关：set global general_log=‘ON’;
执行SQL语句
查看表中日志内容：select * from mysql.general_log;