数据库性能优化培训

基础知识
• 聚簇索引和非聚簇索引
基本知识
• 访问数据只有两种基本方式 1.顺序扫描
– 全表扫描 – 分区扫描 2.通过索引访问数据 – 访问索引，然后访问表 – 只访问索引（fast index scan）
基本知识
• 表连接
MySQL只实现了嵌入式循环连接（nested loop join） 性能取决于连接项是否有索引、取回的数量是否 巨大 From后面表的书写顺序不影响性能，mySQL能 够自行选择驱动表。
避免读写冲突
• myISAM存储引擎缺点 ---表级锁，读写互斥，并发效率低 • 每次读取少量数据，多次读取 • 打开concurrent_insert功能 ---只对insert有效 • 降低写的优先级 ---low-priority-updates=1和max_write_lock_count • 换用innodb存储引擎 ---使用索引访问数据用行锁，否则表锁
子查询优化
• 不是所有子查询都能够将子查询上推至主 查询，在能合并到主查询时，应当使用表 连接的方式，而不是使用子查询。慢查询 中出现的子查询基本都可以通过改写来大 幅提升查询效率。 • 主流数据库基本都解决了子查询的性能问 题，方法是查询重写（Query Rewrite）。 mySQL也使用了重写，但重写不成功。
通用SQL优化（举例）
通用SQL优化（举例）
通用SQL优化（举例）
通用SQL优化（进一步讨论）
• 使用MySQL时，为了得到合理的执行计划， 需要使用hint。这使得本该对应用程序屏蔽 的执行计划暴露给了执行计划。 • 当数据分布变化时，需要更改SQL语句。 这是一个硬伤。 • 其它先进的数据库如何解决这个问题（data distribution is skewed） • ORACLE和PostgreSQL的统计信息和 histgram（bucket）
系统优化
• 三个数据缓存 • 表缓存和线程缓存 • 两个日志缓存
架构优化
• • • • mySQL复制 DRDB 利用缓存 分库
基本查询举例
• 全表扫描
+--------------+-------------+--------------+ | table_name | data_length | index_length | +--------------+-------------+--------------+ | comment_star | 531921 | 39936 | +--------------+-------------+--------------+ 1 row in set (0.01 sec) mySQL> select avg(upcount) from comment_star; +--------------+ | avg(upcount) | +--------------+ | 0.0000 | +--------------+ 1 row in set (0.03 sec)
数据类型不匹配
• 建有索引的列，在写查询条件时应当尽量 匹配列的定义 • 例如，列存放的是字符类型，在写查询条 件是尽量用字符类型，而不是使用数值
பைடு நூலகம்
数据类型不匹配
• 表file_delete中的fmt列是varchar型，不是数值型 • mySQL> select SQL_no_cache * from file_delete where host='60.29.226.133' and status=0 and deltime<1311782096 and fmt!='2' and fmt in ('0','1','3','9','10') order by id limit 100; • Empty set (0.02 sec) • mySQL> select SQL_no_cache * from file_delete where host='60.29.226.133' and status=0 and deltime<1311782096 and fmt!=2 and fmt in (0,1,3,9,10) order by id limit 100; • Empty set (3.11 sec)
基本查询举例
• 通过索引扫描
性能取决于返回的数据量的大小，几百行数据一 般只需0.0X秒
root@localhost ptv >select id,uid,content from comment_video where id = '2011'; +------+-----+------------------------------------------------+ | id | uid | content | +------+-----+------------------------------------------------+ | 2011 | 0 | 小江涛~哇哈哈哈哈 还有辉哥~哈哈 | +------+-----+------------------------------------------------+ 1 row in set (0.01 sec)
基本查询举例
• 全表扫描
性能决定于表的大小，类似的扫描性能决定于取 回数据的多少
| table_name | table_rows | avg_row_length | data_length | index_length | +---------------+------------+----------------+-------------+--------------+ | comment_video | 3706189 | 897 | 3324451533 | 224796672 | +---------------+------------+----------------+-------------+--------------+ 1 row in set (0.66 sec) mySQL> select SQL_no_cache avg(upcount) from comment_video; +--------------+ | avg(upcount) | +--------------+ | 0.4882 | +--------------+ 1 row in set (46.29 sec)
完成查询的关键三要素
• SQL语句 • 需要访问的数据总量和分布 • 数据库管理系统的配置
mySQL经验优化
• 子查询优化 • 避免数据类型转换 • 避免读写冲突
子查询优化
• mySQL经常不能得到合理的执行计划 • 子查询能上推到主查询时应当上推
SELECT * FROM filmlib_rel_vg_mid WHERE vgid IN (SELECT vgid FROM filmlib_rel_album_vg WHERE aid=25442); ... 18 rows in set (2.54 sec) root@localhost mms 11:38:11>SELECT * FROM filmlib_rel_vg_mid m,filmlib_rel_album_vg v WHERE m.vgid=v.vgid and v.aid=25442; ... 18 rows in set (0.00 sec)
数据管理类调优根本
• 理解底层原理，知道参数背后的含义
事务考虑
• • • • 及时提交 分区表 关注日志缓存 防止并发操作线性化
数据库执行逻辑
• 在查询出现严重性能问题时应当考虑数据 库执行的真实过程 • 例如，子表没建索引，父表删除困难
业务逻辑优化
• 合理的应用设计是系统优化的关键，其作 用远比SQL调优和数据库管理系统调优重 要 • ORACLE副总裁Tomas kyte说：系统如果 设计成拖拉机，我不能把它调整成赛车 • 淘宝DBA简朝阳说：业务实现的优化占总 体效果的55% P102 • 平衡衍生数据对读取的益处和写入数据的 成本增加。 • 要避免写入数据的串行化。
数据库性能优化培训
2011/08/15 李天凡
数据库性能优化
• • • • 查询优化 业务逻辑优化 系统优化 架构优化
查询优化
• • • • • 基本知识 mySQL经验优化 通用SQL优化 数据库执行逻辑 事务相关优化
基本知识
• 数据放在表中 • mySQL利用聚簇索引的方式存放数据 表中的数据按主键的顺序存放 第二索引不是聚簇索引 • ORACLE等商业数据库缺省时随机存放 表中的数据没有顺序 所有的索引都不是聚簇索引 可以强制数据顺序存放（IOT）
业务逻辑优化（举例）
• 举例：集团用户管理中的权限管理 • 预先生成每个用户的权限，避免用户集中 登录时通过多表连接实时生成权限 • 权限的改变的频率相对读取频率非常小， 而且更改并不集中
业务逻辑优化（举例）
• • • • 需求：一个论坛帖子总量的统计 附加要求：实时更新 执行成本昂贵select count(*) from tablea; 不能使用一个量纪录更新，会导致并发操 作线性化 • 关键：实时更新没必要
评价访问方式
• 考虑获取数据的时间