索引的两种
索引的类型:索引分为聚簇索引和非聚簇索引两种,聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快。
索引的类型
创建索引时字段的length是指索引的长度,可以不指定,length只对字符串类型的字段有效。
普通索引
是最基本的索引,它没有任何限制。它有以下几种创建方式:
直接创建索引
CREATE INDEX index_name ON table(column(length))
创建主键
alter table tax_source add primary key (id);
删除主键
alter table tax_source drop primary key;
修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
创建表的时候同时创建索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
);
唯一索引(unique index)
CREATE UNIQUE INDEX indexName ON table(column(length))
主键索引(primary key)
是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引:
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) NOT NULL ,
PRIMARY KEY (`id`)
);
组合索引
组合索引遵循最左原则
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);
全文索引
主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用,而不是一般的where语句加like。它可以在create table,alter table ,create index使用,不过目前只有char、varchar,text 列上可以创建全文索引。值得一提的是,在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。
- 创建表的时候添加全文索引
CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(255) CHARACTER NOT NULL , `content` text CHARACTER NULL , `time` int(10) NULL DEFAULT NULL , PRIMARY KEY (`id`), FULLTEXT (content) );
- 修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)
- 直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)
索引相关sql语句
删除索引
DROP INDEX index_name ON table
什么情况不会用到索引
- 一条查询语句只会使用一个索引
- 当列含有null值,则不会使用索引
like "xxx%"
会使用索引,但是 “%xxx%” 则不会使用索引- 列在被使用计算、函数的时不会使用索引
not in
,not exists
, (<> 不等于 !=) 这些不会使用索引- < 小于 > 大于 <= >= 这个根据实际查询数据来判断,如果全盘扫描速度比索引速度要快则不走索引
- 索引的最左前缀原则,如果有(a,b)索引,查询where a会使用索引,但是where b则不会使用索引
- 索引的最左前缀原则2:如果有(a,b,c,d)索引, a = 1 and b = 2 and c > 3 and d = 4 将在匹配c时不使用索引,但是如果索引是(a, b, d, c)则会全部使用索引,因为:mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配
- where or 语句将不适用索引,如果or的字段一样,可以改为 where in 语句, 如
SELECT * FROM T1 WHERE a=3 OR a=5
可以改为SELECT * FROM T1 WHERE a in (3, 5)
, 也可以使用union代替or:(SELECT * FROM T1 WHERE a=5) UNION (SELECT * FROM T1 WHERE b=5)
索引的优点
- 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
- 可以大大加快数据的检索速度,这也是创建索引的最主要的原因
- 在需要连接的字段上设置索引,可以加快连接查询
- 可以加快
group by
和排序order by
的速度
索引的缺点
- 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加
- 索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间
- 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度
什么时候应该创建索引
- 在经常需要作为查询条件的列上,可以加快搜索的速度
- 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度
- 在经常需要根据范围进行搜索、或者进行排序、或者分组的列上建索引,因为索引已经排序,其指定的范围是连续的
什么时候不应该创建索引
- 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求
- 对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度
- 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少
- 当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少 索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引
优化索引
- 使用较短的length
- 即时删除不再使用的索引