MySQL 学习笔记

DDL 数据定义语言

用来定义数据库对象（数据库、表、字段）

数据库操作

# 查询所有数据库
SHOW DATABASES;
# 查询当前数据库
SELECT DATABASE();
#创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
# 删除
DROP DATABASE [IF EXISTS] 数据库名;
# 使用
USE 数据库名;

表操作

# 查询
# 查询当前数据库所有表
SHOW TABLES;
# 查询表结构
DESC 表名;
# 查询指定表的建表语句
SHOW CREATE TABLE 表名;

# 创建
CREATE TABLE 表名(
    字段1 字段1类型[COMMENT '字段1注释'],
    字段2 字段2类型[COMMENT '字段2注释'],
    ...
    字段3 字段3类型[COMMENT '字段3注释'],
)[COMMENT '表注释']

# 修改
# 添加字段
ALTER TABLE 表名 ADD 字段名 类型（长度） [COMMENT '注释'] [约束];
# 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）;
# 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度） [COMMENT '注释'] [约束];
# 删除
ALTER TABLE 表名 DROP 字段名;
# 修改表名
ALTER TABLE 表名 RENAME TO 新表名;

# 删除
# 删除表
DROP TABLE [IF EXISTS] 表名;
# 删除指定表，并重新创建该表
TRUNCATE TABLE 表名;

数据库数据类型主要分为三种，数值、字符串、日期时间

DML 数据操作语言

用来对数据库表中数据进行增删改

# 添加数据
# 1、给指定字段添加数据
INSERT INTO 表名(字段名1,字段名2,...) VALUES (值1,值2,...);
# 2、给全部字段添加数据
INSERT INTO 表名 VALUES(值1,值2,...);
# 3、批量添加数据
INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,...)(值1,...)(值1,...);
INSERT INTO 表名 VALUES (...)(...)(...);

# 修改数据
UPDATE 表名 SET 字段名1=值1,字段名2=值2,...[WHERE 条件];

# 删除数据
DELETE FROM 表名 [WHERE 条件];

DQL 数据查询语言

用来查询数据库中表的记录

# 语法
SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数;
# 基本查询
# 1、查询多个字段
SELECT 字段1,字段2,... FROM 表名;
SELECT * FROM 表名;
# 2、设置别名
SELECT 字段1 [AS 别名1],... FROM 表名;
# 3、去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

# 条件查询
# 范围之内BETWEEN...AND... 列表中的值IN(...) 占位符，模糊匹配LIKE 判断空IS NULL 并且AND && 或者OR || 非NOT !

# 聚合函数
# 统计COUNT 最大MAX 最小MIN 平均AVG 求和SUM

# 分组查询
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

# 排序查询
SELECT 字段列表 FROM 表名 ORDER BY [字段1,排序方式1,...];

# 分页查询
SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数;

# 执行顺序
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

DCL 数据控制语言

用来创建数据库用户，控制数据库的访问权限

# 管理用户
# 1、查询用户
USE mysql;
SELECT * FROM user;
# 2、创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
# 3、修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
# 4、删除用户
DROP USER '用户名'@'主机名';

# 权限控制
# 1、查询权限
SHOW GRANTS FOR '用户名'@'主机名';
# 2、授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
# 3、撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

函数

字符串函数

CONCAT(s1,s2,...sn)
LOWER(str)
UPPER(str)
LPAD(str,n,pad)
RPAD(str,n,pad)
TRIM(str)
SUBSTRING(str,start,len)

数值函数

CEIL(X)
FLOOR(X)
MOD(X)
RAND(X)
ROUND(X)

日期函数

CURDATE()
CURTIME()
NOW()
YEAR(date)
MONTH(date)
DAY(dat)
DATE_ADD(date,interval expr type)
DATEDIFF(date1,date2)

流程函数

IF(value,t,f)
IFNULL(value1,value2)
CASE WHEN [val1] THEN [res1] ... ELSE [default] END
CASE [expr] WHEN [val1] THEN[res1] ...ELSE [default] END

约束

约束是作用于表中字段上的规则，用于限制存储在表中的数据。

非空约束 NOT NULL

唯一约束 UNIQUE

主键约束 PRIMARY KEY/(自增：AUTO_INCREMENT)

默认约束 DEFAULT

检查约束 CHECK

外键约束 FOREIGN KEY

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

# 添加外键
CREATE TABLE(
    字段名 数据类型
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表类名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);

# 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

