mysql数据库之索引

CREATE INDEX 索引名 ON 表名 (列名(长度));
 
#（列名(length）：length是可选项，下同。如果忽略length的值，则使用整个列作为索引。如果指定，使用列的前length的个字符来创建索引，这样有利于减少索引文件的大小。在不损失精确性的情况下，长度越短越好。

#索引名建议以"_index"结尾

方式二：修改表结构的方式创建索引

 ALTER TABLE 表名 ADD INDEX 索引名(列名);

方法三：创建表时指定索引

create table 表名 （字段1 数据类型，字段2 数据类型[,.....],索引名（列名））；

该方式一般不建议采用：在创建表的时候添加索引，会让插入数据变慢。

2）唯一索引的创建

唯一索引：与普通索引类似，但区别是唯一索引列的每个值都唯一。唯一索引允许有空值，但是不允许有两个及其以上的空值（注意和主键不同）。

如果是用组合索引创键，则列值的组合必须唯一，添加唯一键将自动创建唯一索引。

创建唯一键或者创建唯一索引都可实现。

方式一：直接创建唯一索引

create unique index 索引名 on 表名（列名）；

create unique index cardid_index on member(cardid);

方式二：修改表结构的方式增加唯一键

 ALTER TABLE 表名 ADD UNIQUE 索引名(列名);

方式三：创建表时指定索引

create table 表名 （字段1 数据类型，字段2 数据类型[,....]，unique  索引名 （列名））；

方法四：添加唯一键，即为唯一索引

alter table 表名 add unique key(字段);

3）主键索引的创建

主键索引是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。一个表只能有一个主键，不允许有空值，且该字段为唯一值。添加主键将自动创建主键索引。

方式一：创建表时添加主键

create table 表名 （[...],primary key (列名)）；

方式二：在现有表中添加主键

alter table 表名 add primary key （列名）；

alter table 表名  member add primary key （id）；

4）组合索引的创建

组合索引（单列索引与多列索引）：可以是单列上创建的索引，也可以是在多列上创建的索引。

需要满足最左原则，因为select 语句的where条件是依次从左往右执行的，所以在使用select 语句查询时where 条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。

方法一：直接创建索引

 CREATE INDEX 索引名 on 表名(字段1,字段2，字段3);

方法二：修改表的方式创建索引

 alter table 表名 add index 索引名(字段1,字段2, ..., 字段n);

方法三：创建表的时候指定索引

create table 表名 （列名1 数据类型，列名2 数据类型，列名3 数据类型，index 索引名 （列名1，列名2，列名3））；

组合索引的使用方法

select * from 表名 where 列名1=‘...’ and 列名2=‘...’ 列名3=‘...’；

5）全文索引的创建

适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息

在MySQL5.6版本以前FULLTEXT索引仅可用于MyISAM引擎，在5.6版本之后innodb 引擎也支持FULLTEXT 索引

全文索引可以在CHAR、 VARCHAR 或者TEXT 类型的列上创建
每个表一般只创建一个全文索引
查询时只能匹配完整的单词/字符串

方法一：直接创建索引

create fulltext index 索引表 on 表名 （列名）；

方式二：修改表的方式创建索引

alter table 表名 add fulltext 索引名（列名）；
 
alter table member add fulltext remark_index (remark);

方法三：创建表时指定索引

create table 表名 （字段1 数据类型[,...],fulltext 索引名 （列名））；

#·数据类型可以为CHAR、VARCHAR或者TEXT

全文索引在查询中使用方法

select * from 表名 where match（列名）against（‘查询内容’）；
 
insert into member values（1，‘zhangsan’，123123、123123、  ‘nanjing’，‘this is member’ ！）；                                              
 
insert into member values（2，‘lisi’，456456、456456、‘beijing’，‘this is vip!’  ‘nanjing’）；
 
insert into member values（3，‘wangwu’，789789、789789、‘shanghai’，‘this is vip member!’);
 
select * from member where match(remark) against('vip');

6）索引删除的通用方式

直接删除索引

drop index 索引名 on 表名;

修改表方式删除索引

alter table 表名 drop index 索引；

主键索引的删除方法

alter table 表名 drop primary key;

7）查看表中存在全部索引的方法

 show index from 表名;      #能查看索引的字段和细节，建议以纵向形式查看
 show index from 表名\G     #建议使用\G以纵向形式查看
 
 show keys from 表名;
 show keys from 表名\G
 
 show create table 表名;    #只能查看索引的字段和名称

案例：比如为某商场做一个会员卡的系统。这个系统有一个会员表，有下列字段：

会员编号 INF

会员姓名 VARCHAR（10）

会员身份证号码 INT（18）

会员电话 INT（11）

