Mysql学习（一）

最新推荐文章于 2024-08-01 08:23:29 发布

samir_liu

最新推荐文章于 2024-08-01 08:23:29 发布

阅读量316

点赞数

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/m0_37683758/article/details/86737895

版权

数据库专栏收录该内容

15 篇文章 0 订阅

订阅专栏

MySQL逻辑架构

基本架构

　　如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图，有助于深入理解MySQL服务器。下图展示了MySQL的逻辑架构图。

MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。

　　MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。

所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。

　　最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。

中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。每一个客户端发起一个新的请求都由

服务器端的连接/线程处理工具负责接收客户端的请求并开辟一个新的内存空间，在服务器端的内存中生成一个新的线程，

当每一个用户连接到服务器端的时候就会在进程地址空间里生成一个新的线程用于响应客户端请求，

用户发起的查询请求都在线程空间内运行，结果也在这里面缓存并返回给服务器端。线程的重用和销毁都是由连接/线程处理管理器实现的。

　　综上所述：用户发起请求，连接/线程处理器开辟内存空间，开始提供查询的机制。

MySQL查询过程

当向MySQL发送一个请求的时候，MySQL到底做了些什么呢？下图展示了MySQL的查询过程。

客户端/服务端通信协议

MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，

要么是客户端向服务器发送数据，这两个动作不能同时发生。

一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。

　　客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。

但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。

　　与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。

但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。

因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，

这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。

查询缓存

在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。

如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

　　MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，

这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。

所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。

　　如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。

比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的

查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

　　既然是缓存，就会失效，那查询缓存何时失效呢？MySQL的查询缓存系统会跟踪查询中涉及的每个表，

如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，

MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。

而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

　　1. 任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存
　　2. 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

　　基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，

只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。如果系统确实存在一些性能问题，

可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：
　　1. 多个小表代替一个大表，注意不要过度设计
　　2. 批量插入代替循环单条插入
　　3. 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适
　　4. 可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存
　　最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为DEMAND，

这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。

语法解析和预处理

　　MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。

比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

查询优化

　　经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，

最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。

　　MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。

mysql> select * from t_message limit 10;
...省略结果集

mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 6391.799000 |
+-----------------+-------------+

　　示例中的结果表示优化器认为大概需要做6391个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的，

这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。

　　有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、

MySQL认为的最优跟我们想的最优并不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短）等等。

　　MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划：　　

重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）
优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值）
提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）
优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，
而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多）

查询执行引擎

　　在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。

整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。

实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。

存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

返回结果给客户端

查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等等。

　　如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。

　　结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了。

这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，

结果集中的每一行都会以一个满足客户端/服务器通信协议的数据包发送，再通过TCP协议进行传输，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送。

　　回头总结一下MySQL整个查询执行过程，总的来说分为6个步骤：

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

MySQL存储引擎

简单来说，存储引擎就是指表的类型以及表在计算机上的存储方式。

存储引擎的概念是MySQL的特点，Oracle中没有专门的存储引擎的概念，Oracle有OLTP和OLAP模式的区分。不同的存储引擎决定了MySQL数据库中的表可以用不同的方式来存储。

我们可以根据数据的特点来选择不同的存储引擎。

存储引擎	说明
MyISAM	高速引擎，拥有较高的插入，查询速度，但不支持事务
InnoDB	5.5版本后MySQL的默认数据库，支持事务和行级锁定，比MyISAM处理速度稍慢
ISAM	MyISAM的前身，MySQL5.0以后不再默认安装
MRG_MyISAM（MERGE）	将多个表联合成一个表使用，在超大规模数据存储时很有用
Memory	内存存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。只在内存上保存数据，意味着数据可能会丢失
Falcon	一种新的存储引擎，支持事物处理，传言可能是InnoDB的替代者
Archive	将数据压缩后进行存储，非常适合存储大量的独立的，作为历史记录的数据，但是只能进行插入和查询操作
CSV	CSV 存储引擎是基于 CSV 格式文件存储数据(应用于跨平台的数据交换)

在MySQL中的存储引擎有很多种，可以通过“SHOW ENGINES”语句来查看。下面重点关注InnoDB、MyISAM、MEMORY这三种。

MyISAM存储引擎

存储

每个MyISAM在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分别为：

