MySQL优化系列（三）--索引的使用、原理和设计优化

一、索引的概述和使用：
（1）概述：
1）什么是索引？
索引是一种特殊的文件（innodb数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。
更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度，在没有索引的情况下，数据库会遍历全部数据后选择符合条件的；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。
索引的性质分类：
索引分聚簇索引和非聚簇索引。聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了。局促索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。
2）索引的优点：
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；
可以大大加快数据的检索速度，这也是创建索引的最主要的原因；
可以加快表和表之间的连接，特别是实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；
通过使用索引，可以再查询的过程中，使用优化隐藏器，提高系统的性能。
3）索引的缺点：
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间会更大
当对表中的数据进行增加删除、修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
4）为什么需要索引
数据在磁盘上是以块的形式存储的，为确保对磁盘操作的原子性，访问数据的时候回一并访问所有的数据块。磁盘上的这些数据块与链表相似，即他们都包含一个数据段和一个指针，指针指向下一个节点（内存块）的内存地址，而且他们都不需要连续存储（即逻辑上相邻的数据块在物理上可以相隔很远）
鉴于很多记录只能做到按一个字段排序，所以要查询某个未经排序的字段，就需要使用线性查找，即要访问N/2个数据块，其中N指的是一个表所涵盖的所有数据块。如果该字段是非键字段（也就是说，不包含唯一值），那么就要搜索整个表空间，即要访问全部N个数据块。（在某些情况下，索引可以避免排序操作。）

然而，对于经过排序的字段，可以使用二分查找，因此只要访问log2 N个数据块。同样，对于已经排过序的非键字段，只要找到更大的值，也就不用再搜索表中的其他数据块了。这样一来，性能就会有实质性的提升。
（2）索引的用法
创建索引

//第一种方式：
//在执行CREATE TABLE 时创建索引：（硬设一个id索引）
CREATE TABLE `black_list` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`black_user_id` BIGINT(20) NULL DEFAULT NULL,
	`user_id` BIGINT(20) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
    INDEX indexName (black_user_id(length))
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

第二种方式：使用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)//这条语句创建的索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)。 
    ALTER TABLE table_name ADD PRIMARY KEY (column_list)//该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
    ALTER TABLE table_name ADD FULLTEXT index_name(olumu_name);该语句指定了索引为FULLTEXT，用于全文索引。

第三种方式
使用CREATE INDEX命令创建
CREATE INDEX可对表增加普通索引或UNIQUE索引。
不能用CREATE INDEX语句创建PRIMARY KEY索引

//标准语句：
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可以再alter table 内部作为语句处理

drop index [index_name] on [table_name]
alter table [table_name] drop index [index_name]
alter table [table_name] drop primary key

从表中删除某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。
查看索引
show index from [table_name]
show keys from [table_name]

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：注释
**二、索引的基本原理：
举例解析基本原理：
整体性原理例子：**通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。
**存储性质:**由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。
由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。
索引的数据结构：B+tree

B树：
B树中每个节点包含了键值和键值对于的数据对象存放地址指针，所以成功搜索一个对象可以不用到达树的叶节点。
成功搜索包括节点内搜索和沿某一路径的搜索，成功搜索时间取决于关键码所在的层次以及节点内关键码的数量。

在B树中查找给定关键字的方法是：首先把根结点取来，在根结点所包含的关键字K1,…,kj查找给定的关键字（可用顺序查找或二分查找法），若找到等于给定值的关键字，则查找成功；否则，一定可以确定要查的关键字在某个Ki或Ki+1之间，于是取Pi所指的下一层索引节点块继续查找，直到找到，或指针Pi为空时查找失败。
B+tree性质：
1.）n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。
2.）所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
3.）所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
B+树非叶节点中存放的关键码并不指示数据对象的地址指针，非叶节点只是索引部分。所有的叶节点在同一层上，包含了全部关键码和相应数据对象的存放地址指针，且叶节点按关键码从小到大顺序链接。如果实际数据对象按加入的顺序存储而不是按关键码次数存储的话，叶节点的索引必须是稠密索引，若实际数据存储按关键码次序存放的话，叶节点索引时稀疏索引。

B+ 树中，数据对象的插入和删除仅在叶节点上进行。

B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。
B+tree有两种搜索方法：
1）一种是按叶节点自己拉起的链表顺序搜索。
2）一种是从根节点开始搜索，和B树类似，不过如果非叶节点的关键码等于给定值，搜索并不停止，而是继续沿右指针，一直查到叶节点上的关键码。所以无论搜索是否成功，都将走完树的所有层。
这两种处理索引的数据结构的不同之处：（B和B+树）
1）B树中同一键值不会出现多次，并且它有可能出现在叶结点，也有可能出现在非叶结点中。
而B+树的键一定会出现在叶结点中，并且有可能在非叶结点中也有可能重复出现，以维持B+树的平衡。

