MySQL语句 MySQL SHOW DATABASES 显示数据库MySQL SHOW SCHEMAS 显示数据库语句MySQL CREATE DATABASE 创建数据库语句MySQL CREATE SCHEMA 创建数据库语句MySQL SHOW CREATE DATABASE 显示创建数据库的语句MySQL SHOW CREATE SCHEMA 显示创建数据库的语句MySQL ALTER DATABASE 修改数据库语句MySQL ALTER SCHEMA 修改数据库语句MySQL DROP DATABASE删除数据库MySQL DROP SCHEMA 删除数据库MySQL USE 选择数据库语句MySQL CREATE TEMPORARY TABLE 创建临时表语句 MySQL CREATE TABLE 创建数据表语句MySQL DROP TEMPORARY TABLE 删除临时表语句MySQL DROP TABLE 删除数据表语句MySQL SELECT 查询语句MySQL INSERT INTO 增加数据语句MySQL UPDATE 更新语句MySQL ALTER TABLE 修改数据表语句MySQL SHOW TABLES 显示数据表语句MySQL DESCRIBE/DESC 查看数据表/视图语句MySQL SHOW CREATE TABLE 显示创建表语句MySQL DELETE 删除表数据语句MySQL WHERE 条件语句MySQL TRUNCATE 清空表语句MySQL LIKE 模糊匹配语句MySQL NOT LIKE 模式匹配外语句MySQL DISTINCT 去重语句MySQL AS 别名语句MySQL INSERT IGNORE INTO 忽略重复数据插入语句MySQL GROUP BY 分组语句MySQL HAVING 包含语句MySQL LIMIT限制查询数量 语句MySQL ORDER BY 排序语句MySQL BETWEEN AND 筛选范围内数据语句MySQL NOT BETWEEN AND 筛选不在范围数据语句MySQL IS NULL 查询是否为NULL值语句MySQL IS NOT NULL 查询不为NULL值语句MySQL IN 查询某个范围内的数据语句MySQL NOT IN 查询某个不在范围内的数据语句MySQL SELECTI...INTO OUTFILE将查询结果输出到外部文件语句MySQL LOAD DATA大量数据快速导入数据库语句MySQL UNION 合并两个或多个SELECT语句MySQL HAVING对聚合结果进行筛选语句MySQL SOUNDS LIKE查询发音相似语句MySQL JOIN 交叉连接语句MySQL CROSS JOIN交叉连接语句MySQL INNER JOIN交叉连接语句MySQL LEFT JOIN 左连接语句MySQL RIGHT JOIN 右连接语句MySQL EXISTS存在查询语句MySQL NOT EXISTS不存在查询语句MySQL CREATE VIEW创建视图语句MySQL RENAME TABLE重命表名/视图名语句MySQL ALTER VIEW 修改视图语句MySQL DROP VIEW 删除视图语句MySQL SHOW INDEX查看索引语句MySQL DROP INDEX删除索引语句MySQL EXPLAIN分析查询语句MySQL数据类型 MySQL tinyint、smallint、mediumint、int、bigint整数数据类型(精确值)MySQL decimal、numeric、double、float、real小数数据类型(精确、近似值)MySQL date、time、year、timestamp、datetime日期时间数据类型MySQL char、varchar、text、enum、set字符串类型MySQL bit、binary、varbinary、tinyblob、blob、mediumblob、longblob二进制数据类型MySQL空间数据类型MySQL函数 MySQL COUNT 统计函数MySQL ANY_VALUE函数 禁止 ONLY_FULL_GROUP_BY 值拒绝MySQL CASE 多分支函数MySQL LENGTH 返回字节数函数MySQL CHARACTER_LENGTH 返回字符数函数MySQL CHAR_LENGTH 返回字符数函数MySQL SUBSTRING提取字符串函数MySQL CONCAT 合拼字符串函数MySQL INSERT替换字符串函数MySQL LOWER 字符串转为小写 函数MySQL UPPER 字符串转为大写 函数MySQL LEFT 提取左侧字符 函数MySQL RIGHT 提取右侧字符 函数MySQL TRIM 删除头尾空格或指定字符 函数MySQL REPLACE 替换字符串函数MySQL REVERSE 字符串反转函数MySQL ABS求绝对值函数MySQL FIND_IN_SET 查逗号分隔特定值函数 MySQL ASCII最左边字符的数字值函数MySQL BIN将整数值转二进制字符串函数MySQL BIT_LENGTH返二进制字符串长度函数MySQL CONCAT_WS用分隔符连接字符串函数MySQL INTERVAL 返N值在区间段索引位置 函数MySQL ELT 指定位置提取子字符串函数MySQL IFNULL 处理NULL值函数MySQL EXPORT_SET二进制位生成拼接字符串函数MySQL FIELD 包含特定值的位置函数MySQL FORMAT 返回带有格式的数字函数MySQL TO_BASE64 字符串转base-64编码函数MySQL FROM_BASE64 将base-64编码解码函数MySQL AES_ENCRYPT 使用密钥进行加密函数MySQL AES_DECRYPT使用密钥解密函数MySQL HEX字符串转十六进制函数MySQL UNHEX 十六进制字符串转二进制数据函数MySQL INSTR 返回子字符串第一次出现的索引函数MySQL REGEXP 正则表达式函数MySQL NOT REGEXP不匹配正则表达式函数MySQL LCASE用于转换字符串为小写函数MySQL LOAD_FILE 加载文件到数据库函数MySQL LOCATE 搜索字符串第一次出现的位置函数MySQL LPAD左侧添加指定长度字符函数MySQL LTRIM删除字符串左侧空格的函数MySQL MAKE_SET 创建集合函数MySQL MATCH() AGAINST()两函数来实现全文搜索MySQL MID提取指定位置字符串函数MySQL OCT 返指定数值的八进制形式字符串函数MySQL OCTET_LENGTH返回字符串长度函数MySQL ORD获取字符ASCII码函数MySQL POSITION查字符串在某子字符串起始位置函数MySQL QUOTE返回用单引号包围的字符串函数MySQL REPEAT重复字符串次数函数MySQL REPLACE替换字符串中的子字符串函数MySQL REVERSE反转字符串函数MySQL RIGHT从右侧截取指定长度字符串函数MySQL RLIKE正则表达式函数MySQL RPAD向字符串的右侧填充指定字符函数MySQL RTRIM去除字符串右侧的空格函数MySQL SOUNDEX将一个字符串转为发音字符串函数MySQL SPACE生成指定数量空格函数MySQL STRCMP比较两个字符串大小函数MySQL SUBSTR从字符串中截取子字符串函数MySQL SUBSTRING_INDEX按指定字符分隔后进行截取函数MySQL UCASE 小写字母换大写字母函数Mysql WEIGHT_STRING 返回字符串的权重字符串函数MySQL =等于赋值运算符MySQL <=>运算符MySQL &(按位与)运算符MySQL > 大于运算符MySQL >>位右移运算符MySQL >=大于等于运算符MySQL <小于运算符MySQL (<>或!=)不等于运算符MySQL <<位左移运算符MySQL <= 小于等于运算符MySQL %或mod()求余运算符MySQL *乘法运算符MySQL +加法运算符MySQL -减法运算符MySQL -负号运算符MySQL JSON_EXTRACT从JSON数据提取字段值函数MySQL ->JSON数据提取运算符 MySQL JSON_UNQUOTE去除JSON值的引号函数MySQL ->>提取JSON去除引号的值运算符MySQL /除法运算符MySQL DIV整数除法运算符MySQL :=特殊赋值运算符MySQL ^位异或运算符MySQL |位或运算符MySQL ~位取反运算符MySQL ABS 返绝对值函数MySQL ACOS反余弦函数MySQL ADDDATE 日期函数MySQL DATE_ADD加时间间隔日期函数MySQL SUBDATE减时间间隔函数Mysql DATE_SUB减去时间间隔函数MySQL DATE从日期时间值中提取日期部分函数MySQL DATEDIFF计算两个日期之间的天数差异函数MySQL TIMEDIFF两日期时间的时间差函数MySQL MINUTE取日期时间中分钟数函数MySQL HOUR取日期时间中小时数函数MySQL SECOND取日期时间值中秒数函数MySQL CONVERT_TZ 将日期时间值转为另一时区函数MySQL DAYOFYEAR返回日期值对应的一年中第几天函数MySQL TIMESTAMPDIFF算两日期时间之间的时间差函数MySQL TO_DAYS日期转换为天数函数MySQL ​FROM_UNIXTIME将时间戳转换日期时间格式函数MySQL TO_SECONDS将日期时间值转换为秒数函数MySQL WEEK返回日期对应的星期数函数MySQL WEEKDAY返回日期对应的星期几的整数函数MySQL WEEKOFYEAR返回日期对应的星期数函数MySQL YEARWEEK返回日期的年和周的函数MySQL DATE_FORMAT将日期或日期时间值格式化函数MySQL STR_TO_DATE将字符串转日期或日期时间函数MySQL TIME_FORMAT格式化时间值函数MySQL UNIX_TIMESTAMP将日期时间转换时间戳函数MySQL ADDTIME时间加法运算函数MySQL AND和&&逻辑运算符与MySQL NOT和!逻辑运算符非MySQL OR和||逻辑运算符或MySQL XOR逻辑运算符异或MySQL ST_Area计算几何对象的面积MySQL Area函数

