mysql数据库优化查询速度总结

数据库优化查询速度总结

常用的sql查询方法

1、查看数据表占用的空间大小

• TABLE_NAME：表名

• TABLE_ROWS：总行数

• DATA_LENGTH：表数据大小

• INDEX_LENGTH：索引大小

SELECT
    TABLE_NAME,
    TABLE_ROWS,
	DATA_LENGTH,
	INDEX_LENGTH,
	(DATA_LENGTH + INDEX_LENGTH) AS total_sizeFROM
    information_schema.tablesWHERE
    TABLE_SCHEMA = '库名' #数据库名AND TABLE_NAME = '表名';#数据表名

2、更新索引的统计信息

MySQL 会对指定表的索引进行分析，计算索引的分布情况，以便更好地估计查询的成本和选择合适的查询策略

ANALYZE TABLE 表名;

3、EXPLAIN + SQL 分析查询执行计划

• id：每个 SELECT 语句的 ID，用于标识查询块。

• select_type：查询的类型，例如 SIMPLE（简单查询）、PRIMARY（主查询）、UNION 等。

• table：涉及到的表名。

• type：访问类型，例如 ALL（全表扫描）、index（索引扫描）、range（范围扫描）、ref（使用索引查找单行）等。

• possible_keys：可能使用的索引。

• key：实际使用的索引。

• key_len：使用的索引长度。

• ref：使用的键或常量。

• rows：MySQL 预计需要检查的行数。

• Extra：额外信息，例如 Using where（使用 WHERE 子句过滤结果）、Using index（使用覆盖索引）等

数据库优化

在对MySQL查询进行优化时需要考虑多个方面，包括索引的选择、查询条件的优化以及查询计划的分析

1. 索引设计

1. 当查询涉及多个条件时，考虑创建包含所有相关字段的复合索引

2. 复合索引的字段顺序应根据查询条件的重要性来确定，优先级高的字段放在前面

3. 对于经常作为查询条件出现的单个字段，考虑创建单字段索引。

4. 单字段索引适用于数据量较小的情况，可以快速过滤数据。

2. 查询条件优化

1. 使用合适的条件 尽量使用等值条件，避免使用 LIKE 开头的模糊匹配 会导致全表扫描

2. 避免在索引字段上使用函数

3. 对于 NULL 值的判断，使用 IS NULL 或 IS NOT NULL，而不是 = NULL 或 <> NULL。

3. 查询计划分析

1. 使用 EXPLAIN 命令来查看查询计划，了解 MySQL 如何执行查询

2. 观察 EXPLAIN 输出中的 key 列，确认是否使用了预期的索引

3. 关注 type、possible_keys、key、rows 和 Extra 列，这些信息可以帮助识别查询中的瓶颈

4. 如果 Extra 列中有 Using temporary 或 Using filesort，说明查询可能需要优化

1. 空间占用

1. 测试结果数据表一条复合索引大约占用3M左右

2. 一般来说不需要考虑索引的空间占用 、除非是创建了特别多不同索引的情况下（不过对空间的占用可以忽略不及，牺牲少量空间换时间何乐而不为）

建立索引

MySQL 优化器会根据查询条件、表的统计数据和索引的统计信息来决定使用哪个索引

当数据表中数据量较多/较少时mysql优化器会优先选择不同得索引来执行查询

数据量较多时：mysql优化器倾向于选择能够有效过滤数据的索引来执行查询、复合索引通常更适合这种场景，因为它们可以同时满足多个查询条件，从而减少需要扫描的数据量

数据量较少时：当数据量较少时，即使是全表扫描也可能比使用索引更快、mysql优化器会根据表的统计数据来决定是使用索引还是全表扫描

1、如果某些字段在查询中总是出现并且这些字段可以有效地过滤数据 则建议将固定字段建立复合索引 提交查询效率

例如：SELECT * FROM 表名 WHERE trade_mode = '9610' AND ucode = 'cvy17'; 每次查询时该值都存在，就可以给这两个字段建立复合索引提高查询效率

ALTER TABLE 表名 ADD INDEX `idx1`(`ucode`, `trade_mode`);

2、对于那些不是每次查询都会出现的条件，可以考虑创建单独的索引 例如：declare_status 它不是每次都出现在查询中，可以创建单字段索引

ALTER TABLE 表名 ADD INDEX `idx1`(`ucode`);

3、如果某些字段偶尔出现在查询中，并且数据量较大时使用这些字段可以提高查询效率，可以将这些字段添加到复合索引中 例如：

ALTER TABLE 表名 ADD INDEX `idx2`(`ucode`, `trade_mode`,`declare_statu`);

此时再次查询就会命中该索引 效率更高

注意

• 注意添加索引时的先后顺序，否则会导致无法命中最理想的索引

• 例如查询sql为：EXPLAIN SELECT * FROM jk_orderWHERE ucode='2fa5t'AND status=60 AND deal_status=30 AND push_type=30 ORDER BY create_timeDESC

• 理论上idx3是最佳的索引 但实际中因为优先级较低 会导致命中idx1或idx2索引

• 这时如果调整顺序 再次查询则正常命中

• 实际添加复合索引时 要综合考虑下再决定

• 查询时字段值的类型要和字段类型保持一致，否则也可能会导致索引失效

• 复合索引要合理调整字段优先级，否则会导致索引失效

总结

1、数据量较多时，复合索引更有效
2、数据量较少时，全表扫描可能更快
3、对于总是出现在查询中的字段，创建复合索引
4、对于偶尔出现在查询中的字段，可以考虑创建单字段索引