数据库优化

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

优化原因

优化方面

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

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: index
possible_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

何时使用索引

索引优化

数据库结构优化

2020310201242

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

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

2020310201126

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

分库分表

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

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

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

一些分库分表中间件

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

系统迁移到分库分表

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

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

批注 2020-03-20 163424

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

同时后台开启一个数据库迁移工具，这个工具负责把老库的数据写到新库去，写到新库的条件是，老库有的数据新库没有或者是 老库的数据更新时间比新库的新

工具会比较新库与老库的每一条数据，只有每条数据都一致，才算完成，否则继续新一轮迁移

这样工具几轮操作过去后，新老库的数据就一致了

批注 2020-03-20 163921

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

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

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

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

2022816205127

唯一ID生成

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

拆分策略

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

拆分后的问题

数据访问优化

减少请求的数据量

减少扫描行数

使用索引覆盖来覆盖查询

查询方式优化

分解大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源

分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联

配置优化

执行顺序