深入理解mysql-阿里云开发者社区

一、mysql

mysql是一个开放源代码的关系数据库管理系统，原开发作者为瑞典的MySQL AB公司，该公司于2008年被Sun公司收购，2009年又被Oracle公司收购，当初Oracle收购MySQL的时候，就遭到了广大开源爱好都们的极力反对，Oracle也承认对其在5年之内不会收费，现已临近5年的最后时刻，MySQL将何去何从也倍受人们的关注。

MySQL在过去由于性能高、成本低、可靠性好，已经成为了最流行的开源数据库，因此被广泛地应用到了Internet上的中小型网站中。随着MySQL的发展和不断成熟，它也逐步用于了更大规模的网站和应用。

由于MySQL被Oracle收购，所以Oracle极有可能将其商业化，因此MySQL的原作者就开发了另外一套强大的数据库管理系统MariaDB，依然对其开源。介于此，好多大公司，如谷歌、维基百科、facebook等也纷纷将其数据库从MySQL迁移到了MariaDB。

二、数据库管理系统

1、数据库管理系统

数据库管理系统（Database Management System）是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。用户通过DBMS访问数据库中的数据，DBA也通过DBMS进行数据库的维护工作。它可使多个应用程序和用户用不同的方法在同时或不同时刻去建立、修改和询问数据库。大部分DBMS提供数据定义语言DDL（Data Definition Language）和数据操纵语言DML（ Data Manipulation Language ），供用户定义数据库的模式结构与权限约束，实现对数据追加、删除等操作。

2、数据库管理系统模型

1）mysql解析器：把接收到的SQL命令切片，做语法分析、词法分析，选择最佳执行路径

2）文件存取方法：文件访问接口，把内存中的数据转换为持久数据（做流式化、遍平化）

3）缓存管理器：把数据缓存到事务日志文件中，尽可能减少性能的降低

4）并发控制管理器：其主要目的就是为了实现多个用户同时访问时，如何保证在出故障时数据正常有序的进行；对于并发管理器来说，如果支持事务，则需要事务管理器，事务需要有隔离级别，所以就需要有锁管理器

5）修复管理器：修复管理器主要目的就是为了保证出现故障，数据做如何处理；比如万一断电了，数据才同步到一关，这该怎么办呢？所以就有了修复管理器：对于提交的事务，则需要同步到数据文件中；对于未提交的事务，则回滚到事务执行前的状态

三、事务（transaction）

1、事务

事务就是一组原子性的查询语句，也即将多个查询当作一个独立的工作单元

2、事务标准

判断是否是事务的标准是看其是否能够满ACID测试，如果能满足ACID测试就表示其支持事务，或兼容事务

A：Atomicity，原子性（一个原子是不可分隔的，要么都执行，要么都不执行）

C：Consistency，一致性（从一个一致性状态转到另一个一致性状态而不会导致数据丢失）

例，比如银行转账，在转账前后，双方钱的总数是一致的

I：Isolation，隔离性（一个事务的所有操作在提交之前，对其它用户是不可见的）

例，比如银行转账，你给别人转账5000，在你提交之前，别人的账户是看不到的，也不会多出5000块

D：Durability，持久性（一旦事务得到提交，其所做的修改会永久有效）

例，比如银行转账，你提交转账之后，钱到达对方账户后就不能再撤销了

3、隔离级别

在说隔离级别之前，先说三个概念：

①、脏读：可以读别人未提交的数据，意思就是一个用户连入一个会话后，对于它所做的修改操作，在没有提交之前，别的会话中的用户是可以读到它的操作的，可想而知，这是多么的不安全，安全性太差了

②、幻读：就像刚才说的，脏读是可以看到别人未提交的，所以对于在另一个会话中的用户来说，如果前一个会话中的用户做了修改操作，它刚才明明可以看到的数据，现在又看不到了，感觉好像眼花了，出现幻觉似的，所以就叫幻读

