理解MySQL数据库覆盖索引

话说有这么一个表:

CREATE TABLE `user_group` (
`id` int(11) NOT NULL auto_increment,
`uid` int(11) NOT NULL,
`group_id` int(11) NOT NULL,
PRIMARY KEY  (`id`),
KEY `uid` (`uid`),
KEY `group_id` (`group_id`),
) ENGINE=InnoDB AUTO_INCREMENT=750366 DEFAULT CHARSET=utf8

看AUTO_INCREMENT就知道数据并不多,75万条。然后是一条简单的查询:

  SELECT SQL_NO_CACHE uid FROM user_group WHERE group_id = 245;

很简单对不对?怪异的地方在于:

如果换成MyISAM做存储引擎的时候,查询耗时只需要0.01s,用InnoDB却会是0.15s左右。 Continue reading

mysql explain 中key_len的计算

今天丁原问我mysql执行计划中的key_len是怎么计算得到的,当时还没有注意,在高性能的那本书讲到过这个值的计算,但是自己看执行计划的时候一直都没有太在意这个值,更不用说深讨这个值的计算了:

ken_len表示索引使用的字节数,根据这个值,就可以判断索引使用情况,特别是在组合索引的时候,判断所有的索引字段都被查询用到。

在查看官方文档的时候,也没有发现详细的key_len的计算介绍,后来做了一些测试,在咨询了丁奇关于变长数据类型的值计算的时候,突然想到innodb 行的格式,在这里的计算中有点类似,总结一下需要考虑到以下一些情况: Continue reading

MySQL 数据库优化(11)The Query Optimization Process

摘自:http://www.chenyajun.com/2009/01/02/1623

查询优化过程

查询优化器
一个查询通常有很多种不同的但是产生相同结果的执行方式,优化器要找到最好的。
MySQL使用基于代价的优化器,它意味着它尽力预计不同执行计划的代价并选择最不昂贵的,代价的单位是一个4k的数据页的次数。你可以通过查看 Last_query_cost看到优化器预计的代价:

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM sakila.film_actor;
+----------+
| count(*) |
+----------+
| 5462     |
+----------+
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 1040.599000 |
+-----------------+-------------+

这意味着它要随机读取1040个数据页。它基于统计而估计:每个表或者索引的page数量,索引中不同值的比例,行和key的长度,key的分布。 优化器估计时并不包括任何类型的cache所产生的效果——它假定每次读都是一个磁盘IO操作。 Continue reading

关于MySQL explain 中的ID(推荐)

Explain ID详解

含义:select查询的序列号,是一组数字,表示的是查询中执行select子句或者是操作表的顺序。

id的情况有三种,分别是:

  • id相同表示加载表的顺序是从上到下。
  • id不同id值越大,优先级越高,越先被执行。
  • id有相同,也有不同,同时存在。id相同的可以认为是一组,从上往下顺序执行;在所有的组中,id的值越大,优先级越高,越先执行。

再看一个查询计划的例子:

执行顺序依次为 4 -> 3 -> 2 > 1 > NULL

第一行:id列为1,表示第一个select,select_type列的primary表示该查询为外层查询,table列被标记为<derived3>,表示查询结果来自一个衍生表,其中3代表该查询衍生自第三个select查询,即id为3的select。[select d1.name……]

第二行:id为3,表示该查询的执行次序为2(4→3),是整个查询中第三个select的一部分。因查询包含在from中,所以为derived。[select id,name from t1 where other_column=”]

第三行:select列表中的子查询,select_type为subquery,为整个查询中的第二个select。[select id from t3]

第四行:select_type为union,说明第四个select是union里的第二个select,最先执行。[select name,id from t2]

第五行:代表从union的临时表中读取行的阶段,table列的<union1,4>表示用第一个和第四个select的结果进行union操作。[两个结果union操作]

Extra:包含不适合在其他列中显示但十分重要的额外信息。

Only index,这意味着信息只用索引树中的信息检索出的,这比扫描整个表要快。

Using where 是使用上了where限制,表示MySQL服务器在存储引擎收到记录后进行“后过滤”(Post-filter),如果查询未能使用索引,Using where的作用只是提醒我们MySQL将用where子句来过滤结果集。

impossible where 表示用不着where,一般就是没查出来啥。

Using filesort(MySQL中无法利用索引完成的排序操作称为“文件排序”)当我们试图对一个没有索引的字段进行排序时,就是filesoft。它跟文件没有任何关系,实际上是内部的一个快速排序。

Using temporary(表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询),使用filesort和temporary的话会很吃力,WHERE和ORDER BY的索引经常无法兼顾,如果按照WHERE来确定索引,那么在ORDER BY时,就必然会引起Using filesort,这就要看是先过滤再排序划算,还是先排序再过滤划算。

https://blog.csdn.net/xifeijian/article/details/19773795

