myisam 索引特点 mysql索引特性

索引：提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行正确的create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度。

常见的索引分为：

• 主键索引（primary key）
• 唯一索引（unique）
• 普通索引（index）
• 全文索引（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; return return_str; 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);

先创建一个8000000条数据记录
查询员工编号为高位的员工的时候

select * from EMP where emno=998877;

耗时肯定不短，因为数据太大，查找的时候是遍历查找，查找的时候就很慢。

• 解决办法就是创建索引

alter table EMP add index(empno);

再查询的时候就是秒出查询结果

1. 基本原理

索引其实就是创建一个B树，将数据都插入到树上，

查询的时候类似于二分查找，所以在8000000条数据的时候最多查找23次

但是索引的缺点

• 占用磁盘空间
• 当添加一条数据的时候，除了添加到表中，还要维护二叉树，速度有影响，但是不大。
• 当我们添加一个索引的时候，不能解决所有查询问题，需要分别给字段建立索引
• 索引是以空间换时间

2. 创建索引

• 创建主键索引

第一种方式：
		--	在创建表的时候，直接在字段名后指定 primary key
		create table	user1(id int primary key, name varchar(30));
第二种方式：
		--	在创建表的最后，指定某列或某几列为主键索引
		create table	user2(id int, name varchar(30), primary key(id));
第三种方式：
		create table	user3(id int, name varchar(30));
		--	创建表以后再添加主键
		alter table user3 add primary key(id);

• 主键索引的特点

1. 一个表中，最多有一个主键索引，当然可以使符合主键
2. 主键索引的效率高（主键不可重复）
3. 创建主键索引的列，它的值不能为NULL，且不能重复
4. 主键索引的列基本上是int

• 唯一索引创建

第一种方式：
			--	在表定义时，在某列后直接指定unique唯一属性。
			create table user4(id int primary key, name varchar(30) unique);
第二种方式：
			--	创建表时，在表的后面指定某列或某几列为unique
			create table user5(id int primary key, name varchar(30), unique(name));
第三种方式：
				create table user6(id int primary key, name varchar(30)）； 
				alter table user6 add unique(name);

• 唯一索引的特点

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

• 普通索引的创建

第一种方式：
			在表的定义最后，指定某列为索引
			create table user8(id int primary key,
			name varchar(20),
			email varchar(30),
			index(name) --在表的定义最后，指定某列为索引
			);
第二种方式：
			在创建完表之后指定某列为普通索引
			create table user9(id int primary key, name varchar(20), email varchar(30)); 
			alter table user9 add index(name); --创建完表以后指定某列为普通索引
第三种方式：
			创建某个字段为索引
			create table user10(id int primary key,name varchar(20),email varchar(30));
			--	创建一个索引名为 idx_name 的索引 create
			 index idx_name on user10(name);

• 普通索引的特点：

1. 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
2. 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引。

• 全文索引的创建
  当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)。
• 查询索引

1. show keys from 表名；
2. show index from 表名；
3. desc 表名

三种方式都能查询到索引

• 删除索引

1. 删除主键alter table 表名 frop primary key
2. 其他索引删除alter table 表名 drop index 索引名
3. drop table user10 drop index index_name

索引创建原则

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