MySQL索引原理及查询优化

其实在工作中有去优化mysql语句，但之前优化仅仅是降到能够接受花费时间之下，并有很多可以继续提供的空间。很多时候在优化完成之后sql，也并不能向外展示。故这里将自己平时优化的sql方法记录下来，并找到一个总结优化sql的地方。

索引原理
mysql的创建索引其实就像是字典的目录有一定的相似之处，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果。我们索引原理可以通过了解为什么数据库的索引需要使用B+树，来了解索引的原理。

mysql的数据存储在磁盘之中，mysql的获取数据需要将mysql中数据读取到内存之中，这个过程可以叫IO,这段时间需要耗费的时间是比较大的。当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)，具体一页有多大数据跟操作系统有关，一般为4k或8k。如果没有索引，mysql就需要去读去整个数据库里面的内容。我们经常接触到查找数据结构一般有 二叉树、平衡二叉树、红黑树、再到B-树、B+树、哈希索引。

为什么从这些结构选择了B+树作为mysql的索引结构呢？对比二叉树、平衡二叉树、红黑树 ，B+树的子节点更少，这样可以缩小整个树的高度，这样可以缩小IO读取的数据的次数，判断的次数更小。对于B-树相对于B+数据，B-Tree因为非叶子结点也保存具体数据，所以在查找某个关键字的时候找到即可返回。而B+Tree所有的数据都在叶子结点，每次查找都得到叶子结点。所以在同样高度的B-Tree和B+Tree中，B-Tree查找某个关键字的效率更高。但由于B+Tree所有的数据都在叶子结点，并且结点之间有指针连接，在找大于某个关键字或者小于某个关键字的数据的时候，B+Tree只需要找到该关键字然后沿着链表遍历就可以了，而B-Tree还需要遍历该关键字结点的根结点去搜索。更重要的是由于B-Tree的每个结点（这里的结点可以理解为一个数据页）都存储主键+实际数据，而B+Tree非叶子结点只存储关键字信息，而每个页的大小有限是有限的，所以同一页能存储的B-Tree的数据会比B+Tree存储的更少。这样同样总量的数据，B-Tree的深度会更大，增大查询时的磁盘I/O次数，进而影响查询效率。Hash索引在不存在hash碰撞的情况下，之需一次读取，查询复杂度为O（1），比B+树快。但hash只能用于等值查询，不能用于范围查询。并且b+数据叶子节点的数据是连接在一起的，而hash数据是更加散乱，我们在获取数据的时候基本都是一批数据。所以hash也并不适用。

我们在对优化sql的时候，对mysql的索引有个大概印象就可以，并不需要了解非常深入。

SQL优化步骤

1、先运行看看是否真的很慢，注意设置SQL_NO_CACHE。
2、explain查看执行计划，特别需要注意是否连接查询中很多rows很大的值。
（rows很大，很多是必要的子查询、连接查询的对索引字段进行的操作、从锁定记录较少的表开始查询）

3、在连接查询的时候我们并不需要所有数据，可以把需要 排序、限制条数的表先进行排序、限定条数，再进行关联。

4、调整使用的索引。

5、如果还是不能满足要求需要重新 从第二步开始。

创建索引的几大原则：
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)的索引，那么只需要修改原来的索引即可。