MySQL之五----存储引擎、服务器配置、查询缓存、索引及事务及隔离级别

一、MySQL架构

第一层，即最上一层，所包含的服务并不是MySQL所独有的技术。它们都是服务于C/S程序或者是这些程序所需要的 ：连接处理，身份验证，安全性等等。

第二层值得关注。这是MySQL的核心部分。通常叫做 SQL Layer。在 MySQL据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断， sql解析，行计划优化， query cache 的处理以及所有内置的函数(如日期,时间,数学运算,加密)等等。各个存储引擎提供的功能都集中在这一层，如存储过程，触发器，视 图等。

第三层包括了存储引擎。通常叫做StorEngine Layer ，也就是底层数据存取操作实现部分，由多种存储引擎共同组成。它们负责存储和获取所有存储在MySQL中的数据。就像Linux众多的文件系统 一样。每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交互的。这个接口隐藏 了各个存储引擎不同的地方。对于查询层尽可能的透明。这个API包含了很多底层的操作。如开始一个事 物，或者取出有特定主键的行。存储引擎不能解析SQL，互相之间也不能通信。仅仅是简单的响应服务器 的请求。

连接管理和安全

在服务器内部，每个client连接都有自己的线程。这个连接的查询都在一个单独的线程中执行。这些线程轮流运行在某一个CPU内核(多核CPU)或者CPU中。服务器缓存了线程，因此不需要为每个client连接单独创建和销毁线程 。

当clients（也就是应用程序）连接到了MySQL服务器。服务器需要对它进行认证(Authenticate)。认证是基于用户名，主机，以及密码。对于使用了SSL（安全套接字层）的连接，还使用了X.509证书。clients一连接上，服务器就验证它的权限 （如是否允许客户端可以查询world数据库下的Country表的数据）。

优化和执行

MySQL会解析查询，并创建了一个内部数据结构（解析树）。然后对其进行各种优化。这些优化包括了，查询语句的重写，读表的顺序，索引的选择等等。用户可以通过查询语句的关键词传递给优化器以便提示使用哪种优化方式，这样即影响了优化器的优化方式。另外，用户也可以请求服务器给出优化过程的各种说明，以获知服务器的优化策略，为用户提供了参数基准，以便用户可以重写查询，架构和修改相关服务器配置，便于mysql更高效的运行。

优化器并是不关心表使用了哪种存储引擎，但是存储引擎对服务器优化查询的方式是有影响的。优化器需要知道存储引擎的一些特性：具体操作的性能和开销方面的信息，以及表内数据的统计信息。例如，存储引擎支持哪些索引类型，这对于查询是非常有用的。

在解析查询之前，要查询缓存，这个缓存只能保存查询信息以及结果数据。如果请求一个查询在缓存 中存在，就不需要解析，优化和执行查询了。直接返回缓存中所存放的这个查询的结果。

2.MySQL逻辑模块组成

虽然从上图1看起来 MySQL 架构非常的简单，就是简单的两部分而已，但实际上每一层 中都含有各自的很多小模块，尤其是第二层 SQL Layer ，结构相当复杂的。下面我们就分别 针对 SQL Layer 和 Storage Engine Layer 做一个简单的分析。我们看下图体系结构：                                        

1.Connectors

指的是不同语言中与SQL的交互

2 Management Serveices & Utilities： 

系统管理和控制工具

3 Connection Pool: 连接池

管理缓冲用户连接，线程处理等需要缓存的需求。

负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，
接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。

4 SQL Interface: SQL接口。

接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

5 Parser: 解析器。

SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。

在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query ，在 MySQL Server 里面，连接线程接收到客户端的一个 Query 后，会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。
主要功能：
a . 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。
b.  如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的

6 Optimizer: 查询优化器。

SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求的 query（sql语句） ，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果