MySQL EXPLAIN分析查询语句

在 MySQL 中,EXPLAIN是一个用于分析查询语句执行计划的关键字。通过使用EXPLAIN关键字,可以在执行查询语句之前查看优化器生成的执行计划,而不会真正执行查询。执行计划提供了有关查询如何执行的信息,包括表的读取顺序使用的索引连接类型等。这对于理解查询的性能、优化查询以及发现潜在的性能问题非常有帮助。

EXPLAIN 语法

EXPLAIN [PARTITIONS] SELECT ...

其中,PARTITIONS是可选的,用于显示有关分区的信息。如果查询中涉及到分区表,那么添加PARTITIONS选项将提供关于分区的详细信息。

EXPLAIN 语句的输出字段

以下是EXPLAIN语句输出中常见的字段及其含义:

一、`id`:表示查询中每个操作的标识符。它按照执行顺序分配给每个操作。

二、`select_type`:表示查询的类型,例如`SIMPLE`表示简单查询,`PRIMARY`表示主查询,`SUBQUERY`表示子查询等。

三、`table`:表示查询中涉及的表名。

四、`partitions`:表示查询计划中涉及的分区数量。如果表没有分区,则该值为`NULL`。

五、 `type`:表示访问表的方式,例如`ALL`表示全表扫描,`index`表示索引扫描,`range`表示范围扫描等。

