一，数据库应该如何优化

数据库优化有很多层面。

1.SQL与索引

因为 SQL 语句是在我们的应用端编写的，所以第一步，我们可以在程序中对 SQL 语句进行优化，最终的目标是用到索引。这个是容易的也是最常用的优化手段。

2.表与存储引擎

数据是存放在表里面的，表又是以不同的格式存放在存储引擎中的，所以我们可以选用特定的存储引擎，或者对表进行分区，对表结构进行拆分或者冗余处理，或者对表结构比如字段的定义进行优化。

3.架构

对于数据库的服务，我们可以对它的架构进行优化。如果只有一台数据库的服务器，我们可以运行多个实例，做集群的方案，做负载均衡。或者基于主从复制实现读写分离，让写的服务都访问 master 服务器，读的请求都访问从服务器，slave 服务器自动 master 主服务器同步数据。或者在数据库前面加一层缓存，达到减少数据库的压力，提升访问速度的目的。为了分散数据库服务的存储压力和访问压力，我们也可以把不同的数据分布到不同的服务节点，这个就是分库分表（scale out）。

注意主从（replicate）和分片（shard）的区别：

1. 主从通过数据冗余实现高可用，和实现读写分离。
2. 分片通过拆分数据分散存储和访问压力。

4.配置

数据库配置的优化，比如连接数，缓冲区大小等等，优化配置的目的都是为了更高效地利用硬件。

5.操作系统与硬件

从上往下，成本收益比慢慢地在增加。所以肯定不是查询一慢就堆硬件，堆硬件叫做向上的扩展（scale up）。

二，慢日志查询

1.概述

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合explain进行全面分析。

2.实操

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

2.1查看及开启

①日志

SHOW VARIABLES LIKE '%slow_query_log%';

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的。

set global slow_query_log=1;

只对窗口生效，重启服务失效。

②时间

SHOW VARIABLES LIKE '%long_query_time%';

SET GLOBAL long_query_time=0.1;

全局变量设置，对所有客户端有效。但必须是设置后进行登录的客户端。

SET SESSION long_query_time=0.1; #session可省略

对当前会话连接立即生效，对其他客户端无效。

假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。

③永久生效

修改配置文件my.cnf（其它系统变量也是如此）

[mysqld]下增加或修改参数

slow_query_log 和slow_query_log_file后，然后重启MySQL服务器。也即将如下两行配置进my.cnf文件 [1]

slow_query_log =1
slow_query_log_file=/var/lib/mysql/yhd-slow.log 
long_query_time=3
log_output=FILE

2.2Case

记录慢SQL并后续分析

查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

3.日志分析工具-mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

查看mysqldumpslow的帮助信息（windows下需要安装perl环境）

mysqldumpslow --help

-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序；
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
-t: 即为返回前面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的；

3.1常用SQL

得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/yhd-slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/yhd-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/yhd-slow.log
另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/yhd-slow.log | more

4.SHOW PROCESSLIST

作用：查询所有用户正在干什么。

如果出现不顺眼的：kill [id]

三，EXPLAIN调优实战

1.准备数据

员工表插入500w数据，部门表插入10w数据。

CREATE TABLE `dept`
(
    `id`       INT(11) NOT NULL AUTO_INCREMENT,
    `deptName` VARCHAR(30) DEFAULT NULL,
    `address`  VARCHAR(40) DEFAULT NULL,
    `ceo`      INT     NULL,
    PRIMARY KEY (`id`)
) ENGINE = INNODB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8;
CREATE TABLE `emp`
(
    `id`     INT(11) NOT NULL AUTO_INCREMENT,
    `empno`  INT     NOT NULL,
    `name`   VARCHAR(20) DEFAULT NULL,
    `age`    INT(3)      DEFAULT NULL,
    `deptId` INT(11)     DEFAULT NULL,
    PRIMARY KEY (`id`)
    #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE = INNODB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8;
#生成随机字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n
        DO
            SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1 + RAND() * 52), 1));
            SET i = i + 1;
        END WHILE;
    RETURN return_str;
END $$
#用于随机产生多少到多少的编号
DELIMITER $$
CREATE FUNCTION rand_num(from_num INT, to_num INT) RETURNS INT(11)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(from_num + RAND() * (to_num - from_num + 1));
    RETURN i;
