标签归档:MySQL FAQ

[MySQL FAQ]系列 — EXPLAIN结果中哪些信息要引起关注

MySQL FAQ
插图来自网络并作简单加工,如果觉得不当还请及时告知 :)

我们使用EXPLAIN解析SQL执行计划时,如果有下面几种情况,就需要特别关注下了:

首先看下 type 这列的结果,如果有类型是 ALL 时,表示预计会进行全表扫描(full table scan)。通常全表扫描的代价是比较大的,建议创建适当的索引,通过索引检索避免全表扫描。此外,全索引扫描(full index scan)的代价有时候是比全表扫描还要高的,除非是基于InnoDB表的主键索引扫描。

再来看下 Extra 列的结果,如果有出现 Using temporary 或者 Using filesort 则要多加关注:

Using temporary,表示需要创建临时表以满足需求,通常是因为GROUP BY的列没有索引,或者GROUP BY和ORDER BY的列不一样,也需要创建临时表,建议添加适当的索引。

Using filesort,表示无法利用索引完成排序,也有可能是因为多表连接时,排序字段不是驱动表中的字段,因此也没办法利用索引完成排序,建议添加适当的索引。

Using where,通常是因为全表扫描或全索引扫描时(type 列显示为 ALLindex),又加上了WHERE条件,建议添加适当的索引。

暂时想到上面几个,如果有遗漏,以后再补充。

其他状态例如:Using index、Using index condition、Using index for group-by 则都还好,不用紧张。

更多详情请看官方文档:8.8.2 EXPLAIN Output Format

[MySQL FAQ]系列 — processlist中哪些状态要引起关注

MySQL FAQ
插图来自网络并作简单加工,如果觉得不当还请及时告知 :)

一般而言,我们在processlist结果中如果经常能看到某些SQL的话,至少可以说明这些SQL的频率很高,通常需要对这些SQL进行进一步优化。

今天我们要说的是,在processlist中,看到哪些运行状态时要引起关注,主要有下面几个:

状态 建议
copy to tmp table 执行ALTER TABLE修改表结构时建议:放在凌晨执行或者采用类似pt-osc工具
Copying to tmp table 拷贝数据到内存中的临时表,常见于GROUP BY操作时建议:创建适当的索引
Copying to tmp table on disk 临时结果集太大,内存中放不下,需要将内存中的临时表拷贝到磁盘上,形成 #sql***.MYD、#sql***.MYI(在5.6及更高的版本,临时表可以改成InnoDB引擎了,可以参考选项default_tmp_storage_engine建议:创建适当的索引,并且适当加大sort_buffer_size/tmp_table_size/max_heap_table_size
Creating sort index 当前的SELECT中需要用到临时表在进行ORDER BY排序建议:创建适当的索引
Creating tmp table 创建基于内存或磁盘的临时表,当从内存转成磁盘的临时表时,状态会变成:Copying to tmp table on disk建议:创建适当的索引,或者少用UNION、视图(VIEW)、子查询(SUBQUERY)之类的,确实需要用到临时表的时候,可以在session级临时适当调大 tmp_table_size/max_heap_table_size 的值
Reading from net 表示server端正通过网络读取客户端发送过来的请求建议:减小客户端发送数据包大小,提高网络带宽/质量
Sending data 从server端发送数据到客户端,也有可能是接收存储引擎层返回的数据,再发送给客户端,数据量很大时尤其经常能看见备注:Sending Data不是网络发送,是从硬盘读取,发送到网络是Writing to net建议:通过索引或加上LIMIT,减少需要扫描并且发送给客户端的数据量
Sorting result 正在对结果进行排序,类似Creating sort index,不过是正常表,而不是在内存表中进行排序建议:创建适当的索引
statistics 进行数据统计以便解析执行计划,如果状态比较经常出现,有可能是磁盘IO性能很差建议:查看当前io性能状态,例如iowait
Waiting for global read lock FLUSH TABLES WITH READ LOCK整等待全局读锁建议:不要对线上业务数据库加上全局读锁,通常是备份引起,可以放在业务低谷期间执行或者放在slave服务器上执行备份
Waiting for tables,Waiting for table flush FLUSH TABLES, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, OPTIMIZE TABLE等需要刷新表结构并重新打开建议:不要对线上业务数据库执行这些操作,可以放在业务低谷期间执行
Waiting for lock_type lock 等待各种类型的锁:• Waiting for event metadata lock• Waiting for global read lock• Waiting for schema metadata lock• Waiting for stored function metadata lock• Waiting for stored procedure metadata lock• Waiting for table level lock• Waiting for table metadata lock• Waiting for trigger metadata lock

建议:比较常见的是上面提到的global read lock以及table metadata lock,建议不要对线上业务数据库执行这些操作,可以放在业务低谷期间执行。如果是table level lock,通常是因为还在使用MyISAM引擎表,赶紧转投InnoDB引擎吧,别再老顽固了

