数据库调优教程（五） 索引的作用和索引的种类
三、 索引
上一章我们学习了使用explain来生成一个查询执行计划（QEP），从而发现语句存在的问题。在explain返回的结果中，有三个是和索引有关的（possible key、key、extra），可见索引在改善查询效率上的显著地位。
这一章，我们将全方位讲解如何使用索引来优化我们的数据库。
1. 索引的作用
索引的原理是利用特殊的查找算法（如二叉树算法），限制访问的行数，提高访问效率。
另外，索引还可以高效地在表之间建立连接操作，高效的对结果进行排序等等
2. 基本索引种类及创建
索引可以分为主键索引、普通索引、唯一索引和全文索引。
1) 主键索引
当一张表，把某个列设为主键的时候，则该列就是主键索引
- create table aaa
- (id int unsigned primary key auto_increment ,
- name varchar(32) not null defaul ‘’);
这是id 列就是主键索引.
如果你创建表时，没有指定主键索引，也可以在创建表后再添加
- alter table 表名 add primary key (列名);
2) 普通索引
一般来说，普通索引的创建，是先创建表，然后在创建普通索引
- create index 索引名 on 表 (列名1,列名2);
当括号中的列不止一个时，我们称之为多列索引，多列索引在一定情况下可以进化为覆盖索引（后面会介绍）。
这里我们通过在emp表中创建普通索引来见识一下索引的威力。
在未创建索引前
我们执行以下语句
- select count(1) from emp where ename like ‘yu%‘;
接着我们创建索引
- create index index_ename on emp(ename);
再次执行
- select count(1) from emp where ename like ‘yu%‘;
不可思议！
索引的威力可见一斑！
当然索引是有开销的
这是我在未创建索引时mysql安装目录下data/bigtable/emp.ibd文件的大小，对于innodb引擎的表来说，ibd文件存放了表的数据和索引。
在我创建了索引之后
显然，索引会占用主机的空间，这也称作索引的开销。
3) 唯一索引
当表的某列被指定为unique约束时，这列就是一个唯一索引- create table ddd(id int primary key auto_increment , name varchar(32) unique);
这时, name 列就是一个唯一索引
在创建表后，再去创建唯一索引
- create unique index 索引名 on 表名 (列表..);
4) 全文索引
全文索引，只对MyISAM引擎有用。主要是针对对文件，文本的检索, 比如文章或者段落,.
它会把某个数据表的某个数据列出现过的所有单词生成一份清单
全文索引的创建
在创建表时创建
- CREATE TABLE articles (
- id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
- title VARCHAR(200),
- body TEXT,
- FULLTEXT (title,body)
- )engine=myisam charset utf8;
或者在创建表后添加
- ALTER TABLE articles ADD FULLTEXT (title,body);
全文索引正确的用法是:
- select * from articles where match(title,body) against(‘要搜索的单词’);
另外，mysql自己提供的fulltext针对英文生效，想要搜索中文需要使用sphinx全文检索引擎或者使用加强版的模糊查询。
注意：
全文索引不完全等同于模糊查询
比如title字段有这么个数据’abcd20088ccaa’,使用模糊查询select * from articleswhere title like’%2008%’可以查找到，而使用全文检索select * from articleswhere match(title) against(‘2008’);是检索不到的，因为2008不是一个单词！郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