#删除/更新行为
# NO ACTION/RESTRICT 有对应外键时不允许
# CASCADE 有外键则删除/更新子表中记录
# SET NULL 设置为NULL
# SET DEFAULT 设置为默认值
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

多表查询

一对多 在多的表中建立外键指向"一"的主键。

多对多 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

一对一 多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中。在任意一方加入外键，关联另一方的主键，并且设置外键为唯一（UNIQUE)。

SELECT 字段列表 FROM 表名 WHERE 表名.主键 = 表名.外键
# 连接查询：内连接（交集）、外连接（左外连接、右外连接）、自连接
# 隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
# 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件;

# 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
# 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

# 自连接
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件;

# 联合查询-union,union all
SELECT 字段列表 FROM 表A...
UNION [ALL]
SELECT 字段列表 FROM 表B...;
-- 联合查询的多表列数需保持一致，字段类型保持一致。union all会将全部数据直接合并，union会在合并后去重。

# 子查询（嵌套查询）
# 标量子查询、列子查询、行子查询、表子查询
SELECT 字段列表 FROM 表1 WHERE 字段 = (SELECT 字段列表 FROM 表2);

事务

是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

# 开启事务
START TRANSACTION / BEGIN;
# 提交事务
COMMIT;
# 回滚事务
ROLLBACK;

事务四大特性ACID

原子性：事务是不可分割的最小操作单元。

一致性：事务完成时，必须使所有数据都保持一致。

隔离性：保证事务在不受外部并发操作影响的独立环境运行。

持久性：事务一旦提交或回滚，它对数据库中的数据的改变是永久的。

# 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
#  设置事务隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE}

MySQL体系结构

连接层 最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证及相关的安全方案。服务器也会为安全接入的客户端验证它所具有的操作权限。

服务层 第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现。

引擎层 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。

存储层 主要是将数据存储在文件系统上，并完成于存储引擎的交互。

存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也被称为表类型。

# 1、创建表时指定存储引擎
CREATE TABLE 表名(
    ...
)ENGINE = INNODB [COMMENT 注释];
# 2、查看当前数据库支持的存储引擎
SHOW ENGINES;

InnoDB 支持事务、行级锁、外键，innoDB引擎的每张表都会对应表空间文件xxx.idb，存储表结构（frm、sdi）、数据和索引

MyISAM 如果应用以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求没有那么高，那么很适合这个引擎。

MEMORY 将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。缺陷是对表的大小有限制，而且无法保障数据的安全性。

索引

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。常见索引结构B+Tree、Hash、R-tree（空间索引）、Full-text（全文索引）。

二叉树缺点：顺序插入时退化成链表，查询效率为O(N)。-> 红黑树缺点：大数据量情况下，层级较深，检索速度慢。

B-Tree(多路平衡查找树)

B+Tree：所有数据都会出现在叶子节点，叶子节点形成单向链表。

聚集索引选取规则：

• 如果存在主键，主键就是聚集索引。
• 如果不存在主键，使用第一个唯一 UNIQUE 索引作为聚集索引。
• 如果没有主键和唯一索引，InnoDB会自动生成一个rowid作为隐藏的聚集索引。

回表查询指的是先二级索引查到对应主键值，再回到聚集索引查到行数据。

# 创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...);
# 查看索引
SHOW INDEX FROM table_name;
# 删除索引
DROP INDEX index_name ON table_name;

SQL性能分析

SQL执行频率

# 查询服务器状态信息
SHOW [SESSION|GLOBAL] STATUS;
# 访问频率
SHOW [SESSION|GLOBAL] STATUS LIKE 'COM_______';

慢查询日志记录了所有执行时间超过指定参数(long_query_time)的所有SQL语句的日志。

# 查看慢日志查询设置
SHOW VARIABLES LIKE 'SLOW_QUERY_LOG';
# var/lib/mysql文件中
# 开启慢日志查询开关
slow_query_log = 1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就视为慢查询。
long_query_time = 2

profile详情

show profiles能够在SQL优化时帮助我们了解时间耗费到哪里了。

# 是否支持profile操作
SELECT @@HAVE_PROFILING;
# 开启
SET PROFILING = 1;

# 查看每一条SQL耗时基本情况
SHOW PROFILES;
# 查看指定query_id的SQL语句各个阶段耗时情况
SHOW PROFILE FOR QUERY QUERY_ID;
# 查看指定query_id的SQL语句CPU使用情况
SHOW PROFILE CPU FOR QUERY QUERY_ID;

