MySQL高级部分理论知识细讲

文章目录

一、数据库分区、分表、分库、分片

1.1 单机数据库的瓶颈

• 单个表数据量越大，读写锁，插入操作重新建立索引效率越低。
• 单个库数据量太大（一个库数据量到1T - 2T就是极限）
• 单个数据库服务器压力过大
• 读写速度遇到瓶颈（并发量几百）

1.2 分区

数据库分区是一种物理数据库的设计技术，它的目的是为了在特定的 SQL操作中减少数据读写的总量以缩减响应是间。

分区并不是生成新的数据库表，而是将表的数据均匀分摊到不同的硬盘，系统或不同服务器存储介子中，实际上还是一张表。另外，分区可以做到将表的数据分摊到不同的地方，提高数据检索的效率，降低数据库频繁 IO压力值，分区的优点如下：

1. 相对于单个文件系统或硬盘，分区可以存储更多的数据。
2. 数据管理比较方便，如要清理或废弃某年的数据，就可以直接删除该日期的分区数据即可。
3. 精准定位分区查询数据，不需要全表扫描查询，大大提高检索效率。
4. 可跨多个分区磁盘查询，来提高查询的吞吐量。
5. 在涉及聚合函数时，很容易进行数据的合并。

1.2.1 什么时候考虑使用分区？

• 一张表的查询速度已经慢到影响使用的时候。
• sql经过优化
• 数据量大
• 表中的数据是分段的
• 对数据的操作往往只涉及一部分数据，而不是所有的数据

1.2.2 水平分区

这种形式分区是对表的行进行分区，通过这种的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分体或集体分割。所有在表中定义的列在每个数据集中都能找到，所以表的特性得以保持。

举例：一个包含十年发票记录的表可以被分区为10个不同的分区，每个分区包含的是其中一年的记录。

1.2.3 垂直分区

这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。

举例：一个包含了大 text和 blob列的表，这些 text和 blod列又不经常被访问，这时候就要把这些不经常使用的 text和 blob了划分到另一个分区，在保证它们数据关联性的同时还能提高访问速度。

1.2.4 分区实现的方式

mysql5开始支持分区功能

创建表：

create table sales(
	id int auto increment,
	amount double not null,
	order_day datetime not null,
	primary key(id,order_day)
) engine=Innodb

设置分区：

partition by range(year(order_day))(
	partition p_2010 values less than (2000),
	partition p_2011 values less than (2011),
	partition p_2012 values less than (2012),
	partition p_2012 values less than maxvalue
);

1.3 分表

1.3.1 什么时候考虑分表？

• 一张表的查询速度已经慢到影响使用的时候
• sql经过优化
• 数据量大
• 当频繁插入或者联合查询时，速度变慢

1.3.2 分表解决的问题

分表后，单表的并发能力提高了，磁盘的 IO性能也提供了，写操作效率也提高了。

• 查询一次的时间短了
• 数据分布在不同的文件，磁盘 I/O性能提高
• 读写锁影响的数据量变小
• 插入数据库需要重新建立索引的数据减少

1.3.3 分表实现方式

要业务系统配合迁移升级，工作量较大

常用分区分表的规则策略

• Range（范围）
• Hash（哈希）
• 按照时间拆分
• Hash之后按照分表个数取模
• 在认证库中保存数据库配置，就是建立一个 DB，这个 DB单独保存 user_id到 DB的映射关系

1.4 分库

1.4.1 什么时候考虑分库？

• 单台 DB的存储空间不够
• 随着查询量的增加单台数据库服务器已经没办法支撑

1.4.2 分库解决的问题

其主要目的是为突破单节点数据库服务器的 I/O 能力限制，解决数据库扩展性问题。

1.4.3 分库实现的方式

垂直拆分

把系统中不存在关联关系或者需要 join的表可以放在不同的数据库不同的服务器中。 按照业务垂直拆分。比如：可以按照业务分为资金、会员、订单三个数据库。

需要解决的问题：跨数据库的事务、 join查询等问题。

水平拆分

例如，大部分的站点。数据都是和用户有关，那么可以根据用户，将数据按照用户水平拆分。

按照规则拆分，一般水平库是在垂直分库之后的。比如每天处理的订单数量是海量的，可以按照一定的规则水平划分。

需要解决的问题：数据路由、组装。

读写分离

对于时效性不高的数据，可以通过读写分离缓解数据库压力。

需要解决的问题：在业务上区分哪些业务是允许一定时间延迟的，以及数据同步问题。

1.5 分区、分表、分库的对比

分区就是把一张表的数据分成N个区块，在逻辑上看最终只是一个表，但底层是由N个物理区块组成的。分表就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表名，然后操作它。分库一旦分表，一个数据库中的表会越来越多