END$$
#创建往emp表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE insert_emp(START INT, max_num INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0; #设置手动提交事务
    REPEAT
        #循环
        SET i = i + 1; #赋值
        INSERT INTO emp (empno, NAME, age, deptid)
        VALUES ((START + i), rand_string(6), rand_num(30, 50), rand_num(1, 10000));
    UNTIL i = max_num
        END REPEAT;
    COMMIT; #提交事务
END$$
#创建往dept表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE `insert_dept`(max_num INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
        SET i = i + 1;
        INSERT INTO dept (deptname, address, ceo) VALUES (rand_string(8), rand_string(10), rand_num(1, 500000));
    UNTIL i = max_num
        END REPEAT;
    COMMIT;
END$$
#执行存储过程，往dept表添加10万条数据
CALL insert_dept(100000);
#执行存储过程，往emp表添加500万条数据
CALL insert_emp(100000, 5000000);

2.批量删除索引

建立好的索引在哪里？

SHOW INDEX FROM t_emp ; -- 只能查看索引，但不能删除。
information_schema.STATISTICS -- 存储索引的表（元数据库，统计表），我们可以对表数据进行删除操作。

知识点

1. 删除某一个索引

DROP INDEX idx_xxx ON emp

1. 查出该表有哪些索引，索引名—>集合

SHOW INDEX FROM t_emp
-- 元数据：meta DATA  描述数据的数据
SELECT index_name
FROM information_schema.STATISTICS
WHERE table_name = 't_emp'
  AND table_schema = 'mydb'
  AND index_name <> 'PRIMARY'
  AND seq_in_index = 1

3.单表使用索引

建立索引

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
CREATE INDEX idx_name ON emp(NAME);

3.1 全值匹配

# 单表查询-全值匹配
EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE emp.age = 30;
EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE emp.age = 30
  and deptid = 4;
EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE emp.age = 30
  and deptid = 4
  AND emp.name = 'abcd';

3.2 最左前缀法则

# 单表查询-左前缀法则
EXPLAIN SELECT * FROM emp WHERE age=1 AND deptid=1 AND NAME='aaa';
EXPLAIN SELECT * FROM emp WHERE age=1 AND deptid=1;
EXPLAIN SELECT * FROM emp WHERE age=1 AND NAME='aaa' AND deptid=1;
EXPLAIN SELECT * FROM emp WHERE deptid=1 AND NAME ='aaa';

过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

3.3 索引列上计算/函数导致索引失效

# 单表查询-操作索引列导致索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name  LIKE 'abc%';
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(emp.name,3) = 'abc';

3.4 范围查询导致的索引失效

EXPLAIN 
SELECT 
  SQL_NO_CACHE * 
FROM
  emp 
WHERE emp.name = 'abc'
  AND emp.deptId > 20 
  AND  emp.age = 30 ;

应用开发中范围查询，例如： 金额查询，日期查询往往都是范围查询。应将查询条件放置where语句最后。

3.5 不等于(!= 或者<>)索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <>  'abc' ;

3.6 is not null无法使用索引，is null可使用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NULL;
#用到索引  
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NOT NULL;
#未用到索引

3.7 like以%开头索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE NAME LIKE '%aaa';

3.8 类型转换导致索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE NAME=123;

设计实体类属性时，一定要与数据库字段类型相对应，否则会出现类型转换的情况，导致索引失效。

4. 关联查询优化

4.1 左外连接

explain select * from emp left join dept on emp.deptId=dept.id;

这种情况下，驱动表无法避免全表扫描，但是因为被驱动表的主键存在索引并且是两张表关联查询的关联条件，所以可以避免被驱动表的全表扫描。

4.2 内连接(TODO)

内连接MySQL会自动为我们选择驱动表。

explain  select * from dept  straight_join emp on emp.deptId=dept.id;
## 1. dept 全表扫描   10w
## 2. emp deptid  ref
explain    select * from dept join emp on emp.deptId=dept.id;
## 1. emp 500w
## 2. dept id ref

1. 保证被驱动表的join字段被索引
2. left join 时，选择小表作为驱动表，大表作为被驱动表
3. inner join 时，mysql会自动将小结果集的表选为驱动表。选择相信mysql优化策略。
4. 子查询尽量不要放在被驱动表，衍生表建不了索引。
5. 能够直接多表关联的尽量直接关联，不用子查询。
6. 两张表的连接查询，比方说 left join right、inner join 等，他们的连表方式是什么？
7. 连表查询一共三种算法：nlj bnl bka 算法 。
8. right join 底层，会给你转化为left join。

4.3 子查询优化

#①不推荐
explain SELECT
  *
FROM
  emp
