MySQL索引

逸Y 仙X

已于 2024-12-15 09:40:17 修改

阅读量1.1k

点赞数 11

文章标签： mysql 数据库数据库调优索引

于 2024-12-15 09:39:01 首次发布

本文链接：https://blog.csdn.net/hdk5855/article/details/144471567

版权

创建索引的sql语句：

使用CREATE INDEX创建索引：

使用ALTER TABLE ... ADD INDEX创建索引：

使用ALTER TABLE ... ADD KEY创建索引：

创建索引的sql语句：

使用`CREATE INDEX`创建索引：

     CREATE INDEX index_name ON table_name (column_list);
     CREATE UNIQUE INDEX index_name ON table_name (column_list);

使用`ALTER TABLE ... ADD INDEX`创建索引：

     ALTER TABLE table_name ADD INDEX index_name (column_list);
     ALTER TABLE table_name ADD UNIQUE index_name (column_list);
     ALTER TABLE table_name ADD PRIMARY KEY (column_list);

使用`ALTER TABLE ... ADD KEY`创建索引：

     ALTER TABLE table_name ADD KEY index_name (column_list);
     ALTER TABLE table_name ADD UNIQUE KEY index_name (column_list);
     ALTER TABLE table_name ADD PRIMARY KEY (column_list);

索引的分类：

从数据结构的角度分为：

哈希索引，B+树索引，R-tree索引，FULLTEXT(全文索引)

这里面我们就是需要了解一个问题：问什么我们InnoDB采用B+tree结构呢
我们在查询数据库是，是直接和内存中的磁盘中，所以我们减少IO操作的次数和数量就是提高效率的有效方法。

对于大的数据量，我们采取分治的思想，这是正确的处理方式，所以我们采取分块读取。

对于InnoDB存储引擎，我们每次加载的最小单位是页，每页的大小是4KB,所以我们读取的大小是页的整数倍，所以我们每次加载16K的数据

在加载时如果数据和索引信息实在两个文件中，这就是两次IO操作，所以在优化中InnoDB将存储的真实数据和索引信息放在一个文件中。

这是我们为了减少次数在文件上的努力，对于数据存储的结构，使用B+树就会减少我们的IO操作。
- 在这里我们在添加一个点：为什么我们的树的高度增加后，IO的次数会增加
```
 //我们的默认值是树的一层就是一次IO
因为我们在存储数据时，是分块存储的，所以如果我们每一块存储的不是一个节点的数据的话，那这个里面放置的数据可能是一棵树，
但是如果是一棵树的的话，树和树直接的关联很麻烦，为了便于管理，MySQL就将一块区域只是存储很小的部分，并且我们可以尽可能地填满我们的这个小空间。这其实也是引出了B-树和B+树的概念。
```
这是二叉树，并且每个块中只是一个节点

但是如果我们每个节点存放的数据变多呢:整棵树就开始变得高度减少，IO次数减少。

首先我们开始讨论Hash索引，我们的InnoDB存储引擎是不支持这种hash索引的（但是他是支持自适应hash的），因为hash的键值对的方式在直接查询时速度还是可以的，但是在范围查询时，就是在范围内进行遍历操作，时间复杂度是O(N).

这是我们就引出了二叉树，BST,AVL,和红黑树：
1. BST：（二叉搜索树）：左子树中的所有节点值小于根节点的值，右子树中的所有节点值大于根节点的值。如此的话，在极限情况下，他就是一条链表。
2. AVL树：AVL树是一种自平衡的二叉搜索树，AVL树的主要特点是每个节点的左右子树的高度差不超过1，这意味着AVL树是高度平衡的。这种高度平衡的特性确保了AVL树在查找、插入和删除操作中的时间复杂度为O(log n)，其中n是树中节点的数量。但是他是二叉树，三层最多只是放置7个节点
3. 红黑树也是二叉树，所以他也是三层最多只是放置7个节点
但是使用b-树是，每个节点可以存放多个数据。但是B-树的数据也是放置在非叶子节点上，故而出现了B+树：

b+树的叶子节点存放数据，，非叶子节点只是放置Key和指针，进行检索式，从根节点开始查找，也可以在叶子节点中从前向后或者从后向前检索。

注意：B+树中，3，4层的B+树就足以满足我们的要求，可以支撑千万级别的数据。

从物理存储结构上分为：

聚簇索引（有表的主键生成的B+树，这个B+数的叶子节点存放的是所有的数据）非聚簇索引。

注意：非聚簇索引的叶子节点存储的是聚簇索引的值（多数情况下就是主键）

从逻辑结构上分为：

主键索引，唯一索引，普通索引，组合索引

