非常试用的SQL优化技巧

SQL优化技巧是数据库管理中的重要环节，通过优化SQL语句，可以显著提升查询速度和系统性能。以下是一些实用的SQL优化策略： 1. **负向条件查询与IN查询优化**： - 避免使用`NOT IN`或`NOT EXISTS`，它们通常会导致全表扫描。例如，`status!=0 and status!=1`可以优化为`status in (2,3)`，这样可以利用索引来加速查询。 2. **前导模糊查询与非前导模糊查询**： - 非前导的模糊查询（如`desc like 'XX%'`）可以利用索引，而前导模糊查询（如`desc like '%XX'`）则不能。尽量避免在查询中使用前导通配符，以利用索引。 3. **数据区分度与索引选择**： - 如果字段的取值范围很小，如性别字段只有男和女，那么该字段不宜建立索引，因为区分度太低，无法有效过滤数据。只有当索引能过滤掉大部分数据时（如80%以上），使用索引才有效。 4. **函数操作与索引**： - 在查询条件中对字段进行函数运算（如`YEAR(date) <= '2017'`）会使得索引失效，应改为直接比较日期，如`date <= '2017-01-01'`。 5. **Hash索引与B-Tree索引**： - 对于单条查询为主的场景，Hash索引（如MySQL的Memory引擎）可能比B-Tree索引更高效，因为其查找时间复杂度为O(1)，而B-Tree为O(log(n))。 6. **NULL值与索引**： - 单列索引不包含NULL值，复合索引不包含所有列为NULL的组合。在查询时，应避免因NULL值导致的意外结果，推荐使用`NOT NULL`约束并设置默认值。 7. **复合索引与最左前缀**： - 复合索引遵循最左前缀原则，如`(login_name, passwd)`索引，`login_name`是查询条件时可命中，反之则不可。注意，`passwd`单独作为查询条件无法使用该索引。 8. **使用ENUM代替字符串**： - ENUM类型的字段实际上存储为TINYINT，占用空间小，查询效率高。避免在ENUM中存储大字符串，以减少存储和查询成本。 9. **LIMIT 1提升效率**： - 当确定查询结果仅一行时，使用`LIMIT 1`可以提前终止查询，提高效率，例如`select * from user where login_name=? limit 1`。 10. **计算放到业务层**： - 将计算逻辑移到应用程序中，例如将`CURDATE()`替换为变量，可以节省数据库CPU并优化查询缓存。例如，先在业务层计算当前日期，然后传入SQL查询。 11. **强制类型转换与全表扫描**： - 强制类型转换（如`phone=13800001234`）会导致索引失效。在比较电话号码时，确保类型匹配，或者转换查询条件以匹配列的类型。 12. **避免使用SELECT ***： - 仅选择需要的列，避免使用`SELECT *`，这可以减少数据传输量，减轻数据库内存负担，提高查询性能。 通过理解和应用这些SQL优化技巧，可以显著改善数据库的运行效率，尤其是在处理大量数据或高并发查询时。