meisw's blog

mysql myisam转innodb的2种方法

一，用alter table来实现。
mysql> alter table merchant type=innodb;

ALTER TABLE  `block_view` ENGINE = INNODB;

二，导出数据，重新建表
1，导出表结构以及数据，删除老表，重新建表
查看复制打印?
//方法1，我们可以把把创建这个表的sql拷贝出来，把ENGINE=MyISAM换成，ENGINE=INNODB 
mysql> show create table merchant\G; 
*************************** 1. row *************************** 
 Table: merchant 
Create Table: CREATE TABLE `merchant` ( 
 `btid` int(5) unsigned zerofill NOT NULL auto_increment, 
 `btname` varchar(100) NOT NULL, 
 `btnote` text, 
 PRIMARY KEY  (`btid`), 
 UNIQUE KEY `btname` (`btname`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 
1 row in set (0.00 sec) 
 
//方法2，直接导出表结构，在文件里面替换一个myisam 
mysqldump -udba -p -d test merchant >/tmp/test/mytest/merchant.sql; 
2，导出数据
mysqldump -udba -p --no-create-info test merchant >/tmp/test/mytest/merchant_data.sql; 
3，删除老表，重新建表，导入数据，这个就不说了，linux 简单的mysql备份和导入，以及文件的备份和导入

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /www/wdlinux/mysql-5.5.30/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/wdlinux/mysql-5.5.30/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M  //启用这个,就不能启动
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

--------

要把ib_log*的文件删除,再启动就正常了

MySQL may use temporary tables during query execution. Ideally you would want to avoid this, since its an expensive and slow operation. It can be avoided by optimizing queries. Sometimes it can’t be completely avoided – in that case you want to make sure the temporary table is created as a “memory” storage engine table, since its very fast, as it is never written to disk and remains, as the name states, in memory. But, as the manual explains, there are some conditions, such as TEXT/BLOB columns, or a combination of GROUP BY/ORDER BY clauses that makes MySQL write the temporary table to disk as a MyISAM table. One can spot these queries by the EXPLAIN output:
[...] Using where; Using temporary; Using filesort
In that case performance depends on disk I/O speed. If there are multiple similar queries running simultaneously, they try to read/write a lot of information to the disk, and will become extremely slow.

Solution? TMPFS!

tmpfs is a filesystem, that resides in RAM/Swap, so if your server has enough available RAM, files written there will bypass disk I/O completely, and will perform significantly faster.

Now, “High Performance MySQL, Second Edition” claims that this solution is still not as good as a MEMORY table, since it requires MySQL to use some expensive OS calls to write & read the temporary table, but it is still faster than the disk based temporary table.

To set it up, just mount a tmpfs system on an empty directory (you should also add this to fstab):
mount tmpfs /tmpfs -t tmpfs
and edit my.cnf to make MySQL use that directory as a temporary directory:
tmpdir = /tmpfs
Be careful though, there is a bug in some versions that prevents this from working properly.

For more information, see this blog.

今天在为数据库中的某两个字段设置unique索引的时候，出现了Specified key was too long; max key length is 1000 bytes错误，经过查询才知道，是Mysql的字段设置的太长了，于是我把这两个字段的长度改了一下就好了。

建立索引时，数据库计算key的长度是累加所有Index用到的字段的char长度后再按下面比例乘起来不能超过限定的key长度1000：
latin1 = 1 byte = 1 character
uft8 = 3 byte = 1 character
gbk = 2 byte = 1 character
举例能看得更明白些，以GBK为例：
CREATE UNIQUE INDEX `unique_record` ON reports (`report_name`, `report_client`, `report_city`);
其中report_name varchar(200), report_client varchar(200), report_city varchar(200)
(200 + 200 +200) * 2 = 1200 > 1000，所有就会报1071错误，只要将report_city改为varchar(100)那么索引就能成功建立。
如果表是UTF8字符集，那索引还是建立不了。

什么是索引？

CREATE TABLE test (  id INT NOT NULL,    
                   last_name CHAR(30) NOT NULL, 
                    first_name CHAR(30) NOT NULL,
                    PRIMARY KEY (id), 
                    INDEX name (last_name,first_name));

SELECT * FROM test WHERE last_name='Widenius'; 
SELECT * FROM test    WHERE last_name='Widenius' AND first_name='Michael';
SELECT * FROM test    WHERE last_name='Widenius'    AND (first_name='Michael' OR first_name='Monty'); 
SELECT * FROM test    WHERE last_name='Widenius'    AND first_name >='M' AND first_name < 'N';

SELECT * FROM test WHERE first_name='Michael'; 
SELECT * FROM test    WHERE last_name='Widenius' OR first_name='Michael';

 MYSQL如果使用索引(from mysql reference)

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

如果列不构成索引最左面的前缀，MySQL不能使用局部索引。假定有下面显示的SELECT语句： 

SELECT * FROM tbl_name WHERE col1=val1;SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
SELECT * FROM tbl_name WHERE col2=val2;SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

也可以在表达式通过=、>、>=、<、<=或者BETWEEN操作符使用B-树索引进行列比较。如果LIKE的参数是一个不以通配符开头的常量字符串，索引也可以用于LIKE比较。例如，下面的SELECT语句使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

... WHERE index_part1=1 AND index_part2=2 AND other_column=3    
/* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2    
/* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5    
/* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

/* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2     
/*  Index is not used in both parts of the WHERE clause  */
... WHERE index=1 OR A=10     
/* No index spans all rows  */
... WHERE index_part1=1 OR index_part2=10

SELECT * from tbl_name WHERE primary_key=1； 
SELECT * from tbl_name WHERE primary_key_part1=1 and  primary_key_part2=2；

SELECT * FROM ref_table WHERE key_column=expr; 
SELECT * FROM ref_table,other_table  WHERE ref_table.key_column=other_table.column; 
SELECT * FROM ref_table,other_table  WHERE ref_table.key_column_part1=other_table.column    AND 
ref_table.key_column_part2=1;

at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
 at java.lang.Thread.run(Thread.java:534)
Caused by: com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Broken pipe

STACKTRACE:

java.net.SocketException: Broken pipe
 at java.net.SocketOutputStream.socketWrite0(Native Method)
 at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
 at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
 at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:66)
 at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:124)

 ... 40 more