WHERE emp.id NOT IN   -- not in 导致无法对in进行优化，用不了exists
  (SELECT
    dept.ceo
  FROM
    dept
  WHERE dept.ceo IS NOT NULL) ; -- is not null 导致索引失效
#②推荐
explain SELECT
  emp.*
FROM
  emp
  LEFT JOIN dept
    ON emp.id = dept.ceo  -- 如果ceo没有索引，两张表都是全表扫描，如果ceo有索引，被驱动表就是ref级别
WHERE dept.id IS NULL ;
# 尝试在ceo创建索引后，确实是  create index idx_ceo on dept(ceo);

尽量不要使用not in 或者 not exists，会使索引失效。

MySQL自动做出的子查询优化，物化子查询，转为半连接

物化子查询：把子查询的结果查出来后，建立一个临时表，“物化”->变成一张内存临时表

半连接：把子查询转化为类似连接查询的方式，但又不是真正的连接查询，所以叫 半 连接优化

5.排序分组优化

5.1 无过滤，不索引

EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid; 
#没用上索引，Using filesort 
EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid LIMIT 10; 
#使用上索引 null

因为order by的字段顺序和索引的顺序一样，所以此时会先尝试内存排序，但是因为上面的sql没有limit，导致内存放不下，使用了文件排序（文件系统级别，相当于在磁盘做排序），所以第一条sql效率更低。

order后面的字段想要使用索引，必须要有过滤条件，limit也行。

5.2顺序错，必排序

EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid;
# Using index condition
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid,NAME;
# Using index condition
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid,empno;
# Using index condition; Using filesort
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY NAME,deptid;
# Using index condition; Using filesort
EXPLAIN SELECT * FROM emp WHERE deptid=45 ORDER BY age;
# Using where; Using filesort

在SQL语句中的顺序一定要和定义索引中的字段顺序完全一致。

5.3 方向反，必排序

EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid DESC, NAME DESC ;
#Using where
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid ASC, NAME DESC ;
#Using index condition; Using filesort

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序

要么全升序、要么全降序。有升有降无法使用索引。

5.4 索引的选择

两个索引同时存在，mysql自动选择最优的方案，但是，随着数据量的变化，选择的索引也会随之变化的。

所有的排序都是在条件过滤之后才执行的，所以，如果条件过滤掉大部分数据的话，剩下几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序，但实际提升性能很有限。

当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围（过滤条件）字段上。反之，亦然。

扫描行数的多少，就是explain里的rows，可以说明一个需要扫描的行数多，一个扫描行数少，扫描行数多，代表成本高，扫描行数少代表成本少。优化器最终是对比成本值的大小来选取索引的。准确的说，是MySQL基于成本，优化器是在server层。

有时候优化器会选择错索引为什么？

1. 主要是出在优化器预估行数上，这个涉及到了一条sql的执行流程，语法分析，词法分析之后，进入优化阶段，由优化器进行优化，在优化阶段，会尽可能的生成全部的执行计划，然后对比一下哪一个成本值最低，就选它，所以优化器有一个选择索引，选择表的连接顺序的过程，索引不同，成本不同，读表顺序不同，成本不同，索引的选取，需要存储引擎提供统计信息，innodb中，统计信息是随机采样，随机选取8个索引页，取平均值，当做该索引的全部情况，也就是部分代表整体，也就是最终导致rows那里是个预估值，而不是准确的。所以有时候MySQL选错了索引，有一定概率，是由于这个随机采样造成的。而随机采样的不准确，是由于数据不断添加导致索引页的分裂，导致有些页内数据较少。

   解决方案：

   1. 执行一下alter table +表名 就可以使统计信息稍微准确点，他会重新构建索引，使索引页保持紧凑，这个就是B+树的分裂。
   2. 调整参数，加大InnoDB采样的页数，页数越大越精确，但性能消耗更高。一般不建议这么干。

1. 在优化阶段，会对表中所有索引进行对比，优化器基于成本的原因，选择成本最低的索引，所以会错过最佳索引。带来的问题便是，执行速度很慢。

   解决方案：

   1. 通过explain查看执行计划，结合sql条件查看可以利用哪些索引。
   2. 使用 force index(indexName)强制走指定索引。弊端就是后期若索引名发生改变，或索引被删除，该sql语句需要调整。

5.5 双路排序&单路排序

如果不在索引列上，filesort有两种算法： mysql就要启动双路排序和单路排序。

双路排序

MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出

从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

取一批数据，要对磁盘进行两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

