Java培訓(xùn)：Mysql性能優(yōu)化詳解

更新時(shí)間:2022-11-10 來源:黑馬程序員瀏覽量:

IT培訓(xùn)班

1.為什么要進(jìn)行sql優(yōu)化

　　因?yàn)闆]有進(jìn)行sql優(yōu)化的語句執(zhí)行性能低下。而性能低下的原因:sql語句欠佳，索引失效，服務(wù)器參數(shù)設(shè)置不合理(緩沖、線程數(shù))

　　本文整個(gè)優(yōu)化過程主要是圍繞索引進(jìn)行

　　2.Mysql安裝啟動(dòng)配置(CentOS7)

　　1)版本介紹與選擇

　　目前主流版本 5.x

　　5.0-5.1: 相當(dāng)于4.x版本的延續(xù)，升級(jí)維護(hù)

　　5.4 -5.x: Mysql整合了三方公司的新存儲(chǔ)引擎 (推薦使用5.7版本，當(dāng)前比較穩(wěn)定的版本)

　　2)mysql安裝-rpm

　　2.1檢查服務(wù)器msyql安裝情況,有就先卸載自帶的mysql【Centos7 默認(rèn)安裝mariadb】

　　> rpm -qa|grep 軟件名字【檢查命令】

　　> rpm -e --nodeps 軟件包名【卸載命令】

　　> yum remove 軟件包名【卸載命令】

　　rpm -qa|grep mariadb

　　//卸載方式一

　　rpm -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64

　　//卸載方式二(建議使用yum卸載,可自動(dòng)處理依賴關(guān)系)

　　yum remove mariadb-libs-5.5.44-2.el7.centos.x86_64

　　2.2下載安裝包

　　https://downloads.mysql.com/archives/community/

　　2.3解壓安裝包到/usr/local/mysql

mkdir /usr/local/mysql
tar -xvf mysql-5.7.25-1.el7.x86_64.rpm-bundle.tar -C /usr/local/mysql

　　
2.4按照順序安裝rpm安裝包

#先安裝依賴
yum install net-tools

#注意: 下列安裝包的安裝順序不能變
rpm -ivh mysql-community-common-5.7.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-5.7.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.25-1.el7.x86_64.rpm

　　
2.5驗(yàn)證是否安裝成功【查看mysql版本】

mysqladmin --version

　　
2.6mysql常用命令

　　systemctl status mysqld 查看mysql服務(wù)狀態(tài)

　　systemctl start mysqld 啟動(dòng)mysql服務(wù)

　　systemctl stop mysqld 停止mysql服務(wù)

　　systemctl enable mysqld 設(shè)置開機(jī)時(shí)啟動(dòng)mysql服務(wù)，避免每次開機(jī)啟動(dòng)mysql

　　2.7設(shè)置密碼

/usr/bin/mysqladmin -u root password 'new-password'

　　
2.8登錄

mysql -u root -p

　　
2.9設(shè)置root用戶遠(yuǎn)程連接權(quán)限和密碼

mysql> grant all privileges on  *.*  to  'root'  @'%'  identified  by  'remote-password';
mysql> flush  privileges;

　　
2.10開放端口

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

　　
3.邏輯分層,存儲(chǔ)引擎,解析過程

　　1)邏輯分層

　　連接層 - 提供與客戶端連接的服務(wù)

　　服務(wù)層 - 1.提供各種用戶使用的接口(增刪改查，等) 2.提供sql優(yōu)化器(mysql query optimizer)

　　引擎層 - 提供了各種存儲(chǔ)數(shù)據(jù)的方式(InnoDB,MyISAM)

　　存儲(chǔ)層 - 存儲(chǔ)數(shù)據(jù)

　　2)存儲(chǔ)引擎

　　2.1常用引擎(InnoDB， MyISAM)

　　MyISAM：ISAM是Indexed Sequential Access Method (有索引的順序訪問方法) 的縮寫，它是存儲(chǔ)記錄和文件的標(biāo)準(zhǔn)方法。不是事務(wù)安全的，而且不支持外鍵，如果執(zhí)行大量的select，insert MyISAM比較適合

　　InnoDB：支持事務(wù)安全的引擎，支持外鍵、行鎖、事務(wù)是他的最大特點(diǎn)。如果有大量的update和insert，建議使用InnoDB，特別是針對(duì)多個(gè)并發(fā)和QPS較高的情況

　　2.2區(qū)別