更多详情可参考官方手册:8.14.2 General Thread States

[MySQL FAQ]系列 — MySQL无法启动例一

MySQL FAQ
插图来自网络并作简单加工,如果觉得不当还请及时告知 :)

【场景】某个打算用于slave新搭建的实例启动报错,启动过程中报告InnoDB数据页发生损坏。错误日志像下面这样:

150330 15:37:44 mysqld_safe Starting mysqld daemon with databases from /data/mysql/mytest_3306
2015-03-30 15:37:45 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2015-03-30 15:37:45 5884 [Warning] Using unique option prefix myisam_recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2015-03-30 15:37:45 5884 [Note] Plugin ‘FEDERATED’ is disabled.
2015-03-30 15:37:45 5884 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-03-30 15:37:45 5884 [Note] InnoDB: The InnoDB memory heap is disabled
2015-03-30 15:37:45 5884 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-03-30 15:37:45 5884 [Note] InnoDB: Memory barrier is not used
2015-03-30 15:37:45 5884 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-03-30 15:37:45 5884 [Note] InnoDB: Using Linux native AIO
2015-03-30 15:37:45 5884 [Note] InnoDB: Using CPU crc32 instructions
2015-03-30 15:37:45 5884 [Note] InnoDB: Initializing buffer pool, size = 2.0G
2015-03-30 15:37:46 5884 [Note] InnoDB: Completed initialization of buffer pool
2015-03-30 15:37:47 5884 [Note] InnoDB: Highest supported file format is Barr.
2015-03-30 15:37:48 5884 [Warning] InnoDB: Resizing redo log from 3*32768 to 2*16384 pages, LSN=2740249189
2015-03-30 15:37:48 5884 [Warning] InnoDB: Starting to delete and rewrite log files.
2015-03-30 15:37:48 5884 [Note] InnoDB: Setting log file ./ib_logfile101 size to 256 MB
InnoDB: Progress in MB: 100 200
2015-03-30 15:37:49 5884 [Note] InnoDB: Setting log file ./ib_logfile1 size to 256 MB
InnoDB: Progress in MB: 100 200
2015-03-30 15:37:50 5884 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2015-03-30 15:37:50 5884 [Warning] InnoDB: New log files created, LSN=2740249612
2015-03-30 15:37:50 5884 [Note] InnoDB: 128 rollback segment(s) are active.
2015-03-30 15:37:50 5884 [Note] InnoDB: Waiting for purge to start
2015-03-30 15:37:50 5884 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.21-rel69.0 started; log sequence number 2740249189
2015-03-30 15:37:50 5884 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ab27d3e4-d6af-11e4-8020-c81f66eeffa6.
150330 15:36:33 mysqld_safe Starting mysqld daemon with databases from /data/mysql/mytest_3306
2015-03-30 15:37:50 5884 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ab27d3e4-d6af-11e4-8020-c81f66eeffa6.
2015-03-30 15:37:50 5884 [Note] RSA private key file not found: /data/mysql/mytest_3306//private_key.pem. Some authentication plugins will not work.
2015-03-30 15:37:50 5884 [Note] RSA public key file not found: /data/mysql/mytest_3306//public_key.pem. Some authentication plugins will not work.
2015-03-30 15:37:50 5884 [Note] Server hostname (bind-address): ’10.x.x.x’; port: 3306
2015-03-30 15:37:50 5884 [Note] – ’10.x.x.x’ resolves to ’10.x.x.x’;
2015-03-30 15:37:50 5884 [Note] Server socket created on IP: ’10.x.x.x’.
2015-03-30 15:37:50 7f4ce4d68700 InnoDB: Error: page 32769 log sequence number 2740254202
InnoDB: is in the future! Current system log sequence number 2740249622.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.
2015-03-30 15:37:50 5884 [Note] Event Scheduler: Loaded 0 events
2015-03-30 15:37:50 5884 [Note] /opt/Percona-Server-5.6.21-rel69.0-675.Linux.x86_64/bin/mysqld: ready for connections.
Version: ‘5.6.21-69.0-log’ socket: ‘/data/mysql/mytest_3306/mysql.sock’ port: 3306 Percona Server (GPL), Release 69.0, Revision 675
2015-03-30 15:37:58 7f4ce4d68700 InnoDB: Error: page 6327 log sequence number 2740254445
InnoDB: is in the future! Current system log sequence number 2740251356.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.

【分析】正常情况下,新部署的实例是不太可能出现InnoDB数据页损坏的。经了解,这个实例是采用xtrabackup工具从master备份过来做恢复的。

细心的同学,通过观察上面的日志,应该能从中发现一些蛛丝马迹。有几个地方需要引起注意:

1、版本是Percona Server 5.6.21;
2、刚启动就把InnoDB的redo log给resize了;
3、刚启动就发现InnoDB的page LSN和redo log中的不匹配;

