首頁 > 資料庫 > mysql教程 > 筆記之 MySQL 優化

筆記之 MySQL 優化

coldplay.xixi
發布: 2020-12-16 09:31:58
轉載
2235 人瀏覽過

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

筆記之 MySQL 優化

推薦(免費):mysql影片教學

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

最佳化方向

SQL最佳化

  1. #sql最佳化分析
  2. ##索引最佳化

sql最佳化分析

##索引最佳化
  1. 優化資料庫物件
  2. 優化表的資料類型
表格拆分(水平、垂直)

#反範式

使用中間表
  1. 優化mysql server
mysql記憶體管理最佳化

log機制及最佳化

調整mysql並發參數
  1. 應用程式最佳化
  2. 資料庫連線池
使用快取減少壓力

負載平衡建立叢集

主主同步、主從複製

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服務及自身效能瓶頸

保證大型系統穩定可靠運作
  1. #應用程式最佳化方法


  2. 使用連線池

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

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

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

主從備份及讀寫分離

主主備份

##負載平衡

#相關免費學習推薦:

php程式設計(影片)

#

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

相關標籤:
來源:learnku.com
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
作者最新文章
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板