一、索引的概念
1、索引的作用
索引的作用在于不需要加内存、不用改程序、不用调sql,只需要创建索引,就能提高海量数据的检索速度,但是要以插入、删除和更新的速度为代价。
2、索引的基本概念:
创建索引会形成一棵二叉树,查询时可进行二分查找。创建索引会占用磁盘空间,处理信息时,除了改变表中的数据,还要维护二叉树,对插入、删除和更新的速度有影响,但是影响不大,是典型的以空间换取时间。如下图所示:
常见的索引分为:主键索引(primary key)、唯一索引(unique)、普通索引(index)和全文索引(fulltext)。
3、创建索引的原则
(1)比较频繁作为查询条件的字段应该创建索引;
(2)唯一性太差的字段不适合单独创建索引,及时频繁作为查询条件;
(3)更新非常频繁的字段不适合创建索引;
(4)不会出现在where子句中的字段不该创建索引。
二、创建索引
1、创建主键索引
(1)第一种方式:在创建表的时候,直接在字段名后指定primary key。
(2)第二种方式:在创建表的最后,指定某列或某几列为主键索引。
(3)第三种方式:创建表以后再添加主键。
主键索引的特点:
1)一个表中,最多有一个主键索引;
2)主键索引的效率高;
3)创建索引的列不能为空,且不能重复;
4)主键索引的列基本上是int类型。
2、唯一索引的创建
1)第一种方式:在表定义时,在某列后面直接指定unique唯一属性。
2)第二种方式:创建表时,在表的后面指定某列或某几列为unique。
3)第三种方式:创建表以后再添加唯一属性。
唯一索引的特点:
1)一个表中可以有多个唯一索引,查询效率高;
2)如果在某一列建立唯一索引,这列上的数据可以为空,但是不可以重复;
3)如果一个唯一索引上的列指定为not null,等价于主键索引。
3、普通索引的创建
(1)第一种方式:在表定义最后,指定某列为普通索引。
(2)第二种方式:创建完表以后,添加某列为普通索引。
(3)第三种方式:使用create index 索引名 on 表名
普通索引的特点:如果某列需要创建索引,但是该列有重复的值,则应该使用普通索引,一个表中可以有多个普通索引。
4、全文索引的创建——fulltext
(1)全文索引的概念:
对文章字段或有大量文字的字段进行检索时,会使用到全文索引。MySQL提供全文索引机制,但是要求表的存储引擎必须是MyISAM,而且默认的全文索引支持英文不支持中文。如果对中文进行全文检索,可以使用sphinx的中文版(coreseek)。
(2)全文索引的创建
1)第一种方式:创建表时创建全文索引
2)第二种方式:使用“create fulltext index 全文索引名 on 表名(列名,1 列名2,…)”格式创建索引。
3)第三种方式:使用alter table 表名 add fulltext index 全文索引名(列名1, 列名2…)格式创建索引。
(3)基本操作
1)插入数据
2)使用普通方法查询
3)查看查询类型:可用explain关键字查看该语句用到的查询类型。
4)全文索引的使用
格式为:select * from 表名 where match(列名1, 列名2) against(‘xxx xxx’);如下所示:
通过explain来分析这个sql语句
但是,为何查询字符’do’却查不到?
因为:MySQL中的全文索引,有两个变量,最小搜索长度和最大搜索长度。对于长度小于最小搜索长度和大于最大搜索长度的词语,都不会被索引。可通过命令“show variables like ‘%ft%’”查看,如下所示。
从中可知:ft_max_word_len为84,ft_min_word_len为4,表示MyISAM存储引擎允许搜索的字符范围是4~84。如何改变存储引擎的范围?全文索引的相关参数都无法进行动态修改,必须通过修改 MySQL 的配置文件” /etc/my.cnf”来完成,然后重启 MySQL 服务器,并修复全文索引(命令:repair table test quick),不然参数不会生效。
三、查询索引
(1)第一种方法:show keys from 表名
(2)第二种方法:show index from 表名
(3)第三种方法:desc 表名
四、删除索引
(1)第一种方法-删除主键索引:alter table
表名 drop primary;
(2)第二种方法-删除其它索引:其它索引的删除,不能删除主键索引:alter table 表名 drop index 索引名;
(3)第三种方法:drop index 索引名 on 表名