之所以提醒大家注意上面的三点,并且把版本信息放在了第一条,是因为从5.6版本开始,InnoDB如如果发现当前的redo log文件大小和预设配置的redo log大小不一致的话,就会自动将其删除重建

写到这里,相信聪明的你应该已经想到什么了吧,没错,导致这个启动报错的原因是:从master上xtrabackup备份出来的innodb redo log大小和本地配置参数不一致,被删除重建,结果事务恢复失败,提示数据也损坏错误信息

【解决】修改slave本地配置文件,把下面几个InnoDB配置选项都修改成和在master上的一样,再次执行恢复启动即可。

innodb_data_file_path
innodb_log_file_size
innodb_log_files_in_group
innodb_file_per_table

[MySQL FAQ]系列 — pt-table-checksum工具使用报错一例

Percona Tollkit
图片来自Percona官网

今天同事在用 percona toolkit 工具中的 pt-table-checksum 对主从数据库进行校验,提交命令后,一直提示下面的信息:

Pausing because Threads_running=0

看字面意思是在提示当前活跃线程数为0,但为什么不继续执行呢。这个提示信息有点含糊其辞,该工具是用Perl写的,因此直接打开看脚本跟踪一下,大概就明白怎么回事了,原来是这个工具有负载保护机制,避免运行时对线上数据库产生影响。

和这个机制相关的参数名是: –max-load,其类型是:Array,用法是一个或多个 variables = value 组成的判断条件,然后根据这个规则判断某些条件是否超标。例如,设定 –max-load=”Threads_running=25″,意思是当前活跃线程数如果超过25,就暂停 checksum 工作,直到活跃线程数低于 25。

因此,在我们这个案例中,想要强制让 table-checksum 继续工作的话,可以设定 –max-load 的值,例如:

pt-table-checksum --max-load="Threads_running=25" ...其他选项...

或者

pt-table-checksum --max-load="Threads_connected=25" ...其他选项...

前面的选项意思是判断活跃线程数不要超过25个,后面的选项意思是当前打开的线程数不要超过25个。

下面是 pt-table-checksum 帮助手册里的一段话:

–max-load
type: Array; default: Threads_running=25; group: Throttle

Examine SHOW GLOBAL STATUS after every chunk, and pause if any status variables are higher than the threshold. The option accepts a comma-sep-
arated list of MySQL status variables to check for a threshold. An optional “=MAX_VALUE” (or “:MAX_VALUE”) can follow each variable. If not
given, the tool determines a threshold by examining the current value and increasing it by 20%.

For example, if you want the tool to pause when Threads_connected gets too high, you can specify “Threads_connected”, and the tool will check
the current value when it starts working and add 20% to that value. If the current value is 100, then the tool will pause when Threads_con-
nected exceeds 120, and resume working when it is below 120 again. If you want to specify an explicit threshold, such as 110, you can use
either “Threads_connected:110” or “Threads_connected=110”.

The purpose of this option is to prevent the tool from adding too much load to the server. If the checksum queries are intrusive, or if they
cause lock waits, then other queries on the server will tend to block and queue. This will typically cause Threads_running to increase, and the
tool can detect that by running SHOW GLOBAL STATUS immediately after each checksum query finishes. If you specify a threshold for this vari-
able, then you can instruct the tool to wait until queries are running normally again. This will not prevent queueing, however; it will only
give the server a chance to recover from the queueing. If you notice queueing, it is best to decrease the chunk time.

[MySQL FAQ]系列 — 为什么要关闭query cache,如何关闭

mysql-query-cache-seems-so-beautiful
备注:插图来自淘宝苏普的博客并保留水印,如果觉得不当还请及时告知 :)

写在前面:MySQL的query cache大部分情况下其实只是鸡肋而已,建议全面禁用。当然了,或许在你的场景下还是挺好的,还能发挥作用,那就继续使用吧,把本文当做参考就好。

不过,可能有的人人为只需要把 query_cache_size 大小调整为 0 就可以了,可以忽略 query_cache_type 参数的值,反正它也是可以在线调整的。

事实果真如此吗?让我们来实际模拟测试下就知道了。

我们模拟了以下几种场景:

1、初始化时,同时设置 query_cache_size 和 query_cache_type 的值为 0;

2、初始化时,设置 query_cache_size = 0,但设置 query_cache_type = 1;

3、初始化时,设置 query_cache_size = 0,query_cache_type = 1,但是启动后立刻 修改 query_cache_type = 0

4、初始化时,设置 query_cache_size = 0,query_cache_type = 0,但是启动后立刻 修改 query_cache_type = 1

5、初始化时,设置 query_cache_size = xMB,query_cache_type = 1,但是启动后立刻 修改 query_cache_type = 0

 

经过测试,可以得到下面几个重要结论(详细测试过程请见最后):

1、想要彻底关闭query cache,务必在一开始就设置 query_cache_type = 0,即便是启动后将 query_cache_type 从 1 改成 0,也不行;

