首頁 > 文章 > 資料庫 > 筆記之 MySQL 優化

筆記之 MySQL 優化

coldplay.xixi轉載: 2020-12-16 09:31:582158瀏覽

mysql影片教學欄位介紹MySQL的最佳化方法

筆記之 MySQL 優化

推薦（免費）：mysql影片教學

記錄學習筆記，持續更新。

最佳化方向

SQL最佳化

#sql最佳化分析

sql最佳化分析

##索引最佳化

優化資料庫物件
優化表的資料類型

表格拆分（水平、垂直）

#反範式

使用中間表

優化mysql server

mysql記憶體管理最佳化

log機制及最佳化

調整mysql並發參數

應用程式最佳化
資料庫連線池

使用快取減少壓力

負載平衡建立叢集

主主同步、主從複製

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 不用访问表或者索引，直接得到结果

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 优化笔记

mysql有什麼引擎？

關於表引擎的指令

show engines; 查看myql所支持的存储引擎
show variables like '%storage_engine'; 查看mysql默认的存储引擎
show create table table_name 查看具体表使用的存储引擎

關於innodb

1. 提供事务、回滚、系统奔溃修复能力、多版本并发控制事务2. 支持自增列3. 支持外键4. 支持事务以及事务相关联功能5. 支持mvcc的行级锁

#關於MyISAM

1. 不支持事务、不支持行级锁，只支持并发插入的表锁，主要用于高负载的select2. 支持三种不同的存储结构：静态、动态、压缩

調整參數最佳化mysql後台服務

MyISAM記憶體最佳化

#修改相应服务器位置的配置文件 my.cnf

key_buffer_size
决定myisam索引块缓存区的大小，直接影响表的存取效率，建议1/4可用内存

read_buffer 读缓存

write_buffer 写缓存

#InnoDB記憶體最佳化

innodb_buffer_pool_size 存储引擎表数据和索引数据的最大缓存区大小

innodb_old_blocks_pct LRU算法 决定old sublist的比例

innodb_old_blocks_time LRU算法 数据转移间隔时间

mysql並發參數

max_connections 最大连接数，默认151back_log 短时间内处理大量连接，可适当增大

table_open_cache 控制所有SQL执行线程可打开表缓存的数量，受其他参数制约

thread_cache_size 控制缓存客户服务线程数量，加快数据库连接速度，根据threads_created/connections来衡量是否合适

innodb_lock_wait_timeout 控制事务等待行锁时间，默认50ms

#為什麼要做應用最佳化

資料的重要性

mysql服務及自身效能瓶頸

保證大型系統穩定可靠運作

使用連線池
#減少對mysql的真實連線
a.避免相同資料重複執行（查詢快取）
b. 使用mysql快取（sql快取）

#負載平衡 a. LVS 分散式 b. 讀寫分離（主主複製、主從複製保證資料一致性）

##########資料庫連接池######php-cp 擴展，只記錄一下，這種方案可能已過時###

主從備份及讀寫分離

主主備份

##負載平衡

#相關免費學習推薦：
php程式設計（影片）
#

以上是筆記之 MySQL 優化的詳細內容。更多資訊請關注PHP中文網其他相關文章！

陳述：

本文轉載於：learnku.com。如有侵權，請聯絡admin@php.cn刪除

上一篇：如何修改mysql資料庫表結構下一篇：如何修改mysql資料庫表結構

看更多

筆記之 MySQL 優化

最佳化方向

SQL最佳化

sql最佳化分析

#反範式

log機制及最佳化

負載平衡建立叢集

Mysql最佳化問題分析定位

分析SQL執行頻率
show status 例如：分析读为主，还是写为主

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

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 来重启，以便让其他引擎支持

優化資料庫物件

#優化表格資料類型
PROCEDURE ANALYSE (16,256) 排除多于16个，大于256字节的ENUM建议"your sql" PROCEDURE ANALYSE ()

表格分割
垂直拆分针对某些列常用、不常用水平拆分表很大表中的数据有独立性，能简单分类需要在表存放多种介质

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

使用中間表格
数据查询量大数据统计、分析场景

mysql有什麼引擎？

關於表引擎的指令
show engines; 查看myql所支持的存储引擎 show variables like '%storage_engine'; 查看mysql默认的存储引擎 show create table table_name 查看具体表使用的存储引擎

關於innodb
1. 提供事务、回滚、系统奔溃修复能力、多版本并发控制事务2. 支持自增列3. 支持外键4. 支持事务以及事务相关联功能5. 支持mvcc的行级锁

#關於MyISAM
1. 不支持事务、不支持行级锁，只支持并发插入的表锁，主要用于高负载的select2. 支持三种不同的存储结构：静态、动态、压缩

