联系QQ 284710375
mysql的索引在查询中至关重要，表的数据量较大时，如果没有索引，查询速度就会慢很多。
mysql的四种索引：主键索引、普通索引、唯一索引、全文索引
1、添加索引
1.1、主键索引
当一张表，把某个字段设为主键primary key的时候，则这个字段就是主键索引，比如
create table aaa(
id int unsigned primary key auto_increment,
name varchar(32) not null
);
这里的id就是主键索引
如果创建表时没有添加主键，也可以在创建表后再添加，添加主键语句
alter table 表名 add primary key(列名);
比如，创建一个没有主键的表
create table bbb(id int, name varchar(32) not null default '');
添加主键
alter table bbb add primary key (id);
注意：主键字段，不能为null，也不能重复
1.2、普通索引
一般来说，普通索引的创建，是先创建表，然后再创建索引，比如创建一张表
create table ccc(id int unsigned, name varchar(32));
创建普通索引
create index 索引名 on 表 (列);
1.3、唯一索引
当表的某列被指定为unique约束时，这个列就是一个唯一索引，比如创建表时顺便创建唯一索引：
create table ddd(id int primary key auto_increment, name varchar(64) unique);
这时，name列就是一个唯一索引
unique字段可以为null，并且可以有多个null，但如果是具体的内容(例如:''代表空字符串)，则不能重复
例如：
#下面两个可以正常执行
insert into ddd values(1,null);
insert into ddd values(2,null);
#下面是错误写法， 不可以执行（唯一索引不能重复使用空字符串）
insert into ddd values(1,'');
insert into ddd values(2,'');
原因：null代表什么都没有，没有这个空间，''代表一个空字符串
如果创建表时没有添加唯一索引，可以在表创建成功后，再添加唯一索引，sql语句为
create unique index 索引名 on 表名 (字段名);
创建表
create table eee(id int primary key auto_increment, name varchar(64));
添加唯一索引
create unique index my_unique on eee (name);
还有一种多字段联合组成唯一索引的用法，创建索引语句为
alter table 表名 add nuique 索引名(字段1,字段2..);
添加了唯一索引后，再向表中insert into相同数据时，会返回错误信息
不论单个字段添加的唯一索引，或者多个字段联合的唯一索引，我们可通过唯一索引让sql端实现存在即更新，不存在即添加的功能，只需把insert into更换为replace into即可，注意：这种做法在相同数据存在的情况下，主键id会被更新，所以我们会看到mysqli_affected_rows返回的影响条数为2
1.4、全文索引（创建一个大概、可能匹配到的索引）
创建一个包含全文索引的表
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=myisam charset utf8;
添加数据
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 ...');
错误用法
select * from articles where body like '%mysql%'; #不会使用到全文索引
证明方法
explain select * from artilces where body like '%mysql%'\G;
通过explain语句可以分析，mysql如何执行你的sql语句，获得关于MySQL如何执行select语句的信息
如上图，返回的key都为空，证明没有使用全文索引
全文索引正确用法
select * from articles where match(title,body) against('database'); #可以使用到全文索引
证明如图：
全文索引注意事项
⑴、在mysql中 fulltext(全文) 索引只针对myisam生效（上面创建表时用到的engine=myisam）；
⑵、mysql自己提供的 fulltext 只针对英文生效，中文可以用sphinx(中文版->coreseek)技术处理；
⑶、使用方法是 match(字段名1,字段名2,...) against(关键字);
⑷、全文索引有“停止词”，因为在一个文本中，创建索引会生成一个无穷大的数，因此，对一些常用的词和字符，就不会创建，这些词，称为停止词。
2、查询索引
desc 表名 #该方法缺点：不能够显示索引名
show index from 表名
show indexes from 表名
show keys from 表名\G #可以一列一列的显示
3、删除索引
alter table 表名 drop index 索引名;
比如删除唯一索引
alter table eee drop index my_unique;
删除主键索引
alter table 表名 drop index primary key;
4、修改索引
一般的方法是：先删除，再创建
5、索引使用注意事项
索引虽然增加查询速度，但维护索引是有代价的：
1、占用磁盘空间
2、对dml(insert/update/delete)的操作有影响，变慢
变慢的原因：每次dml操作会维护索引文件（比如添加或者删除数据，索引文件会重新进行二叉树算法计算）
6、索引适用场景
6.1、较频繁的作为查询条件的字段应该创建索引
select * from emp where empno=1;
6.2、唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件，比如查询性别
select * from emp where sex="男"
6.3、更新非常频繁的字段不适合创建索引（每次更新都会维护索引文件，这样做反而会变慢）
6.4、不会出现在where子句中的字段不该创建索引
总结，满足以下条件的字段，才应该创建索引
1、肯定在where条件中经常使用
2、该字段的内容不仅仅是的几个值(例如sex)
3、字段内容不是频繁变化的
7、索引的原理
为什么创建索引后，速度就会变快，看下图
上图中的emp表共有11条数据，执行左边的sql语句，会对表中的数据进行依次匹配，即使匹配到还会继续进行匹配，直到所有把数据都匹配完。
如果创建了索引，会用二叉树算法进行匹配，如图：
取所有数据的中间数，依次向下排列，用主键做为条件进行对比时，只需要进行几次对比便可以找到该数据在磁盘中的物理位置。
二叉树算法，简称：BTREE
二叉树算法的效率：log2N (以2为底的N次方)，比如检索10次 可以检索2的10次方条记录(1024条记录)
提示：索引记录的是数据在磁盘中的物理位置，有时候把mysql下data中的数据库文件拷贝到另一个数据库中去运行，会发现数据还在，但是速度变慢，原因是索引不能用，因为索引数据的物理位置已经不在，解决方法：重建索引。
上一篇：MySQL优化 - 慢查询
下一篇：MySQL优化 - 水平分割
15601023311