他使用的是“选取-投影-联接”策略进行查询。
       用一个例子就可以理解： select uid,name from user where gender = 1;
       这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤
       这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤
       将这两个查询条件联接起来生成最终查询结果

7 Cache和Buffer： 查询缓存。

他的主要功能是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值 做
一个对应。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

8 、存储引擎接口

存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点了。目前各种数据库产品中，基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是 一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天 MySQL 可插拔存储引擎的特色。

     可以看出，MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。
    注意：存储引擎是基于表的，而不是数据库。

二、查询的执行路径

MySQL 整个查询执行过程，总的来说分为 5 个步骤 :
1> 客户端向 MySQL 服务器发送一条查询请求
2> 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段
3> 服务器进行 SQL解析、预处理、再由优化器生成对应的执行计划
4> MySQL 根据执行计划，调用存储引擎的 API来执行查询
5> 将结果返回给客户端，同时缓存查询结果

查询缓存

查询缓存（ Query Cache ）原理

缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询语句请求，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写

优点：

不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从Query Cache中获得查询结果，提高查询性能

缺点：

查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低其效率；
查询缓存的使用，会增加检查和清理Query Cache中记录集的开销

哪些查询可能不会被缓存

1> 查询语句中加了SQL_NO_CACHE参数
2> 查询语句中含有获得值的函数，包含自定义函数，如：NOW()CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ()等
3> 对系统数据库的查询：mysql、information_schema 查询语句中使用SESSION级别变量或存储过程中的局部变量
4> 查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句，查询语句中类似SELECT …INTO 导出数据的语句
5> 对临时表的查询操作；存在警告信息的查询语句；不涉及任何表或视图的查询语句；某用户只有列级别权限的查询语句
6> 事务隔离级别为Serializable时，所有查询语句都不能缓存

查询缓存相关的服务器变量

1> query_cache_min_res_unit：查询缓存中内存块的最小分配单位，默认4k，较小值会减少浪费，但会导致更频繁的内存分配操作，较大值会带来浪费，会导致碎片过多，内存不足
2> query_cache_limit：单个查询结果能缓存的最大值，默认为1M，对于查询结果过大而无法缓存的语句，建议使用SQL_NO_CACHE
3> query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值40KB，低于此值有警报
4> query_cache_wlock_invalidate：如果某表被其它的会话锁定，是否仍然可以从查询缓存中返回结果，默认值为OFF，表示可以在表被其它会话锁定的场景中继续从缓存返回数据；ON则表示不允许
5> query_cache_type：是否开启缓存功能，取值为ON, OFF, DEMAND

SELECT语句的缓存控制

SQL_CACHE：显式指定存储查询结果于缓存之中
SQL_NO_CACHE：显式查询结果不予缓存

query_cache_type参数变量　

1> query_cache_type的值为OFF或0时，查询缓存功能关闭
2> query_cache_type的值为ON或1时，查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定SQL_NO_CACHE，不予缓存，此为默认值
3> query_cache_type的值为DEMAND或2时，查询缓存功能按需进行，显式指定SQL_CACHE的SELECT语句才会缓存；其它均不予缓存

参看： https://mariadb.com/kb/en/library/server-system-variables/#query_cache_type
           https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html

优化查询缓存

查询缓存相关的状态变量：SHOW GLOBAL STATUS LIKE ‘Qcache%';

1> Qcache_free_blocks：处于空闲状态 Query Cache中内存 Block 数
2> Qcache_total_blocks：Query Cache 中总Block ，当Qcache_free_blocks相对此值较大时，可能用内存碎片，执行FLUSH QUERY CACHE清理碎片
3> Qcache_free_memory：处于空闲状态的 Query Cache 内存总量
4> Qcache_hits：Query Cache 命中次数
5> Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，即没有命中的次数
6> Qcache_lowmem_prunes：记录因为内存不足而被移除出查询缓存的查询数
7> Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL以及由于 query_cache_type 设置的不会被 Cache 的 SQL语句
8> Qcache_queries_in_cache：在 Query Cache 中的 SQL 数量