.frm(存储表定义)
MYD(MYData，存储数据)
MYI(MYIndex，存储索引)

例：

文件名	说明
test.frm	存储表定义
test.MYD	存储数据
test.MYI	存储索引

　　MyISAM的表支持3种不同的存储格式：

静态(固定长度)表：

静态表是默认的存储格式。静态表中的字段都是非变长字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，

出现故障容易恢复；缺点是占用的空间通常比动态表多。静态表在数据存储时会根据列定义的宽度定义补足空格，但是在访问的时候并不会得到这些空格，

这些空格在返回给应用之前已经去掉。同时需要注意：在某些情况下可能需要返回字段后的空格，而使用这种格式时后面到空格会被自动处理掉。

动态表

动态表包含变长字段，记录不是固定长度的。如果数据表中出现varchar、xxxtext或xxxBLOB字段时，服务器将自动选择这种表类型。

相对于静态MyISAM，这种表存储空间比较小，但由于每条记录的长度不一，所以多次修改数据后，数据表中的数据就可能离散的存储在内存中，进而导致执行效率下降。同时，内存中也可能会出现很多碎片。

因此，这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理。

压缩表

　　压缩表由myisamchk工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。以上说到的两种类型的表都可以用myisamchk工具压缩。

这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先时行解压缩。

索引

支持的索引类型：

(1)BTree索引（最常见）

(2)R-Tree索引

(3)Full-Text索引

其他特点

锁机制——支持表级锁定
事务处理——为保证效率，不支持事务处理
增删改查性能——SELECT性能较高，适合执行查询较多的情况使用
COUNT(*)问题——MyISAM存储引擎记录表行数，所以在使用COUNT(*)时，只需取出存储的行数，而不用遍历表，效率较高

InnoDB存储引擎

从物理意义上来讲，InnoDB表由共享表空间、日志文件组（redo文件组）、表结构定义文件组成。若将innodb_file_per_table设置为on，

则系统将为每一个表单独的生成一个table_name.ibd的文件，在此文件中，存储与该表相关的数据、索引、表的内部数据字典信息。表结构文件则以.frm结尾，这与存储引擎无关。

InnoDB表类型可以看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能。InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

特点

同MyISAM一样的是，InnoDB存储引擎也有.frm文件存储表结构定义
与MyISAM不同的是，InnoDB的表数据与索引数据是存储在一起的，但在这个文件中每张表是独自占有一块表空间还是共享所有表空间，是由用户决定的。

如果独享表空间，每个表的表数据与索引数据都会存放在一个.ibd(innoDB data)文件中；如果是共享表空间，通过innodb_data_file_path指定后，每次增加数据文件后必须停机重启才能生效，很不方便

InnoDB有支持事务及安全的日志文件，这个文件非常重要，InnoDB可以通过日志文件将数据库崩溃时已经完成但还没来得及将内存中已经修改但未完全写入磁盘的数据写入磁盘，也可以把已部分完成并写入磁盘的未完成事务回滚，保证数据一致性。
支持的索引类型与MyISAM基本一致，但具体实现因为文件结构的不同有很大差异。具体请看下文
锁机制的改进——实现了行级锁，为承受高并发增加了竞争力。
事务处理——实现了事务处理，可谓与MyISAM最重要的区别之一。
COUNT(*)问题——InnoDB存储引擎会遍历表以计算数量，效率较低。
其他特点——实现了外键、提供了多版本数据的提取。

MEMORY存储引擎

MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。

MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。

每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，

这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。

MEMORY默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。

三种存储引擎比较

MySQL索引相关

B-tree索引原理

B树索引

下面来具体介绍一下B-树（Balance Tree），B树是一种多路平衡查找树，每个节点最多包含k个孩子，k称为树的阶，k的大小取决于磁盘页的大小。一个m阶的B树具有如下几个特征：

1.根结点至少有两个子女。

2.每个中间节点都包含k-1个元素和k个孩子，其中 m/2 <= k <= m

3.每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m

4.所有的叶子结点都位于同一层。

5.每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划。

例如一个3阶B树如下图所示：

特征：

1、每个节点不再只有一个key，而是多个key；相比二叉树，有效压低了树高

2、和典型二叉树一样，依然是每个节点包括key和value；这加剧了随机IO问题，也是B+树被使用的原因；

