一、索引是什么

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质： 索引是数据结构。可以简单理解为排好序的快速查找数据结构。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：
image.png
左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指 针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

二、索引优劣势

1、优势

  • 提高数据检索的效率，降低数据库的IO成本。

  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

    2、劣势

  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

    三：索引结构

    索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的，所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下四种索引：
  1. BTREE索引：最常见的索引类型，大部分索引都支持B树索引。
  2. HASH索引：只有Memory引擎支持，使用场景简单
  3. R-tree索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间的数据类型，通常使用较少，不做特别介绍。
  4. Full-text（全文索引）：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从MySQL5.6版本开始支持全文索引。

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引，其中聚集索引，符合索引，前缀索引，唯一索引默认都是使用B-tree树索引，统称为索引。

四：BTREE索引

（1）BTREE结构

BTREE又叫多路平衡搜索树，一颗m叉的BTREE特性如下：

  1. 树中每个节点最多包含m个孩子。
  2. 除根节点与叶子节点外，每个节点至少有【ceil(m/2)】个孩子
  3. 若根节点不是叶子节点，则至少有两个孩子
  4. 所有的叶子节点都在同一层
  5. 每个非叶子节点由n个key与n+1个指针组成，其中【ceil(m/2)-1】<=n<=m-1

BTREE树和二叉树相比，查询数据的效率更高，因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度会更快。

（2）B+TREE结构

B+Tree为BTREE的变种，B+TREE与BTREE的区别为：

  1. n叉B+TREE最多含有n个key，而BTREE最多含有n-1个key。
  2. B+TREE的叶子节点保存所有的key信息，依key大小顺序排列。
  3. 所有的非叶子节点都可以看做是key的索引部分。

由于B+TREE只有叶子节点保存key信息，查询任何key都要从root走到叶子，所以B+TREE的查询效率更加稳定。

（3）MySQL中的B+TREE

MYSQL索引数据结构对经典的B+TREE进行了优化，在原B+TREE的基础上，增加一个指向相邻叶子节点的链表指针，就形成了 带有顺序指针的B+TREE，提高区间访问（即范围搜索）的性能。

五：索引分类

  1. 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
  2. 唯一索引：索引列的值必须唯一，但允许有空值（运行包含多个空值）
  3. 复合索引：即一个索引包含多个列

    六：索引语法

    索引在创建表的时候，可以同时创建，也可以随时增加新的索引。

    （1）创建索引

    1. CREATE 【UNIQUE|FULLTEXT|SPATIAL】 INDEX index_name #index_name代表索引名称
    2. 【USING index_type】 #【USING index_type】指定索引用哪个类型，不指定则默认为BTREE类型
    3. ON tbl_name(index_col_name...)
    4. # 在mysql当中，如果一个键为主键，则他默认就有主键索引
    5. # 例如：
    6. CREATE INDEX idx_city_name ON city(city_name);

    （2）查看索引

    1. show index from table_name;

    （3）删除索引

    1. drop index index_name on table_name;

    （4）ALTER命令

    ```sql alter table ta_name add primary key(column_list);

    该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL

alter table tb_name add unique index_name(column_list);

这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）

alter table tb_name add index index_name(column_list);

添加普通索引，索引值可以出现多次

alter table tb_name add fulltext index_name(column_list);

该语句指定了索引为FULLTEXT，用于全文索引

  1. <a name="s6PV4"></a>
  2. # 七：索引设计原则
  3. 索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。
  5. 1. 对查询频次较高，且数据量比较大的表建立索引
  6. 1. 索引字段的选择，最佳候选列应当从where字句的条件中提取，如果where字句中的组合较多，那么应当挑选最常用、过滤效果最好的列组合。
  7. 1. 使用唯一索引，区分度越高，使用索引的频率越高。
  8. 1. 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多 ，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MYSQL也会犯选择困难症，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
  9. 1. 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率，假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MYSQL访问索引的I/O效率。
  10. 1. 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时对where字句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。
  11. ```sql
  12. # 创建组合索引
  13. create index idx_name_email_status on tb_seller(NAME,email,STATUS);
  14. # 就相当于
  15.     #对name创建索引
  16.   #对name,email创建了索引
  17.   #对name,email,status创建了索引

八：建立索引选择

8.1 需要建索引的情况

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其他表关联的字段，外键关系建立索引
  • 频繁更新的字段不适合创建索引——因为每次更新不单单是更新了记录还会更新索引
  • where条件里用不到的字段不创建索引
  • 单键/组合索引的选择问题（在高并发下倾向创建组合索引）
  • 查询中排序的字段，排序字段若通过索引去访问 将大大提高排序速度

  • 查询中统计或者分组字段适合创建索引

    8.2 不需要建索引的情况

  • 表记录太少（300万以上可建索引）

  • 经常增删改的表不建索引
  • 数据重复且分布平均的表字段不建立索引（因此应该只为最经常查询和最经常排序的数据列建立索引）
  • 注意：如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
  • 举例：
    • 假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建立索引一般不会提高数据库的查询速度。
    • 索引的选择性是指索引列中不通知的数目与表中记录数的比，如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。