命中率和内存使用率估算　　

1> 查询缓存中内存块的最小分配单位query_cache_min_res_unit ：(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
2> 查询缓存命中率 ：Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100%
3> 查询缓存内存使用率：(query_cache_size – qcache_free_memory) /query_cache_size * 100%　　

命中率解释：

在解析一个查询语句前，如果查询缓存是打开的，那么 MySQL 会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。
MySQL将缓存存放在一个引用表 (不要理解成table，可以认为是类似于 HashMap 的数据结构)，通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。
所以两个查询在任何字符上的不同 (例如 : 空格、注释)，都会导致缓存不会命中

查询开启缓存的情况：

MariaDB [(none)]> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 10485760 |
| query_cache_strip_comments   | OFF      |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
7 rows in set (0.00 sec)

计算命中率：

此时，我们再来查询之前查询过的内容，就会出现命中的次数：

索引

索引：是特殊数据结构，定义在查找时作为查找条件的字段，在MySQL又称为键key，索引通过存储引擎实现

优点：

索引可以降低服务需要扫描的数据量，减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序I/O

缺点：

占用额外空间，影响插入速度

索引类型：

1> B+ TREE、HASH、R TREE
2> 聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
3> 主键索引、二级（辅助）索引
4> 稠密索引、稀疏索引：是否索引了每一个数据项
5> 简单索引、组合索引
      左前缀索引：取前面的字符做索引
      覆盖索引：从索引中即可取出要查询的数据，性能高

二叉树

原理：

1> 将数据存放在一个一个节点上；

2> 节点又分为三种节点：最上面的叫根节点，中间的叫分支节点，最下面的到底叫叶子节点。

3> 每个分支节点有一个分支，或者两个分支。

缺点：

二叉树存在不平衡的问题。

红黑树

 

 

 原理： 就是平衡的二叉树。

红黑树（Red-Black Tree）是二叉搜索树（Binary Search Tree）的一种改进。我们知道二叉搜索树在最坏的情况下可能会变成一个链表（当所有节点按从小到大的顺序依次插入后）。
而红黑树在每一次插入或删除节点之后都会花O（log N）的时间来对树的结构作修改，以保持树的平衡。也就是说，红黑树的查找方法与二叉搜索树完全一样；插入和删除节点的的方法前半部分节与二叉搜索树完全一样，而后半部分添加了一些修改树的结构的操作。

 缺点： 查询数字的速度慢。

B-TREE索引

 原理：

B树（B-Tree，并不是B“减”树，横杠为连接符，容易被误导）

  是一种多路搜索树（并不是二叉的）：
1.定义任意非叶子结点最多只有M个儿子；且M>2；
2.根结点的儿子数为[2, M]；
3.除根结点以外的非叶子结点的儿子数为[M/2, M]；
4.每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
5.非叶子结点的关键字个数=指向儿子的指针个数-1；
6.非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
7.非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
8.所有叶子结点位于同一层；

B-树的特性：

 1.关键字集合分布在整颗树中；
 2.任何一个关键字出现且只出现在一个结点中；
 3.搜索有可能在非叶子结点结束；
 4.其搜索性能等价于在关键字全集内做一次二分查找；
 5.自动层次控制；

注意：B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；

  每次查找数据时，都去根节点查找数据，查找效率低下。

B+TREE索引

 

通常所说的索引是指 B-Tree索引，它是目前关系型数据库中查找数据最为常用和有效的索引，大多数存储引擎都支持这种索引，且 MySQL 默认采用这种索引。使用 B-Tree 这个术语，是因为 MySQL 在 CREATE TABLE 或其它语句中使用这个关键字，
但实际上不同的存储引擎可能使用不同的数据结构，比如 InnoDB 就是使用的 B+Tree。B+Tree中的B是指balance，意为平衡。相对 Hash索引，B+Tree在查找单条记录的速度比不上 Hash索引，但是因为更适合排序等操作，所以它更受欢迎。毕竟不可能只对数据库进行单条记录的操作

注 : B+树索引 并不能找到一个给定键值的具体行，它找到的只是被查找数据行所在的页，接着数据库会把页读入到内存，再在内存中进行查找，最后得到要查找的数据

      叶子节点只放数据，根节点和分支节点只放索引。

B+树特性：

1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
2.不可能在非叶子结点命中；
3.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
4.更适合文件索引系统；
5.叶子节点最下面的数据块指针直接指向下一个数据块的位置，适合按顺序范围查询，搜索一定的范围，速度非常快。
6.所有的记录优先级一致，查询次数一致，效率一致。

B+Tree索引：顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据

可以使用B+Tree索引的查询类型：

全值匹配：精确所有索引列，如：姓wang，名xiaochun，年龄30
匹配最左前缀：即只使用索引的第一列，如：姓wang
匹配列前缀：只匹配一列值开头部分，如：姓以w开头的
匹配范围值：如：姓ma和姓wang之间
精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的只访问索引的查询

B+Tree索引的限制：

1> 如不从最左列开始，则无法使用索引，如：查找名为xiaochun，或姓为g结尾
2> 不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列
3> 如果查询中某个列是为范围查询，那么其右侧的列都无法再使用索引：如：姓wang,名x%,年龄30，只能利用姓和名上面的索引

特别提示：

1> 索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
2> 为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求

冗余和重复索引：

冗余索引：（A），（A，B）
重复索引：已经有索引，再次建立索引

索引优化策略：

1> 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧
2> 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估
3> 索引选择性：不重复的索引值和数据表的记录总数的比值
4> 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
5> 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧

Hash 索引

mysql 中，只有 Memory(Memory表只存在内存中，断电会消失，适用于临时表) 存储引擎显示支持 Hash索引，是 Memory表的默认索引类型，尽管 Memory表也可以使用 B+Tree索引。Hash索引 把数据以 hash 形式组织起来，因此当查找某一条记录的时候，速度非常快。
但是因为 hash 结构，每个键只对应一个值，而且是散列的方式分布，所以它并不支持范围查找和排序等功能

聚簇和非聚簇索引，主键和二级索引

MyISAM：frm后缀的文件（存放表结构）、文件后缀是YMD（存放数据文件）和YMI后缀的文件（存放索引文件），索引和数据分开存放，为非聚簇索引

innodb：文件中frm后缀（存放表结构）和idb后缀的文件存放（索引和数据文件），索引和数据文件在一起存放，为聚簇索引

稠密索引：一个索引对应了所有的数据。

稀疏索引：一个索引对应了一个数据，且对应的数据不全。

简单索引：对单个字段创建索引

复合索引：对多个字段创建索引　

索引的设计原则

1> 选择唯一性索引，可更快通过索引确定某条记录
2> 为经常需要 排序ORDER BY、分组GROUP BY 和 联合操作UNION 的字段建立索引
3> 为常作为查询条件的字段建立索引
4> 限制索引的数目，索引太多需要的磁盘空间就越大，修改表示对索引的重构和更新会很麻烦
5> 尽量使用数据量少的索引，对 CHAR(100) 全文索引肯定会比 CHAR(10) 耗时多
6> 尽量使用前缀来索引
7> 删除不再使用或者很少使用的索引
8> 避免多个范围条件 : MySQL 支持单列的范围索引，但不支持多列范围索引
9> 尽量避免NULL，含有 NULL 的索引将很难进行优化

索引优化建议

1> 只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
2> 尽量使用短索引，如果可以，应该制定一个前缀长度
3> 对于经常在where子句使用的列，最好设置索引
4> 对于有多个列where或者order by子句，应该建立复合索引
5> 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
6> 尽量不要在列上进行运算（函数操作和表达式操作）
7> 尽量不要使用not in和<>操作

SQL语句性能优化

1> 查询时，能不要*就不用*，尽量写全字段名
2> 大部分情况连接效率远大于子查询
3> 多表连接时，尽量小表驱动大表，即小表 join 大表
4> 在有大量记录的表分页时使用limit
5> 对于经常使用的查询，可以开启缓存
6> 多使用explain和profile分析查询语句
7> 查看慢查询日志，找出执行时间长的sql语句优化

管理索引　　

创建索引：

CREATE INDEX [UNIQUE] index_name ON tbl_name (index_col_name[(length)],...);
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name);
help CREATE INDEX;