典型B树，形似"多叉树"，且每个节点包括多个有序节点，其访问方法，和二叉树道理一样，只是每个树的节点，包括了多个key而不是二叉树那样只有一个key，这样树的高度大大被压低，以其查询伪代码为例加深印象：

BTree_Search(node, key) {
if(node == null) return null;
foreach(node.key)
{
if(node.key[i] == key) return node.data[i];
if(node.key[i] > key) return BTree_Search(point[i]->node);
}
return BTree_Search(point[i+1]->node);
}
data = BTree_Search(root, my_key);

如同AVL、红黑树一样，增删节点会导致B树分裂；

其读写的算法访问效率是对数级的，了解到这够用。

实际的数据库磁盘读写，如果是B树的话，会是这样做：

1、最开始创建B树时：

磁盘中申请空间，载入到内存，写入；

2、key按顺序不断写入磁盘时：

顺序写入磁盘，速度非常快，如按顺序insert大量数据到mysql时，因为：

1、每次都是申请一个磁盘页(如4K大小)，而不是要写几个字节申请几个字节；

2、B树顺序写的时候，数据都是向后自然顺延，不发生分裂，除非当前磁盘页写满时，再申请一个新的磁盘页，继续顺序写；

3、当B树很大时，随机的写时：

比如，删除某一个key1，然后又更新一个key2的数据，key1和key2不在同一个磁盘页中，然后又增加一个key3导致发生分裂；

比如有大量的这样的操作，导致：

1、寻找key1时，内存未找到，被迫从磁盘里读取一个页；

2、删除后可能导致B树分裂，可能又导致更新磁盘里的其他数据；

3、增加一个key3，又没有找到，再次从磁盘读取一个页；可能导致分裂，再次导致更新磁盘其他页数据

4、更新一个key2，又没有找到；

5、最后会发现，数据越来越多后，增删查改操作，到处都是磁盘IO，经常需要从磁盘里读，然后写；

B+树索引

B+树的改进：

1、非叶子节点不再存储数据；好处是，使每一个磁盘页里，有更多一些的节点；能够减少一些磁盘IO；

2、mysql实际使用时，叶子节点加入了相邻叶子节点的指针；好处是，在有序遍历时，找到了一个叶子，就可以顺序的访问其他叶子，避免了都从根节点遍历；又减少了一些磁盘IO；

B+树，在非叶子层不再存储数据，因而每个树节点变小，也就集中了更多的节点；增多了一个磁盘页中，实际节点的个数；这对于减少磁盘IO是有很大意义的；mysql就是使用B+树作为数据索引；

B+树如下图：

至此，对基于B族树的存储引擎的mysql，其查询效率就是：

1、尽可能使按可以的顺序写入；

2、在随机不按key顺序的增删查改时，就没办法了，B+树尽可能一个磁盘页里有更多的节点，减少磁盘IO次数，叶子节点中加入相邻的指针，进一步减少磁盘IO，更方便范围检索；

B+树索引和哈希索引的区别：

简单地说，哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

从上面的图来看，B+树索引和哈希索引的明显区别是：

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
从示意图中也能看到，如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
同理，哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
哈希索引也不支持多列联合索引的最左匹配规则；
B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

MyISAM树索引

聚簇索引：索引的叶节点指向数据

非聚簇索引：索引的叶节点指向数据的引用

对于innodb引擎：myisam使用非聚簇索引，innodb使用聚簇索引

1、主键索引既存储索引值，又在叶中存储行数据 2、如果没有主键，则会使用 unique key 做主键 3、如果没有unique，则mysql会生成一个rowid做主键.

myisam对于表的主键的索引方式，如一个表有3个列，col1、col2、col3

myisam的叶子存储的数据是，数据所在的地址，而不是数据内容

下图是myisam的非主键的索引，和主键索引方式差不多，也是存数据的地址：

myisam的索引方式的表，在查询时先通过B+树找到key，进而找到数据地址，然后再根据地址，找到数据内容；

InnoDB树索引

再来看一下innodb的，使用过mysql的都知道如下准则或建议：

1、innodb的表要求必须有主键；

2、主键尽可能建议是，按顺序自增的id；

3、主键不应该太长；

来看下为什么，下面是innodb的表的主键的索引：

innodb索引方式是把数据完全放在叶子节点上，而不是myisam那样只存数据地址；

