注
本文是對于sqlercn在慕課上發(fā)表的“MySQL 性能優(yōu)化”課程的學(xué)習(xí)筆記岗宣，內(nèi)容并非原創(chuàng)。
前言
MYSQL性能優(yōu)化主要包含三種方法：
- 查詢優(yōu)化
- 表結(jié)構(gòu)優(yōu)化
- 系統(tǒng)優(yōu)化
大部分的性能問題能通過查詢優(yōu)化解決，其次是表結(jié)構(gòu)優(yōu)化譬胎，最后才是系統(tǒng)優(yōu)化育谬。因此查詢優(yōu)化也會占據(jù)大量的篇幅。
查詢優(yōu)化
慢查詢?nèi)罩?/h3>
如何開啟慢查詢：
mysql> show variables like 'slow_query_log'
mysql> set global slow_query_log = ON;
mysql> set global slow_query_log_file='D:/download/mysql-8.0.19-winx64/mysql-8.0.19-winx64/mysql-slow.log';
mysql> set global log_queries_not_using_indexes=on;
mysql> set global long_query_time=1; //超過1s的查詢都會被記入慢查詢?nèi)罩?TCP Port: 3306, Named Pipe: MySQL
Time Id Command Argument
# Time: 2020-02-15T08:21:23.220194Z
# User@Host: root[root] @ localhost [::1] Id: 13
# Query_time: 0.001607 Lock_time: 0.000536 Rows_sent: 599 Rows_examined: 599
use sakila;
SET timestamp=1581754883;
select * from customer;
可以使用一些工具來分析慢查詢：
1.mysqldumpslow
2.pt-quary-slow
其中mysqldumpslow是mysql自帶的分析工具鞭执，可以匯總慢查詢記錄并進行排序
mysqldumpslow you-slow-log.log ##對you-slow-log.log進行分析
而pt-quary-slow能分析出更多的統(tǒng)計信息
哪些慢查詢需要引起關(guān)注
- 查詢次數(shù)多且用時長
- IO大的SQL
- 沒有使用索引的SQL
explain
mysql> explain select * from customer ;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | customer | NULL | ALL | NULL | NULL | NULL | NULL | 599 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
table: 顯示這行數(shù)據(jù)是關(guān)于哪張表
type： 顯示連接使用了什么類型司顿，包含：const，eq_reg兄纺，ref大溜，range，index估脆，all
const->當(dāng)MySQL對查詢某部分進行優(yōu)化钦奋，并轉(zhuǎn)換為一個常量時，使用這些類型訪問疙赠。如將主鍵置于where列表中付材，MySQL就能將該查詢轉(zhuǎn)換為一個常量（where上用到唯一索引或默認索引）
select * from table where id = 1;
eq_reg->類似ref，區(qū)別就在使用的索引是唯一索引圃阳，對于每個索引鍵值厌衔，表中只有一條記錄匹配，簡單來說限佩，就是多表連接中使用primary key或者 unique key作為關(guān)聯(lián)條件
ref->基于索引的查找
select * from table where catalog = 1;
range->基于索引的范圍查找
select * from table where catalog > 1;
index->對于索引的掃描
select catalog from table;
all->全表掃描
select * from table;
possible_keys:可能使用的索引
key：實際使用的索引
key_len：使用索引的長度（理論上越短越好）
ref：列與索引的比較葵诈，表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值
rows：mysql認為必須檢查的用來返回請求的行數(shù)
extra：
using filesort祟同，using temporary作喘。盡量優(yōu)化！
索引優(yōu)化
- 在需要索引的地方加上索引晕城，并保證索引能被MYSQL正常使用上
- 在滿足使用的情況下泞坦，索引要盡可能的小
- 對于聯(lián)合索引，離散度高的索引加在前砖顷，離散度低的加在后
- 減少重復(fù)贰锁，冗余和不使用的索引
合適的數(shù)據(jù)類型
- 最小的數(shù)據(jù)類型
- 最簡單的數(shù)據(jù)類型
- 少用null
- 少用text，如果要使用滤蝠，將它提出到另一張附加表中
三大范式
- 所有列不可拆分
- 別的列必須依賴與主鍵而不能只依賴主鍵的一部分
- 別的列必須直接依賴主鍵豌熄，而不能間接依賴主鍵
反范式
用一些冗余的數(shù)據(jù)來達到減少查詢開銷的目的，空間換時間
垂直拆分
根據(jù)表的字段進行拆分物咳，比如一個表中字段很多锣险，其中有一部分字段很少被用到，有一部分字段經(jīng)常被用到览闰，還有一部分字段是text或很大的數(shù)據(jù)結(jié)構(gòu)芯肤，那么我們可以將它們根據(jù)這三種特點進行拆分
水平拆分
如果表的數(shù)據(jù)太多，則可以將數(shù)據(jù)分配到多張表中压鉴。使用hash的方法對id進行索引崖咨，同時表可以分為業(yè)務(wù)表和統(tǒng)計表。業(yè)務(wù)表指平時程序調(diào)用的表油吭，對業(yè)務(wù)表采用水平拆分達到性能的提升击蹲，而對統(tǒng)計表署拟，由于被用到的次數(shù)比較少，同時又會設(shè)計到表中大部分的數(shù)據(jù)际邻，因此選擇不對其進行拆分芯丧，避免使用join芍阎。
業(yè)務(wù)表和統(tǒng)計表同時存在世曾，
