mysql 1千万数据查询优化。

2017-01-05 11:09:20来源:oschina作者:烟蒂--未落人点击

一、查查 profiles;


查看profiles 设置。


1.show profiles 查看sql执行情况


mysql> show profiles; Empty set


2.如果是empty set 查看 profiles 是否开启。


mysql> show variables like '%pro%'; +---------------------------+-------+ | Variable_name| Value | +---------------------------+-------+ | have_profiling| YES| | profiling| OFF| | profiling_history_size| 15| | protocol_version | 10| | proxy_user || | slave_compressed_protocol | OFF| | stored_program_cache | 256| +---------------------------+-------+


3、开启 profiles


mysql> set profiling=1; Query OK, 0 rows affected


4.查看sql的执行时间


mysql> show databases; +--------------------+ | Database| +--------------------+ | information_schema | | hnmcc_check| | test | +--------------------+ 3 rows in set


mysql> use hnmcc_check; Database changed mysql> show profiles; +----------+------------+-----------------+ | Query_ID | Duration| Query| +----------+------------+-----------------+ |1 |0.000141 | show tables | |2 |0.000461 | show databases | |3 | 0.00019925 | use hnmcc_check | +----------+------------+-----------------+ 3 rows in set


二、索引


用explain 查看索引的效率

1.数据个数。10606104行数据


sql :SELECT count(1) FROM active_user_12

2.表结构:


mysql> desc active_user_12; +------------+-------------+------+-----+---------+-------+ | Field | Type| Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | phone | varchar(11) | NO| PRI | NULL|| | crete_time | varchar(20) | YES | | NULL|| | flag| int(11) | YES | | NULL|| +------------+-------------+------+-----+---------+-------+

3.主键索引


ysql> show index from active_user_12;


+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | active_user_12 | 0 | PRIMARY|1 | phone| A |10308160 | NULL | NULL| | BTREE | | | | active_user_12 | 1 | index_phe |1 | phone| A |10308160 | NULL | NULL| | BTREE | | | +----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


explain 解析: mysql> EXPLAIN SELECT SQL_NO_CACHE * from active_user_12 t where phone='13403700040'; +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra | +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | t | const | PRIMARY,index_phe | PRIMARY | 35 | const |1 | NULL | +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+


(1). id


SELECT识别符。这是SELECT查询序列号。这个不重要,查询序号即为sql语句执行的顺序。


(2).select_type


select类型,它有以下几种值


(2.1 )simple 它表示简单的select,没有union和子查询


mysql> EXPLAIN SELECT SQL_NO_CACHE * from active_user_12 t where phone='13403700040'; +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra | +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | t | const | PRIMARY,index_phe | PRIMARY | 35 | const |1 | NULL | +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+


(2.2 )primary 最外面的select,在有子查询的语句中,最外面的select查询就是primary,下图中就是这样


mysql> EXPLAIN selec t * from (SELECT * from active_user_12 t limit 0,10) b; +----+-------------+------------+------+---------------+------+---------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+------+----------+-------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL| NULL |10 | NULL | | 2 | DERIVED | t | ALL | NULL | NULL | NULL| NULL | 10308160 | NULL | +----+-------------+------------+------+---------------+------+---------+------+----------+-------+


(2.3) union union语句的第二个或者说是后面那一个.现执行一条语句。


mysql> explain select * from active_user_12 limit 0,10 union all select * from active_user_12 limit 10,10; +------+--------------+----------------+------+---------------+------+---------+------+----------+-----------------+ | id| select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| +------+--------------+----------------+------+---------------+------+---------+------+----------+-----------------+ |1 | PRIMARY | active_user_12 | ALL | NULL | NULL | NULL| NULL | 10308160 | NULL| |2 | UNION| active_user_12 | ALL | NULL | NULL | NULL| NULL | 10308160 | NULL| | NULL | UNION RESULT | | ALL | NULL | NULL | NULL| NULL | NULL | Using temporary | +------+--------------+----------------+------+---------------+------+---------+------+----------+-----------------+ 3 rows in set


(2.4 )dependent union UNION中的第二个或后面的SELECT语句,取决于外面的查询


(2.5) union result UNION的结果


(3)table


输出的行所用的表,这个参数显而易见,容易理解


(4 )type


连接类型。有多个参数,先从最佳类型到最差类型介绍重要且困难


(4.1 )system


表仅有一行,这是const类型的特列,平时不会出现,这个也可以忽略不计


(4.2) const


表最多有一个匹配行,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快


记住一定是用到primary key 或者unique,并且只检索出两条数据的 情况下才会是const。


(4.3)eq_ref


对于eq_ref的解释,mysql手册是这样说的:"对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY"。


(4.4 )ref 对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。


(4.5)ref_or_null该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。


上面这五种情况都是很理想的索引使用情况


(4.6 )index_merge 该联接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。