再来看innodb的表的辅助键的索引：

看吧，innodb的表的辅助键的索引，存的其实是它对应的主键；

也就是：

1、innodb的索引，其实都是到它的主键索引，比如通过辅助键的查询，就是先通过辅助键索引，查到对应的主键，然后再去查主键的索引；

这就是为什么，innodb的表必须有主键；

同时也是为什么，innodb的表的行锁，其实也只是支持字段是主键时的操作时；如果是非主键字段，和myisam是表锁；

innodb的表支持行锁，这也是其支持事务的必要条件之一

2、可见innodb的表是非常依赖主键的了，所以如果主键不是按照自增的顺序，那么在插入时，会出现B+树更多的分裂，内存找不到的又得找磁盘，导致更多的磁盘IO；

这就是为什么，innodb的表建议按业务无关的自增id作为主键；

3、如果主键是一个特别长的字符串之类东西，那么辅助键的索引，叶子节点也都会存这些特别长的主键，那么辅助键的索引，会很大；

这就是为什么，innodb的表不建议主键使用特别长的字段；

4、innodb的叶子存的是数据，比起myisam存的是地址，在写时，原则上应该会少一些磁盘IO，因为myisam还需要再去获取数据；

上面介绍了基于B族树存储引擎的(mysql)的读写原理，结论是：不论是B树，还是B+树，在数据量很大时，随机IO问题均无法良好处理；

索引类型

主键索引

primary key() 要求关键字不能重复，也不能为null,同时增加主键约束主键索引定义时，不能命名

唯一索引

unique index() 要求关键字不能重复，同时增加唯一约束

普通索引

index() 对关键字没有要求

全文索引

fulltext key() 关键字的来源不是所有字段的数据，而是字段中提取的特别关键字

复合索引

可以是某个字段或多个字段，多个字段称为复合索引

建表：
creat table student(
stu_id int unsigned not null auto_increment,
name varchar(32) not null default '',
phone char(11) not null default '',
stu_code varchar(32) not null default '',
stu_desc text,
primary key ('stu_id'), //主键索引
unique index 'stu_code' ('stu_code'), //唯一索引
index 'name_phone' ('name','phone'), //普通索引，复合索引
fulltext index 'stu_desc' ('stu_desc'), //全文索引
) engine=myisam charset=utf8;
更新：
alert table student
add primary key ('stu_id'), //主键索引
add unique index 'stu_code' ('stu_code'), //唯一索引
add index 'name_phone' ('name','phone'), //普通索引，复合索引
add fulltext index 'stu_desc' ('stu_desc'); //全文索引
删除：
alert table sutdent
drop primary key,
drop index 'stu_code',
drop index 'name_phone',
drop index 'stu_desc';

三、索引使用原则

1、列独立

保证索引包含的字段独立在查询语句中，不能是在表达式中

2、左前缀

like:匹配模式左边不能以通配符开始，才能使用索引注意：前缀索引在排序 order by 和分组 group by 操作的时候无法使用。

3、复合索引由左到右生效

建立联合索引，要同时考虑列查询的频率和列的区分度。

1、index(a,b,c)

4、不要滥用索引，多余的索引会降低读写性能or的两边都有存在可用的索引，该语句才能用索引。

即使满足了上述原则，mysql还是可能会弃用索引，因为有些查询即使使用索引，也会出现大量的随机io，相对于从数据记录中的顺序io开销更大。

分析sql是否达到索引

筛选出有问题的 SQL，我们可以使用 MySQL 提供的 explain 查看 SQL 执行计划情况（关联表，表查询顺序、索引使用情况等）。

用法：

字段解释： 1) id：select 查询序列号。id相同，执行顺序由上至下；id不同，id值越大优先级越高，越先被执行

2) select_type：查询数据的操作类型，其值如下：

simple：简单查询，不包含子查询或 union
primary:包含复杂的子查询，最外层查询标记为该值
subquery：在 select 或 where 包含子查询，被标记为该值
derived：在 from 列表中包含的子查询被标记为该值，MySQL 会递归执行这些子查询，把结果放在临时表
union：若第二个 select 出现在 union 之后，则被标记为该值。若 union 包含在 from 的子查询中，外层 select 被标记为 derived
union result：从 union 表获取结果的 select复制代码

