原文鏈接：https://blog.csdn.net/gs932899178/article/details/53827965

目前使用率最多的數據庫均為"關系型數據庫",例如:oracle,MySql…

1.關系型數據庫的瓶頸:

高并發讀寫需求-----------針對網站類用戶的并發性訪問非常高,而一臺數據庫的最大連接數有限,且硬盤I/O有限,其不能滿足很多人同時連接

海量數據的高效率讀寫-------當表中數據量太大,每次的讀寫速率都將非常緩慢(解決方案:分表.分庫)

高擴展性和可用性------一臺數據庫服務器擴充到多臺時,不下電情況很難做到,當服務器掛了,業務都將中斷

故而,需要對數據庫進行優化!

2.MySql數據庫的優化技術

第一步:定位慢查詢Sql

先關閉mysql服務—在服務中中止

開啟慢查詢并把慢查詢記錄到日志中: 在mysql的安裝目錄下執行命令: bin\mysqld.exe--safe-mode --slow- query-log

查看mysql安裝目錄下my.ini文件中datadir(ctrl+f)的路徑值所對應的目錄下是否有生成有生成當前時間下后綴為.log 的文件,若有,表示成功(新生成的三個文件不能刪除)

進行測試: 先設置慢查詢時間---->查看慢查詢時間—>執行需要測試sql語句—>查看是否有慢查詢語句(若有,可以在.log日志文件中查看),如圖:

注意:通過 show session/global status(找出) ,其中session是默認值,一般都用global

開發中,測試的時機為:自檢/測試功能的時候開啟慢查詢

慢查詢語句: .log中

第二步:分析該Sql效率地的原因

explain(通過 explain 語句可以分析，mysql如何執行你的sql語句)分析,如下圖:

第三步:結構設計優化

一.表機構設計

1.范式 3范式規范與反3NF設計,具體視情況而定

備注:三范式指:1.表的列具有原子性,不可再分解;2.建立主鍵,實現記錄唯一性;3.不能出現冗余數據,特殊情況可建立外鍵

2.選擇合適的存儲引擎(myisam+INNODB+memory)

當表對事務要求不高,主要是以查詢和添加為主的時候,一般用myisam,實例:bbs中的發帖表.回復表

反之對事務要求高,保存的數據都是重要數據的時候,則使用INNODB,例如:訂單表.賬號表

若數據變化頻繁,不需要入庫,同時又頻繁的查詢和修改,一般用memory,速度極快

備注:MyISAM和INNODB的區別:

1.MyISAM不支持事務,INNODB支持

2.MyISAM查詢和添加速度較快(底層類似鏈表,可以采用二叉樹查詢)

3.MyISAM支持全文索引,INNODB不支持

4.鎖機制不同,MyISAM是表鎖,INNODB是行鎖

5.MyISAM不支持外鍵,INNODB支持

設置/修改存儲引擎語法:

Create table 表名(字段列表) engine存儲引擎名稱;

例如:Create table employee(id int ,name varchar) engine myisam;

二.索引:索引（Index）是幫助DBMS高效獲取數據的數據結構。

1.索引算法:

備注: 1.btree代表二叉樹算法
2.hash就像Map,通過一個key直接就能找到value
3.FullText全文索引算法，只有myisam中有用,且只能對表中的文本區域(char vachar text)進行索引
總結：使用索引把全表查找變為索引查找，減少查詢次數，增加效率。而索引查找效率的取決于索引算法。也就是索引（Index）是幫助DBMS高效獲取數據的數據結構
2.索引的—添加/修改/刪除/查詢

1):mysql中索引的分類:

普通索引：允許重復的值出現，可以再任何字段上面添加

唯一索引：除了不能有重復的記錄外，其它和普通索引一樣，可以在值是唯一的字段添加（用戶名、手機號碼、身份證、email，QQ），可以為null,并且可以有多個null

主鍵索引：是隨著設定主鍵而創建的，也就是把某個列設為主鍵的時候，數據庫就會給改列創建索引。這就是主鍵索引.唯一且沒有null值

全文索引：用來對表中的文本域(char，varchar，text)進行索引，全文索引針對MyISAM有用

2):添加索引:

普通索引的創建，是先創建表，然后在創建普通索引.

唯一索引:除了不能有重復的記錄外，其它和普通索引一樣

主鍵索引：是隨著設定主鍵而創建的，也就是把某個列設為主鍵的時候，數據庫就會給改列創建索引。這就是主鍵索引.唯一且沒有null值

全文索引:用來對表中的文本域(char，varchar，text)進行索引，全文索引針對MyISAM有用,如下圖

