MySQL各種Tips(不定時更新)
1，into outfile 生成sql：一般都是生成文本或者其他形式的文件，現(xiàn)在需要生成sql形式的文件。
select concat('insert into tab_name(col1,col2,col3) values(',col1,',','\'',col2,'\'',',','\'',col3,'\'',')') into outfile '/tmp/tt.txt' from tt;
出來tt.txt的結(jié)果為：
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中l(wèi)ike語句及相關(guān)優(yōu)化器tips： 利用INNODB特性，來優(yōu)化like '%%'的方法。
3，Mysqldump輸出方式和進度報告： 用這個mysqldump可以直接在備份的時候打印出進度。注意，需要有可執(zhí)行權(quán)限和64位系統(tǒng)。
./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顯示的狀態(tài)里面，update表示正在insert ，updating表示正在delete，Updating才是表示正在update。
5，Innodb表，當(dāng)表里面有100行記錄，有自增ID。清除表里的數(shù)據(jù)之后，重啟數(shù)據(jù)庫。發(fā)現(xiàn)表的自增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 的棄用：當(dāng)執(zhí)行計劃出現(xiàn)索引合并時，可以修改optimizer_switch的一個狀態(tài)，棄用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提示符設(shè)置：prompt
[client] #在client組下面設(shè)置 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個字節(jié)才能建立成功，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顯示的狀態(tài)里面：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" 生成結(jié)果導(dǎo)入指定文件時，若要同時顯示語句本身，-v; 若要增加查詢結(jié)果行數(shù)，加-vv; 若要增加執(zhí)行時間, 加-vvv。
mysql -e "select user,host from mysql.user" -v/-vv/-vvv > 1.txt
15，myisamchk使用注意事項：當(dāng)一張MYISAM表壞了，索引文件大于sort_buffer_size，要是用myisamchk去修復(fù)，會報錯，需要手動指定--sort_buffer_size。大小最好設(shè)置大于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個字節(jié)，最大占用的字節(jié)數(shù)為4。目前這個字段主要應(yīng)用在（Emoji 表情需要4個字節(jié)）讓Mysql支持Emoji表情 ，utf8mb4兼容utf8（1~3字節(jié)），且比utf8能表示更多的字符。什么時候需要才用utf8mb4，否則只是浪費空間（比如utf8占用3個字節(jié)，10個漢字占用30個字節(jié)。這時候改成utf8mb4也只有30個，物理上占用的空間一樣，但是在申請內(nèi)存的時候，utf8mb4會多于utf8 1個字節(jié)）。對于一個BMP字符（<=3字節(jié)），utf8和utf8mb4的有相同的存儲特性：相同的代碼值，相同長度，相同的編碼；但utf8mb4范圍更大。
適用范圍：存類似emoji 這種類型的數(shù)據(jù)，是因為utf8滿足不了，才用utf8mb4的。能用utf8的都能用utf8mb4，而且占用的也只是3個字節(jié)。對于哪些需要4個字節(jié)的，這個就用到了utf8mb4自己的特點。要是不在乎空間浪費的話，utf8mb4可以全部替換掉utf8，擔(dān)心的話，則按照需要分配字符集（表，字段）。
17，在修復(fù)Innodb表的時候需要注意：innodb_purge_threads 和 innodb_force_recovery一起設(shè)置的時候需要注意：innodb_purge_threads=1時，innodb_force_recovery不能大于1（可以等于1）；當(dāng)innodb_purge_threads=0時，innodb_force_recovery沒有限制。
18，數(shù)據(jù)庫中的加鎖協(xié)議，分為樂觀/悲觀兩類：樂觀加鎖，操作記錄時不主動加鎖，由后續(xù)沖突者負(fù)責(zé)加鎖；悲觀加鎖，操作記錄時主動加鎖。樂觀加鎖適用于并發(fā)沖突較小時，減少了加鎖開銷；而悲觀加鎖適用于并發(fā)沖突較大時，簡化操作流程。以InnoDB為例，Update采用了悲觀加鎖協(xié)議，Insert則采用了樂觀加鎖。來自這里
19，Engine=innodb，當(dāng)table.frm文件丟失時，drop database會出現(xiàn)兩種情況： 1）version=5.1,報ERROR 1010 (HY000): Error dropping database (can't rmdir './D1', errno: 39) ；2）version=5.5,正常執(zhí)行。根源在innobase_drop_database函數(shù)的調(diào)用順序的不同。而engine=MyISAM 時，5.1,5.5都報39錯誤。
20，為什么正則表達(dá)式中/d比[0-9]效率差？ /d匹配的是Unicode，所以相當(dāng)于要篩選各種語言里的數(shù)字字符，而[0-9]只匹配這是個數(shù)字。
21，5.6之前add foreign key 還是不能使用fast-index-creation. 5.6之后支持，但需要在執(zhí)行加外鍵之前先 set foreign_key_checks=0。
22，STATEMENT下5.1到5.5復(fù)制的注意事項：5.1 無符號的整數(shù)類型，如果相減得到負(fù)數(shù)，mysql會自動轉(zhuǎn)化為最大的正數(shù)；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創(chuàng)建PK一定是copy table的方式，是因為整個過程都持有metadata lock的排他鎖。當(dāng)設(shè)置old_alter_table=1，所有的DDL都必須copy table，copy table的過程中只持有MDL的共享鎖，就不會阻塞讀了。詳情見：這里和這里；另外，在5.6版本之前，old_alter_table 還有另一個用處：就是對表有重復(fù)數(shù)據(jù)的字段添加唯一索引的時候報錯，則可以設(shè)置為1，詳情見：這里 和 這里，但出現(xiàn)的問題是：設(shè)置old_alter_table=ON，會繞過innodb_plugin的alter table快速建索引不拷貝表的優(yōu)化，會出現(xiàn)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}'
第一列：每秒查詢量 第二列：鏈接數(shù) 第三列：當(dāng)前執(zhí)行的鏈接數(shù) 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
每秒慢查詢的數(shù)量：
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:要排序，需要把所有的結(jié)果取出來排序，再取前N條，不會提前終止。2：不排序，全表隨機掃描記錄，只需要掃描取出前N條記錄就終止了，不會繼續(xù)掃描。相比之下，2比1要快很多，因為掃描的行數(shù)少很多。
26，關(guān)于死鎖：在防止死鎖（deadlock）方面，表鎖比行鎖更有優(yōu)勢。使用表鎖的時候，死鎖不會發(fā)生，因為服務(wù)器可以通過查看語句來檢測需要的數(shù)據(jù)表，并提前鎖定它們。而InnoDB會發(fā)生死鎖，因為存儲引擎沒有在事務(wù)開始的時候分配所有鎖，而是在事務(wù)處理的過程中，當(dāng)檢測到需要鎖的時候才分配。這就可能出現(xiàn)兩個語句獲取了鎖，接著試圖進一步獲取鎖（需要多個鎖），但是這些鎖卻被對方保持著，等待對方釋放。其結(jié)果是每個客戶端都擁有一個鎖，同時還需要利用其它的客戶端擁有的鎖才能繼續(xù)執(zhí)行。這會導(dǎo)致死鎖，服務(wù)器必須終止其中一個事務(wù)。
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 | +----+-------------+-------------+-------+------------------------------------------+------------------------+---------+------+-------+-------------+ 執(zhí)行計劃里的Type為range，但是in屬于多個等值條件，可以繼續(xù)用第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...的區(qū)別：數(shù)學(xué)符號的比較按照正常的大小；而between...and 則按照無符號即（singed）進行比較，在字段里面定義了unsigned 的類型需要注意這個限制，因為需要進行轉(zhuǎn)換（cast）成signed進行比較，所以操作signed的范圍大小會出現(xiàn)問題。
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設(shè)置優(yōu)化：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 設(shè)置的字符集，不管是否自己指定字符集
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設(shè)為none才可以進行復(fù)制，否則報錯
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 默認(rèn)設(shè)置的是 crc32。需要設(shè)置成為none。
32，MySQL5.5 錯誤日志里出現(xiàn)：Native table 'performance_schema'.test has the wrong structure
Native table 'performance_schema'.test has the wrong structure
……
……
原因是 PERFORMANCE_SCHEMA 存儲引擎出問題，修復(fù)該問題只需要執(zhí)行一次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修改字段操作的時候，從的一個字段長度和主的字段長度不一致導(dǎo)致的。
主從同步失敗的錯誤信息： Slave SQL: Column 31 of table 'test.tbname' cannot be converted from type 'varchar(765)' to type 'varchar(512) 以前測試的情況是在ROW模式下的復(fù)制才會有關(guān)系，而當(dāng)前的binlog_format是 MIXED竟然也會出現(xiàn)。
在google的時候也發(fā)現(xiàn)該問題：http://bugs.mysql.com/bug.php?id=60784 ，http://bugs.mysql.com/bug.php?id=55709
解決辦法：先stop slave，再通過設(shè)置slave_type_conversions=ALL_NON_LOSSY，最后再start slave 解決。
通過上面的問題可以得到一個經(jīng)驗：MySQL在做DDL并且主從切換（高可用）的時候，需要保證不要因為上面的原因而出現(xiàn)異常。
34：當(dāng)查詢的tmp_table中包含blob或text字段時，再小的臨時表也會轉(zhuǎn)成tmp_disk_tables。可以從show status like 'Created_tmp_disk_tables' 看到。原因是MEMORY引擎的臨時表表不支持blob、text ，必須轉(zhuǎn)成MyISAM。
35：慢查詢統(tǒng)計：
按天： 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的數(shù)組，數(shù)組a的下標(biāo)為$1，并將相同的相加，會遍歷a.txt $1
遍歷完成后，通過END把后面的句子連起來
for (j in a) 是指打印數(shù)組a的下標(biāo)，并定義下標(biāo)為變量j
最后print a[j],j就是打印數(shù)組下標(biāo)和數(shù)組，這樣就相同的$1排重并計數(shù)
36：set autocommit=0是將本線程設(shè)置為非自動提交模式。在每個事務(wù)結(jié)束后，下個語句開始時自動新建一個事務(wù)，會隱含了一個begin操作。見 這里
37：字符集轉(zhuǎn)換問題。字符集從GBK轉(zhuǎn)換成UTF8，它們對中文字符串內(nèi)部編碼不一樣，會導(dǎo)致按照中文字段排序出現(xiàn)不一致。所以在字符集轉(zhuǎn)換的時候要搞清楚索引大小限制外，還要搞清楚有沒有用字符串排序的字段。
表結(jié)構(gòu) 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) 表數(shù)據(jù) jinyizhou@localhost : test 03:57:36>select * from tmp_a; +----+-----------+-----------------------------------------------------+ | id | name | address | +----+-----------+-----------------------------------------------------+ | 1 | 我們 | 我們的啊歲大的期望方式地方 | | 2 | 你們 | 啊說的前往俄去打掃打掃煩的事 | | 3 | 左左左 | 撒旦發(fā)射點發(fā)我往俄去打掃打掃煩的事 | | 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 | 左左左 | 撒旦發(fā)射點發(fā)我往俄去打掃打掃煩的事 | | 4 | 阿斯頓 | 你好好喲親去打掃打掃煩的事 | | 5 | 周周周 | 可是卻是的打掃打掃煩的事 | +----+-----------+-----------------------------------------------------+ 5 rows in set (0.00 sec) 排序結(jié)果 jinyizhou@localhost : test 03:57:49>select * from tmp_a order by name; #排序出現(xiàn)不一致 +----+-----------+-----------------------------------------------------+ | id | name | address | +----+-----------+-----------------------------------------------------+ | 2 | 你們 | 啊說的前往俄去打掃打掃煩的事 | | 5 | 周周周 | 可是卻是的打掃打掃煩的事 | | 3 | 左左左 | 撒旦發(fā)射點發(fā)我往俄去打掃打掃煩的事 | | 1 | 我們 | 我們的啊歲大的期望方式地方 | | 4 | 阿斯頓 | 你好好喲親去打掃打掃煩的事 | +----+-----------+-----------------------------------------------------+ 5 rows in set (0.00 sec) jinyizhou@localhost : test 03:57:51>select * from tmp_b order by name;#排序出現(xiàn)不一致 +----+-----------+-----------------------------------------------------+ | id | name | address | +----+-----------+-----------------------------------------------------+ | 4 | 阿斯頓 | 你好好喲親去打掃打掃煩的事 | | 2 | 你們 | 啊說的前往俄去打掃打掃煩的事 | | 1 | 我們 | 我們的啊歲大的期望方式地方 | | 5 | 周周周 | 可是卻是的打掃打掃煩的事 | | 3 | 左左左 | 撒旦發(fā)射點發(fā)我往俄去打掃打掃煩的事 | +----+-----------+-----------------------------------------------------+ 原因： jinyizhou@localhost : test 04:11:59>select *,hex(name) from tmp_a; +----+-----------+-----------------------------------------------------+--------------------+ | id | name | address | hex(name) | +----+-----------+-----------------------------------------------------+--------------------+ | 1 | 我們 | 我們的啊歲大的期望方式地方 | E68891E4BBAC | | 2 | 你們 | 啊說的前往俄去打掃打掃煩的事 | E4BDA0E4BBAC | | 3 | 左左左 | 撒旦發(fā)射點發(fā)我往俄去打掃打掃煩的事 | 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 | 左左左 | 撒旦發(fā)射點發(fā)我往俄去打掃打掃煩的事 | D7F3D7F3D7F3 | | 4 | 阿斯頓 | 你好好喲親去打掃打掃煩的事 | B0A2CBB9B6D9 | | 5 | 周周周 | 可是卻是的打掃打掃煩的事 | D6DCD6DCD6DC | +----+-----------+-----------------------------------------------------+--------------+ 由于字符集不一樣，所以他們的編碼不一樣，導(dǎo)致按照name（中文）排序出現(xiàn)的結(jié)果不一致。數(shù)字和字母沒有該問題。
38：全角、半角下的gbk和utf8，在大小寫不區(qū)分的校驗規(guī)則下，gbk可以在唯一約束下插入全角大小寫，utf8則不行。在為表做gbk轉(zhuǎn)換成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下的一些表進行統(tǒng)計說明：具體的一些說明信息見：說明1、說明2、說明3。整理了一下，這里再根據(jù)自己數(shù)據(jù)庫的實際情況進行簡單說明：可以看這篇文章的說明。
40，因為Percona MySQL的慢查詢?nèi)罩竞蜕鐓^(qū)版本的MySQL慢查詢?nèi)罩靖袷讲灰粯樱胍宮ysqlsla（慢查詢分析工具）支持Percona MySQL需要把其慢查詢先轉(zhuǎn)換一下：
cat mysql-slow.log | sed '/^# Schema:/d;/^# Bytes_sent:/d' > slow.log
刪除Schema：開頭的行和Bytes_sent：開頭的行，也可以用其他方法刪除。他們慢查詢的區(qū)別：
社區(qū)版： # 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的事務(wù)隔離級別下，Mixed的binlog模式會以Row格式寫到寫到binlog中，詳情見http://www.rzrgm.cn/zhoujinyi/p/5436250.html。
42，character-set-client-handshake，該參數(shù)的作用是在連接MySQL時，是否忽視其指定的字符集，使用數(shù)據(jù)庫默認(rèn)（character-set-server）的字符集。用 --skip-character-set-client-handshake來指定忽視。如：在配置文件的mysqld選項組下面添加：
skip-character-set-client-handshake
之后通過mysql連接指定任何字符集（--default-character-set=utf8/gbk/latin1）都無效，數(shù)據(jù)庫只是用（character-set-server）設(shè)置的字符集。可以有效的避免客戶端程序誤操作，使用其他字符集連接進來并寫入數(shù)據(jù)，從而引發(fā)亂碼問題。
43，MySQL 5.7內(nèi)存使用監(jiān)控（OOM）。
①：通過valgrind查看內(nèi)存溢出的問題：
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新增了以下這幾張表，用于從各維度查看內(nèi)存的消耗： 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 簡單來說，就是可以根據(jù)用戶、主機、線程、賬號、全局的維度對內(nèi)存進行監(jiān)控。同時庫sys也就這些表做了進一步的格式化，可以使得用戶非常容易的觀察到每個對象的內(nèi)存開銷，默認(rèn)情況下performance_schema只對performance_schema進行了內(nèi)存開銷的統(tǒng)計。但是在對OOM進行診斷時，需要對所有可能的對象進行內(nèi)存監(jiān)控。因此，還需要做下面的設(shè)置： mysql> update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%';
但是這種在線打開內(nèi)存統(tǒng)計的方法僅對之后新增的內(nèi)存對象有效
如想要對全局生命周期中的對象進行內(nèi)存統(tǒng)計，必須在配置文件中進行設(shè)置，然后重啟：
[mysqld]
performance-schema-instrument='memory/%=COUNTED'
查看消耗的內(nèi)存(需要分配的內(nèi)存)：
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：實現(xiàn)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:創(chuàng)建，注意：各個--login-path創(chuàng)建的方式不能使用同一個賬號！ ①：創(chuàng)建一個本地連接：path_name mysql_config_editor set --login-path=local --host=127.0.0.1 --user=zjy --password Enter password: ②：創(chuàng)建一個遠(yuǎn)程連接：可以遠(yuǎn)程到任意一臺123網(wǎng)段的數(shù)據(jù)庫 mysql_config_editor set --login-path=remote --host='192.168.123.%' --user=dba --password Enter password 2:使用 對應(yīng)上面的①：mysql --login-path=local --default-character-set=utf8 --port=3306 對應(yīng)上面的②：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等見官網(wǎng)說明
45，查看那些事務(wù)長時間沒有提交：如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
47，MySQL權(quán)限列表對應(yīng)操作命令
~~~~~~~~~~~~~~~
萬物之中,希望至美
~~~~~~~~~~~~~~~
浙公網(wǎng)安備 33010602011771號