mysql优化一般步聚(教程)

1.1优化SQL的一般步骤

11.1.1 通过show status和应用特点了解各种SQL的执行频率

通过SHOW STATUS可以提供服务器状态信息,也可以使用mysqladminextended- status命令获得。SHOW STATUS可以根据需要显示session级别的统计结果和global 级别的统计结果。

以下几个参数对Myisam和Innodb存储引擎都计数:

1. Com_select 执行select操作的次数,一次查询只累加1;

2. Com_insert执行insert操作的次数,对于批量插入的insert操作,只累加一次;

3. Com_update执行update操作的次数;

4. Com_delete 执行delete操作的次数; Continue reading

MySQL优化篇-查询优化

可以参考一下官方文档中的解释。

http://dev.mysql.com/doc/refman/5.1/zh/optimization.html

7. 优化
7.1. 优化概述
7.1.1. MySQL设计局限与折衷
7.1.2. 为可移植性设计应用程序
7.1.3. 我们已将MySQL用在何处?
7.1.4. MySQL基准套件
7.1.5. 使用自己的基准
7.2. 优化SELECT语句和其它查询
7.2.1. EXPLAIN语法(获取SELECT相关信息)
7.2.2. 估计查询性能
7.2.3. SELECT查询的速度
7.2.4. MySQL怎样优化WHERE子句
7.2.5. 范围优化
7.2.6. 索引合并优化
7.2.7. MySQL如何优化IS NULL
7.2.8. MySQL如何优化DISTINCT
7.2.9. MySQL如何优化LEFT JOIN和RIGHT JOIN
7.2.10. MySQL如何优化嵌套Join
7.2.11. MySQL如何简化外部联合
7.2.12. MySQL如何优化ORDER BY
7.2.13. MySQL如何优化GROUP BY
7.2.14. MySQL如何优化LIMIT
7.2.15. 如何避免表扫描
7.2.16. INSERT语句的速度
7.2.17. UPDATE语句的速度
7.2.18. DELETE语句的速度
7.2.19. 其它优化技巧

MySQL特异功能之:Impossible WHERE noticed after reading const tables

用EXPLAIN看MySQL的执行计划时经常会看到Impossible WHERE noticed after reading const tables这句话,意思是说MySQL通过读取“const tables”,发现这个查询是不可能有结果输出的。比如对下面的表和数据:

  create table t (a int primary key, b int) engine = innodb;
insert into t values(1, 1);
insert into t values(3, 1);

执行“EXPLAIN select * from t where a = 2”时就会输出“Impossible WHERE noticed after reading const tables”。

不 明白所谓的“const tables”是什么意思,对MySQL在查询优化时竟然可以发现一个查询不可能输出结果更是感觉不可思议。按数据库中“传统”的做法,查询优化时只会访 问模式定义和统计信息,而据我所知,数据库中使用的各种统计信息如EquiDepth、MaxDiff柱状图,MCV,属性的最大值、最小值等都不可能精 确到能够断言在上述的表中不存在“a = 2”的记录。

今天看MySQL Internal手册时才总算弄明白,原来MySQL并没有什么神奇之处,这个Impossible WHERE noticed after reading const tables的结论并不是通过统计信息做出的,而是真的去实际访问了一遍数据后,发现确实没有“a = 2”的行才得出的。

当查询中对某个表指定了主键或非空唯一索引上的等值条件,从而使得最多只可能产生一条命中结果(只对该表而言)时,MySQL在EXPLAIN之前会优先根据这一条件查找出对应的记录,并用记录的实际值替换查询中所有用到来自该表的属性的地方。一个更复杂的例子如下:

  explain select * from t as t1, t as t2 where t1.a = 1 and t2.a = t1.b + 1;

的输出结果为(由于排版关系省略了一些输出内容):
+----+...+-----------------------------------------------------+
| id | ... | Extra |
+----+...+-----------------------------------------------------+
| 1 | ... | Impossible WHERE noticed after reading const tables |
+----+...+-----------------------------------------------------+

MySQL得出上述查询不会输出结果的步骤如下:
1、首先根据t1.a = 1条件找到一条记录(1,1);
2、将上述记录中b的值1替换查询中的t1.b,即将上述查询转化为等价的“explain select 1, 1,t2.a, t2.b from t as t2 where t2.a = 1 + 1”;
3、优化器计算常量表达式的值,即计算1+1得出结果为2;
4、优化器根据t2.a = 2条件查找,发现没有命中记录;
5、优化器最终打断出上述查询不可能输出结果。

