Mysql_related

最新推荐文章于 2024-09-15 08:03:51 发布

U5J5N9_MAERD

最新推荐文章于 2024-09-15 08:03:51 发布

阅读量183

点赞数

分类专栏： DailyWorks 文章标签： mysql

本文链接：https://blog.csdn.net/baidu_38938517/article/details/113529293

版权

DailyWorks 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

Practice in class 1-1

MySQL

版本演变：

1995年5月23日，MySQL的第一个内部版本发行了。

1996年10月，MySQL 3.11.1发布(MySQL没有2.x版本)。在接下来的两年里，MySQL被依次移植到各个平台，同时加入了不少新的特性。

1998年1月，MySQL关系型数据库发行了第一个版本。它使用系统核心的多线程机制提供完全的多线程运行模式，并提供了面向C、C++、Eiffel、Java、Perl、PHP、Python及Tcl等编程语言的编程接口(API)，且支持多种字段类型，并且提供了完整的操作符支持。而且MySQL已经能够运行在10多种操作系统之上，其中包括应用非常广泛的 FreeBSD、Linux、Windows 95和Windows NT等。

很快MySQL 3.22也发布了，但它仍然存在很多问题，如不支持事务操作、子查询、外键、存储过程和视图等功能。

1999-2000年，MySQL开始支持事务处理。

2000年4月，MySQL对旧的存储引擎ISAM进行了整理，将其命名为MyISAM。

2001年，集成存储引擎InnoDB，这个引擎同样支持事务处理，还支持行级锁。该引擎之后被证明是最为成功的MySQL事务存储引擎。

2003年12月，MySQL 5.0版本发布，提供了视图、存储过程等功能。

2008年1月，MySQL AB公司被Sun公司收购，MySQL数据库进入Sun时代。在Sun时代，Sun公司对其进行了大量的推广、优化、Bug修复等工作。

2008年11月，MySQL 5.1发布，它提供了分区、事件管理，以及基于行的复制和基于磁盘的NDB集群系统，同时修复了大量的Bug。

2009年4月20日，Oracle公司收购Sun公司，自此MySQL数据库进入Oracle时代，而其第三方的存储引擎InnoDB早在2005年就被Oracle公司收购。

2010年12月，MySQL 5.5发布，其主要新特性包括半同步的复制及对SIGNAL/RESIGNAL的异常处理功能的支持，最重要的是InnoDB存储引擎终于变为当前MySQL的默认存储引擎。MySQL 5.5加强了MySQL各个方面在企业级的特性。

2013年2月，MySQL5.6发布。Oracle最近宣布将于2021年2月停止5.6版本的更新，结束其生命周期。

2015年12月，MySQL5.7发布，其性能、新特性、性能分析带来了质的改变。

2016年9月，MySQL开始了8.0版本，Oracle宣称该版本速度是5.7的两倍，性能更好。

2018年4月，MySQL8.0.11发布。

目前，MySQL已更新到8.0.18版本，但市场主流都还是使用的5.5/5.6/5.7版本。

https://www.cnblogs.com/joyfulcode/p/12683009.html

SQL引擎

数据库引擎是用于存储、处理和保护数据的核心服务。

InnoDB：5.5版本后MySQL的默认数据库。支持事务，是事务型数据库的首选引擎。支持事务安全（ACID特性），支持行锁定和外键，比MyISAM处理速度稍慢

InnoDB主要特性有：

InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合；

InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的；

InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引放在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上；

InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显式在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键；

InnoDB被用在众多需要高性能的大型数据库站点上；

InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。

MyISAM：高速引擎，拥有较高的插入、查询速度，但不支持事务。MyISAM基于ISAM存储引擎，并对其进行扩展。是Web、数据仓储等应用环境下最常使用的存储引擎之一。

在5.1之前，MyISAM是默认的引擎，MyISAM有大量的特性，包括全文索引、压缩、空间函数。但是MyISAM不支持事务和行级锁，而且在崩溃后无法安全恢复。即使后续版本中MyISAM支持了事务，但是很多人的概念中依然是不支持事务的引擎。

对于一些只读数据，或者表空间较小，可以忍受恢复操作，可以使用MyISAM。MyISAM会将表存储在两个文件中：数据文件、索引文件。分别是.MYD、.MYI扩展名。MyISAM表可以包含动态或者静态行。MySQL会根据表定义选择那种行格式。MyISAM表的行记录数，取决于磁盘空间和操作系统中的单个文件最大尺寸。

在MySQL中，默认配置只能存储256TB的数据。因为指向数据记录的指针长度是6字节。需要修改可以修改表的MAX_ROWS和AVG_ROW_LENGTH选项，两个相乘是最大的大小。但会导致重建索引。

MyISAM是对整个表加锁，而不是行锁，读取的时候对表加共享锁，写入的时候加排他锁。但是在表有读取查询的同时，也可以往表内写入记录。

对于MyISAM，即使是Blob，Text等等长字段，也可以基于前500字符创建索引，MyISAM支持全文索引，这是一个基于分词创建的索引，也可以支持复杂的查询。

MyISAM可以选择延迟更新索引键，在创建表的时候指定delay_key_write选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是写到缓存区，只有在清理缓存区或者关闭表的时候才会将索引写入磁盘。这可以极大的提升写入性能，但是在主机崩溃时会造成索引损坏，需要执行修复操作。

MyISAM另一个特性是支持压缩表。如果数据在写入后不会修改，那么这个表适合MyISAM压缩表。可以使用myisampack对MyISAM表进行打包，压缩表是不可以修改数据的。压缩表可以极大的减少磁盘占用，因此可以减少磁盘IO，提升性能，压缩表也支持索引，但是索引也是只读的。