//創建表,同時設置fulltext字段----------------------------全文檢索案例
CREATE TABLE articles2 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
fulltext(title,body)
)engine=myisam charset utf8;
//添加數據
INSERT INTO articles2 (title,body) VALUES
(‘MySQL Tutorial’,‘DBMS stands for DataBase …’),
(‘How To Use MySQL Well’,‘After you went through a …’),
(‘Optimizing MySQL’,‘In this tutorial we will show …’),
(‘1001 MySQL Tricks’,‘1. Never run mysqld as root. 2. …’),
(‘MySQL vs. YourSQL’,‘In the following database comparison …’),
(‘MySQL Security’,‘When configured properly, MySQL …’);

EXPLAIN SELECT * FROM articles2 WHERE title LIKE ‘%MySql%’;//模糊查詢—不支持全文檢索

explain select * from articles2 where match(title,body) against(‘database’);//正確方式–能支持全文檢索
3):查詢
語法:show INDEX from 表名;

4):刪除
語法:ALTER TABLE 表名 drop INDEX 索引名;//刪除索引

5):修改

流程:先刪除后添加=修改

3:索引的代價是:

1).占用磁盤空間

2):對dml(刪除/添加/修改)操作有影響,因為要維護索引,效率變低.

4.使用場景:較為頻繁的作為查詢條件的字段

a.肯定在where條件經常使用

b: 該字段的內容不是唯一的幾個值(sex)

c: 字段內容不是頻繁變化.

5):總結:普通索引（單列索引）：該索引只在一個列上面創建

復合索引（多列索引）：該索引只在多個列上面創建

6):使用小技巧:

a:對于創建的多列索引（復合索引），不是使用的第一部分就不會使用索引。

b:使用like的適合,若%在前面(’%aaa’)不會使用索引,反之(‘aa%’),則會使用索引

c:使用or的適合,必須所有的條件都使用索引此sql才會使用索引

d:如果列類型是字符串，那一定要在條件中將數據使用引號引用起來。否則不使用索引

e:如果mysql估計使用全表掃描要比使用索引快，則不使用索引。

三.分表 :分為 水平分割(行)和垂直分割（列）

1.水平分表

表中數據量巨大時，我們要經常查詢。則可以按照合適的策略拆分為多張小表。盡量在單張表中查詢，減少掃描次數

2.垂直分表

表記錄數并不多，但是字段卻很長，表占用空間很大，檢索表時需要執行大量I/O，嚴重降低了性能。這個時候需要把大的字段拆分到另一個表，并且該表與原表是一對一的關系（外鍵）。 (JOIN)

四.分區:將數據分段劃分在多個位置存放，可以是同一塊磁盤也可以在不同的機器

MySQL 5.1 中新增了分區(Partition)功能，優勢也越來越明顯了：

–與單個磁盤或文件系統分區相比，可以存儲更多的數據

–很容易刪除不用或者過時的數據

一些查詢可以得到極大的優化可以并發查詢

–涉及到 SUM()/COUNT()等聚合函數時，可以并發進行

–IO吞吐量更大
五.sql優化小技巧(批量處理)

DDL(數據庫定義語言):使用場景,一次性插入上百萬條數據
1.通過禁用索引來提供導入數據性能 。 這個操作主要針對有數據庫的表，追加數據

//去除鍵 alter table test3 DISABLEkeys;

//批量插入數據 insert into test3 select * from test;

//恢復鍵 alter table test3 ENABLEkeys;

變多次維護索引為一次維護。

2 關閉唯一校驗

set unique_checks=0 關閉

set unique_checks=1 開啟

變多次唯一性校驗為一次校驗。

3.修改事務提交方式(導入)

set autocommit=0 關閉自動提交

//批量插入

set autocommit=1 開啟

變多次數據庫事務為一次提交
DML(增加/修改/刪除)

1.將多條增/刪/改優化成一條sql

六.集群:主從同步(日志),讀寫分離,主備切換

實現步驟一:主從復制
一、準備主從mysql

1、分別構造主、從數據庫并輸出日志（方便定位問題）

拷貝改端口拷貝數據

拷貝原理數據庫的data.dir mysql到模擬的主從數據庫

配置日志路徑2、安裝及啟動：mysqld --install MySQLXY --defaults-file="C:\ProgramFiles\MySQL\MySQL Server X.Y\my.ini"net start MySQLXYsc delete master

測試

二、master服務器配置：

1、修改master方的mysql.ini :log-bin=mysql-binserver-id=1innodb_flush_log_at_trx_commit=1sync_binlog=1binlog_ignore_db=mysqlbinlog_checksum=none2、重啟master服務，登錄

3、授權savle服務器的使用的賬號及權限

場景：master主服務器： 192.168.1.101slave從服務器 : 192.168.1.1021) 授權給slave數據庫服務器192.168.10.131（master用戶，只對slave服務器開放）語法為：GRANT REPLICATION SLAVE ON *.* to '用戶名'@'192.168.0.102'identifiedby ‘密碼’;Mysql> GRANT REPLICATION SLAVE ON *.* to'yhptest'@'192.168.77.128'identifiedby 'admin';參數說明：yhptest：slave連接master使用的賬號IDENTIFIED BY 'admin' ：slave連接master使用的密碼192.168.77.128：slave IP2)查詢主數據庫狀態Mysql> show master status;+--------------------+----------+---------------+------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+--------------------+----------+---------------+------------------------------+| mysql-bin.000002 | 226 | | mysql |+--------------------+----------+---------------+------------------------------+記錄File 和 Position的值，在slave端使用