六、`possible_keys`:表示可能用于查询的索引列表。

七、`key`:表示实际用于查询的索引。

八、 `key_len`:表示索引中使用的字节数。

九、`ref`:表示在连接操作中引用的列或常量。

十、`rows`:表示估计的返回行数。

十一、 `filtered`:百分比值,剩下多少满足查询条件记录数量的比例。

十二、 `Extra`:包含有关查询执行的其他信息,例如使用的连接类型、文件排序、临时表等。


一、id

select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序,该字段通常与table字段搭配来分析。

#1.id相同,执行顺序从上到下。:id相同,执行顺序从上到下,搭配table列进行观察可知,执行顺序为t1->t3->t2。

#2.id不同,如果是子查询,id的序号会递增,id值越大执行优先级越高。:如果是子查询id的序号会递增,id值越大执行优先级越高,搭配table列可知,执行顺序为t3->t1->t2。

#3.id相同不同,同时存在。:id如果相同,可认为是同一组,执行顺序从上到下。在所有组中,id值越大执行优先级越高。所以执行顺序为t3->derived2(衍生表,也可以说临时表)->t2。

总结:id的值表示select子句或表的执行顺序,id相同,执行顺序从上到下,id不同,值越大的执行优先级越高。

二、select_type

查询的类型,主要用于区别普通查询、联合查询、子查询等复杂的查询。其值主要有六个:

#1.SIMPLE:简单的select查询,查询中不包含子查询或union查询。

#2.PRIMARY:查询中若包含任何复杂的子部分,最外层查询为PRIMARY,也就是最后加载的就是PRIMARY。

#3.SUBQUERY:在select或where列表中包含了子查询,就为被标记为SUBQUERY。

#4.DERIVED:在from列表中包含的子查询会被标记为DERIVED(衍生),MySQL会递归执行这些子查询,将结果放在临时表中。

