Mysql FAQ

隐式类型转换

例如 pid 字段类型是 int,我们 where pid = “1”,这样就会触发隐式类型转换。字段为数字类型,字符串数字转换成数字时,并不会导致索引失效如下图

当where查询操作符**左边为字符类型**时发生了隐式转换,那么会导致索引失效,造成全表扫描效率极低。

name为字符串类型,传入了数字类型001 转换后索引失效

导致查询结果不符合预期

类型隐式转换规则

当操作符与不同类型的操作数一起使用时,会发生类型转换以使操作数兼容,某些转换是隐式发生的

如果不与数字比较,则将十六进制值视为二进制字符串


如果参数之一是a TIMESTAMP或 DATETIME column,而另一个参数是常量,则在执行比较之前,该常量将转换为时间戳。这样做是为了使ODBC更友好。对于的参数,此操作未完成 IN()。为了安全起见,在进行比较时请始终使用完整的日期时间,日期或时间字符串。例如,要在BETWEEN与日期或时间值一起使用时获得最佳结果 ,请使用CAST()将值显式转换为所需的数据类型。


一个或多个表中的单行子查询不被视为常量。例如,如果子查询返回要与DATETIME 值进行比较的整数,则比较将作为两个整数进行。整数不转换为时间值。要将操作数作为DATETIME值进行比较 ,请使用 CAST()将子查询值显式转换为DATETIME


如果参数之一是十进制值,则比较取决于另一个参数。如果另一个参数是十进制或整数值,则将参数作为十进制值进行比较;如果另一个参数是浮点值,则将参数作为浮点值进行比较。


在所有其他情况下,将参数作为浮点数(实数)进行比较。例如,将字符串和数字操作数进行比较,将其作为浮点数的比较。

条件的由字符转为浮点时候

不以数字开头的字符串都将转换为0。如’abc’、’a123bc’、’abc123’都会转化为0;
以数字开头的字符串转换时会进行截取,从第一个字符截取到第一个非数字内容为止。比如’123abc’会转换为123,’012abc’会转换为012也就是12,’5.3a66b78c’会转换为5.3,其他同理。

避免规则,通过程序处理,尽量使类型与数据库字段保持一致

参考

mysql-8.隐式转换导致索引失效或查出不符合where条件结果

类型转换官方文档

Mysql 性能调试或监控常用指令

show processlist

官方手册 https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html

如果有 SUPER 权限,则可以看到全部的线程,否则,只能看到自己发起的线程(这是指,当前对应的 MySQL 帐户运行的线程)

说明各列的含义和用途,

id列:一个标识,你要kill 一个语句的时候很有用。

user列: 显示当前用户,如果不是root,这个命令就只显示你权限范围内的sql语句。

host列:显示这个语句是从哪个ip 的哪个端口上发出的。可用来追踪出问题语句的用户。

db列:显示这个进程目前连接的是哪个数据库。

command列:显示当前连接的执行的命令,一般就是休眠(sleep),查询(query),连接(connect)

通常代表资源未释放,如果是通过连接池,sleep状态应该恒定在一定数量范围内

 实战范例:因前端数据输出时(特别是输出到用户终端)未及时关闭数据库连接,导致因网络连接速度产生大量sleep连接,在网速出现异常时,数据库too many connections挂死。

 简单解读,数据查询和执行通常只需要不到0.01秒,而网络输出通常需要1秒左右甚至更长,原本数据连接在0.01秒即可释放,但是因为前端程序未执行close操作,直接输出结果,那么在结果未展现在用户桌面前,该数据库连接一直维持在sleep状态

time列:此这个状态持续的时间,单位是秒。

state列:显示使用当前连接的sql语句的状态,很重要的列,后续会有所有的状态的描述,请注意,state只是语句执行中的某一个状态,一个sql语句,已查询为例,可能需要经过copying to tmp table,Sorting result,Sending data等状态才可以完成。

info列:显示这个sql语句,因为长度有限,所以长的sql语句就显示不全,但是一个判断问题语句的重要依据。 