删除索引：

DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

查看索引：

SHOW INDEXES FROM [db_name.]tbl_name;

优化表空间：

OPTIMIZE TABLE tb_name;

查看索引的使用

SET GLOBAL userstat=1;
SHOW INDEX_STATISTICS;

示例：

创建索引和查看索引的有效性：

MariaDB [hellodb]> create index idx_name_age on students(name,age); 其中idx_name_age是创建索引名
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> explain select  * from students where name='linchong';  查看索引
+------+-------------+----------+------+---------------+--------------+---------+-------+------+-----------------------+
| id   | select_type | table    | type | possible_keys | key          | key_len | ref   | rows | Extra                 |
+------+-------------+----------+------+---------------+--------------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | students | ref  | idx_name_age  | idx_name_age | 152     | const |    1 | Using index condition |
+------+-------------+----------+------+---------------+--------------+---------+-------+------+-----------------------+
1 row in set (0.01 sec)

查看索引，可将此配置（userstat）写入到 /etc/my.cnf中，使其生效。

查看此时的开关是关闭的：

vim /etc/my.cnf

userstat
systemctl restart mariadb 重启mysql服务

（1）我们此时去查看一个数据：

explain  select * from students  where stuid=1

（2）可以查看到哪些索引已经被利用到：

SHOW INDEX_STATISTICS

 EXPLAIN

