数据库优化

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

优化原因

  • 避免网站出现访问错误
  • 低效的查询导致数据库不稳定
  • 优化用户体验

优化方面

  • 硬件
  • 系统配置
  • 数据库表结构
  • SQL与索引

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

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

问题定位

  • 次数多、时间长
  • IO大
  • 未命中索引

查询执行计划

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
  • id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
  • select_type : SELECT 查询的类型.

    • SIMPLE, 表示此查询不包含 UNION 查询或子查询
    • PRIMARY, 表示此查询是最外层的查询
    • UNION, 表示此查询是 UNION 的第二或随后的查询
    • DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
    • UNION RESULT, UNION 的结果
    • SUBQUERY, 子查询中的第一个 SELECT
    • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.
  • table: 查询的是哪个表

  • partitions: 匹配的分区

  • type: join 类型 通常来说, 不同的 type 类型的性能关系:ALL < index < range ~ index_merge < ref < eq_ref < const < system
  • possible_keys: 此次查询中可能选用的索引
  • key : 此次查询中确切使用到的索引
  • key_len:表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到
  • rows :估算 SQL 要查找到结果集需要扫描读取的数据行数,这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好
  • extra:EXplain 中的很多额外的信息会在 Extra 字段显示

    • Using filesort:表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果,一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大
    • Using index:"覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
    • Using temporary:查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化

索引优化

索引

创建索引

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

何时使用索引

  • 主键列中创建索引
  • 多表连接时连接列创建索引
  • where子句查询的列
  • 需要经常GROUP BY和ORDER BY的列

索引优化

  • 找出重复冗余索引
  • 索引不包含NULL
  • 短索引
  • 排序的索引问题
  • like语句前%不会使用索引
  • 列上运算问题
  • NOT IN会进行全表扫描

数据库结构优化

  • 选择合适的数据类型
  • 范式化
  • 反范式化
  • 垂直拆分

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

分库分表后的ID

  • 使用一个系统来做自增ID的获取
    • redis、数据库自带的自增
    • 多个节点的ID获取无法并行
  • 不同的数据自增ID设置相同的步长不同的初始值,这样就能保证这些节点ID不会重复
    • 但这种方式注定了数据库节点数量不能变化
  • uuid
    • UUID组成部分:当前日期和时间+时钟序列+随机数+全局唯一的IEEE机器识别号
    • 比较长,无法保证趋势递增,做索引时查询效率低
  • 系统时间
    • 可以使用业务字段来拼接避免重复
  • 雪花算法
    • 一个 64 位的 long 型的 id,1 个 bit 是不用的,用其中的 41 bit 作为毫秒数,用 10 bit 作为工作机器 id,12 bit 作为序列号
    • 单个节点内无法并行
    • 多个节点可以并行
    • 可以支撑每秒几万的情况

拆分策略

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

  • 哈希取模
  • 范围,ID范围,时间范围
  • 映射表

拆分后的问题

  • 事务
    • 使用分布式事务
  • 连接
    • 原来的连接需要分解成多个单表查询,在应用层进行连接
  • ID唯一性
    • 全局唯一ID(GUID)
    • 每个分片指定ID范围
    • 分布式ID生成器,雪花算法

数据访问优化

减少请求的数据量

  • SELECT 只返回必要的列
  • 使用LIMIT只返回必要的行
  • 在内存缓存数据避免查询数据库

减少扫描行数

使用索引覆盖来覆盖查询

查询方式优化

分解大查询

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

分解大连接查询

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

  • 可以有效利用缓存
  • 减少锁竞争
  • 应用层拼接数据,数据库拆分更容易,从而做到高性能和可伸缩
  • 单表查询效率可能比连接高

配置优化

  • 设置文件最大打开数
  • 设置最大连接数
  • 设置back_log

    • 存放等待连接的堆栈大小
  • interactive_timeout

  • 缓冲区

    • key_buffer_size
    • query_cache_size
    • record_buffer_size
    • read_rnd_buffer_size
    • sort_buffer_size
    • join_buffer_size
    • tmp_table_size
    • table_cache
    • max_heap_table_size
    • thread_cache_size
    • thread_concurrency
    • wait_timeout
  • 关于InnoDB

执行顺序

  • FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
  • ON: 对虚表VT1进行ON筛选,只有那些符合 < join-condition > 的行才会被记录在虚表VT2中。
  • JOIN: 如果指定了OUTER JOIN(比如left join、 right join),那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中,产生虚拟表VT3, rug from子句中包含两个以上的表的话,那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤,一直到处理完所有的表为止。
  • WHERE: 对虚拟表VT3进行WHERE条件过滤。只有符合 < where-condition > 的记录才会被插入到虚拟表VT4中。
  • GROUP BY: 根据group by子句中的列,对VT4中的记录进行分组操作,产生VT5.
  • CUBE | ROLLUP: 对表VT5进行cube或者rollup操作,产生表VT6.
  • HAVING: 对虚拟表VT6应用having过滤,只有符合 < having-condition > 的记录才会被 插入到虚拟表VT7中。
  • SELECT: 执行select操作,选择指定的列,插入到虚拟表VT8中。
  • DISTINCT: 对VT8中的记录进行去重。产生虚拟表VT9.
  • ORDER BY: 将虚拟表VT9中的记录按照 < order_by_list > 进行排序操作,产生虚拟表VT10.
  • LIMIT:取出指定行的记录,产生虚拟表VT11, 并将结果返回。

results matching " "

No results matching " "

results matching " "

No results matching " "