1.MyISAM不支持事務(wù)，而InnoDB支持。InnoDB的AUTOCOMMIT默認(rèn)是打開的，即每條SQL語句會(huì)默認(rèn)被封裝成一個(gè)事務(wù)，自動(dòng)提交，這樣會(huì)影響速度，所以最好是把多條SQL語句顯示放在begin和commit之間，組成一個(gè)事務(wù)去提交。

　　2. MyISAM只支持表級(jí)鎖，用戶在操作myisam表時(shí)，select，update，delete，insert語句都會(huì)給表自動(dòng)加鎖，如果加鎖以后的表滿足insert并發(fā)的情況下，可以在表的尾部插入新的數(shù)據(jù)。

　　InnoDB：支持行級(jí)鎖和事務(wù)，是innodb的最大特色。行鎖大幅度提高了多用戶并發(fā)操作的新能。但是InnoDB的行鎖，只是在有索引時(shí)是有效的，無索引或索引失效都會(huì)鎖全表的。

　　3. InnoDB支持外鍵，MyISAM不支持。

　　4. InnoDB的主鍵范圍更大，最大是MyISAM的2倍。

　　5. InnoDB不支持全文索引，而MyISAM支持。全文索引是指對(duì)char、varchar和text中的每個(gè)詞(停用詞除外)建立倒排序索引。MyISAM的全文索引其實(shí)沒啥用，因?yàn)樗恢С种形姆衷~，必須由使用者分詞后加入空格再寫到數(shù)據(jù)表里，而且少于4個(gè)漢字的詞會(huì)和停用詞一樣被忽略掉。

　　6. MyISAM支持GIS數(shù)據(jù)，InnoDB不支持。即MyISAM支持以下空間數(shù)據(jù)對(duì)象：Point,Line,Polygon,Surface等。

　　7. 沒有where的count(*)使用MyISAM要比InnoDB快得多。因?yàn)镸yISAM內(nèi)置了一個(gè)計(jì)數(shù)器，count(*)時(shí)它直接從計(jì)數(shù)器中讀，而InnoDB必須掃描全表。所以在InnoDB上執(zhí)行count(*)時(shí)一般要伴隨where，且where中要包含主鍵以外的索引列。為什么這里特別強(qiáng)調(diào)“主鍵以外”?因?yàn)镮nnoDB中primary index是和raw data存放在一起的，而secondary index則是單獨(dú)存放，然后有個(gè)指針指向primary key。所以只是count(*)的話使用secondary index掃描更快，而primary key則主要在掃描索引同時(shí)要返回raw data時(shí)的作用較大。

　　2.3存儲(chǔ)結(jié)構(gòu)

　　InnoDB 和 Myisam 都是用 B+Tree 來存儲(chǔ)數(shù)據(jù)的。

　　一般情況3層B+Tree可以存放上百萬條數(shù)據(jù)

　　3)解析過程

　　3.1sql編寫過程：

select dinstinct.. from.. join.. on.. where.. group by.. having.. order by.. limit..

　　3.2sql解析過程：

from.. on.. join.. where.. group by.. having.. select dinstinct..  order by.. limit..

　　4.索引

　　1)什么是索引

　　索引: 相當(dāng)于書的目錄，是幫助mysql高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)

　　2)索引類型

　　普通索引: index 命名: idx_字段名

　　唯一索引: unique 命名: uk_字段名

　　主鍵索引: primary key 命名: pk_字段名

　　復(fù)合索引: 多個(gè)字段組成的索引(name,age) 命名: 按索引順序字段名命名 idx_name_age

　　復(fù)合索引并不一定所有索引字段都會(huì)用到。當(dāng)name已經(jīng)查詢出結(jié)果是，不會(huì)再去查詢age索引

　　但為了性能考慮建議在sql語句中用到所有的索引字段

　　3)創(chuàng)建索引

方式一: create    索引類型   索引名 on  表(字段);
方式二: alter table 表名 add  constraint  索引名   索引類型(字段);

//簡單索引
mysql>  create index idx_dept on tb(dept);

//唯一索引
mysql>  create unique index uk_name on tb(name);

//復(fù)合索引
mysql>  create index idx_dept_name on tb(dept,name);

//主鍵索引
mysql>  alter table tb add constraint pk_id primary key(id);

　　
4)刪除索引

　　> drop index 索引名 on 表名;

mysql>  drop index uk_name on tb;
mysql>  drop index idx_dept on tb;
mysql>  drop index idx_dept_name on tb;

　　
5)查詢索引

　　> show index from 表名;

mysql> show index from tb;

　　
6)不適合創(chuàng)建索引的字段

　　索引本身需要的存儲(chǔ)空間很大的字段

　　頻繁需要修改的字段

