Mysql详解

Mysql的介绍

【1】MySQL是一个轻量级关系型数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，就增加了速度并提高了灵活性。

【2】sql语言分类：

【3】索引分类

　　1）聚簇索引（又称为主键索引，本质上数据是存储在叶子节点上）

　　2）非二级索引（又称为二级索引，本质上叶子节点只存储数据的id，需要进行回表获得数据）

　　【1】类型

　　　　（1）NORMAL（普通索引，也是最常用的）

　　　　（2）FULLTEXT（全文索引）

　　　　（3）SPATIAL（空间索引）

　　　　（4）UNIQUE（唯一索引）

　　【2】方法

　　　　（1）BTREE（也就是B+Tree）

　　　　（2）HASH（也就是hash table结构）

　　【3】字段个数

　　　　（1）单个

　　　　（2）多个（组合索引又称为复合索引）

Mysql的列的数据类型详解

【1】数值

【2】字符串

【3】时间和日期

【4】null，没有值【注意：使用NULL进行运算，结果为NULL】

Mysql的存储引擎【存储引擎生效的单位是表】

【1】展示

mysql> show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        || MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         || MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         || BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         || MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         || CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         || ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         || PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         || FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+9 rows in set (0.06 sec)

【2】MyISAM存储引擎

【2.1】文件说明

//每张表对应会有三个文件//MyISAM索引文件和数据文件是分离的(非聚集)user.MYI    //索引存储的信息user.MYD    //数据存储的信息user.frm    //数据表结构的信息

【2.2】图示

【3】InnoDB存储引擎

【3.1】文件说明

//每张表对应会有两个文件//表数据文件本身就是按B+Tree组织的一个索引结构文件//聚集索引-叶节点包含了完整的数据记录article.frm    //数据表结构的信息article.ibd    //数据与索引存储的信息

【3.2】图示

【3.3】为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

　　1）首先如果不建立的话，它会从数据列中找出全部不同的一列作为主键，如果找不到会创建一个隐藏列作为主键。那么既然会有隐藏列列了，干脆直接创建就好。

　　2）如果使用UUID作为主键，首先，UUID不易于存储，16字节128位，通常以36长度的字符串表示，很多场景不适用。其次新行的主键值不一定比之前的主键值大，所以innoDb无法做到总是把新行插入到索引的最后，而需要为新行寻找合适的位置来分配新的空间，这个过程会导致：

【1】写入的目标页可能从缓存上移除了，或者还没有加载到缓存上，innodb写入之前需要先从磁盘找到目标页，会产生大量的随机IO；【2】因为写入是乱序的，innoDb 要做频繁的分页操作，以便为行产生新的空间，页分裂导致移动大量的数据，一次插入最少需要修改三个页以上；【3】频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片；【4】随机值（uuid和雪花id）载入到聚簇索引，有时候会需要做一次OPTIMEIZE TABLE来重建表并优化页的填充，这将又需要一定的时间消耗。

　　3）使用自增主键则可以避免上述问题：

【1】自增主键值是顺序的，所以Innodb把每一条记录都存储在一条记录的后面。当达到页面的最大填充因子时候(innodb默认的最大填充因子是页大小的15/16,会留出1/16的空间留作以后的修改)，下一条记录就会写入新的页中；【2】数据按照顺序方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费；【3】新插入的行一定会在原有的最大数据行下一行，mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗；【4】减少了页分裂和碎片的产生

【3.4】非主键索引结构叶子节点存储的是主键值，主要是为了一致性和节省存储空间。

常用函数记录

【1】数据函数

ABS(X)        //绝对值CEILING(X)    //向上取整FLOOR(X)    //向下取整ROUND(X)    //如果无参,返回一个0-1之间的随机数；如果有参，返回参数X的四舍五入的一个整数。SIGN(X)        //符号函数: 负数返回-1,正数返回1,0返回0

【2】字符串函数

【3】日期和时间函数

【4】聚合函数

count()    //返回满足查询（Select）条件的 总和数，如select count(*) [不建议使用，效率低]min()    //可以为数值字段、字符字段或表达式列做统计，返回最小值max()    //可以为数值字段，字符字段或表达式列作统计，返回最大的值avg()    //返回一列的平均值sum()    //返回一列的总和

Mysql索引底层数据结构

