深入理解mysql

  1. 云栖社区>
  2. 博客>
  3. 正文

深入理解mysql

余二五 2017-11-14 22:19:00 浏览1059
展开阅读全文
一、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、数据库管理系统模型

wKioL1NGfD2gmcvsAADbxCNeGx4949.png

   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)启动事务
1
mysql>start transaction
   2)提交事务
1
mysql>commit
   3)回滚
1
2
3
mysql>rollback
mysql>savepoint identifier
mysql>rollback [work] to [savepoint] identifier
5、关于手动提交事务
   mysql中事务是默认提交的,所以会造成服务器性能的下降;因此有时可能会把自动提交事务改为手动提交。

   相关命令:

1
2
3
mysql>show global variables like '%autocommit%';
mysql>select @@global.autocommit;
mysql>set global autocommit=0;
   注意:关闭自动提交后,请记得手动启动事务,手动进行提交
6、查看mysql的事务隔离级别
1
2
3
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存储引擎
   查看表的存储引擎:
1
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、查询日志
   查询日志默认是关闭的,因为查询操作太多了,所以关闭状态可能是一种合理的选择吧
   查询日志相关的服务器变量:
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,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
余二五
+ 关注