MySQL能优化.ppt

MySQL优化 Begin for qingwei qingwei@ 淘宝网 摘要 查询优化 Mysql性格探测 索引简介 Explain语法 配置参数调优 Tips mysqlDBA常用配置项目 查询优化 1.缓存类型:表缓存(table_cache ), SHOW STATUS LIKE ’Opened_tables’; 某个数据表必须打开的次数 。如果这个数字增长得很快，就意味着这个缓存太小了 2. 索引使用: a.但是减慢了插入和删除的速度 b.索引会花费磁盘空间 . 对于MyISAM表频,繁地索引可能引起索引文件比数据文件更快地达到最大限制 . InnoDB, InnoDB共享表空间并不受操作系统的文件大小限制 c.数据的基数，包含不同值的数量,列中包含很多不同的值，重复的值很少 ,工作效率越好 d.索引尽可能短,更少的I/O, InnoDB和BDB等使用聚簇索引,数据行和主键值存储在一起,其它的索引都是次级索引 ,它们被用于定位数据行 ，如果主键值很长，每个次级索引就需要更多的额外空间 e.使用最左（leftmost）前缀 . state, city, zipstate, citystate 查询优化 1.IS NULL 与 IS NOT NULL 不能用null作索引，任何包含null值的列都将不会被包含在索引中 2.select * from employee where last_name like ‘%cliton%’ ，不能使用索引 3.In和exists EXIST,系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项 , IN首先执行子查询，结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询 mysql -udpd -h2 -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); 查询缓冲 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 个表需要打开 查询缓存 %SHOW STATUS LIKE threads%; Threads_cached :连续执行 SHOW STATUS 命令时快速增加 ，需要增大此值 %SHOW STATUS LIKE created_tmp%; %SHOW STATUS LIKE sort%; sort_merge_passes 很大，就表示需要注意 sort_buffer_size MySQL性能探测- \s mysql \s Mysql Ver 14.7 Distrib 4.1.12, for unknown-freebsd4.7 (i386) using readline 4.3 Connection id: