如何优化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的优化器能够按照预想的合理方式运行
网友评论