1 创建表时就指定索引
create table t1(
id int,
name char(5),
unique key nui_name(name),
primary key (id)
);
create table t2(
id int,
name char(5),
index idx_name(name)
);
2 在创建完表后为其添加索引
create table t3(
id int,
name char(5)
);
create inde idx_name on t3(name);
alter table t3 add index idx_id(id);
alter table t3 add primary key(id);
查看
mysql> show create table t3;
+-------+-------------------------------------
| Table | Create Table
+-------+-------------------------------------
| t3 | CREATE TABLE `t3` (
`id` int(11) DEFAULT NULL,
`name` char(5) DEFAULT NULL,
KEY `idx_name` (`name`),
KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------
1 row in set (0.00 sec)
删除
drop index idx_id on t3;
alter table t3 drop primary key;
#加索引可以加快查询效率，但是会降低写的效率
mysql> select count(*) from s1 where id=100000;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.27 sec)
mysql> create index idx_id on s1(id); #速度很慢
Query OK, 0 rows affected (3.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select count(*) from s1 where id=100000;#查询条件中的字段是索引字段，速度很快
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from s1 where name='egon'; #查询条件中的字段是非索引字段，速度仍然很慢
+----------+
| count(*) |
+----------+
| 793686 |
+----------+
1 row in set (0.30 sec)
select count(*) from s1 where name='egon' and gender ='male' and id > 3333 and email ='xxx';
对于多个and条件，会依次往右找到一个区分度高的索引字段，加速查询
对于多个or条件，会依次从左到右判断
create index idx_xx on s1(name,gender,id,email); #无法加速查询
create index idx_xx on s1(name,gender,email,id); #可以加速查询
create index idx_name on s1(name);
create index idx_id on s1(id);
select count(*) from s1 where name='egon' and id=3000;
select count(*) from s1 where name='egon' and id>3000;
select count(*) from s1 where name='egon' or id=3000;
#总结：正确使用索引的注意事项
1、应该选择区分度高的字段作为索引字段
2、范围问题（：>、>=、<、<=、!= 、between...and...、like、），范围过大，即便是有索引速度也会很慢
3、索引字段一定不能参与计算：select * from s1 where id*10 > 12000;
4、最左前缀匹配原则:
应该把范围查询字段往右放
create index idx_xx on s1(id,name,gender,email);
id name gender email
id gender email
id email
gender email
#可以匹配的条件是：
1、id=1 and name = 'egon' and gender='male' and email ='xxx';
2、id=1 and email ='xxx';
3、id=1 and name = 'egon' and email ='xxx';
4、name = 'egon' and email ='xxx';
5、name = 'egon' and gender='male' and email ='xxx';
select count(*) from s1 where id = 1 and name = 'egon' and gender ='male' and email = 'xxxx';
select count(*) from s1 where name = 'egon' and email ='xxx';
select count(*) from s1 where name = 'egon' and gender='male' and email ='xxx';
select count(*) from s1 where name = 'egon' and id>3 and gender='male' and email ='xxx';
create index idx_xx on s1(gender,email,name,id)
能够匹配条件中出现的字段如下
gender email name id/id email name gender
gender id
gender email
gender email id
gender
select count(*) from s1 where name = 'egon' and id>3 and gender='male' and email ='xxx';
select count(*) from s1 where name = 'egon' and id>3 and email ='xxx';
select count(*) from s1 where gender='male';
select count(*) from s1 where gender='xxx';