explain执行计划

explain或者desc命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接顺序。

# 直接在执行语句前加关键字 EXPLAIN/DESC

id: select查询的序列号，表示执行select语句或者是操作表的顺序(id相同，顺序执行; id不同，值大的先执行)。

select_type: 表示SELECT类型，SIMPLE(简单表)、PRIMARY(主查询)、UNION(UNION中第二个或后面的查询语句)、SUBQUERY(SELECT/WHERE之后的子查询)。

type: 表示连接类型，性能好坏NULL>system>const>eq_ref>ref>range>index>all。

possible_key: 显示可能应用的索引，可以有多个。

Key: 实际使用的索引，NULL为未使用索引。

Key_len: 表示索引中使用的字节数，值为索引字段最大可能长度，长度越短越好。

rows: MySQL认为必须要执行查询的行数，估计值。

filtered: 表示返回结果的行数占需读取行数的百分比，值越大越好。

索引使用

最左前缀法则 如果索引了多列（联合索引），查询从索引最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

范围查询 联合索引中出现范围查询(>,<)，范围查询右侧的列索引失效。

索引列运算 不要在索引列上进行运算操作，索引会失效。

字符串不加引号 字符串类型字段使用时，不加引号，索引将失效。

模糊查询 尾部模糊匹配，索引不会失效。头部模糊匹配，索引会失效。

or连接的条件 用or分割开的条件，如果or前的条件中的列有索引，后面的列没有索引，那么涉及的索引都不会被用到。

数据分布影响 如果MySQL评估使用索引比全表慢，则不适用索引。

SQL提示 在SQL语句中加入一些人为的提示来优化操作。

USE INDEX();
IGNORE INDEX();
FORCE INDEX();

覆盖索引 尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *。

前缀索引 字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，此时可以只将字符串一部分前缀建立索引。

# 语法
CREATE INDEX idx_xxx ON table_name(COLUMN(n));
# 前缀长度根据索引选择性决定
# 选择行指不重复的索引值和数据表的记录总数的比值
SELECT COUNT(DISTINCT SUBSTRING(字段名,M,N)/COUNT(*) FROM table_name;

单列索引和联合索引

索引原则

1. 针对数据量较大且查询比较频繁的表建立索引。
2. 针对于常作为查询条件（WHERE）、排序（ORDER BY)、分组（GROUP BY）操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引。区分度越高，使用索引效率就越高。
4. 如果是字符串类型的字段，字段长度较长，可以建立前缀索引。
5. 尽量使用联合索引，减少单列索引。查询时，联合索引很多时候可以覆盖索引，避免回表。
6. 要控制索引数量，索引越多维护索引结构代价越大，影响增删改的效率。
7. 如果索引不能存储NULL，在创建表时就使用NOT NULL约束。当优化器知道每列是否包含NULL，它可以更好的确定哪个索引最有效。

SQL优化

插入数据

# insert 优化
# 批量插入
# 手动提交事务
# 主键顺序插入

# 大批量插入数据可以使用 load 指令
# 客户端连接服务器时加上参数--local-infile
mysql --local-infile -u root -p
# 开启从本地加载文件导入数据的开关
set global local_infile = 1;
# 执行 load 指令将准备好的数据加载到表结构中
load data infile '/root/xxx.log' into table 表名 terminated by ';' lines terminated by '\n';

主键优化

数据组织方式 在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，又称为索引组织表（Index Organization Table IOT）。

页可以为空，也可以填充一半，也可以填充100%。每个页包含2-N行数据（如果一行数据过大，会行溢出），根据主键排列。

页分裂 乱序插入时可能会导致页分裂

页合并 当删除一行记录时，实际只是记录被标记（flagged）为删除并且它的空间允许被其他记录声明使用。当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页，看看是否可以合并两页以优化空间使用。

主键设计原则

尽量降低主键长度；插入数据尽量顺序插入；尽量不要使用UUID或身份证号做主键；业务操作避免修改主键。

order by 优化

Using filesort: 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序。所有不是通过索引直接返回排序结果的排序都叫 Filesort 排序。

Using index: 通过有序索引顺序扫描直接返回有序数据。

group by 优化

• 分组操作时可以通过索引提高效率，且索引使用也是满足最左前缀法则的。

limit 优化

优化思路：一般分页查询时，通过创建覆盖索引加子查询形式优化。

count 优化