2）因为B树键位置不定，且在整个树结构中只出现一次
虽然可以节省存储空间，但使得在插入、删除操作复杂度明显增加。B+树相比来说是一种较好的折中。

3）B树的查询效率与键在树中的位置有关
最大时间复杂度与B+树相同(在叶结点的时候)，最小时间复杂度为1(在根结点的时候)。而B+树的时候复杂度对某建成的树是固定的。

三、索引分类：
一）普通索引：
基本的索引，它没有任何限制。
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
二）唯一索引：
与前面的普通索引类似，不同的就是：MySQL数据库索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
三）主键索引：
它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引
四）全文索引：（FULLTEXT）
定义：
全文检索是对大数据文本进行索引，在建立的索引中对要查找的单词进行进行搜索，定位哪些文本数据包括要搜索的单词。因此，全文检索的全部工作就是建立索引和在索引中搜索定位，所有的工作都是围绕这两个来进行的。

此索引关键：
建立全文索引中有两项非常重要，一个是如何对文本进行分词，一是建立索引的数据结构。分词的方法基本上是二元分词法、最大匹配法和统计方法。索引的数据结构基本上采用倒排索引的结构。分词的好坏关系到查询的准确程度和生成的索引的大小。

应用：
FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。

但是要注意：对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。因为！！插入修改删除表的同时也要针对索引做一系列的处理。
SQL使用全文索引的方法：首先必须是MyISAM的数据库引擎的数据表
MySQL自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。
注意：
目前，使用MySQL自带的全文索引时，如果查询字符串的长度过短将无法得到期望的搜索结果。MySQL全文索引所能找到的词的默认最小长度为4个字符。另外，如果查询的字符串包含停止词，那么该停止词将会被忽略。

如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。
五）单列索引与多列索引（其实是相当于一个用法技巧）
单列索引，就是平常的只索引一个一个的字段的方式
多列索引（也叫组合索引）：
相关概念（适用多列索引的原因）：
MySQL能在多个列上创建索引。一个索引可以由最多15个列组成。（在CHAR和VARCHAR列上，你也可以使用列的前缀作为一个索引的部分)。

一个多重列索引可以认为是包含通过合并(concatenate)索引列值创建的值的一个排序数组。

多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格（获得结果集记录数最少）的索引。

当你为在一个WHERE子句索引的第一列指定已知的数量时，MySQL以这种方式使用多重列索引使得查询非常快速，即使你不为其他列指定值。

适用场景：
1.全字段匹配
2.匹配部分最左前缀
3.匹配第一列
4.匹配第一列范围查询(可用用like a%,但不能使用like %b)
5.精确匹配某一列和和范围匹配另外一列
注意：
在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
组合索引（多列索引）的原则：
原则：
最左前缀：顾名思义，就是最左优先
平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引（多列索引）。例如上面使用的例子就相当于创建了(height)单列索引，(height,name)组合索引以及(height,name,age)组合索引。

聚集索引和非聚集索引
聚集索引不是一种单独的索引类型，而是一种数据存储方式（不是数据结构，而是存储结构）
1）聚集索引：相关概念说法取自此处
定义：
该索引中键值的逻辑顺序决定了表中相应行的物理顺序。

聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。

注意事项：
定义聚集索引键时使用的列越少越好。

使用的场景：
一）包含大量非重复值的列。

二）使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <=。

三）被连续访问的列。

四）返回大型结果集的查询。

五）经常被使用联接或 GROUP BY 子句的查询访问的列；一般来说，这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列进行索引，可以使 SQL Server 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。

六）OLTP 类型的应用程序，这些程序要求进行非常快速的单行查找（一般通过主键）。

缺点：请看此博客
不适用于：

频繁更改的列 。这将导致整行移动（因为 SQL Server 必须按物理顺序保留行中的数据值）。这一点要特别注意，因为在大数据量事务处理系统中数据是易失的。

宽键 。来自聚集索引的键值由所有非聚集索引作为查找键使用，因此存储在每个非聚集索引的叶条目内。

