MySQL規格與基本優化

 

MySQL版本

5.7 (配合gcp 的 cloud sql)

DB引擎

InnoDB

DB規格

character 使用 UTF8mb4
collate 使用 utf8mb4_unicode_ci

CREATE DATABASE  database_name 
	character set UTF8mb4 collate utf8mb4_unicode_ci;

Table規格

character 使用 UTF8mb4
collate 使用 utf8mb4_unicode_ci

CREATE TABLE tbl_name (column_list) ENGINE=INNODB 
	CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

my.cnf設定

[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
#紀錄slow query 
log-slow-queries = /var/log/mysql/mysql-slow.log 
long_query_time = 2
log-long-format
key_buffer 能夠把所有index 載入最好

基本優化觀念

至少看以下項目：

1 資料正規化/反正規化

2 Primary Key / Index 建立概念

3 資料庫記憶體管理

4 基本效能評估

基本優化方向

1 基本上遵循資料正規化，有必要時就反正規化，不必拘泥於規則。

2 減少資料量

3 使用速度快的資料類型

4 優化查詢語句速度

5 硬體的影響：RAM > SSD > CPU。MySQL可以快取，RAM越大越好。

基本優化項目

1 RAM最好>=DB資料的3倍，避免swap SSD。

2 資料類型的SQL計算速度

1boolean > int > timestamp > datetime > double > char > varchar

3 日期盡量用timestamp，因為速度快，且有cache

timestamp儲存UTC時間，會依據time zone設定返回不同時間

4 有where就要建index，有order就要建index

primary key / index 屬性速度
1boolean > int > char > varchar

5 ID盡量不要使用string，因為ID是所有查詢的基本，使用string 佔空間、速度又慢。

6 可以算的先算，不要讓MySQL去算。

雖然MySQL有很多內建函式，但是不會比你程式算的快，而且不好維護。

//使用MySQL內建函式
SELECT * FROM table WHERE DATE_ADD(NOW(),INTERVAL 10 DAY) > create_date;

//更好，先算好時間
SELECT * FROM table WHERE DATE_ADD('2019-01-01 00:00:00',INTERVAL 10 DAY) > create_date;

//更好，改成int
SELECT * FROM table WHERE DATE_ADD(1324335674,INTERVAL 10 DAY) > create_date;

7 order by 的標的要設成index，因為order by會讓MySQL指定key做index搜尋，權重大於WHERE條件式。