配置：mysql4.0.1alpha，是虚拟主机上面的mysql，具体配置不明

jdbc：mysql-connector-java-3.1.10

连接池配置：

 hibernate.c3p0.max_size=100  
  hibernate.c3p0.min_size=2  
 hibernate.c3p0.timeout=1000
 hibernate.c3p0.max_statements=100
 hibernate.c3p0.idle_test_period=300
 hibernate.c3p0.acquire_increment=5
 hibernate.c3p0.validate=false

网上找到http://swforum.sun.com/jive/thread.jspa?threadID=51022&messageID=186798

其中一段：

原文：

Well, one of the key things that should be apparent to mysql users, but may not be,
be sure wait_timeout in your my.cnf file is set to a value greater than the idle_timeout set in your connection pool. (they both are seconds in units)

otherwise the server will kill the connection from behind, with the pool thinking it should still be alive, you'll get one of these here SocketException fangles.


意思是：mysql配置中的wait_timeout值一定要大于等于连接池种的idel_timeout值。否则mysql会在wait_timeout的时间后关闭连接，然而连接池还认为该连接可用，这样就会产生SocketException。

根据这种说法我把连接池的timeout值有1000改成50(暂定认为mysql中的wait_timeout值是50),看能否解决问题。

转

最近看了看mysql的状态变量，感觉好多跟以前自己想象的不一样。为了以后能及时发现自己的错误,就先记下来;
http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html
mysql> show status;

Binlog_cache_disk_use              0     
Binlog_cache_use                   5732727
Binlog_cache_use表示有多少个事物使用了binlog_cache_size来缓存未提交的事物日志。
Binlog_cache_disk_use 当事务日志比binlog_cache_size大时，他会创建临时文件，该状态表示有多少个事务使用了临时文件
以上两个参数可以用来动态调整binlog_cache_size变量，并且以上两个值只有在启动log-bin日志时才会有变化

Com_xxx 语句计数变量表示每个xxx 语句执行的次数。每类语句有一个状态变量。例如，Com_delete和Com_insert分别统计DELETE 和INSERT语句执行的次数。然而，如果一个查询的结果是从查询缓存中得到的，这会增加Qcache_hits，而不是Com_select

Connections                        4192
试图连接到(不管是否成功)MySQL服务器的连接数。

