MySQL索引

索引

添加索引

• create 创建索引时必须指定索引名
• alter table 创建索引时可不指定索引名
  • 默认为字段名，主键除外

-- 唯一索引: 在table_name 表中col_name 字段添加唯一索引index_name
create unique index index_name
on table_name (col_name [(length)] [asc / desc])

-- 主键索引: 在table_name 表中col_name 列添加主键索引
alter table table_name add primary key (col_name)

/* 
主键索引 和 唯一索引 在指定字段约束时默认为索引，
或建表之后使用以上方式添加
*/	

-- 普通索引 (所有索引都可用这两种创建方式)
-- 方式1
create index index_name on tab_name (col_name [(length)] [asc / desc])
-- 方式2
alter table `table_name` add index [index_name](index_col_name)	
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

删除索引

• 删除主键索引 primary key 和唯一索引 unique 会同时删除约束
• 修改索引：删除原字段索引，重新创建

-- 在table_name 表中删除 index_name 索引
drop index index_name on table_name 

-- 删除主键索引
-- 从table_name表中删除主键索引
alter table `table_name` drop primary key 
1
2
3
4
5
6

查询索引

show index from table_name;			-- 方式1
show indexes from table_name;		-- 方式2
show keys from table_name;			-- 方式3
desc table_name;					-- 方式4，在表结构中看索引，但没有前三种索引信息详细
1
2
3
4

索引种类

# 查询表的索引
show indexes from table_name;		-- 查询表 table_name 中所有索引
1
2

• 在表结构中 Non_unique：0 是唯一索引，1 是普通索引

主键索引

• 字段定义为主键后自动为主键索引（类型 primary key）

  • 字段值唯一且不允许为空

  • id int primary key , 	-- 主键约束，自动为主键索引
    1

唯一索引

• 字段定义为 unique 后自动为唯一索引

  • 字段值唯一，允许为 null

  • card_id int unique , 	-- 唯一约束，自动为唯一索引
    1

联合索引

• 最左匹配原则

  • 以最左边为起点任何连续的索引都能匹配上
    • 如果第一个字段是范围查询需要单独建一个索引
    • 创建联合索引时，根据业务需求 where 子句中使用最频繁的一列放在最左边
      • 扩展性比较好
    • 例如：name 经常作为查询条件，salary 不常使用
      • name 放在联合索引的第一位置，即最左边
      • alert table emp add index name_salary (name,salary);
  • 通过索引查询时
    1. select * from emp where name = '';
       • 满足最左特性，只是部复合索引也生效
    2. select * from where salary = xxx;
       • 没有最左边的字段，不满足最左特性，索引失效
    3. slect * from emp where name = '' and salary = xxx;
       • 复合索引全使用，按顺序出现 name、salary，索引生效
    4. selct * from emp where salary = xxx and name = '';
       • 违背最左特性，但 MySQL 执行SQL时会进行优化，底层进行颠倒优化
       • 索引生效

• 多字段联合添加索引

  • 单列索引：节点中关键字[name] 及索引的关键字的值为那么对应的值，比如 张三。

  • 联合索引：节点中关键字[name,phoneNum]，比如张三，138888888。

  • 联合索引列选择原则

    1. 经常用的列优先 【最左匹配原则】
    2. 选择性（离散度）高的列优先【离散度高原则】
    3. 宽度小的列优先【最少空间原则】

  • 创建联合索引的时，如：(k1,k2,k3)

    • 相当于创建（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。

    • 联合索引不满足最左原则，索引一般会失效

• 索引下推：select * from table where name = ? and age = ?

• MySQL5.6 及以后的版本出现

• 原查询过程：先根据 name 在存储引擎中获取数据，后根据 age 在 server 层过滤

• 有索引下推后：根据 name、age 在存储引擎获取数据，返回对应的数据，不再到 server 层过滤

• 使用 Explain 分析SQL语句时出现 index condition pushdown 就是使用了索引下推

  • 索引下推在组合索引的情况出现几率最大

