MySQL语法EXPLAIN句法.docx

1、MySQL语法EXPLAIN句法EXPLAIN句法(得到关于SELECT的信息) EXPLAIN tbl_nameor EXPLAIN SELECT select_optionsEXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一个同义词。 当你在一条SELECT语句前放上关键词EXPLAIN，MySQL解释它将如何处理SELECT，提供有关表如何联结和以什么次序联结的信息。 借助于EXPLAIN，你可以知道你什么时候必须为表加入索引以得到一个使用索引找到记录的更快的SELECT。你也能知道优化器是否以一个最佳次序联结表

2、。为了强制优化器对一个SELECT语句使用一个特定联结次序，增加一个STRAIGHT_JOIN子句。 对于非简单的联结，EXPLAIN为用于SELECT语句中的每个表返回一行信息。表以他们将被读入的顺序被列出。MySQL用一边扫描多次联结的方式解决所有联结，这意味着MySQL从第一个表中读一行，然后找到在第二个表中的一个匹配行，然后在第3个表中等等。当所有的表被处理完，它输出选择的列并且回溯表列表直到找到一个表有更多的匹配行，从该表读入下一行并继续处理下一个表。 从EXPLAIN的输出包括下面列： table 输出的行所引用的表。 type 联结类型。各种类型的信息在下面给出。 possibl

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

4、n key_len列显示MySQL决定使用的键长度。如果键是NULL，长度是NULL。注意这告诉我们MySQL将实际使用一个多部键值的几个部分。 ref ref列显示哪个列或常数与key一起用于从表中选择行。 rows rows列显示MySQL相信它必须检验以执行查询的行数。 Extra 如果Extra列包括文字Only index，这意味着信息只用索引树中的信息检索出的。通常，这比扫描整个表要快。如果Extra列包括文字where used，它意味着一个WHERE子句将被用来限制哪些行与下一个表匹配或发向客户。 不同的联结类型列在下面，以最好到最差类型的次序： system 桌子仅有一行(=

5、系统表)。这是const联结类型的一个特例。 const 桌子有最多一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被剩下的优化器认为是常数。 const表很快，因为它们只读取一次！ eq_ref 对于每个来自于先前的表的行组合，从该表中读取一行。这可能是最好的联结类型，除了const类型。它用在一个索引的所有部分被联结使用并且索引是UNIQUE或PRIMARY KEY。 ref 对于每个来自于先前的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联结只使用键的最左面前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联结不能基于键值选择单个行的话)，使

6、用ref。如果被使用的键仅仅匹配一些行，该联结类型是不错的。 range 只有在一个给定范围的行将被检索，使用一个索引选择行。ref列显示哪个索引被使用。 index 这与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。 ALL 对于每个来自于先前的表的行组合，将要做一个完整的表扫描。如果表格是第一个没标记const的表，这通常不好，并且通常在所有的其他情况下很差。你通常可以通过增加更多的索引来避免ALL，使得行能从早先的表中基于常数值或列值被检索出。 通过相乘EXPLAIN输出的rows行的所有值，你能得到一个关于一个联结要多好的提示。这应该粗略地告诉你My

7、SQL必须检验多少行以执行查询。当你使用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.CurrentPro

8、cess, 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

9、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 Extraet ALL PRIMARY NULL NULL NULL

10、74do ALL PRIMARY NULL NULL NULL 2135et_1 ALL PRIMARY NULL NULL NULL 74tt 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行。如果表更大，你只能想象它将

11、花多长时间 如果列声明不同，这里的一个问题是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语句产生这个结果： ta

12、ble type possible_keys key key_len ref rows Extratt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where useddo 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

13、.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 Extraet ALL PRIMARY NU

14、LL NULL NULL 74tt ref AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where usedet_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1这“几乎”象它能得到的一样好。 剩下的问题是，缺省地，MySQL假设在tt.ActualPC列的值是均匀分布的，并且对tt表不是这样。幸好，很容易告诉MySQL关于这些： shell myisamchk -analyze PATH_TO_MYSQL

15、_DATABASE/ttshell mysqladmin refresh现在联结是“完美”的了，而且EXPLAIN产生这个结果： table type possible_keys key key_len ref rows Extratt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where usedet eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1do eq_ref PRIMARY PRIMARY 15 t

