MySQL 索引优化分析：为啥你的SQL慢？为啥你建的索引常失效？( 二 ) _MySQL

初步优化：为order_level,input_date 创建复合索引

文章插图

创建复合索引后你会惊奇的发现，和没创建索引一样？？？都是全表扫描，都用到了文件排序。是索引失效？还是索引创建失败？我们试着看看下面打印情况

文章插图

将select * from 换成了 select order_level,input_date from 后。type从all升级为index，表示（full index scan）全索引文件扫描，Extra也显示使用了覆盖索引。可是不对啊！！！！检索虽然快了，但返回的内容只有order_level和input_date 两个字段，让业务同事怎么用？难道把每个字段都建一个复合索引？
MySQL没有这么笨，可以使用force index 强制指定索引。在原来的sql语句上修改 force index(idx_order_levelDate) 即可。

文章插图

再次优化：订单级别真的要排序么？
其实给订单级别排序意义并不大，给订单级别添加索引意义也不大。因为order_level的值可能只有，低，中，高，加急，这四种。对于这种重复且分布平均的字段，排序和加索引的作用不大。
我们能否先固定 order_level 的值，然后再给 input_date 排序？如果查询效果明显，是可以推荐业务同事使用该查询方式。

文章插图

和之前的sql比起来，type从index 升级为 ref(非唯一性索引扫描) 。索引的长度从68变成了5，说明只用了一个索引。ref也是一个常量。Extra 为Using index condition 表示自动根据临界值，选择索引扫描还是全表扫描。总的来说性能远胜于之前的sql 。
上面两个案例只是快速入门，我们需严记一点：优化是基于业务逻辑来的。绝对不能为了优化而擅自修改业务逻辑。如果能修改当然是最好的。
索引简介
官方定义：索引（Index）是帮助MySQL高效获取数据的数据结构。
大家一定很好奇，索引为什么是一种数据结构，它又是怎么提高查询的速度？我们拿最常用的二叉树来分析索引的工作原理。看下面的图片：

文章插图

创建索引的优势
1 提高数据的检索速度，降低数据库IO成本：使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
2 降低数据排序的成本，降低CPU消耗：索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则真好降低了排序的成本。
创建索引的劣势
1 占用存储空间：索引实际上也是一张表，记录了主键与索引字段，一般以索引文件的形式存储在磁盘上。
2 降低更新表的速度：表的数据发生了变化，对应的索引也需要一起变更，从而减低的更新速度。否则索引指向的物理数据可能不对，这也是索引失效的原因之一。
3 优质索引创建难：索引的创建并非一日之功，也并非一直不变。需要频繁根据用户的行为和具体的业务逻辑去创建最佳的索引。
索引分类
我们常说的索引一般指的是BTree（多路搜索树）结构组织的索引。其中还有聚合索引，次要索引，复合索引，前缀索引，唯一索引，统称索引，当然除了B+树外，还有哈希索引（hash index）等。
单值索引：一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：一个索引包含多个列，实际开发中推荐使用
实际开发中推荐使用复合索引，并且单表创建的索引个数建议不要超过五个
基本语法：
创建：
create [unique] index indexName on tableName (columnName...)alter tableName add [unique] index [indexName] on (columnName...)删除：
drop index [indexName] on tableName查看：
show index from tableName哪些情况需要建索引：
1 主键，唯一索引
2 经常用作查询条件的字段需要创建索引
3 经常需要排序、分组和统计的字段需要建立索引
4 查询中与其他表关联的字段，外键关系建立索引
哪些情况不要建索引：
1 表的记录太少，百万级以下的数据不需要创建索引
2 经常增删改的表不需要创建索引
3 数据重复且分布平均的字段不需要创建索引，如 true,false 之类。
4 频发更新的字段不适合创建索引