由于没有行锁机制，所以在海量写入的时候，会导致所有查询处于Locked状态。

https://zhuanlan.zhihu.com/p/53619907

事务处理级别划分

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

体系结构

由连接池组件、管理服务和工具组件、SQL接口组件、查询分析器组件、优化器组件、

缓冲组件、插件式存储引擎、物理文件组成。

Practice in class 2-1

在业务需求不是以固定的顺序访问表和行的时候，即交叉访问时，如果没有在业务逻辑上加上合适的锁，容易造成事务等待回路；

应尽量避免大事务，占有的资源锁越多，越容易出现死锁，所以可以在业务逻辑上将大事务拆分成小事务；

有些代码逻辑需要进行加锁分析以避免事务阻塞乃至死锁；

平常开发的时候如果对查询条件没有索引的，一定要进行一致性读，也就是加锁读，会导致全表加上索引，会导致其他事务全部阻塞，数据库基本会处于不可用状态。

https://blog.csdn.net/qq_41701956/article/details/81623448

mysql数据库锁机制实现的特征？

根据锁锁定数据的颗粒度可分为表级锁、行级锁和页级锁；

行级锁与表级锁是可以共存的，InnoDB为了让表锁和行锁共存而引入了意向锁；

InnoDB中有几种行级锁类型：Record Lock、Gap Lock、Next-key Lock；

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

无论是悲观锁还是乐观锁，都是人们定义出来的概念，可以认为是一种思想。针对于不同的业务场景，应该选用不同的并发控制方式。不要把乐观并发控制和悲观并发控制狭义的理解为DBMS中的概念，更不要把他们和数据中提供的锁机制（行锁、表锁、排他锁、共享锁）混为一谈。在DBMS中，悲观锁正是利用数据库本身提供的锁机制来实现的。

InnoDB的细粒度锁(即行锁)，是实现在索引记录上的(如果未命中索引则会失效)；

记录锁锁定索引记录；间隙锁锁定间隔，防止间隔中被其他事务插入；临键锁锁定索引记录+间隔，防止幻读；

InnoDB使用插入意向锁，可以提高插入并发；

间隙锁(gap lock)与临键锁(next-key lock)只在RR以上的级别生效，RC下会效；

https://www.cnblogs.com/volcano-liu/p/9890832.html

Practice in class 2-2

\1. Mysql针对优化的工具

1） mysqltuner-pl

数据库性能诊断工具，主要检查参数设置的合理性包括日志文件、存储引擎、安全建议及性能分析。

2） tuning-primer.sh

对于mysql的整体进行一个体检，对潜在的问题，给出优化的建议。

3） pt-variable-advisor

分析MySQL变量并就可能出现的问题提出建议。

4） pt-qurey-digest

从日志、进程列表和tcpdump分析MySQL查询。

性能监控工具

1） Nagios

一款用于系统和网络监控的应用程序。它可以在你设定的条件下对主机和服务进行监控，在状态变差和变好的时候给出告警信息。

2）MONyog

MONyog是一个轻量级的无代理的监控系统，它的实现方法较特殊：它的底层是一个JavaScript引擎，所有配置都是通过JavaScrpt对象模型来完成的。它被设计为在桌面系统上运行，运行时它会在一个闲置的端口上打开一个HTTP监听器。这样，可以把浏览器指向这个端口，查看MySQL服务器的信息了，这信息都是结合了Javascript和Flash来表示的。MONyog实际上有交互式和非交互式两种类型。

分析工具

1） HackMySQL

2） Maatkit分析工具

一系列命令行工具的集合。

https://www.cnblogs.com/javastack/p/12606106.html

https://www.cnblogs.com/ceshi2016/p/9068818.html

Practice in class 2-3

对于底层封装不去深入了解，只是知道如何使用，这样在功能升级或优化之后，不能很好的适应变化。

Practice in class 3-1

myisam的主键索引的叶子节点只存放数据在物理磁盘上的指针，其他次索引也是一样的；innodb的主键索引的叶子节点下面直接存放数据，其他次索引的叶子节点指向主键id。

Practice in class 3-2

复合索引的使用原则是最左匹配原则，如果要查询的是y或z相关的数据行，则不会用到该复合索引；

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。所以如果表是经常需要更新的，不适合做索引；

如果where条件中是OR关系，加索引不起作用；

数据量少的字段不需要加索引，维护开销大。

Practice in class 3-3

普通索引

普通索引是MySQL中最基本索引类型，基本上对列的数据不会加任何的限制，可以允许空值和重复值，最主要的用途就是加快数据的访问速度。

唯一索引

唯一索引指的是对数据添加了唯一性约束，这一列的数据当中不能够出现重复的值，但是可以有null值（至多有一个）。在创建唯一索引时会进行数据校验，如果此时已经有了重复数据，则索引创建失败。在建立唯一索引后，再向表中插入已有的重复数据时，也会被拒绝。

主键索引

主键索引的特点是：非空、唯一。所以如果我们想找一列作为每条数据的唯一标识，就会将这一列声明为主键。由于主键的特点，经常会被作为其他表外键的参照列，也是在进行数据同步时的主要参照。

复合索引

同一索引类型作用在多个不同列上时，可以称之为组合索引，对于主键索引，有一个特别的称呼：复合主键。（注意：复合主键与联合主键不是同一个含义，联合主键指的是将多个主键的值联合在一起，常出现在多对多逻辑的中间表中，比如：将两个有关联的数据主键定义在中间表中，然后再为中间表设立一个主键来唯一标识每一条关联数据）