背景

如果我们需要在生产环境中修改MySQL数据库中某个库表的结构。那么，需要考虑哪些要点，才能确保不会出问题呢？

碰到的问题

这里先描述一下我在生产环境MySQL数据库中修改库表结构时遇到的问题。
在开发过程中，我发现MySQL中某个库表需要添加一个字段，比如库表：

需要给Sname后面添加一个字段：Sheight。那么就使用命令：
alter table practice.Student add column Sheight int(4) not null default 0 comment '"身高"
输入完这个命令后，我就去做别的事情去了。
直到过了一小会，有人反馈说线上的系统有些界面没有数据。这个时候我才意识到，是这个操作出了问题。导致了线上bug。

问题的解决

我立马查看这个操作，发现还没有执行结束。首先kill了这个执行任务，于是线上系统恢复了正常。

导致该问题的原因

当时，我用命令：show processlist查询，看到这个语句的State显示的是：
Waiting for table metadata lock。我们知道，这个状态是说，该表在等待获取表的metadata lock，也就是MDL。
也就是说，由于前面有MDL读锁没有被释放，因此我这个命令也就获取不到MDL写锁。导致后面再过来的各种操作都无法被执行，都在等待MDL读锁。
这里解释下metadata的概念，metadata lock（MDL）也就是元数据锁，它是一种表级锁。
各种对该表的操作，比如增删改查，都会占有MDL的读锁。当修改表结构时，会占用MDL的写锁。
读锁和读锁之间互不冲突，而读锁与写锁、写锁与写锁之间互相冲突。
简单说，就是对一个表增删改查同时进行，MDL锁不会冲突，我们可以用多线程同时执行这些操作，只会导致行锁，而不会锁整个表。
但是，如果在对表增删改查的同时，要对表结构进行修改，那么就会造成锁等待的状态。
如果有一个长事务在对该表进行操作，那么在修改表结构时，就会有状态：Waiting for table metadata lock，也就是锁等待。如果这个时候，另外又有查询操作过来，那么，后面这个操作就也要进行Waiting for table metadata lock，也就是锁等待了。当然，对该表的查询操作就会全部阻塞。
我当时的情况就是这样，有一个事务操作了该表，但是可能由于大意没有关掉该事务，该事务长时间存在。而我同时又进行表结构的更改，于是导致了这次事故。

如何做

首先，我们要了解一下有没有什么事务对该表进行了操作，却长期没有提交。因为，只有对该表操作的事务最终提交了，MDL锁才会被释放。
换句话说，如果某个事务对该表进行了操作，比如读操作，但是最终没有做提交，那么，该事务依然会占用MDL锁的。
查看事务可以用命令：SELECT * FROM information_schema.INNODB_TRX。
做完这一步之后，基本上可以避免出现Waiting for table metadata lock的情况了，但还有一点需要注意，就是线上会不会对该表进行频繁的操作，
有些热表可能一直处于有人在查询的状态，这种时候怎么做呢？
我们可以在变更表结构的命令中添加一个超时时间，如果这个命令在该时间段内一直无法执行，那么会自动超时的，起码可以保证不会长时间的影响用户的操作。
该命令为：alter table practice.Student wait 100 add column Sheight int(4) not null default 0 comment "身高"

总结

在生产环境中变更MySQL数据库中库表结构是一件比较有风险的事情，所以一定要三思而后行，避免引起任何可能的线上事故。

背景

在使用MySQL数据库时，有时会遇到Warning: (1265, u”Data truncated for column ‘XXX’ at row 1″)这样的报错信息。
具体可以考虑哪些问题呢？这里简单记录一下问题原因与解决方法。

报错原因

1. 写入该字段的数据长度大于该字段定义的最大长度，比如定义了字段user_name VARCHAR(10)，这个字段定义了最长写入10位字符，但是，如果你写入的数据超过了10位字符，那么就会出现该警告信息。
2. 传入的数据类型有误。比如定义了字段cost DECIMAL(10,2)，这个字段小数点后有两位，但是，如果你写入的数据为超过了两位小数，比如10.1122，则会出现该警告信息。
3. 插入了非法字符。