单列索引

• 单一字段添加索引

  • 多个单列索引在多条件查询时优化器会优先选择最优索引策略
    • 可能只用一个索引，也可能将多个索引全用上
    • 但会创建多个B+索引树，占用空间，也会浪费一定搜索效率
      • 如果只有多条件联合查询时建议使用联合索引

• 同时存在联合索引和单列索引（字段有重复）

  • 涉及到 MySQL 本身的查询优化器策略

    • 当一个表有多条索引可走时，mysql 根据查询语句的成本来选择使用哪条索引

  • MySQL 执行优化器会对其进行优化

    • 当不考虑索引时，where 条件顺序对效率没有影响，真正有影响的是是否用到了索引！

全文索引

• MyISAM 存储引擎支持全文索引
  • 用于查找文本中的关键词，而不是直接比较是否相等
  • 查找条件使用 MATCH AGAINST，而不是普通的 WHERE
• 全文索引一般使用倒排索引实现，记录着关键词到其所在文档的映射
  • InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引
• fulltext
  • MySQL自带，并不好用
• 一般开发考虑使用全文搜索 Solr 和 ElasticSearch（ES）

聚簇索引

定义

• 聚簇索引、非聚簇索引也被称之为主索引、二级索引
• 数据跟索引存储在一个文件里，就是聚簇索引，否则就是非聚簇索引
  • 聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引
• 聚簇索引默认是主键
  • 若表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替
    • 如果没有 InnoDB 会隐式定义一个主键作为聚簇索引
    • InnoDB 只聚集在同一个页面中的记录，包含相邻键值的页面可能相距甚远
  • 如果已经设置了主键为聚簇索引，必须先删除主键，然后添加想要的聚簇索引，最后恢复设置主键即可
    • 此时其他索引只能被定义为非聚簇索引

场景

• InnoDB 使用聚簇索引将主键组织到一棵B+树中，行数据就储存在叶子节点

  • 使用 where id = 14 条件查找主键，按照B+树的检索算法即可查找到对应的叶节点，获得行数据
  • 对 Name 列进行条件搜索需要两个步骤
    1. 在辅助索引B+树中检索 Name，到达其叶子节点获取对应的主键
    2. 使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据
       • 通过其他键需要建立辅助索引

• MyISAM 使用非聚簇索引

  • 两棵B+树节点的结构完全一致，只是存储的内容不同
    • 主键索引 B+ 树的节点存储主键
    • 辅助键索引 B+ 树存储辅助键
    • 表数据存储在独立的地方，两颗 B+ 树的叶子节点都使用一个地址指向真正的表数据
      • 对于表数据两个键没有任何差别
      • 索引树是独立的，通过辅助键检索无需访问主键的索引树

• 辅助索引：InnoDB 中在聚簇索引上创建的索引

  • 辅助索引访问数据总是需要二次查找
  • 非聚簇索引都是辅助索引
    • 复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不是行的物理位置，而是主键值

优劣

• 优势
  1. 加载同一页的行数据到 buffer 中，再次访问从内存中而不必进行磁盘 IO
     • 主键和行数据一起加载，通过叶子节点就可以直接返回数据
     • 按照主键Id组织会更快获得数据
  2. 辅助索引使用主键作为指针，减少出现行移动或者数据页分裂时辅助索引的维护工作
     • 主键值作指针会占用更多的空间，但 InnoDB 移动行时无须更新辅助索引中的指针
       • 行的位置会随数据的修改而变化
         • 前面的 B+ 树节点分裂以及 Page 的分裂
           • 行位置在实现中通过 16K 的 Page 来定位
       • 聚簇索引保证辅助索引数不受主键B+树的节点变化影响
  3. 聚簇索引适合用在排序的场合，非聚簇索引不适合
  4. 取出一定范围数据的时使用聚簇索引
  5. 二级索引需要两次索引查找，而不是一次才能取到数据
     • 存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键
     • 第二次在聚簇索引中用主键再次查找索引，再找到数据
  6. 把相关数据保存在一起
     • 例如：实现电子邮箱可以根据用户 ID 来聚集数据
       • 只需从磁盘读取少数的数据页就能获取某个用户的全部邮件
       • 如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 I/O