innodb的聚簇索引实际上在同一个结构中保存了btree索引和数据行。叶子页包含了行的全部数据，但是节点页只包含了索引值的索引。innodb将通过主键聚集数据，如果没有定义主键，Innodb会选择第一个非空的唯一索引代替，如果没有非空唯一索引，Innodb会隐式定义一个6字节的rowid主键来作为聚集索引。innodb只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距甚远。
要注意：聚簇主键可能对性能有帮助，但也可能导致严重的性能问题，尤其是将表的存储引擎从innodb转换成其他引擎的时候。
2）非聚集索引：
定义：
数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。

非聚集索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。对于非聚集索引，可以为在表非聚集索引中查找数据时常用的每个列创建一个非聚集索引。有些书籍包含多个索引。例如，一本介绍园艺的书可能会包含一个植物通俗名称索引，和一个植物学名索引，因为这是读者查找信息的两种最常用的方法。
非聚集索引叶子节点中保存的不是指向行的物理位置的指针，而是行的主键值。这意味着通过非聚集索引查找行，存储引擎需要找到非聚集索引的叶子节点获得对应的主键值，然后根据这个主键值去聚集索引中查找对应的行，这里做了重复的工作，两次btree查找而不是一次，对于innodb，自适应哈希索引能减少这样的重复工作。
选择使用的场景：

关于聚集索引以及非聚集索引的几个问题：
一）聚集索引的约束是唯一性，是否要求字段也是唯一的呢？
一般我们指定一个表的主键，如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，SQL会默认在此字段上创建一个聚集索引，而主键都是唯一的，所以理所当然的认为创建聚集索引的字段也需要唯一。

聚集索引可以创建在任何一列你想创建的字段上，这是从理论上讲，实际情况并不能随便指定，否则在性能上会是恶梦。

二）|主键就是聚集索引？？？
这样有时会对聚集索引的一种浪费。Innodb将通过主键聚集数据，如果没有定义主键，Innodb会选择第一个非空的唯一索引代替，如果没有非空唯一索引，Innodb会隐式定义一个6字节的rowid主键来作为聚集索引。innodb只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距甚远。

因为每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。

使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。在实际应用中，因为 ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次，让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则；当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。

三）是不是聚集索引就一定要比非聚集索引性能优呢？？？
如果想查询学分在60-90之间的学生的学分以及姓名，在学分上创建聚集索引是否是最优的呢？

答：否。既然只输出两列，我们可以在学分以及学生姓名上创建联合非聚集索引（也就是多列索引），此时的索引就形成了覆盖索引，即索引所存储的内容就是最终输出的数据，这种索引在比以学分为聚集索引做查询性能更好。

四）在数据库中通过什么描述聚集索引与非聚集索引的？
索引是通过二叉树的形式进行描述的，我们可以这样区分聚集与非聚集索引的区别：聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节仍然是索引节点，但它有一个指向最终数据的指针。

五）在主键是创建聚集索引的表在数据插入上为什么比主键上创建非聚集索引表速度要慢？
在有主键的表中插入数据行，由于有主键唯一性的约束，所以需要保证插入的数据没有重复。我们来比较下主键为聚集索引和非聚集索引的查找情况：聚集索引由于索引叶节点就是数据页，所以如果想检查主键的唯一性，需要遍历所有数据节点才行，但非聚集索引不同，由于非聚集索引上已经包含了主键值，所以查找主键唯一性，只需要遍历所有的索引页就行，这比遍历所有数据行减少了不少IO消耗。这就是为什么主键上创建非聚集索引比主键上创建聚集索引在插入数据时要快的真正原因。
innodb和myisam物理存储的数据分布对比：

myisam：

是按照数据插入的顺序存储在磁盘上的，myisam中的主键索引和二级索引在结构上并没有什么不同，主键索引就是一个名为primary的唯一非空索引。

innodb：

因为innodb支持聚集索引，所以使用非常不同的方式存储同样的数据，innodb聚集索引包含了整个表的数据，而不是只有索引，因为在Innodb中，聚集索引就是表，所以不像myisam那样需要独立的行存储。聚集索引的每一个叶子节点都包含了主键值，事务ID，用于事务和MVCC的回滚指针以及所有剩余列的值，如果主键是一个列前缀索引，innodb也会包含完整的主键列和剩下的列的值。

还有一点和myisam不同的是，innodb的二级索引和聚集索引很不同，innodb二级索引的叶子节点中存储的不是行指针，而是主键值，并以此作为指向行的指针，这样的策略减少了当出现行移动或者数据页的分裂时二级索引的维护工作，使用主键值当做指针会让二级索引占用更多的空间，换来的好处是，innodb在移动行时无须更新二级索引中的这个指针。

