MySQL索引(六)

一、什么是索引

　　索引就像一本书的目录一样，如果在字段上建立索引，那么以索引为列的查询条件时可以加快查询的速度，这个就是MySQL优化的重要部分

二、创建主键索引

　　整个表的每一条记录的主键值在表内都是唯一的，用来唯一标识一条记录

　　查询数据库，按主键查询是最快的，每个表只能有一个主键列，但是可以有多个普通索引列。主键列要求列的所有内容必须唯一，而普通索引列不要求内容必须唯一

　　无论建立主键索引还是普通的索引，都要在表的对应列上创建，可以对单列创建索引，也可以对多列创建索引

创建主键索引的方法(在创建表的时候)--PRI
create table test1(
id int(4) not null auto_increment,
name char(20) not null,
age tinyint(2) not null default '0',
dept varchar(16) default Null,
primary key(id), 			# 主键索引
key index_name(name)                      
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


查看主键索引
show index from test;

为创建好的表用sql添加主键(基本上不用)
alter table test change id id int primary key auto_increment;

PS：如果主键索引有auto_increment自增参数是不能删除索引的，在工作中主键索引也不可能删除

三、创建普通索引

创建普通索引(创建完表随时会加)--MUL
在创建表的时候
create table test2(
id int(4) not null auto_increment,
name char(20) not null,
age tinyint(2) not null default '0',
dept varchar(16) default Null,
primary key(id), 				
key index_name(name)          # 普通索引            
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

查看索引
show index from test1;

删除普通索引
alter table test1 drop index index_name;
drop index index_name on test1;

创建表后增加普通索引
alter table test1 add index index_name(name);


PS:添加索引的时间取决于表中的数据的大小

四、创建联合索引

　　一张表的一个字段唯一值不多的情况下，希望创建一个唯一值更少的索引，所以就可以把多个字段联合起来创建索引(字段越多创建联合索引，唯一值就越少)

在创建表的时候加上联合索引
create table test3(
id int(4) not null auto_increment,
name char(20) not null,
age tinyint(2) not null default '0',
dept varchar(16) default Null,
primary key(id), 				
key index_name(name),
key index_name_dept(name,dept)  # 联合索引                    
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建联合索引
create index index_name_dept on test(name,dept);
alter table test add index index_name_dept(name,dept);

对字段的前n个字符创建联合索引
create index index_name_dept on test(name(8),dept(10));
alter table test add index index_name_dept(name(8),dept(10));

删除联合索引
alter table test drop index index_name_dept;
drop index index_name_dept on test;

主键也可以创建联合索引
create table test4(
id int(4) not null auto_increment,
name char(20) not null,
age tinyint(2) not null default '0',
dept varchar(16) default Null,
primary key(id,age),            # 主键的联合索引				
key index_name(name),
key index_name_dept(name,dept)  # 联合索引                    
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


PS：建立索引的原则：我们尽量在唯一值多的大表上建立索引

PS:联合索引的前缀特性
　　index(a,b,c)仅a,ab,abc三个查询条件会走索引
　　尽量把最常用的作为查询条件的列，放在第一的位置

五、创建唯一索引(非主键)

创建非主键的唯一索引(相当于和主键类似)--UNI 可以为空
create unique index uni_index_name on test(name);

删除索引
alter table test drop index uni_index_name;
drop index uni_index_name on test;

查看索引
show index from test;

六、给字段的前n个字符创建索引

　　当一个字段的内容的前N个字符已经接近唯一的时候，我们可以对前n个字符创建索引,这样可以节省创建索引的空间，以及降低读取金和更新维护索引消耗的系统资源

创建前n个字符的索引
create index index_dept on test(dept(8));
alter table test add index index_dept(dept(8));

删除索引
alter table test drop index index_dept;
drop index index_dept on test;

查看索引
show index from test;

七、关于索引的问题

既然索引可以加快查询的速度，那么就可以给所有的列加上索引吧？
因为索引不但占用系统空间，而且更新数据时还需要维护索引数据，因此索引是双刃剑，并不是越多越好，如：数十到几百行的小表上无需创建索引，更新频繁，读取比较少的表要少建索引

需要在哪些列上创建索引呢？
select user from mysql.user where host='' 索引一定要创建在where后的条件列上，如果是连表查询要创建在连接的列上，而不是创建在select后选择数据的列上，另外我们要尽量选择在唯一值多的大表上的列创建索引

八、索引的总结

创建索引的基本知识小结：
1、 索引类似书籍的目录，会加快查询数据的速度。
2、 要在表的列（字段）上创建索引。
3、 索引会加快查询速度，但是也会会影响更新的速度，因为更新要维护索引数据。
4、 索引列并不是越多越好，要在频繁查询的表语句where后的条件列上创建索引。
5、 小表或重复值很多的列上可以不建索引，要在大表以及重复值少的条件列上创建索引.
6、 多个列联合索引有前缀生效特性。
7、 当字段内容前N个字符己经接近唯一时，可以对字段的前N个字符创建索引。
8、 索引从工作方式区分，有主键，唯一，普通索引。
9、 索引类型会有BTREE (默认）和hash (适合做缓存（内存数据库）等