MySQL基础知识

目录

MySQL的复制原理以及流程

主线程binlog

从线程io

sql执行线程(从)

mysql主从复制详解

Mysql主从复制的实现原理图大致如下

Mysql中的myisam与innodb的区别

InnoDB引擎的四大特性

InooDB和MyISAM的select count（*）哪个更快

MySQL事务的4种隔离级别

读未提交产生脏读问题：

读已提交

可重复读

可串行化

B+树索引和哈希索引的区别

哈希索引的优势：

哈希索引不适用的场景：

B树和B+树的区别

MySQL联合索引

MySQL分区

什么是表分区

表分区与分表的区别

表分区有什么好处？

分区表的限制因素

Mysql支持的分区类型有哪些

行级锁定的优点

行级锁定的缺点

Mysql优化

---

MySQL的复制原理以及流程

基本原理流程，3个线程以及之间的关联；

• 主线程binlog

记录下所有改变了数据库数据的语句，放进master上的binlog中；

• 从线程io

在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；

• sql执行线程(从)

执行relay log中的语句；

mysql主从复制详解

MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。

下图就描述了一个多个数据库间主从复制与读写分离的模型(来源网络)：

在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行。如果配置了多个从服务器或者多个主服务器又涉及到相应的负载均衡问题。

Mysql主从复制的实现原理图大致如下

MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。 

Mysql中的myisam与innodb的区别

1. InooDB支持事务，而MyISAM不支持事务
2. InnoDB支持行级锁，而MyISAM支持表级锁
3. InnoDB支持MVCC，而MyISAM不支持
4. InnoDB支持外键，而MyISAM不支持
5. InnoDB不支持全文索引，而MyISAM支持

InnoDB引擎的四大特性

插入缓冲，二次写，自适应哈希索引，预读

InooDB和MyISAM的select count（*）哪个更快

myisam更快，因为myisam内部维护了一个计算器，可以直接调取。MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

MySQL事务的4种隔离级别

1. 读未提交
2. 读已提交
3. 可重复读
4. 串行化

读未提交产生脏读问题：

#首先，修改隔离级别

set tx_isolation='READ-UNCOMMITTED';

select @@tx_isolation;

+------------------+

| @@tx_isolation |

+------------------+

| READ-UNCOMMITTED |

+------------------+

#事务A：启动一个事务

start transaction;

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

+------+------+

#事务B：也启动一个事务(那么两个事务交叉了)

在事务B中执行更新语句，且不提交

start transaction;

update tx set num=10 where id=1;

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 10 |

| 2 | 2 |

| 3 | 3 |

+------+------+

#事务A：那么这时候事务A能看到这个更新了的数据吗?

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 10 | --->可以看到！说明我们读到了事务B还没有提交的数据

| 2 | 2 |

| 3 | 3 |

+------+------+

#事务B：事务B回滚,仍然未提交

rollback;

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

+------+------+

#事务A：在事务A里面看到的也是B没有提交的数据

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 1 | --->脏读意味着我在这个事务中(A中)，事务B虽然没有提交，但它任何一条数据变化，我都可以看到！

| 2 | 2 |

| 3 | 3 |

+------+------+

读已提交

这种隔离级别出现的问题是——不可重复读(Nonrepeatable Read)：不可重复读意味着我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果。

1. 这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）
2. 它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变

#首先修改隔离级别

set tx_isolation='read-committed';

select @@tx_isolation;

+----------------+

| @@tx_isolation |

+----------------+

| READ-COMMITTED |

+----------------+

#事务A：启动一个事务

start transaction;

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

+------+------+

#事务B：也启动一个事务(那么两个事务交叉了)

在这事务中更新数据，且未提交

start transaction;

update tx set num=10 where id=1;

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 10 |

| 2 | 2 |

| 3 | 3 |

+------+------+

#事务A：这个时候我们在事务A中能看到数据的变化吗?

select * from tx; --------------->

+------+------+ |

| id | num | |

+------+------+ |

| 1 | 1 |--->并不能看到！

| 2 | 2 | |

| 3 | 3 | |

+------+------+ |——>相同的select语句，结果却不一样



#事务B：如果提交了事务B呢? 

commit; 



#事务A: |

select * from tx; --------------->

+------+------+

| id | num |

+------+------+

| 1 | 10 |--->因为事务B已经提交了，所以在A中我们看到了数据变化

| 2 | 2 |

| 3 | 3 |

+------+------+

可重复读

1. 这是MySQL的默认事务隔离级别
2. 它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行
3. 此级别可能出现的问题——幻读(Phantom Read)：当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行

#首先，更改隔离级别

set tx_isolation='repeatable-read';

select @@tx_isolation;

+-----------------+

| @@tx_isolation |

+-----------------+

| REPEATABLE-READ |

+-----------------+

#事务A：启动一个事务

start transaction;

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

+------+------+

#事务B：开启一个新事务(那么这两个事务交叉了)在事务B中更新数据，并提交

