Mysql

mysql acid特性详细分析：

https://www.cnblogs.com/kismetv/p/10331633.html

你们先上线上怎么分库分表的？

库：取模

表：整除后在取模

32*32 =1024张表  4台服务器 每台8个库

线上预估能存50个亿的数据 目前存了20亿。。

如果想提高写并发 可以扩容机器，，将每台机器只放一个库即可。。。

 mysql主从复制原理：

1、主库和从库各有一个io线程来通信做主从同步

2、主库io线程从binlog中不断读取操作日志，然后传给从库

3、从库io线程接收到数据后写入relaylog，这个relaylog是先写oscache，然后定时刷盘，但是可以忽略

因为 sql线程读取relaylog并且写入磁盘是单线程的，所以可能会有延迟。。

之前做过压测，大概是主库并发达到1000时，从库会有2ms的延迟。

semi-sync 半同步复制开启防止数据丢失：（因为mysql默认是写入binglog后就返回成功，假设主机宕机，从机就可能没有同步到一些数据）

主要步骤：开启后，主机写入bingLog后，从机io线程将日志写入relaylog后才返回给客户端成功，这样就能一定程度保证数据一致。

mysql5.7并行重放（sql线程变成多线程，提高效率，缓解主从延迟问题）

多个线程来重放relay中的日志到磁盘中，但是每个库就是一个线程。确保不会乱序。。。

解决主从复制延迟问题：

show status命令 查看Seconds_Behind_Masster参数，可以看到落后几毫秒

比如如下场景：先插入数据，在查询数据，再修改数据

1、分库，将一个主库拆分几个主库，每个主库并发就会下去，然后主从延迟就会减少

2、打开mysql支持的并行复制，多个库并行复制，但是有可能某个库写入就是非常频繁，治标不治本

3、重写代码  先插入，然后直接更新

4、如果确认需要先插入，后查询，那么可以强制将这个查询直连主库去查询，不推荐这种方法。。

隔离性

1、读未提交

         一个事务可以读到其他事务还没有提交的数据，会出现脏读。

2、读已提交（READ COMMITTED）

一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，会出现不可重复读、幻读。

3、可重复读（REPEATABLE READ）

一个事务第一次读过某条记录后，即使其他事务修改了该记录的值并且提交，该事务之后再读该条记录时，读到的仍是第一次读到的值，而不是每次都读到不同的数据，这就是可重复读，这种隔离级别解决了不可重复，但是还是会出现幻读。

4、串行化（SERIALIZABLE）

以上3种隔离级别都允许对同一条记录同时进行读-读、读-写、写-读的并发操作，如果我们不允许读-写、写-读的并发操作，可以使用SERIALIZABLE隔离级别，这种隔离因为对同一条记录的操作都是串行的，所以不会出现脏读、幻读等现象。

MySQL在REPEATABLE READ隔离级别下，是可以禁止幻读问题的发生的。

MVCC(多版本并发控制)

MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SEELCT操作时访问记录的版本链的过程。可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。READ COMMITTD、REPEATABLE READ这两个隔离级别的一个很大不同就是：生成ReadView的时机不同，READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了。

ReadView

对于使用READ UNCOMMITTED隔离级别的事务来说，直接读取记录的最新版本就好了，对于使用SERIALIZABLE隔离级别的事务来说，使用加锁的方式来访问记录。对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，就需要用到我们上边所说的版本链了，核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。

ReadView中主要包含4个比较重要的内容：

1. m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
2. min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
3. max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。
4. creator_trx_id：表示生成该ReadView的事务的事务id。

注意max_trx_id并不是m_ids中的最大值，事务id是递增分配的。比方说现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，m_ids就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4

访问原则（判断被访问记录是否可以被当前读事务访问）：

• 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。

• 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
• 如果被访问版本的trx_id属性值大于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。

• 如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

读锁与写锁

• 读锁：共享锁、Shared Locks、S锁。
• 写锁：排他锁、Exclusive Locks、X锁。
• select :不加锁

读操作

1、对于普通 SELECT 语句，InnoDB 不会加任何锁

2、select ... lock in share mode

将查找到的数据加上一个S锁，允许其他事务继续获取这些记录的S锁，不能获取这些记录的X锁（会阻塞）

使用场景：读出数据后，其他事务不能修改，但是自己也不一定能修改，因为其他事务也可以使用select ... lock in share mode 继续加读锁。

3、select ... for update

将查找到的数据加上一个X锁，不允许其他事务获取这些记录的S锁和X锁。

使用场景：读出数据后，其他事务即不能写，也不能加读锁，那么就导致只有自己可以修改数据。

写操作

• DELETE：删除一条数据时，先对记录加X锁，再执行删除操作。
• INSERT：插入一条记录时，会先加隐式锁来保护这条新插入的记录在本事务提交前不被别的事务访问到。
• UPDATE

• 如果被更新的列，修改前后没有导致存储空间变化，那么会先给记录加X锁，再直接对记录进行修改。
• 如果被更新的列，修改前后导致存储空间发生了变化，那么会先给记录加X锁，然后将记录删掉，再Insert一条新记录。

隐式锁：一个事务插入一条记录后，还未提交，这条记录会保存本次事务id，而其他事务如果想来对这个记录加锁时会发现事务id不对应，这时会产生X锁，所以相当于在插入一条记录时，隐式的给这条记录加了一把隐式X锁。

行锁

• LOCK_REC_NOT_GAP：单个行记录上的锁。
• LOCK_GAP：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
• LOCK_ORDINARY：锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