解决方法

具体问题具体分析，可以对库表的字段类型做调整，或者检查插入的数据是否不符合预期。

背景

我们在业务场景中经常会碰到通过某个字符串查询对应记录的情况。比如常见的邮箱登录、或是手机号登录。
如果不给它创建索引，则MySQL就会进行全局扫描，非常耗时。那么，类似邮箱地址这样的字符串，我们应该如何给它创建索引呢？这里简单介绍几种方法。

几种方法

比如我们有一张表user_info用于存储用户登录信息，包括自增主键ID、邮箱地址、对应密码。
这里我们经常会用到的SQL操作是select email, password from user_info where email="XXXXXXX"

直接创建索引

第一种方法当然是直接使用email来创建索引。这种方法虽然简单粗暴且会占用大量的存储空间，但是有一个好处，就是回表操作只需一次就行。

使用前缀索引

所谓前缀索引，比如前面的示例，就是将email字段的前缀截取下来，然后作为索引来使用。

添加索引的SQL语句为：alter table user_info add index index_eamil(email(9))，这里就添加了一个email字段前9位字符为前缀索引。

假如email字段的前9位字符就可以唯一的标示一个email地址，比如前9位是不同的数字，后面都是@qq.com。那么这样就做到了完全的区分度。在选择某email时，比如select email, password from user_info where email="123456789@qq.com"，MySQL会拿前9位去索引查找，查找得到则会回表到主键树中获取到对应的记录。

使用前缀索引，一定要权衡好前缀选取的长度，因为前缀的长度就意味着区分度。如果前缀索引可以完全区分，那当然是最好的，这样只需要回表一次就可以拿到数据了。区分度越高越好，区分度越高，也就使得回表操作次数越少。

只要前缀长度取的合适，就可以既有满意的区分度，又有满意的存储空间占用。

不过使用前缀索引也有一个劣势，在于使用前缀索引会影响到覆盖索引的使用。

倒序存储

倒序存储即是将对应字段倒序过来再取前缀创建索引。针对于前缀区分度低，但后缀区分度高时使用。

比如我们需要存储一个user_info用户信息表，存储的是自增ID、身份证号、密码。身份证号就属于前缀区分度低（前缀都是一些通用代号），但后缀区分度高的字符串。

在存储以及查询时也需要将身份证号倒过来使用。比如，在查询时，使用语句：select user_id, password from user_info where user_id=reverse(XXXXXX)

hash存储

将对应字段hash之后作为索引存储，hash之后冲突的概率较低，也可大大降低回表的次数。只是由于需要存储字段hash之后的数据，会增加额外存储空间的占用。

总结

前面简单介绍了一下几种不同的对字符串创建索引的方法。
总结如下：

索引方式	优势	劣势	适用场景
直接创建索引	回表次数少	索引空间占用大，且每次字符串匹配时间较长	数据量不大或字符串不长的情况
前缀索引	索引空间占用小，且每次字符串匹配时间较短	可能增加回表次数	选取出的前缀可以有较高的区分度
倒序存储	无需添加新字段	只能支持单条查询，不支持范围查询	前缀区分度低，后缀区分度高
hash存储	冲突概率低，得出的索引区分度较高	只能支持单条查询，不支持范围查询，且需要增加新的存储字段	无需范围查询且普适性较高

背景

我们经常会使用到一个SQL语句，就是查询某张表的总行数。常常使用的查询命令有几种，比如：select count(*) from t，select count(id) from t（id为主键)，select count(1) from t，select count(某普通字段) from t以及show table status的rows字段。然而却不知道用哪种查询方式最合适。接下来简单介绍一下我们在MySQL中查询表的总行数时该用什么命令。

不同存储引擎，查询效率不同

在分析几种查询方式之间的不同之前，我们先来看下以select count(*) from t为例，不同存储引擎之间查询行数方式的不同。常见的存储引擎有MyISAM和InnoDB。