1、通过EXPLAIN来分析索引的有效性

2、EXPLAIN SELECT clause

获取查询执行计划信息，用来查看查询优化器如何执行查询

输出信息说明：
参考 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

id: 当前查询语句中，每个SELECT语句的编号

复杂类型的查询有三种：
              简单子查询
              用于FROM中的子查询
              联合查询：UNION

注意：UNION查询的分析结果会出现一个额外匿名临时表

3、select_type：
    简单查询为SIMPLE
    复杂查询：

SUBQUERY 简单子查询
PRIMARY 最外面的SELECT
DERIVED 用于FROM中的子查询
UNION UNION语句的第一个之后的SELECT语句
UNION RESULT 匿名临时表

table：SELECT语句关联到的表

4、type：关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式，以下顺序，性能从低到高

ALL: 全表扫描
index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了使用覆盖索引，而非全表扫描
range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点
ref: 根据索引返回表中匹配某单个值的所有行
eq_ref：仅返回一个行，但与需要额外与某个参考值做比较
const, system: 直接返回单个行

5、possible_keys：查询可能会用到的索引
6、key: 查询中使用到的索引
7、key_len: 在索引使用的字节数

并发控制

锁粒度：

表级锁
行级锁

锁：

读锁：共享锁，只读不可写（包括当前事务） ，多个读互不阻塞
写锁：独占锁，排它锁，写锁会阻塞其它事务（不包括当前事务）的读和它锁

实现

存储引擎：自行实现其锁策略和锁粒度
服务器级：实现了锁，表级锁，用户可显式请求

分类：

隐式锁：由存储引擎自动施加锁
显式锁：用户手动请求

锁策略：在锁粒度及数据安全性寻求的平衡机制
显式使用锁

LOCK TABLES 加锁
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type: READ ， WRITE
UNLOCK TABLES 解锁
FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]   关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁
SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]  查询时加写或读锁