③、可重读：在自己提交之前，看到的数据都是一致的

对于事务的隔离性，依据其隔离强度又划分为4个级别

1）READ UNCOMMITTED，读未提交

这种隔离级别下就会出现脏读和幻读，而且不可重读

2）READ COMMITTED，读提交

这种隔离级别是别人提交了才能看到，所以它就避免了脏读问题，但依然还存在幻读和不可重读的问题。大部分的关系型数据库都是这个隔离级别

3）REPEATABLE READ，可重读，在自己的事务提交之前，看到的数据都是一样的，保证在多次读一个事务时，读到的数据都是同一个。从名字就可以看出来，这个隔离级别是可以重读的，解决了脏读问题，依然存在幻读。mysql默认就是这个隔离级别

4）SERIALIZABLE，可串行化，这个级别的隔离中事务和事务之间是进行严格隔离的，在这个隔离级别中，一个事务如果操作数据，就会对其阻塞，别的事务根本连读的机会都没有。所以这个隔离级别强制事务的串行化执行，就会完全避免了幻读问题，可想而知，它的性能是极低的，只有为了确保数据非常一致才会用到SERIALIZABLE隔离级别

4、跟事务相关的常用命令

1）启动事务

 
          mysql>start transaction

2）提交事务

 
          mysql>commit

3）回滚

 
          mysql>rollback
         
          mysql>savepoint identifier
         
          mysql>rollback [work] to [savepoint] identifier

5、关于手动提交事务

mysql中事务是默认提交的，所以会造成服务器性能的下降；因此有时可能会把自动提交事务改为手动提交。

相关命令：

 
         mysql>show global variables like 
         '%autocommit%'
         ; 
        
         mysql>
         select 
         @@global.autocommit; 
        
         mysql>
         set 
         global autocommit=0;

注意：关闭自动提交后，请记得手动启动事务，手动进行提交

6、查看mysql的事务隔离级别

 
          mysql>show global variables like 
          'tx_isolation'
          ; 
         
          mysql>
          select 
          @@global.tx_isolation'; 
         
          mysql>
          set 
          global tx_isolation=
          'READ-COMMITTED'
          ;

在对事务要求不是特别严格的场景下，可以使用读提交

7、MVCC，多版本并发控制

每个事务启动时，InnoDB会为每个启动的事务提供一个当下时刻的快照；为了实现此功能，InnoDB会为每个表提供两隐藏的字段，一个用于保存行的创建时间，一个用于保存行的失效时间

里面存储的是系统版本号（system version number）

只在两个隔离级别下有效：READ COMMITTED和REPEATABLE READ（对于那两个来说，一个是改了就能看到，一个是改了也看不到）

四、mysql存储引擎

查看表的存储引擎：

 
          mysql>show table status like 
          'students'
          \G;

修改默认存储引擎：

通过default_storage_engine服务变量实现

1、InnoDB

两种格式：

1）innodb_file_per_table=OFF，即使用共享表空间

每张表一个独有的格式定义文件：tb_name.frm

还有一个默认位于数据目录下共享的表空间文件：ibdata#

查看数据目录：mysql>show variables like 'datadir';

2）innodb_file_per_table=ON，即使用独立表空间

每个表在数据库目录下存储两个文件

tb_name.frm 每表有一个表结构文件

tb_name.ibd 一个独有的表空间文件

3）InnoDB存储引擎的特性

①、支持事务，事务日志最少是两个连续的磁盘空间

②、外链 ③、支持MVCC ④、聚簇索引 ⑤、支持辅助索引 ⑥、支持自适应hash索引

⑦、等级锁（在行和行之间施加一个锁来隔离行）

⑧、支持热备份（必须把表存储为独立表空间，不离线即可备份）

表空间：table space，由InnoDB管理的特有的格式数据文件，内容可同时存储数据和索引

2、MyISAM

1）每个表都在数据库目录下存储三个文件

