MySQL数据库——索引（一）

索引的概念：

可以比喻为图书的目录，大量数据时才有意义，定义在字段列中。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引的作用：

提升查询效率，降低增删改效率，所以并不是索引越多越好。一般最少 10W 数据，通常 20W 以上。

约束：

是关系数据库中的对象，用来存放插入到一个表中一列数据的规则，用来确保数据的准确性和一致性。UNIQUE 约束唯一标识数据库表中的每条记录。UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。PRIMARY KEY 拥有自动定义的 UNIQUE 约束。请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

注意：

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引：

• MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换。
• MEMORY/HEAP存储引擎：支持HASH和BTREE索引。

索引的类别：

• 普通索引：仅加速查询，定义在普通列上，允许重复的列中插入重复值和空值。
• 唯一索引：加速查询 + 索引列数据不重复(可以有null)。当在表中创建了唯一约束时，数据库会自动在该列创建唯一索引。
• 主键索引：加速查询 + 主键列非空且唯一(不可以有null) + 表中只有一个。主键自动创建主键索引。
• 组合索引：多列值组成一个索引专用于组合搜索，效率大于索引合并。
• 全文索引：对文本的内容进行分词，进行搜索。支持全文查找，允许重复值和空值。
• 空间索引：对空间类型的列建立的索引。

ps：

• 索引合并：使用多个单列索引组合搜索。
• 覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，也就是说查询列要被所建的索引覆盖。

创建索引的原则：

1. 最左前缀匹配原则：mysql会一直向右匹配，直到遇见范围查询 (>、<、between、like) 就停止匹配；如果第一个条件没有用索引，那么后面的也不会用到索引。例如：在此where语句中如果建立abcd顺序的索引 (组合索引)，d是用不到索引的；如果建立abdc顺序的索引，则都可以用到，abd可以随意调换位置。

   where a = 1 and b = 2 and c > 3 and d = 4

2.  = 和 in 可以乱序，mysql的查询优化器会优化成索引识别的形式。
3. 尽量选取区分度高 (字段不重复的比例) 的列作为索引，区分度越大扫描的记录数越少。
4. 索引列不能参与计算：不计算列而是计算值。例如：form_unixtime(create_time) = '2020-10-20' 应该写成 create_time = unix_timestamp('2020-10-20')。
5. 尽量的修改索引而不是新建：例如：表中已有 a 的索引，现在要加 (a,b) 的索引，只需修改原来的索引即可。
6. 查询时减少使用 * 返回全部列，不要返回不需要的列。
7. 索引应该尽量小，在字节数小的列上建立索引。
8. WHERE 子句中有多个条件表达式时，包含索引列的表达式应置于其他表达式之前。
9. 避免在 ORDER BY 子句中使用表达式。

索引失效：

1. 模糊查询%匹配放在最前面。
2. sql语句中where条件后面有or。

查询优化神器——explain命令

可以通过其来查看将要执行的sql是否走索引。

索引在 MySQL 中的分类：

• B+ 树索引
• Hash索引
• 全文索引

MySQL中的索引类型：

• 普通索引：经常使用。
• 唯一索引：当创建 unique 唯一约束的时候使用，数据库自动创建。
• 主键索引：当创建 primary key 主键的时候，数据库自动创建。
• 组合索引：同时创建在多列上。
• 全文索引：全库。
• 空间索引：全磁盘空间。

MySQL中适合建立索引的条件：

• 频繁搜索的列
• 经常用作查询选择的列
• 经常排序、分组的列
• 经常用作连接的列（主键、外键）

MySQL中不适合建立索引的条件：

• 仅包含几个不同值得列（性别）
• 表中仅包含几行数据

InnoDB 存储引擎。

InnoDB 存储引擎支持：B+树索引、Hash索引、全文索引。

B+ Tree 索引和 Hash 索引的区别

• 哈希索引适合等值查询，但是无法进行范围查询 
• 哈希索引没办法利用索引完成排序 
• 哈希索引不支持多列联合索引的最左匹配规则 
• 如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

聚簇索引（主键索引）与 非聚簇索引（非主键索引、辅助索引、二级索引）。

• 在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。
• 索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。
• 一个表最多只能有一个聚簇索引。
• 主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次查询（回表）。
• 非主键索引也可以通过覆盖索引只查询一次。
• 查看非主键索引的查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。