+----+-------------+-------+-------+---------------------+---------------------+---------+------+------+-----------------------+
|? 1 | SIMPLE? ? ? | actor | range | idx_actor_last_name | idx_actor_last_name | 137? ? | NULL |? ? 7 | Using index condition |
+----+-------------+-------+-------+---------------------+---------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
robin@localhost[sakila]> explain select * from actor_like where last_name like 'A%';
+----+-------------+------------+-------+---------------------+---------------------+---------+------+------+-----------------------+
| id | select_type | table? ? ? | type? | possible_keys? ? ? | key? ? ? ? ? ? ? ? | key_len | ref? | rows | Extra? ? ? ? ? ? ? ? |
+----+-------------+------------+-------+---------------------+---------------------+---------+------+------+-----------------------+
|? 1 | SIMPLE? ? ? | actor_like | range | idx_actor_last_name | idx_actor_last_name | 137? ? | NULL |? ? 7 | Using index condition |
+----+-------------+------------+-------+---------------------+---------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
--从上面的执行计划可以看出，like方式建表与原表使用了相同的执行计划
4、基于myisam引擎进行create table like方式克隆
robin@localhost[sakila]> alter table actor_like engine=myisam;
Query OK, 200 rows affected (0.03 sec)
Records: 200? Duplicates: 0? Warnings: 0
robin@localhost[sakila]> show table status like 'actor_like'\G
*************************** 1. row ***************************
? ? ? ? ? Name: actor_like
? ? ? ? Engine: MyISAM
? ? ? ? Version: 10
? ? Row_format: Dynamic
? ? ? ? ? Rows: 200
?Avg_row_length: 25
? ? Data_length: 5016
Max_data_length: 281474976710655
? Index_length: 7168
? ? ? Data_free: 0
?Auto_increment: 201
? ? Create_time: 2015-01-19 11:19:55
? ? Update_time: 2015-01-19 11:19:55
? ? Check_time: 2015-01-19 11:19:55
? ? ? Collation: utf8_general_ci
? ? ? Checksum: NULL
?Create_options:
? ? ? ? Comment:
1 row in set (0.00 sec)
robin@localhost[sakila]> create table actor_like_isam like actor_like;
Query OK, 0 rows affected (0.01 sec)
robin@localhost[sakila]> insert into actor_like_isam select * from actor_like;
Query OK, 200 rows affected (0.00 sec)
Records: 200? Duplicates: 0? Warnings: 0
robin@localhost[sakila]> insert into actor_like_isam select * from actor_like;
Query OK, 200 rows affected (0.00 sec)
Records: 200? Duplicates: 0? Warnings: 0
robin@localhost[sakila]> show index from actor_like_isam\G
*************************** 1. row ***************************
? ? ? ? Table: actor_like_isam
? Non_unique: 0
? ? Key_name: PRIMARY
?Seq_in_index: 1
? Column_name: actor_id
? ? Collation: A
? Cardinality: 200
? ? Sub_part: NULL
? ? ? Packed: NULL
? ? ? ? Null:
? Index_type: BTREE
? ? ? Comment:
Index_comment:
*************************** 2. row ***************************
? ? ? ? Table: actor_like_isam
? Non_unique: 1
? ? Key_name: idx_actor_last_name
?Seq_in_index: 1
? Column_name: last_name
? ? Collation: A
? Cardinality: 100
? ? Sub_part: NULL
? ? ? Packed: NULL
? ? ? ? Null:
? Index_type: BTREE
? ? ? Comment:
Index_comment:
2 rows in set (0.00 sec)
robin@localhost[sakila]> explain select * from actor_like_isam where last_name like 'A%';
+----+-------------+-----------------+-------+---------------------+---------------------+---------+------+------+-----------------------+
| id | select_type | table? ? ? ? ? |