结论

由于单路是后出的，总体而言好过双路。

但是用单路有问题：

在sort_buffer中，单路比多路要多占用很多空间，因为单路是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排……从而多次I/O。

单路本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

优化策略

增大sort_buffer_size参数的设置

增大max_length_for_sort_data参数的设置

减少select 后面的查询的字段。

提高order by的速度

1. Order by时select * 是一个大忌。只Query需要的字段， 这点非常重要。

当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。

两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。

1. 尝试提高 sort_buffer_size

不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程（connection）的 1M-8M之间调整。 MySQL5.7，InnoDB存储引擎默认值是1048576字节，1MB。

SHOW VARIABLES LIKE '%sort_buffer_size%';

1. 尝试提高 max_length_for_sort_data

提高这个参数， 会增加用改进算法的概率。

但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。如果需要返回的列的总长度大于max_length_for_sort_data，使用双路算法，否则使用单路算法。1024-8192字节之间调整。

SHOW VARIABLES LIKE '%max_length_for_sort_data%'; #默认1024字节

5.6 分组优化

group by 使用索引的原则几乎跟order by一致 ，唯一区别：

group by 先排序再分组，遵照索引建的最佳左前缀法则

当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置

where高于having,能写在where限定的条件就不要写在having中了

group by没有过滤条件，也可以用上索引。Order By 必须有过滤条件才能使用上索引。

6. 覆盖索引

禁止使用select *，禁止查询与业务无关字段，尽量使用覆盖索引，防止回表。

覆盖索引减少了 IO 次数，减少了数据的访问量，可以大大地提升查询效率。

四，追踪优化器

前面的原理篇详细分析过，在此不再赘述。

五， 分库分表

从维度来说分成两种，一种是垂直，一种是水平。

垂直切分：基于表或字段划分，表结构不同。我们有单库的分表，也有多库的分库。

水平切分：基于数据划分，表结构相同，数据不同，也有同库的水平切分和多库的切分。

1.垂直切分

垂直分表有两种，一种是单库的，一种是多库的。

1.1 单库垂直分表

单库分表，比如：商户信息表，拆分成基本信息表，联系方式表，结算信息表，附件表等等。

可以考虑根据冷热点字段拆分，是否经常发生修改操作拆分，根据字段功能拆分。

1.2 多库垂直分表

多库垂直分表就是把原来存储在一个库的不同的表，拆分到不同的数据库。

比如电商平台的消费系统：一开始，商品表，商品详情表，订单表，用户表，支付记录表，库存表，风控表都在一个库里面，随着数据的增长和业务的扩张，可以考虑将商品和商品详情表单独放到一个库，订单表单独放到一个库，支付记录单独放到一个库，库存表单独放到一个库，风控表单独放到一个库。

当我们对原来的一张表做了分库的处理，如果某些业务系统的数据还是有一个非常快的增长速度，比如说订单数据库的订单表，数据量达到了几个亿，这个时候硬件限制导致的性能问题还是会出现，所以从这个角度来说垂直切分并没有从根本上解决单库单表数据量过大的问题。在这个时候，我们还需要对我们的数据做一个水平的切分。

2.水平拆分

当我们的客户表数量已经到达数千万甚至上亿的时候，单表的存储容量和查询效率都会出现问题，我们需要进一步对单张表的数据进行水平切分。水平切分的每个数据库的表结构都是一样的，只是存储的数据不一样，比如每个库存储 1000 万的数据。

水平切分也可以分成两种，一种是单库的，一种是多库的。

2.1 单库水平分表

银行的交易流水表，所有进出的交易都需要登记这张表，因为绝大部分时候客户都是查询当天的交易和一个月以内的交易数据，所以我们根据使用频率把这张表拆分成三张表：

当天表：只存储当天的数据。

当月表：在夜间运行一个定时任务，前一天的数据，全部迁移到当月表。用的是 insert into select，然后 delete。

历史表：同样是通过定时任务，把登记时间超过 30 天的数据，迁移到 history历史表（历史表的数据非常大，我们按照月度，每个月建立分区）。

跟分区一样，这种方式虽然可以一定程度解决单表查询性能的问题，但是并不能解决单机存储瓶颈的问题。

2.2 多库水平分表

比如客户表，我们拆分到多个库存储，表结构是完全一样的。

一般我们说的分库分表都是跨库的分表。

3. 分库分表带来的问题

3.1 跨库关联查询

