笔记之 MySQL 优化

mysql视频教程栏目介绍MySQL的优化方法

推荐（免费）：mysql视频教程

记录学习笔记，持续更新。

优化方向

SQL优化

1. sql优化分析
2. 索引优化

优化数据库对象

1. 优化表的数据类型
2. 表拆分（水平、垂直）
3. 反范式
4. 使用中间表

优化 mysql server

1. mysql内存管理优化
2. log机制及优化
3. 调整mysql并发参数

应用优化

1. 数据库连接池
2. 使用缓存减少压力
3. 负载均衡建立集群
4. 主主同步、主从复制

Mysql优化问题分析定位

分析SQL执行频率

show status

例如：分析读为主，还是写为主

定位执行效率低的SQl

慢查询日志定位-log-slow-queries = xxx（指定文件名）SHOW PROCESSLIST查看当前正在进行的线程，包括线程状态、是否锁表

分析SQL执行计划

explain "your sql"desc "your sql"- 部分参数分析
select_type: SIMPLE 简单表，不使用表连接或子查询PRIMARY 主查询，即外层的查询UNION SUBQUER 子查询的第一个select

type: ALL 全表扫描
index 索引全扫描
range 索引范围扫描
ref 使用非唯一索引或唯一索引的前缀扫描
eq_ref 类似ref，使用的索引是唯一索引const/system 单表中最多有一个匹配行NULL 不用访问表或者索引，直接得到结果

本文来源gaodai^.ma#com搞#代!码网

show profile分析SQL

select @@have_profiling 是否支持
select @@profiling 是否开启

执行 "your sql"show profiles 
show profile block io for QUERY 17

索引优化

索引的存储分类

B-TREE索引：常见，大部分都支持HASH索引：只有memory引擎支持R-TREE索引：空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型
full-text索引：全文索引，MyISAM的一个特殊索引类型，innodb从5.6开始支持

索引的创建与删除

添加索引ALTER Table `table_name` ADD PRIMARY KEY（`column`）ALTER Table `table_name` ADD UNIQUE（`column`）ALTER Table `table_name` ADD INDEX（`column`）ALTER Table `table_name` ADD FULLTEXT（`column`）

删除ALTER Table `table_name` drop index index_name

Mysql中能使用索引的情况

匹配全值
匹配值范围查询
匹配最左前缀
仅仅对索引进行查询（覆盖查询）
匹配列前缀 （添加前缀索引）
部分精确+部分范围

不能使用索引的场景

以%开关的like查询
数据类型出现隐式转换
复合索引查询条件不包含最左部分
使用索引仍比全表扫描慢
用or分割开的条件

mysql语句优化

定期优化表

optimize table table_name 合并表空间碎片，对MyISAM、BDB、INNODB有效

如果提示不支持，可以用 mysql --skip-new 或者 mysql --safe-mode 来重启，以便让其他引擎支持

常用优化

尽量避免全表扫描，对where及orderby的列建立索引
尽量避免where使用 != 或 <>尽量避免where子句用 or 连接条件
乱用%导致全表扫描
尽量避免where子句对字段进行表达式操作
尽量避免where子句对字段进行函数操作
覆盖查询，返回需要的字段
优化嵌套查询，关联查询优于子查询
组合索引或复合索引，最左索引原则
用exist代替in当索引列有大量重复数据时，SQL查询可能不会去利用索引

优化数据库对象

优化表数据类型

PROCEDURE ANALYSE (16,256) 排除多于16个，大于256字节的ENUM建议"your sql" PROCEDURE ANALYSE ()

表拆分

垂直拆分
针对某些列常用、不常用

水平拆分
表很大
表中的数据有独立性，能简单分类
需要在表存放多种介质

反范式

增加冗余列、增加派生列、重新组表和分割表

使用中间表

数据查询量大
数据统计、分析场景

Mysql引擎比较

mysql有什么引擎？

喜欢 (0)赏

[搞代码]

分享 (0)