mysql優化
一、資料庫設計原則
資料庫設計的三範式
學生表(學号，姓名，生日，課程名，課程成績，等級，學院，院長)
| 學号 | 姓名 | 生日 | 課程号 | 課程名 | 成績 | 等級 | 學院 | 院長 |
|---|---|---|---|---|---|---|---|---|
| 10001 | 張三 | 1998.1.1 | tp3021,h333 | 計算機原理，高等數學 | 87，90 | B,A | 計算機學院 | xxx |
- 第一範式 表中每一個屬性不可再分，不允許出現表中套表的情況
學号 姓名 生日 課程号 成績 等級 學院 院長 10001 張三 1998.1.1 tp3021 87 B 計算機學院 xxx 10001 張三 1998.1.1 h333 90 A 計算機學院 xxx 課程号 課程名 tp3201 計算機原理 h333 高等數學 - 第二範式
鍵/碼 區分不同記錄，可以一個屬性或者多個屬性的組合，主鍵是指定的鍵 每一個非主屬性（不保含在任何一個鍵裡的屬性）必須完全依賴于主鍵（不存在非主屬性部分依賴于碼）學号 姓名 生日 學院編号 學院編号 學院 院長 學号 課程号 成績 等級 - 第三範式
第三範式： 不存在非主屬性傳遞依賴于主碼學号 課程号 成績 等級編号 up down 等級 1 100 90 A 2 89 80 B - 反範式設計
- 如果考慮查詢性能，應該盡量減少表的關聯
- 将經常查詢的資料都放到一個表裡，盡量少采用字典表
表設計
- 盡量選用長度小的類型
- 能選用整型，不要選用字元串
- 日期型能用date就不要使用datetime，不準用字元串存儲日期
- 字段不要設定預設值null
- 主鍵一般無意義，用整型，自增
二、sql語句的優化
1.開啟慢查詢
在mysql下查詢
mysql> show VARIABLES like '%slow%';
+---------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |慢查詢的門檻值，超過這個值認為是慢查詢
| slow_query_log | OFF |慢查詢是關閉的
| slow_query_log_file | /var/lib/mysql/ubuntu-slow.log |慢查詢日志檔案路徑
+---------------------------+--------------------------------+
5 rows in set (0.01 sec)
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
#在[mysqld]後面增加
long_query_time = 1 #超過1秒鐘的sql記錄下來
log-slow-queries = /var/mysql/log/log.txt #慢查詢的日志檔案
log-queries-no-using-indexes #把沒使用索引的查詢記錄下來
2 使用explain/desc分析sql語句
explain select ... #分析sql語句，sql語句本身并不執行
explain select name ,age from user where age >10
-> ;
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | user | range | index_age | index_age | 2 | NULL | 2 | Using where |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)
select_type : 查詢的類型
simple 指單表查詢（不使用連接配接、子查詢）
primary： 主查詢
union 聯合查詢的第二個sql語句，或後面的查詢
subquery 子查詢
table 表名
type 查詢方式：
all
全表掃描，對于資料表從頭到尾找一遍
如果有limit限制，則找到之後就不在繼續向下掃描
select * from tb1 where email = '[email protected]'
select * from tb1 where email = '[email protected]' limit 1;
找到一個後就不再繼續掃描
index
全索引掃描，對索引從頭到尾找一遍
select id from student;
range
對索引列進行範圍查找
index_merge
合并索引，使用多個單列索引搜尋
ref_or_null
ref
根據索引查找一個或多個值
eq_ref
連接配接時使用primary key 或 unique類型
const
常量
表最多有一個比對行,因為僅有一行,在這行的列值可被優化器剩餘部分認為是常數,const表很快,
因為它們隻讀取一次
system
系統，表僅有一行(=系統表)。這是const聯接類型的一個特例
性能
all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
possible_keys 可能被用到的索引
key 使用的索引
key_len 索引字段最大使用長度
rows 影響的行數，越小越好
關注：type 、key、rows
3 優化sql語句
1 查詢的時候不要使用select *
2.盡量使用limit 1 取得唯一的一行
3.盡量使用索引字段進行查詢
4 可以使用覆寫索引加速查詢 （一個索引包含了查詢結果中所有的字段）
5 盡量少用like 或者or
6 不要使用全文索引，如果非要使用可以把全文索引獨立出來，建立全文索引伺服器
7 關聯查詢的時候，關聯的字段都應該有索引
8 不要使用!=操作，不使用索引
9 查詢的時候類型不比對不使用索引
10 聯合索引不帶左字首，不使用索引
11 盡量減少子查詢，可以使用關聯查詢代替子查詢
select count(*) from article where uid in(select uid from user where id=10)
select count(*) from aticle,user where user.id = aticle.uid and user.id=10
select count(*) from aticle join user on user.id=article.uid where userid.id=10
12 盡量多試驗不同sql語句，比較他們的效率，采用最少
13 不要在where中，運算符左邊運算,隻要是計算，不采用索引
select username from user where age/2>10
14 不要在where中，運算符左邊不要出現任何函數,否則不采用索引
select COUNT(*) from user where year(birthday) == 1993;
15 避免預設排序
select cid,count(*) from bbs group by cid
select cid,count(*) from bbs group by cid order by null #不排序
4 其他優化措施
在應用層面可以nosql技術，把資料儲存redius、memcached中加速查詢
從架構方面：讀寫分離
可以使用mysql分區技術，把一個表分為多個檔案
分庫分表分機器
把表進行垂直切分，或水準切分
資料庫設計 ====》索引 ===》SQL語句優化 ===》分區=》nosql緩存 ==》讀寫分離=》分庫分表分機器（資料庫中間件）=》表的垂直/水準切分
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)