优先级：垂直分库–>水平分库–>读写分离

1.6 拆分后面临的新问题

• 事务的支持，分库分表，就变成了分布式事务
• join时跨库，跨表的问题
• 分库分表，读写分离使用了分布式，分布式为了保证强一致性，必然带来延迟，导致性能降低，系统的负责度降低。

解决方案：

对于不同的方式之间没有严格的界限，特点不同，侧重点不同。需要根据实际情况，结合每种方式的特点来进行处理。选用第三方的数据库中间件（ Atlas， Mycat， TDDL， DRDS），同时业务系统需要配合数据存储的升级。

总结：优先考虑分区。当分区不能满足需求时，开始考虑分表，合理的分表对效率的提升会优于分区。

1.7 京东评论案例

现状

• 商品的评论数量：数十亿条
• 每天的服务调用：数十亿次
• 每年成倍增长

整体的数据存储：基础数据存储，文本存储

基础数据存储

MySQL：只存储非文本的基础信息。包括：评论状态，用户，时间等基础数据。以及图片，标签，点赞等附加信息。数据组织形式（不同的数据又可选择不同的库表拆分方案）：

• 评论基础数据按用户 ID进行拆库并拆表
• 图片及标签处于同一数据库下，根据商品编号分别进行拆表
• 其它的扩展信息数据，因数据量不大、访问量不高，处理于同一库下且不做分表即可

文本存储

文本存储（评论的内容）使用了 mongodb、 hbase

• 选择 nosql而非 mysql。
• 减轻了 mysql存储压力，释放 msyql，庞大的存储也有了可靠的保障。
• nosql的高性能读写大大提升了系统的吞吐量并降低了延迟。

1.8 数据分片

在分布式存储系统中，数据需要分散在多台设备上，数据分片（ Sharding）就是用来确定数据在多台存储设备上分布的技术，数据分片要达到三个目的：

1. 分布均匀，即每台设备上的数据量要尽可能相近
2. 负载均衡，即每台设备上的请求量要尽可能相近
3. 扩缩容时产生的数据迁移尽可能少

数据分片方法

• 划分号段
• 取模
• 检索表
• 一致性哈希算法（ Consistent Hashing）是在1997年由 MIT提出的一种分布式哈希 (DHT)实现算法，设计目标是为了解决因特网的热点(Hot Spot)问题。一致性哈希的算法简单而巧妙，很容易做到数据均分布，其单调性也保证了扩缩容的数据迁移是比较少的。

虚拟服务器

为了让系统有更好的扩展性，这里提出存储层 VServer（虚拟服务器）的概念，一个 VServer是一个逻辑上的存储服务器，是分布式存储系统的一个存储单元，一台物理设备上可以部署多个 VServer，一个 VServer支持一个写进程和多个读进程。

通过 VServer的方式，会有下面一些好处：

1. 提高单机性能。为了不引入复杂的锁机制，采用了单写进程的设计，如果单机只有一个写进程，写并发能力会受到限制，通过 VServer方式把单机上的存储资源（内存、硬盘）划分为多个存储单元，这样就支持多个写进程同时工作，大大提升单机写并发能力。
2. 部署扩展性更好。 VServer的方式在部署上非常灵活，可以根据单机的资源情况来确定 VServer的数量，针对不同的机型配置不同的 VServer数量，这样不同的机型都能充分利用机器上的资源，即使在一个系统中使用多种机型，也能做到机器的负载比较均衡。

二、事务的ACID和隔离级别

• 原子性(Atomic)：事务中各项操作，要么全做要么全不做，任何一项操作失败都会导致整个事务的失败
• 一致性(Consistent)：事务结束后系统的状态是一致的
• 隔离性(Isolated):并发操作的事务彼此看不到对方的中间状态
• 持久性(Durable)：事务完成后所做的改动都会被持久化

数据库事务并发导致的问题

• 解读： 事务 A读到了事务 B未提交的数据
• 可重复读：事务 A查询得到一行记录 row1，事务B提交修改后，事务 A第二次查询得到 row1,但列内容发生了改变，侧重于次数
• 幻读：事务 A第一次查询得到一行记录 row1,事务 B提交修改后，事务 A第二次查询得到两行记录 row1和 row2,侧重于 insert

MySQL数据库给我们提供的4种隔离级别

• 串行化(Serializable)：事务A多次从一张表中读取到相同的行，禁止其他事务对这张表进行CRUD操作
• 不可重复读(Repeatable read)：事务A可以读取到相同的值，禁止其他事务对字段进行更改
• 读已提交(Read committed)：事务A只能读取已提交的数据
• 读未提交：(Read uncomitted): 事务A可以读取到未提交的数据

