索引的原理是利用特殊的查找算法(如二叉树算法),限制访问的行数,提高访问效率。
索引的分类:
主键索引、普通索引、唯一索引和全文索引。
文件:
针对于mysql来说
innodb:两个文件 .frm(表结构) .ibd(索引文件)
myisam:三个文件 .frm(表结构) .MYD(表数据) .MYI(表索引)
主键索引
当一张表,把某个列设为主键的时候,则该列就是主键索引
普通索引
一般来说,普通索引的创建,是先创建表,然后在创建普通索引
唯一索引
当表的某列被指定为unique约束时,这列就是一个唯一索引
unique字段可以为NULL,并可以有多NULL,即Null!=Null。但是如果是具体内容,则不能重复
全文索引
全文索引,只对MyISAM引擎有用。主要是针对文件,文本的检索, 比如文章或者段落,
它会把某个数据表的某个数据列出现过的所有单词生成一份清单 【注】mysql自己提供的fulltext针对英文生效,想要搜索中文需要使用sphinx全文检索引擎或者使用加强版的模糊查询。
【注】全文索引不完全等同于模糊查询比如title字段有这么个数据’abcd20088ccaa’,
使用模糊查询select * from articles where title like’%2008%’可以查找到,而使用全文检索select * from articles where match(title) against(‘2008’);是检索不到的,因为2008不是一个单词!
索引的添加、删除、查看
创建索引
1、ALTER TABLE方法
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
2、CREATE INDEX
CREATE INDEX可对表增加普通索引或UNIQUE索引。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
删除索引
可利用ALTER TABLE或DROP INDEX语句来删除索引。
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY //只能删除主键索引
查看索引
show index from 表名\G
Table:表的名称。
Non_unique:如果索引不能包括重复词,则为0。如果可以,则为1。
Key_name:索引的名称。
Seq_in_index:索引中的列序列号,从1开始。
Column_name:列名称。
Collation:列以什么方式存储在索引中。在MySQL中,有值‘A'(升序)或NULL(无分类)。
全文索引
创建表时添加
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(‘要搜索的单词’);
索引的代价
SQL语言共分为四大类:数据查询语言DQL,数据管理语言DML(增删改),数据定义语言DDL(create),数据控制语言DCL(grant,commit,rollback)。
1、占用磁盘空间
索引是有开销的,表现在添加索引后.ibd文件(innodb引擎)或者.myi文件(myisam引擎)会变大。
2、导致dml操作速度变慢
添加索引后之所以会快,是因为表依据索引对数据按某种算法(二叉树等)进行排序,所以删除、增加、插入后二叉树要重新排序,导致执行效率降低。
此时要看自己的数据库是dml语句执行的多还是dql语句执行的多
使用以下语句可以查询
show status like 'com_select';
show status like 'com_insert';
show status like 'com_delete';
show status like 'com_update';
一般来说,dql语句操作比dml语句要多得多!接近9:1