　　很少使用的列 (sql語句中用不到的列)

　　重復(fù)值多的列

　　5.執(zhí)行計(jì)劃

　　1)數(shù)據(jù)準(zhǔn)備

mysql> create table tab_school_timetable(stid int(3),stname varchar(20),tid int(3));
mysql> create table tab_teacher(tid int(3),tname varchar(20));
mysql> create table tab_teacher_card(tcid int(3), tid int(3),tcdesc varchar(200));

mysql> create table tab_school_timetable(stid int(3),stname varchar(20),tid int(3));
mysql> create table tab_teacher(tid int(3),tname varchar(20));
mysql> create table tab_teacher_card(tcid int(3), tid int(3),tcdesc varchar(200));

　　2)語法

　　> explain sql語句;

　　3)explain信息詳解 id

　　> id：sql語句執(zhí)行的編號(hào)

　　> id值相同，從上往下順序執(zhí)行。這個(gè)順序受表數(shù)據(jù)量的大小影響，先查數(shù)據(jù)量小的，后查數(shù)據(jù)量大的

　　> id值不同：先執(zhí)行id值大的

#查詢課程編號(hào)為2 或 教師證編號(hào)為3 的老師信息
explain select t.tname from tab_teacher t, tab_school_timetable st, tab_teacher_card tc where t.tid = st.tid  and t.tid = tc.tcid and (st.stid = 2 or tc.tcid = 3);

#查詢教授sql課程老師的描述信息
explain select tc.tcdesc from tab_teacher_card tc where tc.tid = (select st.tid from tab_school_timetable st where st.stname = 'sql' )

　　4)explain信息詳解 select_type

　　select_type 查詢類型

　　PRIMARY: 主查詢，sql中包含有子查詢

　　SUBQUERY: 子查詢

　　SIMPLE：普通查詢 (不含有子查詢和union 連接查詢的查詢)

　　DERIVED: 衍生查詢 (使用到了臨時(shí)表)

　　union: 使用到了union 連接查詢

　　union result: 顯示拿些表之間使用了union

　　5)explain信息詳解 table

　　表名

　　6)explain信息詳解 type

　　> type 索引類型

　　> 常用到的類型system > const > eq_ref > ref > range > index > all

　　> system 性能最高，all性能最低。實(shí)際項(xiàng)目中達(dá)到 ref > range 性能就行

　　> system ：表只有中一條數(shù)據(jù)的主查詢

　　> const ：查詢結(jié)果只有一條數(shù)據(jù)的sql ,并且索引類型必須為主鍵索引或者唯一索引

　　> eq_ref ：查詢結(jié)果可以有多條數(shù)據(jù)，但滿足where判斷條件的每一條數(shù)據(jù)必須是唯一的一條數(shù)據(jù)(不能多條也不能為0條)。

explain
select t.tname from teacher t,teacherCard tc where t.tid = tc.tid;
 ``` 
ref :   索引查詢返回匹配所有行（0條，多條）

explain
select t.tname from tab_teacher t where t.tname = 'ta';

range  : 檢索指定范圍的行，where后面是一個(gè)范圍查詢（between ,  in , > , <  等   其中in可能會(huì)導(dǎo)致索引失效而變成 all）

explain select tc.tcdesc from tab_teacher_card tc where tc.tid between 1 and 2;
```
index :  查詢?nèi)克饕臄?shù)據(jù)