調整參數最佳化mysql後台服務

MyISAM記憶體最佳化
#修改相应服务器位置的配置文件 my.cnf key_buffer_size 决定myisam索引块缓存区的大小，直接影响表的存取效率，建议1/4可用内存 read_buffer 读缓存 write_buffer 写缓存

#InnoDB記憶體最佳化
innodb_buffer_pool_size 存储引擎表数据和索引数据的最大缓存区大小 innodb_old_blocks_pct LRU算法决定old sublist的比例 innodb_old_blocks_time LRU算法数据转移间隔时间

mysql服務及自身效能瓶頸

#負載平衡 a. LVS 分散式 b. 讀寫分離（主主複製、主從複製保證資料一致性）

主從備份及讀寫分離

主主備份

##負載平衡

相關文章

筆記之 MySQL 優化

最佳化方向

SQL最佳化

sql最佳化分析

#反範式

log機制及最佳化

負載平衡建立叢集

Mysql最佳化問題分析定位

分析SQL執行頻率show status 例如：分析读为主，还是写为主

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

show profile分析SQLselect @@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 来重启，以便让其他引擎支持

優化資料庫物件

#優化表格資料類型PROCEDURE ANALYSE (16,256) 排除多于16个，大于256字节的ENUM建议"your sql" PROCEDURE ANALYSE ()

表格分割垂直拆分 针对某些列常用、不常用 水平拆分 表很大 表中的数据有独立性，能简单分类 需要在表存放多种介质

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

使用中間表格数据查询量大 数据统计、分析场景

mysql有什麼引擎？

關於表引擎的指令show engines; 查看myql所支持的存储引擎 show variables like '%storage_engine'; 查看mysql默认的存储引擎 show create table table_name 查看具体表使用的存储引擎

關於innodb1. 提供事务、回滚、系统奔溃修复能力、多版本并发控制事务2. 支持自增列3. 支持外键4. 支持事务以及事务相关联功能5. 支持mvcc的行级锁

#關於MyISAM1. 不支持事务、不支持行级锁，只支持并发插入的表锁，主要用于高负载的select2. 支持三种不同的存储结构：静态、动态、压缩

調整參數最佳化mysql後台服務

MyISAM記憶體最佳化#修改相应服务器位置的配置文件 my.cnf key_buffer_size 决定myisam索引块缓存区的大小，直接影响表的存取效率，建议1/4可用内存 read_buffer 读缓存 write_buffer 写缓存

#InnoDB記憶體最佳化innodb_buffer_pool_size 存储引擎表数据和索引数据的最大缓存区大小 innodb_old_blocks_pct LRU算法 决定old sublist的比例 innodb_old_blocks_time LRU算法 数据转移间隔时间

mysql服務及自身效能瓶頸

#負載平衡 a. LVS 分散式 b. 讀寫分離（主主複製、主從複製保證資料一致性）

主從備份及讀寫分離

主主備份

##負載平衡

相關文章

分析SQL執行頻率
show status 例如：分析读为主，还是写为主

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

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分割开的条件

#定期最佳化表格
optimize table table_name 合并表空间碎片，对MyISAM、BDB、INNODB有效如果提示不支持，可以用 mysql --skip-new 或者 mysql --safe-mode 来重启，以便让其他引擎支持

#優化表格資料類型
PROCEDURE ANALYSE (16,256) 排除多于16个，大于256字节的ENUM建议"your sql" PROCEDURE ANALYSE ()

表格分割
垂直拆分针对某些列常用、不常用水平拆分表很大表中的数据有独立性，能简单分类需要在表存放多种介质

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

使用中間表格
数据查询量大数据统计、分析场景

關於表引擎的指令
show engines; 查看myql所支持的存储引擎 show variables like '%storage_engine'; 查看mysql默认的存储引擎 show create table table_name 查看具体表使用的存储引擎

關於innodb
1. 提供事务、回滚、系统奔溃修复能力、多版本并发控制事务2. 支持自增列3. 支持外键4. 支持事务以及事务相关联功能5. 支持mvcc的行级锁

#關於MyISAM
1. 不支持事务、不支持行级锁，只支持并发插入的表锁，主要用于高负载的select2. 支持三种不同的存储结构：静态、动态、压缩

MyISAM記憶體最佳化
#修改相应服务器位置的配置文件 my.cnf key_buffer_size 决定myisam索引块缓存区的大小，直接影响表的存取效率，建议1/4可用内存 read_buffer 读缓存 write_buffer 写缓存

#InnoDB記憶體最佳化
innodb_buffer_pool_size 存储引擎表数据和索引数据的最大缓存区大小 innodb_old_blocks_pct LRU算法决定old sublist的比例 innodb_old_blocks_time LRU算法数据转移间隔时间