Created_tmp_disk_tables            0
服务器执行语句时在硬盘上自动创建的临时表的数量
Created_tmp_files                  5
mysqld创建的临时文件个数    
Created_tmp_tables                 1
服务器执行语句时在内存上自动创建的临时表的数量，如果Created_tmp_disk_tables较大，你可能要增加tmp_table_size值使临时 表基于内存而不基于硬盘。

Flush_commands                     1
flush的执行个数    

Handler_commit                     0     
Handler_delete                     0
从 表中delete行的次数，此参数与 Com_delete不一样，只要执行delete，Com_delete就会增加，而Handler_delete只有当在表中删除了行的时候才增加。 如果delete删除没有影响到表里的任何行，则不会增加Handler_delete值
Handler_discover                   0     
Handler_prepare                    0     
Handler_read_first                 0
索引中第一条被读的次数。这个表明。服务器正在进行全索引扫描 explain看的时候tpye类型为index    
Handler_read_key                   0
根据索引读行的次数，如果较高，说明查询使用了正确的表索引，explain看的时候tpye类型为const、eq_reg、ref、range、
Handler_read_next                  0
根据键顺序来读取下一行，如果你使用索引范围（range）或执行索引（index）扫描，该值增加，增加的大小为查出来的行数，一般order by 键值  该值增加 
Handler_read_prev                  0
根据键顺序来读取前一行， 基本上是用在ORDER BY ... DESC. 增加的大小为查出来的行数
Handler_read_rnd                   0
根据固定位置读一行的请求数，当需要对非键值排序时，该值会增加或者需要mysql扫描整个表时，该值会增加，增加的大小为查出来的行数 
Handler_read_rnd_next              0
在数据文件中读下一行的请求数，如果你正进行大量的表扫描，该值较高，并且增加的值为扫描的行数
(select  * from mrhao_order_info where address like '%AA%' order by address ，这个值会导致Handler_read_rn增加7，Handler_read_rnd_next 而这个会增加103840,
而select  * from mrhao_order_info where address like '%AA%' Handler_read_rnd_next 这个会增加103840，而Handler_read_rnd不会增加)

Handler_rollback                   0     
Handler_savepoint                  0     
Handler_savepoint_rollback         0     
Handler_update                     0     
Handler_write                      132
（以上handler同Handler_delete，只有影响了表里的任何行，才会增加该值）

Innodb_buffer_pool_pages_data      491   
Innodb_buffer_pool_pages_dirty     0     
Innodb_buffer_pool_pages_flushed   6050  
Innodb_buffer_pool_pages_free      0     
Innodb_buffer_pool_pages_latched   0     
Innodb_buffer_pool_pages_misc      21    
Innodb_buffer_pool_pages_total     512
data 包含数据的页数（脏或者干净的）dirty 当前的脏页数。flushed  刷新的页数 free 空页数
latched 锁定的页数（由于这个数据很号资源，所以UNIV_DEBUG 系统上编译使用） misc分配给行锁或者
hash索引管理用的页数 total 是总的页数 total=data+free+misc(5.4上 1页=16k)

Innodb_buffer_pool_read_ahead_rnd  3288  
Innodb_buffer_pool_read_ahead_seq  31840 
Innodb_buffer_pool_read_requests   3413220598
Innodb_buffer_pool_reads           104184
Innodb_buffer_pool_wait_free       0
Innodb_buffer_pool_write_requests  85786
read_ahead_rnd 表示在初始化innodb时，使用随机预读的个数，这个发生在对一个表有大量随机扫描的查询，read_ahead_seq 在初始化innodb时，使用顺序预读的个数，一般发生在全表顺序扫描，（innodb有两种预读模式：随机预读方式跟顺序预读 http://hi.baidu.com/fishhust/blog/item/7558f41083fdb808213f2efb.html/cmtid/71718e369bb4353a0b55a960  http://dev.mysql.com/doc/refman/5.0/en/innodb-disk-io.html）       read_requests 逻辑读请求的个数，reads 不在buffer pool 不得不从磁盘读取的逻辑请求个数，wait_free，一般情况下，在后台像innodb buffer pool写，然后，当需要读或者创建时，而又没有干净的页使用时，就需要等待页面刷新，这个状态就是等待实例进行计数， write_requests  为往buffer pool写入的个数

