本章中，我们将使用第一章中建立的100万条记录的表，显示进行从无到有的一个mysql优化的过程。
当前我们的表并没有使用任何索引做过优化，现在我们对表进行一次查询，来观察一下查询时间。
首先我们先查看表中是否使用索引：
show index from big_table;
上图可以看出，big_table表已经有了主键索引。但是没有建立其他的索引。现在在这个基础之上，我们来进行实战
1.尽量不要使用select *
select * from big_table a 【耗时：3.083s】为什么会这么慢，通过explain来分析下
select_type:SIMPLE表示查询语句是一个单一的查询语句
filtered:100 表示返回结果的行占需要读到的行(rows列的值)的百分比
type:ALL 说明进行了全表扫描，从而表明这条查询语句性能很差。
所以证明：select *会使用全表扫描，会导致索引失效，以至于查询效率低下
select uid from big_table 【耗时：1.745s】可以看到，当select后面指定字段后，查询速度明显改善了，这是为什么呢，通过explain来看一下
通过type为index，key为primary这说明这条查询语句使用了索引。
可以看出，这里不使用select * ，使用uid，便可以是原本为主键索引的uid发挥其索引的作用，这样便可以直接通过所以查出数据，而不用在去全表扫描了。
--------------------------------------------------------------------------------------------------------------------------------------------------------------
2.当select col1,col2,...时，col1,col2最好都是索引字段。
在select后面的字段，最好都是建立索引的字段，如果有又一个未建立索引则会导致全表扫描，从而无法通过索引提高性能。
看例子：
#uid为主键索引，passwd未建立索引
select uid,passwd from big_table 【耗时：2.049】当select中的字段存在为建立索引的字段时，是否使用了全表扫描呢？通过explain来看一下：
图1
type为ALL，说明当前的sql使用了全表扫描。未能通过索引进行优化。
那么怎样才能使用上索引的优化呢？根据标题所示，我们通过将select的字段全都建立上索引，然后再来看一下查询效果。两步的sql如下：
#给字段passwd创建索引
ALTER TABLE `big_table` ADD INDEX idx_passwd ( `passwd` )
select uid,passwd from big_table 【1】【耗时：1.558s】当给passwd字段也创建索引后，现在select的两个字段全都是为索引的字段了，可以看到查询的速度，比之前要快了。下面通过explain再来验证一下：
图2
从结果可以看出，图2的type为index，而图1的type为ALL，这就说明索引生效了。通过key和Extra俩个字段也能看出使用到了索引。
所以在使用select的时候，select的字段尽量都建立了索引。
--------------------------------------------------------------------------------------------------------------------------------------------------------------
3.带有检索条件的查询
3.1 能用=号就用=号
select * from big_table a where uid = 102194 【1】【耗时：0.001s】
select uid from big_table a where uid = 102194 【2】【耗时：0.001s】
select passwd from big_table a where uid=102194【3】【耗时：0.038s】
select passwd from big_table a where passwd='f0d0ae10ab4f693101024557c8effc95'【4】【耗时：1.524s】
--注意看：同样where用一个字段作为条件，但是使用带有主键索引的uid，显然速度要快的多结论：
uid是主键，自动会建立主键索引。当用主键索引作为检索条件时，不管是select全部字段还是单个字段，时间都会很少，所以使用主键索引作为查询条件会加快检索速度。
但是如果使用普通的字段作为检索条件如【4】，即便是select一个字段，查询速度也会很慢。所以查询条件尽量是索引，一遍提高查询速度。
下面通过用explain来分析一下
explain select * from big_table a where uid = 102194 【1】
explain select uid from big_table a where uid = 102194 【2】
explain select passwd from big_table a where uid = 102194 【3】
explain select passwd from big_table a where passwd = 'f0d0ae10ab4f693101024557c8effc95' 【4】
图1
图1中type和ref都是const，const：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
图2
图2中type和ref同业阳朔const，另外Extra为Using index，说明使用了覆盖索引，查询的字段本身为索引（select uid中的uid本身为主键索引），那么就是覆盖索引。
图3
图3与图1相同，都是用索引所谓条件，select单个字段。这说明select 的这个字段是什么与查询速度无关。
图4
图4与前三个图大不相同了，type变为了ALL，说明是全文检索，并未用到索引。Extra中Using where通常来说，意味着全表扫描或者在查找使用索引的情况下，但是还有查询条件不在索引字段当中。所以【4】的查询速度会那么慢。
所以就需要优化了。
另外你可以发现，查询速度快的rows的值都会相对小。图4的rows为994222说明查找了994222行才找到了结果。而图2的rows为1，说明一下命中。这也就解释了为什么【2】的查询速度块的原因之一了。
优化：
通过上面图4的explain可以看出，之所以慢是因为进行了全文检索，而没有用到索引，因为passwd没有索引。下面我们通过给passwd建立索引，看看是否能够优化查询速度：
首先，为passwd字段创建普通索引（关于每种索引的意思可百度）
ALTER TABLE `big_table` ADD INDEX idx_passwd ( `passwd` ) 【数据为100W条，创建索引时间为：11.427s】
#注意：创建索引是需要花费时间的，数据越多，创建索引用的时间也越长下面我们再来执行一下【4】这条sql，比较一下现在的执行时间
select passwd from big_table a where passwd='f0d0ae10ab4f693101024557c8effc95'【4】【耗时：0.002s】
#看以看到，当给passwd创建索引后，查询速度就特别快了最后我们再通过explain来与图4对比一下
可以看到type变成了ref，说明使用了索引，key为idx_passwd表示用了idx_passwd这个索引，Extra也变为了Using index也说明用到了索引。rows也变为了1说明只通过一行就找到了结果。
3.2尽量不要使用like，如果一定要使用，将like的%放到字符串的右边
首先对passwd字段建立索引，上面我已经建立过了，就不在建立了，语句就在上面，然后我们来看一下%在不同的位置，查询速度是怎样的：
select passwd from big_table a where passwd like '%f0d0ae10ab4f693101024557c8effc95' 【1】【耗时：1.241s】
select passwd from big_table a where passwd like 'f0d0ae10ab4f693101024557c8effc95%' 【2】【耗时：0.037s】
select passwd from big_table a where passwd like '%f0d0ae10ab4f693101024557c8effc95%'【3】【耗时：1.249s】上面的结果可以看出只有%号在字符串的右边，速度才是最快的。为什么呢，我们通过explain来看一下：
【1】和【3】执行explain的结果是相同的
【2】执行explain
注意看rows字段，【2】的rows为1，从这就看出为什么%放在字符串右边查询速度快。
所以：最好不要使用like，如果非要使用like，最好是在最右边加%。这样才能让所以发挥效果。
3.3使用between
--未使用索引字段的速度
explain select sex from big_table a where sex BETWEEN 0 and 1 【1】【耗时：1.714s】--速度慢
between and的字段未建立索引时，可以看出type为ALL，采用全表扫描。所以会很慢
--使用索引字段的速度
explain select sex from big_table a where uid BETWEEN 102194 and 102194 【2】【耗时：0.001s】
当使用索引作为between and 的字段时，索引生效，所以查询速度很快。注意这里between and的是同一个值，如果不同值，explain的结果会怎样呢，看下面这个
explain select sex from big_table a where uid BETWEEN 102194 and 102198 【3】【耗时：0.001s】
注意看，当between and的值不是同一个值时，type变为了range，不再是const了。这就引出了一个问题，比如我们这里是BETWEEN 102194 and 102198，如果这2个数字的范围差太大时，也就是rows的值越大时，那么效率就会越低下，查询速度就会越慢。
下面将看一下in和exists，在这之前，我们先来看一些两者一些区别，以便更好的理解：
| IN 和 EXISTS 的区别 1.in（）适合B表比A表数据小的情况。 2.exists（）适合B表比A表数据大的情况，当A表数据与B表数据一样大时，in和exists的效率差不多，可以任选一个使用。 详细解释： select * from A where id in(select id from B) 以上查询使用了in语句,in()只执行一次,它查出B表中的所有id字段并缓存起来.之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录.可以看出，当B表数据较大时不适合使用in（），因为B表数据全部遍历一次。 select a.* from A a where exists(select 1 from B b where a.id=b.id) 以上查询使用了exists语句,exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false. 当B表比A表数据大时适合使用exists(),因为它没有那么遍历操作,只需要再执行一次查询就行. 如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等. 如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果. 再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快. 结论: 前表大用in，前表小用exists，当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用. |
3.4使用in（not in）
select * from big_table a where uid in (102194) 【1】【耗时：0.047s】
select uid from big_table a where uid in (102194) 【2】【耗时：0.001s】
select passwd from big_table a where uid in (102194) 【3】【耗时：0.001s】
select sex from big_table a where sex in (0) 【4】【耗时：1.524s】--速度慢结论：
【1】【2】【3】的查询速度都很快因为查询条件in用的uid是主键索引。【1】和【2】【3】差的的时间是因为select *导致的。
【4】条件中的in用的sex不是索引，所以查询速度会慢。
下面通过explain再来看一下
explain select * from big_table a where uid in (102194) 【1】
explain select uid from big_table a where uid in (102194) 【2】
explain select passwd from big_table a where uid in (102194) 【3】
explain select sex from big_table a where sex in (0) 【4】
图1
图2
图3
图4
总结：
图1，图2，图3通过type和ref的const可以看出，查询语句是可接受的，都用到主键索引优化了查询速度
图4的type为ALL，说明是通过全文搜索来查找满足条件的记录。rows为994227也说明了扫描了这么多条数据才找到了满足条件的记录。从而也证明了type为ALL（一共100万条记录，这里经过90多万的扫描，所以查询时间慢）。
3.5使用exists
select * from big_table a where exists (select * from big_table where uid = 102194) 【1】【耗时：2.494s】#速度慢 慢在第一个主查询【2.459s】
select sex from big_table a where exists (select * from big_table where uid = 102194) 【3】【耗时：1.836s】#速度慢
select sex from big_table a where exists (select * from big_table where sex = 0) 【4】【耗时：1.587s】#速度慢结论：
上面三个查询从时间上可以发现，速度都相当慢。为什么会这么慢呢？还记得上面说in和exists区别的时候讲过，当使用exsits时，不会缓存记录，主表每条都会查询一次，这也就是为什么所有的查询都这么慢。我们来佐证一下这个问题，我们单独选择子查询时，因为uid为索引，所以瞬间就可以查出子查询的结果，但是当我们选中主查询是，会发现因为要全表查一次，所以会很慢。这也就是为什么说不建议使用exsits，如果非要用则如果主表（前表）记录少时再用exists。
下面我们再通过explain来分析一下慢的原因：
explain select * from big_table a where exists (select * from big_table where uid = 102194) 【1】
explain select sex from big_table a where exists (select * from big_table where uid = 102194) 【2】
explain select sex from big_table a where exists (select * from big_table where sex = 0) 【3】
图1
图2
图3
执行explain后，出现两条记录，这是因为现在的查询语句有了子查询。因为有了子查询，就会牵扯到执行顺序问题，所以这里先讲一下怎么来看查询顺序。那就是观察explain结果记录中的id字段。
| id的情况有三种，分别是： 1. id相同表示加载表的顺序是从上到下。 |
结论：
所以，从图1可以看出id为2先执行，他执行的是什么？通过table为big_table可以知道这个子查询就是exsits后面的那个查询语句。我们观察id为2这一行，通过type为const，ref为const，rows为1可以看出这个子查询速度没有问题。
然后再来看id为1这一行，table为a说明这条记录是针对sql中的主查询进行的分析，他的type为ALL，说明进行了全文检索，rows为994227说明大约遍历了994227行记录才执行完毕。通过这两个点，就可以确定这就是慢的主要原因。也是将来需要进行优化的点。
图2与图1效果是一样的。因为自语句都用了主键索引，所以子查询速度都很快。瓶颈在主查询上
图3则略有不同，我们发现id为2的这条记录type也为ALL，rows也是994227，说明也是进行了全文检索。因为自查询中的where sex不是索引。所以这也增加了检索时间。这样子查询和主查询都需要进行优化。
4 其他
select * from big_table a where uid>102194 【1】【耗时：2.514s】#速度慢
#在上面的select * from big_table a where uid=102194时，瞬间就可以查询出结果
#这里主要为了来观察其区别
图1
结论：
通过explain可以看出，当使用>,<,<=,>=,<>这些范围相关的比较运算符时，虽然where中明明用了主键索引uid为条件，但还是会使索引失效。这也就是为什么rows为497113，type为range，而不是像之前where uid=102194时rows为1，type为const了。也就是查询速度慢的一个原因了。
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
通过这一章，我们运行不同的sql，然后配合explain来观察了在百万级的记录情况下，简单的查询语句在单条件情况下的各种执行速度和分析。
第三章，将会继续看一下单表多条件的情况下，执行速度，以及explain的分析。
8122
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