在innodb表中按主键顺序插入行，如果正在使用Innodb表并且没有什么数据需要聚集，那么可以定义一个代理键作为主键，这种主键的数据应该和应用无关，最简单的方法是使用auto_increment自增列，这样可以保证数据行是按顺序插入的，对于根据主键做关联操作的性能也会更好。

不要使用UUID来作为聚集索引，否则性能会很糟糕，因为它使得聚集索引的插入变得完全随机，使得数据没有任何聚集特性。因为UUID作为主键插入行不仅花费的时间更长，而且索引也更大，这一方面是因为主键字段变长了，另外一方面毫无疑问是由于页分裂导致时间变长和碎片导致的索引变大。因为主键的值是顺序的，所以Innodb把每一条记录都存储在上一条记录的后面，当达到页的最大填充因子时（innodb默认的最大填充因子是页大小的十六分之十五，留出部分空间用于以后修改），下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近似被顺序的记录填满，这也正是所期望的结果（然而，二级索引页可能是不一样的）。
　　在UUID主键下，因为新插入行的主键值不一定比前面的大，所以innodb无法简单地总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置，通常是已有数据的中间位置，并且分配新的空间，这会增加很多额外的工作，并导致数据分布不够优化，下面是使用UUID作为主键的一些缺点：

A：写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，innodb在插入前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO

B：因为写入是乱序的，innodb不得不频繁地做页分裂操作，以便为新的行分配空间，页分裂会导致移动大量数据，一次插入最少需要修改三个页不是一个页

C：由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片
把这些随机值载入到聚集索引之后，也许需要做一次optimize table来重建表并优化页的填充。使用innodb时应该尽可能地按照主键顺序插入数据，并且尽可能地使用简单增加的聚簇键的值来插入新行。
注：顺序的主键什么时候会造成更坏的结果？

对于高并发工作负载，在Innodb中按主键顺序插入可能会造成明显的争用，主键的上界会称为热点，因为所有的插入都发生在这里，所以并发插入可能导致间隙锁争用，另一个热点可能是auto_increment锁机制，如果遇到这个问题，则可能需要重新设计表或者应用，或者更改innodb_autoinc_lock_mode配置。
　　四、索引设计优化：
（1）索引建立的几大原则：
1） 最左前缀匹配原则
非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）=和in可以乱序
比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3）尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)
表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

4）索引列不能参与计算，保持列“干净”
比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5）尽量的扩展索引，不要新建索引。
比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。
7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
8）对于定义为text、image和bit的数据类型的列不要建立索引。
9）对于经常存取的列避免建立索引
（2）索引使用的注意点：
一、）一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDER BY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况。
**二、）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。**如：

select id from t where num is null 

最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。
不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
*****三、）*应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
*****四、）*应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。如：
**select id from t where num=10 or Name = 'fuzhu'**

可以这样查询，充分利用索引：

**select id from t where num = 10
union all
select id from t where Name = 'fuzhu'** 

五、）in 和 not in 也要慎用，否则会导致全表扫描。
而且负向查询（not , not in, not like, <>, != ,!>,!< ） 不会使用索引

**select id from t where num in(1,2,3)** 

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

很多时候用 exists 代替 in 是一个好的选择，当然exists也不跑索引。

select num from a where num in(select num from b) 

正上面的，用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num) 

*****六、）*下面的模糊查询也将导致全表扫描：

select id from t where name like ‘%abc%’ 

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。

若要提高效率，可以考虑全文检索。

既然谈到模糊查询下使用索引，我们就顺便详细地讲讲吧。

1. like %keyword 索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全表扫描。例子在此处

2. like keyword% 索引有效。

3. like %keyword% 索引失效，也无法使用反向索引。
   //可以拿我给出的数据库试一下嘛。然后用explain测试，就能测出有没有走索引了

   select * from table where code like ‘Classify_Description%’
   select * from table where code like ‘%Classify_Description%’
   select * from table where code like ‘%Classify_Description’

*****七、）*如果在 where 子句中使用参数，也会导致全表扫描。
因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num = @num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num = @num
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2 = 100 
正上面的应改为：
select id from t where num = 100*2 

八、）应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3) = ’abc’ //name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′) = 0 -–‘2005-11-30’ //生成的id
应改为:
select id from t where name like ‘abc%’
select id from t where createdate >= ‘2005-11-30’ and createdate < ‘2005-12-1’
九、）.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
十、）在使用索引字段作为条件时，如果该索引是复合索引（多列索引），那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
十一、）索引并不是越多越好
索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

十二、）应尽可能的避免更新 clustered 索引数据列
因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

十三、）尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
十四、）MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。
因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。