InnoDB：每次我们需要查询某张表的总行数时，都会遍历整张表计算出结果。

MyISAM：每张表的总行数会记录在磁盘中存储，在我们需要查询某张表的总行数时，命令会直接取出对应字段信息。（不过需要注意，当命令查询的不是表总行数，而是where查询某些行时，也依然需要像InnoDB一样遍历整张表计算出结果）

很显然，在表行数较多时，MyISAM的方式是要比InnoDB更快的。

那么，为什么InnoDB不采用与MyISAM相同的处理方式呢？

本质原因在于InnoDB支持事务，我们可以看个具体的例子就明白了。

假设我们的表t中包含了100行数据，且有如下三个会话在做SQL操作。（会话2与会话3是事务，会话1是普通语句）

先不管中间是怎样的过程，我们看最后三个会话输出的查询结果。

如果是MyISAM存储引擎，它是不支持事务的，查询出的结果三个会话就都是102。

而如果是InnoDB存储引擎，它是支持事务的，可重复读是默认的隔离级别，所以查询出的结果三个会话各不相同，会话1是101，会话2是100，会话3是102。

那么，为什么InnoDB不采用与MyISAM相同的处理方式呢？这就很明显了。因为在相同时刻，InnoDB由于支持事务，所以可能会读出的结果不同，

因此，InnoDB只能在需要查询表总行数时去遍历计算了。

MyISAM由于不存在这样的问题，当然就可以把总行数存储下来，每次需要时直接读取就行了。

几个常用查询语句的效率不同

说回到开始说的几个常用查询语句，我们到底该用哪个查询语句呢？我们以InnoDB引擎为例进行说明。

首先，我们要了解一下count()函数的作用，count()函数会判断所查询字段是否为NULL，会将非NULL的行累加起来，得到最终的行数值。

接下来分别分析这几个常用查询语句：

select count(字段) from t（字段为非NOT NULL且非索引）：服务器会将字段返回给服务器，服务器判断每一个字段内容是否为NULL，如果不为NULL，则会计数加1，累加出来的结果即为语句结果返回。

select count(id) from t（id为主键）：InnoDB会将每一行id返回给服务器，服务器判断主键不可能为NULL，累加出来的结果即为语句结果返回。

select count(1) from t：InnoDB会遍历整张表，不过不会取字段，也不会取值。而是服务器放一个数字1进去，并将累加出来的值作为结果返回。

select count(*) from t：优化器针对该语句做了优化，InnoDB不需要取字段给服务器，而是服务器直接计算行总数并返回。

由于 count(*) 与 count(1) 都无需从InnoDB中取值，而是直接计算行总数，则两者运行时间差不多。

而 count(id) 会取出id值进行判断，虽然主键值都不会为NULL，但运行时间会比前两者长一些。

count(字段) 与 count(id) 判断方式一样，只是其不是索引，所以遍历起来较count(id) 会慢。

总结来说，四种常用查询方式的速度顺序为：count(字段)

注意：select count(id) from t语句，虽然id为主键不可能是NULL值，理论上来说，MySQL是无需一个一个再进行数值判断的。然而目前MySQL并没有对此做优化，应该是在查询总行数时，MySQL就建议使用select count() from t，因此专门对count() 做了优化。其余的查询就都按照需要什么数据则取什么数据这样的原则统一处理。

show table status命令

show table status命令中的Rows字段也会显示出表的总行数，但是该命令是通过采样统计来计算出的表总行数，因此会有很大的误差。作为参考值还可以，如果要使用精确值，就不推荐了。

总结

简单总结一下，我们在查询某张表总行数时，如果需要知道大体行数，则可使用show table status的rows字段查看。如果需要知道精确行数，则需使用select count(*) from t命令来查看。

背景

我们都经常使用Mysql作为数据库来存储与查询较常用的数据。当我们输入一行如SELECT * FROM table_name WHERE id=26这样的语句之后，Mysql如果正确执行的情况下，会输出你想要的信息。