回表：

什么是会回表：

数组在使用非聚簇索引查找数据时，因为非聚簇索引在叶子节点处存放的是聚簇索引的值，他会获取叶子节点上面聚簇索引的值，再根据获取的值在聚簇索引的上获取到查询的值。这就是回表。

如何解决回表问题：

索引覆盖可以解决回表的问题。我们在查询的列数量不多时，通过创建联合索引，达到索引覆盖解决回表问题。

什么是联合索引：

构建索引的完全可以是多个字段。所以，将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引，比如index(a,b)就是将a,b两个列组合起来构成一个索引。

但是这是叶子节点上面存储的数据就不再仅仅只是聚簇索引的值，而是：

a这个列，b这个列，和聚簇索引的值（多数情况下就是主键）

什么是索引覆盖：

因为在联合索引的叶子节点处存放的是联合索引的列和聚簇索引的值，所以在查询数据时，如果可以直接在组合索引的叶子节点中获取数据，则无需去聚簇索引查找。

最左匹配原则：

在组合查询时，多个列进行匹配时要严格遵循从左到右的顺序，否则就会失败。

-- 假设有一张表，表中有以下字段：id，name，age，gender，address
-- id是主键，(name,age)是组合索引

1、Select * from table where name = 'zhangsan' and age = 10;
2、Select * from table where name = 'zhangsan';
3、Select * from table where age = 10;
4、Select * from table where age = 10 and name = 'zhangsan';

上述的四条语句中，1,2,4都可以用到组合索引，3用不到，但是很多同学会有疑问，为什么第四条会用到，明明不符合最左匹配原则的顺序，这里需要注意，如果把第四条SQL语句的条件换一下顺序，会影响最终的查询结果吗？答案是不会的，所以mysql中的优化器会进行优化，调整条件的顺序

自适应Hash索引：

我们再InnoDB引擎之中是没有hash索引的，但是他有一个自适应的hash索引。

自适应的hash索引就是如果我们对于一个数据多次的进行操作，达到一定的次数后，MySQL会将它标记为热数据，对于B+树的索引，我们的IO次数多数情况下需要达到3~4次，但是，如果我们只是将热点数据放置在Hash结构中，通过键值对的方式，可以直接获取，增大了我们的效率。

自适应hash索引的哈希函数采用除法散列的方式，哈希冲突时，采用链表的方式，注意，对于自适应哈希索引仅是数据库自身创建并使用的，我们并不能对其进行干预。

如何创建高性能的索引：

索引的作用：

在这里我们可以将索引作为我们的字典的目录一样，可以提高我们的查询效率。

一个索引就是一个B+树，所以它可以帮助我们快速定位和扫描我们需要的数据记录。

注意：一个select查询语句在执行过程中一般最多能使用一个二级索引，即使在where条件中用了多个二级索引。

索引的列的数据类型尽量小：

首先我们从减少IO次数上考虑：对于聚簇索引，叶子节点上存放的是索引列的值和全部是数据，对于非聚簇索引，叶子节点存放的是聚簇索引的值（就是索引列），索引如果索引列小的话，可以节省空间存放数据，减少io次数

另外：数据类型越小，查询时比较的速率越快。

索引的选择性

索引列要选为选择性/离散型高的列，就是重复出现的越少越好。这可以让MySQL过滤掉更多的列。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

很差的索引选择性就是列中的数据重复度很高，比如性别字段，不考虑政治正确的情况下，只有两者可能，男或女。那么我们在查询时，即使使用这个索引，从概率的角度来说，依然可能查出一半的数据出来。

假设现在有一个表数据：

-- 计算离散性的SQL
select COUNT(DISTINCT sex)/count(*) from t_uset

就是这简单的表但是如果他有10万条数据，选用sex过滤，依然可能查出一半的数据出来。并不高效

前缀索引：

对于text，blob，很长的varchar字段，mysql不支持索引他们的全部长度，需建立前缀索引。

缺点：前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

-- 记录在如果截取多少创建索引是最好的
SELECT
COUNT(DISTINCT LEFT(department_name,10))/COUNT(*) as num
from departments

只为我们搜索、排序或分组的列创建索引

也就是说，只为出现在WHERE 子句中的列、连接子句中的连接列创建索引，而出现在查询列表中的列一般就没必要建立索引了，除非是需要使用覆盖索引；又或者为出现在ORDER BY或GROUP BY子句中的列创建索引

多列索引：

多列索引的列顺序至关重要。对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。

然而，性能不只是依赖于索引列的选择性，也和查询条件的有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序，比如排序和分组，让这种情况下索引的选择性最高。

同时，在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。