(4.7)unique_subquery


(4.8)index_subquery


(4.9) range 给定范围内的检索,使用一个索引来检查行。看下面两条语句


explain select * from uchome_space where uid in (1,2)


explain select * from uchome_space where groupid in (1,2)


uid有索引,groupid没有索引,结果是第一条语句的联接类型是range,第二个是ALL.以为是一定范围所以说像 between也可以这种联接,很明显


explain select * from uchome_space where friendnum = 17


这样的语句是不会使用range的,它会使用更好的联接类型就是上面介绍的ref


(4.10 )index 该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表,但index是从索引中读取的,而all是从硬盘中读的)


当查询只使用作为单索引一部分的列时,MySQL可以使用该联接类型。


(4.11 )ALL 对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记const的表,这通常不好,并且通常在它情况下差。通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常数值或列值被检索出。

5 possible_keys提示使用哪个索引会在该表中找到行,不太重要


6 keysMYSQL使用的索引,简单且重要


7 key_lenMYSQL使用的索引长度


8 refref列显示使用哪个列或常数与key一起从表中选择行。


9 rows显示MYSQL执行查询的行数,简单且重要,数值越大越不好,说明没有用好索引


10 Extra 该列包含MySQL解决查询的详细信息。


10.1DistinctMySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。一直没见过这个值


10.2Not exists


10.3range checked for each record


没有找到合适的索引


10.4 using filesort


MYSQL手册是这么解释的“MySQL需要额外的一次传递,以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序顺序检索行。”目前不太明白


10.5 using index 只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的信息。这个比较容易理解,就是说明是否使用了索引


xplain select * from ucspace_uchome where uid = 1的extra为using index(uid建有索引)


explain select count(*) from uchome_space where groupid=1 的extra为using where(groupid未建立索引)


10.6 using temporary


为了解决查询,MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。


出现using temporary就说明语句需要优化了,举个例子来说


EXPLAIN SELECT ads.id FROMads, city WHERE city.city_id = 8005 AND ads.status = 'online' AND city.ads_id=ads.idORDER BYads.iddesc


id select_type table type possible_keys key key_len ref rows filtered Extra------ ----------- ------ ------ -------------- ------- ------- -------------------- ------ -------- -------------------------------1 SIMPLEcityref ads_id,city_id city_id 4 const 2838 100.00Using temporary; Using filesort1 SIMPLE ads eq_ref PRIMARY PRIMARY 4 city.ads_id 1 100.00 Using where


这条语句会使用using temporary,而下面这条语句则不会

EXPLAIN SELECT ads.id FROMads, city WHERE city.city_id = 8005 AND ads.status = 'online' AND city.ads_id=ads.idORDER BYcity.ads_iddesc


id select_type table type possible_keys key key_len ref rows filtered Extra------ ----------- ------ ------ -------------- ------- ------- -------------------- ------ -------- ---------------------------1 SIMPLEcityref ads_id,city_id city_id 4 const 2838 100.00Using where; Using filesort1 SIMPLE ads eq_ref PRIMARY PRIMARY 4 city.ads_id 1 100.00 Using where


这是为什么呢?他俩之间只是一个order by不同,MySQL 表关联的算法是 Nest Loop Join,是通过驱动表的结果集作为循环基础数据,然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。EXPLAIN 结果中,第一行出现的表就是驱动表(Important!)以上两个查询语句,驱动表都是 city,如上面的执行计划所示!


对驱动表可以直接排序,对非驱动表(的字段排序)需要对循环查询的合并结果(临时表)进行排序(Important!)


因此,order by ads.id desc 时,就要先 using temporary 了!


驱动表的定义


wwh999在 2006年总结说,当进行多表连接查询时,[驱动表]的定义为: 1)指定了联接条件时,满足查询条件的记录行数少的表为[驱动表]; 2)未指定联接条件时,行数少的表为[驱动表](Important!)。


永远用小结果集驱动大结果集

今天学到了一个很重要的一点:当不确定是用哪种类型的join时,让mysql优化器自动去判断,我们只需写select * from t1,t2 where t1.field = t2.field


10.7 using where


WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,如果Extra值不为Using where并且表联接类型为ALL或index,查询可能会有一些错误。(这个说明不是很理解,因为很多很多语句都会有where条件,而type为all或index只能说明检索的数据多,并不能说明错误,useing where不是很重要,但是很常见)


如果想要使查询尽可能快,应找出Using filesort和Using temporary的Extra值。


10.8Using sort_union(...),Using union(...),Using intersect(...)


这些函数说明如何为index_merge联接类型合并索引扫描


10.9Using index for group-by


类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。并且,按最有效的方式使用索引,以便对于每个组,只读取少量索引条目。


实例讲解

通过相乘EXPLAIN输出的rows列的所有值,你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时,也用这个乘积来确定执行哪个多表SELECT语句。


最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台