说 白了,这个“Impossible WHERE noticed after reading const tables”就不再神秘了。但从这件事,我更加感觉到MySQL是个“怪怪”的数据库,有很多地方跟惯常的做法不太一样。很多数据库会在联接时将指定了 唯一索引等值条件的表优先执行,作为查询执行的第一步,但据我所知只有MySQL将这一步骤提前到查询优化的第一步来做。这么做到底在什么情况下才有好处 好像是个很微妙的问题,对于本文中给出的这两个例子,在优化时还是执行时做这一步开销都没什么区别。不过这么做好像没什么坏处。

这么会导 致一个“怪怪”的现象,那就是EXPLAIN有时候也会被阻塞。比如“EXPLAIN select * from t where a = 2 lock in share mode”,同时又有另一个事务插入了一条a = 2的记录而没有提交时,EXPLAIN就会在那里等锁。

MySQL EXPLAIN句法

Explain虽然是大家常用的分析mysql优化的办法,但对于系统级别内容的消耗资源信息就无能为力了.这时需要用到Mysql中的Profiling(程序剖析) 功能.参考:http://blog.haohtml.com/archives/4624

EXPLAIN tbl_name or EXPLAIN SELECT select_options

EXPLAIN tbl_name是DESC[RIBE] tbl_name或SHOW COLUMNS FROM tbl_name的一个同义词。

当你在一条SELECT语句前放上关键词EXPLAIN,MySQL解释它将如何处理SELECT,提供有关表如何联结和以什么次序联结的信息。

借助于EXPLAIN,你可以知道
1)你什么时候必须为表加入索引以得到一个使用索引找到记录的更快的SELECT。
2)你也能知道优化器是否以一个最佳次序联结表。为了强制优化器对一个SELECT语句使用一个特定联结次序,增加一个STRAIGHT_JOIN子句。

对于非简单的联结,EXPLAIN为用于SELECT语句中的每个表返回一行信息。表以他们将被读入的顺序被列出。
MySQL用一边扫描多次联结的方式解决所有联结,这意味着MySQL 1)从第一个表中读一行,2)然后找到在第二个表中的一个匹配行,3)然后在第3个表中等等。=>当所有的表被处理完,它输出选择的列并且回溯表列表直到找到一个表有更多的匹配行,从该表读入下一行并继续处理下一个表。

从EXPLAIN的输出包括下面列:

table输出的行所引用的表。

type
联结类型。各种类型的信息在下面给出。

possible_keys
possible_keys列指出MySQL能使用哪个索引在该表中找到行。
注意,该列完全独立于表的次序。这意味着在possible_keys中的某些键实际上不能以生成的表次序使用。
如果该列是空的,没有相关的索引。在这种情况下,你也许能通过检验WHERE子句看是否它引用某些列或列不是适合索引来提高你的查询性能。如果是这样,创造一个适当的索引并且在用EXPLAIN检查查询。见7.8 ALTER TABLE句法。为了看清一张表有什么索引,使用SHOW INDEX FROM tbl_name。 (可以查出,哪些索引基本上未用,或用的少)

key
key列显示MySQL实际决定使用的键。如果没有索引被选择,键是NULL。

key_len
key_len列显示MySQL决定使用的键长度。如果键是NULL,长度是NULL。注意这告诉我们MySQL将实际使用一个多部键值的几个部分。

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

rows
rows列显示MySQL相信它必须检验以执行查询的行数。

Extra
如果是Only index,这意味着信息只用索引树中的信息检索出的。通常,这比扫描整个表要快。
如果是where used,它意味着一个WHERE子句将被用来限制哪些行与下一个表匹配或发向客户。
如果是impossible where 表示用不着where

Type的取值常会有下面这些:

system
表仅有一行(=系统表)。这是const联结类型的一个特例。

const
表有最多一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被剩下的优化器认为是常数。 const表很快,因为它们只读取一次!

eq_ref
对于每个来自于先前的表的行组合,从该表中读取一行。这可能是最好的联结类型,除了const类型。它用在一个索引的所有部分被联结使用并且索引是UNIQUE或PRIMARY KEY。

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

range
只有在一个给定范围的行将被检索,使用一个索引选择行。ref列显示哪个索引被使用。

index
这与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。

ALL
对于每个来自于先前的表的行组合,将要做一个完整的表扫描。
如果表格是第一个没标记const的表,这通常不好,并且通常在所有的其他情况下很差。你通常可以通过增加更多的索引来避免ALL,使得行能从早先的表中基于常数值或列值被检索出。

通过相乘EXPLAIN输出的rows行的所有值,你能得到一个关于一个联结要多好的提示。这应该粗略地告诉你MySQL必须检验多少行以执行查询。当你使用max_join_size变量限制查询时,也用这个数字。见10.2.3 调节服务器参数。

下列例子显示出一个JOIN如何能使用EXPLAIN提供的信息逐步被优化。