#5.UNION:若第二个select出现在union后,则被标记为UNION,若union包含在from子句的子查询中,外层select将被标记为DERIVED。

#6.UNION RESULT:从union表获取结果的select。

三、显示sql操作属于哪张表的。

四、partitions

官方定义为The matching partitions(匹配的分区),该字段应该是看table所在的分区吧(不晓得理解错误没)。值为NULL表示表未被分区。

五、type

表示查询所使用的访问类型,type的值主要有八种,该值表示查询的sql语句好坏,从最好到最差依次为:system>const>eq_ref>ref>range>index>ALL

#1.system:表只有一行记录(等于系统表),是const的特例类型,平时不会出现,可以忽略不计。

但是笔者发现在MySQL5.7.22时,不会出现该字段值,只能出现const,但是在MySQL5.7版本以下可以出现该情况。猜测MySQL5.7版本是不是进行了优化,因为system官网的解释:注:两个引擎的执行信息不一样,5.5.48执行过程中产生了临时表(DERIVED),5.7.22为简单查询。

#2.const:表示通过一次索引就找到了结果,常出现于primary key或unique索引。因为只匹配一行数据,所以查询非常快。如将主键置于where条件中,MySQL就能将查询转换为一个常量。

注:对于system和const可能实际意义并不是很大,因为单表单行查询本来就快,意义不大。

#3.eq_ref:唯一索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见主键或唯一索引扫描。

注:经理只有一人,进行了tb_dept的主键扫描。

#4.ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,返回匹配某值(某条件)的多行值,属于查找和扫描的混合体。

由于是非唯一性索引扫描,所以对tb_emp表的deptid字段创建索引:

create index idx_tb_emp_deptid on tb_emp(deptid);

#5.range:只检索给定范围的行,使用一个索引来检索行,可以在key列中查看使用的索引,一般出现在where语句的条件中,如使用between、>、<、in等查询。这种索引的范围扫描比全表扫描要好,因为索引的开始点和结束点都固定,不用扫描全索引。虽然我们为deptid字段创建了索引并在where中使用了between等,但在如下情况type仍为ALL。

对比两图,可以看到使用deptid和id进行操作,其type的值一个是ALL也就是进行了全表扫描,一个是range进行了指定索引范围值检索。可能原因deptid并不是唯一索引。

对于以上问题,需要具体问题具体分析,并不能一概而论。

#6.index:全索引扫描,index和ALL的区别:index只遍历索引树,通常比ALL快,因为索引文件通常比数据文件小。虽说index和ALL都是全表扫描,但是index是从索引中读取,ALL是从磁盘中读取。

#7.ALL:全表扫描。

注:一般来说,需保证查询至少达到range级别,最好能达到ref。

六、possible_keys

显示可能应用在表中的索引,可能一个或多个。查询涉及到的字段若存在索引,则该索引将被列出,但不一定被查询实际使用。

七、key

实际中使用的索引,如为NULL,则表示未使用索引。若查询中使用了覆盖索引,则该索引和查询的select字段重叠。

八、key_len

表示索引中所使用的字节数,可通过该列计算查询中使用的索引长度。在不损失精确性的情况下,长度越短越好。key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,并不是通过表内检索出的。

九、ref

显示关联的字段。如果使用常数等值查询,则显示const,如果是连接查询,则会显示关联的字段。

注:由于id相同,因此从上到下执行:

#1.tb_emp表为非唯一性索引扫描,实际使用的索引列为idx_name,由于tb_emp.name='rose'为一个常量,所以ref=const。

#2.tb_dept为唯一索引扫描,从sql语句可以看出,实际使用了PRIMARY主键索引,ref=db01.tb_emp.deptid表示关联了db01数据库中tb_emp表的deptid字段。

十、rows

根据表统计信息及索引选用情况大致估算出找到所需记录所要读取的行数。当然该值越小越好。

十一、filtered

百分比值,表示存储引擎返回的数据经过滤后,剩下多少满足查询条件记录数量的比例。

十二、Extra

显示十分重要的额外信息。其取值有以下几个:

#1.Using filesort

Using filesort表明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。

mysql中无法利用索引完成的排序操作称为“文件排序”。