这个命令中最关键的就是state列,mysql列出的状态主要有以下几种:

Checking table

正在检查数据表(这是自动的)。

Closing tables

正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表。这是一个很快的操作,如果不是这样的话,就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。

Connect Out

复制从服务器正在连接主服务器。

Copying to tmp table on disk

 由于临时结果集大于 tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存。

索引及现有结构无法涵盖查询条件,才会建立一个临时表来满足查询要求,产生巨大的恐怖的i/o压力。

很可怕的搜索语句会导致这样的情况,如果是数据分析,或者半夜的周期数据清理任务,偶尔出现,可以允许。频繁出现务必优化之。

Copy to tmp table通常与连表查询有关,建议逐渐习惯不使用连表查询。

实战范例:

某社区数据库阻塞,求救,经查,其服务器存在多个数据库应用和网站,其中一个不常用的小网站数据库产生了一个恐怖的copy to tmp table操作,导致整个硬盘i/o和cpu压力超载。Kill掉该操作一切恢复。

Creating tmp table

正在创建临时表以存放部分查询结果。

deleting from main table

服务器正在执行多表删除中的第一部分,刚删除第一个表。

deleting from reference tables

服务器正在执行多表删除中的第二部分,正在删除其他表的记录。

Flushing tables

正在执行 FLUSH TABLES,等待其他线程关闭数据表。

Killed

发送了一个kill请求给某线程,那么这个线程将会检查kill标志位,同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位,不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了,那么kill请求会在锁释放时马上生效。

Locked 被其他查询锁住了

有更新操作锁定

通常使用innodb(支持行锁定)可以很好的减少locked状态的产生,但是切记,更新操作要正确使用索引,即便是低频次更新操作也不能疏忽。如上影响结果集范例所示。

在myisam的时代,locked是很多高并发应用的噩梦。所以mysql官方也开始倾向于推荐innodb。

Sending data

 正在处理 SELECT 查询的记录,同时正在把结果发送给客户端。

Sending data并不是发送数据,别被这个名字所欺骗,这是从物理磁盘获取数据的进程,如果你的影响结果集较多,那么就需要从不同的磁盘碎片去抽取数据,偶尔出现该状态连接无碍。

回到上面影响结果集的问题,一般而言,如果sending data连接过多,通常是某查询的影响结果集过大,也就是查询的索引项不够优化。

如果出现大量相似的SQL语句出现在show proesslist列表中,并且都处于sending data状态,优化查询索引,记住用影响结果集的思路去思考。

Sorting for group

正在为 GROUP BY 做排序。

Sorting for order

正在为 ORDER BY 做排序。 

        和Sending data类似,结果集过大,排序条件没有索引化,需要在内存里排序,甚至需要创建临时结构排序  

Opening tables

这个过程应该会很快,除非受到其他因素的干扰。例如,在执 ALTER TABLE 或 LOCK TABLE 语句行完以前,数据表无法被其他线程打开。 正尝试打开一个表。

Removing duplicates

正在执行一个 SELECT DISTINCT 方式的查询,但是MySQL无法在前一个阶段优化掉那些重复的记录。因此,MySQL需要再次去掉重复的记录,然后再把结果发送给客户端。

Reopen table

获得了对一个表的锁,但是必须在表结构修改之后才能获得这个锁。已经释放锁,关闭数据表,正尝试重新打开数据表。

Repair by sorting

修复指令正在排序以创建索引。

Repair with keycache

修复指令正在利用索引缓存一个一个地创建新索引。它会比 Repair by sorting 慢些。

Searching rows for update

正在讲符合条件的记录找出来以备更新。它必须在 UPDATE 要修改相关的记录之前就完成了。

Sleeping

正在等待客户端发送新请求.

System lock

正在等待取得一个外部的系统锁。如果当前没有运行多个 mysqld 服务器同时请求同一个表,那么可以通过增加 –skip-external-locking参数来禁止外部系统锁。

Upgrading lock

INSERT DELAYED 正在尝试取得一个锁表以插入新记录。

Updating

正在搜索匹配的记录,并且修改它们。