那么，在你输入这行语句之后，一直到它显示出你想要的信息，这中间Mysql都经历了什么呢？这篇文章会简单聊一下这个事情。

Mysql基本架构图

我们先看下Mysql的一个较整体的架构图。

接下来我会以具体的SQL语句为例，详细的叙述从你在客户端输入了这个语句之后，到它返回你想要的信息，这中间具体经历了什么。

客户端

所谓客户端，即是我们登录与操作Mysql所使用的终端。我们都是在客户端对Mysql进行操作的，无论是输入连接数据库的信息，还是输入查询某个表的SQL，或者是收到Mysql返回给我们的查询信息，这些都是在客户端完成的。

连接器

用户信息验证

我们在一个客户端跟前，想要使用Mysql数据库，那么第一步就是要先连接上你要使用的数据库。

我们都知道，我们要输入命令mysql -h$ip -P$port -u$username -p。

之后客户端会要求我们输入密码。再之后，如果我们输入的信息都没有问题了，我们就进入Mysql的操作界面了。

如果我们输入的信息有问题，就会收到客户端返回的报错信息。比如我们将密码输入错误了，这时就会收到”Access denied for user”这样的报错信息。

那么，这中间连接器具体做了什么呢？

首先，连接器会拿着我们输入的IP和端口，去做最经典的TCP握手，握手如果都失败了，那就自然没有后续了，直接返回相应的报错信息。

如果握手成功了，此时则会去验证我们输入的用户名和密码，验证失败则同样会返回相应的报错信息。

用户权限获取

如果用户名密码也没有问题，接下来连接器则会取出权限表读取该用户相应的权限数据。用户跟着所做的所有操作，都基于此时读取到的用户权限。

权限表共有4个：user, db, tables_priv, columns_priv。

当用户通过权限验证，进行权限分配时，按照user, db, tables_priv, columns_priv的顺序进行分配。即先检查用户的全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查剩余3个表；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y；如果为N，则到columns_priv中检查具体的列。

这也就意味着，当我们修改了某个用户的用户权限，只有到下一次该用户登录（创建新的连接）时，才会影响到该用户。

连接与等待超时

我们可以通过show processlist来查看当前所有的用户连接及其行为。

Command中的字段显示该用户目前的状态，此时这个用户是查询状态。

但若Command显示的状态是Sleep，那么说明该用户当前在等待状态。若等待超过了一段时间，则连接器会自动断开。

该超时时间由wait_timeout变量控制，可以通过show global variables like 'wait_timeout'来查看。

mysql默认为28800秒，即8小时。

长连接与短连接

所谓长连接，即用户的持续操作使用的都是同一个连接，连接在一段时间内长时间建立。

所谓短连接，即用户每做几次操作则断开，再下次操作时再进行连接。

长连接的优点是，在持续操作时，可以节省很多建立连接所需要消耗的时间。但是长连接所要存储的临时数据都在连接对象中，长时间积累，会导致系统内存溢出，具体表现
为Mysql异常重启。

短连接的优缺点与长连接相反，虽然不用担心内存溢出的问题，但短连接在持续操作的情况下多次连接，连接消耗很多时间，整体操作效率会很低。

缓存器

连接器连接完成的下一步就是缓存器的缓存查询，如果我们需要对一张静态表（不常更新）经常做查询操作，那么可能会用到缓存器。

缓存器中使用的是key-value的存储形式，key值存储的是查询语句，value值存储的是对应结果。

要注意的是，只要该表做了一次更新操作，那么该表对应的缓存就会全部被清理。因此使用场景并不多。

所以当前缓存器的使用较少。我们可以通过query_cache_type来查看缓存器是否开启。

现在一般都是默认关闭的状态。且Mysql从8.0版本会开始彻底弃用该功能。

分析器

假设我们不使用缓存器，或者通过缓存器没有命中SQL语句。

那么连接器做连接操作之后，接下来我们就输入了一个查询语句，比如：SELECT host FROM mysql.user LIMIT 1。

