索引

索引是存储在一张表中特定列上的数据结构，索引是在列上创建的。并且，索引是一种数据结构。

索引作用

在索引列上，除了有序查找之外，数据库利用各种各样的快速定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。

索引类型

主键索引：PRIMARY KEY，PRIMARY KEY索引和UNIQUE索引非常类似。事实上，PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY，因为一个表中不可能具有两个同名的索引。
唯一索引：UNIQUE，单列或多列
普通索引
复合索引
前缀索引

索引方法

全局索引(FULLTEXT)：全局索引，MyISAM 引擎支持全局索引, Innodb（MySQL 5.6以上支持），它的出现是为了解决针对文本的模糊查询效率较低的问题。

哈希索引(HASH)：哈希索引是 MySQL 中用到的唯一 key-value 键值对的数据结构，很适合作为索引。HASH 索引具有一次定位的好处，不需要像树那样逐个节点查找，但是这种查找适合应用于查找单个键的情况，对于范围查找，HASH 索引的性能就会很低。

B-Tree 索引：B 就是 Balance 的意思，BTree 是一种平衡树，它有很多变种，最常见的就是 B+ Tree，它被 MySQL 广泛使用。mysql中主要的索引类型。

R-Tree 索引：R-Tree 在 MySQL 很少使用，仅支持 geometry 数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种，相对于 B-Tree 来说，R-Tree 的优势在于范围查找。

索引创建规则：

1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引的字段必须是经常作为查询条件的字段;
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
A、正确选择复合索引中的主列字段，一般是选择性较好的字段；
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
F、如果索引多个字段，第一个字段要是经常作为查询条件的。如果只有第二个字段作为查询条件，这个索引不会起到作用;
8、频繁进行数据操作的表，不要建立太多的索引；
9、删除无用的索引，避免对执行计划造成负面影响；
10、不要过度索引。不要以为 索引 “ 越多越好 ” ，什么东西都用索引是错的。每个额外的 索引都要占用额外的磁盘空间，并降低写操作的性能。如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是，则就不要费力去增加这个索引了，因为已经有了。
11、考虑在列上进行的比较类型。索引可用于 “ < ” 、 “ < = ” 、 “ = ” 、 “ > = ” 、 “ >” 和 BETWEEN 运算。在模式具有一个直接量前缀时（第一个字符不是通配符的情况下），索引也用于 LIKE 运算。
12、对于值唯一不重复的列要添加唯一索引，可以更快速的通过该索引来确定某条记录。唯一索引是最有效的。
13、WHERE字句的查询条件里使用了函数（如：WHERE DAY(column)=…），MYSQL将无法使用索引
14、如果某个数据列里包含着许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含了净是
些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。

索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，
一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。

创建索引：

1、执行CREATE TABLE语句时可以创建索引：
实例：

  1. CREATE TABLE `log_day_url_body` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `body` varchar(255) NOT NULL DEFAULT '',
  4.   `p_id` int(11) NOT NULL DEFAULT '0',
  5.   `plt_id` int(3) NOT NULL DEFAULT '1',
  6.   PRIMARY KEY (`id`),
  7.   KEY `body` (`body`),
  8.   KEY `categories` (`body`,`plt_id`,`p_id`),
  9.   UNIQUE KEY `url` (`id`,`plt_id`,`p_id`),
  10.   UNIQUE KEY `url_3` (`id`)
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='注释语句'

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)

table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

3、单独用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)
'添加FULLTEXT(全文索引)'
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

删除索引：

可利用ALTER TABLE或DROP INDEX语句来删除索引。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

其中，前两条语句是等价的，删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。
如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。
如果删除组成索引的所有列，则整个索引将被删除。

查看索引：

show index from tblname;
'或'
show keys from tblname;

查询结果列：

· Table
表的名称。
· Non_unique
如果索引不能包括重复词，则为0。如果可以，则为1。
· Key_name
索引的名称。
· Seq_in_index
索引中的列序列号，从1开始。
· Column_name
列名称。
· Collation
列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
· Cardinality
索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
· Sub_part
如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
· Packed
指示关键字如何被压缩。如果没有被压缩，则为NULL。
· Null
如果列含有NULL，则含有YES。如果没有，则该列含有NO。
· Index_type
用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
· Comment
一些关于过引的常用语句

索引使用注意事项：

1、不要对索引字段进行运算，而要想办法做变换，比如

SELECT ID FROM T WHERE NUM/2=100
'应改为:'
SELECT ID FROM T WHERE NUM = 100*2
SELECT ID FROM T WHERE NUM/2 = NUM1
'如果NUM有索引应改为:'
SELECT ID FROM T WHERE NUM = NUM1*2
如果NUM1有索引则不应该改。

SELECT 年,月,金额 FROM 结余表 WHERE 100*年+月=2010*100+10

'改为：'

SELECT 年,月,金额 FROM 结余表 WHERE 年=2010 AND月=10

2、不要对索引字段进行格式转换


'日期字段的例子'

WHERE CONVERT(VARCHAR(10), 日期字段, 120) = '2010-07-15'

'应该改为'

WHERE日期字段〉='2010-07-15' AND 日期字段

'ISNULL转换的例子：'

WHERE ISNULL(字段,'12')<>'12'应改为:WHERE字段<>'12'
WHERE ISNULL(字段,'')=''不应修改
WHERE ISNULL(字段,'F') ='T'应改为: WHERE字段='T'
WHERE ISNULL(字段,'F')<>'T'不应修改

