作者:LAMP小白 点击:2170 发布日期:2012-11-12 00:31:00 返回列表
当我们发出一个选区数据行的查询时,MYSQL会分析他,并考虑是否可以对他进行优化以加快查询
如果你发布了下面的查询,不管数据表有多复杂MYSQL都会迅速的告诉你结果
mysql> EXPLAIN SELECT * FROM tt1 WHERE FALSEG
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE
1 row in set (0.00 sec)
优化程序的目标
查询优化程序有好几个目标,主要目的是只要可能就要使用索引,并且使用条件最严格的索引来尽可能多,尽可能快的排除那些不符合索引条件的数据行
如:SELECT haha FROM heihei WHERE id<300 AND val >970
其中val>970的有30个匹配行 ,而id<300的有300个匹配行,于是优化查询会先执行val>970 将结果排除到30 然后再执行id<300
一些充分利用优化器的建议
1.对数据表进行分析
2.使用EXPLAIN验证优化操作
3.向优化器提供提示或在必要时屏蔽之
可以再数据表列表中的某个数据表名字后面利用FORCE INDEX,USE INDEX或IGNORE INDEX限定词告诉MYSQL你想使用哪种索引
还可以利用STRAGHT_JOIN强制优化器按特定的顺序使用数据表
4.尽量使用数据类型相同的数据列进行比较
5.使带索引的数据列在比较表达式中单独出现
6.不要在LIKE模式的开始位置使用通配符
7.利用优化器的长处
8.实验各种查询的变化格式,而且要多次运行他们
9.避免过多的使用自动类型转换
用EXPLAIN语句检查优化器操作
建立一个4张1000行的表的内关联查询
| 995 | 995 | 995 | 995 |
| 996 | 996 | 996 | 996 |
| 997 | 997 | 997 | 997 |
| 998 | 998 | 998 | 998 |
| 999 | 999 | 999 | 999 |
+-----+-----+-----+-----+
1000 rows in set (0.53 sec)
mysql> EXPLAIN SELECT tt1.id, tt2.id, tt3.id, tt4.id FROM `tt1` INNER JOIN `tt2` INNER JOIN `tt3` INNER JOIN `tt4` WHERE tt1.id = tt2.id AND tt2.id = tt3.id AND tt3.id = tt4.idG
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tt4
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 805
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: tt1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 859
Extra: Using where; Using join buffer
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: tt3
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1408
Extra: Using where; Using join buffer
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: tt2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1513
Extra: Using where; Using join buffer
4 rows in set (0.00 sec)
EXPLAIN的type字段告诉我们数据如何从数据表读出,ALL意思是"将检查所有的数据行",ROWS告诉我们优化器估计需要扫描805个数据行才能发成查询
为TT1建立索引
mysql> ALTER TABLE tt1 ADD INDEX(id);
Query OK, 1000 rows affected (0.05 sec)
Records: 1000 Duplicates: 0 Warnings: 0
id: 1
select_type: SIMPLE
table: tt1
type: ref
possible_keys: id
key: id
key_len: 4
ref: mio.tt4.id
rows: 1
Extra: Using index
首先type变成了ref,这表明使用了一个引用值(来自tt4.id)进行一次索引搜索来定位tt1中符合条件的数据行
ref表示引用这个结果值的字段
rows告诉我们优化器估计只需要扫描一行就能获得结果
那么试试给所有表都加上索引效果如何?
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tt4
type: index
possible_keys: id
key: id
key_len: 4
ref: NULL
rows: 706
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: tt3
type: ref
possible_keys: id
key: id
key_len: 4
ref: mio.tt4.id
rows: 1
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: tt2
type: ref
possible_keys: id
key: id
key_len: 4
ref: mio.tt3.id
rows: 1
Extra: Using where; Using index
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: tt1
type: ref
possible_keys: id
key: id
key_len: 4
ref: mio.tt2.id
rows: 1
Extra: Using where; Using index
4 rows in set (0.00 sec)
但是我们还能优化,运行ANALYSE TABLE生成关于键值分布情况的统计数据
mysql> ANALYZE TABLE tt1,tt2,tt3,tt4;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| mio.tt1 | analyze | status | OK |
| mio.tt2 | analyze | status | OK |
| mio.tt3 | analyze | status | OK |
| mio.tt4 | analyze | status | OK |
+---------+---------+----------+----------+
4 rows in set (0.01 sec)
再来看看查询用时
| 995 | 995 | 995 | 995 |
| 996 | 996 | 996 | 996 |
| 997 | 997 | 997 | 997 |
| 998 | 998 | 998 | 998 |
| 999 | 999 | 999 | 999 |
+-----+-----+-----+-----+
1000 rows in set (0.01 sec)
上一篇:关于索引 下一篇:快递查询API