1.explain
执行格式:explain+sql语句
以下将对每个字段进行讲解。
1.1 id
分三种情况:
(1)id相同,执行顺序由上而下。执行顺序1->3->2。
(2)如果是子查询,id序号递增,id越大越先被执行。执行顺序3->1->2。
(3)id相同可以认为是一组,从上往下顺序执行,在所有组中,id越大越先被执行。执行顺序3->衍生表_>2。
1.2 select_type
常用的六个值:
simple
简单的select查询,查询中不包括子查询或union。
primary
查询中若包含任何复杂的子部分,最外层查询被标记为primary。
subquery
在select或where列表中包含了子查询。
derived
在from列表中包含的子查询被标记为derived(衍生),mysql会递归执行这些子查询,把结果放在临时表。
union
union result
从union表获取结果的result。
1.3 table
显示这一行的数据是关于哪张表的。
1.4 type
会出现8种值。all表示全表扫描(百万级别数据必须优化)。
一般来说,至少保证查询达到range级别,最好达到ref。
system
const
eq_ref
ref
range
index
all
全表扫描,数据量大时必须进行优化。
1.5 possible_keys和key
覆盖索引:查询使用的字段与建索引所用字段完全相同且顺序一致。
如:select col1,col2 from t1;
create index idx_col1_col2 on t1(col1,col2);
此时出现:
1.6 key_len
一般来说,key_len越大,查询结果越精确。
1.7 ref
1.8 rows
每张表有多少行被查询,越小越好。
1.9 extra
包含不适合在其他列中显示但十分重要的信息。
Using filesort
说明查询没有按建好索引的顺序来查,情况较差。如果可以,尽快优化。
下图中第二种结果明显比第一种要好,因为没有using filesort。
Using temporary
使用临时表,情况更差。一般常见于order by和group by。但可以通过调整所用分组的字段个数和顺序来优化。
下图,索引为col1和col2复合索引,第一个group by只用到col2,情况较差,第二个用到col1和col2,得到优化。
Using index
2. 索引
2.1 单表优化案例
初始情况:type是all,并出现了Using filesort,情况很差,必须优化。
一般来说,建索引所用字段为where条件后所用到的字段,因此初步考虑建立category_id、comments、view的复合索引。效果如下:
仍然出现using filesort,情况较差,删除原来索引重新进行优化。
综上,我们将comments从索引中删除,只建立category_id和views的复合索引,效果如下:
2.2 两表优化案例
左表为book,右表为class。
需要优化的语句:
左连接在右表(book) 的card字段加索引时:
左连接在左表(class) 的card字段加索引时:
由于ref好于index,而且查询行数上第二种要多,所以第二种方案较优。
结论:左连接加右表索引,右连接加左表索引。
2.3 三表优化案例
explain后:
在book和phone的card字段上加上联合索引后:
2.4 结论
小表驱动大表
优先优化内层循环
保证join语句中被驱动表上join条件字段加索引
无法保证被驱动表的join字段被索引时,不要吝惜joinbuffer的设置
3. 索引优化十例
3.1 最佳左前缀法则
首先建立name、age、pos的联合索引。
由下可知,建立联合索引后只使用一个字段查询仍然会使用联合索引,不会导致索引失效。
建立联合索引后使用两个字段进行查询,不会导致索引失效:
使用全部字段进行查询:
索引建立字段及顺序为name、age、pos,下面为age+pos和pos,没有用到索引:
综上,如果索引了多列,查询从索引的最左前列开始并且不跳过索引中的列。
3.2 不在索引列上做任何操作(计算、函数、类型转换等),会导致索引失效转向全表扫描
3.3 范围之后全失效
3.4 尽量使用覆盖索引(索引列和查询列一致,减少select *)
3.5 使用不等于(!=或<>)无法使用索引导致全表扫描
3.6 is null,is not null也无法使用索引
3.7 like%加右边
解决like '%字符串%'时索引不被使用的问题
使用覆盖索引。
下图中这些语句都可以使用索引。(建立了name、age的复合索引,id为主键自动建立索引)
下图中,查询的字段要多于建立索引的字段,覆盖索引失效,全表扫描。
3.8 字符串不加单引号索引失效
varchar类型一定要加单引号。
mysql存储引擎会在底层进行类型转换,导致索引失效。
3.9 少用or,会导致索引失效
结果正确,但不会使用索引。
4. 数据库事务
ACID原则
原子性(Atomicity)、一致性(Consistency)、独立性(Isolation)、持久性(Durability)。
事务的隔离级别
脏读
一个事务读取了另外一个事务未提交的数据。
不可重复读
一个事务内读取表中的某一行数据,多次读取结果不同。主要针对update和delete。
幻读
在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。主要针对insert。
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交 | 是 | 是 | 是 |
读已提交 | 是 | 是 | |
可重复读 | 是 | ||
序列化 |
mysql默认隔离级别为可重复读。
5.MySQL索引