3、不要对索引字段使用函数

WHERE LEFT(NAME, 3)=’ABC’ 或者WHERE SUBSTRING(NAME,1, 3)=’ABC’
应改为: WHERE NAME LIKE ‘ABC%’

日期查询的例子：

WHERE DATEDIFF(DAY, 日期,’2010-06-30′)=0
应改为:WHERE 日期>=’2010-06-30′ AND 日期

WHERE DATEDIFF(DAY, 日期,’2010-06-30′)>0
应改为:WHERE 日期

WHERE DATEDIFF(DAY, 日期,’2010-06-30′)>=0
应改为:WHERE 日期

WHERE DATEDIFF(DAY, 日期,’2010-06-30′)　　
应改为:WHERE 日期>=’2010-07-01′

WHERE DATEDIFF(DAY, 日期,’2010-06-30′)
应改为:WHERE 日期>=’2010-06-30′

4、不要对索引字段进行多字段连接

比如：

WHERE FAME+ ‘. ‘+LNAME=’HAIWEI.YANG’
应改为:
WHERE FNAME=’HAIWEI’ AND LNAME=’YANG’

5、索引的列如果是表达式的一部分或者是函数的参数，则失效。

6、针对特别长的字符串，可以使用前缀索引，根据索引的选择性选择合适的前缀长度。

7、索引应该建在小字段上，对于大的文本字段甚至超长字段，最好不要建索引，实在要建的话可以用前缀索引

8、使用多列索引的时候，可以通过 AND 和 OR 语法连接。

9、重复索引没必要，如（A，B）和（A）重复。

10、索引在where条件查询和group by语法查询的时候特别有效。

11、将范围查询放在条件查询的最后，防止范围查询导致的右边索引失效的问题。

12、索引最好不要选择过长的字符串，而且索引列也不宜为null。

13、索引的字段必须是经常作为查询条件的字段;经常出现在Where子句中的字段，特别是大表的字段，应该建立索引

14、如果索引多个字段，第一个字段要是经常作为查询条件的。如果只有第二个字段作为查询条件，这个索引不会起到作用;

15、不要过度索引，不要以为 索引 “ 越多越好 ” ，什么东西都用索引是错的。每个额外的 索引都要占用额外的磁盘空间，并降低写操作的性能。如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是，则就不要费力去增加这个索引了，因为已经有了。

16、对于值唯一不重复的列要添加唯一索引，可以更快速的通过该索引来确定某条记录。唯一索引是最有效的

17、频繁进行数据操作的表，不要建立太多的索引；

18、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

> 1、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

> 2、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

> 3、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；

> 4、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

> 5、如果索引多个字段，第一个字段要是经常作为查询条件的。如果只有第二个字段作为查询条件，这个索引不会起到作用;

索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下， 
一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。

19、对于具体索引有效性检查， 可以用explain 查看sql语句执行计划

20、对于索引，若表字段类型与sql语句的字段类型不一致则索引无效；

如：若 test1127 表中 student_id 为varchar型，但下面语句中当做int型查询，即使有索引也不会生效。

select name, score from test1127 where student_id = 123 and type = 2 and grand in (3,4,5);

应改为：

select name, score from test1127 where student_id = ‘123’ and type = 2 and grand in (3,4,5);

21、!=号是使用索引的。

22、 >, <, IN() 是使用索引的。

23、使用 LIKE %abc% 不会走索引，而使用 LIKE abc% 会走索引。前缀索引的使用。

使用多索引

MySQL可以使用超过1个索引

“索引合并”

SELECT * FROM TBL WHERE A=5 AND B=6
– 可以分别使用索引 KEY(A)和 KEY(B)

索引 KEY(A,B) 是更好的选择

SELECT * FROM TBL WHERE A=5 OR B=6
– 两个索引同时分别被使用

索引 KEY(A,B) 在这个查询中无法使用

前缀索引

你可以在字段最左前缀建立索引

ALTER TABLE TITLE ADD KEY(TITLE(20));

需要对BLOB/TEXT类型的字段建立索引

能显著的减少空间使用

不能用于覆盖索引

选择前缀长度成为一个问题

索引有效性检查 explain

explain select * from table where id=1;

EXPLAIN SELECT * FROM users WHERE id=6;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | users | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.03 sec)

下面我们就来看看这个EXPLAIN分析结果的含义:

table：这是表的名字。
type：连接操作的类型。

主要包含如下集中类型；
◇ all：全表扫描
◇ const：读常量，且最多只会有一条记录匹配，由于是常量，所以实际上只需要读一次；
◇ eq_ref：最多只会有一条匹配结果，一般是通过主键或者唯一键索引来访问；
◇ fulltext：
◇ index：全索引扫描；
◇ index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行merge 之后再读
取表数据；
◇ index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个
主键或者唯一索引；
◇ rang：索引范围扫描；
◇ ref：Join 语句中被驱动表索引引用查询；
◇ ref_or_null：与ref 的唯一区别就是在使用索引引用查询之外再增加一个空值的查询；
◇ system：系统表，表中只有一行数据；
◇ unique_subquery：子查询中的返回结果字段组合是主键或者唯一约束；

从好到差 system,const,eq_ref,ref,range,index,ALL

possible_keys：可能可以利用的索引的名字。
Key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。
key_len：索引中被使用部分的长度，以字节计。
ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行。
rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1。
Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响。
ID：Query Optimizer 所选定的执行计划中查询的序列号；
Select_type：所使用的查询类型