会员住址 VARCHAR（50）

会员备注信息 TEXT

create table member （id int （10），name varchar（10），cardid varchar（10），phone int（11），address varchar（50），remark text）；

create table member （id int （10），name varchar（10），cardid varchar（10），phone int（11），address varchar（50），remark text）；

alter table member add primary key （id）；
 
create index name_index on member (name);
 
create unique index cardid_index on member（cardid）；
 
alter table member add fulltext remark_index （remark）；

那么会员编号，作为主键，使用primary key
会员姓名，如果要建索引的话，那么就是普通的index
会员身份证号码，如果要建索引的话，那么可以选择fulltext，全文搜索
不过fulltext用于搜索很长一篇文章的时候，效果最好，用在比较短的文本，如果就一两行字的，普通的index也可以

四、explain 语句用法以及mysql查询速度的优化

1）explain语句的作用

当我们写好了查询语句不确定自己是否引用的字段是不是索引字段时，可以在查询语句前添加explain来确定自己是否引用了索引字段

explain select * from 表名 where 条件语句;

2）mysql查询速度的优化

我们知道mysql的核心功能就是数据的查询，数据查找的速度尤为重要。对于内容条数过多的数据表会产生响应慢的可能。所以查询速度优化是数据库管理员必不可少的工作

硬件上的优化

CPU对于MySQL应用，推荐使用S.M.P.架构的多路对称CPU，例如：可以使用两颗Intel Xeon 3.6GHz的CPU，现在我较推荐用4U的服务器来专门做数据库服务器，不仅仅是针对于mysql
物理内存对于一台使用MySQL的Database Server来说，服务器内存建议不要小于2GB，推荐使用4GB以上的物理内存，不过内存对于现在的服务器而言可以说是一个可以忽略的问题，工作中遇到了高端服务器基本上内存都超过了16G
磁盘寻道能力（磁盘I/O）,以目前高转速SCSI硬盘(7200转/秒)为例，这种硬盘理论上每秒寻道7200次，这是物理特性决定的，没有办法改变。MySQL每秒钟都在进行大量、复杂的查询操作，对磁盘的读写量可想而知。所以，通常认为磁盘I/O是制约MySQL性能的最大因素之一，对于日均访问量在100万PV以上的系统，由于磁盘I/O的制约，MySQL的性能会非常低下！解决这一制约因素可以考虑以下几种解决方案：使用RAID-0+1磁盘阵列，注意不要尝试使用RAID-5，MySQL在RAID-5磁盘阵列上的效率不会像你期待的那样快

查询语句进行索引优化

在使用数据查询语句时，发现查询的时间明显缓慢（一般1s钟以上就存在慢的问题），使用explain语句进行分析（查看是否存在索引，以及该语句是否真正用到了索引）
若该语句中的条件并为涉及索引，可以添加索引来进行优化
索引类型单一，条件语句中条件用到的较多，可以尝试组合索引，以此加快查询速度

总结

 （1）创建索引

1）普通索引：
create index 索引名 on 表名 (字段);

alter table 表名 add index 索引名 (字段(4));

create table 表名 (字段.... , index 索引名(字段));

2）唯一索引：
create unique index 索引名 on 表名 (字段);

alter table 表名 add unique 索引名(字段);

create table 表名 (字段.... , unique 索引名(字段));

3）主键索引：
alter table 表名 add primary key (字段);

create table 表名 (字段.... , primary key (字段));

create table 表名 (字段 primary key, ... );

4）组合索引（单列、多列索引）:
create index XXX_index on 表名 (字段1,字段2, ... , 字段n);

alter table 表名 add index XXX_index (字段1,字段2, ... , 字段n);

create table 表名(列名1 数据类型,列名2 数据类型,列名3 数据类型, INDEX 索引名(字段1,字段2,字段3));

使用时要注意 where 的最左原则：

select * from 表名 where 字段1=XXX and 字段2=XXX and ....

5）全文索引：
create fulltext index 索引名 on 表名 (字段);

alter table 表名 add fulltext 索引名 (字段);

create table 表名 (字段.... , fulltext 索引名(字段));

查询时只能匹配完整的字符串：

select * from 表名 where match(字段) against('查询字符串');



（2）查看表中存在的索引 

show index from 表名; //能查看索引的字段和细节，建议使用\G纵向查看

show keys from 表名;

show create table 表名; //只能查看索引的字段和名称



（3）删除索引 

删除主键索引的方法：
alter table 表名 drop primary key;

删除其他索引：
drop index 索引名 on 表名;

alter table 表名 drop index 索引名;



（4）explain语句
explain添加在查询语句前面，可以分析出该语句是否使用了索引，以及具体使用索引的字段