1.1 概述

  • 帮助mysql高效获取数据（查询） 的数据结构（有序）
  • 这些数据结构以某种方式引用（指向）数据
  • 查找数据的数据结构叫做索引
  • 一般索引本身很大，往往以索引文件的形式存储在磁盘上，是数据库中用来提高性能的最常用工具。

1.2 优势劣势

  • 优点
    • 类似书籍目录索引，提高数据检索速度，降低数据库I/O成本
    • 对数据进行排序，降低排序成本，降低CPU消耗
  • 缺点
    • 占空间
    • 降低了表的更新速度，需要随着表更新。因此索引字段不是越多越好。

1.3 索引结构

  • 在存储引擎层实现
  • BTREE索引
  • HASH索引：使用场景简单
  • R-tree索引（空间索引）：用于地理空间数据类型
  • Full-text索引（全文索引）

image.png

  • 索引默认是B+树结构组织的

1.3.1 BTREE结构

  • Btree：多路平衡搜索树（m叉）
    • 树中每个节点最多包含m个孩子
    • 除根节点和叶子节点外，每个节点至少有[ceil(m/2)]个孩子
    • 若根节点不是叶子节点，至少有两个孩子
    • 所有叶子节点都在同一层
    • 每个非叶子节点由n个key和n+1个指针组成：ceil(m/2)-1<=n<=m-1

image.png

  • 和二叉树相比，层数更少，搜索速度更快

1.3.2 B+TREE结构

  • n叉B+树最多含有n个key
  • 叶子节点保存所有的key信息，依据key大小顺序排序
  • 所有的非叶子节点都可以看作是key的索引部分

image.png

  • 查询稳定性好一些

1.3.3 MySQL中的B+TREE

  • 在原B+树的基础上增加了一个指向相邻叶子节点的链表指针（双向链表），形成了带有顺序指针的B+树，提高区间访问的性能。
  • 有利于范围搜索

image.png

1.4 索引分类

1、单值索引

  • 一个索引只包含单个列

2、唯一索引

  • 索引列的值必须唯一（每个值都是唯一的），但允许有空值（可以多个NULL）

3、复合索引

  • 一个索引包含多个列

1.5 索引语法

  • 索引在创建表时可以同时创建，也可以随时增加新的索引
  • 表的主键在创建时默认有了主键索引

1.5.1 创建索引

  1. CREATE [unique|fulltext|spatial] INDEX index_name
  2. [USING index_type]
  3. ON tbl_name(index_col_name,...)
  5. index_col_name: column_name[(length)][ASC|DESC]
  1. create index idx_city_name on city(ciry_name); //普通索引

1.5.2 查看索引

  1. show index from city\G; //规范化查看索引

1.5.3 删除索引

  1. drop index idx_city_name on city;

1.5.4 ALTER索引

  • 添加一个主键索引，意味着索引值必须唯一且不为NULL

    1. alter table tb_name add primary key(column_list);

  • 唯一索引

    1. alter table tb_name add unique index_name(column_list);

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

    1. alter table tb_name add index index_name(column_list);

  • 全文索引 ```sql alter table tb_name add fulltext index_name(column_list);

select * from tb_name where match(column) against(‘xxx’);

  1. <a name="TBk5r"></a>
  2. ### 
  3. <a name="wtgTW"></a>
  4. ### 1.5.5 索引设计原则
  6. - 对**查询频次高**，**数据量大**的表建立索引
  7. - 添加索引字段的最佳候选列是在**where**后用得多的列和组合（查询条件中的字段）
  8. - 尽量使用唯一索引
  9. - 不是越多越好，会增加delete、insert、update维护代价
  10. - 使用短索引，能提升访问的I/O速度，节省存储成本
  11. - 利用**最左前缀**，针对N个列建立组合索引，就相当于创建了N个索引，提高查询效率
  12. ```sql
  13. create index idx_name_email_status on tb(name,email,status);
  15. //相当于：
  16. //对name、name+email、name+email+status创建了索引