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的结果为:
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表不会。
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';
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模式不被支持。
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。
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的所有信息。
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)的表定义,否则建表出错。原因见:这里
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文件的大小。
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
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 | +----+-------------+-------------+------+------------------------------------------+-------------------+---------+-------+-------+-------------+