mysql性能优化

最新推荐文章于 2025-06-15 11:59:29 发布

坎坎坷坷的影子

最新推荐文章于 2025-06-15 11:59:29 发布

阅读量141

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.youkuaiyun.com/yizhan_csdn/article/details/120686323

版权

MySQL 专栏收录该内容

3 篇文章

订阅专栏

本文探讨了如何在大规模数据插入后对索引进行优化，包括删除和添加索引，以及不同类型的索引（主键索引、唯一索引、普通索引和组合索引）的应用。还提到了存储优化策略，如禁用索引、唯一检查和外键检查，以及表结构设计的最佳实践。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

3、索引优化

对上述表进行批量插入100W数据。

drop procedure if exists tb_insert; 
CREATE PROCEDURE tb_insert()
BEGIN
DECLARE i INT;
SET i = 0;
START TRANSACTION;
WHILE i < 10 DO -- 10即插入10条数据
    INSERT INTO tb_table (`name`,`number`) VALUES (concat("张三",i),i);
    SET i = i+1;
END WHILE;
COMMIT;
END;
call tb_insert();

3.1、不使用索引查询

首先将number索引删除掉，然后执行下列SQL

select * from tb_table where number = 887876;

查询信息：
在这里插入图片描述

没有使用索引情况下，查询时间是：0.25s，使用explain分析下SQL语句
在这里插入图片描述

type类型为All，是进行全表扫描，没有使用到索引，查询的行数是992376。

3.2、使用索引查询

给number列加上索引，执行下列SQL语句，条件不要使用之前查询过的，因为mysql默认缓存是开启的，如果之前查询过，会走缓存，影响分析结果。

select * from tb_table where number = 900002;

查询信息：
在这里插入图片描述

使用explain分析SQL语句：

在这里插入图片描述

3.3、索引类型

主键索引 PRIMARY KEY

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。
```
PRIMARY KEY (`id`)
```
唯一索引 UNIQUE

唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。可以在创建表的时候指定，也可以修改表结构。
```
UNIQUE KEY `num` (`number`) USING BTREE
```
普通索引 INDEX

这是最基本的索引，它没有任何限制。可以在创建表的时候指定，也可以修改表结构
```
KEY `num` (`number`) USING BTREE
```
组合索引 INDEX

索引分单列索引和组合索引(联合索引)。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
```
KEY `num` (`number`,`name`) USING BTREE
```
注意，组合索引前面索引必须要先使用，后面的索引才能使用。
全文索引 FULLTEXT

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

3.4、索引的使用

虽然索引能够为查找带来速度上的提升，但是也会对性能有一些损失。

索引会增加写操作的成本
太多的索引会增加查询优化器的选择时间

当创建索引带来的好处多过于消耗的时候，才是最优的选择~

使用索引的场景

主键自动建立唯一索引；
经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
作为排序的列要建立索引；
查询中与其他表关联的字段，外键关系建立索引
高并发条件下倾向建立组合索引；
用于聚合函数的列可以建立索引，例如使用count(number)时，number列就要建立索引

不使用索引的场景

有大量重复的列不单独建立索引
表记录太少不要建立索引，因为没有太大作用。
不会作为查询的列不要建立索引

3.5、存储优化

3.5.1、禁用索引

对于使用索引的表，插入记录时，MySQL会对插入的记录建立索引。如果插入大量数据，建立索引会降低插入数据速度。为了解决这个问题，可以在批量插入数据之前禁用索引，数据插入完成后再开启索引。

禁用索引的语句： ALTER TABLE table_name DISABLE KEYS 开启索引语句： ALTER TABLE table_name ENABLE KEYS

MyISAM对于空表批量插入数据，则不需要进行操作，因为MyISAM引擎的表是在导入数据后才建立索引。

3.5.2、禁用唯一检查

唯一性校验会降低插入记录的速度，可以在插入记录之前禁用唯一性检查，插入数据完成后再开启。

禁用唯一性检查的语句：SET UNIQUE_CHECKS = 0; 开启唯一性检查的语句：SET UNIQUE_CHECKS = 1;

3.5.3、禁用外键检查

插入数据之前执行禁止对外键的检查，数据插入完成后再恢复，可以提供插入速度。

禁用：SET foreign_key_checks = 0; 开启：SET foreign_key_checks = 1;

3.5.4、表结构优化

尽量将表字段定义为NOT NULL约束，这时由于在MySQL中含有空值的列很难进行查询优化，NULL值会使索引以及索引的统计信息变得很复杂。
对于只包含特定类型的字段，可以使用enum、set 等数据类型。
数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用最小、最简单的数据类型。例如IP地址可以使用int类型。
尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED。但对整数类型指定宽度，比如INT(11)，没有任何用，因为指定的类型标识范围已经确定。
VARCHAR的长度只分配真正需要的空间
尽量使用TIMESTAMP而非DATETIME，但TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。
单表不要有太多字段，建议在20以内
合理的加入冗余字段可以提高查询速度。