mysqls索引

使用索引的全部意义就是通过缩小一张表中需要查询的记录/行的数目来加快搜索的速度


索引的最左匹配特性。


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




对于一个22.1G容量的表，也只需要高度为3的B+树就能存储了，这个容量大概能满足很多应用的需要了。如果把高度增大到4，则B+树的存储容量立刻增大到25.9T之巨




1. 优化更需要优化的 Query;
2. 定位优化对象的性能瓶颈;
3. 明确的优化目标;
4. 从 Explain 入手;
5. 多使用 profile
6. 永远用小结果集驱动大的结果集;
7. 尽可能在索引中完成排序;
8. 只取出自己需要的 Columns;
9. 仅仅使用最有效的过滤条件;
10. 尽可能避免复杂的 Join 和子查询;


#什么时候需要建立索引
1.较频繁的作为查询条件的字段应该创建索引;
2.唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
3.更新非常频繁的字段不适合创建索引
4.不会出现在 WHERE 子句中的字段不要创建索引
#建议
1. 对于单键索引,尽量选择针对当前 Query 过滤性更好的索引;
2. 在选择组合索引的时候,当前 Query 中过滤性最好的字段在索引字段顺序中排列越靠前越好;
3. 在选择组合索引的时候,尽量选择可以能够包含当前 Query 的 WHERE 子句中更多字段的索引;
4. 尽可能通过分析统计信息和调整 Query 的写法来达到选择合适索引的目的而减少通过使用Hint 人为控制索引的选择,因为这会使后期的维护成本增加,同时增加维护所带来的潜在风险。
#MySQL 中索引的限制
1. MyISAM 存储引擎索引键长度总和不能超过 1000 字节;
2. BLOB 和 TEXT 类型的列只能创建前缀索引;
3. MySQL 目前不支持函数索引;
4. 使用不等于(!= 或者 <>)的时候 MySQL 无法使用索引;
5. 过滤字段使用了函数运算后(如 abs(column)),MySQL 无法使用索引;
6. Join 语句中 Join 条件字段类型不一致的时候 MySQL 无法使用索引;
7. 使用 LIKE 操作的时候如果条件以通配符开始( '%abc...')MySQL 无法使用索引;
8. 使用非等值查询的时候 MySQL 无法使用 Hash 索引;










四、type
表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL, index,  range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成