- 1. MySQL优化Begin for qingwei qingwei@taobao.com 淘宝网
- 2. 摘要查询优化 Mysql性格探测 索引简介 Explain语法 配置参数调优 Tips mysqlDBA常用配置项目
- 3. 查询优化1.缓存类型:表缓存(table_cache ), SHOW STATUS LIKE ’Opened_tables’; 某个数据表必须打开的次数 。如果这个数字增长得很快，就意味着这个缓存太小了 2. 索引使用: a.但是减慢了插入和删除的速度 b.索引会花费磁盘空间 . 对于MyISAM表频,繁地索引可能引起索引文件比数据文件更快地达到最大限制 . InnoDB, InnoDB共享表空间并不受操作系统的文件大小限制 c.数据的基数，包含不同值的数量,列中包含很多不同的值，重复的值很少 ,工作效率越好 d.索引尽可能短,更少的I/O, InnoDB和BDB等使用聚簇索引,数据行和主键值存储在一起,其它的索引都是次级索引 ,它们被用于定位数据行 ，如果主键值很长，每个次级索引就需要更多的额外空间 e.使用最左（leftmost）前缀 . state, city, zip state, city state
- 4. 查询优化1.IS NULL 与 IS NOT NULL 不能用null作索引，任何包含null值的列都将不会被包含在索引中 2.select * from employee where last_name like ‘%cliton%’ ，不能使用索引 3.In和exists EXIST,系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项 , IN首先执行子查询，结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询 mysql -udpd -h192.168.205.62 -pdpd -P3307 dpd_magazine select id from item where status=1 and not exists (select item_id from item_file where item_file.item_id=item.id); select id from item where status=1 and id not in (select item_id from item_file);
- 5. 查询缓冲1.Mysql查询缓冲变量 SHOW STATUS LIKE 'qcache%'; Qcache_free_blocks中相邻内存块的个数,数目大可能有碎片 Qcache_free_memory缓存中的空闲内存 Qcache_hits,Qcache_insert命中次数除以插入次数就是不中比率 Qcache_not_cache Qcache_lowmem_prunes 缓存内存不足 Qcache_queries_in_cache cache_total_size 缓存中块的数量 Qcache_inserts : 多少次未命中然后插入的. Qcache_hits : 多少次命中的. 查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100% 缓存的命中率 = Qcache_queries_in_cache/Com_select 2. SHOW STATUS LIKE 'open%tables'; Open_tables | 5000 | | Opened_tables | 195 有 5,000 个表是打开的，有 195 个表需要打开
- 6. 查询缓存%SHOW STATUS LIKE 'threads%'; Threads_cached :连续执行 SHOW STATUS 命令时快速增加 ，需要增大此值 %SHOW STATUS LIKE 'created_tmp%'; %SHOW STATUS LIKE "sort%"; sort_merge_passes 很大，就表示需要注意 sort_buffer_size
- 7. MySQL性能探测- \smysql> \s -------------- Mysql Ver 14.7 Distrib 4.1.12, for unknown-freebsd4.7 (i386) using readline 4.3 Connection id: 2697076 Current database: Current user: root@localhost SSL: Not in use Current pager: more Using outfile: '' Using delimiter: ; Server version: 4.1.12-standard-log Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /tmp/mysql.sock Uptime: 23 days 19 hours 28 min 23 sec Threads: 14 Questions: 28510630 Slow queries: 87 Opens: 1286 Flush tables: 123 Open tables: 35 Queries per second avg: 13.858 --------------
- 8. MySQL性能探测- slow_logvi /usr/local/mysql/data/slow.log # Time: 070114 2:00:55 # User@Host: root[root] @ localhost [] # Query_time: 55 Lock_time: 0 Rows_sent: 0 Rows_examined: 9286 SET timestamp=1168711255; update QSP_15_PRF.QSP_SEED as q, TSP_15_PRF.TSP_SEED as t SET q.DF_STATUS = 100 WHERE q.URL = t.URL; Lock time 太大表示锁冲突 Rows_examined 太大表示SQL语句需要优化
- 9. MySQL性能探测 - 索引命中率MyIsam mysql> SHOW STATUS LIKE '%key_read%'; +-------------------+---------+ | Variable_name | Value | +-------------------+---------+ | Key_read_requests | 1726173 | | Key_reads | 583 | Key_reads / Key_read_requests < 1/1000 +-------------------+---------+ 2 rows in set (0.00 sec) InnoDB mysql> SHOW INNODB STATUS\G *************************** 1. row *************************** ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 951801698; in additional pool allocated 2055680 Buffer pool size 51200 Free buffers 108 Database pages 50103 Buffer pool hit rate 1000 / 1000
- 10. 索引优化 – 索引简介索引类型 B树 Hash表 倒排树
- 11. 索引优化 - MyISAM vs. InnoDBMyISAM 索引文件 (.MYI) 、数据文件 (.MYD) 所有的索引都是对等的，KEY与PRIMARY KEY 效果相同，都是包含一个指向数据文件的指针 InnoDB 使用 PRIMARY KEY 聚簇索引（clustered index） 数据与 PRIMARY KEY存放在一起 其他索引包含PKV（PRIMARY KEY values ） 指向数据
- 12. 索引优化 – MyIsam 特点优点 查询快（读多、更新删除少）读与插入不冲突 文件数据库，容易控制 缺点 表级锁，读改混合会冲突，性能低 大量更新下容易坏，需要定期修复 表大小超过4G需要特殊设置
- 13. 索引优化 – InnoDB 特点优点 行级锁，并发优势 支持事务 数据库大小无限制 缺点 查询速度稍慢(PK查询快) 如果损坏，修复的手段不多 死锁
- 14. 索引优化 – 一般规则每query每table只能使用一个索引（MySQL 5.0 支持 index merge） 尽量使用多字段索引，复用单索引 索引不是万能的
- 15. 索引优化 – 索引能主键查询 PRIMARY KEY(i) :: WHERE i = 5 索引查询 INDEX(i) :: WHERE i = 7 前缀查询 INDEX(s) :: WHERE s LIKE “foo%” INDEX(i, j) :: WHERE i = 5 Join INDEX(i) :: WHERE a.i = b.i
- 16. 索引优化 – 索引不能1以通配符开始的LIKE条件 INDEX(s) :: WHERE s LIKE “%foo” Bitwise 运算 INDEX(i) :: WHERE i & 4 非前缀查询 INDEX(i, j) :: WHERE j = 5 非常量查询 INDEX(s) :: WHERE foo(s) = “FOO” INDEX(s) :: WHERE s = f(t)
- 17. 索引优化 – 索引不能2索引越多越好？ 更新成本 内存、硬盘空间占用成本 选择成本 不一定会使用索引：optimizer 1/3rd 规则 CREATE TABLE EMPLOYEE ( … SEX TINYINT NOT NULL, # 只有两个值 KEY IDX_SEX ( SEX ) ) ENGINE = InnoDB; 一般会退化成为全表扫描（Full Table Scan），为什么？ 例外：表很小、只需要索引扫描
- 18. 索引优化 – Explain示例 mysql> explain select * from QSP_SEED where SITE in ( "24188" ) and DF_STATUS = 1 and DF_EXPECT < "2006-08-31 17:16:15" order by DF_EXPECT asc\G *************************** 1. row *************************** id: 1 # 子查询的ID select_type: SIMPLE # 查询的类型：simple、subquery、union table: QSP_SEED # 表名 type: range # 表的操作类型 possible_keys: DF_KEY,BW_KEY # 备选的索引 key: DF_KEY # 实际使用的索引 key_len: 264 # 实际使用索引的长度 ref: NULL # 索引引用的值 rows: 10 # 预计需要扫描的行数 Extra: Using where # 其它信息 非select语句转换成为相同条件的select语句
- 19. Explain输出注意key字段的值为空 type字段的值为“ALL”表示全表扫描 rows字段的值太大 Extra字段的值为： Using filesort : 需要外部排序 Using temporary : 需要创建（内存、磁盘）临时表
- 20. Explain的其它说明Explain的原理：统计表、数据列的基数（cardinality） 不能对空表、小表进行Explain 定期更新统计表：analyse table 强制指定索引：select * from table force index( KEY ) where ...
- 21. 配置参数调优1show status like “…”; # 察看系统状态 show variables like “…”; # 察看系统初始配置 key_buffer_size : 索引缓存大小，仅MyIsam 类型 table_cache : log-slow-queries : 慢速日志，建议打开 skip-name-resolve : 忽略反向DNS解析，要求设置 max_connections : 允许的最大连接数，根据实际情况 sort_buffer_size : MyIsam 类型、但是 InnoDB 也会在 order by 子句中用到 skip-networking : 如果仅本地访问，建议打开 log-bin : 非备份、Master不需要就不用
- 22. 配置参数调优2innodb_data_file_path : 尽量平均分布数据到多个磁盘 innodb_buffer_pool_size : InnoDB的索引缓存空间，50% ~ 80% of RAM innodb_flush_log_at_trx_commit : 0
- 23. Tips对应用中的每一个SQL语句，考虑是否需要索引；并预先建立索引(中途增加索引的成本高：停止服务、时间长) 批量插入的性能：load data > multi-row insert >> insert select count(*) 在InnoDB上需要表扫描，MyISAM不需要 InnoDB 清空表：DROP TABLE + CREATE TABLE 而不是 DELETE FROM Query Cache 分盘：符号链接 / ibdata 分盘 / stripe 统计类型应用使用 Master-Slave 模式 尽量使用应用级别的锁，不使用表级锁 记录尽量窄
- 24. Mysql常用配置max_connections = 200 tmp_table_size = 256M log-bin: binlog-do-db=db_nam e // 为该库生成log binlog-do-db=db_name_log // 为该库生成log skip-name-resolve // 跳过域名解析 innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:60000M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/日志组的文件目录 innodb_buffer_pool_size = 2048M 对于索引的缓存 innodb_additional_mem_pool_size = 20M //存储数据目录和内部数构 innodb_log_file_size = 500M innodb_log_buffer_size = 8M
