MySQL索引和查询优化的实际操作
以下的文章主要介绍的是MySQL索引和查询优化的实际操作流程，我们大家都知道MySQL索引和查询优化在实际操作中出现的比例较高，所以对其有更深的了解会在你今后的学习中有所收获所。

恰当的索引可以加快查询速度，可以分为四种类型：主键、唯一索引、全文索引、普通索引。



主键：唯一且没有null值。



create table pk_test(f1 int not null,primary key(f1));  alter table customer modify id int not null, add primary key(id);


普通索引：允许重复的值出现。



create table tableanme (fieldname1 columntype,fieldname2 columntype,index [indexname] (fieldname1 [,fieldname2...]));  create table tablename add index [indexname] (fieldname1 [fieldname2...]);  alter table slaes add index(value);


全文MySQL索引：用来对大表的文本域(char，varchar，text)进行索引。


语法和普通索引一样－fulltext。


使用全文索引：create table ft2 (f1 varchar(255)，fulltext(f1));


insert into ft2 values('wating for the bvarbariands'),('in the heart of the country'),('the master of petersburg'),('writing and being'),('heart of the beast'),('master master');


select * from ft2 where match(f1) against('master'); // match()－匹配域；against()匹配值。


MySQL会对某些字忽略，造成查询的误差：a. 50％以上的域出现的单词；b.少于三个字的单词；c.MySQL预定义的列表，包括the。查询语句：select * from ft2 where match(f1) against('the master'); // 与希望的结果是不同的


相关性分数查询：select f1,(match(f1) against('master')) from ft2;


MySQL4的新功能－布尔全文查询：select * from ft2 where match(f1) against('+master -pet' in boolean mode); // 运算符类型 +-<>()~*"



唯一索引：除了不能有重复的记录外，其它和普通索引一样。


create table ui_test (f1 int,f2 int,unique(f1));


alter table ui_test add unique(f2);


对域(varchar，char，blob，text)的部分创建MySQL索引：alter table customer add index (surname(10));


自动增加域：每次插入记录时会自动增加一个域的值,只能用于一个域，且这个域有索引。


create table tablename(fieldname int auto_increment,[fieldname2...,] primary key(filedname));


alter table tablename modify fieldname columntype auto_increment;


last_insert_id()函数返回最新插入的自动增加值。


select last_insert_id() from customer limit 1;


此函数在多个连接同时进行时，会发生错误。


重置自动增加计数器的值：


create table tablename(fieldname int auto_increment,[fieldname2...,] primary key(filedname) auto_increment=50);


alter table tablename auto_increment=50;


如果重置的值比存在的值小，自动增加计数器会从记录中最大的那个值开始增加计数，比如customer表中的id已经有1、2、3、15、16、20，当把自动增加计数器的值设为1时，下次插入的记录会从21开始。


自动增加计数器的越界：有效值为1～2的127次方，即2147483647。如果超过这个值(包括负值)，MySQL会自动把它设为最大值，这样就会产生一个重复键值的错误。


自动增加域在多列MySQL索引中的使用：


create table staff(rank enum('employee','manager','contractor') not null,position varchar(100),id int not null auto_increment,primary key(rank,id));


insert into staff(rank,position) values('employee','cleaner'),('cotractor','network maintenance'),('manager','sales manager');


在对每个级别添加一些数据，会看到熟悉的自动增加现象：


insert into staff(rank,position) values('employee','cleaner1'),('employee','network maintenance1'),('manager','sales manager1');


在这种情况下是不能重置自动增加计数器的。


删除或更改索引：对索引的更改都需要先删除再重新定义。


alter table tablename drop primary key;


alter table table drop index indexname;


drop index on tablename;



高效使用索引：下面讨论的是用了索引会给我们带来什么？


1.) 获得域where从句中匹配的行：select * from customer where surname>'c';


2.) 查找max()和min()值时，MySQL只需在排序的索引中查找第一个和最后一个值。


3.) 返回的部分是MySQL索引的一部分，MySQL就不需要去查询全表的数据而只需看索引：select id from custo及mer;


4.) 对域使用order by的地方：select * from customer order by surname;


5.) 还可以加速表的连接：select first_name,surname,commission from sales,sales_rep where sales.sales_rep=sales_rep.employee_number and code=8;


6.) 在通配符的情况下：select * from sales_rep where surname like 'ser%';


这种情况就不能起作