浅谈Mysql索引基础

什么是索引？

索引是一种数据库快速寻找记录的方式，索引就像字典里的偏旁查询页，如果没有它那么数据库对表的检索将是“从字典第一页翻到最后一页，直到找到你所要的数据”。所以，索引的用处也就自然而然地体现出来了。那么毫无疑问索引是已经排序好的一页数据，在innodb引擎中，mysql使用的是一整个表空间，就算设置了每个表有自己的表空间，mysql还是会把所有表的索引和数据放在一个表空间文件里。对于单表查询索引可以体现出很大的作用，而对于多表查询索引更能发挥功效。比如一个多表查询操作涉及到t1、t2、t3，使用索引的情况下，常常会全表扫描一遍t1，在t2表用索引定位到t1的某个值，然后在t3用索引定位到t1的某个值，这样以此类推。所以由此看来，索引往往是用在某个字段上，比如where涉及的字段、order、group都有用，而且对于一些像max的函数也会大大提升检索速度，另外，如果索引的字段就是所要的字段，那么就没有必要再去查数据表了。

 

索引有缺点吗？

肯定有。索引也是数据，所以跟表数据一样占用存储空间，而且索引使得数据写入操作更繁琐。

 

索引如何构建？

最基本的用法就是上面提到的，把索引加在where、order、group、on字段上。要注意的是索引的效果跟字段基数（字段不同值个数）有关系，基数很小的列根本没有使用索引的意义；然后就是尽量让字段更简单，数据类型越小越好，比较得快计算得快、索引的负担也小；对于长字符型索引要指定前缀；接着是考虑最左前缀，因为复合索引指定的时候总是按照最左集合来排序，所以新建索引的时候也要注意需要的索引是否已经存在或者是已经是某个复合索引的最左前缀；然后就是有些函数的操作是需要对数据标的每一行都执行的，所以这就没必要建立索引了；由于引擎支持的索引实现不同，所以针对数据比较操作可以选用适合的引擎表，比如innodb总是使用b树实现索引，这就很适合像>、<类似这样的范围操作，而memory引擎会使用散列索引，所以就适合=、<>这样的单一操作，还有就是myisam引擎的r树索引等有待了解；最关键的就是慢日志查询，对于日志里的查询需要进一步优化才可行，这个所谓的慢是程序测量出来的，所以测量结果跟服务器负载程度有一定关系，负载大的时候可能会有更多的慢查询语句进入日志，这个时候需要我们设定一个合适的查询超时阈值。

 

索引是如何被使用的？

尝试过索引使用的人都知道，索引建立之后就不需要多余的代码来使用它了，貌似索引被mysql自动使用，没错，mysql的查询优化程序主动使用索引来优化语句。常见的优化有分析表，比如where中有两个字段用and连接做比较计算，那么这两个字段的索引中的键值被比较的时候，优化程序会对索引键值做一次检查，比如看一下键值大致有多少项，然后依据这个值来优化查询的顺序，如果是一个字段有100项，另一个50项，那么就会从少的一边开始查，那么自然失败的记录条数就会少很多，以减少了io操作，这一点叫作键值分析，可以使用命令analyse table；在做多表查询的时候偶尔会需要自己显示指定索引的使用来提示优化程序执行，比如连接表名后加上use index、force index等，或者用straight_join来强制指定按照from关键字后的表顺序连接，一般来说多表查询跟单表一样的思路，都是行数少的表先操作；还有类型的不同也会影响比较操作的速度，尽量使用相同类型比较，不过数据类型还是主要偏向该字段的实际含义而定；还有很关键的是必须把被索引字段单独放在比较符号的一侧才能够使用索引，这一点不用多说，因为不这样做就会被看作一个表达式，表达式跟函数一样是逐行操作的；还有模糊查询的时候基本上用不了索引，因为优化程序查看你匹配字符的前几个来做索引前缀的查询，但是模糊查询往往开头是%，而且regexp的功能也不会使用索引；关于子查询和连接的等效替换在这里还是偏向使用连接，这样优化效果会好一些；有个小陷阱就是，如果你对两个语句做性能测试要运行多次，因为只运行一次可能第二个运行的结果是从缓存中取出，导致影响结果；以前说过一个mysql的数据类型隐式转换，如果字段是int，那么写成col=’4’也是个问题，这样每一行的这个字段都会做类型转换操作，所以相当于没使用索引；表碎片有时候也会影响查询速度，变长字段的大量使用以及更新删除text、blob类型字段的时候会产生一些存储空缺，对于innodb和myisam可以使用命令optimize table来清除碎片或者使用表转储解决；对了，尽量避免null是个提升性能的好习惯。

 

强大的explain？

上面说到优化程序，那么explain就是用来检查优化程序的操作的。Explain的使用非常关键，比如报表的type字段会提示表扫描的方式，像all、range、ref等。还有就是rows字段会标明扫描的记录数，为了让这个记录数更加精准，需要在执行explain之前执行一下上面说的anaylse table命令。Explain的其它字段含义就不在这里说了。

 

其它性能提升？

除了索引还有很多提升性能的方式。

1. 开启查询缓存。查询缓存默认是不开的，如果要开还要同时设置几个缓存的变量值，比如最小分配内存大小等。Innodb引擎中，加锁的查询是不会用缓存的，而且只有事务id比较大的才可使用缓存。由于查询缓存涉及很多繁琐的操作，比如依据几个查询特征生成hash值，然后查hash对应的缓存数据结果集，还有开辟内存后的碎片问题等，所以开启查询缓存并不一定能提高查询性能，可能反而带来更大的系统负担。
2. 不能随便使用函数。上面有说到过隐世类型转换的问题，其实道理都一样，如果在查询中使用函数，那么通常情况下函数会使得索引无效，并且每一行数据都会使用函数计算，就像类型转换、order by rand()等。但是如果非要使用函数，还要使用索引那么只能使用mysql5.7的新特性——虚拟列。这个东西就是和普通的列一样，只不过它是由其他列计算得来，那么如何计算的呢？就是使用你想要的函数计算了，使用起来很方便。
3. 无特殊情况主键最好选用自增非负int类型，性能最好，但是往往开发的时候采用的主键很特殊，所以这点忽略不计。
4. 尽量避免使用非驱动表的字段排序。驱动表是explain执行计划的第一行的表，这个表的数据会用作基础循环数据，就是全表扫描。这个表的字段排序十分快，但是如果使用了非驱动表的字段排序，那么mysql要先合并临时表，这样很消耗性能，所以会出现use temporary这样的待优化提示。所以join或者where这样的关键字会让mysql自行判断最好的驱动表，没有特殊情况不需要使用left join和right join，因为它们强行指定前面的表为驱动表。

 

额。。暂时文章就先写到这里了，稍微说了一点优化的皮毛，其实还有太多的优化点没有提到，像优化工具使用、并发情况、锁等等。等到博主有时间再继续扯吧。。