数据库优化
对于优化最重要的事是测量,如果优化的成本高于收益,就要停止优化。
优化原因
- 避免网站出现访问错误
- 低效的查询导致数据库不稳定
- 优化用户体验
优化方面
- 硬件
- 系统配置
- 数据库表结构
- 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: indexpossible_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会进行全表扫描
数据库结构优化
- 选择合适的数据类型
- 范式化
- 反范式化
- 垂直拆分
使用垂直切分将按数据库中表的密集程度部署到不同的库中
切分后部分表无法join,只能通过接口方式解决,提高了系统复杂度,存在分布式事务问题
- 水平拆分
当一个表的数据不断增多时,水平拆分是必然的选择,它可以将数据分布到集群的不同节点上,从而缓存单个数据库的压力
分库分表
同上面的水平拆分,每张表或者每个库只存储一定量的数据,当需要进行数据读写时,根据唯一ID取模得到数据的位置
为什么分库分表能提高性能
将一张表的数据拆分成多个n张表进行存放,然后使用第三方中间件(MyCat或者Sharding-JDBC)可以并行查询
一些分库分表中间件
cobar,tddl,atlas,sharing-jdbc,my-cat
系统迁移到分库分表
如何将一个单裤单表的系统动态迁移到分库分表上去
- 停机迁移
禁止全部数据写入,编写一个程序,将单库单表的数据写到分库分表上
- 双写迁移
新系统部署后,每条数据都会在老库和新库写一遍
同时后台开启一个数据库迁移工具,这个工具负责把老库的数据写到新库去,写到新库的条件是,老库有的数据新库没有或者是 老库的数据更新时间比新库的新
工具会比较新库与老库的每一条数据,只有每条数据都一致,才算完成,否则继续新一轮迁移
这样工具几轮操作过去后,新老库的数据就一致了
动态扩容缩容的分库分表方案
- 停机扩容
同上,只不过上面那是从单个数据库到多个数据库,这次这个是多个数据库到多个数据库但是不推荐这种做法,原因是数据量很大,数据很难在短时间内转移完毕
- 第一次分库分表,就一次性给他分个够
32 个库,每个库 32 个表这里可以多个库都在同一台机器上,当不够用的时候,可以将这些库转移到新机器上这样,数据的逻辑位置没有发生改变,也避免扩容缩容带来的数据迁移问题
- 级联同步迁移
比较适合数据从自建机房向云上迁移的场景,在切写的时候需要短暂的停止写入
唯一ID生成
- 使用一个单点系统来做自增ID的获取
- 必须要能应对时钟回拨,或者服务器异常重启之后计数器不会重复的问题
- redis、数据库自带的自增
- 多个节点的ID获取无法并行
- 使用多个单点系统,每个系统自增ID设置相同的步长不同的初始值,这样就能保证这些节点ID不会重复
- 但这种方式注定了节点数量不能变化
为了避免每次生成都需要一次调用,在需要产生新的全局 ID 的时候,每次单点服务都向数据库批量申请 n 个 ID,在本地用内存维护这个号段,并把数据库中的 ID 修改为当前值 +n,直到这 n 个 ID 被耗尽;下次需要产生新的全局 ID 的时候,再次到数据库申请一段新的号段
- UUID
- UUID组成部分:当前日期和时间+时钟序列+随机数+全局唯一的IEEE机器识别号
- 比较长,无法保证趋势递增,做索引时查询效率低
- 系统时间
- 可以使用业务字段来拼接避免重复
- 雪花算法
- 一个 64 位的 long 型的 id,第一个 bit 是不用的,用其中的 41 bit 作为毫秒数,用 10 bit 作为工作机器 id,12 bit 作为序列号
- 单个节点内无法并行
- 多个节点可以并行
- 可以支撑每秒400万+的ID生成,在某些实现里,如果某一毫秒内的计数器被耗尽达到上限,会死循环直至这 1ms 过去
拆分策略
使用水平拆分时,操作一条数据,要在哪张表找到它
- 哈希取模
- 范围,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筛选,只有那些符合`
`的行才会被记录在虚表VT2中。 - JOIN: 如果指定了OUTER JOIN(比如left join、 right join),那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中,产生虚拟表VT3, rug from子句中包含两个以上的表的话,那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤,一直到处理完所有的表为止。
- WHERE: 对虚拟表VT3进行WHERE条件过滤。只有符合`
`的记录才会被插入到虚拟表VT4中。 - GROUP BY: 根据group by子句中的列,对VT4中的记录进行分组操作,产生VT5.
- CUBE | ROLLUP: 对表VT5进行cube或者rollup操作,产生表VT6.
- HAVING: 对虚拟表VT6应用having过滤,只有符合`
`的记录才会被 插入到虚拟表VT7中。 - SELECT: 执行select操作,选择指定的列,插入到虚拟表VT8中。
- DISTINCT: 对VT8中的记录进行去重。产生虚拟表VT9.
- ORDER BY: 将虚拟表VT9中的记录按照`
`进行排序操作,产生虚拟表VT10. - LIMIT:取出指定行的记录,产生虚拟表VT11, 并将结果返回。