【1】索引的本质：索引是帮助MySQL高效获取数据的排好序的数据结构。

【2】索引数据结构：在我们创建索引的时候会给予我们两个选择，BTREE【这里指的是b+tree】与HASH。

【2.1】B+Tree结构

　　1）说明

1.B+Tree是B-Tree的变种2.非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引3.叶子节点包含所有索引字段4.叶子节点用指针连接，提高区间访问的性能

　　2）分析能存储的数据量

　　　　【1】页是 InnoDB 管理的最小单位，常见的有 FSP_HDR，INODE, INDEX 等类型。页结构分为文件头(前38字节)，页数据和文件尾(后8字节)。每个数据页大小为16kb，查看方法为

mysql> show global status like 'Innodb_page_size';+------------------+-------+| Variable_name    | Value |+------------------+-------+| Innodb_page_size | 16384 |+------------------+-------+1 row in set (0.03 sec)

　　　　【2】系统从磁盘中读取数据到内存时是以磁盘块（block）为基本单位（4kb【这个与操作系统有关】），位于同一个磁盘块中的数据会被一次性读取出来。block大小空间往往没有16kb大，因此innodb每次io操作时都会将若干个地址连续的磁盘块的数据读入内存，从而实现整页读入内存。

　　　　【3】如果索引字段为bigint，它会占据8B，而地址指针【指向下一个磁盘块文件地址】占据的是6B。所以此时，一般能存1170个左右的数据【这个数值其实是没有减去数据页的一些其他数据的，只是假设全部用于存储数据】。如果非叶子节点是两层的话，那么就会是136万的数据量。假设叶子结点存储的数据是1K，那么可以存储16个，数据量便会是2176万的数据。

　　　　【4】而且Mysql的索引的根结点是常驻内存的。

　　3）图示

【2.2】hash结构

　　1）说明

1.对索引的key进行一次hash计算就可以定位出数据存储的位置2.很多时候Hash索引要比B+ 树索引更高效3.仅能满足 “=”，“IN”，不支持范围查询4.hash冲突问题

　　2）图示

【3】对于那么多的数据结构，如链表，数组，二叉树，红黑树（平衡二叉树），B-tree（多路平衡二叉树）为什么都不选择？

【3.1】首先对于链表与数组，它们自身的缺点很明显，数组查询快但是遇到向中间插入数据的情况会涉及到大量的数据迁移，而链表涉及的数据迁移几乎没有但是查询效率在大数据量下其实很慢。故是不合适的。

【3.2】对于二叉树，在极端情况下，它是会存在退化为链表结构的。所以才会有平衡二叉树的存在，但是平衡二叉树也会存在问题，就是数据量大了之后层级会很多，也不能很好的利用磁盘块的理念，所以才会出现多路平衡二叉树。

【3.3】那么为什么B-tree也不会被选择呢？

　　1）说明

1.叶节点具有相同的深度，叶节点的指针为空2.所有索引元素不重复3.节点中的数据索引从左到右递增排列

　　2）图示

　　3）理由

　　　　【1】层级依旧会出现很大的情况，如果数据量是1k，那么地址指针的大小先不算，一个数据页最多能容纳16个数据，两千多万的数据便需要7层，远远大于B+Tree的层级。一次load节点是一次磁盘IO，是非常慢的，但是我们把它load到内存中之后在你内存里随机的找某一个元素是非常快的，跟一次磁盘IO这个时间消耗去比对的话几乎可以忽略不计。故层级越多涉及的磁盘IO也就越多。

　　　　【2】其次是不好优化，因为BTree中，数据都在节点上，必然会出现层级不一的情况，快慢便是取决于你所在的层级。而B+Tree要获取数据需要到对应的叶子结点上，保证了经历的层级数是相同的。

【4】联合索引的原理

　　1）说明

索引最左前缀原理1.首先索引是帮助MySQL高效获取数据的排好序的数据结构，重点是排好序的数据结构。2.所以基于此情况下它是先将第一个元素排序，然后再第一个元素相同的情况下，进行第二个元素的排序，以此类推。3.所以在查找过程中需要先匹配到第一个元素，然后在匹配到第二个，再到第三个【当然只匹配前面的也是可以的】，这样就可以获取到数据的id。4.基于id再去聚簇索引找到完整的行记录【逐一进行回表扫描】，这边是我们常说的回表操作。5.其中里面又涉及了一个叫做索引下推的概念：通过explain查看执行计划，看到Extra一列里 Using index，这就是用到了索引下推。6.索引下推：指当第一个元素匹配了，然后还可以用第二个乃至第三个元素进行过滤。因为这种不需要进行回表拿到数据进行过滤，减少了回表的次数。7.其次，这类索引的好处是，因为你已经是索引的一部分了，所以对应的获取这部分的数据其实也是不需要进行回表的。