start transaction;

update tx set num=10 where id=1;

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 10 |

| 2 | 2 |

| 3 | 3 |

+------+------+

commit;

#事务A：这时候即使事务B已经提交了,但A能不能看到数据变化？

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 1 | --->还是看不到的！(这个级别2不一样，也说明级别3解决了不可重复读问题)

| 2 | 2 |

| 3 | 3 |

+------+------+

#事务A：只有当事务A也提交了，它才能够看到数据变化

commit;

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 10 |

| 2 | 2 |

| 3 | 3 |

+------+------+

可串行化

1. 这是最高的隔离级别
2. 它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。
3. 在这个级别，可能导致大量的超时现象和锁竞争

#首先修改隔离界别

set tx_isolation='serializable';

select @@tx_isolation;

+----------------+

| @@tx_isolation |

+----------------+

| SERIALIZABLE |

+----------------+

#事务A：开启一个新事务

start transaction;

#事务B：在A没有commit之前，这个交叉事务是不能更改数据的

start transaction;

insert tx values('4','4');

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

update tx set num=10 where id=1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

B+树索引和哈希索引的区别

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且叶子节点的指针相互链接，是有序的

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从跟节点到叶子节点逐级查找，只需要 一次哈希算法即可，是无序的

哈希索引的优势：

1. 等值查询。哈希索引具有绝对优势（前提是：没有大量重复键值，如果大量重复键值时，哈希索引的效率很低，因为存在所谓的哈希碰撞问题。）

哈希索引不适用的场景：

1. 不支持范围查询
2. 不支持索引完成排序
3. 不支持联合索引的最左前缀匹配规则

通常，B+树索引结构适用于绝大多数场景，像下面这种场景用哈希索引才更有优势：

在HEAP表中，如果存储的数据重复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引，例如这种SQL：

select id,name from table where name='李明'; — 仅等值查询

而常用的InnoDB引擎中默认使用的是B+树索引，它会实时监控表上索引的使用情况，如果认为建立哈希索引可以提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引（在InnoDB中默认开启自适应哈希索引），通过观察搜索模式，MySQL会利用index key的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。

注意：在某些工作负载下，通过哈希索引查找带来的性能提升远大于额外的监控索引搜索情况和保持这个哈希表结构所带来的开销。但某些时候，在负载高的情况下，自适应哈希索引中添加的read/write锁也会带来竞争，比如高并发的join操作。like操作和%的通配符操作也不适用于自适应哈希索引，可能要关闭自适应哈希索引。

B树和B+树的区别

1、B树，每个节点都存储key和data，所有的节点组成这可树，并且叶子节点指针为null，叶子节点不包含任何关键字信息

2、B+树，所有的叶子节点中包含全部关键字的信息，及指向含有这些关键字记录的指针，且叶子节点本身依关键字的大小自小到大的顺序链接，所有的非终端节点可以看成是索引部分，节点中仅含有其子树根节点中最大（或最小）关键字

MySQL联合索引

1. 联合索引是两个或更多个列上的索引。对于联合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a 、 a,b 、 a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。
2. 利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知 道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

MySQL分区

什么是表分区

表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

表分区与分表的区别

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。

分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

表分区有什么好处？

1. 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。 2. 和单个磁盘或者文件系统相比，可以存储更多数据
2. 优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。
3. 分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。
4. 可与使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问，ext3问价你系统的inode锁竞争等。

分区表的限制因素

1. 一个表最多只能有1024个分区
2. MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。
3. 如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。
4. 分区表中无法使用外键约束
5. MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

Mysql支持的分区类型有哪些

1. RANGE分区：这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区
2. List分区：这种模式允许系统通过预定义的列表的值来对数据进行分割。按照list中的值分区，与RANGE的区别是，range分区的区间范围值是连续的
3. HASH分区：这种模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表
4. KEY分区：上面Hash模式的一种延伸，这里的Hash Key是Mysql系统产生的

行级锁定的优点

1. 当在许多线程中访问不同的行时只存在少量锁定冲突
2. 回滚时只有少量的更改
3. 可以长时间锁定单一的行

行级锁定的缺点

1. 比页级或表级锁定占用更多的内存
2. 当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁
3. 如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多
4. 用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定

Mysql优化

1. 开启查询缓存，优化查询
2. explain你的select查询，这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序
3. 当只要一行数据时使用limit 1，Mysql数据库引擎会在找到一条数据后停止搜索，而不是继续往后查找下一条符合记录的数据
4. 为搜索字段建索引
5. 使用ENUM而不是VARCHAR，如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用ENUM而不是VARCHAR
6. Prepared Statement Prepared Statements很像存储过程，是一种运行在后台的sql语句集合，我们可以从使用prepared statement获得很多好处，无论是性能问题还是安全问题。Prepared Statements可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL注入式”攻击
7. 垂直分表
8. 选择正确的存储引擎