Mysql

从转载http://svip.iocoder.cn/MySQL/Interview/

什么是mysql的分区表？

所有数据还在一个表中，但物理存储根据一定的规则放在不同的文件中。这个是mysql支持的功能，业务rd代码无需改动。

为什么大部分互联网还是更多的选择自己分库分表来水平扩展呢？

1）分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁

2）一旦数据量并发量上来，如果在分区表实施关联，就是一个灾难

3）自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控

4）运维的坑，嘿嘿

MySQL 索引的原理

在 MySQL 中，我们可以看到两种索引方式：
B-Tree 索引。
Hash 索引。
实际场景下，我们基本仅仅使用 B-Tree 索引。
什么是 B-Tree 索引？
B-Tree 是为磁盘等外存储设备设计的一种平衡查找树。因此在讲 B-Tree 之前先了解下磁盘的相关知识。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为 16 KB，可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K ，在 MySQL 中可通过如下命令查看页的大小：
mysql> show variables like ‘innodb_page_size’;
而系统一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB 。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘 I/O 次数，提高查询效率。
B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组 [key, data] ，key 为记录的键值，对应表中的主键值，data 为一行记录中除主键外的数据。对于不同的记录，key值互不相同。
一棵 m 阶的 B-Tree 有如下特性：
1、每个节点最多有 m 个孩子。
2、除了根节点和叶子节点外，其它每个节点至少有 Ceil(m/2) 个孩子。
若根节点不是叶子节点，则至少有 2 个孩子。
所有叶子节点都在同一层，且不包含其它关键字信息。
每个非叶子节点包含 n 个关键字信息（P0,P1,…Pn, k1,…kn）
3、关键字的个数 n 满足：ceil(m/2)-1 <= n <= m-1
ki(i=1,…n) 为关键字，且关键字升序排序。
Pi(i=0,…n) 为指向子树根节点的指针。P(i-1) 指向的子树的所有节点关键字均小于 ki ，但都大于 k(i-1) 。
B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个 3 阶的 B-Tree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的 key 和三个指向子树根节点的 point ，point 存储的是子节点所在磁盘块的地址。两个 key 划分成的三个范围域，对应三个 point 指向的子树的数据的范围域。
以根节点为例，key 为 17 和 35 ，P1 指针指向的子树的数据范围为小于 17 ，P2 指针指向的子树的数据范围为 [17~35] ，P3 指针指向的子树的数据范围为大于 35 。
模拟查找 key 为 29 的过程：
1、根据根节点找到磁盘块 1 ，读入内存。【磁盘I/O操作第1次】
2、比较 key 29 在区间（17,35），找到磁盘块 1 的指针 P2 。
3、根据 P2 指针找到磁盘块 3 ，读入内存。【磁盘I/O操作第2次】
4、比较 key 29 在区间（26,30），找到磁盘块3的指针P2。
5、根据 P2 指针找到磁盘块 8 ，读入内存。【磁盘I/O操作第3次】
6、在磁盘块 8 中的 key 列表中找到 eky 29 。
分析上面过程，发现需要 3 次磁盘 I/O 操作，和 3 次内存查找操作。由于内存中的 key 是一个有序表结构，可以利用二分法查找提高效率。而 3 次磁盘 I/O 操作是影响整个 B-Tree 查找效率的决定因素。B-Tree 相对于 AVLTree 缩减了节点个数，使每次磁盘 I/O 取到内存的数据都发挥了作用，从而提高了查询效率。

什么是 B+Tree 索引？

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用 B+Tree 实现其索引结构。
从上一节中的 B-Tree 结构图中可以看到，每个节点中不仅包含数据的 key 值，还有 data 值。而每一个页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率。在 B+Tree 中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+Tree 的高度。
B+Tree 相对于 B-Tree 有几点不同：
非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。
将上一节中的 B-Tree 优化，由于 B+Tree 的非叶子节点只存储键值信息，假设每个磁盘块能存储 4 个键值及指针信息，则变成 B+Tree 后其结构如下图所示：

通常在 B+Tree 上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

索引有什么好处

提高数据的检索速度，降低数据库IO成本：使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
降低数据排序的成本，降低CPU消耗：索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则正好降低了排序的成本。

索引有什么坏处

占用存储空间：索引实际上也是一张表，记录了主键与索引字段，一般以索引文件的形式存储在磁盘上。
降低更新表的速度：表的数据发生了变化，对应的索引也需要一起变更，从而减低的更新速度。否则索引指向的物理数据可能不对，这也是索引失效的原因之一。

索引的类型