Innodb_data_fsyncs                 32146 
Innodb_data_pending_fsyncs         0     
Innodb_data_pending_reads          0     
Innodb_data_pending_writes         0     
Innodb_data_read                   1418334208
Innodb_data_reads                  146431
Innodb_data_writes                 34452 
Innodb_data_written                212622848
read 从服务器开启以来，innodb读取的字节数。reads 服务器读取的页数 ，written innodb写入的字节数 writes  服务器写入的页数（1页16K）fsyncs fsyncs（）操作数，pending_fsyncs 挂起的fsyncs（）操作数 pending_reads 挂起的读 pending_writes挂起的写

Innodb_dblwr_pages_written         6050  
Innodb_dblwr_writes                3324 
pages_written 双写操作已经写好的页数。  writes  已经执行的双写操作数量

Innodb_log_waits                   0     
Innodb_log_write_requests          2617  
Innodb_log_writes                  22335 
Innodb_os_log_fsyncs               25566 
Innodb_os_log_pending_fsyncs       0     
Innodb_os_log_pending_writes       0     
Innodb_os_log_written              12722176

Innodb_page_size                   16384 
Innodb_pages_created               13    
Innodb_pages_read                  610723
Innodb_pages_written               6050
size编译时，页的大小，一般为16K created 新建的页数 read  读的页数 written 写的页数

Innodb_row_lock_current_waits      0     
Innodb_row_lock_time               0     
Innodb_row_lock_time_avg           0     
Innodb_row_lock_time_max           0     
Innodb_row_lock_waits              0     
Innodb_rows_deleted                91    
Innodb_rows_inserted               824   
Innodb_rows_read                   1155270834
Innodb_rows_updated                337
deleted 删除的行数 inserted 插入的行数 read扫描时的行数（测试发现不是读取的行数） updated跟新的行数 以上数据只对innodb引擎，而com_ handler_是对所有引擎

Key_blocks_not_flushed             0     
Key_blocks_unused                  7150  
Key_blocks_used                    211   
Key_read_requests                  872516
Key_reads                          4553  
Key_write_requests                 7498  
Key_writes                         3768
Key_blocks_not_flushed  在 key cache中修改了，但是还没有刷新到磁盘上的块数，Key_blocks_unused     未使用的块数（key_buffer控制），
Key_blocks_used     使用的块数Key_read_requests  从cache中读取的请求数，Key_reads   从磁盘读取的块数，用Key_reads/Key_read_requests来计算cache的miss rate
Key_write_requests 写入cache的请求数，Key_writes  写入磁盘的块数
（注： 内存与磁盘交互的是块数，而cache的请求数为操作的行数。myisam 中data 与index是文件是分离的，当insert 数据的时候，myisam会直接插入数据到磁盘，但不会立即插入index到磁盘上，而是直接插入key_buffer中，而不刷新到磁盘上，所以有时候 Key_blocks_not_flushed 与Key_write_requests 可能会很高，但Key_writes 确很低，可用“flush table 表名” 强制刷新index到磁盘上。如果大量的块没有flush到磁盘上，若服务器这时候出现故障，myisam会在下次重启时，根据data文件重建 index文件，若没有重建成功，可用”repair table 表名“修理表   ）

Last_query_cost                    0.000000
查询优化器计算的最后编译的查询的总成本，这个在对于同一语句，来对比不同优化器很有用，默认为0，该变量为session变量
Max_used_connections               22  
服务器启动后，同时使用的连接的最大数量。
Not_flushed_delayed_rows           0
等待写入INSERT DELAY队列的行数

Open_files           
Open_streams
Open_table_definitions
Open_tables
Opened_files
Opened_table_definitions
Opened_tables
Open_files 打开文件的个数，这个统计是服务器打开的正规文件的个数。不包括socket 及pipe。当打开myisam表数据时，他会增加两个（数据文件与索引文件），当打开innodb表时，该值不增加，当打开的myisam表已另一个别 名打开时，Open_files只会增加一个。flush tables 会清空该值
Opened_files，当增加Open_files同时，他会已同样大小增加该值。当table_open_cache增加，或者flush tables 时，该值是不会减少，但也不增加的。
Open_table_definitions 打开表时被cache的frm文件个数。
Open_tables 打开表的个数。
Opened_table_definitions 与 Opened_tables 的解释与Opened_files差不多（跟网上说的只有当
table cache 到达table_open_cache时，才会增加Opened_files这值不一样哦）,以上状态有global 跟session

Prepared_stmt_count                0
当前prepared statements的个数，最大数会由变量max_prepared_stmt_count控制 ，当DEALLOCATE PREPARE时，改状态值会减小  