User Lock

正在等待 GET_LOCK()。

Waiting for tables

该线程得到通知,数据表结构已经被修改了,需要重新打开数据表以取得新的结构。然后,为了能的重新打开数据表,必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知:FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, 或 OPTIMIZE TABLE。

waiting for handler insert

INSERT DELAYED 已经处理完了所有待处理的插入操作,正在等待新的请求。

Waiting for net, reading from net, writing to net

   偶尔出现无妨

   如大量出现,迅速检查数据库到前端的网络连接状态和流量

   案例:因外挂程序,内网数据库大量读取,内网使用的百兆交换迅速爆满,导致大量连接阻塞在waiting for net,数据库连接过多崩溃

大部分状态对应很快的操作,只要有一个线程保持同一个状态好几秒钟,那么可能是有问题发生了,需要检查一下。还有其它的状态没在上面中列出来,不过它们大部分只是在查看服务器是否有存在错误是才用得着。

show profiles
show profile for query 1

官方手册:https://dev.mysql.com/doc/refman/8.0/en/show-profile.html

show status
flush status
show global status
show engine innodb status(老版 show innodb status)

 show tables from information_schema

explain

官方手册 https://dev.mysql.com/doc/refman/8.0/en/using-explain.html

详细介绍:https://www.yangliuan.cn/?p=145

参考链接

MySql索引的使用总结

所有存储引擎都支持每个表至少 16 个索引,总索引长度至少为 256 字节。

组合索引KEY(col1,col2) 联合主键索引 PARIMARY KEY( col1,col2) 

最左匹配原则

创建了key(k1,k2,k3),相当于创建了(k1)、(k1,k2) 和 (k1,k2,k3) 三个索引

索引全部命中的情况where条件不区分顺序 where k1 = xxx and k2 = xxx and k3 = xxx 等同于 where k3 = xxx and k2 = xxx  and k1 = xxx 查询优化器会处理并使用索引

where k1 = xxx 或 where k1 = xxx and k2 = xxx 使用索引 where k2 = xxx 或 where k3 = xxx 无法使用索引

当查询优化器发现最优索引时,并不会遵循最左匹配原则,而是使用最优查询,如下图k1,k2,k3为连续字段时,不按最左匹配查询还是会命中索引,此时k1,k2,k3被查询优化器当成了一个字段,单次查询k1,k2,k3都会命中

EXPLAIN select * from 3k where k2 = 'sdfdsa'

当k1,k2,k3不在连续中间被其它字段隔开时

相同的查询语句将不会命中索引

联合索引,那么 key 也由多个列组成,同时,索引只能用于查找 key 是否存在(相等),遇到 范围查询(>、<、between、like 左匹配)等就不能进一步匹配了,后续退化为线性查找。因此,列的排列顺序决定了可命中索引的列数。

如有索引 (a, b, c, d),查询条件 a = 1 and b = 2 and c = 3 and d > 4,则会在每个节点依次命中 a、b、c,无法命中 d。也就是最左前缀匹配原则。

不需要考虑 =、in 等的顺序,MySQL 会自动优化这些条件的顺序,以匹配尽可能多的索引列。

如有索引 (a, b, c, d),查询条件 c > 3 and b = 2 and a = 1 and d < 4 与 a = 1 and c > 3 and b = 2 and d < 4 等顺序都是可以的,MySQL 会自动优化为 a = 1 and b = 2 and c > 3 and d < 4,依次命中 a、b。

离散度高原则,选择性(离散性)高的优先,即数据重复率低的列,像性别字段只有男/女两个值,因此选择性很差

离散度的公式是 count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是 1,而一些状态、性别字段可能在大数据面前区分度就是 0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要 join 的字段我们都要求是 0.1 以上,即平均 1 条扫描 10 条记录。

索引前缀

字符串字段过长时可以使用,索引前缀,截取字段前一部分的值作为索引

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

Range类型 要使用单字段索引

当使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE 或 IN() 运算符中的任何一个将键列与常量进行比较时,可以使用范围