索引，都是实现在存储引擎层的。主要有六种类型：
1、普通索引：最基本的索引，没有任何约束。
2、唯一索引：与普通索引类似，但具有唯一性约束。
3、主键索引：特殊的唯一索引，不允许有空值。
4、复合索引：将多个列组合在一起创建索引，可以覆盖多个列。
5、外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作。
6、全文索引：MySQL 自带的全文索引只能用于 InnoDB、MyISAM ，并且只能对英文进行全文检索，一般使用全文索引引擎。

MySQL 索引的“创建”原则

1、最适合索引的列是出现在 WHERE 子句中的列，或连接子句中的列，而不是出现在 SELECT 关键字后的列。
2、索引列的基数越大，索引效果越好。
3、根据情况创建复合索引，复合索引可以提高查询效率。因为复合索引的基数会更大。
4、避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。
5、主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率。
6、对字符串进行索引，应该定制一个前缀长度，可以节省大量的索引空间。

MySQL 索引的“使用”注意事项

1、避免在 WHERE 子句中使用 != 或 <> 操作符;
2、避免在 WHERE 子句中使用 OR 来连接条件;
3、避免在 WHERE 子句中对字段进行表达式操作;
4、避免在 WHERE 子句中对字段进行函数操作;
5、不要在 WHERE 子句中的 = 左边进行函数、算术运算或其他表达式运算;
以上会导致引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。
注意，column IS NULL 也是不可以使用索引的。
6、复合索引遵循前缀原则。
7、如果 MySQL 评估使用索引比全表扫描更慢，会放弃使用索引。如果此时想要索引，可以在语句中添加强制索引。
8、列类型是字符串类型，查询时一定要给值加引号，否则索引失效。
9、LIKE 查询，% 不能在前，因为无法使用索引。如果需要模糊匹配，可以使用全文索引。

Mysql的事务是什么

事务就是对一系列的数据库操作（比如插入多条数据）进行统一的提交或回滚操作，如果插入成功，那么一起成功，如果中间有一条出现异常，那么回滚之前的所有操作。
这样可以防止出现脏数据，防止数据库数据出现问题。

事务的特性是指

指的是 ACID
原子性 Atomicity ：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
一致性 Consistency ：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器)、级联回滚等。
隔离性 Isolation ：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性 Durability ：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务的并发问题

实际场景下，事务并不是串行的，所以会带来如下三个问题：
1、脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。
3、幻读：系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

MySQL 的四种事务隔离级别

事务定义了四种事务隔离级别，不同数据库在实现时，产生的并发问题是不同的。
不同的隔离级别有不同的现象，并有不同的锁定/并发机制，隔离级别越高，数据库的并发性就越差。
READ UNCOMMITTED（未提交读）：事务中的修改，即使没有提交，对其他事务也都是可见的,会导致脏读。
READ COMMITTED（提交读）：事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的,会导致不可重复读.
这个隔离级别，也可以叫做“不可重复读”。
REPEATABLE READ（可重复读）：一个事务按相同的查询条件读取以前检索过的数据，其他事务插入了满足其查询条件的新数据。产生幻行。会导致幻读。
SERIALIZABLE（可串行化）：强制事务串行执行。

什么是 MVCC ？

多版本并发控制（MVCC），是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。

mysql查询优化

添加链接描述

聚集索引和非聚集索引

添加链接描述
聚集索引的顺序就是数据的物理存储顺序;
非聚集索引的顺序和数据物理排列无关。
因为数据在物理存放时只能有一种排列方式，所以一个表只能有一个聚集索引。
聚集索引表记录的排列顺序与索引的排列顺序一致
优点是查询速度快，因为一旦具有第一个索引值的纪录被找到，具有连续索引值的记录也一定物理的紧跟其后。
缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排， 降低了执行速度。建议使用聚集索引的场合为：
a. 此列包含有限数目的不同值；
b. 查询的结果返回一个区间的值；
c. 查询的结果返回某值相同的大量结果集。
非聚集索引指定了表中记录的逻辑顺序，但记录的物理顺序和索引的顺序不一致，聚集索引和非聚集索引都采用了B+树的结构，但非聚集索引的叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。
非聚集索引比聚集索引层次多，添加记录不会引起数据顺序的重组。
建议使用非聚集索引的场合为：
a. 此列包含了大量数目不同的值；
b. 查询的结束返回的是少量的结果集；
c. order by 子句中使用了该列。
在SQL SERVER中，索引是通过二叉树的数据结构来描述的；我们可以如此理解这个两种索引：聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍然是索引节点，只不过其包含一个指向对应数据块的指针。

mysql主从同步