3) table：显示该行数据是关于哪张表

4) partitions：匹配的分区

5) type：表的连接类型，其值，性能由高到底排列如下：

system：表只有一行记录，相当于系统表
const：通过索引一次就找到，只匹配一行数据
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常用于主键或唯一索引扫描
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。用于=、< 或 > 操作符带索引的列
range：只检索给定范围的行，使用一个索引来选择行。一般使用between、>、<情况
index：只遍历索引树
ALL：全表扫描，性能最差复制代码

注：前5种情况都是理想情况的索引使用情况。通常优化至少到range级别，最好能优化到 ref

6) possible_keys：指出 MySQL 使用哪个索引在该表找到行记录。如果该值为 NULL，说明没有使用索引，可以建立索引提高性能

7) key：显示 MySQL 实际使用的索引。如果为 NULL，则没有使用索引查询

8) key_len：表示索引中使用的字节数，通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好显示的是索引字段的最大长度，并非实际使用长度

9) ref：显示该表的索引字段关联了哪张表的哪个字段

10) rows：根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好

11) filtered：返回结果的行数占读取行数的百分比，值越大越好

12) extra：包含不合适在其他列中显示但十分重要的额外信息，常见的值如下：

using filesort：说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。出现该值，应该优化 SQL
using temporary：使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。出现该值，应该优化 SQL
using index：表示相应的 select 操作使用了覆盖索引，避免了访问表的数据行，效率不错
using where：where 子句用于限制哪一行
using join buffer：使用连接缓存
distinct：发现第一个匹配后，停止为当前的行组合搜索更多的行复制代码

注意：出现前 2 个值，SQL 语句必须要优化。

MySQL事务

事务基本概念

先看一个经典银行转账案例：

A向B的银行卡转账1000元，这里分两个主要事件，一个是A向B转账1000，那么A的银行卡转账成功后必须在原来的数额上扣掉1000元，

另一个是B收到了A的转款，B的银行卡上数额必须增加1000元，这两个步骤是必须都成功才算转账成功，总不能A转账B后，A的数额没有变化而B增加了1000元吧？这样银行不得亏死了？

因此两个步骤只要有一个失败，此次转账的结果就是失败。但我们在执行sql语句时，两个动作是分两个语句执行的，万一执行完一个突然没电了另外一个没有执行，那岂不出问题了？

此时就需要事务来解决这个问题了，所谓的事物就是保证以上的两个步骤在同一个环境中执行，只要其中一个失败，事务就会撤销之前的操作，回滚的没转账前的状态，如果两个都执行成功，那么事务就认为转成成功了。这就是事务的作用。

对事务有了初步理解后，进一步了解事务的官方概念，事务是DBMS的执行单位。它由有限个数据库操作语句组成。但不是任意的数据库操作序列都能成为事务。一般来说，事务是必须满足4个条件（ACID）

原子性（Autmic）：一个原子事务要么完整执行，要么干脆不执行。也就是说，工作单元中的每项任务都必须正确执行，如果有任一任务执行失败，则整个事务就会被终止并且此前对数据所作的任何修改都将被撤销。
如果所有任务都被成功执行，事务就会被提交，那么对数据所作的修改将会是永久性的
一致性（Consistency）：一致性代表了底层数据存储的完整性。它是由事务系统和应用开发人员共同来保证。事务系统通过保证事务的原子性，隔离性和持久性来满足这一要求;
应用开发人员则需要保证数据库有适当的约束(主键，引用完整性等)，并且工作单元中所实现的业务逻辑不会导致数据的不一致(数据预期所表达的现实业务情况不相一致)。例如，在刚才的AB转账过程中，
从A账户中扣除的金额必须与B账户中存入的金额相等。
隔离性（Isolation）：隔离性是指事务必须在不干扰其他事务的前提下独立执行，也就是说，在事务执行完毕之前，其所访问的数据不能受系统其他部分的影响。
持久性（Durability）：持久性指明当系统或介质发生故障时，确保已提交事务的更新数据不能丢失，也就意味着一旦事务提交，DBMS保证它对数据库中数据的改变应该是永久性的，
耐得住任何系统故障，持久性可以通过数据库备份和恢复来保证。

事务原理概要

事务的处理机制是通过记录更新日志而实现的，其中与事务处理相关的日志是UNDO日志和REDO日志。

