数据库索引

索引

什么是索引？
MySQL 官方对索引的定义为：索引 Index 是帮助 MySQL 高效获取数据的数据结构。

索引的本质：索引是数据结构。

数据库查询是数据库的最主要功能之一，希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。

MySQL 目前主要有普通索引、唯一索引、主键索引、组合索引和全文索引共 5 种索引类型。

普通索引

普通索引是 MySQL 中的基本索引类型，允许在定义索引的列中插入重复值和空值。

 CREATE INDEX index_name ON table(column(length)) ALTER TABLE table_name ADD INDEX index_name ON (column(length)) DROP INDEX index_name ON table

唯一索引

唯一索引的索引列的值必须唯一，但允许空值。如果是组合索引，则列的值得纸盒必 须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

主键索引

主键索引，一种特殊的唯一索引，不允许有空值，一般在建表的时候同时建立主键索引。

单列索引

单列索引和组合索引的划分是根据创建索引时所引用的列的数量来划分。

单列索引是只一个索引只包含单个列，一个表可以有多个单列索引。

组合索引

组合索引指在表的多个字段组合上创建索引，但只有在查询条件中使用了这些字段的 左边字段时，索引才会被使用。

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

全文索引

全文索引类型为 FULLTEXT，在定义索引的列上至此值需得全文查找，允许在这些索引 列上插入空值和重复值。

空间索引

空间索引可以用作地理数据存储，通过R树来实现。空间索引会从所有维度来索引数据，使得空间搜索变得高效。查询时可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数来维护数据。

索引的优点

• 可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性

• 建立索引可以大大提高检索的数据,以及减少表的检索行数

• 在表连接的连接条件 可以加速表与表直接的相连

• 在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)

• 建立索引,在查询中使用索引 可以提高性能，但是会影响修改的性能

索引的缺点

• 在创建索引和维护索引 会耗费时间,随着数据量的增加而增加，所以一般建议一个表上创建的索引数不要超过6个

• 索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间

• 当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)

注意事项
1、索引不会包含有 null 值的列，在数据库设计时不要让索引字段的默认值为 null。
2、使用短索引
3、索引列排序4、like 语句操作， like “%aaa%” 不会使用索引而 like “aaa%”可以使用索引。
5、不要在列上进行运算
6、不使用 not in 和<>操作

创建索引

创建索引的方式有直接创建、修改表添加和创建时直接创建三种

直接创建

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name 
(column(length),…) [ASC|DESC]

-- 普通索引
create index bknameIndex on book(bookname) ;
-- 唯一索引
create unique index unique_Index on book(bookId) ;
--单列索引
create index single_Index on book(comment(10)) ;
-- 多列索引
create index mutil_Index on book(authors(20) , info(20)) ;
--全文索引
create fulltext index fulltext_Index on book(info) ;

修改表添加

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] 
index_name (column(length),…) [ASC|DESC]

-- 普通索引
alter table book add index bkName(bookname(30)) ;
--唯一索引
alter table book add unique index uniqueIndex(bookId) ;
--单列索引
alter table book add index signalIndex2 on(comment(50)) ;
--多列索引
alter table book add index mutilIndex (authors(30) , info(50)) ;
--创建全文索引
alter table book add fulltext index fulltextIndex (info) ;

直接创建

CREATE TABLE table_name (
……（创建字段和约束）,
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [INDEX|KEY] [index_name] (col_name 
[length]) [ASC|DESC]
)

	-- 普通索引
create table book(
 ……
index pub_index(year_publication)
);
-- 唯一索引
create table book(
 ……
 unique index pub_index(bookid));
-- 单列索引
create table book(
 ……
 index single_index(authors)
);
-- 多列索引
create table book(
 ……
 index mutil_index(bookid, authors)
);
-- 全文索引,需要修改表的存储引擎为 MyISAM
create table book(
 ……
 fulltext index fulltext_index(info)
);

使用 ALTER TABLE 删除索引

ALTER TABLE table_name DROP INDEX index_name ;

有 AUTO_INCREMENT 约束的字段的唯一索引不能被删除
删除 book 表中名为 fulltext_Index 的索引 alter table book drop index fulltext_Index ;
使用 DROP INDEX 删除索引

DROP INDEX index_name ON table_name ;

删除 book 表中名为 fulltext_Index 的索引 drop index mutil_index on book ；

不适合创建索引

• 二进制多媒体数据
• 超大文本数据
• web 应用中活跃性大但是数据量不是很大的数据

索引的设计原则

• 索引设计不合理或者缺少索引都会对数据库性能造成不良影响
• 索引并非越多越好，一个表中如果有大量的索引，不仅浪费磁盘空间，而且会影响 INSERT、DELETE、UPDATE 等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更新
• 数据量小的表最好不要使用索引，由于数据量较小，查询所花费的时间可能比表里索引的时间还要短，索引可能不会产生优化的效果
• 避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。对经常用于查询的字段应该创建少量索引，但要避免添加不必要的字段
• 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列
  （例如性别字段，只有男女两个取值）不要建立索引
• 当唯一性是数据本身的特征时，指定唯一索引，可确保数据完整性并提高查询
• 速度在频繁进行排序、分组的列上建立索引，如果排序的列有多个，可以在这些列上建立组合索引。

使用索引的场景

在建立索引的时候应该考虑索引应该建立在数据库表中的某些列上面 哪一些索引需要建立,哪一些所以是多余的‘=
1、在经常需要搜索的列上,可以加快索引的速度
2、主键列上可以确保列的唯一性
3、在表与表的而连接条件上加上索引,可以加快连接查询的速度
4、在经常需要排序(order by),分组(group by)和的 distinct 列上加索引 可以加快排序查询的时间, (单独 order by 用不了索引，索引考虑加 where 或加 limit)
4、在一些 where 之后的 < <= > >= BETWEEN IN 以及某个情况下的 like 建立字段的索引(B-TREE)
5、like 语句的 如果你对 nickname 字段建立了一个索引.当查询的时候的语句是
nickname lick ‘%ABC%’ 那么这个索引讲不会起到作用.而 nickname lick ‘ABC%’ 那么将可以用到索引
6、索引不会包含 NULL 列,如果列中包含 NULL 值都将不会被包含在索引中,复合索引中如果有一列含有 NULL 值那么这个组合索引都将失效,一般需要给默认值 0 或者 ’ '字符串
7、使用短索引,如果你的一个字段是 Char(32)或者 int(32),在创建索引的时候指定前缀长度 比如前 10 个字符 (前提是多数值是唯一的…)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少 I/0 操作
8、不要在列上进行运算,这样会使得 mysql 索引失效,也会进行全表扫描
9、选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快