　　2）图示

Mysql的锁机制

【1】锁分类

1）从性能上分为乐观锁(用版本对比来实现)和悲观锁2）从对数据操作的粒度分，分为表锁和行锁3）从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)，还有意向锁

【2】对锁类型的分析【本质上怎么说，这些基本不用手动加，数据库的引擎会自动加】

【2.1】读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响，比如：select * from T where id=1 lock in share mode;

【2.2】写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁，数据修改操作都会加写锁，查询也可以通过for update加写锁，比如：select * from T where id=1 for update;

【2.3】意向锁（Intention Lock）：又称I锁，针对表锁，主要是为了提高加表锁的效率，是mysql数据库自己加的。当有事务给表的数据行加了共享锁或排他锁，同时会给表设置一个标识，代表已经有行锁了，其他事务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁。特别是表中的记录很多时，逐行判断加表锁的方式效率很低。而这个标识就是意向锁。

意向锁主要分为：意向共享锁，IS锁，对整个表加共享锁之前，需要先获取到意向共享锁。意向排他锁，IX锁，对整个表加排他锁之前，需要先获取到意向排他锁

【3】对锁粒度的分析

【3.1】表锁：每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。

【3.1.1】基本操作

// 建表SQLCREATE TABLE `mylock` (    `id` INT (11) NOT NULL AUTO_INCREMENT,    `NAME` VARCHAR (20) DEFAULT NULL,    PRIMARY KEY (`id`)) ENGINE = MyISAM DEFAULT CHARSET = utf8;// 手动增加表锁lock table 表名称 read(write),表名称2 read(write);// 查看表上加过的锁show open tables;// 删除表锁unlock tables;//如果加了读锁当前session和其他session都可以读该表当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待//如果加了写锁当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞

【3.1.2】总结

1、对MyISAM表的读操作(加读锁) ,不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。2、对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作

【3.2】行锁：每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

【3.2.1】一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞

【3.2.2】InnoDB与MYISAM的最大不同有两点：

InnoDB支持事务（TRANSACTION）InnoDB支持行级锁

【3.3】总结：

MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

Mysql的事务机制

【1】事务的ACID特性

//事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。1）原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。2）一致性(Consistent) ：在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。3）隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。）持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

【2】并发事务处理带来的问题

【3】事务隔离级别

【4】事务隔离级别注意事项

//数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。//同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读"和“幻读”并不敏感,可能更关心数据并发访问的能力。常看当前数据库的事务隔离级别: show variables like 'tx_isolation';设置事务隔离级别：set tx_isolation='REPEATABLE-READ';Mysql默认的事务隔离级别是可重复读【因为MVCC多版本并发控制机制】，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别。

MVCC多版本并发控制机制

【1】Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制。（至于为什么要实现）

【2】因为隔离性就是靠MVCC(Multi-Version Concurrency Control)机制来保证的，对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操作加锁互斥来实现的。

【3】实现这个机制主要是依赖undo日志版本链与read view机制：

【4】undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链。

【5】当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前都不会变化【这里指在可重复读隔离级别下】(如果是读已提交隔离级别在每次执行查询sql时都会重新生成)，这个视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。

【6】图示：

【7】版本链比对规则：

1）如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的；2）如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的 trx_id 就是当前自己的事务是可见的）；3）如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况  【1】 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的事务是可见的)；  【2】 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。

【8】对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。

【注意】begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句，事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。

【总结】MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。

Innodb引擎SQL执行的BufferPool缓存机制

【1】图示

【2】说明（为什么Mysql不能直接更新磁盘上的数据而设置这么一套复杂的机制来执行SQL？）

　　1）如果来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据，这样的效率是很低的。因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。

　　2）Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性（undoLog针对数据进行恢复，RedoLog对数据进行重做操作）。

　　3）更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。

　　4）正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干甚至上万的读写请求。