出现Using filesort就非常危险了,在数据量非常大的时候几乎“九死一生”。出现Using filesort尽快优化sql语句。

deptname字段未建索引的情况。

为deptname字段创建索引后。

#2.Using temporary

使用了临时表保存中间结果,常见于排序order by和分组查询group by。非常危险,“十死无生”,急需优化。

将tb_emp中name的索引先删除,出现如下图结果,非常烂,Using filesort和Using temporary,“十死无生”。

为name字段创建索引后。

#3.Using index

表明相应的select操作中使用了覆盖索引,避免访问表的额外数据行,效率不错。

如果同时出现了Using where,表明索引被用来执行索引键值的查找。(where deptid=1)

如果没有同时出现Using where,表明索引用来读取数据而非执行查找动作。

删除tb_emp表中name和deptid字段的单独索引,创建复合索引。

从这里给出覆盖索引的定义:select的数据列只从索引中就能取得数据,不必读取数据行。通过上面的例子理解:创建了(name,deptid)的复合索引,查询的时候也使用复合索引或部分,这就形成了覆盖索引。简记:查询使用复合索引,并且查询的列就是索引列,不能多,个数需对应。

使用优先级Using index>Using filesort(九死一生)>Using temporary(十死无生)。也就说出现后面两项表明sql语句是非常烂的,急需优化!!!

EXPLAIN 语句的案例

假设有一个名为`students`的表,包含以下列:

- `id`:学生编号,主键。

- `name`:学生姓名。

- `age`:学生年龄。

- `gender`:学生性别。


要查询年龄大于 20 岁的学生信息,可以使用以下查询语句:

SELECT * FROM students WHERE age > 20;

使用EXPLAIN关键字来分析该查询的执行计划:

EXPLAIN SELECT * FROM students WHERE age > 20;

这将返回以下执行计划信息:

+----+-------------+-------+------+---------------+------+---------+------+--------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |
+----+-------------+-------+------+---------------+------+---------+------+--------+------+-------+
| 1 | SIMPLE | students | NULL | range | age | age | 5 | NULL | 10 | 100.00 |
+----+-------------+-------+------+---------------+------+---------+------+--------+------+-------+

在这个执行计划中,我们可以看到以下信息:

通过分析执行计划,我们可以了解查询的执行方式,包括使用的索引、表的读取顺序、连接类型等。这有助于我们优化查询,提高查询的性能。

EXPLAIN 语句的注意事项

1. EXPLAIN语句不会真正执行查询,而是生成执行计划信息。因此,它不会返回实际的查询结果,只会返回有关查询执行的信息。

2. 在分析查询计划时,需要注意以下几点:

   - `type`字段表示访问表的方式。常见的类型包括`ALL`(全表扫描)、`index`(索引扫描)、`range`(范围扫描)等。尽量避免全表扫描,而是使用索引扫描或范围扫描来提高查询效率。

   - `key`字段表示实际使用的索引。如果可能的话,尽量使用合适的索引来加速查询。

   - `rows`字段表示估计的返回行数。这可以帮助你了解查询的规模和可能的性能影响。

   - `filtered`字段表示在表扫描之前,根据条件过滤掉的行数的百分比。较高的过滤百分比表示查询在扫描表之前已经排除了许多不符合条件的行,这可以提高查询效率。

3. 在分析查询计划时,还可以考虑以下几点:

   - 检查索引的选择是否正确。如果查询计划中显示没有使用索引,或者使用了不合适的索引,可以考虑创建或修改索引来优化查询。

   - 检查连接类型。如果查询中涉及到多个表的连接,可以查看连接类型(例如`INNER JOIN`、`LEFT JOIN`等)是否符合预期。

   - 分析子查询的性能。如果查询中包含子查询,可以查看子查询的执行计划,以确定是否存在性能问题。

4. 请注意,执行计划信息可能会受到查询优化器的影响,不同的优化器版本或配置可能会生成不同的执行计划。因此,在优化查询时,可能需要进行一些实验和调整,以找到最佳的执行计划。

通过使用EXPLAIN关键字和分析执行计划信息,可以深入了解查询的执行方式,发现潜在的性能问题,并采取相应的优化措施来提高查询的效率和性能。