2、即便query_cache_size = 0,但 query_cache_type 非 0 的话,在实际环境中,可能会频繁发生 Waiting for query cache lock;

3、一开始就设置 query_cache_type = 0 的话,没有办法在运行 过程中再次动态启用,反过来则可以。也就是说,一开始是启用 query cache 的, 在运行过程中将其关闭,但事实上仍然会发生  Waiting for query cache lock,并没有真正的关闭;

 

关于query cache的延伸阅读,请见:

1、我的前同事waterbin帅哥的悲惨经历:MySQL Troubleshoting:Waiting on query cache mutex
2、淘宝苏普的旧文:Query Cache,看上去很美

 

详细测试过程:

一、测试方法

采用sysbench模拟并发oltp请求:

sysbench --test=tests/db/oltp.lua --oltp_tables_count=10 --oltp-table-size=100000 --rand-init=on --num-threads=64 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=1800 --max-requests=0 run

二、具体几种测试模式

1、一直关闭QC(query cache的简写,下同),即  query_cache_size = 0, query_cache_type = 0

测试过程中,一直都没有和query cache lock相关的状态出现,结果tps:2295.34

 

2、启用QC,但QC size 设置为 0,即:query_cache_size = 0,query_cache_type = 1

测试过程中,一直有 Waiting for query cache lock 状态出现,结果tps:2272.52

 

3、启用QC,但QC size为0,但启动时立刻关闭QC,即初始化时 query_cache_size = 0,query_cache_type = 1,启动后立刻修改 query_cache_type = 0

测试过程中,也一直有 Waiting for query cache lock 状态出现,结果tps:2311.54

 

4、关闭QC,但启动后立刻启用QC,即初始化时 query_cache_size = 0,query_cache_type = 0,启动后立刻修改 query_cache_type = 1

这时,会提示报错信息:

失败:ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

也就是说,如果一开始就关闭 QC 的话,是没办法在运行过程中动态再启用QC的。

 

5、启用QC,并设置QC size为256M,即 query_cache_size = 256M,query_cache_type = 1

这种情况下,在测试过程中一直有 Waiting for query cache lock 状态出现,并且结果tps也很差,只有 1395.39(几个案例中最差的一种)

 

6、启用QC,设置QC size为256M,但启动后立刻关闭QC,即 query_cache_size = 256M,query_cache_type = 1,启动后立刻修改 query_cache_type = 0

这种情况下,在测试过程中也一直有  Waiting for query cache lock 状态出现,结果tps:2295.79(在这个模式下,如果设置 query_cache_type = 2,效果也不佳)

 

第三种模式下,虽然看起来tps还不错,但毕竟上面只是简单模拟测试,实际情况下如果有频繁的query cache lock的话,tps肯定不会太好看。

因此,总的来说,想要获得较高tps的话,最好还是一开始就关闭QC,不要心存侥幸或者固守陈规。

[MySQL FAQ]系列 — 如何查看当前最新事务ID

InnoDB

写在前面:在个别时候可能需要查看当前最新的事务ID,以便做一些业务逻辑上的判断(例如利用事务ID变化以及前后时差,统计每次事务的响应时长等用途)。

通常地,我们有两种方法可以查看当前的事务ID:

1、执行SHOW ENGINE INNODB STATUS,查看事务相关信息

=====================================
150303 17:16:11 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
...
------------
TRANSACTIONS
Trx id counter 3359877657 -- 当前最大事务ID
Purge done for trx's n:o < 3359877468 undo n:o < 0 state: running
History list length 324
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started -- 该会话中执行SHOW ENGINE INNODB STATUS,不会产生事务,所以事务ID为0
MySQL thread id 4692367, OS thread handle 0x51103940, query id 677284426 xx.173ops.com 10.x.x.x yejr init
SHOW /*!50000 ENGINE*/ INNODB STATUS
---TRANSACTION 3359877640, not started --非活跃事务,还未开始
mysql tables in use 1, locked 0
MySQL thread id 4678384, OS thread handle 0x41a57940, query id 677284427 xx.173ops.com 10.x.x.x yejr System lock
select polinfo0_.Fid as Fid39_0_, ...

---TRANSACTION 3359877652, not started
MySQL thread id 4678383, OS thread handle 0x50866940, query id 677284420 xx.173ops.com 10.x.x.x yejr cleaning up

---TRANSACTION 3359877635, ACTIVE 1358 sec, thread declared inside InnoDB 5000 --活跃长事务,运行了1358秒还未结束,要引起注意,可能会导致大量锁等待发生
mysql tables in use 1, locked 1
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1
MySQL thread id 3120717, OS thread handle 0x529b4940, query id 677284351 xx.173ops.com 10.x.x.x yejr query end
insert into t_live_room ...

 

2、查看INFORMATION_SCHEMA.INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS 三个表,通过这些信息能快速发现哪些事务在阻塞其他事务

