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

常用的sql查询方法

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

SELECT 
    TABLE_NAME,#表名
    TABLE_ROWS,#总行数
	DATA_LENGTH,#表数据大小
	INDEX_LENGTH,#索引大小
	(DATA_LENGTH + INDEX_LENGTH) AS total_size
FROM 
    information_schema.tables
WHERE 
    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. 索引设计
   当查询涉及多个条件时，考虑创建包含所有相关字段的复合索引。
   复合索引的字段顺序应根据查询条件的重要性来确定，优先级高的字段放在前面。
   对于经常作为查询条件出现的单个字段，考虑创建单字段索引。

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

2. 查询条件优化
   使用合适的条件 尽量使用等值条件，避免使用 LIKE 开头的模糊匹配 会导致全表扫描。
   避免在索引字段上使用函数
   对于 NULL 值的判断，使用 IS NULL 或 IS NOT NULL，而不是 = NULL 或 <> NULL。

3. 查询计划分析
   使用 EXPLAIN 命令来查看查询计划，了解 MySQL 如何执行查询。
   观察 EXPLAIN 输出中的 key 列，确认是否使用了预期的索引。
   关注 type、possible_keys、key、rows 和 Extra 列，这些信息可以帮助识别查询中的瓶颈。
   如果 Extra 列中有 Using temporary 或 Using filesort，说明查询可能需要优化
   

4. 空间占用
   测试结果数据表一条复合索引大约占用3M左右
   一般来说不需要考虑索引的空间占用 、除非是创建了特别多不同索引的情况下（不过对空间的占用可以忽略不及，牺牲少量空间换时间何乐而不为）

建立索引

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、对于偶尔出现在查询中的字段，可以考虑创建单字段索引