mysql索引策略_MySQL高级篇——索引的创建与设计原则

CREATE INDEX idx_age_classid_name ON student(age,classId,name);
SHOW INDEX FROM student;

2. .0索引新特性2.1支持降序索引

创建降序外键索引：

CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));

查看MySQL 8.0版本中数据表ts1的结构，我们可以看到它是按降序排列的。结果如下：

查看MySQL 5.7版本中数据表ts1的结构。该索引仍按默认升序排列。结果如下：

2.2 隐藏索引

在 MySQL 5.7 及更早版本中，只能显式删除索引。此时如果删除索引后出现错误，则只能通过显式创建索引的方式将被删除的索引创建回来。如果数据表的数据量很大，或者数据表本身就比较大，这个操作会消耗太多的系统资源，操作成本会很高。

将要删除的索引设置为隐藏索引，等MySQL确认删除索引后不会出现错误后，再彻底删除该索引。

从 MySQL 8.x 开始支持隐藏索引 ()。你只需要将要删除的索引设置为隐藏索引，这样查询优化器就不会再使用这个索引（即使使用了force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不会受到任何影响，然后就可以彻底删除索引了。这种先将索引设置为隐藏索引，然后删除索引的方法就是软删除。

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

3、索引设计原则 3.1 适合索引创建 3.1.1 简洁版具有独特特征的字段，适合创建索引时经常用作where条件的字段，适合创建经常分组或排序查询的索引，适合创建索引增删改查语句 查询条件字段适合创建索引字段。创建索引多表连接时，连接的表数量不要超过3个。其中字段和连接字段适合创建索引字段。数据范围越小，越适合创建长索引的字段。适合为区分度高的字段创建前缀索引。它适合索引联合索引。将经常查询的列放在左侧。当需要创建多个字段建立索引时，联合索引比单值索引要好。建议单表索引数量不要超过6个。3.1.2详细版

1、具有独特特征的字段，适合创建索引

对于具有独特业务特征（如唯一约束、主键约束）的字段，甚至是复合字段，都必须构建唯一索引。 （来源：）

注意：不要认为唯一索引影响速度。这个速度损失可以忽略不计，但是搜索速度的提升是显而易见的。

2. 经常用作where条件的字段适合创建索引

如果某个字段在语句的WHERE条件中经常使用，那么就需要为该字段创建索引。尤其是当数据量较大时，创建公共索引可以大大提高数据查询的效率。

3、查询时经常分组或排序的字段适合创建索引

索引本身已经排序了，B+叶子节点共同构成双向链表，非常适合范围查询。非常适合索引。

索引允许按特定顺序存储或检索数据。因此，当我们使用GROUP BY对查询数据进行分组，或者使用ORDER BY对数据进行排序时，我们需要对分组或排序的字段建立索引。

如果需要排序的列有多个，可以在这些列上创建联合索引。

4.添加和修改语句的查询条件字段，适合创建索引

, WHERE 条件列。根据一定条件查询数据，然后进行OR运算后，如果在WHERE字段上创建索引，则可以大大提高效率。原理是我们需要先根据WHERE条件列检索到这条记录，然后再更新或删除它。如果更新时更新的字段是非索引字段，效率提升会更明显。这是因为更新非索引字段不需要维护索引。

5、字段，适合创建索引

有时我们需要对某个字段进行去重并使用它。对该字段建立索引也会提高查询效率。因为索引按照一定的顺序对数据进行排序，排序后去重会快很多。

SELECT DISTINCT 字段列表 FROM 表名;

6、连接多表时，连接表的数量不要超过3个。where字段和连接字段适合创建索引。

首先，连接表的数量尽量不要超过3个，因为每增加一个表就相当于增加一个嵌套循环，数量级增长会非常快，严重影响查询的效率。

其次，在WHERE条件上创建索引，因为WHERE是数据条件的过滤器。如果数据量非常大，没有WHERE条件的过滤是非常可怕的。

最后，对用于连接的字段创建索引，并且该字段在多个表中必须是同一类型。例如，table 和 table 的类型都是 int(11)，但不能一个是 int 类型，另一个是 int 类型。

7、数据范围越小，越适合创建索引。

这里我们所说的类型大小是指类型所表示的数据范围的大小。

当我们定义表结构时，必须明确指定列类型。以整数类型为例，有、、INT等，它们占用的存储空间依次增大，可以表示的整数范围也依次增大。如果我们要对一个整数列进行索引，在所表示的整数的范围允许的情况下，尽量为索引列使用更小的类型。例如，如果我们可以使用 INT，就不要使用它。如果可以的话，不要使用 INT。这是因为：

数据类型越小，查询时的比较操作越快

数据类型越小，索引占用的存储空间就越少，一个数据页中可以放置更多的记录，从而减少磁盘I/0带来的性能损失，这意味着更多的数据页可以缓存在内存中加快读写效率。