where min <= 1 and max >= 1 语句使用min和max联合索引并不会生效,应该使用min和max的两个单字段索引

索引失效情况总结

查询条件包含 or,会导致索引失效

隐式类型转换,会导致索引失效,where条件左边字段类型为字符串类型时,传入其他类型会触发

like 通配符会导致索引失效,注意:”ABC%” 不会失效,会走 range 索引,”% ABC” 或 “%ABC%” 索引会失效

联合索引,最左匹配原则被中断时

对索引字段进行函数运算,列运算(算术,逻辑,移位等) 注意是对字段做运算,对字段的值做运算时并不影响,如下图

索引字段上使用(!= 或者 < >,not in)时,会导致索引失效

索引字段上使用 is null, is not null,可能导致索引失效

相 join 的两个表的字符编码不同,不能命中索引,会导致笛卡尔积的循环计算

mysql 优化器判断使用全表扫描要比使用索引快,则不使用索引

简单索引和联合索引如何抉择?

能扩展就不要新建索引。如果已有索引 (a),想建立索引 (a, b),尽量选择修改索引 (a) 为索引 (a, b),这样占用空间最小效果一致。

如果已有索引 (a, b),则不需要再建立索引 (a),但是如果有必要,则仍然需考虑建立索引 (b)。

索引尽量少原则

虽说索引可以加速查询,但索引未必是越多越好,因为:

  • 第一点、数据的增删都会涉及到随索引的修改,索引越多维护成本越高,所以频繁进行数据操作的表,不要建立太多的索引;
  • 第二点、索引越多也意味着存储空间需要越大;

因为索引是有代价的,所以用不到的索引,也需要清理掉。

如何定位哪些页面和接口需要加索引?

最简单直接方法是:往数据库里生成 100 万条数据,然后做黑盒测试。

数据有了以后,假装你是一个正常的用户,然后不断地点来点去,做各种操作,点赞、收藏、发文章、评论等,看看哪个动作或者页面加载很慢,就记录下来

如果使用laravel框架可以用telescope或者debug工具查看执行的sql语句,对访问频率高的接口和页面,增加索引,优化查询

参考

盘点那些被问烂了的 Mysql 面试题

MySQL 规约(转自阿里巴巴 Java 开发手册)

官方手册优化与索引

laravel优化教程

Mysql Explain命令

官方手册 :https://dev.mysql.com/doc/refman/8.0/en/using-explain.html

Explain命令 查看语句的执行计划,用于sql语句优化

属性说明:

select_type:select 的语句的查询类型

类型值类型值说明

SIMPLE

简单SELECT(不使用UNION或子查询等)

PRIMARY

最外面的SELECT

UNION

UNION中的第二个或后面的SELECT语句

DEPENDENT UNION

UNION中的第二个或后面的SELECT语句,取决于外面的查询

UNION RESULT

UNION的结果

SUBQUERY

子查询中的第一个SELECT

DEPENDENT SUBQUERY

子查询中的第一个SELECT,取决于外面的查询

DERIVED派生表
DEPENDENT DERIVED派生表依赖于另一个表
MATERIALIZED物化子查询,子查询来自视图
UNCACHEABLE SUBQUERY一个子查询,其结果不能被缓存,必须对外层查询的每一行进行重新评估
UNCACHEABLE UNIONUNION中的第二个或以后的选择,属于不可缓存的子查询(参见不可缓存的子查询)

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

partitions: 被查询记录所在的分区,没有返回null,分区功能参考mysql分区功能

type:这列最重要,显示了连接使用了哪种类别,有无使用索引,是使用Explain命令分析性能瓶颈的关键项之一

