mysql基本知识点梳理和查询优化

阅读量：788 次

发布时间：2023-02-12

本文共 1703 字，大约阅读时间需要 5 分钟。

MySQL数据库优化指南

一、索引相关

索引是数据库查询性能的重要因素，但并非所有情况都适合使用。索引的基数是数据列中不同值的数量，基数越高，索引越有用。例如，包含多个不同年龄值的字段，索引会显著提升查询效率。而性别字段（如仅有“M”和“F”）由于值重复率高，索引的帮助不大，甚至可能引发全表扫描。

索引失效原因

运算类型不支持：对索引列进行运算如加、减、乘、除、逻辑运算、模运算等，索引会失效。

类型不匹配：将varchar字段用于number类型的where条件，索引会被忽略。

函数应用：对索引字段应用函数（如ROUND()），需要建立基于函数的索引。MySQL 8.0支持直接索引函数字段，5.7可通过虚拟列实现。

OR条件优化：OR条件即使有索引，也可能无法使用。建议避免OR条件，若必须使用，需为每个列建立索引。

字符串类型：未使用引号的字符串字段索引失效。

位图索引：is null和is not null索引会失效。

组合索引：遵循最左原则，确保索引字段顺序与查询条件一致。

二、EXPLAIN分析

基本用法

使用DESC或EXPLAIN分析SQL语句。

使用EXPLAIN extended查看实际执行计划，分析查询性能。

性能优化特性

覆盖索引：查询数据已在索引中找到，避免回表访问。

Index Condition Pushdown (ICP)：部分where条件可下推到存储引擎，提升查询效率。

索引合并：多个索引分别查询，合并结果以提高效率。

临时表和文件排序：使用临时表优化排序和分组查询。

查询状态：

Using index：覆盖索引。

Using filesort：MySQL无法利用索引排序。

Using temporary：查询涉及排序操作。

impossible where：where条件总是错误，无法获取数据。

select optimized away：优化了MIN/MAX或COUNT(*)操作。

distinct：优化distinct操作，早止重复值查找。

type：
- system：系统表，常见于const类型。
- const：主键或唯一索引扫描。
- eq_ref：唯一性索引扫描。
- ref：非唯一性索引扫描。
- range：基于范围条件的扫描。
- index：全表索引扫描。
- all：全表扫描。

三、字段类型和编码

字段类型

字符串编码：
- utf8：最大支持3个字节，不支持emoji，需使用utf8mb4。
- utf8mb4：支持emoji，需在配置文件中指定客户端字符集，并在连接池中设置初始化SQL。

排序规则：
- utf8_general_ci：大小写不敏感。
- utf8_bin：区分大小写，常用于区分大小写敏感的场景。

四、SQL语句总结

常用但容易忘的语句

插入忽略冲突：INSERT IGNORE INTO。

更新或插入：INSERT ... ON DUPLICATE KEY UPDATE。

替代插入：REPLACE INTO。

备份表：CREATE TABLE ... SELECT ...。

复制表结构：CREATE TABLE ... LIKE ...。

锁相关：

共享锁：LOCK IN SHARE MODE。

排它锁：FOR UPDATE。

强制索引：FORCE INDEX。

禁用缓存：SQL_NO_CACHE。

查询优化

延迟关联：避免回表操作，适用于分页。

避免多表join：尽量少于3个表关联。

注意事项

where顺序：从右到左解析条件，优先使用where而非having。

distinct性能：通过group by优化。

连表查询：避免复杂的join操作。

五、踩坑

自增列处理：truncate会重置自增基数。

聚合函数滤空：SUM(NULL)返回NULL而非0。

NULL处理：a = null永远为false，正确写法为a is null。

在线修改：大表修改需使用pt-online-schema-change工具。

通过以上优化，数据库性能将得到显著提升，建议结合实际业务场景，合理设计索引和查询策略，避免常见问题。

转载地址：http://phbfk.baihongyu.com/

你可能感兴趣的文章

MySQL 深度分页性能急剧下降，该如何优化？

MySQL 用 limit 为什么会影响性能？

查看>>

MySQL 用 limit 为什么会影响性能？有什么优化方案？

查看>>

MySQL 用户权限管理：授权、撤销、密码更新和用户删除（图文解析）

查看>>

mysql 用户管理和权限设置

查看>>

MySQL 的 varchar 水真的太深了！

查看>>

mysql 的GROUP_CONCAT函数的使用（group_by 如何显示分组之前的数据）

查看>>

MySQL 的instr函数

查看>>

MySQL 的mysql_secure_installation安全脚本执行过程介绍

查看>>

MySQL 的Rename Table语句

MySQL 的存储引擎有哪些？为什么常用InnoDB？

MySQL 精选 60 道面试题（含答案）

查看>>

mysql 索引

查看>>