MySQL 深入优化


Mysql 深入优化 dba&qa Mysql 深入优化 本文档部分基于 jay pipesjay pipesjay pipesjay pipes的演讲稿: Performance coding for mysql Performance coding for mysql Performance coding for mysql Performance coding for mysql 本文档部分测试结果来自网络,一并对原作者表 示感谢 mysql mysql mysql mysql 的系统架构 的系统架构 的系统架构 的系统架构 •高度耦合的子系统 •基于链接的内存分配 (相对于全局内存模块) •各种等级的 cache •基于代价的优化器 •模块化的存储引擎 schema 设计的基本原则 •关系应用性能的基础构件 •先设计基本系统,在设计特殊系统 •尽可能的小(表,字段,数据类型) --abc原则 •分而治之 •清楚各种存储引擎的优缺点 基础搞不好,建不起高楼 the more records you can fit into a page in memory /disk the faster performance you can do •是不是真的需要一个 bigint的类型的主键? innodb 引擎主键越小越好 •使用 int unsigned not null 来处理 ip地址 •谨慎的使用 varchar 数据类型 myisam 用char innodb 用varchar 临时表用 char 代替 varchar •尽量少的使用 text 类型 如果有必要,还是考虑分表吧 •坚决抵制使用 blob数据类型 如有必要,还是尽量用文件系统的文件来存储吧。 smaller ,smaller ,smaller smaller ,smaller ,smaller smaller ,smaller ,smaller smaller ,smaller ,smaller 以小定天下 小原则之 ipipipip地址的处理 INET_ATON() 把字符串转为数字 INET_NTOA() 把数字转为字符串 分而治之 分而治之 分而治之 分而治之 •垂直分表 把有很多列的表分成多个表 •水平分表 把有很多行的表分成多个表 •不同的情况考虑不用的分表原则 •mysql 5.0 之后的分区功能是就是 一个对应用透明的水平分表功能 垂直分表 垂直分表 垂直分表 垂直分表 #1 #1 #1 #1 •第一个表是基本数据,第二个表是额外数据且数据量较大 •额外信息这块很多数据都是 null或者很少被访问 •考虑到结果所占的内存空间的尺寸 分成的一级信息表,尺寸较小,一个内存页能装下更多的数据。 查询速度也快 垂直分表 #2 #2 #2 #2 •把不变的数据分成一个表 •计数数据经常修改,分成一个表 •考虑到 cache的效果 不变部分的数据可以很好的缓存到 query cache 中 而对计数数据的查询加 hint sql_no_cache 不写 query cache 提高性能 •innodb 表对不带条件的 count()的操作很差,建立一个对应 count表 垂直分表 垂直分表 垂直分表 垂直分表 #3#3#3#3 •我们目前的 article article_ontent 模式是值得推荐的 •一个不好的例子: 女性网晒客 : sk_dinary 单行数据包括 2个clob/text 字段, 8个255,1个1024,1个4000 的vavarchar2 单行数据超过 8k ,在oracle这边已经超过 一个数据块的尺寸, 如果迁移到 mysql,将是很大的考验。 垂直分表 垂直分表 垂直分表 垂直分表 #4----example#4----example#4----example#4----example 垂直分表 垂直分表 垂直分表 垂直分表 #4----example#4----example#4----example#4----example 垂直分表 垂直分表 垂直分表 垂直分表 #4----example#4----example#4----example#4----example 垂直分表 垂直分表 垂直分表 垂直分表 #4----example#4----example#4----example#4----example query cache query cache query cache query cache •必须确定应用的读写比例 •query cache 的设计原则是 CPU与读性能的一个 平衡点 •query cache 是基于 select 语句的 hash 结果集 并 且严格区分 sql的版本 严格区分大小写,空格数量 相同的 sql 当前库不同,亦不匹配 client的字符集不同也要 cache 两份数据 •对sql 语句中表的任何修改都将使结果集失效。 •写多的应用不应该使用 query cache query_cache_type=0 query_cache_size=0 index ---数据库黄页 •提高查询速度,但是会影响数据修改的速度 不是索引越多越好 •确保 where ,on ,group by ,order by 这些字句的字段在索引上 •确保 join 的字段上有索引,且数据类型是一致的 数据类型不一致,会有隐含的数据转换,增加开销 •注意列的顺序 原则上按照在表中的顺序。 •索引覆盖 (covering index) 当一个 select 所查询的所有字段都在索引记录中的时候, 不会再去从表中查询数据,直接从索引返回。 •删除不需要 /重复的索引 •注意列在索引中的排列顺序 只有最左边的列出现在 where条件中,才有可能走索引 •理解各种存储引擎中索引记录的组织形式 index ---数据库黄页 myisam myisam myisam myisam 引擎索引结构 •非cluster 数据结构 •会出现空洞 需要定期整理 •....myd 文件存放表记录 表数据是无序存放的 •.myi .myi .myi .myi 文件存放所有的索引 索引记录一个指针指向 .myd文件 中具体的数据记录 index ---数据库黄页 innodb innodb innodb innodb 引擎索引结构 引擎索引结构 引擎索引结构 引擎索引结构 •cluster 数据结构 (主键索引与数据存放在一起) 二级索引是 Btree结构 叶子节点是存储指向主键的指针而非指向数据 •数据页为 16KB 数据页包含主键索引的叶子节点(数据 +索引值) 每个数据页都有个字典(位于数据页的尾部) 存储排过序的主键索引值与指向数据页内具体记录的指针 •主键字段越小越好。 所有的二级索引都会存储主键索引值 •如果没有指定主键,系统会自动制定一个唯一的索引作为主键。 索引对查询效率的影响索引对查询效率的影响索引对查询效率的影响索引对查询效率的影响 ---1 ---1 ---1 ---1 过度索引是如何影响数据库的性能的呢? 1。 在执行 sql之前,数据库会根据 metadata信息决定该使用哪个索引,如果索引过多会影响这一 步骤的效率。 2。 由于每次数据更新和插入都要更新索引,因此会影响相关操作的效率 测试环境: drop table if EXISTS test_index_performance; CREATE TABLE test_index_performance ( id int primary key , col1 varchar(10), col2 varchar(10), col3 varchar(10), col4 varchar(10), col5 varchar(10), col6 varchar(10), col7 varchar(10), col8 varchar(10), col9 varchar(10), col10 varchar(10) )engine=innodb; create PROCEDURE insert_data_for_test_index_performance () begin DECLARE total int default 100000; DECLARE i int default 0; truncate table test_index_performance; while(i < total) do insert into test_index_performance values (i, ’a',’a',’a',’a',’a',’a',’a',’a',’a',’a'); set i=i+1; end while ; end $$ delimiter ; call insert_data_for_test_index_performance(); 索引对查询效率的影响 索引对查询效率的影响 索引对查询效率的影响 索引对查询效率的影响 ---2 ---2 ---2 ---2 测试 1 create index idx1 on test_index_performance (col1); create index idx2 on test_index_performance (col1,col2); create index idx3 on test_index_performance (col1,col2,col3); create index idx4 on test_index_performance (col1,col2,col3,col4); create index idx5 on test_index_performance (col1,col2,col3,col4,col5); create index idx6 on test_index_performance (col1,col2,col3,col4,col5,col6); create index idx7 on test_index_performance (col1,col2,col3,col4,col5,col6,col7); create index idx8 on test_index_performance (col1,col2,col3,col4,col5,col6,col7,col8); create index idx9 on test_index_performance (col1,col2,col3,col4,col5,col6,col7,col8,col9); create index idx10 on test_index_performance (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10); 执行以下语句 select count(*) from test_index_performance where col1=’a’ ; – show profile for query 1; 结果的 statistics部分 – 1索引 0.000070 – 2索引 0.000083 – 3索引 0.000107 – 4索引 0.000112 – 5索引 0.000126 – 6索引 0.000155 – 7索引 0.000152 – 8索引 0.000164 – 9索引 0.000187 结果一:与执行计划相关的索引(出现在 possible keyspossible keyspossible keyspossible keys的那些), 索引的数量与 sqlsqlsqlsql执行消耗时间成正比。 索引对查询效率的影响 索引对查询效率的影响 索引对查询效率的影响 索引对查询效率的影响 ---3 ---3 ---3 ---3 测试 2 create index idx12 on test_index_performance (col2); create index idx13 on test_index_performance (col2,col3); create index idx14 on test_index_performance (col2,col3,col4); create index idx15 on test_index_performance (col2,col3,col4,col5); create index idx16 on test_index_performance (col2,col3,col4,col5,col6); create index idx17 on test_index_performance (col2,col3,col4,col5,col6,col7); create index idx18 on test_index_performance (col2,col3,col4,col5,col6,col7,col8); create index idx19 on test_index_performance (col2,col3,col4,col5,col6,col7,col8,col9); create index idx20 on test_index_performance (col2,col3,col4,col5,col6,col7,col8,col9,col10); 结果二: 与执行计划无关的索引(不出现在 possible keyspossible keyspossible keyspossible keys的那些), 不会影响 sqlsqlsqlsql的执行效率。 索引对查询效率的影响 索引对查询效率的影响 索引对查询效率的影响 索引对查询效率的影响 ---4 ---4 ---4 ---4 测试 3 表大小对查询影响 select count(*) from test_index_performance where col1=’a’ ; – show profile for query 1; 结果的 statistics部分 – 1w条 0.000187 – 10w条 0.000192 – 20w条 0.000198 – 30w条 0.000192 结果三: 表的大小,与索引对于 sqlsqlsqlsql执行效率的影响,没有直接联系 索引对查询效率的影响 索引对查询效率的影响 索引对查询效率的影响 索引对查询效率的影响 ---5 ---5 ---5 ---5 总结: 1111。与本条语句执行相关的 indexindexindexindex的数量( possible keypossible keypossible keypossible key),会影响最终效率 2222。对效率的影响体现在, statisticsstatisticsstatisticsstatistics阶段 3333。原因在于优化器需要从 information_schemainformation_schemainformation_schemainformation_schema中获取相关索引的 metadatametadatametadatametadata信息 并分析,索引数量越多,这个过程越漫长 4444。与本条语句执行无关的 indexindexindexindex数量不影响最终效率 5555。 效率影响在 10%10%10%10%左右 coding coding coding coding 原则 •不要考虑迭代例如( for loop , while loop 这类的实现) •thingking in terms of sets (用集合的思想?) •把复杂的 sql语句或者业务逻辑拆分成小的,可管理的模块 •把几个语句合在一个事务中提交,但要控制事务的大小 减少 commit的次数 •下面是一个具体的事例 sets-wise thinking-- sets-wise thinking-- sets-wise thinking-- sets-wise thinking-- 集合思想的事例 •事例: 查出每个客户的最后的支付信息 很多人的想法: 1.从每个人的支付记录里找出最后的支付日期 2.有2个数据集一个是最后的支付日期, 一个是支付信息 (如何关联这个结果集?) •两个 sql的执行计划,注意他们所耗费的时间 0.00 vs 0.01 秒, 似乎第一个看起来更好,也符合我们的常规思维。 sets-wise thinking-- sets-wise thinking-- sets-wise thinking-- sets-wise thinking-- 集合思想的事例 #2#2#2#2 •结果出来了,第二个 sql sql sql sql 比第一个快 5555倍。 1.主表只有 16000笔记录 -----随着表数据量的增长,性能会更差 2.衍生表即使达到百万级别的数据,性能也依然很好 索引列上的函数操作 索引列上的函数操作 索引列上的函数操作 索引列上的函数操作 #1#1#1#1 •优化器选择了索引上 range scan range scan range scan range scan 的访问策略, 并且 速度也不错 也只有这种形式的 ‘str%' 可以走索引 ’%str%' 则无法走索引 •走了全表扫描,性能很差,并且与数据量正相关。 使用了 left()函数,没有走索引 考虑加一个函数的索引? 索引列上的函数操作 #2#2#2#2 SELECT * FROM Orders WHERE TO_DAYS(CURRENT_DATE()) – TO_DAYS(order_created) <= 7; 在索引字段( order_created) 字段上使用了函数操作,我们修改为下面的模式 SELECT * FROM Orders WHERE order_created >= CURRENT_DATE() - INTERVAL 7 DAY; 我们改写了语句,可以走索引了,但是 where 条件中 current_date()仍然是个不 确定的值,结果集无法从 query cache 中重用。再改写下 SELECT * FROM Orders WHERE order_created >= '2008-01-11' - INTERVAL 7 DAY; 我们用具体的值代替了 current_date()使得这个 sql语句的所有部分变成了确认的值, 结果集可以 cache到qery cache 中被重用 但是字段中有个 text类型 占用空间很大,怎么办? 只取我们需要的字段。 SELECT order_id, customer_id, order_total, order_created FROM Orders WHERE order_created >= '2008-01-11' - INTERVAL 7 DAY; 字段上的计算 对email字段进行翻转处理后生成索引,在查询的时候,利 用索引避免全表扫描 避免删除操作 •避免对表做删除操作 尤其是对 myisam 引擎的表 •应对方法: 建立一个表 deleted_roews 记录要删除的记录 •定期的执行删除操作: DELETE from main_table INNER JOIN deleted_rows ON main_table.id = deleted_rows.id; TRUNCATE TABLE deleted_rows; •分析表 OPTIMIZE TABLE main_table; •对数主表数据的查询: SELECT .. FROM main_table LEFT JOIN deleted_rows ON main_table.id = deleted_rows.id WHERE deleted_rows.id IS NULL 关于存储过程 •存储过程的 cache 是基于 session 的 •不要把简单的 select 语句包装成,存储过程 •在非共享结构中( shared-nothing architecture) 会有性能问题 例如 php将可能导致灾难( php是shared-nothing的架构) •使用范围: ETL或者复杂的数据收集 sql 重复执行的 sql 批量的操作 命中率 命中率 命中率 命中率 命中率反应了 mysqlmysqlmysqlmysql数据库的效率 ● Query cache hit ratio: Qcache_hits / (Com_select + Qcache_hits) ● MyISAM key buffer hit ratio: Key_read_requests / Key_reads ● InnoDB buffer pool hit ratio: Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads Mysql Mysql Mysql Mysql 深入优化 深入优化 深入优化 深入优化 ---Explain---Explain---Explain---Explain Explain Explain Explain Explain 都能提供什么信息呢? 表的读取顺序 每个表都是如何读取的 可能用到哪些索引,实际使用了哪些索引 表是如何引用的 查询优化器从每个表中预计读取的记录数 其他额外信息,例如是否使用了内存表,是否引 发排序等 explain explain explain explain •把explain 命令加到 select 语句的前面 就能返回优化器为这个 sql选择的执行计划 返回的每一行都代表这个 select语句所使用到的一系列的信息 EXPLAIN EXPLAIN EXPLAIN EXPLAIN 各行的含义 各行的含义 各行的含义 各行的含义 •select_type :查询的类型 •table : 表名,表的别名,或者衍生表( derived table) •type : 访问策略 •possible_key: 优化器可能用到的索引 •key : 优化器使用的索引名字 •key_len : 索引记录的长度 •rows : 估算的需要处理的记录数 •extra : 关于 join条件的一些额外的信息 •ref : 哪一个索引列或者列被用来从表中选择记录 EXPLAIN EXPLAIN EXPLAIN EXPLAIN 各行的含义 ----SELECT_TYPE----SELECT_TYPE----SELECT_TYPE----SELECT_TYPE •SIMPLE: 简单查询,(没有子查询或者 union) •PRIMARY: 有外部的查询 •UNION : union 语句的第 2个或最后一个 sql •DEPENDENT UNION : 语句的第 2个或最后一个 sql 但是依赖于外面的查询 •UNION RESULT :union 的结果集 •SUBQUERY : 子查询中的第一个 select •DEPENDENT SUBQUERY: 子查询中的第一个 select 但是依赖于外部查询 •DERIVED : 衍生表 (在 from 中的子查询) EXPLAIN EXPLAIN EXPLAIN EXPLAIN 各行的含义 --type--type--type--type •system: system: system: system: 这个表只有一行数据 •const : const : const : const : 表中只有一行符合条件的记录( 可能有其他记录但不符合条件) •eq_ref :eq_ref :eq_ref :eq_ref : 对于驱动表的每一行记录,只有一行比较一般使用主键索引与驱动表关联 •ref :ref :ref :ref :对于驱动表的每一行记录,表里有几行记录与之比较: n:1的关系 •ref_or_null :ref_or_null :ref_or_null :ref_or_null :跟ref相同,只是表中还可能有 null值 •index_mergeindex_mergeindex_mergeindex_merge: index merge (后面会有个说明) •unique_subquery:unique_subquery:unique_subquery:unique_subquery: 跟ref相同,只用于 in () 列表,一般是主键索引列用 in ( ) 列表条件 •index_subquery index_subquery index_subquery index_subquery :跟unique_subquery相同,只是 in() 在普通索引列上 •range range range range : 在索引列上的条件是 >/< 这样的比较运算 •index : index : index : index : 索引的全扫描 •all : all : all : all : 全表扫描 EXPLAIN EXPLAIN EXPLAIN EXPLAIN 各行的含义 --EXTRA--EXTRA--EXTRA--EXTRA •Using indexUsing indexUsing indexUsing index : covering index 我们前面刚提到的直接从索引记录里取值 下面我们会专门说这个 •Where usedWhere usedWhere usedWhere used: mysql用where 条件去验证符合条件的记录, 如果不用 where 条件会得到整个表的全部记录 •Distinct Distinct Distinct Distinct :mysql 从表中只取一行符合条件的记录 •Not exists Not exists Not exists Not exists : mysql 使用了 left join "miss rows" •Using filesortUsing filesortUsing filesortUsing filesort: 使用了问价排序这是很不好的执行计划 •Using temporaryUsing temporaryUsing temporaryUsing temporary: 使用了临时表 这也是一个不很好的执行计划 •Range checked for each recordRange checked for each recordRange checked for each recordRange checked for each record: mysql无法确定使用哪个索引, 对于驱动表的每一行都执行一个范围扫描 Mysql 深入优化 Mysql 深入优化 Mysql 深入优化 Mysql 深入优化 Mysql 深入优化 explain -----merge_indexexplain -----merge_indexexplain -----merge_indexexplain -----merge_index •在type type type type 栏目显示的 index index index index 和 在EXtra EXtra EXtra EXtra 栏目显示的 indexindexindexindex是有很大不同的: type: index :表示走的是全索引扫描是一个很差的执行计划 EXTRA: index : 表示走的 covering index 是一个很好的执行计划 两者不能混谈。 •5.05.05.05.0及其以后的版本优化器增加一个 index_mergeindex_mergeindex_mergeindex_merge的策略 之前的版本最多只能选择一个索引。 merge_index merge_indexmerge_indexmerge_indexmerge_index 来自 mysqlmysqlmysqlmysql官方网站的建议: http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.htmlhttp://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.htmlhttp://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.htmlhttp://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30; For this query, two plans are possible: An Index Merge scan using the (goodkey1 < 10 OR goodkey2 < 20) condition. A range scan using the badkey < 30 condition. However, the optimizer considers only the second plan. If your query has a complex WHERE clause with deep AND/OR nesting and MySQL doesn't choose the optimal plan, try distributing terms using the following identity laws: (x AND y) OR z = (x OR z) AND (y OR z) (x OR y) AND z = (x AND z) OR (y AND z) Mysql 深入优化
还剩48页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享pdf获得金币 ] 1 人已下载

下载pdf

pdf贡献者

spj2007

贡献于2012-06-25

下载需要 10 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf