配置優(yōu)化
文件位置
/etc/my.cnf或%MYSQL_HOME%/my.ini
根據(jù)實(shí)際情況適當(dāng)調(diào)整配置項(xiàng)
-
公共參數(shù)默認(rèn)值
#同時(shí)處理最大連接數(shù)仅乓，推薦設(shè)置最大連接數(shù)是上限連接數(shù)的80%左右 max_connections = 151 #查詢排序時(shí)緩沖區(qū)大小，只對(duì)order by和group by起作用风纠，可增大此值為16M sort_buffer_size = 2M #打開文件數(shù)限制蚯斯，如果show global status like 'open_files'查看的值等于或者大于open_files_limit值時(shí)薄风，程序會(huì)無(wú)法連接數(shù)據(jù)庫(kù)或卡死 open_files_limit = 1024 -
MyISAM 引擎參數(shù)默認(rèn)值
#索引緩存區(qū)大小，一般設(shè)置物理內(nèi)存的30-40% key_buffer_size = 16M #讀操作緩沖區(qū)大小拍嵌，推薦設(shè)置16M或32M read_buffer_size = 128K #打開查詢緩存功能 query_cache_type = ON #查詢緩存限制遭赂，只有1M以下查詢結(jié)果才會(huì)被緩存，以免結(jié)果數(shù)據(jù)較大把緩存池覆蓋 query_cache_limit = 1M #查看緩沖區(qū)大小横辆，用于緩存SELECT查詢結(jié)果撇他，下一次有同樣SELECT查詢將直接從緩存池返回結(jié)果，可適當(dāng)成倍增加此值 query_cache_size = 16M -
InnoDB 引擎參數(shù)默認(rèn)值
#索引和數(shù)據(jù)緩沖區(qū)大小狈蚤，一般設(shè)置物理內(nèi)存的60%-70% innodb_buffer_pool_size = 128M #緩沖池實(shí)例個(gè)數(shù)困肩，推薦設(shè)置4個(gè)或8個(gè) innodb_buffer_pool_instances = 1 #關(guān)鍵參數(shù)，0代表大約每秒寫入到日志并同步到磁盤脆侮，數(shù)據(jù)庫(kù)故障會(huì)丟失1秒左右事務(wù)數(shù)據(jù)锌畸。1為每執(zhí)行一條SQL后寫入到日志并同步到磁盤，I/O開銷大他嚷，執(zhí)行完SQL要等待日志讀寫蹋绽，效率低芭毙。2代表只把日志寫入到系統(tǒng)緩存區(qū)，再每秒同步到磁盤卸耘，效率很高退敦，如果服務(wù)器故障，才會(huì)丟失事務(wù)數(shù)據(jù)蚣抗。對(duì)數(shù)據(jù)安全性要求不是很高的推薦設(shè)置2侈百，性能高，修改后效果明顯翰铡。 innodb_flush_log_at_trx_commit = 1 #默認(rèn)是共享表空間钝域，共享表空間idbdata文件不斷增大，影響一定的I/O性能锭魔。推薦開啟獨(dú)立表空間模式例证，每個(gè)表的索引和數(shù)據(jù)都存在自己獨(dú)立的表空間中，可以實(shí)現(xiàn)單表在不同數(shù)據(jù)庫(kù)中移動(dòng)迷捧。 innodb_file_per_table = OFF #日志緩沖區(qū)大小织咧，由于日志最長(zhǎng)每秒鐘刷新一次，所以一般不用超過(guò)16M innodb_log_buffer_size = 8M
數(shù)據(jù)庫(kù)設(shè)計(jì)優(yōu)化
- 數(shù)字類型沒有負(fù)數(shù)時(shí)漠秋，最好添加
UNSIGNED標(biāo)識(shí) - 為每張表添加一個(gè)
UNSIGNED INT類型的主鍵字段 - 盡量使用
NOT NULL定義字段笙蒙，節(jié)省空間；可以給定默認(rèn)值庆锦，以確保非空 - IP 地址定義為
UNSIGNED INT類型捅位，節(jié)省空間（INET_ATON()函數(shù)) - 為字段指定合適的長(zhǎng)度和類型，如能使用
TINYINT就不要使用INT - 經(jīng)常搜索的字段建立索引
對(duì)于存在模糊查詢使用場(chǎng)景的字段搂抒，添加索引艇搀，可提高like的查詢速度； 同時(shí)要確保該字段NOT NULL才會(huì)有效燕耿； - 索引不是越多越好中符，太多會(huì)影響
INSERT、UPDATE和DELETE的效率 - 索引誉帅、分區(qū)
對(duì)于數(shù)據(jù)量較大的表淀散，通過(guò)時(shí)間+標(biāo)識(shí)（如果存在）等多個(gè)字段建立索引，同時(shí)建立分區(qū)蚜锨；
對(duì)于歷史數(shù)據(jù)档插，考慮定時(shí)備份到歷史表（可新增一張歷史表），可加快實(shí)時(shí)數(shù)據(jù)的查詢 - 固定精度字段使用
DECIMAL而不是DOUBLE亚再，如果對(duì)存儲(chǔ)空間要求更高郭膛，建議乘以固定倍數(shù)轉(zhuǎn)換成整數(shù)存儲(chǔ)，可以大大節(jié)省存儲(chǔ)空間 - 定長(zhǎng)字符使用
CHAR氛悬，非定長(zhǎng)字符使用VARCHAR - 時(shí)間日期類型選擇合適的定義则剃，節(jié)省存儲(chǔ)空間耘柱，例如：如果只是存儲(chǔ)到天，則使用
DATE棍现；如果精確到時(shí)間调煎，則使用TIMESTAMP而不是DATETIME - 外鍵約束
系統(tǒng)中存在軟刪除（表中存在刪除標(biāo)識(shí)）， 建立外鍵約束存在沖突己肮，即記錄以軟刪除形式存在（刪除標(biāo)識(shí)為已刪除）士袄，此時(shí)操作主表中的記錄會(huì)報(bào)錯(cuò)，被其他表引用谎僻。
對(duì)于這種情況娄柳，手動(dòng)維護(hù)主從表之間的關(guān)系
SQL 執(zhí)行效率分析
-
開啟慢查詢?nèi)罩?/p>
- 臨時(shí)修改變量
set global slow_query_log=on; #開啟慢查詢功能 set global slow_query_log_file=' /var/run/mysqld/mysqld-slow.log'; #指定慢查詢?nèi)罩疚募恢?set global log_queries_not_using_indexes=on; #記錄沒有使用索引的查詢 set global long_query_time=1; #只記錄處理時(shí)間1s以上的慢查詢- 修改配置文件
slow_query_log=on slow_query_log_file=' /var/run/mysqld/mysqld-slow.log' log_queries_not_using_indexes=on long_query_time=1查看配置是否生效
show variables like '%slow%';slow-variables.PNG分析慢查詢?nèi)罩荆褂?MySQL 自帶的 mysqldumpslow 工具
mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log #查看最慢的前三個(gè)查詢 EXPLAIN
用于解釋 MySQL 是如何處理 SQL 語(yǔ)句艘绍，有助于分析語(yǔ)句和表結(jié)構(gòu)赤拒，如主鍵、索引鞍盗、搜索需了、排序等等“慵祝可配合慢查詢?nèi)罩荆业叫枰治龅恼Z(yǔ)句鹅颊，通過(guò)解釋結(jié)果定位問題敷存。
EXPLAIN SELECT ...
結(jié)果字段說(shuō)明
- select_type
- simple 簡(jiǎn)單表
- primary 主查詢
- union 第二個(gè)或者后面的查詢
- dependent union union中的第二個(gè)或后面的select語(yǔ)句,取決于外面的查詢
- union result union的結(jié)果
- subquery 子查詢中的第一個(gè)select
- dependent subquery 子查詢中的第一個(gè)select,取決于外面的查詢
- derived 導(dǎo)出表的select(from子句的子查詢)
- table 輸出結(jié)果的表
- type 表示mysql在表中查詢方式
- all 掃描全表
- index 索引掃描
- range 索引掃描范圍，常見于<,<=,>,>=,between
- ref 非唯一索引掃描
- eq_ref 唯一索引掃描
- const,system 最多只有一行匹配堪伍，查詢非趁常快，例如主鍵primary key/唯一索引unique index/表中只有一條記錄
- null 不用訪問表或者索引帝雇，直接就能得到結(jié)果 `explain select 1 from test where 1`
- EXTRA
- Using filesort: **出現(xiàn)時(shí)需要優(yōu)化**涮俄。MYSQL需要進(jìn)行額外的步驟來(lái)發(fā)現(xiàn)如何對(duì)返回的行排序。它根據(jù)連接類型以及存儲(chǔ)排序鍵值和匹配條件的全部行的行指針來(lái)排序全部行
- Using temporary **出現(xiàn)時(shí)需要優(yōu)化**尸闸。MYSQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)存儲(chǔ)結(jié)果,這通常發(fā)生在對(duì)不同的列集進(jìn)行ORDER BY上,而不是GROUP BY上
- `explain extened select count(1) from test where 1=1` 可以查看執(zhí)行sql前彻亲，mysql做了哪些優(yōu)化
- `explain partitions select id from test where id = 15` 可以查看分區(qū)名稱
- 查詢緩存
查詢緩存可以跳過(guò)SQL解析優(yōu)化查詢等階段，直接返回緩存結(jié)果給用戶吮廉，MySQL 已默認(rèn)開啟
image
對(duì)于頻繁更新的表苞尝，查詢緩存是不適合的，而對(duì)于不常改變數(shù)據(jù)且有大量相同sql查詢的表宦芦，查詢緩存能提升性能宙址。
查詢緩存不自動(dòng)處理空格，因此 SQL 語(yǔ)句中要盡量減少空格的使用
-
命中條件
緩存存在一個(gè)hash表中调卑，通過(guò)查詢SQL抡砂、數(shù)據(jù)庫(kù)大咱、客戶端協(xié)議等作為key
在判斷是否命中前，MySQL不會(huì)解析SQL注益，而是直接使用SQL去查詢緩存- 查詢必須完全一致碴巾，包括大小寫、空格聊浅、注釋
- 緩存不存儲(chǔ)不確定結(jié)果的查詢餐抢。如
NOW()這類不確定函數(shù)的使用
SQL 語(yǔ)句優(yōu)化
- 盡量滿足查詢緩存條件來(lái)編寫 SQL，提高緩存命中率
- 非必要場(chǎng)景不要使用
SELECT *低匙，只查詢需要的字段 - 只返回一條記錄時(shí)使用
LIMIT 1 - 拆分大量的
INSERT或DELETE語(yǔ)句旷痕，分批執(zhí)行 - 盡量避免在 where 子句中使用
!=或<>操作符，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描 - 盡量避免在 where 子句中使用
or來(lái)連接條件顽冶，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描 - 盡量避免在 where 子句中對(duì)字段進(jìn)行
NULL操作 - 使用
EXISTS代替IN - 使用
JOIN代替子查詢 - 使用
UNION ALL代替UNION - 對(duì)于存在分區(qū)的表欺抗，查詢時(shí)一定要帶上分區(qū)列
- 不要在索引上進(jìn)行下列操作：
- 計(jì)算索引字段
- 使用
NOT、<>强重、!= - 使用
IS NULL和IS NOT NULL - 轉(zhuǎn)換數(shù)據(jù)類型
- 使用空值
