之前的文章一直在规避索引的建立去优化数据库，不是不想讲，而是这个太重要，必须抽出来讲。今天我们就来研究下数据库索引的设计与优化（MySQL为例）。
文章结构：（1）索引的概述和使用；（2）索引的基本原理；（3）索引分类；（4）索引设计优化
本系列：demo下载
（一）MySQL优化笔记（一）--库与表基本操作以及数据增删改
（二）MySQL优化笔记（二）--查找优化（1）（非索引设计）
（三）MySQL优化笔记（二）--查找优化（2）（外连接、多表联合查询以及查询注意点）
（四） MySQL优化笔记（三）--索引的使用、原理和设计优化
（五） MySQL优化笔记（四）--表的设计与优化（单表、多表）
（九） MySQL优化笔记（八）--锁机制超详细解析（锁分类、事务并发、引擎并发控制）
文章目录：
（1）索引的概述和使用
概述
- 什么是索引
- 索引的优点
- 索引的缺点
- 为什么需要索引
索引的使用（语法）
- 创建索引：（三种方式）
- 删除索引
- 查看索引
（2）索引的基本原理
整体性原理例子
针对存储性质讲解
索引的数据结构：B+tree
B+tree性质
B+tree有两种搜索方法
（3）索引分类
普通索引
唯一索引
主键索引
全文索引：（FULLTEXT）
单列索引与多列索引（其实是相当于一个用法技巧）(重点)
- 单列索引
- 多列索引
- 聚集索引和非聚集索引
- 聚集索引
- 非聚集索引
- 关于聚集索引以及非聚集索引的几个问题
（4）索引设计优化:
索引建立的几大原则
索引使用的注意点（大概有14点）
一、索引的概述和使用：
（1）概述：
1）什么是索引？
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。在没有索引的情况下，数据库会遍历全部数据后选择符合条件的；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。在没有索引的情况下，数据库会遍历全部数据后选择符合条件的；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。
索引的性质分类：
索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。
2）索引的优点：
一】通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
二】可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
三】可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
四】在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
五】通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
3）索引的缺点：
一】创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
二】索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
三】当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
4）为什么需要索引：
数据在磁盘上是以块的形式存储的。为确保对磁盘操作的原子性，访问数据的时候会一并访问所有数据块。磁盘上的这些数据块与链表类似，即它们都包含一个数据段和一个指针，指针指向下一个节点（数据块）的内存地址，而且它们都不需要连续存储（即逻辑上相邻的数据块在物理上可以相隔很远）。
鉴于很多记录只能做到按一个字段排序，所以要查询某个未经排序的字段，就需要使用线性查找，即要访问N/2个数据块，其中N指的是一个表所涵盖的所有数据块。如果该字段是非键字段（也就是说，不包含唯一值），那么就要搜索整个表空间，即要访问全部N个数据块。（在某些情况下，索引可以避免排序操作。）
然而，对于经过排序的字段，可以使用二分查找，因此只要访问log2 N个数据块。同样，对于已经排过序的非键字段，只要找到更大的值，也就不用再搜索表中的其他数据块了。这样一来，性能就会有实质性的提升。
（2）索引的使用（语法）：本文代码实例的针对此数据库
一】创建索引：（三种方式）
第一种方式：
//第一种方式：//在执行CREATE TABLE 时创建索引：（硬设一个id索引）CREATE TABLE `black_list` ( `id` BIGINT(20) NOTNULLAUTO_INCREMENT, `black_user_id` BIGINT(20)NULLDEFAULTNULL, `user_id` BIGINT(20)NULLDEFAULTNULL, 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 INDEXindex_name(column_list)//添加普通索引，索引值可出现多次。 ALTER TABLE table_name ADDUNIQUE(column_list)//这条语句创建的索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)。 ALTER TABLE table_name ADD PRIMARYKEY(column_list)//该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。ALTER TABLE table_name ADD FULLTEXTindex_name(olumu_name);该语句指定了索引为FULLTEXT，用于全文索引。//针对上述数据库，增加商品分类的索引ALTER table commodity_list ADD INDEXclassify_index(Classify_Description)
其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。
第三种方式：使用CREATE INDEX命令创建
CREATE INDEX可对表增加普通索引或UNIQUE索引。
//标准语句：CREATE INDEX index_name ONtable_name(column_list)CREATE UNIQUE INDEX index_name ONtable_name(column_list)//针对上述数据库：CREATE INDEX classify_index ONcommodity_list(Classify_Description)
table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。
二】删除索引：
删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：
DROP INDEX [indexName] ON [table_name];alter table [table_name] drop index [index_name] ;alter table [table_name] drop primary key ;//针对上述数据库drop index classify_index on commodity_list ;
其中，在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。
三】查看索引：
SHOWINDEXFROM[table_name];showkeysfrom[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：注释
二、索引的基本原理：
举例解析基本原理：
整体性原理例子：
除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。
SQL的应用场景会使用索引：
数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段......这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。
针对存储性质讲解：此部分转载自此博主此博客
由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。
由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。
预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。
索引的数据结构：B+tree此处部分转载自此博主此博客
这里写图片描述
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+树的时候复杂度对某建成的树是固定的。
这里写图片描述
上图展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。
三、索引分类：
一）普通索引：
基本的索引，它没有任何限制。
创建方式：
//标准语句：ALTER TABLE table_name ADD INDEXindex_name(column_list)CREATE INDEX index_name ONtable_name(column_list);//还有建表的时候创建亦可CREATE TABLEtable_name( ID INT NOT NULL, column_listVARCHAR(16)NOT NULL,INDEX [index_name ](column_list(length)) );
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
例子：假如length为10，也就是索引这个字段的记录的前10个字符。
二）唯一索引：
与前面的普通索引类似，不同的就是：MySQL数据库索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
它有以下几种创建方式：
ALTER TABLE table_name ADD UNIQUE (column_list)CREATE UNIQUE INDEX index_name ON table_name (column_list)//还有建表时创建CREATE TABLE table_name ( ID INT NOTNULL, column_list VARCHAR(16) NOTNULL, UNIQUE [index_name ] (column_list(length)) );
三）主键索引：
它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：
CREATE TABLE table_name ( ID INT NOTNULL, [column] VARCHAR(16) NOTNULL, PRIMARY KEY(ID) );
四）全文索引：（FULLTEXT）
定义：
全文检索是对大数据文本进行索引，在建立的索引中对要查找的单词进行进行搜索，定位哪些文本数据包括要搜索的单词。因此，全文检索的全部工作就是建立索引和在索引中搜索定位，所有的工作都是围绕这两个来进行的。
此索引关键：
建立全文索引中有两项非常重要，一个是如何对文本进行分词，一是建立索引的数据结构。分词的方法基本上是二元分词法、最大匹配法和统计方法。索引的数据结构基本上采用倒排索引的结构。分词的好坏关系到查询的准确程度和生成的索引的大小。
应用：
FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。
但是要注意：对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。因为！！插入修改删除表的同时也要针对索引做一系列的处理。
创建方法：
//针对content做了全文索引：CREATE TABLE `table` (`id`int(11) NOTNULLAUTO_INCREMENT ,`title`char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOTNULL,`content` text CHARACTER SET utf8 COLLATE utf8_general_ciNULL,PRIMARY KEY (`id`),FULLTEXT (content));
SQL使用全文索引的方法：首先必须是MyISAM的数据库引擎的数据表
如果是其他数据引擎，则全文索引不会生效。
SELECT * FROM article WHERE MATCH( content) AGAINST('想查询的字符串')
此外，MySQL自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。
注意：
目前，使用MySQL自带的全文索引时，如果查询字符串的长度过短将无法得到期望的搜索结果。MySQL全文索引所能找到的词的默认最小长度为4个字符。另外，如果查询的字符串包含停止词，那么该停止词将会被忽略。
如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。
五）单列索引与多列索引（其实是相当于一个用法技巧）
单列索引，就是平常的只索引一个一个的字段的方式
//例子为name列的头10个字符创建一个索引：CREATE TABLEtest(name CHAR(200)NOT NULL,KEYindex_name(name(10)));
多列索引（也叫组合索引）：
相关概念（适用多列索引的原因）：
MySQL能在多个列上创建索引。一个索引可以由最多15个列组成。（在CHAR和VARCHAR列上，你也可以使用列的前缀作为一个索引的部分)。
一个多重列索引可以认为是包含通过合并(concatenate)索引列值创建的值的一个排序数组。
多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格（获得结果集记录数最少）的索引。
当你为在一个WHERE子句索引的第一列指定已知的数量时，MySQL以这种方式使用多重列索引使得查询非常快速，即使你不为其他列指定值。
适用场景：
1.全字段匹配
2.匹配部分最左前缀
3.匹配第一列
4.匹配第一列范围查询(可用用like a%,但不能使用like %b)
5.精确匹配某一列和和范围匹配另外一列
例子：
//假设只使用单列索引名字ALTER TABLE people ADD INDEXname(name);//使用多列索引：ALTER TABLE people ADD INDEXheight_name_age(height,name,age);//相当于创建了(height)单列索引，(height,name)组合索引以及(height,name,age)组合索引/*
注意：
注：在mysql中执行查询时，只能使用一个索引，如果我们在name,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。
*/
注意：
在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
组合索引（多列索引）的原则：
原则：
最左前缀：顾名思义，就是最左优先
平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引（多列索引）。例如上面使用的例子就相当于创建了(height)单列索引，(height,name)组合索引以及(height,name,age)组合索引。
此外，补充一个概念对比，那就是聚集索引和非聚集索引：
1）聚集索引：相关概念说法取自此处
定义：
该索引中键值的逻辑顺序决定了表中相应行的物理顺序。
聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。
注意事项：
定义聚集索引键时使用的列越少越好。
使用的场景：
一）包含大量非重复值的列。
二）使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <=。
三）被连续访问的列。
四）返回大型结果集的查询。
五）经常被使用联接或 GROUP BY 子句的查询访问的列；一般来说，这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列进行索引，可以使 SQL Server 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。
六）OLTP 类型的应用程序，这些程序要求进行非常快速的单行查找（一般通过主键）。
缺点：请看此博客
不适用于：
频繁更改的列 。这将导致整行移动（因为 SQL Server 必须按物理顺序保留行中的数据值）。这一点要特别注意，因为在大数据量事务处理系统中数据是易失的。
宽键 。来自聚集索引的键值由所有非聚集索引作为查找键使用，因此存储在每个非聚集索引的叶条目内。
2）非聚集索引：
定义：
数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。
非聚集索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。对于非聚集索引，可以为在表非聚集索引中查找数据时常用的每个列创建一个非聚集索引。有些书籍包含多个索引。例如，一本介绍园艺的书可能会包含一个植物通俗名称索引，和一个植物学名索引，因为这是读者查找信息的两种最常用的方法。
两者的区别此处有个很清晰的例子：请点此处
选择使用的场景：
这里写图片描述
关于聚集索引以及非聚集索引的几个问题：
一）聚集索引的约束是唯一性，是否要求字段也是唯一的呢？
一般我们指定一个表的主键，如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，SQL会默认在此字段上创建一个聚集索引，而主键都是唯一的，所以理所当然的认为创建聚集索引的字段也需要唯一。
聚集索引可以创建在任何一列你想创建的字段上，这是从理论上讲，实际情况并不能随便指定，否则在性能上会是恶梦。
二）|主键就是聚集索引？？？
这样有时会对聚集索引的一种浪费。Innodb将通过主键聚集数据，如果没有定义主键，Innodb会选择第一个非空的唯一索引代替，如果没有非空唯一索引，Innodb会隐式定义一个6字节的rowid主键来作为聚集索引。innodb只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距甚远。
因为每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。
使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。在实际应用中，因为 ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次，让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则；当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。
三）是不是聚集索引就一定要比非聚集索引性能优呢？？？
如果想查询学分在60-90之间的学生的学分以及姓名，在学分上创建聚集索引是否是最优的呢？
答：否。既然只输出两列，我们可以在学分以及学生姓名上创建联合非聚集索引（也就是多列索引），此时的索引就形成了覆盖索引，即索引所存储的内容就是最终输出的数据，这种索引在比以学分为聚集索引做查询性能更好。
四）在数据库中通过什么描述聚集索引与非聚集索引的？
索引是通过二叉树的形式进行描述的，我们可以这样区分聚集与非聚集索引的区别：聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节仍然是索引节点，但它有一个指向最终数据的指针。
五）在主键是创建聚集索引的表在数据插入上为什么比主键上创建非聚集索引表速度要慢？
在有主键的表中插入数据行，由于有主键唯一性的约束，所以需要保证插入的数据没有重复。我们来比较下主键为聚集索引和非聚集索引的查找情况：聚集索引由于索引叶节点就是数据页，所以如果想检查主键的唯一性，需要遍历所有数据节点才行，但非聚集索引不同，由于非聚集索引上已经包含了主键值，所以查找主键唯一性，只需要遍历所有的索引页就行，这比遍历所有数据行减少了不少IO消耗。这就是为什么主键上创建非聚集索引比主键上创建聚集索引在插入数据时要快的真正原因。
四、索引设计优化：
（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 idfromt where num isnull
最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。
不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
三、）应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
四、）应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。如：
select idfromt where num=10orName ='fuzhu'
可以这样查询，充分利用索引：
select id from twherenum = 10union allselect id from twhereName ='fuzhu'
五、）in 和 not in 也要慎用，否则会导致全表扫描。
select id from twherenumin(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select idfromt where num between1and3
很多时候用 exists 代替 in 是一个好的选择
select numfroma where numin(select numfromb)
正上面的，用下面的语句替换：
select num from awhereexists(select 1 from bwherenum=a.num)
六、）下面的模糊查询也将导致全表扫描：
select id from twherename like ‘%abc%’
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。
若要提高效率，可以考虑全文检索。
既然谈到模糊查询下使用索引，我们就顺便详细地讲讲吧。
1. like %keyword 索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全表扫描。例子在此处
2. like keyword% 索引有效。
3. like %keyword% 索引失效，也无法使用反向索引。
//可以拿我给出的数据库试一下嘛。然后用explain测试，就能测出有没有走索引了select *fromtable where code like'Classify_Description%'select *fromtable where code like'%Classify_Description%'select *fromtable where code like'%Classify_Description'
七、）如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from twherenum = @num
可以改为强制查询使用索引：
select id from twith(index(索引名)) where num=@num
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from twherenum/2 = 100
正上面的应改为：
select id from twherenum = 100*2
八、）应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t wheresubstring(name,1,3)= ’abc’//name以abc开头的idselect id from t wheredatediff(day,createdate,’2005-11-30′)=0-–‘2005-11-30’//生成的id
应改为:
select idfromt where name like'abc%'select idfromt where createdate >='2005-11-30'andcreatedate <'2005-12-1'
九、）.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
十、）在使用索引字段作为条件时，如果该索引是复合索引（多列索引），那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
十一、）索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
十二、）应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
十三、）尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
十四、）MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
好了，MySQL优化笔记（三）--索引的使用、原理和设计优化，这是积累的必经一步，我会继续出这个系列文章，分享经验给大家。欢迎在下面指出错误，共同学习！！你的点赞是对我最好的支持！！
作者：JackFrost_fuzhu
链接：https://www.jianshu.com/p/6b080a787b61
來源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