#先查询 INNODB_TRX 表,看看都有哪些事务

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
*************************** 1. row ***************************
 trx_id: 17778 -- 当前事务ID
 trx_state: LOCK WAIT -- 处于锁等待状态,也就是等待其他会话释放锁资源
 trx_started: 2015-03-04 10:40:26
 trx_requested_lock_id: 17778:82:3:6 -- 欲请求的锁
 trx_wait_started: 2015-03-04 10:40:26
 trx_weight: 2 -- 大意是该锁影响了2行记录
 trx_mysql_thread_id: 657 -- processlist中的线程ID
 trx_query: update trx_fee set fee=rand()*1000 where id= 4
 trx_operation_state: starting index read
 trx_tables_in_use: 1
 trx_tables_locked: 1
 trx_lock_structs: 2
 trx_lock_memory_bytes: 360
 trx_rows_locked: 1
 trx_rows_modified: 0
 trx_concurrency_tickets: 0
 trx_isolation_level: REPEATABLE READ
 trx_unique_checks: 1
 trx_foreign_key_checks: 1
 trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
 trx_is_read_only: 0
 trx_autocommit_non_locking: 0
 *************************** 2. row ***************************
 trx_id: 17773
  trx_state: RUNNING
 trx_started: 2015-03-04 10:40:23
 trx_requested_lock_id: NULL
 trx_wait_started: NULL
 trx_weight: 10
 trx_mysql_thread_id: 656
 trx_query: NULL
 trx_operation_state: NULL
 trx_tables_in_use: 0
 trx_tables_locked: 0
 trx_lock_structs: 2
 trx_lock_memory_bytes: 360
 trx_rows_locked: 9
 trx_rows_modified: 8
 trx_concurrency_tickets: 0
 trx_isolation_level: REPEATABLE READ
 trx_unique_checks: 1
 trx_foreign_key_checks: 1
 trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
 trx_is_read_only: 0
 trx_autocommit_non_locking: 0

 

#再看 INNODB_LOCKS 表,看看都有什么锁

mysql> select * from information_schema.INNODB_LOCKS\G
*************************** 1. row ***************************
lock_id: 17778:82:3:6 --当前锁ID
lock_trx_id: 17778 --该锁对应的事务ID
lock_mode: X -- 锁类型,排它锁X
lock_type: RECORD --锁范围,记录锁:record lock,其他锁范围:间隙锁:gap lock,或者next-key lock(记录锁+间隙锁)
lock_table: `test`.`trx_fee`
lock_index: PRIMARY --加载在哪个索引上的锁
lock_space: 82
lock_page: 3
lock_rec: 6
lock_data: 4
*************************** 2. row ***************************
lock_id: 17773:82:3:6
lock_trx_id: 17773
lock_mode: X
lock_type: RECORD
lock_table: `test`.`trx_fee`
lock_index: PRIMARY
lock_space: 82
lock_page: 3
lock_rec: 6
lock_data: 4

 

#最后看 INNODB_LOCK_WAITS 表,看看当前都有哪些锁等待

mysql> select * from information_schema.INNODB_LOCK_WAITS\G
*************************** 1. row ***************************
requesting_trx_id: 17778 --请求锁的事务ID(等待方)
requested_lock_id: 17778:82:3:6 -- 请求锁ID
blocking_trx_id: 17773 -- 阻塞该锁的事务ID(当前持有方,待释放)
blocking_lock_id: 17773:82:3:6 -- 持有的锁ID

关于INFORMATION_SCHEMA中和InnoDB有关的表用途描述,可以查看手册:21.29 INFORMATION_SCHEMA Tables for InnoDB

 

3、利用percona分支的特性,查看当前最新事务ID,该特性从5.6.11-60.3版本开始引入,执行下面的2个命令即可查看

mysqladmin ext | grep Innodb_max_trx_id
或者
mysql> show global status like 'Innodb_max_trx_id';

最后,交代下问题的来源其实是这样的,有位朋友和我讨论问题,说在java连接池中,发现2个事务的事务ID是一样的,测试的SQL代码:

begin;update trx set un=rand() where id=round(rand()*10)+1;select * from information_schema.INNODB_TRX; commit;select sleep(0.01);begin;update trx set un=rand() where id=round(rand()*10)+1;select * from information_schema.INNODB_TRX;commit;

这串代码不能折行,中间的 sleep 停留 不能太大,也就是模拟足够快的情况下,检查2次事务的ID是否有变化。可以发现,时间足够短的话,2次查询到的事务ID是一样的,并没有发生变化。大家也可以在自己的环境下试试。

[MySQL FAQ]系列 — 从MyISAM转到InnoDB需要注意什么

问题
当前,绝大多数业务场景用InnoDB已经完全能搞定了,越来越多的业务从MyISAM转向InnoDB引擎,那么有哪些注意事项呢?

分析
当了解完两种引擎的不同之处,很轻松的就能知道有哪些关键点了。
总的来说,从MyISAM转向InnoDB的注意事项有:

1、MyISAM的主键索引中,可以在非第一列(非第一个字段)使用自增列,而InnoDB的主键索引中包含自增列时,必须在最前面;这个特性在discuz论坛中,被设计用于“抢楼”功能,因此,若有类似的业务,则无法将该表从MyISAM转成InnoDB,需要自行变通实现(我们则是将其改到Redis中实现);
2、不带条件频繁统计全表总记录数时(SELECT COUNT(*) FROM TAB),InnoDB相对较慢,而MyISAM则飞快;不过,如果是基于索引条件的统计,则二者相差不大;
3、InnoDB在5.6以前不支持全文索引,不过这个相信无所谓,没什么人会在MySQL里直接跑全文索引,尤其是对中文的全文索引(前阵子有开发同学提需求直接被我否了),确实有需要的话,可以采用Sphinx、Lucene等其他方案实现;
4、一次性导入大量数据并且后续还要进行加工处理的,可以先导入到MyISAM引擎表中,经过一通加工处理完后,再导入InnoDB表(我曾经在业务中用此方法提高数据批量导入及处理效率);
5、InnoDB不支持LOAD TABLE FROM MASTER语法(不过应该也很少人使用吧);
从MyISAM转成InnoDB可以享受的好处则有:

1、完整事务特性支持,以及更高的数据并发存取效率,即更高的TPS;
2、数据库实例异常重启后,InnoDB表能自动修复,而且速度相对更快,而MyISAM需要被触发才能修复,且相对耗时可能多4~5倍甚至更多;
3、更高的数据读取性能,因为InnoDB把数据及索引同时缓存在内存中,而MyISAM只缓存了索引;
4、InnoDB支持外键(不过在MySQL中,应该很少人用到外键);
两个引擎间的重要区别详情见下:

MyISAM引擎的特点:
1、堆组织表;
2、不支持事务;
3、数据文件和索引文件分开存储;
4、支持全文索引;
5、主键索引和二级索引完全一样都是B+树的数据结构,只有是否唯一的区别(主键和唯一索引有唯一属性,其他普通索引没有唯一属性。B+树叶子节点存储的都是指向行记录的row pointer);
6、有特殊计数器记录当前记录数;
7、不支持Crash recovery;
8、索引文件很容易损坏;
InnoDB引擎的特点

1、索引组织表;
2、支持事务;
3、数据文件和索引文件存储在同一个表空间中;
4、在5.6以前,不支持全文索引;
5、主键和二级索引数据结构一样都是B+树,但叶子节点存储的键值不一样(主键的叶子节点存储整行数据,因此也称为聚集索引;而二级索引的叶子节点存储的是主键的键值)
5、支持Crash recovery;
6、相同数据量时,InnoDB表空间文件大小约为MyISAM引擎的1.5~2倍;
关于InnoDB、MyISAM两种引擎的对比测试,可以参考Percona的这个对比:http://www.percona.com/blog/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/

[MySQL FAQ]系列 — 如何将两个表名对调

问题
有位同学问我,在类似pt-osc场景下,需要将两个表名对调,怎么才能确保万无一失呢?

分析
估计其他同学就笑了,表名对掉还不简单吗,相互RENAME一下嘛。

但是,我们想要的是同时完成表名对调,如果是先后的对掉,可能会导致有些数据写入失败,那怎么办?

其实也不难,从MySQL手册里就能找到方法,那就是:同时锁定2个表,不允许写入,然后对调表名。

我们通常只锁一个表,那么同时锁两个表应该怎么做呢,可以用下面的方法:

LOCK TABLES t1 WRITE, t2 WRITE;
ALTER TABLE t1 RENAME TO t3;
ALTER TABLE t2 RENAME TO t1;
ALTER TABLE t3 RENAME TO t2;
UNLOCK TABLES;
看到了吧,其实很简单,两个表同时加表级写锁,然后用ALTER语法改名就可以了。

废话挺多的,谢谢各位客官耐心看完 :)

[MySQL FAQ]系列 — 修改my.cnf配置不生效

问题
修改了 my.cnf 配置文件后,却不生效,这是怎么回事?
原因
我们注意到,这里只说了修改 my.cnf,并没有说清楚其绝对路径是哪个文件。也就是说,有可能修改的不是正确路径下的my.cnf文件。

 

在MySQL中,是允许存在多个 my.cnf 配置文件的,有的能对整个系统环境产生影响,例如:/etc/my.cnf。有的则只能影响个别用户,例如:~/.my.cnf。

MySQL读取各个my.cnf配置文件的先后顺序是:

  • /etc/my.cnf
  • /etc/mysql/my.cnf
  • /usr/local/mysql/etc/my.cnf
  • ~/.my.cnf
  • 其他自定义路径下的my.cnf,例如:/data/mysql/yejr_3306/my.cnf