而分析器做的事情就是对你输入的语句做 “词法分析” 与 “语法分析”。

所谓 “词法分析” ，就是判断每一个你输入的词，比如分析器首先会判断出你输入的第一个词是“SELECT”，第二个词你输入了“host”，等等。

而 “语法分析” 则是跟在 “词法分析” 之后，就是依据你输入的这些词来判断你输入的是否符合语法规则。

假如符合语法规则，则会顺利进行下去并返回相应信息。

假如不符合语法规则，则分析器会返回报错信息给客户端。

具体出错的地方，一般都是跟在use near之后，我们看这里就能知道语法错误出在了哪一块。

优化器

在分析器工作结束后，如果语法有问题，那么就会直接返回报错信息，且不继续向下运行。

若语法正确，那么，则会到优化器部分的工作。优化器顾名思义，就是对该语句的执行做优化。

比如，在一个语句查询某个表时，该表可能有多个索引，此时使用哪个索引会使语句的执行效率最高？这就是优化器要做的事情。

再比如，执行语句select * from t1 join t2 on t1.ID=1 and t2.ID=2，

该语句执行时，是先从t1表中找到ID=1的行关联到t2表之后，再从t2表中查找ID=2的行。

还是先从t2表中找到ID=2的行关联到t1表之后，再从t1表中查找ID=1的行。

两种执行顺序可能就导致执行效率的不同，怎样选择执行顺序会提高执行效率，这也是优化器要做的事情。

执行器

在上述步骤完成之后，就轮到执行器去执行具体的语句了。

例如语句：select * from mysql.tables_priv

在执行器做具体的语句执行之前，会对该表的操作权限进行验证，验证失败则返回权限错误的报错。如下：

而实际上，权限验证不仅仅在执行器这部分会做，在分析器之后，也就是知道了该语句要“干什么”之后，也会先做一次权限验证。叫做precheck。

而precheck是无法对运行时涉及到的表进行权限验证的，比如使用了触发器的情况。因此在执行器这里也要做一次执行时的权限验证。

如果验证成功，那么则会使用该表对应的存储引擎的接口，继续执行语句。
最后将成功执行的结果返回给客户端。

总结

简单来说，一条SQL语句在Mysql中执行，一共会经历四步（算上连接Mysql），分别是连接、分析、优化与执行。每一步都会精确执行，如果发现有问题就会返回给客户端相应的报错。只有每一步都正确执行，最终才会在客户端得到你想要查询或操作的结果。

背景

使用Python脚本高并发的连接Mysql数据库时遇到了此报错。
但是场景不仅限于Python，其它程序在高并发连接Mysql数据库时也可能会遇到此问题。

原因定位

出现该报错信息是因为在高并发连接Mysql数据库时，由于同时连接Mysql数据库的链接过多，且每次连接都是非常短的时间，导致有许多的TIME_WAIT，以致用光了服务器端口，所以新的连接就没有端口可用，导致了该报错产生。

我们可以看下当前端口占用的个数：
netstat -an|wc -l

也可以看下当前可用端口的范围：
cat /proc/sys/net/ipv4/ip_local_port_range

解决方法

1

知道了原因，最好的解决方法是减少这种高并发的连接，由于是每次连接的处理时间较短时，不然很容易出现这种问题。

2

修改可用端口的范围
echo 1024 65000 > /proc/sys/net/ipv4/ip_local_port_range

3

查看内核的时间戳支持：
cat /proc/sys/net/ipv4/tcp_timestamps
如果是0，则修改为1。开启对于TCP时间戳的支持。
运行sysctl -w net.ipv4.tcp_timestamps=1

查看是否开启快速回收：
cat /proc/sys/net/ipv4/tcp_tw_recycle
如果是0，则修改为1。开启TCP连接中TIME-WAIT sockets的快速回收。
运行sysctl -w net.ipv4.tcp_tw_recycle=1

修改tcp_max_tw_buckets
运行sudo sh -c "echo '5000'> /proc/sys/net/ipv4/tcp_max_tw_buckets"