这个建议更适用于表的主键，因为不仅主键值会存储在聚集索引中，而且一条记录的主键值也会存储在所有其他二级索引节点中。如果主键使用更小的数据类型，这意味着节省更多的存储空间和更高效的I/0。

8.非常长的字段适合创建前缀索引

假设我们的字符串很长，存储一个字符串会占用大量的存储空间。当我们需要对这个字符串列建立索引时，就意味着对应的B+树存在两个问题：

B+树索引中的记录需要存储该列的完整字符串，比较耗时。而且字符串越长，在索引中占用的存储空间就越大；

如果B+树索引中索引列中存储的字符串很长，则执行字符串比较会花费更多时间。我们可以通过截取字符串中区分度高的前缀子串来创建索引。这称为前缀索引。这样，在查找记录时虽然无法准确定位记录的位置，但是可以定位到对应前缀的位置，然后根据记录的主键值将完整的字符串值返回到表中。具有相同前缀的记录。它不仅节省空间，还减少了字符串比较时间，一般可以解决排序问题。

计算歧视程度：

count(distinct left(列名, 索引长度))/count(*)

left() 函数用于获取字符串前缀。

案件：

创建商户表。由于地址字段比较长，因此在地址字段上创建前缀索引。

create table shop(address varchar(120) not null);
alter table shop add index(address(12));

问题是，拦截多少？如果拦截过多，就达不到节省索引存储空间的目的；如果拦截太少，重复内容就会过多，字段的哈希度（选择性）就会降低。

计算不同长度的区别，通过区别来判断

所有数据中完整字段的选择性：

select count(distinct address) / count(*) from shop;

通过不同长度计算并与整个表的选择性进行比较：

count(distinct left(列名, 索引长度))/count(*)

索引列前缀对排序的影响：《Java开发手册》

【强制】为字段创建索引时，必须指定索引长度。没有必要对整个字段建立索引。索引长度根据实际的文本区分来确定。

注：索引长度和区分度是一对矛盾。一般情况下，对于字符串类型数据，长度为20的索引区分度会达到90%以上。可以使用count(left(列名,索引长度))/count(*)来判断区分程度。

9. 字段区分度高，适合用作索引

10.联合索引，将经常查询的列放在左边

这还允许创建更少的索引。同时，由于“最左前缀原则”，可以增加联合索引的使用量。

11、当需要对多个字段建立索引时，联合索引比单值索引要好。

12.建议单表索引数量不要超过6个

在实际工作中，我们也需要注意平衡。索引越多越好。我们需要限制每个表上的索引数量。建议单表索引数量不要超过6个。原因：

- 每个索引都需要磁盘空间。索引越多，所需的磁盘空间就越大。

- 索引会影响、、等语句的性能，因为当表中的数据发生变化时，索引也会进行调整和更新，这会造成负担。

- 当优化器选择如何优化查询时，它将根据统一信息评估每个可用索引，以生成最佳执行计划。如果同时有很多索引可用于查询，则会增加MySQL优化器生成执行计划的时间，降低查询性能。

3.2 不适合创建索引的情况

1、where中不使用的字段不要设置索引

2.对于数据量较小的表，不要设置索引

当数据表的数据行数比较少，比如小于1000行时，就不需要创建索引。

3、不要在重复数据较多的列上设置索引。

当数据重复较大时，比如高于10%，则不需要对该字段使用索引。

例如，学生表有100万条数据，其中只有10个男生，其余都是女生。不要在性别字段上设置索引。

4、对于更新频繁的表，不要创建过多的索引

第一个含义：经常更新的字段不一定需要建立索引。因为更新数据时，索引也需要更新。如果索引过多，也会在更新索引时造成负担，从而影响效率。

第二层含义：避免在频繁更新的表上创建过多的索引，索引中的列尽可能少。此时虽然查询速度提高了，但是更新表的速度会降低。

5.不建议使用无序值作为索引

例如身份证、UUID（索引比较时需要转为ASCII，插入时可能会造成分页）、MD5、HASH、无序长字符串等。

6.删除很少使用的索引

当表中的数据被大量更新，或者数据的使用方式发生改变后，原有的一些索引可能不再需要。数据库管理员应该定期识别这些索引并删除它们，以减少索引对更新操作的影响。

7. 不要定义冗余或重复的索引

冗余索引示例：个人信息表，联合索引最左边字段不需要创建索引

CREATE TABLE person_info(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
    KEY idx_name (name(10))
);

我们知道，通过联合索引可以快速查找到name列，那么专门为name列创建索引就是一种冗余索引。维护这个索引只会增加维护成本，对搜索没有好处。

重复索引示例：

还有一种情况，我们可能会对某一列进行重复索引，例如：

CREATE TABLE repeat_index_demo (
    col1 INT PRIMARY KEY,
    col2 INT,
    UNIQUE uk_idx_c1 (col1),
    INDEX idx_c1 (col1)
);

我们看到col1不仅是主键，还定义为唯一索引，并为其定义了普通索引。但是主键本身会生成聚集索引，所以定义的唯一索引和普通索引是重复的。这种情况要避免。