不管是mysqld服务器端程序,还是mysql客户端程序,都可以采用下面两个参数来自行指定要读取的配置文件路径:

  • –defaults-file=#, 只读取指定的文件(不再读取其他配置文件)
  • –defaults-extra-file=#, 从其他优先级更高的配置文件中读取全局配置后,再读取指定的配置文件(有些选项可以覆盖掉全局配置从的设定值)

因此,可以看到,如果你修改的是非“著名”目录下的 my.cnf,有可能看起来是不生效的,需要自行指定,或者统一放在 /etc/my.cnf 下,采用多实例的方式来管理即可。

[MySQL FAQ]系列 — MySQL联合索引是否支持不同排序规则

篇首语:
截止到目前的5.7.4版本为止,MySQL的联合索引仍无法支持联合索引使用不同排序规则,例如:ALTER TABLE t ADD INDEX idx(col1, col2 DESC)。

先来了解下MySQL关于索引的一些基础知识要点:

• a、EXPLAIN结果中的key_len只显示了条件检索子句需要的索引长度,但 ORDER BY、GROUP BY 子句用到的索引则不计入 key_len 统计值;
• b、联合索引(composite index):多个字段组成的索引,称为联合索引;
例如:ALTER TABLE t ADD INDEX `idx` (col1, col2, col3)
• c、覆盖索引(covering index):如果查询需要读取到索引中的一个或多个字段,则可以从索引树中直接取得结果集,称为覆盖索引;
例如:SELECT col1, col2 FROM t;
• d、最左原则(prefix index):如果查询条件检索时,只需要匹配联合索引中的最左顺序一个或多个字段,称为最左索引原则,或者叫最左前缀;
例如:SELECT * FROM t WHERE col1 = ? AND col2 = ?;
• e、在老版本(大概是5.5以前,具体版本号未确认核实)中,查询使用联合索引时,可以不区分条件中的字段顺序,在这以前是需要按照联合索引的创建顺序书写SQL条件子句的;
例如:SELECT * FROM t WHERE col3 = ? AND col1 = ? AND col2 = ?;
• f、MySQL截止目前还只支持多个字段都是正序索引,不支个别字段持倒序索引;
例如:ALTER TABLE t ADD INDEX `idx` (col1, col2, col3 DESC),这里的DESC只是个预留的关键字,目前还不能真正有作用
• g、联合索引中,如果查询条件中最左边某个索引列使用范围查找,则只能使用前缀索引,无法使用到整个索引;
例如:SELECT * FROM t WHERE col1 = ? AND col2 >= ? AND col3 = ?; 这时候,只能用到 idx 索引的最左2列进行检索,而col3条件则无法利用索引进行检索
• h、InnoDB引擎中,二级索引实际上包含了主键索引值;

关于 key_len 的计算规则:

• 当索引字段为定长数据类型,比如:char,int,datetime,需要有是否为空的标记,这个标记需要占用1个字节;
• 当索引字段为变长数据类型,比如:varchar,除了是否为空的标记外,还需要有长度信息,需要占用2个字节;
• 当字段定义为非空的时候,是否为空的标记将不占用字节;
• 同时还需要考虑表所使用字符集的差异,latin1编码一个字符1个字节,gbk编码一个字符2个字节,utf8编码一个字符3个字节;

因此,key_len长度的计算公式

• varchr(10)变长字段且允许NULL : 10*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL标记位)+2(变长字段)
• varchr(10)变长字段且不允许NULL : 10*(Character Set:utf8=3,gbk=2,latin1=1)+2(变长字段)
• char(10)固定字段且允许NULL : 10*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL标记位)
• char(10)固定字段且不允许NULL : 10*(Character Set:utf8=3,gbk=2,latin1=1)

附,关于 filesort 排序算法:
光看 filesort 字面意思,可能以为是要利用磁盘文件进行排序,实则不全然。
当MySQL不能使用索引进行排序时,就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序,如果内存装载不下,它会将磁盘上的数据进行分块,再对各个数据块进行排序,然后将各个块合并成有序的结果集(实际上就是外排序)。

对于filesort,MySQL有两种排序算法:
1、两遍扫描算法(Two passes)
实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出,然后在设定的内存(通过参数 sort_buffer_size 设定)中进行排序,完成排序之后再次通过行指针信息取出所需的列。
注:该算法是4.1之前只有这种算法,它需要两次访问数据,尤其是第二次读取操作会导致大量的随机I/O操作。不过,这种方法内存开销较小。

2、一次扫描算法(single pass)
该算法一次性将所需的列全部取出,在内存中排序后直接将结果输出。

注:从 MySQL 4.1 版本开始支持该算法。它减少了I/O的次数,效率较高,但是内存开销也较大。如果我们将并不需要的列也取出来,就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中,可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时,MySQL 就会选择使用第一种排序算法,反之,则会选择第二种。为了尽可能地提高排序性能,我们自然更希望使用第二种排序算法,所以在SQL中仅仅取出需要的列是非常有必要的。

