数据库优化

对于优化最重要的事是测量，如果优化的成本高于收益，就要停止优化。

优化原因

优化方面

成本从下到上递增，效果从上到下递减

MYSQL优化

监控

性能剖析 show profile(逐渐淘汰)

一条SQL语句结束后

使用show profile查询剖析工具，可以指定具体的type

show profile cpu;

all：显示所有性能信息

block io：显示块io操作的次数

context switches：显示上下文切换次数，被动和主动

cpu：显示用户cpu时间、系统cpu时间

IPC：显示发送和接受的消息数量

memory：内存

page faults：显示页错误数量

source：显示源码中的函数名称与位置

swaps：显示swap的次数

show status则可以查看相关计数器数据，计数器数据价值相较于profile低。

使用performance schema

通过该数据库直接通过sql就能得到服务器相关的一些测量信息

使用show processlist查看连接的线程个数

开启慢查询

慢查询日志式开销最低，精度最高的测量查询时间的工具

set global slow_query_log=ON; #开启慢查询set global long_query_time=1.0; #设置记录时长为1秒set global log_queries_not_using_indexes = ON; #不适用索引

慢查询日志地址：

地址存储在slow_query_log_file变量中

慢查询日志存储格式

# Time: 2019-11-29T06:01:43.909217Z 执行时间# User@Host: root[root] @ localhost []  Id:     9 主机信息# Query_time: 0.104442 查询时间  Lock_time: 0.000153 锁定时间   Rows_sent: 1  发送行数   Rows_examined: 16249 锁扫描行数SET timestamp=1575007303; 执行时间戳select count(*) from actor,payment; SQL

慢查询分析工具

mysqldumpslow -t 10 日志地址 # 分析前10条记录
wget percona.com/get/pt-query-digest # 下载chmod u+x pt-query-digest # 添加执行权限/pt-query-digest 慢查询日志地址 # 分析日志

问题定位

查询执行计划

explain sql
id: 1  select_type: SIMPLE #         table: staff   partitions: NULL         type: indexpossible_keys: NULL          key: idx_fk_store_id      key_len: 1          ref: NULL         rows: 2     filtered: 100.00        Extra: Using index

索引优化

索引

创建索引

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引

ALTER TABLE table_name ADD INDEX index_name (column_list)ALTER TABLE table_name ADD UNIQUE (column_list)ALTER TABLE table_name ADD PRIMARY KEY (column_list)

CREATE INDEX可对表增加普通索引或UNIQUE索引

CREATE INDEX index_name ON table_name (column_list)CREATE UNIQUE INDEX index_name ON table_name (column_list)

获取索引

show keys  from table_name

何时使用索引

索引优化

数据库结构优化

垂直拆分

IDNameAvatar
1Shaun<Binaries>
2Lucy<Binaries>
3Linda<Binaries>
4Mary<Binaries>
5Tom<Binaries>

↓

IDName
1Shaun
2Lucy
3Linda
4Mary
5Tom
AvatarID
<Binaries>1
<Binaries>2
<Binaries>3
<Binaries>4
<Binaries>5

使用垂直切分将按数据库中表的密集程度部署到不同的库中

切分后部分表无法join，只能通过接口方式解决，提高了系统复杂度，存在分布式事务问题

水平拆分

IDName
1Shaun
2Lucy
3Linda
4Mary
5Tom

↓

IDName
1Shaun
2Lucy
3Linda
IDName
4Mary
5Tom

当一个表的数据不断增多时，水平拆分是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力

分库分表

同上面的水平拆分，每张表或者每个库只存储一定量的数据，当需要进行数据读写时，根据唯一ID取模得到数据的位置

为什么分库分表能提高性能

将一张表的数据拆分成多个n张表进行存放，然后使用第三方中间件（MyCat或者Sharding-JDBC）可以并行查询

一些分库分表中间件

cobar，tddl，atlas，sharing-jdbc，my-cat

系统迁移到分库分表

如何将一个单库单表的系统动态迁移到分库分表上去

禁止全部数据写入，编写一个程序，将单库单表的数据写到分库分表上：

  1. 应用程序预先实现分库分表的支持
  2. 停机暂停所有流量接入
  3. 启动脚本，循环读取数据，将每一条数据按照既定规则写入到新库，直至完成
  4. 应用程序切换到新库上
  5. 恢复，继续接收流量

新系统部署后，每条数据都会在老库和新库写一遍

双写切换流程：

  1. 刚开始只读写老库
  2. 切换到读写老库的同时，开始往新库写一遍数据
  3. 切换到只写老库，开始启用新库读写
  4. 完成切换，不再写老库，只用新库读写

同时还需要一个校验与修复脚本持续不断运行，将老库与新库不一致的数据修复，把老库更新到新库的条件是：老库有的数据新库没有，或者是老库的数据更新时间比新库的新

工具会比较新库与老库的每一条数据，只有每条数据都一致，才算完成，否则持续运行校验，这样脚本几轮操作过去后，新老库的数据就一致了

动态扩容缩容的分库分表方案

同上，只不过上面那是从单个数据库到多个数据库，这次这个是多个数据库到多个数据库但是不推荐这种做法，原因是数据量很大，数据很难在短时间内转移完毕

32 个库，每个库 32 个表这里可以多个库都在同一台机器上，当不够用的时候，可以将这些库转移到新机器上这样，数据的逻辑位置没有发生改变，也避免扩容缩容带来的数据迁移问题

比较适合数据从自建机房向云上迁移的场景，在切写的时候需要短暂的停止写入

2022816205127

唯一ID生成

为了避免每次生成都需要一次调用，在需要产生新的全局 ID 的时候，每次单点服务都向数据库批量申请 n 个 ID，在本地用内存维护这个号段，并把数据库中的 ID 修改为当前值 +n，直到这 n 个 ID 被耗尽；下次需要产生新的全局 ID 的时候，再次到数据库申请一段新的号段

拆分策略

使用水平拆分时，操作一条数据，要在哪张表找到它

拆分后的问题

一个查询的执行顺序

(8) SELECT (9)DISTINCT<select list>(1) FROM <left table>(3) <join_type>JOIN<right_table>(2) ON<join condition>(4) WHERE<where condition>(5) GROUP BY<group_by_list>(6) WITH (CUBE|ROLLUP)(7) HAVING<having condition>(10) ORDER BY<order by_list>(11) LIMIT <limit number>