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, zipstate, citystate
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
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)