比如查询合同信息的时候要关联客户数据，由于是合同数据和客户数据是在不同的数据库，那么我们肯定不能直接使用 join 的这种方式去做关联查询。

解决方案

①字段冗余

比如我们查询合同库的合同表的时候需要关联客户库的客户表，我们可以直接把一些经常关联查询的客户字段放到合同表，通过这种方式避免跨库关联查询的问题。

②数据同步

比如商户系统要查询产品系统的产品表，我们干脆在商户系统创建一张产品表，通过 ETL 或者其他方式定时同步产品数据。

③全局表（广播表）

比如行名行号信息被很多业务系统用到，如果我们放在核心系统，每个系统都要去关联查询，这个时候我们可以在所有的数据库都存储相同的基础数据。

④ER表

我们有些表的数据是存在逻辑的主外键关系的，比如订单表 order_info，存的是汇总的商品数，商品金额；订单明细表 order_detail，是每个商品的价格，个数等等。或者叫做从属关系，父表和子表的关系。他们之间会经常有关联查询的操作，如果父表的数据和子表的数据分别存储在不同的数据库，跨库关联查询也比较麻烦。所以我们能不能把父表的数据和从属于父表的数据落到一个节点上呢？

比如 order_id=1001 的数据在 node1，它所有的明细数据也放到 node1；order_id=1002 的数据在 node2，它所有的明细数据都放到 node2，这样在关联查询的时候依然是在一个数据库。

上面的思路都是通过合理的数据分布避免跨库关联查询，实际上在我们的业务中，也是尽量不要用跨库关联查询，如果出现了这种情况，就要分析一下业务或者数据拆分是不是合理。如果还是出现了需要跨库关联的情况，那我们就只能用最后一种办法。

⑤系统层组装

在不同的数据库节点把符合条件数据的数据查询出来，然后重新组装，返回给客户端。

3.2 分布式事务

具体分布式事务会单独写一篇文章

3.3 排序，翻页，函数计算问题

跨节点多库进行查询时，会出现 limit 分页，order by 排序的问题。比如有两个节点，节点 1 存的是奇数 id=1,3,5,7,9……；节点 2 存的是偶数 id=2,4,6,8,10……

执行 select * from user_info order by id limit 0,10

需要在两个节点上各取出 10 条，然后合并数据，重新排序。

max、min、sum、count 之类的函数在进行计算的时候，也需要先在每个分片上执行相应的函数，然后将各个分片的结果集进行汇总和再次计算，最终将结果返回。

3.4 全局主键避重

MySQL 的数据库里面字段有一个自增的属性，Oracle 也有 Sequence 序列。如果是一个数据库，那么可以保证 ID 是不重复的，但是水平分表以后，每个表都按照自己的规律自增，肯定会出现 ID 重复的问题，这个时候我们就不能用本地自增的方式了。

解决方案

①UUID

UUID 标准形式包含 32 个 16 进制数字，分为 5 段，形式为 8-4-4-4-12 的 36 个字符，例如：c4e7956c-03e7-472c-8909-d733803e79a9。

UUID 是主键是最简单的方案，本地生成，性能高，没有网络耗时。但缺点也很明显，由于 UUID 非常长，会占用大量的存储空间；另外，作为主键建立索引和基于索引进行查询时都会存在性能问题，在 InnoDB 中，UUID 的无序性会引起数据位置频繁变动，导致分页。

②数据库

把序号维护在数据库的一张表中。这张表记录了全局主键的类型、位数、起始值，当前值。当其他应用需要获得全局 ID 时，先 for update 锁行，取到值+1 后并且更新后返回。并发性比较差。

③redis

基于 Redis 的 INT 自增的特性，使用批量的方式降低数据库的写压力，每次获取一段区间的 ID 号段，用完之后再去数据库获取，可以大大减轻数据库的压力。

④雪花算法

优点：毫秒数在高位，生成的 ID 整体上按时间趋势递增；不依赖第三方系统，稳定性和效率较高，理论上 QPS 约为 409.6w/s(1000*2^12)，并且整个分布式系统内不会产生 ID 碰撞；可根据自身业务灵活分配 bit 位。

不足就在于：强依赖机器时钟，如果时钟回拨，则可能导致生成 ID 重复。

4. 多数据源/读写数据源的解决方案

分析一下 SQL 执行经过的流程：

DAO——Mapper（ORM）——JDBC——代理——数据库服务

4.1 客户端DAO 层

在我们连接到某一个数据源之前，我们先根据配置的分片规则，判断需要连接到哪些节点，再建立连接。

Spring 中提供了一个抽象类 AbstractRoutingDataSource，可以实现数据源的动态切换。

1）aplication.properties 定义多个数据源
2）创建@TargetDataSource 注解
3）创建 DynamicDataSource 继承 AbstractRoutingDataSource
4）多数据源配置类 DynamicDataSourceConfig
5）创建切面类 DataSourceAspect，对添加了@TargetDataSource 注解的
类进行拦截设置数据源。
6）在 启 动 类 上 自 动 装 配 数 据 源 配 置
@Import({DynamicDataSourceConfig.class})
7）在 实 现 类 上 加 上 注 解 ， 如 @TargetDataSource(name =
DataSourceNames.SECOND)，调用

在 DAO 层实现的优势：不需要依赖 ORM 框架，即使替换了 ORM 框架也不受影响。实现简单（不需要解析 SQL 和路由规则），可以灵活地定制。

缺点：不能复用，不能跨语言。

4.2 ORM框架层

比如我们用 MyBatis 连接数据库，也可以指定数据源。我们可以基于 MyBatis 插件的拦截机制（拦截 query 和 update 方法），实现数据源的选择。

4.3 驱动层

不管是MyBatis还是Hibernate，还是Spring的JdbcTemplate，本质上都是对JDBC的封装，所以第三层就是驱动层。比如 Sharding-JDBC，就是对 JDBC 的对象进行了封装。JDBC 的核心对象：

DataSource：数据源

Connection：数据库连接

Statement：语句对象

ResultSet：结果集

那我们只要对这几个对象进行封装或者拦截或者代理，就可以实现分片的操作。

4.4 代理层

前面三种都是在客户端实现的，也就是说不同的项目都要做同样的改动，不同的编程语言也有不同的实现，所以我们能不能把这种选择数据源和实现路由的逻辑提取出来，做成一个公共的服务给所有的客户端使用呢？

这个就是第四层，代理层。比如 Mycat 和 Sharding-Proxy，都是属于这一层。

4.5 数据库服务

某些特定的数据库或者数据库的特定版本可以实现这个功能。

六，主从复制

1. 基本原理

MySQL复制过程分成三步：

1. master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
2. slave将master的binary log events拷贝到它的中继日志（relay log）；
3. slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，slave会从master读取binlog来进行数据同步。

2.与Redis主从复制的差别

1. redis主从复制是将主机的所有数据都拷贝给从机，并且是近乎实时的。
2. mysql主从复制不会将建立连接以前的数据发送给从机，并且是异步，且串行化的。

3.复制的基本原则

每个slave只有一个master

每个slave只能有一个唯一的服务器ID

每个master可以有多个salve

4.复制的最大问题

延时

全同步可以避免，但性能会极差，正常情况下半同步，且容忍一部分数据不一致。如果不容忍数据不一致，只有强制读主。

5.一主一从常见配置

1. MySQL版本一致且后台以服务运行
2. 主从配置都在【mysqld】节点下，且全部小写
3. 主机修改my.ini文件

1. 主服务器唯一ID server-id=1
2. 启用二进制日志
3. 设置不要复制的数据库
4. 设置需要复制的数据库
5. 设置logbin格式

log-bin=自己的本地路径/data/mysqlbin

binlog-ignore-db=mysql

binlog-do-db=需要复制的主数据库名字

binlog_fromat=STATEMENT(默认)

七，硬件层面的配置

1.选择合适的CPU

数据库分为两大类，在线事务处理和在线分析处理。

InnoDB储存引擎一般应用于OLTP的数据库应用，这种应用的特点如下：

1. 用户操作的并发量大
2. 事务处理时间一般比较短
3. 查询的语句较为简单，一般都走索引
4. 复杂查询比较少

在当前的MySQL数据库版本中，一条SQL语句只能在一个CPU工作，并不支持多CPU。若cpu支持多核，innodb版本应该选择1.1或者更高。另外如果是多核cpu，可以通过修改参数innodb_read_io_threads和innodb_write_io_threads来增大IO的线程，这样也可以更充分的利用cpu的多核性能。

2.内存的重要性

内存大小直接反映数据库的性能。Innodb存储引擎既缓存数据，又缓存索引，并且将它们缓存于一个很大的缓冲池中，即InnoDB Buffer Pool。因此，内存的大小直接影像数据库的性能。

3.磁盘对数据库性能的影响

4.合理设置RAID类型

5.操作系统的选择

6.文件系统的选择