count()是一个聚合函数，对于返回的结果集，一行行判断，如果NOT NULL，计数加一，最后返回累计值。

用法：count(*) ≈ count(1) > count(主键) > count(字段)。

count(1) 遍历整张表但不取值，服务层对于每一行放数字"1"，直接按行累加。

count(*) 有专门优化，不取值服务层按行累加。

update 优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

视图/存储过程/触发器

锁

全局锁：锁定数据库中所有表，典型使用场景是做全库的逻辑备份。

表级锁：每次操作锁住整张表

表共享读锁read lock/ 表独占写锁write lock

读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

# 加锁
lock tables 表名... read/write;
# 释放锁
unlock tables;

元数据锁（meta data lock MDL）加锁是系统自动控制，无需显示使用，在访问一张表时候会自动加上。MDL锁主要是维护表元数据一致性，在表上有活动事务时候，不可以对元数据进行写入操作。避免DML与DDL冲突，保证读写正确性。

当对一张表进行CRUD的时候，加MDL读锁（共享）；当对表结构进行变更时，加MDL写锁（排他）。

意向锁使得表锁不用检查每行数据是否加锁。

意向共享锁（IS）：由语句select ... lock in share mode 添加。与 read lock 兼容，与 write lock 互斥。

意向排他锁（IX）：由insert、update、delete、select ... for update添加。与 read lock 及 write 都互斥。意向锁之间不会互斥。

行级锁：每次操作锁住对应行

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的。行锁主要分为三种：

1. 行锁（Record Lock）：锁定单个记录的锁，防止其他事务对此进行update和delete。在RC、RR隔离级别下都支持。

   SQL	行锁类型	说明
   INSERT/UPDATE/DELETE	排他锁	自动加锁
   SELECT	不加锁
   SELECT .. LOCK IN SHARE MODE	共享锁	手动加LOCK IN SHARE MODE
   SELECT ... FOR UPDATE	排他锁	手动加FOR UPDATE

2. 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下支持。

3. 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据以及数据前面的间隙。在RR隔离级别下支持。

默认情况下，InnoDB在RR隔离级别运行，使用next-key锁进行搜索和索引扫描，以防止幻读。

1. 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。
2. 索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁。
3. 索引上的范围查询（唯一索引）会访问到不满足条件的第一个值为止。

共享锁（S）：允许一个事务读一行，阻止其他事务获得相同数据集的排他锁。

排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

InnoDB引擎

逻辑存储结构

表空间（idb文件），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。

段，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB是索引组织表，数据段就算B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个区。

区，表空间的单元结构，每个区的大小为1M。默认情况下InnoDB引擎页的大小为16k，即一个区中一共有64个连续的页。

页，是InnoDB存储引擎磁盘管理的最小单元，每个页大小默认为16KB。为了保证页的连续性，InnoDB每次向磁盘申请4-5个区。

行，InnoDB存储引擎数据是按行进行存放的。行头Trx_id事务id、Roll_pointer回滚指针。

架构

内存架构

Buffer Pool 缓冲池

Change Buffer 更改缓冲区

Adaptive Hash Index 自适应hash索引

Log Buffer 日志缓冲区

磁盘架构

事务原理

redo log 重做日志记录的是事务提交时数据页的物理修改，用来实现事务的持久性。文件由两部分组成：重做日志缓冲（redo log buffer）（内存中）以及重做日志文件（redo log file）（磁盘中）。事务提交后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。

undo log 回滚日志用于记录数据被修改前的信息，用来保证事务的原子性，提供回滚和MVCC（多版本并发控制）。undo log 是逻辑日志。

MVCC

Multi-Version Concurrency Control，多版本并发控制，指维护一个数据的多个版本，使得读写操作没有冲突，快照读提供了一个非阻塞读功能。MVCC具体实现依赖于三个隐式字段、undo log 日志和readView。

当前读读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

快照读就是简单的select（不加锁），读取的时记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

RC：每次select都生成一个快照读。

RR：开启事务后的第一个select语句才是快照读的地方。

Serializable：快照读会退化为当前读。

• 记录中的隐式字段

  DB_TRX_ID 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。

  EB_ROLL_PTR 回滚指针，指向这条记录的上一个版本，配合undo log，指向上一个版本。

  DB_ROW_ID 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

• undo log

  insert时产生的undo log 日志只在回滚时需要，事务提交后可以被删除。

  update、delete时，产生的undo log 不仅在回滚时需要，快照读时也需要，不会立即被删除。

  undo log 版本链 不同事物或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