16、t.ClientID 1注意在从EXPLAIN输出的rows列是一个来自MySQL联结优化器的“教育猜测”；为了优化查询，你应该检查数字是否接近事实。如果不是，你可以通过在你的SELECT语句里面使用STRAIGHT_JOIN并且试着在在FROM子句以不同的次序列出表，可能得到更好的性能。DESCRIBE句法 (得到列的信息）DESCRIBE | DESC tbl_name col_name | wildDESCRIBE提供关于一张表的列的信息。col_name可以是一个列名字或包含SQL的“%”和“_”通配符的一个字符串。 如果列类型不同于你期望的是基于一个CREATE TABLE语句，注意

17、MySQL有时改变列类型。见7.7.1 隐含的列说明变化。 这个语句为了与 Oracle 兼容而提供的。 SHOW语句提供类似的信息。见7.21 SHOW句法(得到表，列的信息)。 LOCK TABLES/UNLOCK TABLES句法LOCK TABLES tbl_name AS alias READ | LOW_PRIORITY WRITE , tbl_name READ | LOW_PRIORITY WRITE .UNLOCK TABLESLOCK TABLES为当前线程锁定表。UNLOCK TABLES释放被当前线程持有的任何锁。当线程发出另外一个LOCK TABLES时，或当服务器的

18、连接被关闭时，当前线程锁定的所有表自动被解锁。 如果一个线程获得在一个表上的一个READ锁，该线程(和所有其他线程)只能从表中读。如果一个线程获得一个表上的一个WRITE锁，那么只有持锁的线程READ或WRITE表，其他线程被阻止。 每个线程等待(没有超时)直到它获得它请求的所有锁。 WRITE锁通常比READ锁有更高的优先级，以确保更改尽快被处理。这意味着，如果一个线程获得READ锁，并且然后另外一个线程请求一个WRITE锁, 随后的READ锁请求将等待直到WRITE线程得到了锁并且释放了它。当线程正在等待WRITE锁时，你可以使用LOW_PRIORITY WRITE允许其他线程获得READ

19、锁。如果你肯定终于有个时刻没有线程将有一个READ锁，你应该只使用LOW_PRIORITY WRITE。 当你使用LOCK TABLES时，你必须锁定你将使用的所有表！如果你正在一个查询中多次使用一张表(用别名)，你必须对每个别名得到一把锁！这条政策保证表锁定不会死锁。 注意你应该不锁定任何你正在用INSERT DELAYED使用的表，这是因为在这种情况下，INSERT被一个不同的线程执行。 通常，你不必锁定表，因为所有单个UPDATE语句是原语；没有其他线程能防碍任何其它正在执行SQL语句的线程。当你想锁定表，有一些情况： 如果你将在一堆表上运行许多操作，锁定你将使用的表是较快的。当然缺点是

20、，没有其他线程能更新一个READ锁定的表并且没有其他线程能读一个WRITE-锁定的表。 MySQL不支持事务环境，所以如果你想要保证在一个SELECT和一个UPDATE之间没有其他线程到来，你必须使用LOCK TABLES。下面显示的例子要求LOCK TABLES以便安全地执行： mysql LOCK TABLES trans READ, customer WRITE; mysql select sum(value) from trans where customer_id= some_id; mysql update customer set total_value=sum_from_pre

21、vious_statement where customer_id=some_id;mysql UNLOCK TABLES;没有LOCK TABLES，另外一个线程可能有一个机会在执行SELECT和UPDATE语句之间往trans表中插入一个新行。 通过使用渐增更改(UPDATE customer SET value=value+new_value)或LAST_INSERT_ID()函数，在很多情况下你能使用LOCK TABLES来避免。 你也可以使用用户级锁定函数GET_LOCK()和RELEASE_LOCK()解决一些情况，这些锁保存在服务器的一张哈希表中并且用pthread_mutex_

22、lock()和pthread_mutex_unlock()实现以获得高速度。见7.4.12 其他函数。 有关锁定政策的更多信息，见10.2.8 MySQL 怎样锁定表。 SET OPTION句法SET OPTION SQL_VALUE_OPTION= value, .SET OPTION设置影响服务器或你的客户操作的各种选项。你设置的任何选择保持有效直到当前会话结束，或直到你设置选项为不同的值。 CHARACTER SET character_set_name | DEFAULT 这用给定的映射表从/到客户映射所有字符串。对character_set_name当前唯一的选项是 cp1251_k

23、oi8，但是你能容易通过编辑在MySQL源代码分发的“sql/convert.cc”文件增加新的映射。缺省映射能用character_set_name的DEFAULT值恢复。注意设置CHARACTER SET选项的语法不同于设置其他选项目的语法。 PASSWORD = PASSWORD(some password) 设置当前用户的口令。任何非匿名的用户能改变他自己的口令！ PASSWORD FOR user = PASSWORD(some password) 设置当前服务器主机上的一个特定用户的口令。只有具备存取mysql数据库的用户可以这样做。用户应该以userhostname格式给出，这里

24、user和hostname完全与他们列在mysql.user表条目的User和Host列一样。例如，如果你有一个条目其User和Host字段是bob和%.loc.gov，你将写成： mysql SET PASSWORD FOR bob%.loc.gov = PASSWORD(newpass);或mysql UPDATE mysql.user SET password=PASSWORD(newpass) where user=bob and host=%.loc.gov;SQL_AUTO_IS_NULL = 0 | 1 如果设置为1（缺省 )，那么对于一个具有一个自动加1的行的表，用下列构件能找

