一、概述
二叉树——>索引文件:效率log2N
检索10次:2的10次方。1024条记录。
索引带来的开销
查看数据的文件(安装目录下的data目录),会发现三个文件,
.frm:表示表的结构
.myd:表示数据
.myi:表示索引的文件
索引带来的问题:会导致insert,update,delete的效率
更新频繁的字段不适合创建索引。
唯一性比较差的字段不适合创建索引。比如人的性别只有男和女
满足一下条件才会创建索引
1、肯定在where条件中经常使用到。
2、该字段的变化不会太频繁。
二、索引使用场景
1、快速查找符合where条件的记录。
2、快速确定候选集。若where条件使用了多个索引字段,则MySQL会优先使用能使候选集规模最小的那个索引,以便尽快淘汰不符合条件的记录。
3、如果表中存在几个字段构成的联合索引,则查找记录时,这个联合索引的最左前缀匹配字段也会被自动作为索引来加速查找。
例如:若为某张表创建了3个索引,(c1,c2,c3)构成的联合索引,则(c1),(c1,c2),(c1,c2,c3)均会作为索引,(c2,c3)就不会被作为索引,而(c1,c3)其实只利用到c1索引。
4、多表做join操作时会使用索引(如果参与join的字段在这些表中均建立了索引的话)
5、若某个字段已建立索引,求该字段做sort或group操作时,MySQL会使用索引。
三、通过EXPLAIN分析执行低效SQL的执行计划
如:
mysql> explain select * from taxgrouptaxes\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: taxgrouptaxes type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 Extra:
select_type :表示SELECT 的类型,
常见的取值有
SIMPLE:简单表,既不使用表连接或者子查询的
PRIMARY :主查询,既外层的查询
union :UNION 中的第二个或者后面的查询语句
SUBQUERY :子查询中的第一个select .
table :输出结果集的表
type 表示MYSQL 在表中找到所需行的方式。或者叫访问类型。
常见的类型有以下几种;
all
index
range
ref
eq_ref
const,system
null
从上到下呢,性能有最差到最好。
1:type=all 全表扫描。
2:type=index 索引扫面
3:type=tange 索引范围扫描。常见于< <= > >= between 等操作
4:type=ref s使用费唯一索引扫描或唯一索引的前缀扫描。
5:type=eq_ref 类似ref 区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配。
6:type=const/system 单表中最多有一个匹配行,查询起来非常迅速。
7 : type=null MYSQL 不用访问表或者索引。直接就能够得到结果。
possible_keys: 表示查询时可能使用的索引
key: 表示实际使用的索引
key_len: 使用到索引字段的长度
rows : 扫描行的数量
Extra : 执行情况的说明和扫描。包含不适合的其他列中显示单是对执行计划非常重要 。
四、通过show profile分析SQL
1、首先查看MySQL是否支持show profile
mysql> select @@have_profiling; +------------------+ | @@have_profiling | +------------------+ | YES | +------------------+ 1 row in set (0.00 sec)
2、如果profile是关闭的,可以通过set语句在session级别开启profile
set profiling = 1;
3、执行完毕之后,可以通过show profiles语句,查看当前SQL的queryID。
4、通过show profile for query queryID
以上就是MySQL高级十三——通过索引优化SQL的内容,更多相关内容请关注PHP中文网(www.php.cn)!