Qcache_free_blocks                 0     
Qcache_free_memory                 0     
Qcache_hits                        0     
Qcache_inserts                     0     
Qcache_lowmem_prunes               0     
Qcache_not_cached                  0     
Qcache_queries_in_cache            0     
Qcache_total_blocks                0
Qcache_total_blocks 显示了所有的块数(未使用的内存跟已使用),而Qcache_free_blocks 反映了未使用的块数。如果Qcache_free_blocks很大（如果没有内存碎片的话，应该为1），说明内存的碎片很多，内存的使用率会比较差，所 以这时虽然 Qcache_free_memory显示还有剩余的内存，也可能无法使用，当插入新的query时就需要清除旧的，使得 Qcache_lowmem_prunes很高。可以使用
flush query cache重整内存，操作之后Qcache_free_blocks应该为1，因为所有未使用的内存都放在一起作为连续的一块了  

Queries  
被服务器执行的语句个数，包括存储过程里的语句，也包括show status之类的  
Questions                          19483094
被服务器执行的语句个数， 但是不包括存储过程里面的语句

Rpl_status                         NULL  （暂时未使用）

Select_full_join                   0     
Select_full_range_join             0     
Select_range                       0     
Select_range_check                 0     
Select_scan                        1
有兴趣可以看看下面内容
http://hackmysql.com/selectandsort
在优化器explain中，显示的第一个表或者唯一的一个表他会影响： Select_scan and Select_range，
第二个表或子表会影响： Select_full_join, Select_range_check, and Select_full_range_join
show session status like "select%";的值会按照以下规则来增加
1；Select_scan 当顺序的从磁盘读取时，会增加该值，如explain中第一个表的type" 列显示ALL
EXPLAIN select * from t1,t2

2：Select_range 当explain中第一个表的type" 列显示range时，该值增加
EXPLAIN select * from t1,t2 where t1.c1>7800 and t1.c1<8000 and t1.c1=t2.c1

3：Select_full_join  explain当第二个表或子表的“type" 列显示ALL
EXPLAIN select * from t1,t2 where t1.c1>7800 and t1.c1<8000 and t1.c2=t2.c2

4：Select_full_range_join  explain的第二个表或子表为range时 该值增加
EXPLAIN select * from t1,t2 where t1.c1>7800 and t1.c1<8000 and t2.c1<1000 这个会增加Select_full_range_join 及Select_range

5：Select_range_check 的意思是不确定range的范围，
如：EXPLAIN select * from t2,t1 where t2.c1<t1.c2;第二个表显示：Range checked for each record (index map: 0x1)

Sort_merge_passes                  0     
Sort_range                         0     
Sort_rows                          0     
Sort_scan                          0
一般的，查询sort都会经历三个步骤
1.  查找where条件的值
2. 排序值
3. 读排序后的行
当在第一步时增加Select_scan, 则第三步就会是 增加Sort_scan. 如果第一步是增加 Select_range,则第三步就是 增加Sort_range.
Sort_merge_passes 包括两步。MySQL 首先会尝试在内存中做排序，使用的内存大小由系统变量 Sort_buffer_size 决定，如果它的大小不够把所有的记录都读到内存中，MySQL 就会把每次在内存中排序的结果存到临时文件中，这时候会增加Sort_merge_passes。等 MySQL 找到所有记录之后，再把临时文件中的记录做一次排序。实际上，MySQL 会用另一个临时文件来存再次排序的结果，所以通常会看到 Sort_merge_passes 增加的数值是建临时文件数的两倍。因为用到了临时文件，所以速度可能会比较慢，增加 Sort_buffer_size 会减少 Sort_merge_passes 和 创建临时文件的次数。但盲目的增加 Sort_buffer_size 并不一定能提高速度，

Slave_open_temp_tables             0     
Slave_retried_transactions         0     
Slave_running                      OFF   

Slow_launch_threads                0 
The number of threads that have taken more than slow_launch_time seconds to create.    
Slow_queries                       0     
The number of queries that have taken more than long_query_time seconds.

Table_locks_immediate              24364829
Table_locks_waited                 0     
Table_locks_immediate      立即获得的表的锁的次数。
Table_locks_waited         不能立即获得的表的锁的次数。

