【学习记录】MySql数据库相关知识

Mysql数据库的特性

原子性：事务是一个原子操作单元，对数据的修改，要么全都执行，要么全都不执行

• 通过undoLog（回滚日志）进行保证原子性

一致性：在事务开始和完成时，数据都必须保持一致状态

• 通过原子性、隔离性以及持久性来保证一致性，这是非关系型数据库最核心以及最本质的东西

隔离性：事务不受外部并发操作影响的“独立”环境执行

• 通过数据库锁+MVCC来进行保证

持久性：事务完成之后，它对于数据的修改是永久性的

• 通过redoLog以及binLog、两阶段提交来保证

MVCC 多版本并发控制

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

了解MVCC之前我们需要知道两个概念：当前读、快照读

当前读：像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

快照读：像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

MVCC的实现
MVCC模块在MySQL中的具体实现是由三个隐式字段，undoLog、read view三个组件来实现的。

MVCC是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单项增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照，所以MVCC可以为数据库解决一下问题：

1、在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
2、解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题

三个隐藏字段：

• DB_TRX_ID：最近修改事务id，记录创建这条记录或者最后一次修改该记录的事务id；
• DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本,用于配合undolog，指向上一个旧版本
• DB_ROW_JD：隐藏主键，如果数据表没有主键，那么innodb会自动生成一个6字节的row_id

UndoLog
undolog被称之为回滚日志，表示在进行insert，delete，update操作的时候产生的方便回滚的日志；

不同事务或者相同事务的对数据库同一记录的修改，会导致该记录的undolog生成一条记录版本线性表，即链表，undolog的链首就是最新的旧记录，链尾就是最早的旧记录

当进行insert操作的时候，产生的undolog只在事务回滚的时候需要，并且在事务提交之后可以被立刻丢弃
当进行update和delete操作的时候，产生的undolog不仅仅在事务回滚的时候需要，在快照读的时候也需要，所以不能随便删除，只有在快照读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

Read View
Read View是事务进行快照读操作的时候生产的读视图，有可能读取到的是最新的数据，也有可能读取的是当前行记录的undolog中某个版本的数据

RC、RR级别下的InnoDB快照读有什么不同
在RC（读已提交）隔离级别下，是每个快照读都会生成并获取最新的Read View,而在RR（可重复读）隔离级别下，则是同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是同一个Read View.

数据库的事务隔离级别

数据库的事务隔离级别有四种，分别是读未提交、读已提交、可重复读、序列化，不同的隔离级别下会产生脏读、幻读、不可重复读等相关问题

Oracle默认隔离级别是读已提交，mysql默认隔离级别则是可重复读

各种隔离级别和数据库异常情况对应情况如下：

隔离级别	脏读	不可重复读	幻读
READ- UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE- READ	×	×	√
SERIALIZABLE	×	×	×

1. 脏读（dirty read）：A事务读取B事务尚未提交的更改数据
2. 不可重复读（unrepeatable read）：A事务读取了B事务已经提交的更改（或删除）数据。
3. 幻读（phantom read）：A事务读取了B事务已经提交的新增数据。注意和不可重复读的区别，这里是新增，不可重复读是更改（或删除）。
   如果事务中都是用快照读，那么不会产生幻读的问题，但是快照读和当前读一起使用的时候就会产生幻读。
   此时可以采用间隙锁的方式来解决幻读的问题

sql join原理?

MySQL是只支持一种Join算法Nested-Loop Join(嵌套循环连接)，并不支持哈希连接和合并连接，不过在mysql中包含了多种变种，能够帮助MySQL提高join执行的效率。

1、Simple Nested-Loop Join
从驱动表中取出R1匹配S表所有列，然后R2，R3,直到将R表中的所有数据匹配完，然后合并数据，可以看到这种算法要对S表进行RN次访问

2、Index Nested-Loop Join
这种算法在链接查询的时候，驱动表会根据关联字段的索引进行查找，当在索引上找到了符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表。至于驱动表的选择，MySQL优化器一般情况下是会选择记录数少的作为驱动表，但是当SQL特别复杂的时候不排除会出现错误选择

3、Block Nested-Loop Join
Block Nested-Loop Join对比Simple Nested-Loop Join多了一个中间处理的过程，也就是join buffer，使用join buffer将驱动表的查询JOIN相关列都给缓冲到了JOIN BUFFER当中，然后批量与非驱动表进行比较，这也来实现的话，可以将多次比较合并到一次，降低了非驱动表的访问频率。也就是只需要访问一次S表。这样来说的话，就不会出现多次访问非驱动表的情况了，也只有这种情况下才会访问join buffer。
默认情况下join_buffer_size=256K

---

Mysql存储引擎

MyISAM介绍

MyISAM是MySQL的默认数据库引擎（5.5版之前），由早期的ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然性能极佳，但却有一个缺点：不支持事务处理（transaction）。不过，在这几年的发展下，MySQL也导入了InnoDB（另一种数据库引擎），以强化参考完整性与并发违规处理机制，后来就逐渐取代MyISAM。

InnoDB介绍

