本文详细介绍了MySQL的性能优化方法，包括通过`show status`和慢查询日志定位低效SQL，使用`explain`分析执行计划，优化查询缓存，避免全表扫描，以及如何选择和使用索引。此外，还探讨了存储引擎的选择、数据类型的优化、表的垂直和水平拆分、事务处理和并发控制等关键点，提供了多个实用的优化策略和经验分享。
如何优化SQL语句？ select version() -- 查看SQL当前版本
|
|- 1) 通过 show status 命令了解各种SQL的执行频率 -- Com_select Com_delete Com_update Com_delete || Innodb_rows_read Innodb_rows_inserted Innodb_rows_updated Innodb_rows_deleted | Connections Uptime Slow_queries
|
|- 2)定位执行效率较低的语句
||
||- 通过慢查询日志定位 -- -log_slow_queries[=file name]启动时 -- slow_query_time
|
|- 3)通过explain分析执行效率较低的SQL语句 (explain desc)
|| -- 说明：1. select_type 表示select的类型
| 2. table 表名称(查询的结果集)
| 3. type 在表中找到所需要行的方式(访问类型)-- ALL(全表扫描) => index(索引全扫描) => range(索引范围扫描) => ref(使用唯一索引或非唯一索引的前缀扫描，返回唯一结果)
| => eq_ref(唯一索引查询) => const,system(单表中最多有唯一匹配行) => null(不使用索引)
| 4. possible_keys 查询中可能会用到的索引
| 5. key 实际用到的索引
| 6. key_len 使用到索引字段的长度
| 7. rows 扫描的行数
| 8. extra 执行情况的说明与描述
| 9. partitions SQL执行所在分区
|
|- 4)利用查询缓存优化查询：NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而开启缓存
|
|- 5)如果确定查询结果返回的只有一条记录，limit 1 会提高查询效率：
|
|- 4) 通过show profile分析SQl -- select @@have_profiling 查看是否支持 -- show profiles 查看所有SQL的延时情况 -- show profile for query + id编号
| |
| |- √MyISAM具有元数据缓存，count(*)查询较快
| |- √Innodb没有缓存，count(*)查询较慢 - sending data 磁盘读写操作(所以消耗时间)
|
|- 5) 通过trace分析优化器如何选择执行计划
|
|- 6)确定问题后如何制定优化措施？ 添加索引
索引问题
|
|- 1)MySQl的索引类型
| |
| |- 1. B-Tree索引 -- 全部支持
| |- 2. HASH索引 -- 只有Memory引擎支持
| |- 3. R-Tree(空间索引) -- MyISAM支持 特殊类型
| |- 4. Full-text(空间索引) -- MyISAM支持 特殊类型
|
|- 2)如何使用索引
| |
| |- 1. 使用索引的情况
| | |
| | |- √ 匹配索引的全值
| | |- √ 匹配索引的范围查找
| | |- √ 匹配最左前缀 col1 + col2 + col3 -- 最左原则
| | |- √ 仅仅对索引进行查询
| | |- √ 匹配索引列前缀
| | |- √ 如果列名是索引 使用 column is NULL 会用到索引
| | |- √ ICP特性 复合索引
| |
| |- 2. 使用短索引：短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作
|
|- 3)存在索引但不能使用的情况
| |
| |- 1. 以%开头的like查询不能够利用到B-Tree查询
| |- 2. 数据类型出现隐式转换
| |- 3. 符合索引没有用到最左原则
| |- 4. 如果全表扫描比使用索引要快
| |- 5. or分割开的条件前面的字段有索引后面的没有索引
| |- 6. 索引不会包含有NULL值的列
| |- 7. NOT IN 和 操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替
|
|- 4)查看索引的使用情况 -- Handler_read_key值高表示索引利用率高。 Handler_read_rnd_next值高表示需要添加索引
优化表
|
|- 1)定期分析表和检查表
| |- 1. analyze table ifu_phrase_content
| |- 2. check table ifu_phrase_content
|
|- 2) 定期优化表
| |- 1. optimize table ifu_phrase_content
常用的SQL优化
|
|- 1)大批量插入数据情况
| |
| |- 1. MyISAM: -- ALTER table ifu_phrase_content disable keys (关闭非唯一索引) ALTER table ifu_phrase_content disable keys (打开非唯一索引)
| |- 2. Innodb:
| |
| |- √Innodb类型存储数据是按照索引顺序保存的 可以根据主键的顺序保存
| |- √导入前执行 set unique checks = 0 关闭唯一性校验 之后 set unique checks = 1 开启
| |- √如果应用使用的是自动提交的方式 导入前执行 set unique_checks = 1
|
|- 2) 优化Insert语句
|
|- 3)优化order by语句
| |
| |- Mysql两种排序方式：
| | |- 1. Using Index
|| |- 2. Using filesort
|
|- 4) 优化Group by语句 -- order by null 可以减少消耗
|
|- 5) 优化嵌套查询：合理利用join
|
|- 6) 优化or条件语句：为每一个条件添加索引
数据库名/表名大小写问题
优化数据库对象
|
|- 1) 优化表的数据类型
| |- 1. 使用 procedure analyse() 函数 提出对每列的优化建议
| |- 2. 通过拆分表提高表的访问效率
| |
| |- √垂直拆分
| |- √水平拆分
|
|- 2)逆三范式
|
|- 3)使用中间表提高统计查询访问速度
表存储引擎的选择
|
|- 1) MyISAM:
||
||- √不支持事务
||- √不支持外键
||- √存储本地3个文件 - .frm(存储表定义) .MYD(MYDdata 存储数据) .MYI(MYIindex 存储索引)
||- √支持3种存储格式
| |- @静态(定长)表 -- 默认
| | |
| | |- 优点：每个字段都是定长度的，存储非常迅速，容易缓存，出现故障容易恢复
| | |- 缺点：占用空间大，存储会添加空格，查询空格自动去掉
| |
| |- @动态(不定长)表
| | |
| | |- 优点：占用空间小
| | |- 缺点：频繁删改会产生碎片，出现故障难以恢复
| |
| |- @压缩表
|
|- 2) Innodb: 提供了提交，回滚和崩溃恢复能力的事务安全，主要用来删写，读的效率会慢一些，会占用更多磁盘空间存储数据和索引
|
|- 1. 自动增长列 ALTER TABLRE ifu_phrase_content INCREMENT = n 自动增长列必须是索引，如果是组合索引也必须是索引的第一列
|- 2. 支持外键
|- 3. 存储方式
|- √使用共享表存储空间
|- √使用多表存储空间
选择合适的存储引擎
|
|- 1) MyISAM: 表以读和插入为主，对事物的安全性/并发性要求不是很高
|
|- 2) Innodb: 与MyISAM相反
选择合适的数据类型
|
|- 1)char和varchar
| |
| |- char固定定长处理速度比较快，但浪费存储空间，可以: 长度变化不大并且存储查询速度要求高的
|
|
|- 2) text和blob
| |
| |- text保存字符串数据
| |- blob保存二进制数据
|
|- 3)浮点数与定点数： 避免浮点数与浮点数之间的比较
| |
| |- 浮点会被四舍五入，会导致存储数据不精确
| |- 定点存储字符串，精度高
|
|- 4)日期类型选择：应满足日期的最小存储格式
应用层面的优化
|
|-1)使用链接池
|
|-2)减少对MySql的访问
| |
| |- √ 避免对同一数据做重复检索
| |- √ 使用查询缓存
| |- √ 增加CACHE层
|
|-3)负载均衡
|
|- √ 主从复置
|- √ 分布式数据库架构
优化MySql Server
|
|-1)MySql内存管理及优化
|
|-2)调整MySql并发相关参数
|
|- √ max_connections
|- √ back_log
|- √ table_open_cache
|- √ thread_cache_size
|- √ innodb_lock_wait_timeout
项目开发MySql经验：
|
|- √ https://coolshell.cn/articles/1846.html - MYSQL性能优化的最佳20+条经验
|- √ https://tech.meituan.com/mysql_index.html - MySQL索引原理及慢查询优化
|- √ https://www.jianshu.com/p/d7665192aaaf?hmsr=toutiao.io&utm_medium=toutiao.io&utm_source=toutiao.io MySQL优化原理
MySQL逻辑架构
|
|-1)最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。
|
|-2)MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)
| 所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。
|
|-3)最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信， 这些API接口屏蔽了不同存储引擎间的差异。
MySQL查询过程：很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
