MySQL各种Tips(不定时更新)
1，into outfile 生成sql：一般都是生成文本或者其他形式的文件，现在需要生成sql形式的文件。
select concat('insert into tab_name(col1,col2,col3) values(',col1,',','\'',col2,'\'',',','\'',col3,'\'',')') into outfile '/tmp/tt.txt' from tt;
出来tt.txt的结果为：
View Code
insert into tab_name(id,name,age) values(1,'aaa','23'); insert into tab_name(id,name,age) values(2,'aaa','23'); insert into tab_name(id,name,age) values(3,'aaa','23'); insert into tab_name(id,name,age) values(4,'aaa','23'); insert into tab_name(id,name,age) values(6,'aaa','23');
2，MySQL中like语句及相关优化器tips： 利用INNODB特性，来优化like '%%'的方法。
3，Mysqldump输出方式和进度报告： 用这个mysqldump可以直接在备份的时候打印出进度。注意，需要有可执行权限和64位系统。
./mysqldump -uroot -p -h192.168.1.11 --default-character-set=utf8 dbname tablename --result-file=table.sql --progress-reportEnter current_table: dbname.tablename Stage: 10%
4，在show processlist显示的状态里面，update表示正在insert ，updating表示正在delete，Updating才是表示正在update。
5，Innodb表，当表里面有100行记录，有自增ID。清除表里的数据之后，重启数据库。发现表的自增ID重新计算，从0开始。MyISAM表不会。
View Code
root@localhost : test 05:12:51>show create table tte\G; *************************** 1. row *************************** Table: tte Create Table: CREATE TABLE `tte` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT 1 row in set (0.00 sec) ERROR: No query specified root@localhost : test 05:13:58>insert into tte(name,age,address) values('a',11,'hz'),('b',22,'gz'),('c',33,'bj'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 root@localhost : test 05:14:39>select * from tte; +----+------+------+---------+ | id | name | age | address | +----+------+------+---------+ | 1 | a | 11 | hz | | 2 | b | 22 | gz | | 3 | c | 33 | bj | +----+------+------+---------+ 3 rows in set (0.00 sec) root@localhost : test 05:14:44>show create table tte\G; *************************** 1. row *************************** Table: tte Create Table: CREATE TABLE `tte` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT 1 row in set (0.00 sec) ERROR: No query specified root@localhost : test 05:14:46>delete from tte; Query OK, 3 rows affected (0.00 sec) root@localhost : test 05:14:59>show create table tte\G; *************************** 1. row *************************** Table: tte Create Table: CREATE TABLE `tte` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT 1 row in set (0.00 sec) ERROR: No query specified root@localhost : test 05:15:04>\q Bye zhoujy@zhoujy:~$ sudo /etc/init.d/mysql restart [sudo] password for zhoujy: Rather than invoking init scripts through /etc/init.d, use the service(8) utility, e.g. service mysql restart Since the script you are attempting to invoke has been converted to an Upstart job, you may also use the stop(8) and then start(8) utilities, e.g. stop mysql ; start mysql. The restart(8) utility is also available. mysql stop/waiting mysql start/running, process 5285 zhoujy@zhoujy:~$ mysql test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.1.67-0ubuntu0.11.10.1-log (Ubuntu) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@localhost : test 05:15:22>show create table tte\G; *************************** 1. row *************************** Table: tte Create Table: CREATE TABLE `tte` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT 1 row in set (0.00 sec) ERROR: No query specified root@localhost : test 05:15:24>insert into tte(name,age,address) values('a',11,'hz'),('b',22,'gz'),('c',33,'bj'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 root@localhost : test 05:15:37>select * from tte; +----+------+------+---------+ | id | name | age | address | +----+------+------+---------+ | 1 | a | 11 | hz | | 2 | b | 22 | gz | | 3 | c | 33 | bj | +----+------+------+---------+ 3 rows in set (0.00 sec)
6，MySQL中pager的使用：Pager的使用
7，MySQL之 index_merge 的弃用：当执行计划出现索引合并时，可以修改optimizer_switch的一个状态，弃用index_merge索引，要慎用
set optimizer_switch = 'index_merge_union=off,index_merge_intersection=off,index_merge_sort_union=off';
View Code
root@localhost : test 02:09:32>explain select * From idx_mer where name='e' and name1 = 'ee'\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: idx_mer type: index_merge possible_keys: idx_name,idx_name1 key: idx_name1,idx_name key_len: 33,33 ref: NULL rows: 1 Extra: Using intersect(idx_name1,idx_name); Using where 1 row in set (0.00 sec) root@localhost : test 02:09:36>set session optimizer_switch='index_merge_intersection=off'; Query OK, 0 rows affected (0.00 sec) root@localhost : test 02:10:12>explain select * From idx_mer where name='e' and name1 = 'ee'\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: idx_mer type: ref possible_keys: idx_name,idx_name1 key: idx_name1 key_len: 33 ref: const rows: 2 Extra: Using where 1 row in set (0.00 sec)
8，MySQL提示符设置：prompt
[client] #在client组下面设置 prompt = \\u@\\h : \\d \\r:\\m:\\s> #屏幕输出打印到指定位置 tee = /home/mysql/query.log 效果： root@localhost : test 09:54:37>
9，MySQL隔离级别 read committed下的注意事项：在read committed/uncommitted 隔离级别下面，binlog 的 statement模式不被支持。
View Code
mysql> select @@global.tx_isolation,@@global.binlog_format; +-----------------------+------------------------+ | @@global.tx_isolation | @@global.binlog_format | +-----------------------+------------------------+ | READ-COMMITTED | ROW | +-----------------------+------------------------+ 1 row in set (0.00 sec) mysql> select @@session.tx_isolation,@@session.binlog_format; +------------------------+-------------------------+ | @@session.tx_isolation | @@session.binlog_format | +------------------------+-------------------------+ | READ-COMMITTED | ROW | +------------------------+-------------------------+ 1 row in set (0.00 sec) mysql> set session binlog_format='statement'; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(1,'test',1); #在read committed/uncommitted 隔离级别下面，binlog 的 statement模式不被支持。 ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. mysql> select @@session.tx_isolation,@@session.binlog_format; +------------------------+-------------------------+ | @@session.tx_isolation | @@session.binlog_format | +------------------------+-------------------------+ | READ-COMMITTED | STATEMENT | +------------------------+-------------------------+ 1 row in set (0.00 sec) mysql> set session transaction isolation level repeatable read,binlog_format = statement; Query OK, 0 rows affected (0.00 sec) mysql> select @@session.tx_isolation,@@session.binlog_format; +------------------------+-------------------------+ | @@session.tx_isolation | @@session.binlog_format | +------------------------+-------------------------+ | REPEATABLE-READ | STATEMENT | +------------------------+-------------------------+ 1 row in set (0.00 sec) mysql> insert into test values(1,'test',1); Query OK, 1 row affected (0.00 sec)
10，innodb表加索引的限制：索引大小需要小于767个字节才能建立成功，767<size<3072：会报warnging，能建立成功但是会被截断，size>3072：会直接报错，不会建立索引。size：长度*字符集大小，如：varchar(10) utf8 则是10*3=30 bytes。
View Code
root@localhost : test 03:33:33>show create table tb\G; *************************** 1. row *************************** Table: tb Create Table: CREATE TABLE `tb` ( `a` varchar(256) DEFAULT NULL, `b` varchar(2556) DEFAULT NULL, `c` varchar(256) DEFAULT NULL, `d` varchar(256) DEFAULT NULL, `e` varchar(256) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) root@localhost : test 03:33:43>alter table tb add index idx_all(a,b); Query OK, 0 rows affected, 4 warnings (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 root@localhost : test 03:34:05>show warnings; +---------+------+---------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------+ | Warning | 1071 | Specified key was too long; max key length is 767 bytes | | Warning | 1071 | Specified key was too long; max key length is 767 bytes | | Warning | 1071 | Specified key was too long; max key length is 767 bytes | | Warning | 1071 | Specified key was too long; max key length is 767 bytes | +---------+------+---------------------------------------------------------+ 4 rows in set (0.00 sec) root@localhost : test 03:53:40>alter table tb add index idx_all_5(a,b,c,d,e); ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes root@localhost : test 03:54:22>show create table tb\G; *************************** 1. row *************************** Table: tb Create Table: CREATE TABLE `tb` ( `a` varchar(256) DEFAULT NULL, `b` varchar(2556) DEFAULT NULL, `c` varchar(256) DEFAULT NULL, `d` varchar(256) DEFAULT NULL, `e` varchar(256) DEFAULT NULL, KEY `idx_all` (`a`(255),`b`(255)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
11，在show processlist显示的状态里面：update表示正在insert ，updating表示正在delete，Updating才是表示正在update。
+-----+------+-----------+------+---------+------+----------------+----------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+------+-----------+------+---------+------+----------------+----------------+ | 322 | root | localhost | test | Query | 1 | updating | delete from ...| | 322 | root | localhost | test | Query | 18 | Updating | update ...| | 322 | root | localhost | test | Query | 49 | update | insert into ...| +-----+------+-----------+------+---------+------+----------------+----------------+
12，清除Slave信息：在5.5之后新增了一个命令：reset slave all，可以清除Slave的所有信息。
View Code
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.200.25 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: mysqld-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 0 Relay_Log_Space: 126 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 1 row in set (0.00 sec) ERROR: No query specified mysql> reset slave all; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; Empty set (0.00 sec)
13，字段类型timestamp在5.6之前，一个表不能定义2个以上的包含（1，default current_timestamp；2，on update current_timestamp）的表定义，否则建表出错。原因见：这里
View Code
5.6 之前版本： #不能定义2个列包含 default current_timestamp 和 on update current_timestamp 属性的字段： root@localhost : test 09:29:27>CREATE TABLE `tran1` ( -> `id` int(11) NOT NULL DEFAULT '0', -> `name` varchar(10) DEFAULT NULL, -> `ctime` timestamp default current_timestamp, -> `mtime` timestamp on update current_timestamp, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause #能定义同一个列包含 default current_timestamp 和 on update current_timestamp 属性的字段： root@localhost : test 09:29:47>CREATE TABLE `tran1` ( -> `id` int(11) NOT NULL DEFAULT '0', -> `name` varchar(10) DEFAULT NULL, -> `ctime` timestamp default current_timestamp on update current_timestamp, -> `mtime` timestamp, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.27 sec) 5.6版本之后： #没有这个限制： root@192.168.220.200 : test 09:30:03>CREATE TABLE `tran1` ( -> `id` int(11) NOT NULL DEFAULT '0', -> `name` varchar(10) DEFAULT NULL, -> `ctime` timestamp default current_timestamp, -> `mtime` timestamp on update current_timestamp, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.20 sec)
14，mysql -e "command" 生成结果导入指定文件时，若要同时显示语句本身，-v; 若要增加查询结果行数，加-vv; 若要增加执行时间, 加-vvv。
mysql -e "select user,host from mysql.user" -v/-vv/-vvv > 1.txt
15，myisamchk使用注意事项：当一张MYISAM表坏了，索引文件大于sort_buffer_size，要是用myisamchk去修复，会报错，需要手动指定--sort_buffer_size。大小最好设置大于MYI文件的大小。
View Code
root@zjy:/data/mysql2/mstem# myisamchk -r -q edail_log.MYI - recovering (with sort) MyISAM-table ' edail_log.MYI' Data records: 0 - Fixing index 1 - Fixing index 2 myisamchk: error: myisam_sort_buffer_size is too small MyISAM-table ' edail_log.MYI' is not fixed because of errors Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag root@zjy:/data/mysql2/mstem# myisamchk -r -q --sort_buffer_size=2024M edail_log.MYI - check record delete-chain - recovering (with sort) MyISAM-table ' edail_log.MYI' Data records: 0 - Fixing index 1 - Fixing index 2 - Fixing index 3 Data records: 68464254
16，字符集：utf8mb4：mysql 5.5.3之后出来的字符集，占用1~4个字节，最大占用的字节数为4。目前这个字段主要应用在（Emoji 表情需要4个字节）让Mysql支持Emoji表情 ，utf8mb4兼容utf8（1~3字节），且比utf8能表示更多的字符。什么时候需要才用utf8mb4，否则只是浪费空间（比如utf8占用3个字节，10个汉字占用30个字节。这时候改成utf8mb4也只有30个，物理上占用的空间一样，但是在申请内存的时候，utf8mb4会多于utf8 1个字节）。对于一个BMP字符（<=3字节），utf8和utf8mb4的有相同的存储特性：相同的代码值，相同长度，相同的编码；但utf8mb4范围更大。
适用范围：存类似emoji 这种类型的数据，是因为utf8满足不了，才用utf8mb4的。能用utf8的都能用utf8mb4，而且占用的也只是3个字节。对于哪些需要4个字节的，这个就用到了utf8mb4自己的特点。要是不在乎空间浪费的话，utf8mb4可以全部替换掉utf8，担心的话，则按照需要分配字符集（表，字段）。
17，在修复Innodb表的时候需要注意：innodb_purge_threads 和 innodb_force_recovery一起设置的时候需要注意：innodb_purge_threads=1时，innodb_force_recovery不能大于1（可以等于1）；当innodb_purge_threads=0时，innodb_force_recovery没有限制。
18，数据库中的加锁协议，分为乐观/悲观两类：乐观加锁，操作记录时不主动加锁，由后续冲突者负责加锁；悲观加锁，操作记录时主动加锁。乐观加锁适用于并发冲突较小时，减少了加锁开销；而悲观加锁适用于并发冲突较大时，简化操作流程。以InnoDB为例，Update采用了悲观加锁协议，Insert则采用了乐观加锁。来自这里
19，Engine=innodb，当table.frm文件丢失时，drop database会出现两种情况： 1）version=5.1,报ERROR 1010 (HY000): Error dropping database (can't rmdir './D1', errno: 39) ；2）version=5.5,正常执行。根源在innobase_drop_database函数的调用顺序的不同。而engine=MyISAM 时，5.1,5.5都报39错误。
20，为什么正则表达式中/d比[0-9]效率差？ /d匹配的是Unicode，所以相当于要筛选各种语言里的数字字符，而[0-9]只匹配这是个数字。
21，5.6之前add foreign key 还是不能使用fast-index-creation. 5.6之后支持，但需要在执行加外键之前先 set foreign_key_checks=0。
22，STATEMENT下5.1到5.5复制的注意事项：5.1 无符号的整数类型，如果相减得到负数，mysql会自动转化为最大的正数；5.5直接报错。
5.1： root@localhost : test 10:24:30>select cast(0 as unsigned)-1; +-----------------------+ | cast(0 as unsigned)-1 | +-----------------------+ | 18446744073709551615 | +-----------------------+ 1 row in set (0.00 sec) 5.5： zjy@192.168.200.233 : (none) 10:25:04>select cast(0 as unsigned)-1; ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
23，MySQL创建PK一定是copy table的方式，是因为整个过程都持有metadata lock的排他锁。当设置old_alter_table=1，所有的DDL都必须copy table，copy table的过程中只持有MDL的共享锁，就不会阻塞读了。详情见：这里和这里；另外，在5.6版本之前，old_alter_table 还有另一个用处：就是对表有重复数据的字段添加唯一索引的时候报错，则可以设置为1，详情见：这里 和 这里，但出现的问题是：设置old_alter_table=ON，会绕过innodb_plugin的alter table快速建索引不拷贝表的优化，会出现copy tmp table。
24，mysqladmin的察看qps：
mysqladmin --no-defaults ext -i1 -uzjy -p -h192.168.200.2 | awk '/Queries/{q=$4-qp;qp=$4} /Threads_connected/{tc=$4} /Threads_running/{printf "%5d %5d %5d\n",q,tc,$4}'
第一列：每秒查询量 第二列：链接数 第三列：当前执行的链接数 1503 100 2 576 100 2 964 100 3 1288 100 2 582 100 2 1064 100 2 971 100 3 438 100 2 290 100 2 1452 100 2
每秒慢查询的数量：
awk '/^# Time:/{print $3,$4,c;c=0}/^# User/{c++}' mysql-slow-3306.log
131212 10:00:40 9 131212 10:07:27 1 131212 10:09:56 1 131212 10:11:09 1 131212 10:11:12 20 131212 10:11:57 3 131212 10:14:50 1 131212 10:18:26 1 131212 10:18:55 1 131212 10:19:33 1 131212 10:21:18 1 131212 10:25:52 1 131212 10:26:22 2 131212 10:26:32 1 131212 10:28:08 1 131212 10:33:40 1 131212 10:34:43 1 131212 10:36:05 1 131212 10:41:21 1 131212 10:42:27 1 131212 10:45:29 1 131212 10:47:19 1 131212 10:47:24 1 131212 10:47:31 1 131212 10:49:14 1
25，limit 提前终止。limit N是取前N条记录；有2种情况，1:要排序，需要把所有的结果取出来排序，再取前N条，不会提前终止。2：不排序，全表随机扫描记录，只需要扫描取出前N条记录就终止了，不会继续扫描。相比之下，2比1要快很多，因为扫描的行数少很多。
26，关于死锁：在防止死锁（deadlock）方面，表锁比行锁更有优势。使用表锁的时候，死锁不会发生，因为服务器可以通过查看语句来检测需要的数据表，并提前锁定它们。而InnoDB会发生死锁，因为存储引擎没有在事务开始的时候分配所有锁，而是在事务处理的过程中，当检测到需要锁的时候才分配。这就可能出现两个语句获取了锁，接着试图进一步获取锁（需要多个锁），但是这些锁却被对方保持着，等待对方释放。其结果是每个客户端都拥有一个锁，同时还需要利用其它的客户端拥有的锁才能继续执行。这会导致死锁，服务器必须终止其中一个事务。
27，Range扫描。对于范围条件查询【range】，MySQL无法使用范围列后面的其他索引列了【>,<】，而对于多个等值条件查询可以用【in】，但对Order By、Group By也不能。记住以下这些情况：
索引：（customCate,creator) 1： explain select * from tb where customCate in ('4028487718bc5d980118bd277fc40000','402848771a0449fc011a044afca60001','402848771a0449fc011a05672a260655','402848771a05fb0e011a0a50401b058f') and creator ='4028487718bc5d980118bd277fc40069'; +----+-------------+-------------+-------+------------------------------------------+------------------------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+------------------------------------------+------------------------+---------+------+-------+-------------+ | 1 | SIMPLE | tb | range | idx_customCate_creator,idx_creator_releD | idx_customCate_creator | 198 | NULL | 16371 | Using where | +----+-------------+-------------+-------+------------------------------------------+------------------------+---------+------+-------+-------------+ 执行计划里的Type为range，但是in属于多个等值条件，可以继续用第2个索引，和>,<情况不一样。 2： explain select * from tb where customCate in ('402848771a05fb0e011a0a50401b058f','402848771a05fb0e011a0a506eab0590','402848771a05fb0e011a0a50c90f0592') order by creator; +----+-------------+-------------+-------+------------------------+------------------------+---------+------+-------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+------------------------+------------------------+---------+------+-------+-----------------------------+ | 1 | SIMPLE | tb | range | idx_customCate_creator | idx_customCate_creator | 99 | NULL | 19869 | Using where; Using filesort | +----+-------------+-------------+-------+------------------------+------------------------+---------+------+-------+-----------------------------+ 对于Order By、Group By 第2个索引不能用，和>,< 情况一样。 3: explain select * from tb where customCate in ('402848771a05fb0e011a0a50401b058f','402848771a05fb0e011a0a506eab0590','402848771a05fb0e011a0a50c90f0592') and creator in ('4028487718bc5d980118bd277fc40069','4028487718dbd37a0118e54b53e300e8','4028487718dbd37a0118e54b53e300e8'); +----+-------------+-------------+-------+------------------------------------------+------------------------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+------------------------------------------+------------------------+---------+------+-------+-------------+ | 1 | SIMPLE | tb | range | idx_customCate_creator,idx_creator_releD | idx_customCate_creator | 198 | NULL | 19881 | Using where | +----+-------------+-------------+-------+------------------------------------------+------------------------+---------+------+-------+-------------+ 1 row in set (0.00 sec) 可以在多个字段一起用多个等值条件查询，多个字段的索引都可以被利用，和>,<情况不一样。 4:IN 用法不适用于 NOT IN explain select * from tb where customCate not in ('4028487718bc5d980118bd277fc40000','402848771a05fb0e011a0a506eab0590','402848771a05fb0e011a0a50c90f0592') and creator ='4028487718bc5d980118bd277fc40069'; +----+-------------+-------------+------+------------------------------------------+-------------------+---------+-------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+------------------------------------------+-------------------+---------+-------+-------+-------------+ | 1 | SIMPLE | tb | ref | idx_customCate_creator,idx_creator_releD | idx_creator_releD | 99 | const | 42008 | Using where | +----+-------------+-------------+------+------------------------------------------+-------------------+---------+-------+-------+-------------+
28，>=&<= 和 between...and...的区别：数学符号的比较按照正常的大小；而between...and 则按照无符号即（singed）进行比较，在字段里面定义了unsigned 的类型需要注意这个限制，因为需要进行转换（cast）成signed进行比较，所以操作signed的范围大小会出现问题。
root@localhost : test 09:27:47>CREATE TABLE `t` ( -> `id` bigint(20) unsigned DEFAULT NULL, -> `b` int(11) DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.27 sec) root@localhost : test 09:28:09>insert into t values(8894754949779693574,1),(8894754949779693579,2),(17790886498483827171,3),(17804814049994178845,4),(17804814049994178846,5),(18446737795875551629,6); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 root@localhost : test 09:28:15>select * from t; +----------------------+------+ | id | b | +----------------------+------+ | 8894754949779693574 | 1 | | 8894754949779693579 | 2 | | 17790886498483827171 | 3 | | 17804814049994178845 | 4 | | 17804814049994178846 | 5 | | 18446737795875551629 | 6 | +----------------------+------+ 6 rows in set (0.00 sec) root@localhost : test 09:28:22>select count(*) from t where id>=8894754949779693574 and id<=17790886498483827171; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.01 sec) root@localhost : test 09:28:41>select * from t where id>=8894754949779693574 and id<=17790886498483827171; +----------------------+------+ | id | b | +----------------------+------+ | 8894754949779693574 | 1 | | 8894754949779693579 | 2 | | 17790886498483827171 | 3 | +----------------------+------+ 3 rows in set (0.00 sec) root@localhost : test 09:28:47> root@localhost : test 09:28:47>select count(*) from t where id between 8894754949779693574 and 17790886498483827171; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) root@localhost : test 09:29:21>select cast(id as signed) from t; +---------------------+ | cast(id as signed) | +---------------------+ | 8894754949779693574 | | 8894754949779693579 | | -655857575225724445 | | -641930023715372771 | | -641930023715372770 | | -6277833999987 | +---------------------+ root@localhost : test 09:31:20>select * from t where id between -641930023715372771 and -6277833999987; +----------------------+------+ | id | b | +----------------------+------+ | 17804814049994178845 | 4 | | 17804814049994178846 | 5 | | 18446737795875551629 | 6 | +----------------------+------+ 3 rows in set (0.00 sec) root@localhost : test 09:34:45>select count(*) from t where id >= -641930023715372771 and id <= -6277833999987; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)
29，sort_buffer_size设置优化：http://www.mysqlperformanceblog.com/2007/08/18/how-fast-can-you-sort-data-with-mysql/
30，skip-character-set-client-handshake：连接MySQL时，会自动让客户端指定 character_set_server 设置的字符集，不管是否自己指定字符集
zhoujy@zhoujy:~$ mysql --default-character-set=gbk -s root@localhost : (none) 09:18:07>show variables like 'character%'; Variable_name Value character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server utf8 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ zhoujy@zhoujy:~$ mysql -s root@localhost : (none) 09:18:20>show variables like 'character%'; Variable_name Value character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server utf8 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/
31，binlog_checksum：MySQL5.6为主，5.5及更早的MySQL为从。需要把MySQL5.6的binlog_checksum设为none才可以进行复制，否则报错
Last_IO_Error: Got fatal error 1236 from master when reading data from Slave can not handle replication events with the checksum that master is configured to log
原因：MySQL5.6中binlog_checksum 默认设置的是 crc32。需要设置成为none。
32，MySQL5.5 错误日志里出现：Native table 'performance_schema'.test has the wrong structure
Native table 'performance_schema'.test has the wrong structure
……
……
原因是 PERFORMANCE_SCHEMA 存储引擎出问题，修复该问题只需要执行一次mysql_upgrade即可：
mysql_upgrade -uzjy -p -h192.168.1.32 --force
test.a OK
test.b OK
test.c OK
test.d OK
test.e OK
test.f OK
test.g OK
test.h OK
test.i OK
test.j OK
……
33：SET global slave_type_conversions=ALL_NON_LOSSY 。碰到主从同步失败，原因是在运行 pt-online-schema-change 进行DDL修改字段操作的时候，从的一个字段长度和主的字段长度不一致导致的。
主从同步失败的错误信息： Slave SQL: Column 31 of table 'test.tbname' cannot be converted from type 'varchar(765)' to type 'varchar(512) 以前测试的情况是在ROW模式下的复制才会有关系，而当前的binlog_format是 MIXED竟然也会出现。
在google的时候也发现该问题：http://bugs.mysql.com/bug.php?id=60784 ，http://bugs.mysql.com/bug.php?id=55709
解决办法：先stop slave，再通过设置slave_type_conversions=ALL_NON_LOSSY，最后再start slave 解决。
通过上面的问题可以得到一个经验：MySQL在做DDL并且主从切换（高可用）的时候，需要保证不要因为上面的原因而出现异常。
34：当查询的tmp_table中包含blob或text字段时，再小的临时表也会转成tmp_disk_tables。可以从show status like 'Created_tmp_disk_tables' 看到。原因是MEMORY引擎的临时表表不支持blob、text ，必须转成MyISAM。
35：慢查询统计：
按天： grep Time: mysql-slow-3306.log | awk '{print $3}'|awk '{a[$1]++}END{for (j in a) print j,": " a[j]}' | sort -nrk2 -t: 按分钟： grep Time: mysql-slow-3306.log | awk '{print $3,$4}'|awk -F : '{print $1,$2}' | awk -F : '{a[$1]++}END{for (j in a) print j,": " a[j]}' | sort -nrk2 -t: 按小时： grep Time: mysql-slow-3306.log | awk '{print $3,$4}'|awk -F : '{print $1}' | awk -F : '{a[$1]++}END{for (j in a) print j,": " a[j]}' | sort -nrk2 -t:
解释：
awk '{a[$1]++}END{for (j in a) print a[j],j}' a.txt
a[$1]++这里用到了awk的数组，数组a的下标为$1，并将相同的相加，会遍历a.txt $1
遍历完成后，通过END把后面的句子连起来
for (j in a) 是指打印数组a的下标，并定义下标为变量j
最后print a[j],j就是打印数组下标和数组，这样就相同的$1排重并计数
36：set autocommit=0是将本线程设置为非自动提交模式。在每个事务结束后，下个语句开始时自动新建一个事务，会隐含了一个begin操作。见 这里
37：字符集转换问题。字符集从GBK转换成UTF8，它们对中文字符串内部编码不一样，会导致按照中文字段排序出现不一致。所以在字符集转换的时候要搞清楚索引大小限制外，还要搞清楚有没有用字符串排序的字段。
表结构 jinyizhou@localhost : test 04:00:22>show create table tmp_a\G; *************************** 1. row *************************** Table: tmp_a Create Table: CREATE TABLE `tmp_a` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, `address` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified jinyizhou@localhost : test 04:11:55>show create table tmp_b\G; *************************** 1. row *************************** Table: tmp_b Create Table: CREATE TABLE `tmp_b` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, `address` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=gbk 1 row in set (0.00 sec) 表数据 jinyizhou@localhost : test 03:57:36>select * from tmp_a; +----+-----------+-----------------------------------------------------+ | id | name | address | +----+-----------+-----------------------------------------------------+ | 1 | 我们 | 我们的啊岁大的期望方式地方 | | 2 | 你们 | 啊说的前往俄去打扫打扫烦的事 | | 3 | 左左左 | 撒旦发射点发我往俄去打扫打扫烦的事 | | 4 | 阿斯顿 | 你好好哟亲去打扫打扫烦的事 | | 5 | 周周周 | 可是却是的打扫打扫烦的事 | +----+-----------+-----------------------------------------------------+ 5 rows in set (0.01 sec) jinyizhou@localhost : test 03:57:47>select * from tmp_b; +----+-----------+-----------------------------------------------------+ | id | name | address | +----+-----------+-----------------------------------------------------+ | 1 | 我们 | 我们的啊岁大的期望方式地方 | | 2 | 你们 | 啊说的前往俄去打扫打扫烦的事 | | 3 | 左左左 | 撒旦发射点发我往俄去打扫打扫烦的事 | | 4 | 阿斯顿 | 你好好哟亲去打扫打扫烦的事 | | 5 | 周周周 | 可是却是的打扫打扫烦的事 | +----+-----------+-----------------------------------------------------+ 5 rows in set (0.00 sec) 排序结果 jinyizhou@localhost : test 03:57:49>select * from tmp_a order by name; #排序出现不一致 +----+-----------+-----------------------------------------------------+ | id | name | address | +----+-----------+-----------------------------------------------------+ | 2 | 你们 | 啊说的前往俄去打扫打扫烦的事 | | 5 | 周周周 | 可是却是的打扫打扫烦的事 | | 3 | 左左左 | 撒旦发射点发我往俄去打扫打扫烦的事 | | 1 | 我们 | 我们的啊岁大的期望方式地方 | | 4 | 阿斯顿 | 你好好哟亲去打扫打扫烦的事 | +----+-----------+-----------------------------------------------------+ 5 rows in set (0.00 sec) jinyizhou@localhost : test 03:57:51>select * from tmp_b order by name;#排序出现不一致 +----+-----------+-----------------------------------------------------+ | id | name | address | +----+-----------+-----------------------------------------------------+ | 4 | 阿斯顿 | 你好好哟亲去打扫打扫烦的事 | | 2 | 你们 | 啊说的前往俄去打扫打扫烦的事 | | 1 | 我们 | 我们的啊岁大的期望方式地方 | | 5 | 周周周 | 可是却是的打扫打扫烦的事 | | 3 | 左左左 | 撒旦发射点发我往俄去打扫打扫烦的事 | +----+-----------+-----------------------------------------------------+ 原因： jinyizhou@localhost : test 04:11:59>select *,hex(name) from tmp_a; +----+-----------+-----------------------------------------------------+--------------------+ | id | name | address | hex(name) | +----+-----------+-----------------------------------------------------+--------------------+ | 1 | 我们 | 我们的啊岁大的期望方式地方 | E68891E4BBAC | | 2 | 你们 | 啊说的前往俄去打扫打扫烦的事 | E4BDA0E4BBAC | | 3 | 左左左 | 撒旦发射点发我往俄去打扫打扫烦的事 | E5B7A6E5B7A6E5B7A6 | | 4 | 阿斯顿 | 你好好哟亲去打扫打扫烦的事 | E998BFE696AFE9A1BF | | 5 | 周周周 | 可是却是的打扫打扫烦的事 | E591A8E591A8E591A8 | +----+-----------+-----------------------------------------------------+--------------------+ 5 rows in set (0.00 sec) jinyizhou@localhost : test 04:13:30>select *,hex(name) from tmp_b; +----+-----------+-----------------------------------------------------+--------------+ | id | name | address | hex(name) | +----+-----------+-----------------------------------------------------+--------------+ | 1 | 我们 | 我们的啊岁大的期望方式地方 | CED2C3C7 | | 2 | 你们 | 啊说的前往俄去打扫打扫烦的事 | C4E3C3C7 | | 3 | 左左左 | 撒旦发射点发我往俄去打扫打扫烦的事 | D7F3D7F3D7F3 | | 4 | 阿斯顿 | 你好好哟亲去打扫打扫烦的事 | B0A2CBB9B6D9 | | 5 | 周周周 | 可是却是的打扫打扫烦的事 | D6DCD6DCD6DC | +----+-----------+-----------------------------------------------------+--------------+ 由于字符集不一样，所以他们的编码不一样，导致按照name（中文）排序出现的结果不一致。数字和字母没有该问题。
38：全角、半角下的gbk和utf8，在大小写不区分的校验规则下，gbk可以在唯一约束下插入全角大小写，utf8则不行。在为表做gbk转换成utf8/utf8mb4操作时，特别要注意。
dba@192.168.200.94 : jute 04:30:18>create table tmp_gbk(name varchar(30))default charset gbk; Query OK, 0 rows affected (0.05 sec) dba@192.168.200.94 : jute 04:30:21>create table tmp_utf8(name varchar(30))default charset utf8; Query OK, 0 rows affected (0.13 sec) dba@192.168.200.94 : jute 04:30:29>alter table tmp_gbk add unique key uk_name(name); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 dba@192.168.200.94 : jute 04:30:47>alter table tmp_utf8 add unique key uk_name(name); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 dba@192.168.200.94 : jute 04:30:53>insert into tmp_gbk select 'abc'; Query OK, 1 row affected (0.18 sec) Records: 1 Duplicates: 0 Warnings: 0 dba@192.168.200.94 : jute 04:33:21>insert into tmp_gbk select 'ａｂｃ'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 dba@192.168.200.94 : jute 04:33:32>insert into tmp_utf8 select 'abc'; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 dba@192.168.200.94 : jute 04:33:48>insert into tmp_utf8 select 'ａｂｃ'; Query OK, 1 row affected (0.20 sec) Records: 1 Duplicates: 0 Warnings: 0 dba@192.168.200.94 : jute 04:33:53>select * from tmp_gbk; +-----------+ | name | +-----------+ | abc | | ａｂｃ | +-----------+ 2 rows in set (0.01 sec) dba@192.168.200.94 : jute 04:34:17>select * from tmp_utf8; +-----------+ | name | +-----------+ | abc | | ａｂｃ | +-----------+ 2 rows in set (0.00 sec) ###重点在这里 dba@192.168.200.94 : jute 04:34:20>insert into tmp_gbk select 'ABC'; ERROR 1062 (23000): Duplicate entry 'ABC' for key 'uk_name' dba@192.168.200.94 : jute 04:35:07>insert into tmp_gbk select 'ＡＢＣ'; #插入成功 Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 dba@192.168.200.94 : jute 04:35:14>insert into tmp_utf8 select 'ABC'; ERROR 1062 (23000): Duplicate entry 'ABC' for key 'uk_name' dba@192.168.200.94 : jute 04:35:22>insert into tmp_utf8 select 'ＡＢＣ'; ERROR 1062 (23000): Duplicate entry 'ＡＢＣ' for key 'uk_name' #插入失败 dba@192.168.200.94 : jute 04:35:28>select * from tmp_gbk; +-----------+ | name | +-----------+ | abc | | ＡＢＣ | | ａｂｃ | +-----------+ 3 rows in set (0.02 sec) dba@192.168.200.94 : jute 04:42:22>select * from tmp_utf8; +-----------+ | name | +-----------+ | abc | | ａｂｃ | +-----------+ 2 rows in set (0.00 sec)
39：通过MySQL5.6 performance_schema下的一些表进行统计说明：具体的一些说明信息见：说明1、说明2、说明3。整理了一下，这里再根据自己数据库的实际情况进行简单说明：可以看这篇文章的说明。
40，因为Percona MySQL的慢查询日志和社区版本的MySQL慢查询日志格式不一样，想要让mysqlsla（慢查询分析工具）支持Percona MySQL需要把其慢查询先转换一下：
cat mysql-slow.log | sed '/^# Schema:/d;/^# Bytes_sent:/d' > slow.log
删除Schema：开头的行和Bytes_sent：开头的行，也可以用其他方法删除。他们慢查询的区别：
社区版： # Time: 151207 15:23:08 # User@Host: zjy[zjy] @ [192.168.100.249] # Query_time: 1.192820 Lock_time: 0.000200 Rows_sent: 20 Rows_examined: 141760 SET timestamp=1449472988; select count(*) as y0_ from fans_ttt this_ where this_.teamId=8 and this_.group_id=0 and this_.is_fans=1; Percona版： # Time: 151208 14:20:42 # User@Host: dchat_data[dchat_data] @ [192.168.100.220] Id: 3523109 # Schema: dchat_main Last_errno: 0 Killed: 0 ---新增 # Query_time: 0.973749 Lock_time: 0.000132 Rows_sent: 1 Rows_examined: 3227053 Rows_affected: 0 # Bytes_sent: 64 ---新增 SET timestamp=1449555642;
41，read committed的事务隔离级别下，Mixed的binlog模式会以Row格式写到写到binlog中，详情见http://www.cnblogs.com/zhoujinyi/p/5436250.html。
42，character-set-client-handshake，该参数的作用是在连接MySQL时，是否忽视其指定的字符集，使用数据库默认（character-set-server）的字符集。用 --skip-character-set-client-handshake来指定忽视。如：在配置文件的mysqld选项组下面添加：
skip-character-set-client-handshake
之后通过mysql连接指定任何字符集（--default-character-set=utf8/gbk/latin1）都无效，数据库只是用（character-set-server）设置的字符集。可以有效的避免客户端程序误操作，使用其他字符集连接进来并写入数据，从而引发乱码问题。
43，MySQL 5.7内存使用监控（OOM）。
①：通过valgrind查看内存溢出的问题：
valgrind --tool=massif --massif-out-file=/tmp/m.out /usr/sbin/mysqld --defaults-file=/etc/mysql/my.cnf --user=mysql
②：可以通过 performance_schema里的表定位：
MySQL 5.7的库performance_schema新增了以下这几张表，用于从各维度查看内存的消耗： memory_summary_by_account_by_event_name memory_summary_by_host_by_event_name memory_summary_by_thread_by_event_name memory_summary_by_user_by_event_name memory_summary_global_by_event_name 简单来说，就是可以根据用户、主机、线程、账号、全局的维度对内存进行监控。同时库sys也就这些表做了进一步的格式化，可以使得用户非常容易的观察到每个对象的内存开销，默认情况下performance_schema只对performance_schema进行了内存开销的统计。但是在对OOM进行诊断时，需要对所有可能的对象进行内存监控。因此，还需要做下面的设置： mysql> update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%';
但是这种在线打开内存统计的方法仅对之后新增的内存对象有效
如想要对全局生命周期中的对象进行内存统计，必须在配置文件中进行设置，然后重启：
[mysqld]
performance-schema-instrument='memory/%=COUNTED'
查看消耗的内存(需要分配的内存)：
mysql> select event_name,SUM_NUMBER_OF_BYTES_ALLOC/1024/1024 from memory_summary_global_by_event_name order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 10; +-----------------------------------------------------------------------------+-------------------------------------+ | event_name | SUM_NUMBER_OF_BYTES_ALLOC/1024/1024 | +-----------------------------------------------------------------------------+-------------------------------------+ | memory/innodb/buf_buf_pool | 533.00000000 | | memory/mysys/KEY_CACHE | 64.00164795 | | memory/innodb/hash0hash | 52.39000702 | | memory/sql/XID | 19.00152588 | | memory/innodb/log0log | 16.08451843 | | memory/performance_schema/events_statements_history_long | 13.65661621 | | memory/performance_schema/events_statements_history_long.tokens | 9.76562500 | | memory/performance_schema/events_statements_history_long.sqltext | 9.76562500 | | memory/performance_schema/events_statements_summary_by_digest.tokens | 9.76562500 | | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 9.02929688 | +-----------------------------------------------------------------------------+-------------------------------------+
44， mysql_config_editor：实现MySQL无明文密码登陆，5.6以上支持。
#在5.6以上版本明文密码登陆mysql会报一个警告：Warning: Using a password on the command line interface can be insecure.
使用者可以在特定目录指定配置文件，在[client]选项组里输入登陆密码信息，相对来说也不安全，mysql_config_editor很好的解决里整个问题。 说明：生成文件.mylogin.cnf保存在~/中 1:创建，注意：各个--login-path创建的方式不能使用同一个账号！ ①：创建一个本地连接：path_name mysql_config_editor set --login-path=local --host=127.0.0.1 --user=zjy --password Enter password: ②：创建一个远程连接：可以远程到任意一台123网段的数据库 mysql_config_editor set --login-path=remote --host='192.168.123.%' --user=dba --password Enter password 2:使用 对应上面的①：mysql --login-path=local --default-character-set=utf8 --port=3306 对应上面的②：mysql --login-path=remote --host=192.168.123.3 --default-character-set=utf8 --port=3307 #指定IP 对mysqladmin也通用：
mysqladmin --login-path=remote --host=192.168.200.252 ext -i1 | awk '/Queries/{q=$4-qp;qp=$4} /Threads_connected/{tc=$4} /Threads_running/{printf "%5d %5d %5d\n",q,tc,$4}'
3:查看有那些login-path: ①：mysql_config_editor print --all [local] user = zjy password = ***** host = 127.0.0.1 [remote] user = dba password = ***** host = 192.168.123.% ②：mysql_config_editor print --login-path=local [local] user = zjy password = ***** host = 127.0.0.1 4:删除login-path mysql_config_editor remove --login-path=remote 5：其他选项： 更多的选项：--socket、--user等见官网说明
45，查看那些事务长时间没有提交：如10秒
select a.trx_mysql_thread_id,b.TIME from information_schema.INNODB_TRX a inner join information_schema.processlist b on a.trx_mysql_thread_id=b.id where a.trx_state='RUNNING' and b.time>10 and b.COMMAND='Sleep';
gdb -p 10162 -ex "set max_connections=2000" -batch
~~~~~~~~~~~~~~~
万物之中,希望至美
~~~~~~~~~~~~~~~