• 缺点
  1. 维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)时
     • 建议在大量插入新行后，选在负载较低的时间段通过 OPTIMIZE TABLE 优化表
       • 因为必须被移动的行数据可能造成碎片，使用独享表空间可以弱化碎片
  2. 表因为使用 UUId（随机ID）作为主键，使数据存储稀疏
     • 会出现聚簇索引有可能有比全表扫面更慢
     • 建议使用 int 的 auto_increment 作为主键

非聚簇索引

• 将数据存储与索引分开结构

  • 索引结构的叶子节点指向了数据的对应行
  • MyISAM 通过 key_buffer 把索引先缓存到内存中
    • 需要通过索引访问数据时在内存中直接搜索索引，通过索引找到磁盘相应数据

• 使用聚簇索引和非聚簇索引

• 回表：从非聚簇索引跳转到聚簇索引中查找数据

  • 查询的字段为非聚簇索引，但非聚簇索引中没有将需要查询的字段全部包含就是回表
  • 例如：id 为主键索引，name 为普通索引
    • 查询语句：select id, name, age from table where name = 'kaka';
      1. 在 name 的 B+Tree 中寻找到主键 id
      2. 根据主键 id 的索引获取到数据并返回
         • 这个过程就被称为回表
    • 非聚簇索引 name 的叶子节点只有 id 没有age，所以跳转到聚簇索引中，根据id在查询整条记录返回需要的字段数据

结构

• 索引是在存储引擎层实现的，而不是在服务器层实现的
  • 所以不同存储引擎具有不同的索引类型和实现
• MySQL 常用两种索引结构(算法)
  • BTree、Hash
  • 两种算法检索方式不一样，对查询的作用也不一样

Hash

• Hash 索引的底层由 Hash 表实现的
  • 非常适合以 key-value 的形式查询，即：单个 key 查询，或等值查询
• 哈希索引能以 O(1) 时间进行查找，但失去了有序性
  • 无法用于排序与分组
• 使用 BTree 比 Hash 多：Hash 本身由于其特殊性，带来了很多限制和弊端
  1. Hash 索引仅能满足 =、IN、<=> 查询，不能使用范围查询
  2. 联合索引中，Hash 索引不能利用部分索引键查询
     • 对于联合索引中的多个列，Hash 要么全部使用，要么全部不使用，不支持 BTree 支持的联合索引的最优前缀
  3. Hash 索引无法避免数据的排序操作
     • Hash 索引中存放的是经过 Hash 计算后的值
     • 且值的大小关系并不一定和 Hash 运算前的键值完全一样
       • 数据库无法利用索引的数据来避免任何排序运算
  4. Hash 索引任何时候都不能避免表扫描
     • Hash 索引将索引键运算后，运算结果和所对应的行指针信息存放于 Hash 表中
     • 不同索引键可能存在相同 Hash 值
       • 即使满足某个 Hash 键值的数据的记录条数，也无法从Hash索引中直接完成查询
       • 要通过访问表中的实际数据进行比较，并得到相应的结果
     • 遇到大量 Hash 值相等的情况后性能并不一定比 BTree 高
• InnoDB 存储引擎有特殊的功能：自适应哈希索引
  • 某个索引值被使用的非常频繁时，在 B+Tree 索引之上再创建一个哈希索引
  • 让 B+Tree 索引具有哈希索引的一些优点
    • 比如快速的哈希查找

BTree

分类

1. 相对平衡二叉树：BTree

   • 左右两个子树的高度差的绝对值不超过 1
   • B 代表平衡：balance
   • 缺点
     1. 太深：数据处的高/深度决定 IO 操作次数，IO 操作耗时大
     2. 太小：每一个磁盘块（节点/页）保存的数据量太小

