《mysql学习》-- 索引

mysql

参考：MySQL索引原理及慢查询优化

​ 理解MySQL——索引与优化

索引

索引是在存储引擎中实现的，而不是在mysql服务器层实现。每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。

一次查询只能使用一个索引

索引类型

从数据结构角度

• btree索引

  B-Tree的基本思想是，所有值（被索引的列）都是排过序的，每个叶节点到跟节点距离相等，平衡树索引,btree索引中的节点都是按索引列中的顺序存储的，B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点，需要多次的IO访问

  InnoDB和MyISAM都支持B-Tree索引。InnoDB用的是一个变种B+Tree，而MyISAM为了节省空间对索引进行了压缩，从而牺牲了性能。

  • btree索引使用场景

  • 等值匹配
    可用于= != <> IN NOT IN <=>查询语句的优化
  • 范围匹配
    可用于 > >= < <= BTEWEEN AND等范围查询语句的优化
  • 匹配最左前缀
    对于 name like bai% 这种后模糊匹配的查询,是可以利用name字段上建立的索引来优化查询的,但是对于name like %bai这种前模糊匹配的查询则没有办法使用索引了如假如创建一个索引 fisrt+middle+last，不能跳过fisrt直接使用middle进行索引查询，如果查询条件为 where first = ‘x’ and middle like ‘x%’ and last = ‘11’,这样只会使用到first+middle，而不会用到last
  • 覆盖索引
    B-Tree索引的key存放的是字段的值,如果key中包含所有需要查询字段的值,我们就称之为覆盖索引,覆盖索引能够极大的提高性能。
  • 排序
    B-Tree索引是排好序的,所以MySQL可以用来优化ORDER BY 和 GROUP BY等操作。

• hash索引：

  哈希索引基于哈希表实现,只有Memory引擎显示支持哈希索引,使用哈希索引可以一次定位,所以 Hash 索引的查询效率要远高于 B-Tree 索引。但是哈希索引是有很多限制的:

键值对，键是hash码，值是数据位置

主索引不能使用hash索引。复杂度o(1),适用于等值条件和in(),

不适用于对键依赖于索引进行排序

基于hash表，所以不适用于范围查找，即>、<，因为键值已经做成hash码了 无法使用索引排序，不支持键前缀查找(like xx%)

• 全文索引：

全文索引是MyISAM的一个特殊索引类型，主要用于全文检索

• 空间索引(R-Tree)：

MyISAM支持空间索引，主要用于地理空间数据类型，如用于对GIS数据类型创建SPATIAL索引

从物理存储角度

参考：MySQL索引详解

• 聚集索引

  索引和数据是在同一个文件,一张表只有一个聚集索引，数据只能按照一种规则存储，InnoDB是聚集索引,Innode的聚集索引实际上是将主键(PRIMARY kEY )与数据行存放在同一个文件的,一张表只能有一个聚集索引。

  InnoDB要求表必须有主键（MyISAM可以没有）,如果没有显式指定,则MySQL系统会用一个唯一且不为空的索引列做为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键。

  InnoDB的普通索引(二级索引)的叶子节点中存放的是PRIMARY KEY的值,所以需要先查询普通索引(二级索引)的叶子节点找到对应的主键值,然后再根据主键值去聚集索引中查询到对应的数据。

  InnoDB将主键与数据聚集在一起的方式,使得按主键顺序的插入和查询效率会很高,但是更新主键的字段或者不按主键的顺序插入数据的代价会比较高,所以主键的选取很重要(使用AUTO INCREMENT字段或者应用程序生成的顺序递增字段要比无序的UUID好的多)

  二级索引会保存主键的值,所以主键的值不要太大。

• 非聚集索引

  索引和数据不在同一个文件，索引通过指针指向数据,myIsam是非聚集索引,MySQL使用索引查询数据时,先到MYI文件中找出数据存储的位置指针,然后再到MYD文件中读取数据。

从逻辑角度

• 主索引：
• 辅助索引：
• 稠密索引：
• 稀疏索引：

什么样的字段不适合建索引？

• 列的值唯一性太小（如性别，类型什么的），不适合建索引

• 太长的列，可以选择只建立部分索引，（如：只取前十位做索引）

建索引建索引的几大原则

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

6. 更新非常频繁的数据不适宜索引

7. 对where后边条件为字符串的一定要加引号，字符串如果为数字mysql会自动转为字符串，但是不使用索引。

例子

• 覆盖索引(Covering Indexes)

  如果索引包含满足查询的所有数据，就称为覆盖索引。覆盖索引是一种非常强大的工具，能大大提高查询性能。只需要读取索引而不用读取数据有以下一些优点：

  1. 索引项通常比记录要小，所以MySQL访问更少的数据；
  2. 索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O；
  3. 大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。
  4. 覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。
  5. 覆盖索引不能是任何索引，只有B-TREE索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同，并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。
  6. 对于索引覆盖查询(index-covered query)，使用EXPLAIN时，可以在Extra一列中看到“Using index”。

  • 例子

  select picname, smallimg from pics where user_id=17853; 如果建立一个(user_id,picname,smallimg)的联合索引会更加快，一个包含查询所需字段的索引称为“覆盖索引”，MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后进行回表操作，减少IO，提高了效率

• 利用索引进行排序

  MySQL中，有两种方式生成有序结果集：一是使用filesort，二是按索引顺序扫描。利用索引进行排序操作是非常快的，而且可以利用同一索引同时进行查找和排序操作。当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时，可以使用索引来排序。如果查询是连接多个表，仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。其它情况都会使用filesort。