类型值类型值说明
system该表只有一行(= 系统表)。这是 const 连接类型的特例
const该表最多有一个匹配行,在查询开始时读取。因为只有一行,该行中该列的值可以被优化器的其余部分视为常量。 const 表非常快,因为它们只被读取一次。主键或唯一索引时类型为常量
eq_ref对于前面表中的每个行组合,从该表中读取一行。除了 system 和 const 类型之外,这是最好的连接类型。当连接使用索引的所有部分并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引时使用它
ref所有具有匹配索引值的记录都从这个表中读出,用于前面表中的每一个记录组合。如果连接只使用键的最左边的前缀,或者键不是PRIMARY KEY或UNIQUE索引(换句话说,如果连接不能根据键值选择一条记录),就使用ref。如果使用的键只与几条记录相匹配,这就是一个好的连接类型
fulltext全文索引,类搜索引擎功能
ref_or_null这种连接类型就像ref,但增加了MySQL对包含NULL值的行进行额外的搜索。这种连接类型的优化在解决子查询时最常使用。在下面的例子中,MySQL可以使用一个ref_or_null连接来处理ref_table
index_merge这种连接类型表明使用了索引合并优化。在这种情况下,输出行中的key列包含了所使用的索引的列表,key_len包含了所使用的索引的最长的key部分的列表。更多信息,请参见章节8.2.1.3,”索引合并优化
unique_subquery对于以下形式的某些 IN 子查询,此类型替换 eq_ref:value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery这种连接类型类似于 unique_subquery。它取代了 IN 子查询,但它适用于以下形式的子查询中的非唯一索引:value IN (SELECT key_column FROM single_table WHERE some_expr)
range只检索给定范围内的行,使用索引来选择行。输出行中的key列表明使用的是哪个索引。key_len包含了所使用的最长的关键部分。对于这种类型,ref列是NULL
index索引连接类型与ALL相同,只是对索引树进行扫描。这有两种情况。
如果索引是查询的覆盖索引,并且可以用来满足表的所有数据要求,那么只有索引树被扫描。在这种情况下,Extra列显示使用索引。只扫描索引的速度通常比ALL快,因为索引的大小通常比表的数据小。
全表扫描是使用从索引中读出的数据来按索引顺序查找数据行。使用索引不会出现在Extra列中。
当查询只使用属于一个索引的列时,MySQL可以使用这种连接类型。
ALL没有使用任何索引,使用了全表扫描,性能非常差

range示例说明

当使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE 或 IN() 运算符中的任何一个将键列与常量进行比较时,可以使用范围:
SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

结果值从好到坏依次是:一般来说,得保证查询至少达到range级别,最好能达到ref,否则就可能会出现性能问题。

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

possible_keys:列指出MySQL能使用哪个索引在该表中找到行

当前查询可用的索引 多个逗号分隔

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

查询优化器实际选择的索引

key_len:显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。使用的索引的长度。在不损失精确性的情况下,长度越短越好

ref:显示使用哪个列或常数与key一起从表中选择

ref列显示哪些列或常量与列中指定的索引进行比较以 key从表中选择行。

如果值为func,则使用的值是某个函数的结果。要查看哪个功能,请使用 SHOW WARNINGS以下内容 EXPLAIN查看扩展 EXPLAIN输出。该函数实际上可能是一个运算符,例如算术运算符。

rows: rows列表示MySQL认为它必须检查以执行查询的行数,对于InnoDB表,这个数字是一个估计值,不一定准确

filtered: 表按照条件过滤行数的百分比

过滤列表示被表条件过滤的表行的估计百分比。最大值是100,这意味着没有发生过滤的行。从100开始递减的值表示过滤量的增加。rows显示了被检查的行的估计数量,rows × filtered显示了与下面表格连接的行的数量。例如,如果rows是1000,而filtered是50.00(50%),那么与下表中连接的行数是1000×50%=500

Extra:附加信息 包含MySQL解决查询的详细信息,也是关键参考项之一。

这一列包含关于MySQL如何解决查询的额外信息。关于不同值的描述,见EXPLAIN额外信息。

没有与Extra列相对应的单一JSON属性;然而,可能出现在这一列中的值被暴露为JSON属性,或作为消息属性的文本

具体信息看官方文档查询

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information

其他一些Tip:

  1. 当type 显示为 “index” 时,并且Extra显示为“Using Index”, 表明使用了覆盖索引。

EXPLAIN ANALYZE

select * 和 select 具体字段差别