2. 多路平衡查找树：B-Tree

   1. B树 不同于二叉树，节点可以存储多个关键字和多个子树指针
   2. m 阶的 B 树要求除根节点以外，所有的非叶子子节点必须要有 [m/2,m] 个子树
   3. 根节点必须只能有两个子树
      • 允许根节点一个节点的情况
   4. 是一个查找二叉树
      • 越靠前的子树越小，并且同一个节点内，关键字按照大小排序
   5. B树的一个节点要求子树的个数等于关键字的个数+1
   • 

3. 加强版多路平衡查找树：B+Tree；B-Tree plus 版

   • 支节点只保存索引列关键字，不保存数据，只有叶子节点才保存数据
   • 可用于 =,>,>=,<,<= 和 between 这些比较操作符
     • 还可用于 like 操作符，最左侧不能是 %
   1. 将所有的查找结果放在叶子节点中
      • 查找 B+ 树必须到叶子节点才能返回结果
   2. B+ 树每一个节点的关键字个数和子树指针个数相同
   3. B+ 树的非叶子节点的每一个关键字对应一个指针
      • 关键字则是子树的最大，或者最小值
      • 

B+tree

• 基于 BTree 和叶子节点顺序访问指针进行实现

  • 具有 BTree 的平衡性，且通过顺序访问指针来提高区间查询的性能

• 用于查找，还可以用于排序和分组。

  • 可指定多个列作为索引列，多个索引列共同组成键

  • 适用于全键值、键值范围和键前缀查找

    • 键前缀查找只适用于最左前缀查找

  • InnoDB 的 B+Tree 索引分为主索引和辅助索引

    • 主索引的叶子节点 data 域记录完整的数据记录，被称为聚簇索引
      • 无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引
    • 辅助索引的叶子节点的 data 域记录主键的值
      • 因此使用辅助索引进行查找时，需要先查找到主键值，再到主索引中进行查找

• B+Tree 中一个节点的 key 从左到右非递减排列

  • 某个指针的左右相邻 key 分别是 keyi 和 keyi+1，且不为 null

  • 该指针指向节点的所有 key >= keyi 且 <= keyi+1

• 查找

  1. 在根节点进行二分查找，找到一个 key 所在的指针

  2. 递归地在指针所指向的节点进行查找。直到查找到叶子节点

  3. 在叶子节点上进行二分查找，找出 key 所对应的 data

     • 插入删除操作记录会破坏平衡树的平衡性
       • 插入删除操作之后，需要对树进行分裂、合并、旋转等操作来维护平衡性

• B+Tree 与 B-Tree 的区别

  1. B+ 节点关键字搜索采用闭合区间
     • MYSQL 推崇使用 ID 作为索引
       • 由于 ID 是自增的数字类型，只会增大，所以采用向右拓展的方式
  2. B+ 非叶节点不保存数据相关信息，只保存关键字和子节点的引用。
  3. B+ 关键字对应的数据保存在叶子节点中
  4. B+ 叶子节点是顺序排列的，且相邻节点具有顺序引用的关系

• 为什么选择 B+Tree ？

  1. B+树 是 B-树 的变种，多路绝对平衡查找树，拥有B-树的优势
  2. B+树 扫库、表能力更强
  3. B+树 的磁盘读写能力更强
  4. B+树 的排序能力更强
  5. B+树 的查询效率更加稳定

机制

检索机制

• 索引相当于目录，通过目录定位查找数据
• 检索方式
  • 全局查找
  • 索引查找
• 未使用索引时，select 查询默认全表扫描，找到所有符合条件的记录
  • 即使已经查找到符合条件的记录仍会继续扫描
    • 数据库不知道是否还有其它符合条件的记录

执行流程