explain select t.tid from tab_teacher t;
```
all ：查詢?nèi)勘淼臄?shù)據(jù)(sql 將表的所有數(shù)據(jù)都查了一遍) ，沒有用到索引時(shí)常出現(xiàn)

explain select st.stname from tab_school_timetable st;

　　7)explain信息詳解 possible_keys

　　可供選擇的索引

　　8)explain信息詳解key

　　實(shí)際用到的索引

　　9)explain信息詳解key_len

　　實(shí)際使用到索引的長度(utf8 1個(gè)字符3個(gè)字節(jié))

　　10)explain信息詳解ref

　　表之間的引用-指明當(dāng)前表所參照的字段

　　const: 判斷條件中用到了常量或者顯示用到了其他表的那些字段

　　11)explain信息詳解rows

　　估計(jì)查詢了表中的數(shù)據(jù)行數(shù)(MySQL認(rèn)為必須檢查以執(zhí)行查詢的行數(shù))

　　12)explain信息詳解extra

　　> 準(zhǔn)備工作

create table t (
    a1 char(3),
    a2 char(3),
    a3 char(3),
    index idx_a1(a1),
    index idx_a2(a2),
    index idx_a3(a3)
);

　　
> using filesort : 性能損耗大，需要額外的查詢(排序) ，常見于 order by 語句中

#當(dāng)排序和查找不是同一個(gè)字段就會(huì)出現(xiàn)using filesort
explain select t.a1,t.a2,t.a3 from t where t.a1 = '' order by a2;  //反例
explain select t.a1,t.a2,t.a3 from t where t.a1 = '' order by a1;   //正例

#復(fù)合索引不能跨列（最佳左前綴）否則會(huì)出現(xiàn)using filesort
create index idx_a1_a2_a3 on t(a1,a2,a3);
explain select t.a1,t.a2,t.a3 from t where t.a1 = '' order by a3;  //反例(跨了 a2)
explain select t.a1,t.a2,t.a3 from t where t.a2 = '' order by a3;  //反例(跨了 a1)
explain select t.a1,t.a2,t.a3 from t where t.a1 = '' order by a2;  //正例

> using temporary:性能損耗大，用到了臨時(shí)表，一般出現(xiàn)與 group by 語句中

explain select t.a1,t.a2,t.a3 from t where a1 in ('1','2','3') group by a2;  //反例
explain select t.a1,t.a2,t.a3 from t where a1 in ('1','2','3') group by a1;  //正例

> using index: 性能提升，只從索引中查詢數(shù)據(jù)，不需要回表查詢

> using where: 進(jìn)行了回表查詢

drop index idx_a1_a2_a3 on t;
create index idx_a1_a2 on t(a1,a2);

explain select a1,a2 from t where a1='' and a2 = '';  //正例
explain select a1,a3 from t where a1='';  //反例

> impossible where : where 查詢條件永遠(yuǎn)為fasle

explain select a1 from t where a1='a' and a1 = 'b';

> Using join buffer : MySQL引擎使用了連接緩存，表示sql語句太爛，性能低下

　　6.慢查詢

　　1)慢查詢有什么用

　　開啟慢查詢后,可以根據(jù)日志信息分析那些SQL語句性能低下,從而針對(duì)性的進(jìn)行優(yōu)化

　　2)檢查是否開啟了慢查詢

mysql> show variables like '%slow_query_log%';

　　3)開啟慢查詢

#臨時(shí)開啟 - mysql重啟后失效
mysql> set global slow_query_log = 1;

#永久開啟 -  修改my.cnf 文件
vim /etc/my.cnf

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/localhost-slow.log

　　4)慢查詢閾值

#查看
mysql> show variables like '%long_query_time%';

#臨時(shí)設(shè)置(單位秒) - 重新登陸起效
mysql> set global long_query_time = 2;

#永久設(shè)置 - 修改my.cnf 文件
vim /etc/my.cnf

[mysqld]
long_query_time = 2

　　
5)查看超過慢查詢閾值的sql次數(shù)

mysql> show global status like '%slow_queries%';

　　
6)查看超過慢查詢閾值具體的sql信息

　　> 1.查看slow_query_log_file日志文件

　　> 2.使用mysqldumpslow工具

mysqldumpslow --help
-s : 排序方式  (r 逆序)
-l ：鎖定時(shí)間
-t ：查詢多少條
-g : 正則表達(dá)式

//獲取返回記錄最多的3個(gè)SQL
mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log

//獲取訪問次數(shù)最多的3個(gè)SQL
mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log

//按照時(shí)間排序，查詢前10條包含left join查詢語句的sql
mysqldumpslow -s t -t 10 -g 'left join' /var/lib/mysql/localhost-slow.log

　　
7.優(yōu)化總結(jié)

　　```ABAP

　　優(yōu)化一般不能一次就優(yōu)化到最佳效果，需要在開發(fā)過程中根據(jù)使用情況多次逐步優(yōu)化

　　```

　　> 1. 復(fù)合索引保證最佳左前綴原則

　　> 2. 小表驅(qū)動(dòng)大表

　　> 3. 索引建立在經(jīng)常查詢的字段上

　　> 4. 復(fù)合索引，盡量使用全索引匹配(說明:假設(shè)使用了三個(gè)字段建立了一個(gè)復(fù)合索引，在sql查詢中盡量讓三個(gè)索引都用到)

　　> 5. 不要在索引上進(jìn)行任何操作(計(jì)算、函數(shù)、類型轉(zhuǎn)換),否則索引失效

　　> 6. 復(fù)合索引不能使用不等于( != , <> )和 is null, is not null ,否則索引失效

　　> 7. like 盡量以 ‘常量’ 開頭，不要使用 ‘%x%’ ，否則索引失效

　　> 8. 盡量不要使用or ，否則索引失效