undo日志

UNDO日志亦称为回滚端，在进行数据插入、更新、删除的情景下，保存变更前的数据，原理图如下：

在表中保存了指向UNDO日志的指针，rollback执行时根据这个指针来获取旧数据并覆盖到表中，rollback执行完成后或者commit后UNDO日志将被删除。

UNDO还有另外一种作用，当A用户正在更新数据时，还没提交，而B用户也需要使用该数据，这时不可能让B读取未提交的数据，因此会将存在UNDO表中的数据提供给B用户。这就是事务回滚的简单模型。

redo日志

REDO日志主要是事务提交后由于错误或者断电停机等原因使数据无法更新到数据库中时，REDO日志将提供数据恢复作用。

其原理是通过数据库中的一段缓冲的数据先实时更新到REDO日志再更新到数据库，也就是说平常的更新操作并非一步执行到位的，而是首选更新到REDO日志中，再更新到数据库文件的。所以REDO日志才能用户故障数据的恢复。

锁

悲观锁和乐观锁

悲观锁：假设会发生并发冲突，回避一切可能违反数据完整性的操作。

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性，注意乐观锁并不能解决脏读的问题(关于脏读稍后解析)。

在一般情况下，悲观锁依靠数据库的锁机制实现，以保证操作最大程度的排他性和独占性，因而会导致数据库性能的大量开销和并发性很低，特别是对长事务而言，这种开销往往过于巨大而无法承受。

为了解决这样的问题，乐观锁机制便出现了。乐观锁，大多情况下是基于数据版本（ Version ）记录机制实现。

何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。

此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则给予更新，否则认为是过期数据。ok~，关于悲观锁和乐观锁的简单概念就先了解到这。

mysql中的共享锁与排他锁

在mysql中，为了保证数据一致性和防止数据处理冲突，引入了加锁和解锁的技术，这样可以使数据库中特定的数据在使用时不让其他用户(进程或事务)操作而为该数据加锁，直到该数据被处理完成后再进行解锁。

根据并发事务锁定的关系上看，分为共享锁定和独占锁定，共享锁定会防止独占锁定但允许其他的共享锁定。

而独占锁定既防止共享锁定也防止其他独占锁定。为了更改数据，数据库必须在进行更改的行上施加行独占锁定，insert、update、delete和selsct for update语句都会隐式采用必要的行锁定。

共享锁定：将对象数据变为只读形式的锁定，这样就允许多方同时读取一个数据，此时数据将无法修改。
排他锁定（独占锁）：在对数据进行insert/update/delete时进行锁定，在此时其他用户(进程或事务)一律不能读取数据，从而也保证数据完整性。
以上两种锁都属于悲观锁的应用，还有一点，根据锁定粒度的不同，可分为行锁定(共享锁和排他锁使用应用的就是行锁定)，表锁定，数据库锁定，

可见粒度的不同将影响用户(进程或事务)对数据操作的并发性，目前mysql支持行锁定和表锁定。

事务性问题及事务隔离

一个数据库可能拥有多个访问客户端，这些客户端并发访问数据库时，若没有采取必要的隔离措施，存在以下问题，这些问题分为5类，包括3类数据读问题：脏读、不可重复读和幻读。两类数据更新问题：第一类丢失更新、第二类丢失更新。

脏读

A事务读取B事务尚未提交的更改数据，并在这个数据的基础上进行操作，这时候如果事务B回滚，那么A事务读到的数据是不被承认的。例如常见的取款事务和转账事务：

不可重复读

不可重复读发生在一个事务执行相同的查询两次或两次以上，但是每次都得到不同的数据时。这通常是因为另一个并发事务在两次查询期间进行了更新。请注意，不可重复读重点是修改数据导致的(修改数据时排他读);，

例如：在事务1中，客户管理人员在读取了张曹宇的生日为1990-08-05,操作并没有完成

select birth from user where name ='张曹宇' ;
1
在事务2中，这时张曹宇自己修改生日为1990-06-05,并提交了事务.

begin;
-- 其他操作省略
update user set birth='1990-06-05' where name ='张曹宇' ;
commit;

在事务1中，客户管理人员再次读取了张曹宇的生日时，生日变为1990-06-05,从而导致在一个事务中前后两次读取的结果并不一致，导致了不可重复读。

