Mysql索引及优化使用总结

　　优化MySQL数据库是数据库管理员和数据库开发人员的必备技能。优化MySQL，一方面是找出系统的瓶颈，提高Mysql数据库整体的性能；另一方面是合理设计结构和调整参数，以提高用户操作响应的速度。同时还要尽可能节省系统资源，以便系统可以提供更大负荷的服务。

      而小编在这里讲的就是MySQL中的索引优化~

      在关系数据库中，索引的使用十分重要，而且所有的关系数据库支持索引机制，因为有了索引之后，在大数据量检索数据时速度很快，性能消耗很低；当然，凡事有利必有弊，增加索引也会增加数据库系统的开销，我们很多时候需要在性能和检索间折中设计，而且正确使用索引及对他维护和优化是很重要的！

　　一、索引的类型

　　在Mysql中，索引可分为普通索引、唯一索引、主键索引、外键索引及组合索引，它们在创建索引位置上都是一样的，即在表的一个或多个字段上创建使用，具体介绍下：

　　1、普通索引

　　普通索引的唯一任务就是加快数据的检索速度，应该在经常出现在WHERE或ORDERBY后的单一列上使用，而且该索引列可以重复，由关键字KEY或INDEX定义的索引。

　　2、唯一索引

　　唯一索引的主要任务除了加快数据的检索速度之外，还有就是约束创建或使用唯一索引的单一列必须是不重复的，也就是保证了数据的唯一性，所以，它的主要作用体现在检索速度和数据唯一方面，由关键字UNIQUE定义的索引。

　　3、主键索引

　　主键索引是MYSQL为数据表主键字段默认生成的索引，它与唯一索引的唯一不同就是它们的定义语句，这里使用的是PRIMARY而不是UNIQUE。

　　4、外键索引

　　如果为某个外键字段定义一个外键，MYSQL就会为其生成一个索引，来帮助加快对外键约束的使用，由关键字KEY定义，外键数据可重复。

　　5、组合索引

　　组合索引与普通索引的区别是在多个列上定义，与唯一索引相同的是组合索引必须保证唯一，而且MYSQL可以选择不同的索引字段的合适组合来组合查询索引（适用于排列在前的数据列组合）。

　　6、全文索引

　　文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE%word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。

　　这类场合正是全文索引(full-textindex)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建，也可以等日后有必要时再使用下面这条命令添加：

　　ALTERTABLEtablenameADDFULLTEXT(column1,column2)

　　有了全文索引，就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。

　　二、索引的原理

　　索引的工作原理比较清楚明了，在创建或使用索引时，默认按照某种规则为索引字段排好顺序，在用户检索数据时，系统首先检索排序的索引内容，如果找到匹配的内容即刻返回，不进行全文检索，这样也就是大大提高了检索的速度了，在下面索引的分析中会演示索引的工作原理，请继续阅读。

　　三、何时建索引

　　1、准备工作

　　鉴于创建索引需要额外的磁盘空间，需要后期维护清理索引碎片，以及增加表的CUD操作的性能，所以创建索引需要谨慎考虑哦。

　　2、何时创建

　　既然索引的诞生就是为了解决数据的检索效率，那么很明显需要在经常查询的表的某些字段上创建并维护索引，鉴于准备工作部分考虑的因素，对于那些经常查询也同时经常CUD的表字段上如果必须添加索引，那么推荐使用合适的算法使用索引，比如：二分检索（这里不做介绍）。

　　四、索引的存储分类

　　myisam表的数据文件和索引文件是自动分开的；innodb的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。创建索引语法如下：

　　CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_name

　　[USINGindex_type]

　　ONtbl_name(index_col_name,...)

　　index_col_name:

　　col_name[(length)][ASC|DESC]

　　索引的存储类型目前只有两种（btree和hash），具体和表的模式相关：

　　1.myisam  btree

　　2.innodb   btree

　　3.memory/heaphash，btree

　　mysql目前不支持函数索引，只能对列的前一部分（length）进行索引，例：

　　createindexind_testontable1(name(5))，

　　对于char和varchar列，使用前缀索引将大大节省空间。

　　五、MySQL如何使用索引

　　索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高SELECT操作性能的最佳途径。

　　查询要使用索引最主要的条件是查询条件中需要使用索引关键字，如果是多列索引，那么只有查询条件使用了多列关键字最左边的前缀时，才可以使用索引，否则将不能使用索引。

　　下列情况下，Mysql不会使用已有的索引：

　　1.如果mysql估计使用索引比全表扫描更慢，则不使用索引。例如：如果key_part1均匀分布在1和100之间，下列查询中使用索引就不是很好：

　　SELECT*FROMtable_namewherekey_part1>1andkey_part1<90

　　2.如果使用heap表并且where条件中不用＝索引列，其他>、<、>=、<=均不使用索引；

　　3.如果不是索引列的第一部分；

　　4.如果like是以％开始；

　　5.对where后边条件为字符串的一定要加引号，字符串如果为数字mysql会自动转为字符串，但是不使用索引。

　　六、查看索引使用情况

　　如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。

　　Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明表索引不正确或写入的查询没有利用索引。语法：

　　mysql>showstatuslike'Handler_read%';

　　七、索引的优化

　　根据上面对索引的介绍，我们可以总结一下常用的优化索引建议：

　　1、在经常查询的表字段建立索引

　　具体创建什么索引，可根据需求来定，如果没有特殊要求，如：是否允许重复，那么就可以创建一普通的索引；否则，可以创建一个唯一缩影；如果需要多个索引列唯一，那么就创建唯一组合索引即可。

　　2、在大数据量检索中，尽量使用FULL-TEXT索引代替LIKE

　　使用InnoDB引擎的同学，需要升级MYSQL版本到5.6，或者使用MyISAM引擎。

　　3、维护优化索引碎片

　　在建有索引的数据表中，每当删除记录数据时，对应记录上的索引标记并未删除，这会产生数据垃圾，也叫碎片，长期以往不作处理的话，会影响数据的检索效率。

　　比较好的办法：重建索引。

　　4、避免使用聚合函数

　　在建有索引的数据检索中，尽量在检索条件后不使用聚合函数，这可能会使索引失效，影响数据检索速度。

　　小编结语：

　　对于索引的优化，很多时候就是保证索能发挥其正常的功能，所以很多围绕索引的优化，其实就是避免索引被迫害，剩下的就是对检索的SQL的优化了。