是MySQL的数据库引擎之一，为MySQL AB发布binary的标准之一。InnoDB由Innobase Oy公司所开发，2006年五月时由甲骨文公司并购。与传统的ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务（Transaction）功能，类似于PostgreSQL。目前InnoDB采用双轨制授权，一是GPL授权，另一是专有软件授权。

MyISAM和InnoDB两者之间的区别？

区别	InnoDB	MyISAM
事务	支持	不支持
外键	支持	不支持
索引	即支持聚簇索引又支持非聚簇索引	只支持非聚簇索引
行锁	支持	不支持
表锁	支持	支持
存储文件	frm，ibd包含索引以及数据	frm表结构,myi索引文件,myd数据文件
具体行数	每次必须要全表扫描统计行数	通过变量保存行数（查询不能带条件）

聚簇索引和非聚簇索引的区别？

跟数据绑定在一起的索引我们称之为聚簇索引，没有跟数据绑定在一起的索引我们称之为非聚簇索引。
innodb存储引擎中既有聚簇索引也有非聚簇索引，而myisam存储引擎中只有非聚簇索引。

---

数据库索引原理、底层索引数据结构，叶子节点存储的是什么，索引失效的情况？

索引（Index）是帮助MySQL高效获取数据的数据结构。

如何高效的获取数据？

1、减少I/O操作
分块读取：我们应该尽可能给每一行数据分配一个自增的主键，这样数据在磁盘保存的时候存在连续性；而内存读取的时候会遵循局部性原理以及磁盘预读的规则；

局部性原理：CPU访问存储器时，无论是存取指令还是存取数据，所访问的存储单元都趋于聚集在一个较小的连续区域中
磁盘预读：磁盘在进行读取的时候，不只是单单读取需要的数据，而是从一个位置开始，读取之后的一定长度的数据放入内存

2、优化数据结构

此时就延伸出一个问题？Mysql为什么使用B+Tree来作为索引结构，而不是二叉树、红黑树或者BTree

评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度；

二叉树只有两个分支，在数据量足够大或者数据分布不均的情况下，节点深度甚至会趋近与线性，此时查找效率就会大大降低；
红黑树虽然优化了数据结果，但是在数据量够大的情况下，节点深度依旧不利于查询

bTree或者b+Tree利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入

但是bTree每一个节点中都存储着索引以及数据，而b+Tree只有叶子节点才会存储索引及数据，内节点只存储索引，这样一个节点能够存放更过的记录，同时b+Tree叶子节点相邻的数据存在双向链表可以实现快速访问。所以b+Tree的拥有更好的查询性能

尽量在InnoDB上采用自增字段做主键的原因？
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

索引失效的几种情况

	1、组合索引不遵循最左匹配原则
	2、组合索引的前面索引列使用范围查询(<,>,like),会导致后续的索引失效
	3、不要在索引上做任何操作（计算，函数，类型转换）
	4、is null和is not null无法使用索引
	5、尽量少使用or操作符，否则连接时索引会失效
	6、字符串不添加引号会导致索引失效
	7、两表关联使用的条件字段中字段的长度、编码不一致会导致索引失效
	8、like语句中，以%开头的模糊查询
	9、如果mysql中使用全表扫描比使用索引快，也会导致索引失效

---

锁（基于InnoDB引擎）

MySQL给开发者提供了查询存储引擎的功能

SHOW ENGINES

乐观锁

用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。
数据版本：即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

悲观锁

悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。

共享锁（S）

名词解释：共享锁又叫做读锁，所有的事务只能对其进行读操作不能写操作，加上共享锁后在事务结束之前其他事务只能再加共享锁，除此之外其他任何类型的锁都不能再加了。

排它锁（X）

名词解释：若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。

行锁

行锁又分共享锁和排他锁,由字面意思理解，就是给某一行加上锁，也就是一条记录加上锁。

注意：行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁

表锁

innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

死锁

所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。

解除正在死锁的状态有两种方法：

1.查询是否锁表
show OPEN TABLES where In_use > 0;
2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）
show processlist
3.杀死进程id（就是上面命令的id列）
kill id

1：查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
2：查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
3：查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 
4：杀死进程
kill 线程ID

产生死锁的四个必要条件：
（1） 互斥条件：一个资源每次只能被一个进程使用。
（2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
（3） 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
（4） 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

下列方法有助于最大限度地降低死锁：
（1）按同一顺序访问对象。
（2）避免事务中的用户交互。
（3）保持事务简短并在一个批处理中。
（4）使用低隔离级别。
（5）使用绑定连接。

---

Innodb引擎下mysql自身配置优化

1. 内存利用方面：innodb_buffer_pool_size（my.cnf文件）
   这个参数主要缓存innodb表的索引，数据，插入数据时的缓冲。
   该参数分配内存的原则：
   这个参数默认分配只有8M，可以说是非常小的一个值。
   如果是一个专用DB服务器，那么他可以占到内存的70%-80%。