• readView

  读视图是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。四个核心字段：

  字段	含义
  m_ids	当前活跃的事务ID集合
  min_trx_id	最小活跃事务ID
  max_trx_id	预分配事务ID，当前最大事务ID+1（事务ID是自增的）
  creator_trx_id	ReadView创建者事务ID

  1. trx_id==creator_trx_id 数据是当前事务更改的，可以访问该版本
  2. trx_id<min_trxx_id 数据已经提交了，可以访问该版本。
  3. trx_id>max_trx_id 当前事务在ReadView生成后才开启的。
  4. min<=trx_id<=max trx_id不在m_ids中，说明数据已经提交，可以访问该版本。

不同的隔离级别，生成ReadView的时机不同：

• RC：在事务中每一次执行快照读时生成ReadView。
• RR：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

分库分表

介绍

单数据库数据存储存在性能瓶颈：

1. IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO。请求数据太多，带宽不够，网络IO瓶颈
2. CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量COU资源，请求数太多，CPU瓶颈

分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而提升数据库的性能。

拆分策略

垂直拆分

垂直分库：以表为依据，根据业务将不同表拆分到不同库中。特点：

1. 每个库表结构都不一样
2. 每个库的数据也不一样
3. 所有库的并集是全量数据

垂直分表：以字段为依据，根据字段属性不同将不同字段拆分到不同表中。特点：

1. 每个表结构不一样
2. 每个表数据也不一样，一般通过一列（外键/主键）关联
3. 所有表的并集是全量数据

水平拆分

水平分库：以字段为依据，依照一定策略，将一个库的数据拆分到多个库中。特点：

1. 每个库的表结构都一样
2. 每个库的数据都不一样
3. 所有库的并集是全量数据

水平分表：以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。

1. 每个表的表结构都一样
2. 每个表的数据都不一样
3. 所有表的并集是全量数据

实现技术

• shardingJDBC：基于AOP原理，在应用程序中对本地执行的SQL进行拦截、解析、改写、路由处理。需要自行编码配置实现，只支持java，性能较高
• Mycat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者

Mycat概述

概念介绍

Mycat入门

• 安装

• 环境准备

• Mycat配置（schema.xml、server.xml、rule.xml）

  

Mycat配置

schema.xml 涵盖了Mycat的逻辑库、逻辑表、分片规则、分片节点及数据源配置。

• schema标签用于定义Mycat实例中的逻辑库，一个Mycat实例中，可以有多个逻辑库，可以通过schema标签来划分不同的逻辑库。Mycat中逻辑库的概念等同于MySQL中的database概念。需要操作某个逻辑库下的表时，也需要切换逻辑库（use xxx）
• datanode标签中定义了Mycat中的数据节点，也就是我们通常说的数据分片。一个dataNode标签就是一个独立的数据分片。
• datahost标签在Mycat逻辑库中作为底层标签存在，直接定义了具体的数据库实例、读写分离、心跳语句。

server.xml配置文件包含Mycat的系统配置信息，主要有两个重要标签：system、user

rule.xml中定义所有拆分表的规则，在使用过程中可以灵活的使用分片算法，或者对同一个分片算法使用不同的参数，它让分片过程可配置化。主要包含两类标签：tableRule、Function

Mycat分片

• 分片规则-范围

  根据指定的字段及其配置的范围与数据节点的对应情况，来决定该数据属于哪一个分片

• 分片规则-取模

  根据指定字段与节点数量进行球磨运算，根据运算结果来决定该数据属于哪一个分片

Mycat管理及监控

Mycat原理

Mycat管理

Mycat默认开通2个端口，可以在server.xml中进行修改。

• 8066数据访问端口，即进行DML和DDL操作
• 9066数据库管理端口，即mycat服务管理控制功能，用于管理mycat的整个集群状态

读写分离

介绍

读写分离，简单地说就是把对数据库的读和写操作分开，以对应不同的数据库服务器。主数据库提供写操作，从数据库提供读操作，这样能有效的减轻单台数据库的压力。

一主一从

原理：MySQL的主从复制是基于binlog实现的。

一主一从读写分离

配置：Mycat控制后台数据库的读写分离和负责均衡由schema.xml文件datahost标签的balance属性控制

<writeHost></writeHost>
<readHost></readHost>

双主双从

两台主库，相互复制，互为主备，增强可用性

双主双从读写分离

<writeHost></writeHost>
balance\writeType\switchType