MySQL之索引（***）

索引特性

索引：主要是为了提高数据库的性能。只要执行正确的create index，查询速度就可以提高很多。但是查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO，所以索引的价值，在于提高一个海量数据的检索速度。

常见索引：
1）主键索引：primary key
2）唯一索引：unique
3）普通索引：index
4）全文索引：fulltext ——— 解决中文子索引问题

案例：创建一个海量表，存放8000000条数据

//产生随机字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
‘abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ’;
declare return_str varchar(255) default ‘’;
declare i int default 0;
while i < n do
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
end $$
delimiter ;

//产生随机数字
delimiter $$
create function rand_num()
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
delimiter ;
//创建存储过程，向雇员表添加海量数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into EMP values ((start+i)
,rand_string(6),‘SALESMAN’,0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;
//执行存储过程，添加8000000条记录
call insert_emp(100001, 8000000);

上面的代码直接拷贝，运行完就创建了一个拥有海量数据的表。

• 查询员工编号为998877的员工

select * from emp where empno=998877;

在没有使用索引的时候可以看到耗时4.93秒，在实际项目中，假如同时有1000个人并发查询，就有可能死机。

• 创建索引alter table emp add index(empno);
• 换一个员工号，测试查询时间

mysql> select * from emp where empno = 123456;

可以看到，这次只用了0.01秒，速度提高很多。

索引基本原理

在上面的例子中，emp表本来没有索引，当查找一个数据时会进行整表扫描，而添加索引后，索引会形成一棵二叉树。这种二分查找的思想，对于8000000的数据，最多只查找23次。

关于索引的几点说明：
1）索引占用磁盘空间
2）当添加一条记录，除了添加到表中，还要维护二叉树，对速度有一点影响
3）添加一个索引不能解决所有查询问题，需要分别给字段建立索引，例如：select * from emp where ename = ‘abcdef’;
4）索引是以空间换时间

1、创建索引

1.1 创建主键索引

• 方法一：在创建表的时候，直接在字段名后指定primary key

mysql> create table user1(id int primary key,name varchar(30));

• 方法二：在创建表的最后，指定某列或者某几列为主键索引

mysql> create table user2(id int,name varchar(30),primary key(id));

• 方法三：创建表之后再添加主键

mysql> create table user3(id int,name varchar(30));
mysql> alter table user3 add primary key(id);

主键索引特点：
1）一个表中，最多只有一个主键索引，可以使用复合主键
2）主键索引的效率高（主键不可重复）
3）创建主键索引的列，它的值不能为null，且不能重复
4）主键索引的列基本上是int

1.2 唯一索引的创建

• 方法一：在定义表时，在某列后直接指定unique唯一属性

mysql> create table user4(id int primary key,name varchar(30) unique);

• 方法二：创建表时，在表的后面指定某列或某几列为unique

mysql> create table user5(id int primary key,name varchar(30),unique(name));

• 方法三：创建表之后再添加unique

mysql> create table user6(id int primary key,name varchar(30));
mysql> alter table user6 add unique(name);

唯一索引特点：
1）一个表中，可以有多个唯一索引
2）查询效率高
3）如果在某一列创建唯一索引，必须保证这一列不能有重复数据
4）如果一个唯一索引上指定not null，等价于主键索引

1.3 普通索引的创建

• 方法一：在表的定义最后，指定某列为索引

mysql> create table user7(id int primary key,name varchar(20),email varchar(30),index(name));

• 方法二：创建完表以后指定某列为普通索引

mysql> create table user8(id int primary key,name varchar(20),email varchar(30));
mysql> alter table user8 add index(name);

• 方法三：创建一个索引名为idx_name的索引

mysql> create table user10(id int primary key,name varchar(20),email varchar(30));
mysql> create index idx_name on user10(name);

普通索引特点：
1）一个表中可以有多个普通索引，在实际开发中用的比较多
2）如果某列需要创建索引，但是该列有重复的值，就要使用普通索引

1.4 全文索引的创建

当对文章字段或有大量文字的字段进行检索时，会使用全文索引。全文索引要求表的存储引擎必须是MyISAM，而且默认的全文索引只支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)。
案例：

mysql> create table articles(
    -> id int unsigned auto_increment not null primary key,
    -> title varchar(200),
    -> body text,
    -> fulltext(title,body)
    -> )engine=MyISAM;
mysql> insert into articles(title,body) values
    -> ('MySQL Tutorial','DBMS stands for DataBase ...'),
    -> ('How to use MySQL well','After you went through a ...'),
    -> ('Optimizing MySQL','In this tutorial we will show ...'),
    -> ('1001 MySQL Tricks','1. Never run mysqld as root 2....'),
    -> ('MySQL vs. YourSQL','In the following database comparison ...'),
    -> ('MySQL Security','When configured properly,MySQL ...');

• 查询有没有database数据（不使用全文索引）

mysql> select * from articles where body like '%database%';

• 使用全文索引

mysql> select * from articles where match (title,body) against ('database');

使用explain工具来分析一下上面两个SQL语句：

mysql> explain select * from articles where body like '%database%'\G
mysql> explain select * from articles where match (title,body) against ('database')\G

2、查询索引

• 方法一：show keys from 表名;
• 方法二：show index from 表名;
• 方法三：desc 表名;

3、删除索引

• 方法一：删除主键索引
  alter table 表名 drop primary key;
• 方法二：其他索引的删除
  alter table 表名 drop index 索引名
  索引名就是show keys from 表名中的key_name字段。

mysql> alter table user9 drop index idx_name;

• 方法三：drop index 索引名 on 表名

mysql> drop index name on user7;

4、索引创建的原则

• 比较频繁作为查询条件的字段应该创建索引
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
• 更新非常频繁的字段不适合作为索引
• 不会出现在where子句中的字段不该创建索引