示例：

创建读锁：此时创建的读锁，所有的用户都不能改数据。

MariaDB [hellodb]> lock tables students read;  将students表创建锁
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> update students set classid=1 where stuid=25;  更新数据时，无法更新
ERROR 1099 (HY000): Table 'students' was locked with a READ lock and can't be updated

 解锁：

MariaDB [hellodb]> unlock tables;

示例：

创建写锁：创建的写锁，自己有读写功能，而其他用户没有读权限，只能写。

MariaDB [hellodb]> lock tables students write;

 示例：

对所有的表加上只读权限，因此，不管修改哪张表都不会被修改。

MariaDB [hellodb]> flush tables with read lock;

事务

1、事务Transactions：一组原子性的SQL语句，或一个独立工作单元

2、事务日志：记录事务信息，实现undo,redo等故障恢复功能

解释：

redo：当我们正在想数据库中存放数据，或写入数据时，此时出现断电情况，数据只执行了一半，电源连上之后发现此时的两个数据都只做了一半，就会执行redo功能，重新执行。

undo：当我们正在想数据库中存放数据，或写入数据时，此时出现断电情况，前两个数据已经执行完毕，但是第三个数据未传完，电源连上之后发现此时还有后续的完整数据未执行，就会执行undo功能，就会取消执行。

ACID特性：

A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚

C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态

I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发

D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

启动事务：

BEGIN
BEGIN WORK
START TRANSACTION

结束事务：

COMMIT：提交
ROLLBACK: 回滚

注意：只有事务型存储引擎中的DML语句方能支持此类操作
自动提交：set autocommit={1|0} 默认为1，为0时设为非自动提交
      建议：显式请求和提交事务，而不要使用“自动提交”功能
事务支持保存点：savepoint

SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

注意：alter drop create （table）删除的表，用rollback 都无法撤销，不能恢复之前的文件内容，只能撤销,inster  update delete 表的内容。

示例：

事务开始到结束：begin（开始）---->commit（提交），只要开始了事务，在表中增、删、改、查，都可以撤销，如果提交（commit）了事务就无法撤销错误的动作。

MariaDB [hellodb]> begin;   开启事务
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delete from students where stuid=31;  删除stuid=31的表
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> rollback;    撤销当前删除的表
Query OK, 0 rows affected (0.00 sec)

注意：如果提交了commit命令，之前删除的文件就无法撤销。

示例：

插入文件a和b，并分别写入保存点：

MariaDB [hellodb]> insert students(name)values('a');  插入a表
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [hellodb]> savepoint sp_a;   创建保存点a
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> insert students(name)values('b'); 插入b表
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [hellodb]> savepoint sp_b;  创建保存点b
Query OK, 0 rows affected (0.00 sec)

 查看当前的a,b表内容：

MariaDB [hellodb]> select * from students;

|    32 | a             |   0 | F      |    NULL |      NULL |
|    33 | b             |   0 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+

此时撤销到插入a表的步骤：

rollback  to  sp_a;  撤销到插入a表的动作。

select *  from students; 此时查询插入的b表已不存在。

MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+

|    32 | a             |   0 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+

事务隔离级别

事务隔离级别：从上至下更加严格

 READ UNCOMMITTED 可读取到未提交数据，产生脏读
 READ COMMITTED 可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致
 REPEATABLE READ 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
 SERIALIZABILE 可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务。导致并发性能差

MVCC: 多版本并发控制，和事务级别相关

指定事务隔离级别：

服务器变量tx_isolation指定，默认为REPEATABLE-READ，可在GLOBAL和
SESSION级进行设置

SET tx_isolation=''
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE

服务器选项中指定

vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE

READ UNCOMMITTED 可读取到未提交数据，产生脏读

示例一：可读取到未提交数据，产生脏读

修改A服务的mysql配置文件：

vim /etc/my.cnf