幻读

A事务读取B事务提交的新增数据,会引发幻读问题。幻读一般发生在计算统计数据的事务中，例如银行系统在同一个事务中两次统计存款账户的总金额，

在两次统计中，刚好新增了一个存款账户，存入了100，这时候两次统计的总金额不一致。

注意：不可重复读和幻读的区别是：前者是指读到了已经提交的事务的更改数据（修改或删除），后者是指读到了其他已经提交事务的新增数据。

对于这两种问题解决采用不同的办法，防止读到更改数据，只需对操作的数据添加行级锁，防止操作中的数据发生变化；二防止读到新增数据，往往需要添加表级锁，将整张表锁定，防止新增数据。

第一类丢失更新

A事务撤销时，把已经提交的B事务的更新数据覆盖了。例如：

这时候取款事务A撤销事务，余额恢复为1000，这就丢失了更新。

第二类丢失更新

A事务覆盖B事务已经提交的数据，造成B事务所做的操作丢失：

事务处理隔离级别

为了解决上述问题，数据库通过锁机制解决并发访问的问题。根据锁定对象不同：分为行级锁和表级锁；根据并发事务锁定的关系上看：分为共享锁定和独占锁定，共享锁定会防止独占锁定但允许其他的共享锁定。

事实上，锁的出现更多的是为了在多个用户(进程或事务)同时执行更新操作时保证数据的完整性和一致性，但随之而来的问题是当数据的锁定时间越长，数据同时运行性也会随之降低。

也就意味着当一个用户(进程或事务)对数据保存锁定时，其他用户(进程或事务)只能等待锁定解锁，这样也就导致并发访问该数据的同时性较低。

所以在多用户(进程或事务)对数据进行更新或者访问的同时如何保证数据的完整性和一致性，这样的情况下需要有一个相对折中的妥协，因为并不是频繁锁定数据或者极致提供同时运行性就是合理的，

为了描述这个问题数据库中引入分离水平(有些地方称为隔离级别)的概念来确定事务处理之间的相互影响程度。其规则描述：分离水平越高，数据的完整性也就越高，但同时运行性下降，

相反如果分离水平越低数据完整性越低，同时运行性也就提高了。在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务，并发虽然是常见的，

但可能会导致不同分离水平下发生不同的数据读取情况，4种分离水平以及可能导致的情景如下：

四种分离水平(隔离级别)

READ_UNCOMMITTED：这是事务最低的分离水平(隔离级别)，它充许别外一个事务可以看到这个事务未提交的数据，会出现脏读、不可重复读、幻读（分离水平最低，并发性能高）

READ_COMMITTED：保证一个事务修改的数据提交后才能被另外一个事务读取。另外一个事务不能读取该事务未提交的数据。可以避免脏读，但会出现不可重复读、幻读问题（锁定正在读取的行，mysql默认隔离级别）

REPEATABLE_READ：可以防止脏读、不可重复读，但会出幻读（锁定所读取的所有行）

SERIALIZABLE：这是花费最高代价但是最可靠的事务分离水平(隔离级别)，事务被处理为顺序执行。保证所有的情况不会发生（锁表,并发性及其低）

事务的隔离级别和数据库并发性是成反比的，隔离级别越高，并发性越低。

锁、事务、隔离级别之间的关系

通过上述的分析，我们也理解了事务、锁和分离水平的概念，但锁和事务以及隔离级别关系如何呢？

实际上，事务是解决多条sql执行执行过程的原子性、一致性、隔离性、持久性的整体解决方案，

而事务隔离级别则是并发控制的整体解决方案，其实际是综合利用各种类型的锁来解决并发问题。

锁是数据库并发控制的内部基础机制。对应用开发人员来说，只有当事务分离水平无法解决并发问题和需求时，才有必要在语句中手动设置锁。

关于锁的锁定，对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。

请注意InnoDB行锁是通过给索引上的索引项加锁来实现的，也就是说，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

-- 共享锁（S）
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;

-- 排他锁（X)
SELECT * FROM table_name WHERE ... FOR UPDATE;

samir_liu

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql学习（一）

MySQL逻辑架构基本架构　　如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图，有助于深入理解MySQL服务器。下图展示了MySQL的逻辑架构图。MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。　　MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间...
复制链接

扫一扫

专栏目录