25、出最后插入的行：WHERE auto_increment_column IS NULL。这被一些 ODBC 程序入Access使用。 SQL_BIG_TABLES = 0 | 1 如果设置为1，所有临时表存在在磁盘上而非内存中。这将更慢一些，但是对需要大的临时表的大SELECT操作，你将不会得到The table tbl_name is full的错误。对于一个新连接的缺省值是0（即，使用内存中的临时表)。 SQL_BIG_SELECTS = 0 | 1 如果设置为0，如果一个SELECT尝试可能花很长的时间，MySQL将放弃。这在一个不妥当的WHERE语句发出时是有用的。一个大的查询被定义为

26、一个将可能必须检验多于max_join_size行的SELECT。对一个新连接的缺省值是1（它将允许所有SELECT语句)。 SQL_LOW_PRIORITY_UPDATES = 0 | 1 如果设置为1，所有INSERT、UPDATE、DELETE和LOCK TABLE WRITE语句等待，直到在受影响的表上没有未解决的SELECT或LOCK TABLE READ。 SQL_SELECT_LIMIT = value | DEFAULT 从SELECT语句返回的记录的最大数量。如果一个SELECT有一个LIMIT子句，LIMIT优先与SQL_SELECT_LIMIT值。对一个新连接的缺省值是“

27、无限”的。如果你改变了限制，缺省值能用SQL_SELECT_LIMIT的一个DEFAULT值恢复。 SQL_LOG_OFF = 0 | 1 如果设置为1，如果客户有process权限，对该客户没有日志记载到标准的日志文件中。这不影响更新日志记录！ SQL_LOG_UPDATE = 0 | 1 如果设置为0, 如果客户有process权限，对该客户没有日志记载到更新日志中。这不影响标准日志文件！ TIMESTAMP = timestamp_value | DEFAULT 为该客户设置时间。如果你使用更新日志恢复行，这被用来得到原来的时间标记。 LAST_INSERT_ID = # 设置从LAST

28、_INSERT_ID()返回的值。当你在更新一个表的命令中使用LAST_INSERT_ID()时，它存储在更新日志中。 INSERT_ID = # 设置当插入一个AUTO_INCREMENT值时，由INSERT命令使用的值。这主要与更新日志一起使用。 GRANT和REVOKE句法GRANT priv_type (column_list) , priv_type (column_list) . ON tbl_name | * | *.* | db_name.* TO user_name IDENTIFIED BY password , user_name IDENTIFIED BY passwo

29、rd . WITH GRANT OPTIONREVOKE priv_type (column_list) , priv_type (column_list) . ON tbl_name | * | *.* | db_name.* FROM user_name , user_name .GRANT在MySQL 3.22.11或以后版本中实现。对于更早MySQL版本，GRANT语句不做任何事情。 GRANT和REVOKE命令允许系统主管在4个权限级别上授权和撤回赋予MySQL用户的权利： 全局级别 全局权限作用于一个给定服务器上的所有数据库。这些权限存储在mysql.user表中。 数据库级别 数

30、据库权限作用于一个给定数据库的所有表。这些权限存储在mysql.db和mysql.host表中。 表级别 表权限作用于一个给定表的所有列。这些权限存储在mysql.tables_priv表中。 列级别 列权限作用于在一个给定表的单个列。这些权限存储在mysql.columns_priv表中。 对于GRANT如何工作的例子，见6.11 为MySQL增加新的用户权限。对于GRANT和REVOKE语句，priv_type可以指定下列的任何一个： ALL PRIVILEGES FILE RELOADALTER INDEX SELECTCREATE INSERT SHUTDOWNDELETE PROCE

31、SS UPDATEDROP REFERENCES USAGEALL是ALL PRIVILEGES的一个同义词，REFERENCES还没被实现，USAGE当前是“没有权限”的一个同义词。它能用在你想要创建一个没有权限用户的时候。 为了从一个用户撤回grant的权限，使用GRANT OPTION的一个priv_type值： REVOKE GRANT OPTION ON . FROM .;对于表，你能指定的唯一priv_type值是SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、GRANT、INDEX和ALTER。 对于列，你能指定的唯一priv_type值是(即，当你使用一个column_list子句时)是SELECT、INSERT和UPDATE。 你能通过使用ON *.*语法设置全局权限，你能通过使用ON db_name.*语法设置数据库权限。如果你指定ON *并且你有一个当前数据库，你将为该数据库设置权限。(警