1. MySQL索引的原理
1.1 索引目的
索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？
1.2 索引原理
除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。
每个数据表都有一个主键（如果没有主键，数据库会将该表中的唯一索引当作主键使用？），MySQL会以主键的方式构造一棵树，叶子节点存放该主键对应的整行数据。
自己建立的索引，一般叫做辅助索引，辅助索引的树，也自己节点存放了两个东西，一个是索引自身的值，另外一个是索引对应主键的值。
如果索引是联合索引，比如UserID和AddTime索引的方式，索引叶子节点会存储UserID和AddTime之间的配对+主键的配对数据。
1.3 索引的类型
1.3.1 B-树索引B-树索引在生产环境更为广泛，这里我只针对B-树索引进行讨论
B-树索引是一个复杂的内容，可以参见B-tree。
哈希索引（Hash Index）建立在哈希表的基础上，它只对使用了索引中的每一列的精确查找有用。对于每一行，存储引擎计算出了被索引的哈希码（Hash Code），它是一个较小的值，并且有可能和其他行的哈希码不同。它把哈希码保存在索引中，并且保存了一个指向哈希表中的每一行的指针。
在mysql中，只有memory存储引擎支持显式的哈希索引。
- Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。
- Hash 索引无法被用来避免数据的排序操作。
- Hash 索引不能利用部分索引键查询。
- Hash 索引在任何时候都不能避免表扫描。
- Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高
主要用于GIS中空间数据的存储，但是MySQL的空间索引支持并不好，现在多使用PostgreSQL。
1.3.4 全文索引(Full-text)索引文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单 词构成 的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。
这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数 据记录。全文索引即可以随数据表一同创建，也可以等日后有必要时再使用下面这条命令添加：
ALTER TABLE tablename ADD FULLTEXT(column1, column2)
2. 索引的合理使用
| 字段名 | 数据类型 | NULL | INDEX |
|---|---|---|---|
| ID | int(10) | NOT NULL | pk |
| UserID | int(10) | NOT NULL | |
| Mobile | varchar(15) | NOT NULL | |
| ArriveDate | DateTime | NOT NULL | |
| AddDate | DateTime | NOT NULL | |
| UpdateTime | timetamp | NOT NULL | |
下面我们的分析都会这个数据表为例。
2.1 索引在查询中的使用
不使用索引
现在刚建立的表上没有任何索引，但是我们想通过UserID找出ArriveDate这个数据，SQL将会写成如下方式：
select ArriveDate from TestSQL where UserID = 10;
这个SQL没有走索引，数据库就会根据主键（ID）扫描全表，每拿到一条数据库记录就与where条件比对，如果符合条件则将这条记录返回，重复直到全表扫描完毕。
在大数据量的情况下，不使用索引进行查询几乎是不可行的。
- 使用UserID作为索引
这时候看到where条件是使用了UserID索引的。这时候数据库引擎会根据UserID到索引上找到ID，然后根据ID去查询对应记录，从而取出ArriveDate数据。
现在我们将UserID的索引更换为UserID,ArriveDate的联合索引。
现在再来查询：
可以发现这个Extra里面也是使用了索引的，这就意味这个SQL是完全走了索引，数据库引擎根据UserID找到对应的索引， 因为Select的字段是索引的一部分，所以找到索引之后不需要再读取表记录了。
当一个查询语句中使用设计到多个索引时，MySQL数据库引擎会计算不同索引涉及到的行数大小，选取行数最小的索引作为实际执行时使用的索引，如：
一次查询同一张表，MySQL每次只会使用一个索引。
2.2 索引在范围查询的使用
范围查询主要是指查询字段值在某个范围内的记录，表现在where条件中为>,<,between等关键字。如，我们使用如下SQL进行查询：
select UserID,ArriveDate from TestSQL where UserID >0 and UserID <100 and ArriveDate = '2015-09-23 00:00:00';
作为对比，我们使用另一种SQL查询相同记录：
select UserID,ArriveDate from TestSQL where UserID in(2,12) and ArriveDate = '2015-09-23 00:00:00';
联调SQL都会查询出相同的记录：
| UserID | ArriveDate |
|---|---|
| 2 | 2015-09-23 00:00:00 |
| 12 | 2015-09-23 00:00:00 |
第一个SQL（使用范围查询）的explain结果为：
第二个SQL（未使用范围查询）的explain结果为：
对 比可见，两种sql的索引长度是不一样的。在范围查询中，索引的使用是遵循最左（leftmost）原则，例如这个表的使用的索引是 IX_UserID_ArriveDate，但是因为UserID使用了范围查询(Range query)，就不再使用ArrvieDate的索引了。
2.3 排序使用索引
在排序中以下情况无法使用索引：
- Order by 的字段并不是索引
-
使用了两种排序方向，但是索引都是使用升序排列的
在索引的原理里我们讲到过如果索引是联合索引，比如UserID和AddTime索引的方式，索引叶子节点会存储UserID和AddTime之间的配对+主键的配对数据。
这种情况下，索引会以UserID进行排序，当UserID相同时再以AddTime进行排序（默认为升序），以我们现有的数据库为例：
select UserID,ArriveDate from TestSQL where UserID in(2,3,4) order by UserID asc,ArriveDate desc;
这种情况将只使用UserID 作为索引，而ArriveDate 将不再作为索引。
-
不符合最左（Left most）的条件
select * from TestSQL where ArriveDate = '2015-09-23 00:00:00' order by UserID desc
这种情况将不再使用UserID_ArriveDate索引
-
联合查询的情况下，如果第一个筛选条件是范围查询，MySQL不再使用剩下的索引
select * from TestSQL where UserID >1 and UserID < 5 order by UserID desc,ArriveDate desc
这种情况下，因为UserID 为范围查询，所以就不会再使用ArriveDate 索引了。
2.4 join 中使用索引
我们来新建一个TestSQL_join表，其结构与TestSQL 相同，但只有Mobile索引。首先来看一个简单的join操作
select * from TestSQL join TestSQL_join on TestSQL.id = TestSQL.id
MySQL首先比较TestSQL和 TestSQL_join表那个行数少，如TestSQL中的记录较少，TestSQL就是一个小表，而TestSQL_join则是大表，MySQL引 擎先把TestSQL中的ID全部去出来，然后根据id到TestSQL_join中查询相关的记录。
在这里，TestSQL中行数决定了循环的次数，但是TestSQL_join则决定了每次循环查询所需要查询的时间；这时如果TestSQL_join中的ID是索引则会大大减少查询时间如下SQL:
select * from TestSQL a join TestSQL_join b on a.ID = b.ID
由于b.ID 是TestSQL_join的主键，查询使用了TestSQL_join主键索引。
如果此时对TestSQL增加条件筛选：
select * from TestSQL a join TestSQL_join b on a.ID = b.ID where a.UserID = 1;
因为a.UserID 是TestSQL的索引，所以在过滤TestSQL表的行数时，采用次索引查询对应ID，然后根据ID查询TestSQL_join的记录。
如果此时针对TestSQL_join 增加where条件过滤：
select * from TestSQL a join TestSQL_join b on a.ID = b.ID where a.UserID >1 and b.Mobile = "2147483647"
这种时候，因为TestSQL 和 TestSQL_join 根据where条件所筛选出来的行数大小可能会有变化，也就是说TestSQL_join 有可能会变成小表，这时候将会优先从TestSQL_join 查询出相关ID，然后根据ID去查询TestSQL。
join操作时，大表小表的概念，主要是按照两张表分别执行对应查询条件，哪个开销更小，哪个就是小表。
join操作虽然在SQL层面很方便，而且在线上大流量的情况下，一旦SQL的join操作导致查询缓慢，较难即使优化。另外在服务化的系统中，容易导致业务领域不清晰，所以在互联网大流量的应用中是不推荐使用join操作的。
3. 索引建立的原则
-
使用区分度高的列作为索引
区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，区分度越高，索引树的分叉也就越多，一次性找到的概率也就越高。 -
尽量使用字段长度小的列作为索引
-
使用数据类型简单的列（int 型，固定长度）
-
选用NOT NULL的列
在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。 -
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。这样也可避免索引重复。
4. 索引使用的原则
-
最左前缀匹配原则(leftmost)，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停 止匹配，=和in可以乱序，一个联合索引中，如UserID，ArriveDate的联合索引，使用ArriveDate in （）and UserID = 的任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式
-
索引列不能参与计算
select * from TestSQL where UserID + 1 >1 and UserID < 5
这种方式UserID 的索引就不会再被使用，因为在进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。
另外当使用<>,like通配符放置在最前面 如：like'%ddd' ,not, in, !=等运算符都不会使用索引。 -
查询数据库记录时，查询到的条目数尽量小，当通过索引获取到的数据库记录> 数据库总揭露的1/3时，SQL将有可能直接全表扫描，索引就失去了应有的作用。
5. explain的使用
explain是MySQL查询优化过程的神器，详情可以查看explain的使用
References
High Performance MySQL-THIRD EDITION
explain的使用
MySQL索引学习漫画
MySQL索引原理及慢查询优化
理解MySQL??索引与优化
mysql索引的类型和优缺点
Mysql索引及优化使用总结
Mysql索引及优化使用总结：
在关系数据库中，索引的使用十分重要，而且所有的关系数据库支持索引机制，因为有了索引之后，在大数据量检索数据时速度很快，性能消耗很低；当然，凡事有利必有弊，增加索引也会增加数据库系统的开销，我们很多时候需要在性能和检索间折中设计，而且正确使用索引及对他维护和优化是很重要的！
· 索引的类型？
· 索引的原理？
· 何时建索引？
· 索引的使用？
· 索引的优化？
一、索引的类型
在Mysql中，索引可分为普通索引、唯一索引、主键索引、外键索引及组合索引，它们在创建索引位置上都是一样的，即在表的一个或多个字段上创建使用，具体介绍下：
1、普通索引
普通索引的唯一任务就是加快数据的检索速度，应该在经常出现在WHERE或ORDER BY后的单一列上使用，而且该索引列可以重复，由关键字KEY或INDEX定义的索引。
2、唯一索引
唯一索引的主要任务除了加快数据的检索速度之外，还有就是约束创建或使用唯一索引的单一列必须是不重复的，也就是保证了数据的唯一性，所以，它的主要作用体现在检索速度和数据唯一方面，由关键字UNIQUE定义的索引。
3、主键索引
主键索引是MYSQL为数据表主键字段默认生成的索引，它与唯一索引的唯一不同就是它们的定义语句，这里使用的是PRIMARY而不是UNIQUE。
4、外键索引
如果为某个外键字段定义一个外键，MYSQL就会为其生成一个索引，来帮助加快对外键约束的使用，由关键字KEY定义，外键数据可重复。
5、组合索引
组合索引与普通索引的区别是在多个列上定义，与唯一索引相同的是组合索引必须保证唯一，而且MYSQL可以选择不同的索引字段的合适组合来组合查询索引（适用于排列在前的数据列组合）。
6、全文索引
文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。
这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建，也可以等日后有必要时再使用下面这条命令添加：
ALTER TABLE tablename ADD FULLTEXT(column1, column2)
有了全文索引，就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基本语法：
SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘word1′, ‘word2′, ‘word3′)
上面这条命令将把column1和column2字段里有word1、word2和word3的数据记录全部查询出来。
注：
有博文说全文索引在数据库引擎InnoDB中不支持，其实不然，在MYSQL版本5.6已经引入了全文索引
二、索引的原理
索引的工作原理比较清楚明了，在创建或使用索引时，默认按照某种规则为索引字段排好顺序，在用户检索数据时，系统首先检索排序的索引内容，如果找到匹配的内容即刻返回，不进行全文检索，这样也就是大大提高了检索的速度了，在下面索引的分析中会演示索引的工作原理，请继续阅读。
三、何时建索引
1、准备工作
鉴于创建索引需要额外的磁盘空间，需要后期维护清理索引碎片，以及增加表的CUD操作的性能，所以创建索引需要谨慎考虑哦。
2、何时创建
既然索引的诞生就是为了解决数据的检索效率，那么很明显需要在经常查询的表的某些字段上创建并维护索引，鉴于准备工作部分考虑的因素，对于那些经常查询也同时经常CUD的表字段上如果必须添加索引，那么推荐使用合适的算法使用索引，比如：二分检索（这里不做介绍）。
四、索引的使用
语法结构：
CREATE [NORMAL|UNIQUE|FULLTEXT] INDEX index_name
ON table_name (col_name[(length)[ASC | DESC]],[…])
NOTE:
index_name：索引名字；
table_name:建立索引的数据表；
col_name：建立索引的表字段，length为内容字符的前多少位加入索引算法；
1、普通索引
首先，我准备一张数据表t_user_info，该表中我添加了500万条数据，下面我们来测分析下，在使用索引和未使用索引的差别，同样条件的检索：
SELECT account,nickname,email,address FROM t_user_infoWHERE account="cwteam4000000";
未使用索引：使用EXPLAIN分析SQL，分析的结果：
从上图，我们看的出来，本次检索复杂界别为select_type，即简单查询；检索类型为type全表检索；并未使用索引key(NULL)；查询条件Extra为where；检索的条数为4889736；另外，检索的时长在4秒左右浮动（去掉EXPLAIN执行查看）。
已使用索引：使用EXPLAIN分析SQL，分析的结果：
A、创建索引
DROP INDEX IF EXISTS idx_user_info ON t_user_info;
CREATE INDEX idx_user_info ON t_user_info(account);
B、分析检索
在这里，我们只对比不同的地方，使用了索引之后，检索的类型是ref即索引算法检索；使用了key索引，索引名字为idx_user_info；检索的条数为1；查询条件Extra为Using index condition；另外，检索的时长在0.01秒左右浮动，基本提高了4倍速度。
得出结论：
在数据量大的检索中，对比未使用索引，基本提高了4个级别程度，所以在经常检索的表字段建立索引使用。
2、唯一索引
A、创建索引
ALTER TABLE t_user_info DROP INDEX idx_user_info;
ALTER TABLE t_user_info ADD UNIQUE (account);
B、分析索引
从上图分析，为了演示准确，我们将上面建有的普通索引删除，再新建一唯一索引。使用与普通索引同样的检索SQL，分析后主要的结果是一样的；另外，得到的时长也是相同的。上面只是分析了唯一索引在速度方面，下面再来看看唯一索引在数据唯一性方面的特性，这里我们选择插入一条已有的数据，正常系统应该返回并提示错误信息：不能重复插入相同的唯一索引字段内容。
插入的语句：
INSERT INTO t_user_info(account,password,nickname,email,address) VALUES (
"cwteam11",
"e10adc3949ba59abbe56e057f20f883e",
"cwteam11",
"abc@yeah.net",
"china shss"
);
测试结果：
得出结论：
唯一索引的创建和使用，不影响它的查询优势，同时也保证了数据的唯一性。
3、组合索引
A、创建索引
ALTER TABLE t_user_info DROP INDEX account;
ALTER TABLE t_user_info ADD INDEX idx_user_info(account,password);
B、分析索引
从上图看到，组合索引的使用与普通的索引相同的，只不过索引的字段数量不同罢了；另外，在查询时间上也是相同的。
得出结论：
组合索引与普通索引原理相同，不同的是索引的字段数量而已，同时也不影响检索速度。
NOTE:
一般，组合索引用在保证多个列唯一的需求，也就是要结合UNIQUE索引才有实际意义，上面只是演示索引的不同。
4、全文索引
全文索引默认在引擎MyISAM支持，但在Mysql5.6版本开始，数据库引擎InnoDB也开始支持了。由于我的MYSQL版本低于5.6，并未升级（后续升级），所以这里暂时修改数据表的引擎为MyISAM，目的是为了介绍全文检索。
在500万的数据量中，我们对比LIKE和MATCH（…）AGAINST（…）在达到同样功能时：
两者的差别：
A、使用LIKE检索
EXPLAIN SELECT account,nickname,email,address FROM t_user_info WHEREaccount LIKE " cwteam400000";
分析结果：
从上图看出，在使用LIKE检索时，采用的是全表扫描，并未使用索引扫描，时长在1.16秒左右浮动，这对于500万条数据其实还是可以的，但随着数据的增加这个时长也会随之增加的，下面来看下使全文索引生效的MATCH…AGAINST的特点。
B、使用全文索引
EXPLAIN SELECT account,nickname,email,address FROM t_user_info WHEREMATCH(account,password) AGAINST(' cwteam400000');
分析结果：
从上图明显看出，检索使用了全文检索FULLTEXT，时长在0.01秒左右浮动，所以全文索引的效率还是挺高的，当然它的检索时长也会随着数据量增多，不断增多哦（好像是废话）。
得出结论：
如果建立的索引列数较多，建议使用全文检索，而在大数据量检索中，可使用MATCH-AGAINST使FULLTEXT生效，代替使用LIKE全表检索。
五、索引的优化
根据上面对索引的介绍，我们可以总结一下常用的优化索引建议：
1、在经常查询的表字段建立索引
具体创建什么索引，可根据需求来定，如果没有特殊要求，如：是否允许重复，那么就可以创建一普通的索引；否则，可以创建一个唯一缩影；如果需要多个索引列唯一，那么就创建唯一组合索引即可。
2、在大数据量检索中，尽量使用FULL-TEXT索引代替LIKE
使用InnoDB引擎的同学，需要升级MYSQL版本到5.6，或者使用MyISAM引擎。
3、维护优化索引碎片
在建有索引的数据表中，每当删除记录数据时，对应记录上的索引标记并未删除，这会产生数据垃圾，也叫碎片，长期以往不作处理的话，会影响数据的检索效率。
比较好的办法：重建索引。
4、避免使用聚合函数
在建有索引的数据检索中，尽量在检索条件后不使用聚合函数，这可能会使索引失效，影响数据检索速度。
NOTE：
对于索引的优化，很多时候就是保证索能发挥其正常的功能，所以很多围绕索引的优化，其实就是避免索引被迫害，剩下的就是对检索的SQL的优化了。
好了，索引及优化就总结到这里，如有问题，欢迎积极指出并讨论。
（技术讨论群：276592700（新））
来源：http://www.itnose.net/detail/6382888.html
2819
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