Threads_cached                     0     
Threads_connected                  12    
Threads_created                    4191  
Threads_running                    1  
Threads_cached        线程的缓存值，
Threads_connected     当前打开的连接的数量。
Threads_created       创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。缓存访问率的计算方法 Threads_created（新建的线程）/Connections（只要有线程连接，该值就增加）。
Threads_running   激活的（非睡眠状态）线程数。

Uptime                             978671
The number of seconds that the server has been up. 
Uptime_since_flush_status          978671
The number of seconds since the most recent FLUSH STATUS statement（FLUSH STATUS,是把当前的session值加到global上,并重置key-cache及把Max_used_connections值变为当前的连接 数）.

Mysql Explain 详解


一.语法

explain < table_name >

例如: explain select * from t3 where id=3952602;

二.explain输出解释

+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

1.id
  我的理解是SQL执行的顺利的标识,SQL从大到小的执行.

例如:
mysql> explain select * from (select * from ( select * from t3 where id=3952602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

很显然这条SQL是从里向外的执行,就是从id=3 向上执行.

2. select_type

就是select类型,可以有以下几种

(1) SIMPLE
简单SELECT(不使用UNION或子查询等) 例如:
mysql> explain select * from t3 where id=3952602;
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

(2). PRIMARY

我的理解是最外层的select.例如:

mysql> explain select * from (select * from t3 where id=3952602) a ;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

(3).UNION

UNION中的第二个或后面的SELECT语句.例如
mysql> explain select * from t3 where id=3952602 union all select * from t3 ;
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type  | table      | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
|  1 | PRIMARY      | t3         | const | PRIMARY,idx_t3_id | PRIMARY | 4       | const |    1 |       |
|  2 | UNION        | t3         | ALL   | NULL              | NULL    | NULL    | NULL  | 1000 |       |
|NULL | UNION RESULT | <union1,2> | ALL   | NULL              | NULL    | NULL    | NULL  | NULL |       |
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+

(4).DEPENDENT UNION

UNION中的第二个或后面的SELECT语句，取决于外面的查询

mysql> explain select * from t3 where id in (select id from t3 where id=3952602 union all select id from t3)  ;
+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+
| id | select_type        | table      | type   | possible_keys     | key     | key_len | ref   | rows | Extra                    |
+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+
|  1 | PRIMARY            | t3         | ALL    | NULL              | NULL    | NULL    | NULL  | 1000 | Using where              |
|  2 | DEPENDENT SUBQUERY | t3         | const  | PRIMARY,idx_t3_id | PRIMARY | 4       | const |    1 | Using index              |
|  3 | DEPENDENT UNION    | t3         | eq_ref | PRIMARY,idx_t3_id | PRIMARY | 4       | func  |    1 | Using where; Using index |
|NULL | UNION RESULT       | <union2,3> | ALL    | NULL              | NULL    | NULL    | NULL  | NULL |                          |
+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+

(4).UNION RESULT

UNION的结果。

mysql> explain select * from t3 where id=3952602 union all select * from t3 ;
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type  | table      | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
|  1 | PRIMARY      | t3         | const | PRIMARY,idx_t3_id | PRIMARY | 4       | const |    1 |       |
|  2 | UNION        | t3         | ALL   | NULL              | NULL    | NULL    | NULL  | 1000 |       |
|NULL | UNION RESULT | <union1,2> | ALL   | NULL              | NULL    | NULL    | NULL  | NULL |       |
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+

(5).SUBQUERY

子查询中的第一个SELECT.

mysql> explain select * from t3 where id = (select id from t3 where id=3952602 )  ;
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4       | const |    1 |             |
|  2 | SUBQUERY    | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4       |       |    1 | Using index |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+

(6).  DEPENDENT SUBQUERY

子查询中的第一个SELECT，取决于外面的查询

mysql> explain select id from t3 where id in (select id from t3 where id=3952602 )  ;
+----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+
| id | select_type        | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra                    |
+----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+
|  1 | PRIMARY            | t3    | index | NULL              | PRIMARY | 4       | NULL  | 1000 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4       | const |    1 | Using index              |
+----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+


(7).DERIVED

派生表的SELECT(FROM子句的子查询)

mysql> explain select * from (select * from t3 where id=3952602) a ;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+


3.table

显示这一行的数据是关于哪张表的.
有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

mysql> explain select * from (select * from ( select * from t3 where id=3952602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

4.type

这列很重要,显示了连接使用了哪种类别,有无使用索引.
从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL 

(1).system

这是const联接类型的一个特例。表仅有一行满足条件.如下(t3表上的id是 primary key)

mysql> explain select * from (select * from t3 where id=3952602) a ;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

(2).const

表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const表很快，因为它们只读取一次！

const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。在下面的查询中，tbl_name可以用于const表：
SELECT * from tbl_name WHERE primary_key=1；
SELECT * from tbl_name WHERE primary_key_part1=1和 primary_key_part2=2；

例如:
mysql> explain select * from t3 where id=3952602;
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+


(3). eq_ref

对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。

eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。

在下面的例子中，MySQL可以使用eq_ref联接来处理ref_tables：

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
    AND ref_table.key_column_part2=1;

例如
mysql> create unique index  idx_t3_id on t3(id) ;
Query OK, 1000 rows affected (0.03 sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql> explain select * from t3,t4 where t3.id=t4.accountid;
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| id | select_type | table | type   | possible_keys     | key       | key_len | ref                  | rows | Extra |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
|  1 | SIMPLE      | t4    | ALL    | NULL              | NULL      | NULL    | NULL                 | 1000 |       |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY,idx_t3_id | idx_t3_id | 4       | dbatest.t4.accountid |    1 |       |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+

(4).ref

对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。

ref可以用于使用=或<=>操作符的带索引的列。

在下面的例子中，MySQL可以使用ref联接来处理ref_tables：

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
    AND ref_table.key_column_part2=1;

例如:

mysql> drop index idx_t3_id on t3;
Query OK, 1000 rows affected (0.03 sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql> create index idx_t3_id on t3(id) ;
Query OK, 1000 rows affected (0.04 sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql> explain select * from t3,t4 where t3.id=t4.accountid;
+----+-------------+-------+------+-------------------+-----------+---------+----------------------+------+-------+
| id | select_type | table | type | possible_keys     | key       | key_len | ref                  | rows | Extra |
+----+-------------+-------+------+-------------------+-----------+---------+----------------------+------+-------+
|  1 | SIMPLE      | t4    | ALL  | NULL              | NULL      | NULL    | NULL                 | 1000 |       |
|  1 | SIMPLE      | t3    | ref  | PRIMARY,idx_t3_id | idx_t3_id | 4       | dbatest.t4.accountid |    1 |       |
+----+-------------+-------+------+-------------------+-----------+---------+----------------------+------+-------+
2 rows in set (0.00 sec)

(5).  ref_or_null

该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。

在下面的例子中，MySQL可以使用ref_or_null联接来处理ref_tables：

SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

(6). index_merge

该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

例如:
mysql> explain select * from t4 where id=3952602 or accountid=31754306 ;
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+------+------+------------------------------------------------------+
| id | select_type | table | type        | possible_keys              | key                        | key_len | ref  | rows | Extra                                                |
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+------+------+------------------------------------------------------+
|  1 | SIMPLE      | t4    | index_merge | idx_t4_id,idx_t4_accountid | idx_t4_id,idx_t4_accountid | 4,4     | NULL |    2 | Using union(idx_t4_id,idx_t4_accountid); Using where |
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+------+------+------------------------------------------------------+
1 row in set (0.00 sec)

(7). unique_subquery

该类型替换了下面形式的IN子查询的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。

(8).index_subquery

该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

(9).range

只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。

当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range

mysql> explain select * from t3 where id=3952602 or id=3952603 ;
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys     | key       | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | t3    | range | PRIMARY,idx_t3_id | idx_t3_id | 4       | NULL |    2 | Using where |
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
1 row in set (0.02 sec)

(10).index

该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。

当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。

(11). ALL

对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。


5.possible_keys

possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

6. key

key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

7.key_len

key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。
使用的索引的长度。在不损失精确性的情况下，长度越短越好 

8. ref

ref列显示使用哪个列或常数与key一起从表中选择行。

9. rows

rows列显示MySQL认为它执行查询时必须检查的行数。

10. Extra

该列包含MySQL解决查询的详细信息,下面详细.

(1).Distinct 
一旦MYSQL找到了与行相联合匹配的行，就不再搜索了 

(2).Not exists 
MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 

就不再搜索了 

(3).Range checked for each 

Record（index map:#） 
没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一 

(4).Using filesort 
看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行 

(5).Using index 
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候 

(6).Using temporary 
看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上 

(7).Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题