MYSQL 索引

作者: code_debug | 来源:发表于2020-08-20 11:35 被阅读0次

MySQL索引及查询优化书目录
高性能的索引策略
MySQL索引的使用
Mysql索引与锁
索引（二）
MySQL 索引分类
MySQL--索引
mysql索引
5.2MySQL创建高性能索引考察点
MySql 数据查询优化

什么是索引

存储引擎用于快速找到记录的一种数据结构
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
MySQL使用索引来快速查找具有特定列值的行。没有索引，MySQL必须扫描整个表以找到相关的行。表越大，搜索速度越慢。

聚簇索引：

当您创建具有主键或唯一键[的表]时，MySQL将自动创建一个名为的特殊索引PRIMARY。

非聚簇索引：

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

创建索引

通常，在创建时为表创建索引。例如，以下语句创建一个新表，该表的索引由两列c2和c3组成。

CREATE TABLE t(
   c1 INT PRIMARY KEY,
   c2 INT NOT NULL,
   c3 INT NOT NULL,
   c4 VARCHAR(10),
   INDEX (c2,c3) 
);

要为一列或一组列添加索引，请使用以下CREATE INDEX语句：

CREATE INDEX index_name ON table_name (column_list)

要为一列或一列列表创建索引，请指定索引名称，该索引所属的表以及列列表。

例如，要为列c4添加新索引，请使用以下语句：

CREATE INDEX idx_c4 ON t(c4);

默认情况下，如果不指定索引类型，MySQL将创建B树索引。下面显示了基于表的存储引擎的允许索引类型：

储存引擎	允许的索引类型
InnoDB	BTREE
MyISAM	BTREE
MEMORY/HEAP	哈希，BTREE

请注意，CREATE INDEX上面的CREATE INDEX语句是MySQL引入的语句的简化版本。我们将在后续教程中介绍更多选项。

MySQL `CREATE INDEX`示例

以下语句查找title='【山东】-回收校车终端'：

SELECT * from zt_bug WHERE title='【山东】-回收校车终端';

要查看MySQL内部执行此查询的方式，请在EXPLAIN 语句的开头添加该子句，SELECT如下所示：

EXPLAIN SELECT id,title from zt_bug WHERE title='【山东】-回收校车终端';

扫描行数.png

如您所见，MySQL必须扫描整个表，该表由58173行组成，以查找 title='【山东】-回收校车终端'。

现在，让我们project使用以下CREATE INDEX语句为列创建索引：

CREATE INDEX project ON zt_bug(project);

并再次执行以上语句：

EXPLAIN SELECT id,title from zt_bug WHERE project=0 and title='【山东】-回收校车终端';

输出为：

加索引后扫描行数.png

如您所见，MySQL只需从project键列中指示的索引中查找4633行，而无需扫描整个表。

要显示表的索引，请使用以下SHOW INDEXES语句，例如：

SHOW INDEXES FROM zt_bug;

这是输出：

添加的索引.png

利用聚簇索引查找数据

查找过程.png
其中 id 为主键，具体的查找过程如下：
- ①一般根节点都是常驻内存的，也就是说页 1 已经在内存中了，此时不需要到磁盘中读取数据，直接从内存中读取即可。
  从内存中读取到页 1，要查找这个 id>=18 and id <40 或者范围值，我们首先需要找到 id=18 的键值。
  从页 1 中我们可以找到键值 18，此时我们需要根据指针 p2，定位到页 3。
- ②要从页 3 中查找数据，我们就需要拿着 p2 指针去磁盘中进行读取页 3。
  从磁盘中读取页 3 后将页 3 放入内存中，然后进行查找，我们可以找到键值 18，然后再拿到页 3 中的指针 p1，定位到页 8。
- ③同样的页 8 页不在内存中，我们需要再去磁盘中将页 8 读取到内存中。
  将页 8 读取到内存中后。因为页中的数据是链表进行连接的，而且键值是按照顺序存放的，此时可以根据二分查找法定位到键值 18。
  此时因为已经到数据页了，此时我们已经找到一条满足条件的数据了，就是键值 18 对应的数据。
  因为是范围查找，而且此时所有的数据又都存在叶子节点，并且是有序排列的，那么我们就可以对页 8 中的键值依次进行遍历查找并匹配满足条件的数据。
  我们可以一直找到键值为 22 的数据，然后页 8 中就没有数据了，此时我们需要拿着页 8 中的 p 指针去读取页 9 中的数据。
- ④因为页 9 不在内存中，就又会加载页 9 到内存中，并通过和页 8 中一样的方式进行数据的查找，直到将页 12 加载到内存中，发现 41 大于 40，此时不满足条件。那么查找到此终止。
每次从磁盘读取到内存，都会有一次I/O开销，是需要关注的点

索引策略

1.独立的列：索引列不能是表达式的一部分，也不能是函数的参数

a.

函数的一部分.png b.

函数参数.png

2.前缀索引：字符过长的时候，可以索引开始的部分字符，如某表有个id类型为64位uuid，则可以增加索引为：
Alter table tablename add index indexname（id（10））；
此时需注意索引选择性，即不重复的索引值和数据表总数的比值
3.联合索引
4.合适的索引顺序：将选择性最高的列放在前面，联合索引注意最左原则