假定你有显示在下面的SELECT语句,你使用EXPLAIN检验:

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
tt.ProjectReference, tt.EstimatedShipDate,
tt.ActualShipDate, tt.ClientID,
tt.ServiceCodes, tt.RepetitiveID,
tt.CurrentProcess, tt.CurrentDPPerson,
tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
et_1.COUNTRY, do.CUSTNAME
FROM tt, et, et AS et_1, do
WHERE tt.SubmitTime IS NULL
AND tt.ActualPC = et.EMPLOYID
AND tt.AssignedPC = et_1.EMPLOYID
AND tt.ClientID = do.CUSTNMBR;

对于这个例子,假定:

被比较的列被声明如下: 表 列 列类型
tt ActualPC CHAR(10)
tt AssignedPC CHAR(10)
tt ClientID CHAR(10)
et EMPLOYID CHAR(15)
do CUSTNMBR CHAR(15)

表有显示在下面的索引: 表 索引
tt ActualPC
tt AssignedPC
tt ClientID
et EMPLOYID(主键)
do CUSTNMBR(主键)

tt.ActualPC值不是均匀分布的。
开始,在任何优化被施行前,EXPLAIN语句产生下列信息:

table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
do ALL PRIMARY NULL NULL NULL 2135
et_1 ALL PRIMARY NULL NULL NULL 74
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872
range checked for each record (key map: 35)

因为type对每张表是ALL,这个输出显示MySQL正在对所有表进行一个完整联结!这将花相当长的时间,因为必须检验每张表的行数的乘积次数!对于一个实例,这是74 * 2135 * 74 * 3872 = 45,268,558,720行。如果表更大,你只能想象它将花多长时间……

如果列声明不同,这里的一个问题是MySQL(还)不能高效地在列上使用索引。在本文中,VARCHAR和CHAR是相同的,除非他们声明为不同的长度。因为tt.ActualPC被声明为CHAR(10)并且et.EMPLOYID被声明为CHAR(15),有一个长度失配。

为了修正在列长度上的不同,使用ALTER TABLE将ActualPC的长度从10个字符变为15个字符:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

现在tt.ActualPC和et.EMPLOYID都是VARCHAR(15),再执行EXPLAIN语句产生这个结果:

table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used
do ALL PRIMARY NULL NULL NULL 2135
range checked for each record (key map: 1)
et_1 ALL PRIMARY NULL NULL NULL 74
range checked for each record (key map: 1)
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1

这不是完美的,但是是好一些了(rows值的乘积少了一个74一个因子),这个版本在几秒内执行。

第2种改变能消除tt.AssignedPC = et_1.EMPLOYID和tt.ClientID = do.CUSTNMBR比较的列的长度失配:

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
MODIFY ClientID VARCHAR(15);

现在EXPLAIN产生的输出显示在下面:

table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
tt ref AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where used
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1

这“几乎”象它能得到的一样好。

剩下的问题是,缺省地,MySQL假设在tt.ActualPC列的值是均匀分布的,并且对tt表不是这样。幸好,很容易告诉MySQL关于这些:

shell> myisamchk –analyze PATH_TO_MYSQL_DATABASE/tt
shell> mysqladmin refresh

现在联结是“完美”的了,而且EXPLAIN产生这个结果:

table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1

注意在从EXPLAIN输出的rows列是一个来自MySQL联结优化器的“教育猜测”;为了优化查询,你应该检查数字是否接近事实。如果不是,你可以通过在你的SELECT语句里面使用STRAIGHT_JOIN并且试着在在FROM子句以不同的次序列出表,可能得到更好的性能。

mysql查询语句优化命令mysql explain

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 
使用方法,在select语句前加上explain就可以了: 

如:explain select surname,first_name form a,b where a.id=b.id 

分析结果形式如下: 
table |  type | possible_keys | key | key_len  | ref | rows | Extra 
EXPLAIN列的解释: 

table 
显示这一行的数据是关于哪张表的 

type 
这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL 

possible_keys 
显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句 

key 
实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引 

key_len 
使用的索引的长度。在不损失精确性的情况下,长度越短越好 

ref 
显示索引的哪一列被使用了,如果可能的话,是一个常数 

rows 
MYSQL认为必须检查的用来返回请求数据的行数 

Extra 
关于MYSQL如何解析查询的额外信息。将在表4.3中讨论,但这里可以看到的坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢 

extra列返回的描述的意义 

Distinct 
一旦MYSQL找到了与行相联合匹配的行,就不再搜索了 

Not exists 
MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行, 

就不再搜索了 

Range checked for each 

Record(index map:#) 
没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一 

Using filesort 
看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行 

Using index 
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候 

Using temporary 
看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上 

Where used 
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题 

不同连接类型的解释(按照效率高低的顺序排序) 

system 
表只有一行:system表。这是const连接类型的特殊情况 

const 
表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待 

eq_ref 
在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用 

ref 
这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好 

range 
这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况 

index 
这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好,因为索引一般小于表数据) 

ALL 
这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免