脏读可重复读幻读串行化√√√不可重复读√√×读已提交√××读未提交×××

Oracle提供3种隔离级别

读已提交，串行化，只读模式：只读事务只能看到事务执行前就已经提交的数据，且事务中不能执行 insert、 update及 delete语句。

三、MySQL锁机制

3.1 锁的分类

• 从对数据操作的类型（读/写）分读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
• *对数据操作的粒度分

为了尽可能数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等操作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了"锁粒度（ Lock granularity）"的概率。

一种提高共享资源并发性的方式是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发度越高，只要相互之间不发生冲突即可。

• 表锁
• *行锁

3.2 表锁

特点：偏向 MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

案例1【加读锁】:

[session1]

lock table user read;

	这里只能执行查询当前表，不能查询其他表，插入或更新当前表都会提示错误

unlock tables;

[session2]

在session1锁定表后，session2能查询或更新未锁定的表，能查询锁定表，插入或者更新锁定表会一直等待锁被释放。

案例1【加写锁】:

[session1]

lock tables user write;

	这里可以对锁定表做查询、更新、插入操作

unlock tables;

[session2]

在session1锁定表后，查询、更新、插入操作均需要等到锁被释放。

结论：

1. 对 MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会堵塞同一表的写请求。只要当读锁释放后，才会执行其他进程的写操作。
2. 对 MyISAM表的写操作（加写锁），会阻塞其他进程的对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

查看哪些表被加锁： show open tables;

分析表锁定: show status like 'table%';

<br> Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1 ；<br> Table_locks_waited：出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在着较严重的表级锁争用情况；

Myisam的读写锁调度是读优先，这也是myisam不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞

3.3 行锁

特点：

1. 偏向 InnoDB存储引擎，开销大，加锁慢；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
2. InnoDB和 MyISAM的最大不同有两点：一是支持事务;二是采用了行级锁。

案例【加行锁】

[session1]

set autocommit=0;

	这里可以对锁定表做更新操作

commit;

[session2]

在session2锁定表后不commit时，这里对锁定表进行update操作，会等待锁释放。

无索引行锁升级为表锁

当某个索引列没有正常使用，如赋错误的类型的值，会导致行锁变表锁。

间隙锁危害

间隙锁：当我们用范围条件而不是相等条件检索数据，并请求共享或拍他锁时， InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但不存在的记录，叫做"间隙（ GAP）"， InnoDB也会对这个"间隙"进行加锁，这种锁机制就是所谓的间隙锁（ Next-Key）。

危害：当锁定一个范围键值之后，即使某些不存在的键值也会被无故的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据，在某些场景下这可能会对性能造成很大的危害。

【面试题】 如何锁定一行

select * from user for update;

结论：

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的的性能损耗可能比表级锁定会要更高一些，但是整体并发处理能力方面要远远优于 MyISAM的表级锁定。当系统并发量较高的时候， InnoDB的整体性能和 MyISAM相比就有比较明显的优势了。

但是 Innodb的行级锁定同样也有脆弱的一面，当我们使用不当的时候，可能会让 Innodb的整体性能表现不仅不能比 MyISAM高，甚至可能会更差。

分析行锁定：

通过检查 InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

命令： mysql> show status like 'innodb_row_lock%';

<br>
Innodb_row_lock_current_waits：当前正在等待锁定的数量；<br> Innodb_row_lock_time：从系统启动到现在锁定总时间长度；<br> Innodb_row_lock_time_avg：每次等待所花平均时间；<br> Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间；<br> Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
• 合理设计索引，尽量缩小锁的范围
• 尽可能较少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可能低级别事务隔离

3.4 页锁

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

四、MySQL实战问题

4.1 重复数据问题

select p1.Email from person p1 where p1.Email in (select p2.Email from person p2 where p1.Id!=p2.Id);

[优]SELECT email FROM `person` group by email HAVING count(email)>1;

[拓展]删除重复数据
[思路]根据重复数据进行分组,然后查出最小的id,删除其他之外的id行,这里得创建一个临时表,
在mysql中,不能在一条Sql语句中,即查询这些数据,同时修改这些数据
DELETE from person  where id not in( select temp.id from (SELECT min(id) id FROM person group by email)as temp);
注意：这里在mysql5.7以上版本会报错,因为不支持select那些group by和聚合函数之外的字段

4.2 索引创建和查看

创建： create index idx_a_b on table(col_a,col_b);

查看： show index from table;

4.3 where 1=1和where 1=0的意义

where 1=1用于拼接多条件语句时，这样就不用管条件是否存在，拼 where还是拼 and。

where1=0不返回数据，仅返回结构，用于快速建表。