robin@localhost[sakila]> show index from actor_like\G
*************************** 1. row ***************************
Table: actor_like
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
Non_unique: 1
Key_name: idx_actor_last_name
Seq_in_index: 1
Column_name: last_name -- Author: Leshami
Collation: A -- Blog :
Cardinality: 200
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 where last_name like 'A%';
+----+-------------+-------+-------+---------------------+---------------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------+---------------------+---------+------+------+-----------------------+
| 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)