当对连接操作进行排序时,如果ORDER BY仅仅引用第一个表的列,MySQL对该表进行filesort操作,然后进行连接处理,此时,EXPLAIN输出“Using filesort”;否则,MySQL必须将查询的结果集生成一个临时表,在连接完成之后进行filesort操作,此时,EXPLAIN输出“Using temporary;Using filesort”。

后面是几个几个测试结果,有兴趣不怕累的可以看看,哈哈。

测试MySQL版本:5.5.37-log MySQL Community Server (GPL)

#创建一个测试表,id是主键字段,(a1, a2) 组成联合索引

(yejr@imysql.com)> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`a1` int(10) unsigned NOT NULL DEFAULT '0',
`a2` int(10) unsigned NOT NULL DEFAULT '0',
`aa` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx` (`a1`,`a2`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8

#填充了64条测试数据

(yejr@imysql.com)> show table status like 't'\G
*************************** 1. row ***************************
Name: t
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 64
Avg_row_length: 256
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 122
Create_time: 2014-09-15 17:17:09
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:

#对 a1、a2 正序排序,同时取a1、a2两个字段,可以直接使用该联合索引取回结果,并且排序完成
#符合规则c

(yejr@imysql.com)> explain select a1, a2 from t order by a1, a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1、a2 倒序排序,同时取a1、a2两个字段,可以直接使用该联合索引取回结果,并且排序完成
#由于同时对a1、a2都是倒序排序,因此完全可以用到索引的顺序,只是反向扫描而已
#符合规则c

(yejr@imysql.com)> explain select a1, a2 from t order by a1 desc, a2 desc\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1、a2正序排序,只取a1字段,可以直接使用该联合索引取回结果,并且排序完成
#匹配规则c

(yejr@imysql.com)> explain select a1 from t order by a1, a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1、a2 正序排序,只取a2字段,可以直接使用该联合索引取回结果,并且排序完成
#符合规则c

(yejr@imysql.com)> explain select a2 from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#只对 a1 正序排序,同时取a1、a2两个字段,可以直接使用该联合索引取回结果,并且排序完成
#符合规则c

(yejr@imysql.com)> explain select a1, a2 from t order by a1\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1 正序排序,对 a2 倒序排序,只取a1字段,可以直接使用该联合索引取回结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#这时虽然只读取一个字段,但实际还是扫描了整个索引,并非使用前缀索引
#符合规则c、f

(yejr@imysql.com)> explain select a1 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#只取a1字段,同时只对 a1 字段正序排序,这时可用联合索引取得结果,同时也可以利用前缀索引的原则进行排序
#符合规则c

(yejr@imysql.com)> explain select a1 from t order by a1\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#只取a1字段,同时只对 a2 字段正序排序,这时虽然可用联合索引取得结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#符合规则c、f

(yejr@imysql.com)> explain select a1 from t order by a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#对 a1 正序排序,对a2 倒序排序,只取a1字段,可以直接使用该联合索引取回结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#这时虽然只读取一个字段,但实际还是扫描了整个索引,并非使用前缀索引
#符合规则c、f

(yejr@imysql.com)> explain select a1 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#对 a1 正序排序,对a2 倒序排序,只取a2字段,可以直接使用该联合索引取回结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#这时虽然只读取一个字段,但实际还是扫描了整个索引,并非使用前缀索引
#符合规则c、f

(yejr@imysql.com)> explain select a2 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#对 a1 正序排序,对a2 倒序排序,只取a2字段,可以直接使用该联合索引取回结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#这时虽然只读取一个字段,但实际还是扫描了整个索引,并非使用前缀索引
#符合规则c、f

(yejr@imysql.com)> explain select a1 from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1 、a2顺序排序,取得主键id字段,可以直接使用该联合索引取回结果并完成排序。
#这里需要注意下,二级索引其实是包括主键索引的,因此用idx索引即可取到全部结果。
#下面这个SQL也是一样的效果:select a1,a2,id from t order by a1, a2;
#符合规则c、h

(yejr@imysql.com)> explain select id from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1 正序排序,对a2 倒序排序,取得主键id字段,可以直接使用该联合索引取回结果,但需要进行filesort排序。
#符合规则c、f、h

(yejr@imysql.com)> explain select id from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#对 a1 倒序排序,对a2 正序排序,取得主键id字段,可以直接使用该联合索引取回结果,但需要进行filesort排序。
#符合规则c、f、h

(yejr@imysql.com)> explain select id from t order by a1 desc, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#过滤条件a1字段(使用前缀索引扫描,key_len为4),对a2字段进行正序排序,取得主键id字段,可以直接使用联合索引取回结果
#符合规则a、c、d、h

(yejr@imysql.com)> explain select id from t where a1 = 219 order by a2\G
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: idx
key: idx
key_len: 4
ref: const
rows: 2
Extra: Using where; Using index