#创建普通索引
CREATE TABLE t12 ( id INT, NAME VARCHAR ( 10 ), INDEX ( NAME ) );
SHOW CREATE TABLE t12;
#创建唯一性索引
CREATE TABLE t13 (
id INT,
NAME VARCHAR ( 20 ),
UNIQUE INDEX idInx ( id ));
show create table t13;
#创建单列索引
CREATE TABLE t14 (
id INT,
NAME VARCHAR ( 10 ),
INDEX u_name (
NAME ( 10 )));
show create table t14;
#创建组合索引 只有第一个字段才会使用到
CREATE TABLE t15 (
id INT,
NAME VARCHAR ( 10 ),
age INT,
INDEX i_n_a ( id, NAME, age ));
show create table t15;
insert into t15 select *from t15 ;
#检查是否使用了索引
explain select * from t15 where id=1;
explain select * from t15 where age<50;
#添加普通索引
CREATE TABLE t16 (
id INT,
NAME VARCHAR ( 10 ),
age INT);
alter table t16 add index u_name (name);
#添加唯一性索引
alter table t16 add unique index u_name1 (name);
#删除指定类型的索引
alter table t16 drop index u_name;
#创建索引 create
create index ids on t16(id);
#删除索引 drop
drop index ids on t16;
show create table t16;
#索引测试 100W行数据
#清楚当前缓存
reset query cache;