解决幻读问题：就是使用的间隙锁和行锁

表级别的S锁、X锁

尽量不用这两种方式去加锁，因为InnoDB的优点就是行锁，所以尽量使用行锁，性能更高。

悲观锁

悲观锁用的就是数据库的行锁，认为数据库会发生并发冲突，直接上来就把数据锁住，其他事务不能修改，直至提交了当前事务。

乐观锁

乐观锁其实是一种思想，认为不会锁定的情况下去更新数据，如果发现不对劲，才不更新(回滚)。在数据库中往往添加一个version字段来实现。

避免死锁（一般在一个事务里有两个行锁）

• 以固定的顺序访问表和行，比如对两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形
• 大事务拆小，大事务更容易产生死锁
• 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率
• 降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁
• 为表添加合理的索引，如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大

mysql行格式  

1、Compact

 

2、Redundant

3、Dynamic

4、Compressed

记录的额外信息

这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息，这些额外信息分为3类，分别是：

• 变长字段长度列表
• NULL值列表
• 记录头信息

变长字段长度列表

MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、TEXT类型，BLOB类型，这些数据类型修饰列称为变长字段，变长字段中存储多少字节的数据不是固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。

CHAR是一种固定长度的类型，VARCHAR则是一种可变长度的类型。

VARCHAR(M)，M代表最大能存多少个字符。( MySQL5.0.3以前是字节，以后就是字符)

NULL值列表

Compact行格式会把可以为NULL的列统一管理起来，存一个标记为在NULL值列表中，如果表中没有允许存储 NULL 的列，则 NULL值列表也不存在了。

• 二进制位的值为1时，代表该列的值为NULL。
• 二进制位的值为0时，代表该列的值不为NULL。

记录头信息

除了变长字段长度列表、NULL值列表之外，还有一个用于描述记录的记录头信息，它是由固定的5个字节组成。5个字节也就是40个二进制位，不同的位代表不同的意思，如图：

 

行溢出数据

VARCHAR(M)类型的列最多可以占用65535个字节。其中的M代表该类型最多存储的字符数量，如果我们使用ascii字符集的话，一个字符就代表一个字节

记录中的数据太多产生的溢出

一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65533个字节，这样就可能出现一个页存放不了一条记录。

在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页。

Dynamic和Compressed行格式

这两种行格式类似于COMPACT行格式，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储一部分数据，而是把所有的数据都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。另外，Compressed行格式会采用压缩算法对页面进行压缩。

聚簇索引

聚簇索引的特点：

1. 按主键值的大小进行记录和页的排序：

• 数据页(叶子节点)里的记录是按照主键值从小到大排序的一个单向链表。
• 数据页(叶子节点)之间也是是按照主键值从小到大排序的一个双向链表。
• B+树中同一个层的页目录也是按照主键值从小到大排序的一个双向链表。

1. B+树的叶子节点存储的是完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。
   具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建。InnoDB存储引擎会自动的为我们创建聚簇索引。在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

二级索引（复制索引）

聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。当我们想以别的列作为搜索条件时我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。

二级索引与聚簇索引有几处不同：

1. 按指定的索引列的值来进行排序
2. 叶子节点存储的不是完整的用户记录，而只是索引列+主键。
3. 目录项记录中不是主键+页号，变成了索引列+页号。
4. 在对二级索引进行查找数据时，需要根据主键值去聚簇索引中再查找一遍完整的用户记录，这个过程叫做回表

联合索引

以多个列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。

查询优化器

（1）、基于成本的优化步骤

在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询。

下边我们就以一个实例来分析一下这些步骤，单表查询语句如下：

select * from employees.titles where emp_no > '10101' and emp_no < '20000' and to_date = '1991-10-10';

（2）. 计算全表扫描的代价

对于InnoDB存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集，所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。由于查询成本=I/O成本+CPU成本，所以计算全表扫描的代价需要两个信息：

1. 聚簇索引占用的页面数
2. 该表中的记录数

2、连接的原理（JOIN 优化）

不管是内连接还是左右连接，都需要一个驱动表和一个被驱动表，对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，也就是说左连接的驱动表就是左边的那个表，右连接的驱动表就是右边的那个表。

连接的大致原理是：

1. 选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的访问形式来执行对驱动表的单表查询。
2. 对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。
   对应伪代码就是：

SQL优化的具体操作：

1、在表中建立索引，优先考虑where、group by使用到的字段。

2、尽量避免使用select *，返回无用的字段会降低查询效率。如下：

SELECT * FROM t 

优化方式：使用具体的字段代替*，只返回使用到的字段。

3、尽量避免使用in 和not in，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id IN (2,3)

SELECT * FROM t1 WHERE username IN (SELECT username FROM t2)

优化方式：如果是连续数值，可以用between代替。如下：

SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查询，可以用exists代替。如下：

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)

4、尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id = 1 OR id = 3

优化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3

（PS：如果or两边的字段是同一个，如例子中这样。貌似两种方式效率差不多，即使union扫描的是索引，or扫描的是全表）

5、尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE username LIKE '%li%'

优化方式：尽量在字段后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE 'li%'

6、尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t WHERE score = 0

7、尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t2 WHERE score/10 = 9

SELECT * FROM t2 WHERE SUBSTR(username,1,2) = 'li'

优化方式：可以将表达式、函数操作移动到等号右侧。如下：

SELECT * FROM t2 WHERE score = 10*9

SELECT * FROM t2 WHERE username LIKE 'li%'

8、当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE 1=1

优化方式：用代码拼装sql时进行判断，没where加where，有where加and。