CXK带你精通MySQL索引设计

岭师吴彦祖

于 2024-08-30 18:25:24 发布

阅读量462

点赞数 14

文章标签：数据库 mysql 后端性能优化

本文链接：https://blog.csdn.net/weixin_51474578/article/details/141719696

版权

3、经常group by和order by的列

4、update、delete的where条件列

5、distinct字段需要创建索引

二，提升索引性能小技巧

1、多表join连接操作时，创建索引注意事项

1.索引类型

1、主键索引

2、普通索引

3、唯一性索引

4、单列索引

5、组合索引

6、全文索引

MySQL中的全文索引（Full-text Index）是一种特殊类型的索引，专门用于支持全文搜索功能。这种索引使得在大量的文本数据中进行关键词搜索变得更加高效。

2.索引命名规范

1、主键索引

pk_xxx

2、唯一性索引

uk_xxx

3、组合索引

idx_xxx_xxx

4、全文索引

ftx_xxx

3.创建索引的方式

1、创建表的时候指定

CREATE DATABASE dbtest2;
 
USE dbtest2;
 
CREATE TABLE dept (
		dept_id INT PRIMARY KEY AUTO_INCREMENT,
		dept_name VARCHAR(20)
);
 
SHOW INDEX FROM dept;

2、ALTER TABLE……方式创建

DROP TABLE IF EXISTS book5;
CREATE TABLE book5 (
		book_id INT ,
		book_name VARCHAR(100),
		`authors` VARCHAR(100),
		info VARCHAR(100) ,
		`comment` VARCHAR(100),
		year_publication YEAR
);
 
ALTER TABLE book5 ADD INDEX idx_cmt(`comment`);
ALTER TABLE book5 ADD UNIQUE INDEX uk_idx_bname(book_name);
ALTER TABLE book5 ADD INDEX mul_bid_bname_info(book_id, book_name, info);
 
SHOW INDEX FROM book5;

3、CREATE INDEX……ON……方式

DROP TABLE IF EXISTS book6;
CREATE TABLE book6 (
		book_id INT ,
		book_name VARCHAR(100),
		`authors` VARCHAR(100),
		info VARCHAR(100) ,
		`comment` VARCHAR(100),
		year_publication YEAR
);
 
CREATE INDEX idx_cmt ON book6(`comment`);
CREATE UNIQUE INDEX uk_idx_bname ON book6(book_name);
CREATE INDEX mul_bid_bname_info ON book6(book_id, book_name, info);
 
SHOW INDEX FROM book6;

4.删除索引的方式

1、AFTER TABLE

ALTER TABLE table_name DROP INDEX idx_name

2、DROP INDEX

DROP INDEX idx_name ON table_name;

5.限制索引数目原则

在生活中凡是都有一个“度”，超过这个“度”就会引发一些糟糕的事件。在数据库索引设计时也是要遵循这个“度”的，索引的数目不是越多越好，我们需要有意识地限制每张表的索引数据，建议单张表索引数据不超过6个，原因如下：

1、索引就是提高搜索性能的文件集，也会带有磁盘容量的消耗，索引越多消耗的磁盘空间就越大。

2、索引会影响insert、delete、update等更新数据的使用，应该表数据变更时，需要实时维护索引。

3、MySQL在执行select语句时，会经过执行优化器，其会进行查询的优化，选择最优的索引进行查询，评估并生成出一个最好的执行计划，如果索引过多，会导致评估量增加，增加MySQL优化器生成执行计划的时间，进而减低查询效率。

6.不适合创建索引的场景

1、数据量小的表最好不要使用索引

在数据表中的数据行数比较少的情况下，比如不到 1000 行，数据库优化器是不会选择走索引的，所以创建索引不是必要。

2、有大量重复数据的列上不要创建索引

性别、店铺ID这些字段，数据区分度不高的列，创建索引对搜索性能提升微乎其微，但是缺降低了表更新数据的性能【需维护相关索引】，根据产出比，最好选择不要创建索引。

3、避免对经常更新的表创建过多的索引

第一层含义：频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。

第二层含义：避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。此时，虽然提高了查询速度，同时却会降低更新表的速度。

4、不建议使用无序的值作为索引

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

5、删除不在使用或者很少的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要，或者随着业务的调整，某个字段不再称为查询的条件，而为该字段创建了的索引。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

6、不要定义冗余或重复的索引

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)) 
);

我们知道，通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索【最左前缀原则】，再创建一个专门针对 name 列的索引就算是一个余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。

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

我们看到，col1既是主键、又给它定义为一个唯一索引l，还给它定义了一个普通索引l，可是主键本身就会生成聚簇索引l，所以定义的唯一索引和普通索引是重复的，这种情况要避免。

7.适合创建索引的场景

一，推荐创建索引表的字段

1、字段的数值有唯一性的限制

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源： Alibaba ）

说明：拥有唯一特性的字段创建唯一性索引，可以大大提高查询效率。不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

2、频繁作为where查询条件的字段

某个字段在 SELECT 语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。比如 student_info 数据表（含 100 万条数据），假设我们想要查询 student_id=123110 的用户信息。

下面的sql代码首先是查看student_info表中都有哪些索引？然后在不使用索引的情况下查询效率如何，对where查询条件的字段student_id添加索引之后查询效率又如何？最后是执行耗时。

SHOW INDEX FROM student_info;
 
SELECT course_id, class_id, NAME, create_time, student_id 
FROM student_info
WHERE student_id = 123110; #240ms
 
ALTER TABLE student_info ADD INDEX idx_sid(student_id);
 
SELECT course_id, class_id, NAME, create_time, student_id 
FROM student_info
WHERE student_id = 123110; #17ms

3、经常group by和order by的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引。

如果SQL同时进行GROUP和ORDER，可以用GROUP和ORDER相关字段创建组合索引。

这里先将GROUP BY中用到的student_id字段相关的索引删除，看下查询效率。之后再对这个字段加索引，再看查询效率。

ALTER TABLE student_info DROP INDEX idx_sid;
SHOW INDEX FROM student_info;
 
SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id LIMIT 100; #514ms
 
ALTER TABLE student_info ADD INDEX idx_sid(student_id);
 
SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id LIMIT 100; #17ms

4、update、delete的where条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。 如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更 新不需要对索引进行维护。

SHOW INDEX FROM student_info;
 
UPDATE student_info 
SET student_id = 10002 
WHERE NAME = '462eed7ac6e791292a79'; #466ms
 
ALTER TABLE student_info ADD INDEX idx_name(NAME);
 
UPDATE student_info 
SET student_id = 10003
WHERE NAME = '462eed7ac6e791292a79'; #16ms

5、distinct字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT ，那么对这个字段创建索引，也会提升查询效率。比如，我们想要查询课程表中不同的 student_id 都有哪些，如果我们没有对 student_id 创建索引，执行：

SELECT DISTINCT(student_id)
FROM student_info; #531ms
 
ALTER TABLE student_info ADD INDEX idx_sid(student_id);
SHOW INDEX FROM student_info;
 
SELECT DISTINCT(student_id)
FROM student_info; #369ms

二，提升索引性能小技巧

1、多表join连接操作时，创建索引注意事项

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

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

最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致 。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

SELECT s.course_id, NAME, s.student_id, c.course_name 
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE NAME = '462eed7ac6e791292a79'; #16ms
 
DROP INDEX idx_name ON student_info;
 
SELECT s.course_id, NAME, s.student_id, c.course_name 
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE NAME = '462eed7ac6e791292a79'; #189ms

2、尽量使用列数据量小的字段创建索引

我们这里所说的类型大小指的就是该类型表示的数据范围的大小。我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT、INT、BIGINT等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT。

这是因为：

1、数据类型越小，在查询时进行的比较操作越快；

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

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更好的搜索性能。

3、使用字符串前缀创建索引

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

1、B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大。

2、如果B+树索引中索引例存储的字符串很长，那在做字符串比较时会占用史多的时间。

我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能情确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。

CREATE TABLE users (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(100),  
    email VARCHAR(255)  
);  
-- 创建前缀索引，使用email字段的前10个字符  
CREATE INDEX idx_email_prefix ON users (email(10));

4、区分度高【散列性高】的列作为索引

列的是数指的是某一列中不重复数据的个数，比方说某个列包含值2，5，8，2，5，8，2，5，8，虽然有9条记录，但该列的基数却是3。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散：列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

可以使用公式 select count（distinct a）/count（*）from table 计算区分度，越接近1越好，一般超过33%就算是比较高效的索引了。

计算列区分度：

SELECT count(distinct page_type)/count(*) FROM `t_product_recommend`

通过计算，下面列只有25%左右的区分度，区分度较低，作为索引列性价比不高。

拓展：联合索引把区分度高（散列性高的列放在前面）

5、组合索引的列顺序原则

1、越经常用到的列，越放到最左边

2、数据区分度越高的列越放到最左边

3、数据量越小的列，越放到最左边

提升组合索引性能原则就是尽量提高左则列的过滤效果

感谢观看！不妨帮咯咯点个赞再走爱你~~~

岭师吴彦祖

关注

14
点赞
踩
17

收藏

觉得还不错? 一键收藏
0
评论
CXK带你精通MySQL索引设计

我们知道，通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索【最左前缀原则】，再创建一个专门针对 name 列的索引就算是一个余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。col2 INT,我们看到，col1既是主键、又给它定义为一个唯一索引l，还给它定义了一个普通索引l，可是主键本身就会生成聚簇索引l，所以定义的唯一索引和普通索引是重复的，这种情况要避免。
复制链接

扫一扫