transaction-isolation=READ-UNCOMMITTED

重启mysql：systemctl  restart mariadb

（1）在A服务和B服务上分别打开begin;注意先在B服务上打开begin；再在A服务打开begin；

（2）MariaDB [hellodb]> insert students(name)values("ma yun")  此时在A服务上插入一个表

（3）select  *  from students; 可以在A服务机器上查看到当前插入的服务名称。

MariaDB [hellodb]> insert students(name)values("ma yun");
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    34 | ma yun        |   0 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+

（4）在B服务上查看，由于是脏读的原因，可以查看到A服务机器插入的表格内容。

示例二：可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致

修改A服务的mysql配置文件：

vim  /etc/my.cnf
transaction-isolation=READ-COMMITTED

重启mysql：systemctl  restart mariadb

（1）在A服务和B服务上分别打开begin;注意先在B服务上打开begin；再在A服务打开begin；

  B服务：begin;  A服务：begin;

（2）MariaDB [hellodb]> insert students(name)values("ma yun")  此时在A服务上插入一个表

（3）select  *  from students; 可以在A服务机器上查看到当前插入的服务名称。

MariaDB [hellodb]> insert students(name)values("ma yun");
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    34 | ma yun        |   0 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+

（4）在B服务上查看，可以查看到插入表的内容，如果A服务器上提交了一个事务，又提交一个事务，此时B服务机器上只能查看到最新提交的事务，此时验证了不可重复读的功能。　

  MariaDB [hellodb]> insert students(name)values("ma yun") A服务上插入一个表，

  commit；提交此时的表

  MariaDB [hellodb]> insert students(name)values("ma ge") A服务再插入一个表。

  commit;  提交此时的表。

  此时B服务机器只能查看到最新提交的表的信息，每次查看的事务结果都不一样，看到的都是提交的数据。

示例三：可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置

修改A服务机器的配置文件：

vim  /etc/my.cnf
transaction-isolation=REPEATABLE-READ

重启mysql：systemctl  restart mariadb

（1）在A服务和B服务上分别打开begin;注意先在B服务上打开begin；再在A服务打开begin；

  B服务：begin;  A服务：begin;

（2）MariaDB [hellodb]> insert students(name)values("ma yun")  此时在A服务上插入一个表

          commit；提交此时的表

（3）select  *  from students; 可以在A服务机器上查看到当前插入的服务名称。

MariaDB [hellodb]> insert students(name)values("ma yun");
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    34 | ma yun        |   0 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+

（4）此时B服务机器就不能查看到当前插入的表格信息，实现了可重复读的功能，做备份的功能。

select  *  from students;

示例四：可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务。导致并发性能差

修改A服务机器的配置文件：

vim  /etc/my.cnf
transaction-isolation=REPEATABLE-READ

重启Mysql服务：systemctl restart mariadb

（1）在A服务和B服务上分别打开begin;注意先在B服务上打开begin；再在A服务打开begin；

  B服务：begin;  A服务：begin;

（2）MariaDB [hellodb]>select *  from students;此时在B服务上查看表内容。

（3）MariaDB [hellodb]> delete from students where stuid=33; 在A服务上删除表内容，此时无法删除，是因为此时的表格在B服务上没有执行提交表格（commit）的动作，在A服务器上就无法删除表格内容。

  如果在B服务上提交了表的内容，此时A服务上就可以删除表格。

  此时A服务上删除了表内容，但是没有提交表（commit）,B服务上就没法读取表格的信息。

并发控制

死锁：

两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态

死锁示例：

在A机器上修改students表， 在B机器上在修改teachers表，此时我们A和B机器完全隔离，然后A机器修改B机器的teachers表时，就无法修改。

此时在B机器上修改A机器的students表时，也无法进行修改，而且会牺牲自己修改情况，A机器由于先执行修改，就会优先会进行修改。

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction  报错，提示死锁，并将自己执行的结果先撤销。