MySQL索引

索引原理

• 目的：提高查询效率。
• 比喻：类似于书的目录，先定位到章，再定位到小节，然后得到对应的页码。
• 本质：
  • ①通过不断地缩小获取数据的范围，以筛选得到最终想要的结果。
  • ②将随机事件变为顺序事件，总是可以用同一种方式来查找目标数据。

磁盘IO与预读

• 磁盘IO：磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分。
  • 寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；
  • 旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；
  • 传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右。
• 预读：考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。

索引的数据结构

每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级

• B+树
  • 索引字段要尽量的小：
    IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。
  • 索引的最左匹配特性：
    当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

聚集索引和辅助索引

• 聚集索引

  • 1.纪录的索引顺序与无力顺序相同，因此更适合between and和order by操作。
  • 2.叶子结点直接对应数据，从中间级的索引页的索引行直接对应数据页。
  • 3.每张表只能创建一个聚集索引

• 非聚集索引

  • 1.索引顺序和物理顺序无关。
  • 2.叶子结点不直接指向数据页。
  • 3.每张表可以有多个非聚集索引，需要更多磁盘和内容,多个索引会影响insert和update的速度。

MySQL常用索引

• 普通索引(Index)：加速查找
• 唯一索引：加速查找+约束
  • 主键索引(Primary Key)：不为空、不重复
  • 唯一索引(Unique)：不重复
• 联合索引
  • PRIMARY KEY(id,name):联合主键索引，允许将多个列的组合定义为主键。
  • UNIQUE(id,name):联合唯一索引，可以在多个列上创建联合唯一索引，要求索引列的组合值唯一，但允许单个列的值重复。
  • INDEX(id,name):联合普通索引，可以在多个列上创建联合普通索引，允许索引列的组合值重复出现，用于加速查询而非唯一性约束。
  • 在第一个键相同的情况下，已经对第二个键进行了排序处理。

索引类型

• hash：查询单条快，范围查询慢。
• btree：b+树，层数越多，数据量指数级增长（innodb默认）。

引擎支持

• InnoDB: 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
• MyISAM: 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
• Memory: 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
• NDB: 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
• Archive: 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引。

创建和删除索引

#方法一：创建表时
    　　CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC]) 
                );


#方法二：CREATE在已存在的表上创建索引
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;


#方法三：ALTER TABLE在已存在的表上创建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名[(长度)]  [ASC |DESC]) ;
                             
#删除索引：DROP INDEX 索引名 ON 表名字;

索引正确用法

• 范围问题:条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like，要尽可能缩小范围，加上索引后，才能得到较高的查询速度，否则索引可能影响不大。
• 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1。
• 索引列不能在条件中参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。
• and 和 or：从左到右，应该将判断范围越小的字段放在前面，这样便可以快速锁定很小的范围，加速查询。
• 最左前缀匹配原则，非常重要的原则，对于组合索引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的顺序可以任意调整。
• 无用索引反而会降低查询速度。
• 其他事项
  • 避免使用select *
  • 使用count(*)
  • 创建表时尽量使用 char 代替 varchar
  • 表的字段顺序固定长度的字段优先
  • 组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
  • 尽量使用短索引
  • 使用连接（JOIN）来代替子查询(Sub-Queries)
  • 连表时注意条件类型需一致
  • 索引散列值（重复少）不适合建索引，例：性别不适合

覆盖索引

• InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。
• 好处：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。