2. innodb_additional_mem_pool_size：用来存放Innodb的内部目录，这个值不用分配太大，系统可以自动调。通常设置16Ｍ够用了，如果表比较多，可以适当的增大
3. innodb_log_file_size：指定在一个日志组中，每个log的大小
   注意：这个值分配的大小和数据库的写入速度，事务大小，异常重启后的恢复有很大的关系。一般取256M可以兼顾性能和recovery的速度。
   分配原则：几个日志成员大小加起来差不多和你的innodb_buffer_pool_size相等。上限为每个日志上限大小为4G.一般控制在几个Log文件相加大小在2G以内为佳。具体情况还需要看你的事务大小，数据大小为依据。
   说明：这个值分配的大小和数据库的写入速度，事务大小，异常重启后的恢复有很大的关系。
4. innodb_log_files_in_group
   作用：指定你有几个日值组。
   分配原则：　一般我们可以用2-3个日值组。默认为两个。
5. innodb_log_buffer_size：
   作用：事务在内存中的缓冲，也就是日志缓冲区的大小， 默认设置即可，具有大量事务的可以考虑设置为16M。
   如果这个值增长过快，可以适当的增加innodb_log_buffer_size
6. …

---

Mysql主从同步的实现原理

主从同步有什么好处？

• 水平扩展数据库的负载能力。
• 容错，高可用。Failover(失败切换)/High Availability
• 数据备份。

主从同步的原理是什么？

在master机器上，主从同步事件会被写到特殊的log文件中(binary-log);在slave机器上，slave读取主从同步事件，并根据读取的事件变化，在slave库上做相应的更改。

主从同步事件:

1. statement：会将对数据库操作的sql语句写入到binlog中。
2. row：会将每一条数据的变化写入到binlog中。
3. mixed：statement与row的混合。Mysql决定什么时候写statement格式的，什么时候写row格式的binlog。

---

应用：MySQL 对于千万级的大表要怎么优化？

1. 优化你的sql和索引
2. 加缓存，memcached,redis；
3. 以上都做了后，还是慢，就做主从复制或主主复制，读写分离，可以在应用层做，效率高
4. 如果以上都做了还是慢，mysql自带分区表，无需更改代码,但是sql语句是需要针对分区表做优化的，sql条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区
5. 如果以上都做了，那就先做垂直拆分，其实就是根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统
6. 水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key,为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表
   此时需要解决自增ID的问题：使用队列服务，如redis、memcacheq等等，将一定量的ID预分配在一个队列里，每次插入操作，先从队列中获取一个ID，若插入失败的话，将该ID再次添加到队列中，同时监控队列数量，当小于阀值时，自动向队列中添加元素。

myisam读的效果好，写的效率差，这和它数据存储格式，索引的指针和锁的策略有关的，它的数据是顺序存储的（innodb数据存储方式是聚簇索引），他的索引btree上的节点是一个指向数据物理位置的指针，所以查找起来很快，（innodb索引节点存的则是数据的主键，所以需要根据主键二次查找）；
myisam锁是表锁，只有读读之间是并发的，写写之间和读写之间（读和插入之间是可以并发的，去设置concurrent_insert参数，定期执行表优化操作，更新操作就没有办法了）是串行的，所以写起来慢，并且默认的写优先级比读优先级高，高到写操作来了后，可以马上插入到读操作前面去，如果批量写，会导致读请求饿死，所以要设置读写优先级或设置多少写操作后执行读操作的策略;myisam不要使用查询时间太长的sql，如果策略使用不当，也会导致写饿死，所以尽量去拆分查询效率低的sql,

innodb一般都是行锁，这个一般指的是sql用到索引的时候，行锁是加在索引上的，不是加在数据记录上的，如果sql没有用到索引，仍然会锁定表,mysql的读写之间是可以并发的，普通的select是不需要锁的，当查询的记录遇到锁时，用的是一致性的非锁定快照读，也就是根据数据库隔离级别策略，会去读被锁定行的快照，其它更新或加锁读语句用的是当前读，读取原始行；因为普通读与写不冲突，所以innodb不会出现读写饿死的情况，又因为在使用索引的时候用的是行锁，锁的粒度小，竞争相同锁的情况就少，就增加了并发处理，所以并发读写的效率还是很优秀的，问题在于索引查询后的根据主键的二次查找导致效率低；
innodb为了避免二次查找可以使用索引覆盖技术,避免了回表查询，提高了查询效率

例：mysql大数据量使用limit分页，随着页码的增大，查询效率越低下？

select * from product limit start, count
我们分别看下从10， 100， 1000， 10000开始分页的执行时间（每页取20条）
select * from product limit 10, 20  0.016秒
select * from product limit 100, 20  0.016秒
select * from product limit 1000, 20  0.047秒
select * from product limit 10000, 20  0.094秒
最后一页
select * from product limit 866613, 20  37.44秒

结论：
1）limit语句的查询时间与起始记录的位置成正比
2）mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用。
优化
利用表的覆盖索引来加速分页查询

id字段是主键，自然就包含了默认的主键索引

// 使用主键相对于查询了所有列的37.44秒，提升了大概100多倍的速度
select id from product limit 866613, 20 0.2秒
// 查询所有列 ，查询时间为0.2秒
SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20
// 另外一种写法
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id