• 数据结构：B + tree

  • 索引会将该字段所有记录建立二叉树结构
    • 将数据分区存放
    • 一侧存放小于某数据的数据，另一侧存放大的
    • 两侧继续按此规则建立二叉树，直到没有数据，形成总二叉树

• 执行流程

  1. 检索时首先查看条件字段是否有索引

  2. 有索引对象时通过索引检索

     • 索引生成时已经排序

  3. 通过索引定位数据

     • 得到数据的物理地址

  4. 原 select 语句转为 select ... where 物理地址 = 检索到的物理地址

     • 直接从硬盘中按物理地址查找数据，不再通过表

• 例如：select ename from emp where ename = 'SMITH';

  1. 查看 ename 字段，发现该字段有索引

  2. 通过 ename 字段索引对象检索

     • 索引已经对字段进行了排序

       • 先检索 S 区，再 M 区 …

     • 减少扫描次数，快速定位数据

  3. 查找到记录的物理地址 假如：0x00011

  • 数据库中每条记录都有物理地址
    • Oracle 中叫做 rowId
  4. sql 语句变形为 select ... where 物理地址 = 0x00011
  • 此时检索数据不通过表
  • 通过物理地址定位硬盘数据

• 优、劣

  • 优点：优化查询速度

    • 缩小了扫描范围，只扫描条件字段
    • 且条件字段已经排序

  • 缺点

    • 索引本身也占用空间
      • 在创建索引的表中
      • 根据表的存储引擎不同在硬盘或内存中
    • 创建索引只对创建索引的字段查询效率有优化
    • 对 DML 语句会有影响
      • 执行 DML 语句会对二叉树结构造成有影响，要对索引结构维护
        • DML 语句执行速度会受影响
      • 数据库90%以上操作是select，所以索引很有必要

注意事项

使用场景

1. 数据量庞大

2. 较频繁作为查询条件的字段应创建索引

3. 唯一性太差的字段不适合单独创建索引

   • 例如：性别

4. DML 操作非常频繁的字段不适合创建索引

   • 例如：时间戳、登录次数

5. 不会出现在 where 字句的字段不应该创建索引

索引失效

1. like 子查询，% 放前面

2. 非空判断 is not null

3. or 语句前后没有同时使用索引

   • 当 or 左右查询字段只有一个是索引，该索引失效
   • 只有当 or 左右查询字段均为索引时，才会生效；
   • SQL优化要避免写or语句

4. 数据类型出现隐式转化

   • 如 varchar 不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描

索引优化

1. 查询时，索引字段不能是表达式的一部分，也不能是函数的参数，否则无法使用索引
2. 多条件查询时，使用多列索引比使用多个单列索引性能更好
3. 选择性最强的索引列放在前面
   • 选择性: 不重复的索引值和记录总数的比值
     • 最大值为 1，每个记录都有唯一的索引与其对应
     • 选择性越高，查询效率也越高
   • 离散性越高选择性就越好
     • 比如：性别字段索引，男为1，女为0 生成索引树：
       • 搜索女的数据，在根节点触发两条路可以走
       • 从中间走下去可以选择的线路太多会导致搜索引擎懵逼
         • 优化器对sql优化索引扫描不如全表扫描
           • 导致离散型降低，不利于性能
4. 对于 BLOB、TEXT 和 VARCHAR 类型字段使用前缀索引，只索引开始的部分字符。
   • 前缀长度根据索引选择性来确定
5. 覆盖索引：索引包含所有需要查询的字段的值
   1. 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量
      • 可减少数据库 IO，将随机 IO 变为顺序 IO，可提高查询性能
   2. 一些存储引擎(例如 MyISAM)在内存中只缓存索引，而数据依赖于操作系统来缓存
      • 因此只访问索引可以不使用系统调用(通常比较费时)
   3. 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引
      • 例如：联合索引 create index name_phoneNum on users(name,phoneNum)
        • sql：select name, phoneNum from ... ; 使用覆盖索引