MySQL语法EXPLAIN句法.docx
《MySQL语法EXPLAIN句法.docx》由会员分享，可在线阅读，更多相关《MySQL语法EXPLAIN句法.docx（12页珍藏版）》请在冰豆网上搜索。
MySQL语法EXPLAIN句法
EXPLAIN句法（得到关于SELECT的信息）
EXPLAINtbl_name
orEXPLAINSELECTselect_options
EXPLAINtbl_name是DESCRIBEtbl_name或SHOWCOLUMNSFROMtbl_name的一个同义词。
当你在一条SELECT语句前放上关键词EXPLAIN，MySQL解释它将如何处理SELECT，提供有关表如何联结和以什么次序联结的信息。
借助于EXPLAIN，你可以知道你什么时候必须为表加入索引以得到一个使用索引找到记录的更快的SELECT。
你也能知道优化器是否以一个最佳次序联结表。
为了强制优化器对一个SELECT语句使用一个特定联结次序，增加一个STRAIGHT_JOIN子句。
对于非简单的联结，EXPLAIN为用于SELECT语句中的每个表返回一行信息。
表以他们将被读入的顺序被列出。
MySQL用一边扫描多次联结的方式解决所有联结，这意味着MySQL从第一个表中读一行，然后找到在第二个表中的一个匹配行，然后在第3个表中等等。
当所有的表被处理完，它输出选择的列并且回溯表列表直到找到一个表有更多的匹配行，从该表读入下一行并继续处理下一个表。
从EXPLAIN的输出包括下面列：
table
输出的行所引用的表。
type
联结类型。
各种类型的信息在下面给出。
possible_keys
possible_keys列指出MySQL能使用哪个索引在该表中找到行。
注意，该列完全独立于表的次序。
这意味着在possible_keys中的某些键实际上不能以生成的表次序使用。
如果该列是空的，没有相关的索引。
在这种情况下，你也许能通过检验WHERE子句看是否它引用某些列或列不是适合索引来提高你的查询性能。
如果是这样，创造一个适当的索引并且在用EXPLAIN检查查询。
见7.8ALTERTABLE句法。
为了看清一张表有什么索引，使用SHOWINDEXFROMtbl_name。
key
key列显示MySQL实际决定使用的键。
如果没有索引被选择，键是NULL。
key_len
key_len列显示MySQL决定使用的键长度。
如果键是NULL，长度是NULL。
注意这告诉我们MySQL将实际使用一个多部键值的几个部分。
ref
ref列显示哪个列或常数与key一起用于从表中选择行。
rows
rows列显示MySQL相信它必须检验以执行查询的行数。
Extra
如果Extra列包括文字Onlyindex，这意味着信息只用索引树中的信息检索出的。
通常，这比扫描整个表要快。
如果Extra列包括文字whereused，它意味着一个WHERE子句将被用来限制哪些行与下一个表匹配或发向客户。
不同的联结类型列在下面，以最好到最差类型的次序：
system
桌子仅有一行（=系统表）。
这是const联结类型的一个特例。
const
桌子有最多一个匹配行，它将在查询开始时被读取。
因为仅有一行，在这行的列值可被剩下的优化器认为是常数。
const表很快，因为它们只读取一次！
eq_ref
对于每个来自于先前的表的行组合，从该表中读取一行。
这可能是最好的联结类型，除了const类型。
它用在一个索引的所有部分被联结使用并且索引是UNIQUE或PRIMARYKEY。
ref
对于每个来自于先前的表的行组合，所有有匹配索引值的行将从这张表中读取。
如果联结只使用键的最左面前缀，或如果键不是UNIQUE或PRIMARYKEY（换句话说，如果联结不能基于键值选择单个行的话），使用ref。
如果被使用的键仅仅匹配一些行，该联结类型是不错的。
range
只有在一个给定范围的行将被检索，使用一个索引选择行。
ref列显示哪个索引被使用。
index
这与ALL相同，除了只有索引树被扫描。
这通常比ALL快，因为索引文件通常比数据文件小。
ALL
对于每个来自于先前的表的行组合，将要做一个完整的表扫描。
如果表格是第一个没标记const的表，这通常不好，并且通常在所有的其他情况下很差。
你通常可以通过增加更多的索引来避免ALL，使得行能从早先的表中基于常数值或列值被检索出。
通过相乘EXPLAIN输出的rows行的所有值，你能得到一个关于一个联结要多好的提示。
这应该粗略地告诉你MySQL必须检验多少行以执行查询。
当你使用max_join_size变量限制查询时，也用这个数字。
见10.2.3调节服务器参数。
下列例子显示出一个JOIN如何能使用EXPLAIN提供的信息逐步被优化。
假定你有显示在下面的SELECT语句，你使用EXPLAIN检验：
EXPLAINSELECTtt.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
FROMtt,et,etASet_1,do
WHEREtt.SubmitTimeISNULL
ANDtt.ActualPC=et.EMPLOYID
ANDtt.AssignedPC=et_1.EMPLOYID
ANDtt.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语句产生下列信息：
tabletypepossible_keyskeykey_lenrefrowsExtra
etALLPRIMARYNULLNULLNULL74
doALLPRIMARYNULLNULLNULL2135
et_1ALLPRIMARYNULLNULLNULL74
ttALLAssignedPC,ClientID,ActualPCNULLNULLNULL3872
rangecheckedforeachrecord（keymap:
35）
因为type对每张表是ALL，这个输出显示MySQL正在对所有表进行一个完整联结！
这将花相当长的时间，因为必须检验每张表的行数的乘积次数！
对于一个实例，这是74*2135*74*3872=45,268,558,720行。
如果表更大，你只能想象它将花多长时间……
如果列声明不同，这里的一个问题是MySQL（还）不能高效地在列上使用索引。
在本文中，VARCHAR和CHAR是相同的，除非他们声明为不同的长度。
因为tt.ActualPC被声明为CHAR（10）并且et.EMPLOYID被声明为CHAR（15），有一个长度失配。
为了修正在列长度上的不同，使用ALTERTABLE将ActualPC的长度从10个字符变为15个字符：
mysql>ALTERTABLEttMODIFYActualPCVARCHAR（15）;
现在tt.ActualPC和et.EMPLOYID都是VARCHAR（15），再执行EXPLAIN语句产生这个结果：
tabletypepossible_keyskeykey_lenrefrowsExtra
ttALLAssignedPC,ClientID,ActualPCNULLNULLNULL3872whereused
doALLPRIMARYNULLNULLNULL2135
rangecheckedforeachrecord（keymap:
1）
et_1ALLPRIMARYNULLNULLNULL74
rangecheckedforeachrecord（keymap:
1）
eteq_refPRIMARYPRIMARY15tt.ActualPC1
这不是完美的，但是是好一些了（rows值的乘积少了一个74一个因子），这个版本在几秒内执行。
第2种改变能消除tt.AssignedPC=et_1.EMPLOYID和tt.ClientID=do.CUSTNMBR比较的列的长度失配：
mysql>ALTERTABLEttMODIFYAssignedPCVARCHAR（15）,
MODIFYClientIDVARCHAR（15）;
现在EXPLAIN产生的输出显示在下面：
tabletypepossible_keyskeykey_lenrefrowsExtra
etALLPRIMARYNULLNULLNULL74
ttrefAssignedPC,ClientID,ActualPCActualPC15et.EMPLOYID52whereused
et_1eq_refPRIMARYPRIMARY15tt.AssignedPC1
doeq_refPRIMARYPRIMARY15tt.ClientID1
这“几乎”象它能得到的一样好。
剩下的问题是，缺省地，MySQL假设在tt.ActualPC列的值是均匀分布的，并且对tt表不是这样。
幸好，很容易告诉MySQL关于这些：
shell>myisamchk--analyzePATH_TO_MYSQL_DATABASE/tt
shell>mysqladminrefresh
现在联结是“完美”的了，而且EXPLAIN产生这个结果：
tabletypepossible_keyskeykey_lenrefrowsExtra
ttALLAssignedPC,ClientID,ActualPCNULLNULLNULL3872whereused
eteq_refPRIMARYPRIMARY15tt.ActualPC1
et_1eq_refPRIMARYPRIMARY15tt.AssignedPC1
doeq_refPRIMARYPRIMARY15tt.ClientID1
注意在从EXPLAIN输出的rows列是一个来自MySQL联结优化器的“教育猜测”；为了优化查询，你应该检查数字是否接近事实。
如果不是，你可以通过在你的SELECT语句里面使用STRAIGHT_JOIN并且试着在在FROM子句以不同的次序列出表，可能得到更好的性能。
DESCRIBE句法（得到列的信息）
{DESCRIBE|DESC}tbl_name{col_name|wild}
DESCRIBE提供关于一张表的列的信息。
col_name可以是一个列名字或包含SQL的“%”和“_”通配符的一个字符串。
如果列类型不同于你期望的是基于一个CREATETABLE语句，注意MySQL有时改变列类型。
见7.7.1隐含的列说明变化。
这个语句为了与Oracle兼容而提供的。
SHOW语句提供类似的信息。
见7.21SHOW句法（得到表，列的信息）。
LOCKTABLES/UNLOCKTABLES句法
LOCKTABLEStbl_name[ASalias]{READ|[LOW_PRIORITY]WRITE}
[,tbl_name{READ|[LOW_PRIORITY]WRITE}...]
...
UNLOCKTABLES
LOCKTABLES为当前线程锁定表。
UNLOCKTABLES释放被当前线程持有的任何锁。
当线程发出另外一个LOCKTABLES时，或当服务器的连接被关闭时，当前线程锁定的所有表自动被解锁。
如果一个线程获得在一个表上的一个READ锁，该线程（和所有其他线程）只能从表中读。
如果一个线程获得一个表上的一个WRITE锁，那么只有持锁的线程READ或WRITE表，其他线程被阻止。
每个线程等待（没有超时）直到它获得它请求的所有锁。
WRITE锁通常比READ锁有更高的优先级，以确保更改尽快被处理。
这意味着，如果一个线程获得READ锁，并且然后另外一个线程请求一个WRITE锁,随后的READ锁请求将等待直到WRITE线程得到了锁并且释放了它。
当线程正在等待WRITE锁时，你可以使用LOW_PRIORITYWRITE允许其他线程获得READ锁。
如果你肯定终于有个时刻没有线程将有一个READ锁，你应该只使用LOW_PRIORITYWRITE。
当你使用LOCKTABLES时，你必须锁定你将使用的所有表！
如果你正在一个查询中多次使用一张表（用别名），你必须对每个别名得到一把锁！
这条政策保证表锁定不会死锁。
注意你应该不锁定任何你正在用INSERTDELAYED使用的表，这是因为在这种情况下，INSERT被一个不同的线程执行。
通常，你不必锁定表，因为所有单个UPDATE语句是原语；没有其他线程能防碍任何其它正在执行SQL语句的线程。
当你想锁定表，有一些情况：
∙如果你将在一堆表上运行许多操作，锁定你将使用的表是较快的。
当然缺点是，没有其他线程能更新一个READ锁定的表并且没有其他线程能读一个WRITE-锁定的表。
∙MySQL不支持事务环境，所以如果你想要保证在一个SELECT和一个UPDATE之间没有其他线程到来，你必须使用LOCKTABLES。
下面显示的例子要求LOCKTABLES以便安全地执行：
∙mysql>LOCKTABLEStransREAD,customerWRITE;
∙mysql>selectsum（value）fromtranswherecustomer_id=some_id;
∙mysql>updatecustomersettotal_value=sum_from_previous_statement
∙wherecustomer_id=some_id;
mysql>UNLOCKTABLES;
没有LOCKTABLES，另外一个线程可能有一个机会在执行SELECT和UPDATE语句之间往trans表中插入一个新行。
通过使用渐增更改（UPDATEcustomerSETvalue=value+new_value）或LAST_INSERT_ID（）函数，在很多情况下你能使用LOCKTABLES来避免。
你也可以使用用户级锁定函数GET_LOCK（）和RELEASE_LOCK（）解决一些情况，这些锁保存在服务器的一张哈希表中并且用pthread_mutex_lock（）和pthread_mutex_unlock（）实现以获得高速度。
见7.4.12其他函数。
有关锁定政策的更多信息，见10.2.8MySQL怎样锁定表。
SETOPTION句法
SET[OPTION]SQL_VALUE_OPTION=value,...
SETOPTION设置影响服务器或你的客户操作的各种选项。
你设置的任何选择保持有效直到当前会话结束，或直到你设置选项为不同的值。
CHARACTERSETcharacter_set_name|DEFAULT
这用给定的映射表从/到客户映射所有字符串。
对character_set_name当前唯一的选项是cp1251_koi8，但是你能容易通过编辑在MySQL源代码分发的“sql/convert.cc”文件增加新的映射。
缺省映射能用character_set_name的DEFAULT值恢复。
注意设置CHARACTERSET选项的语法不同于设置其他选项目的语法。
PASSWORD=PASSWORD（'somepassword'）
设置当前用户的口令。
任何非匿名的用户能改变他自己的口令！
PASSWORDFORuser=PASSWORD（'somepassword'）
设置当前服务器主机上的一个特定用户的口令。
只有具备存取mysql数据库的用户可以这样做。
用户应该以user@hostname格式给出，这里user和hostname完全与他们列在mysql.user表条目的User和Host列一样。
例如，如果你有一个条目其User和Host字段是'bob'和'%.loc.gov'，你将写成：
mysql>SETPASSWORDFORbob@"%.loc.gov"=PASSWORD（"newpass"）;
或
mysql>UPDATEmysql.userSETpassword=PASSWORD（"newpass"）whereuser="bob'andhost="%.loc.gov";
SQL_AUTO_IS_NULL=0|1
如果设置为1（缺省），那么对于一个具有一个自动加1的行的表，用下列构件能找出最后插入的行：
WHEREauto_increment_columnISNULL。
这被一些ODBC程序入Access使用。
SQL_BIG_TABLES=0|1
如果设置为1，所有临时表存在在磁盘上而非内存中。
这将更慢一些，但是对需要大的临时表的大SELECT操作，你将不会得到Thetabletbl_nameisfull的错误。
对于一个新连接的缺省值是0（即，使用内存中的临时表）。
SQL_BIG_SELECTS=0|1
如果设置为0，如果一个SELECT尝试可能花很长的时间，MySQL将放弃。
这在一个不妥当的WHERE语句发出时是有用的。
一个大的查询被定义为一个将可能必须检验多于max_join_size行的SELECT。
对一个新连接的缺省值是1（它将允许所有SELECT语句）。
SQL_LOW_PRIORITY_UPDATES=0|1
如果设置为1，所有INSERT、UPDATE、DELETE和LOCKTABLEWRITE语句等待，直到在受影响的表上没有未解决的SELECT或LOCKTABLEREAD。
SQL_SELECT_LIMIT=value|DEFAULT
从SELECT语句返回的记录的最大数量。
如果一个SELECT有一个LIMIT子句，LIMIT优先与SQL_SELECT_LIMIT值。
对一个新连接的缺省值是“无限”的。
如果你改变了限制，缺省值能用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_INSERT_ID（）返回的值。
当你在更新一个表的命令中使用LAST_INSERT_ID（）时，它存储在更新日志中。
INSERT_ID=#
设置当插入一个AUTO_INCREMENT值时，由INSERT命令使用的值。
这主要与更新日志一起使用。
GRANT和REVOKE句法
GRANTpriv_type[（column_list）][,priv_type[（column_list）]...]
ON{tbl_name|*|*.*|db_name.*}
TOuser_name[IDENTIFIEDBY'password']
[,user_name[IDENTIFIEDBY'password']...]
[WITHGRANTOPTION]
REVOKEpriv_type[（column_list）][,priv_type[（column_list）]...]
ON{tbl_name|*|*.*|db_name.*}
FROMuser_name[,user_name...]
GRANT在MySQL3.22.11或以后版本中实现。
对于更早MySQL版本，GRANT语句不做任何事情。
GRANT和REVOKE命令允许系统主管在4个权限级别上授权和撤回赋予MySQL用户的权利：
全局级别
全局权限作用于一个给定服务器上的所有数据库。
这些权限存储在mysql.user表中。
数据库级别
数据库权限作用于一个给定数据库的所有表。
这些权限存储在mysql.db和mysql.host表中。
表级别
表权限作用于一个给定表的所有列。
这些权限存储在mysql.tables_priv表中。
列级别
列权限作用于在一个给定表的单个列。
这些权限存储在mysql.columns_priv表中。
对于GRANT如何工作的例子，见6.11为MySQL增加新的用户权限。
对于GRANT和REVOKE语句，priv_type可以指定下列的任何一个：
ALLPRIVILEGESFILERELOAD
ALTERINDEXSELECT
CREATEINSERTSHUTDOWN
DELETEPROCESSUPDATE
DROPREFERENCESUSAGE
ALL是ALLPRIVILEGES的一个同义词，REFERENCES还没被实现，USAGE当前是“没有权限”的一个同义词。
它能用在你想要创建一个没有权限用户的时候。
为了从一个用户撤回grant的权限，使用GRANTOPTION的一个priv_type值：
REVOKEGRANTOPTIONON...FROM...;
对于表，你能指定的唯一priv_type值是SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、GRANT、INDEX和ALTER。
对于列，你能指定的唯一priv_type值是（即，当你使用一个column_list子句时）是SELECT、INSERT和UPDATE。
你能通过使用ON*.*语法设置全局权限，你能通过使用ONdb_name.*语法设置数据库权限。
如果你指定ON*并且你有一个当前数据库，你将为该数据库设置权限。
（警
升级会员 