tb_name.frm 表结构定义

tb_name.MYD 数据文件

tb_name.MYI 索引文件

2） MyISAM 存储引擎的特性

①、支持全文索引，InnoDB不支持，但可借助其它工具实现

②、支持表压缩：用于实现数据仓库，能节约存储空间并提升性能

③、空间索引 ④、延迟更新索引

⑤、表级锁，不支持行级锁，只能到表级

3） MyISAM 存储引擎缺点：

①、不支持事务、外键和行级锁

②、MyISAM崩溃后无法安全恢复数据

4）适用场景

适用于只读数据、较小的表，能够容忍崩溃后的修改操作和数据丢失

五、mysql日志

1、查询日志

查询日志默认是关闭的，因为查询操作太多了，所以关闭状态可能是一种合理的选择吧

查询日志相关的服务器变量：

 
          mysql>show global variables like 
          '%log%'
          ;

log={ON|OFF}:是否记录所有语句的日志信息于一般查询日志文件(general_log);

log_output={TABLE|FILE|NONE}

TABLE和FILE可以同时出现，用逗号分隔即可；

general_log：是否启用查询日志；

general_log_file：定义一般查询日志保存的文件，只有log_output=FILE时，此项才有意义

2、慢查询日志

当服务器某些查询语句执行性能很差时，通常需要启动

long_query_time：10.000000 超出这个时长的都被记录为慢查询

slow_query_log={ON|OFF}

设定是否启用慢查询日志；它的输出位置也取决log_output={TABLE|FILE|NONE}；

slow_query_log_file=www-slow.log

定义日志文件路径及名称；

log_slow_filter=admin, filesort,filesort_on_disk,full_join,full_scan,query_cache，query_cache_miss,tmp_table,tmp_table_on_disk

log_slow_queries=ON

log_slow_rate_limit=1

log_slow_verbosity ：详细慢查

3、错误日志

1）服务器启动和关闭过程中的信息；

2）服务器运行过程中的错误信息；

3）事件调度器运行一个事件时产生的信息；

4）在复制架构中的从服务器上启动从服务器线程时产生的信息；

log_error = /path/to/error_log_file

log_warnings = {1|0}

是否记录警告信息于错误日志中；

4、事务日志

事务日志就是为了增加客户体验，把随机I/O转换为顺序I/O，日志文件组应该至少有两个事务日志文件，以实现轮替，定期同步到数据文件中。

注意：

1）尽可能使用小事务提升事务引擎的性能，因为大事务会造成更大的回滚开销

2）万一数据文件所在的磁盘坏了，数据就不太容易恢复了，所以应该尽可能把事务日志文件放到RAID10等RAID设备上，要保证其足够可靠。

3）事务日志不是用来恢复数据的，事务日志仅仅是崩溃时能够让你去提交，同步数据；保证提交的事务不丢失，而未提交的事务能回滚；它并不能让你把数据重新走一遍，要重新走一遍只能依赖二进制日志

4）尽可能不要把事务日志和数据日志放在一块磁盘上，会对其性能造成影响；因为从内存到磁盘需要I/O，从事务日志到数据文件也需要I/O，所有的I/O都叠加到一块了，所以会影响其性能，因此，如果条件允许，尽可能把事务日志和数据文件各放到一个RAID设备上；如果实在没办法实现硬件级别，就再来一组事务日志文件，对其做镜像（mirror），以保证其在同步到磁盘的同时，再同步一份到镜像中去

5、二进制日志

时间点恢复

复制

具体用法在下一篇博客mysql主从复制中详细介绍

6、中继日志

中继日志在从服务器上，中继日志的内容和二进制日志上的内容是一样的

本文转自 nmshuishui 51CTO博客，原文链接：http://blog.51cto.com/nmshuishui/1393608，如需转载请自行联系原作者

深入理解mysql

热门文章

最新文章

相关课程

相关电子书

相关实验场景