Mysql详解

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)

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

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

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

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

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

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

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

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

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

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

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

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

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

// 建表SQL
CREATE 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对该表的所有操作被阻塞

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

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

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

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

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

Mysql默认的事务隔离级别是可重复读【因为MVCC多版本并发控制机制】，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别。

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 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。