在上一篇博文中，简单的介绍了一下两种数据库引擎的索引区别。
在这篇中准备写 mysql 使用索引的一些事。在上篇博文中，有提到 MyISAM 的回行查找数据与 InnoDB 聚簇索引的特点。
普通索引，主键索引，唯一索引，全文索引，复合索引是五种常见的索引形式。
对于数据库的优化整体优化来说基本分为了三个方面:
- 查得快
- 取得快
- 数据传输得快
对于查得快来说，添加合适的索引是很重要的。那么什么是合适的索引？
它并不是无迹可寻的，和三范式一般，他也有基本的原则。
- 建立索引的字段值，区分度要高，重复的尽可能少
- 依赖该字段进行查询，排序
- 索引字段默认值不应该选 NULL
- 如果可以，索引字段优先选整形
- 索引长度小
索引长度与区分度悖论
从上面可以看出来，第一条和第五条，实际上是有一点矛盾的，因为肯定索引长度越长，那么区分度越高，索引长度越短，值就越容易出现重复的情况，所以我们需在中间做一个权衡。
那么如何做一个平衡呢？也是有一定标准的，可以通过计算得出。
计算原则是测试，假设该字段的索引长度为1，那么通过mysql的left函数，取该字段的一个字符，然后distinct去重，再进行统计行数。
select count(distinct left(name,1)) from user;
以上语句，能取出这个name第一个字都不相同的记录条数。
select ((select count(distinct left(name,1)) from user) / (select count(*) from user));
以上语句，主要是做索引长度的命中率。
其实当做过测试后，会发现，索引长度越往后面增加，命中率却增长的越来越慢，我们可以在其中间点找到一个位置，做权衡。
当然也要看实际业务情况，不过一般索引命中率能够达到百分之90就可以了。
左前缀不易区分的索引该怎么做?
说到索引长度，除了主键索引，每个索引都值得做命中率测试，而实际上，有些字段，长度就是得那么高，因为字段值的前段部分区分度极低 。
但是不可避免的(例如域名，例如地区全称，等)，又必须得加索引那该怎么办呢？
- 字符反转，将存入的值反转过来再存入。
- 伪哈希索引
伪哈希索引:
利用 PHP 中的一个内置哈希函数，crc32(result),这个函数可以将字符串转换成一个32位的整形。
因此，我们可以在表中增加一个 int 字段，在此字段上建立索引，在插入操作时，将本该建立索引的字段值用该函数转换成一串数字，再存入。
在需要查找时也需要用到这个函数。
索引覆盖
什么事索引覆盖？简单来说，就是我只用查索引，就能获取到想要的数据。
例如:
select id from xxx where id=1;
在xxx表中,id为主键，而我只需要查这个主键就行了，而刚好这个id值正在索引树上，不需要再取其他数据就叫索引覆盖。
可想而知，innodb 中，索引和数据都在同一个节点上，所以索引覆盖不是针对innodb来说，而是针对非聚簇索引类型来说的，例如 Myisam 。
假设xxx表中还有一个字段为name且没有加索引，此时在查询语句的字段中加入name字段的话，很明显，索引树上并没有name这个关键字，它只能找到id为1的行指针，通过指针找到地址后再取出name的值。
这个过程叫回行。从查询步骤来讲的话，回行的效率是不够高的。
利用索引覆盖原理，可以大大增加某些情况下的排序速度，因为直接就在索引树上了。要知道分组也是需要先排序的哦。
而通常实际情况下，我们几乎很少只取主键列，所以我们可以根据需要再添加复合索引。
这样我们就能做到取数据也快。
复合索引
什么是复合索引？
这个复合索引有很多种说法，多列索引，联合索引，其实都是指的一个东西。在我这里我叫他复合索引
要知道，建立索引，实际上就是多建立一棵树，而复合索引，就是将两个或以上的字段，建立在同一棵树上。
首先，我们
使用复合索引和非全长度索引，需要了解索引的左前缀原则。
- 根据建立索引时的字段顺序来查
- 靠左字段的查询条件需要是确定的，当遇到不确定因素(like后置，范围)且后边还有字段，或跳过索引字段，将停止索引匹配，之前匹配到的索引保留，会使用到索引。
多列索引建立原则要根据实际业务场景来做，例如说商城，进去首页后，有人先选大分类，再选品牌,在这里可以做一个复合索引，大分类在前，品牌在后。
品牌下，有人会筛选价格，那么这里也可以建立一个索引。单单只给价格加上索引是没有意义的。当做完复合索引后，可以利用左前缀原则，对复合索引进行优化，因为可能会有部分重复。
实际业务情况，也会适当建立冗余索引，也就是品牌-价格，价格-品牌，(或者可以想象，博客标签对标题，标题对标签，这种多对多),颠倒下顺序，因为实际用户使用可能会根据这个顺序来查，这就叫冗余索引。
传输得快
要想数据传输得快，那就得少传点数据了，因此，只查需要用到的字段就行了。
原文链接:mysql优化之索引使用
网友评论