本文共 2765 字，大约阅读时间需要 9 分钟。
随着在工作中接触数据量的增加，发现公司系统的数据库查询缺乏优化，导致一些大数据查询效率低下。本文结合网络资源，总结SQL优化方法，特别关注数据库索引的相关知识和实践经验。
索引是数据库管理系统（DBMS）中用于加快查询速度的数据结构。它通过存储表中某些列的值，生成逻辑指针，指向数据页，从而实现快速查找特定数据。
MySQL索引主要有两种结构：B+Tree索引和Hash索引。
Hash索引适用于内存存储引擎，通过哈希值快速定位数据。其优点是查询速度快，但不支持范围查询和排序操作。
B+Tree索引在B-Tree基础上优化，适合外存储。其非叶子节点不存储数据，提升每个节点存储能力，减少磁盘访问次数。
MySQL支持多种索引类型，包括主键索引、唯一索引、普通索引、全文索引、联合索引和空间索引。以下是常见索引的创建语法和特点：
| 索引类型 | 特点 | 创建语法示例 |
|---|---|---|
| 主键索引 | 唯一且不允许空值，通常与主键列绑定。 | ALTER TABLE table_name ADD PRIMARY KEY (col_name) |
| 唯一索引 | 唯一但允许空值，适用于需要唯一性但非主键列。 | ALTER TABLE table_name ADD UNIQUE (col_name) |
| 普通索引 | 最基本的索引，无特殊限制。 | ALTER TABLE table_name ADD INDEX index_name (col_name) |
| 全文索引 | 适用于全文检索，可用于MyISAM表。 | ALTER TABLE table_name ADD FULLTEXT (col_name) |
| 联合索引 | 组合多列索引，遵循“最左前缀”原则。 | ALTER TABLE table_name ADD INDEX index_name (col1, col2, col3) |
| 空间索引 | 专门用于空间数据类型字段。 | ALTER TABLE table_name ADD SPATIAL COLUMN (col_name) |
创建测试表
使用MyISAM存储引擎创建大数据表，并设置适当索引：CREATE TABLE `million_test` ( `id` bigint(20) AUTO_INCREMENT COMMENT '测试ID', `name` varchar(255) COLLATE utf8_general_ci COMMENT '测试名称', `age` int(11) COMMENT '年龄', `score` int(255) COMMENT '分数', `address` varchar(255) COMMENT '地址') ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
插入测试数据
使用存储过程插入100万条数据，确保数据量足够大以测试索引效率：DELIMITER $$CREATE PROCEDURE `insert_data`()BEGIN DECLARE index_num INT DEFAULT 0; WHILE index_num <= 1000000 DO INSERT INTO `million_test` (`id`, `name`, `age`, `score`, `address`) VALUES (NULL, CONCAT('测试数据', index_num), FLOOR(RAND() * 50), FLOOR(RAND() * 100), CONCAT('光谷金融港', FLOOR(RAND() * 50), '楼')); SET index_num = index_num + 1; END WHILE;END$$CALL `insert_data`;验证数据
检查表中数据量和索引情况：SHOW CREATE TABLE `million_test`;
EXPLAIN分析查询性能以查询SELECT name FROM million_test WHERE age > 10 AND score < 60为例，分析索引使用情况：
EXPLAIN SELECT name FROM `million_test` WHERE age > 10 AND score < 60;
解释结果：
index_age和index_score均未被使用。1129161。Using where，但未能有效使用索引。避免使用!=或<>操作符
WHERE age != 10;
优化WHERE子句中的条件
将复杂的逻辑条件拆分为多个简单条件，避免使用OR逻辑：WHERE age > 10 AND score > 60;
使用UNION ALL代替OR
UNION ALL：SELECT name FROM `million_test` WHERE age = 10 UNION ALL SELECT name FROM `million_test` WHERE score > 60;
避免全表扫描
确保索引覆盖查询条件，避免使用ALL类型联接。避免使用复杂函数或表达式
如WHERE age / 2 = 16会导致索引失效。优化IN和NOT IN操作
BETWEEN代替IN，如：WHERE age BETWEEN 10 AND 20;
避免重复查询字段
使用表变量代替临时表，减少磁盘读取。定期清理索引
删除不再使用的索引，避免索引占用过多空间。索引是提升数据库性能的关键工具，但也存在维护成本。建立索引时需结合查询需求，避免过度索引。以下是索引使用原则：
重点字段建立索引
常用查询字段如WHERE、JOIN和ORDER BY上的字段。覆盖查询条件的索引
确保索引包含查询条件，避免索引失效。避免过多索引
索引数不宜过多，影响插入、更新和维护效率。选择合适的存储引擎
MyISAM适合只读或写入较少的场景，而InnoDB适合需要事务支持的场景。通过以上方法，可以显著提升数据库查询性能，减少索引维护开销，实现高效数据处理。
转载地址：http://wadfk.baihongyu.com/