三、slave服務器配置

1、修改slave服務器的配置文件my.ini將 server-id = 1修改為 server-id = 10，并確保這個ID沒有被別的MySQL服務所使用。2、啟動slave服務器，登錄3、在slave端，配置master鏈接信息 (執行語句)1） 配置

Mysql> change master to

master_host='192.168.77.1', #master IPmaster_user='yhptest', #master數據庫通過GRANT授權的賬號master_password='admin', #master數據庫通過GRANT授權的密碼master_port=3307, #master數據庫的密碼master_log_file='mysql-bin.000001',#master數據庫中通過show master status顯示的File名稱master_log_pos=296 #master數據庫的通過show master status顯示的Position的值2） （重啟）連接Mysql> start slave;3）主從同步檢查show slave status；其中Slave_IO_Running 與Slave_SQL_Running 的值都必須為YES，才表明狀態正常。4、 測試1） 在master上，建庫、建表、添加數據2） 刷新slave庫，記錄也存在

由此，整個MySQL主從復制的過程就完成了，接下來，我們進行MySQL讀寫分離的安裝與配置

如果主服務器已經存在應用數據，則在進行主從復制時，需要做以下處理：

(1)主數據庫進行鎖表操作，不讓數據再進行寫入動作mysql> FLUSH TABLES WITH READLOCK;(2)查看主數據庫狀態mysql> show master status;(3)復制數據文件將主服務器的數據文件（整個/opt/mysql/data目錄）復制到從服務器，建議通過tar歸檔壓 縮后再傳到從服務器解壓。(4)取消主數據庫鎖定mysql> UNLOCK TABLES;

步驟二------------------讀寫分離

使用MySQL Proxy實現讀寫分離

在此使用配置文件的方式來進行配置。

配置文件mysql-proxy.conf中的內容主要包括：

[mysql-proxy]

admin-username=root

admin-password=123456

admin-lua-script=C:/mysql-proxy/lib/mysql-proxy/lua/admin.lua

proxy-backend-addresses=192.168.1.101:3306

proxy-read-only-backend-addresses=192.168.1.102:3306

proxy-lua-script=C:/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

log-file=C:/mysql-proxy/log/mysql-proxy.log

log-level=debug

daemon=true

keepalive=true

執行命令：

mysql-proxy -P 192.168.5.100:4040–defaults-file=E:/mysql-proxy/bin/mysql-proxy.conf

查看日志文件mysql-proxy.log：

2015-10-19 16:27:40: (critical)plugin proxy 0.8.5 started

2015-10-19 16:27:40: (debug) maxopen file-descriptors = 512

2015-10-19 16:27:40: (message)proxy listening on port 192.168.174.133:4040

2015-10-19 16:27:40: (message)added read/write backend: 192.168.174.130:3306

2015-10-19 16:27:40: (message)added read-only backend: 192.168.174.131:3306

出現以上日志信息則表示MySQL Proxy啟動成功，此時便可以實現讀寫分離了。

注意：由于rw-splitting.lua中的min_idle_connections的默認值為4，即當會話數達到最小為4時，才會進行讀寫分離，在此我們將其改為1，則可直接進行讀寫分離。

后端主機需要創建授權帳號

mysql> GRANT ALL ON . TOmike@‘192.168.%.%’ IDENTIFIED BY ‘321’;

mysql> FLUSH PRIVILEGES;
七.MySql使用小技巧

1.修改密碼

1)、停掉mysql服務

2)、在mysql.ini下，添加“skip-grant-tables”配置（mysqld這樣可以去掉登錄時的密碼驗證）

3)、重啟mysql

4)、在cmd下輸入mysql -uroot -P3306 ，無密碼登錄

5)、use mysql;

6)、set password=password（‘123’） --error

update user setpassword=password('admin') where user='root' and host='localhost';

7)、重啟，或直接flushprivileges;

2.隨機獲取行(rand函數)—可用于抽獎

order by rand() limit 1//隨機獲取一行

八.分布式: 按業務分要訪問那個集群.

比如:dao層中的訂單相關業務就訪問訂單DB集群

商品相關業務就訪問商品DB集群

九.緩存…略…見Redis

作者：追夢碼仔
來源：CSDN
原文：https://blog.csdn.net/gs932899178/article/details/53827965
版權聲明：本文為博主原創文章，轉載請附上博文鏈接！

總結

以上是生活随笔為你收集整理的【数据库】关系型数据库优化操作的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。