mysql主键自增长_全面的MySQL优化面试解析
本文概要
文章內圖片有損，需要高清可以在公眾號內回復“大圖”
概述
為什么要優化
- 系統的吞吐量瓶頸往往出現在數據庫的訪問速度上
- 隨著應用程序的運行，數據庫的中的數據會越來越多，處理時間會相應變慢
- 數據是存放在磁盤上的，讀寫速度無法和內存相比
如何優化
- 設計數據庫時：數據庫表、字段的設計，存儲引擎
- 利用好MySQL自身提供的功能，如索引等
- 橫向擴展：MySQL集群、負載均衡、讀寫分離
- SQL語句的優化(收效甚微)
字段設計
字段類型的選擇，設計規范，范式，常見設計案例
原則：盡量使用整型表示字符串
存儲IP
INET_ATON(str)，address to number
INET_NTOA(number)，number to address
MySQL內部的枚舉類型(單選)和集合(多選)類型
但是因為維護成本較高因此不常使用，使用關聯表的方式來替代enum
原則：定長和非定長數據類型的選擇
decimal不會損失精度，存儲空間會隨數據的增大而增大。double占用固定空間，較大數的存儲會損失精度。非定長的還有varchar、text
金額
對數據的精度要求較高，小數的運算和存儲存在精度問題(不能將所有小數轉換成二進制)
定點數decimal
price decimal(8,2)有2位小數的定點數，定點數支持很大的數(甚至是超過int,bigint存儲范圍的數)
小單位大數額避免出現小數
元->分
字符串存儲
定長char，非定長varchar、text(上限65535，其中varchar還會消耗1-3字節記錄長度，而text使用額外空間記錄長度)
原則：盡可能選擇小的數據類型和指定短的長度
原則：盡可能使用 not null
非null字段的處理要比null字段的處理高效些！且不需要判斷是否為null。
null在MySQL中，不好處理，存儲需要額外空間，運算也需要特殊的運算符。如select null = null和select null <> null(<>為不等號)有著同樣的結果，只能通過is null和is not null來判斷字段是否為null。
如何存儲？MySQL中每條記錄都需要額外的存儲空間，表示每個字段是否為null。因此通常使用特殊的數據進行占位，比如int not null default 0、string not null default ‘’
原則：字段注釋要完整，見名知意
原則：單表字段不宜過多
二三十個就極限了
原則：可以預留字段
在使用以上原則之前首先要滿足業務需求
關聯表的設計
外鍵foreign key只能實現一對一或一對多的映射
一對多
使用外鍵
多對多
單獨新建一張表將多對多拆分成兩個一對多
一對一
如商品的基本信息(item)和商品的詳細信息(item_intro)，通常使用相同的主鍵或者增加一個外鍵字段(item_id)
范式 Normal Format
數據表的設計規范，一套越來越嚴格的規范體系(如果需要滿足N范式，首先要滿足N-1范式)。N
第一范式1NF：字段原子性
字段原子性，字段不可再分割。
關系型數據庫，默認滿足第一范式
注意比較容易出錯的一點，在一對多的設計中使用逗號分隔多個外鍵，這種方法雖然存儲方便，但不利于維護和索引(比如查找帶標簽java的文章)
第二范式：消除對主鍵的部分依賴
即在表中加上一個與業務邏輯無關的字段作為主鍵
主鍵：可以唯一標識記錄的字段或者字段集合。
依賴：A字段可以確定B字段，則B字段依賴A字段。比如知道了下一節課是數學課，就能確定任課老師是誰。于是周幾和下一節課和就能構成復合主鍵，能夠確定去哪個教室上課，任課老師是誰等。但我們常常增加一個id作為主鍵，而消除對主鍵的部分依賴。
對主鍵的部分依賴：某個字段依賴復合主鍵中的一部分。
解決方案：新增一個獨立字段作為主鍵。
第三范式：消除對主鍵的傳遞依賴
傳遞依賴：B字段依賴于A，C字段又依賴于B。比如上例中，任課老師是誰取決于是什么課，是什么課又取決于主鍵id。因此需要將此表拆分為兩張表日程表和課程表(獨立數據獨立建表)：
這樣就減少了數據的冗余(即使周一至周日每天都有Java課，也只是course_id:3546出現了7次)
存儲引擎選擇
早期問題：如何選擇MyISAM和Innodb？
現在不存在這個問題了，Innodb不斷完善，從各個方面趕超MyISAM，也是MySQL默認使用的。
存儲引擎Storage engine：MySQL中的數據、索引以及其他對象是如何存儲的，是一套文件系統的實現。
功能差異
show engines
存儲差異
鎖擴展
表級鎖(table-level lock)：lock tables ,... read/write，unlock tables ,...。其中read是共享鎖，一旦鎖定任何客戶端都不可讀；write是獨占/寫鎖，只有加鎖的客戶端可讀可寫，其他客戶端既不可讀也不可寫。鎖定的是一張表或幾張表。
行級鎖(row-level lock)：鎖定的是一行或幾行記錄。共享鎖：select * from where LOCK IN SHARE MODE;，對查詢的記錄增加共享鎖；select * from where FOR UPDATE;，對查詢的記錄增加排他鎖。
這里值得注意的是：innodb的行鎖，其實是一個子范圍鎖，依據條件鎖定部分范圍，而不是就映射到具體的行上，因此還有一個學名：間隙鎖。比如select * from stu where id < 20 LOCK IN SHARE MODE會鎖定id在20左右以下的范圍，你可能無法插入id為18或22的一條新紀錄。
選擇依據
如果沒有特別的需求，使用默認的Innodb即可。
MyISAM：以讀寫插入為主的應用程序，比如博客系統、新聞門戶網站。
Innodb：更新(刪除)操作頻率也高，或者要保證數據的完整性；并發量高，支持事務和外鍵保證數據完整性。比如OA自動化辦公系統。
索引
關鍵字與數據的映射關系稱為索引(包含關鍵字和對應的記錄在磁盤中的地址)。關鍵字是從數據當中提取的用于標識、檢索數據的特定內容。
索引檢索為什么快？
- 關鍵字相對于數據本身，數據量小
- 關鍵字是有序的，二分查找可快速確定位置
圖書館為每本書都加了索引號(類別-樓層-書架)、字典為詞語解釋按字母順序編寫目錄等都用到了索引。
MySQL中索引類型
普通索引(key)，唯一索引(unique key)，主鍵索引(primary key)，全文索引(fulltext key)
三種索引的索引方式是一樣的，只不過對索引的關鍵字有不同的限制：
- 普通索引：對關鍵字沒有限制
- 唯一索引：要求記錄提供的關鍵字不能重復
- 主鍵索引：要求關鍵字唯一且不為null
索引管理語法
查看索引
show create table 表名：
desc 表名
創建索引
創建表之后建立索引
create TABLE user_index( id int auto_increment primary key, first_name varchar(16), last_name VARCHAR(16), id_card VARCHAR(18), information text);-- 更改表結構alter table user_index-- 創建一個first_name和last_name的復合索引，并命名為nameadd key name (first_name,last_name),-- 創建一個id_card的唯一索引，默認以字段名作為索引名add UNIQUE KEY (id_card),-- 雞肋，全文索引不支持中文add FULLTEXT KEY (information);show create table user_index：
創建表時指定索引
CREATE TABLE user_index2 ( id INT auto_increment PRIMARY KEY, first_name VARCHAR (16), last_name VARCHAR (16), id_card VARCHAR (18), information text, KEY name (first_name, last_name), FULLTEXT KEY (information), UNIQUE KEY (id_card));刪除索引
根據索引名刪除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名
alter table user_index drop KEY name;alter table user_index drop KEY id_card;alter table user_index drop KEY information;刪除主鍵索引：alter table 表名 drop primary key(因為主鍵只有一個)。這里值得注意的是，如果主鍵自增長，那么不能直接執行此操作(自增長依賴于主鍵索引)：
需要取消自增長再行刪除：
alter table user_index-- 重新定義字段MODIFY id int,drop PRIMARY KEY但通常不會刪除主鍵，因為設計主鍵一定與業務邏輯無關。
執行計劃explain
CREATE TABLE innodb1 ( id INT auto_increment PRIMARY KEY, first_name VARCHAR (16), last_name VARCHAR (16), id_card VARCHAR (18), information text, KEY name (first_name, last_name), FULLTEXT KEY (information), UNIQUE KEY (id_card));insert into innodb1 (first_name,last_name,id_card,information) values ('張','三','1001','華山派');我們可以通過explain selelct來分析SQL語句執行前的執行計劃：
由上圖可看出此SQL語句是按照主鍵索引來檢索的。
執行計劃是：當執行SQL語句時，首先會分析、優化，形成執行計劃，在按照執行計劃執行。
索引使用場景(重點)
where
上圖中，根據id查詢記錄，因為id字段僅建立了主鍵索引，因此此SQL執行可選的索引只有主鍵索引，如果有多個，最終會選一個較優的作為檢索的依據。
-- 增加一個沒有建立索引的字段alter table innodb1 add sex char(1);-- 按sex檢索時可選的索引為nullEXPLAIN SELECT * from innodb1 where sex='男';可以嘗試在一個字段未建立索引時，根據該字段查詢的效率，然后對該字段建立索引(alter table 表名 add index(字段名))，同樣的SQL執行的效率，你會發現查詢效率會有明顯的提升(數據量越大越明顯)。
order by
當我們使用order by將查詢結果按照某個字段排序時，如果該字段沒有建立索引，那么執行計劃會將查詢出的所有數據使用外部排序(將數據從硬盤分批讀取到內存使用內部排序，最后合并排序結果)，這個操作是很影響性能的，因為需要將查詢涉及到的所有數據從磁盤中讀到內存(如果單條數據過大或者數據量過多都會降低效率)，更無論讀到內存之后的排序了。
但是如果我們對該字段建立索引alter table 表名 add index(字段名)，那么由于索引本身是有序的，因此直接按照索引的順序和映射關系逐條取出數據即可。而且如果分頁的，那么只用取出索引表某個范圍內的索引對應的數據，而不用像上述那取出所有數據進行排序再返回某個范圍內的數據。(從磁盤取數據是最影響性能的)
join
對join語句匹配關系(on)涉及的字段建立索引能夠提高效率
索引覆蓋
如果要查詢的字段都建立過索引，那么引擎會直接在索引表中查詢而不會訪問原始數據(否則只要有一個字段沒有建立索引就會做全表掃描)，這叫索引覆蓋。因此我們需要盡可能的在select后只寫必要的查詢字段，以增加索引覆蓋的幾率。
這里值得注意的是不要想著為每個字段建立索引，因為優先使用索引的優勢就在于其體積小。
語法細節(要點)
在滿足索引使用的場景下(where/order by/join on或索引覆蓋)，索引也不一定被使用
字段要獨立出現
比如下面兩條SQL語句在語義上相同，但是第一條會使用主鍵索引而第二條不會。
select * from user where id = 20-1;select * from user where id+1 = 20;`like`查詢，不能以通配符開頭
比如搜索標題包含mysql的文章：
select * from article where title like '%mysql%';這種SQL的執行計劃用不了索引(like語句匹配表達式以通配符開頭)，因此只能做全表掃描，效率極低，在實際工程中幾乎不被采用。而一般會使用第三方提供的支持中文的全文索引來做。
但是 關鍵字查詢 熱搜提醒功能還是可以做的，比如鍵入mysql之后提醒mysql 教程、mysql 下載、mysql 安裝步驟等。用到的語句是：
select * from article where title like 'mysql%';這種like是可以利用索引的(當然前提是title字段建立過索引)。
復合索引只對第一個字段有效
建立復合索引：
alter table person add index(first_name,last_name);其原理就是將索引先按照從first_name中提取的關鍵字排序，如果無法確定先后再按照從last_name提取的關鍵字排序，也就是說該索引表只是按照記錄的first_name字段值有序。
因此select * from person where first_name = ?是可以利用索引的，而select * from person where last_name = ?無法利用索引。
那么該復合索引的應用場景是什么？組合查詢
比如對于select * person from first_name = ? and last_name = ?，復合索引就比對first_name和last_name單獨建立索引要高效些。很好理解，復合索引首先二分查找與first_name = ?匹配的記錄，再在這些記錄中二分查找與last_name匹配的記錄，只涉及到一張索引表。
而分別單獨建立索引則是在first_name索引表中二分找出與first_name = ?匹配的記錄，再在last_name索引表中二分找出與last_name = ?的記錄，兩者取交集。擴展：帶你徹底了解數據庫索引
or，兩邊條件都有索引可用
一但有一邊無索引可用就會導致整個SQL語句的全表掃描
狀態值，不容易使用到索引
如性別、支付狀態等狀態值字段往往只有極少的幾種取值可能，這種字段即使建立索引，也往往利用不上。這是因為，一個狀態值可能匹配大量的記錄，這種情況MySQL會認為利用索引比全表掃描的效率低，從而棄用索引。
索引是隨機訪問磁盤，而全表掃描是順序訪問磁盤，這就好比有一棟20層樓的寫字樓，樓底下的索引牌上寫著某個公司對應不相鄰的幾層樓，你去公司找人，與其按照索引牌的提示去其中一層樓沒找到再下來看索引牌再上樓，不如從1樓挨個往上找到頂樓。
如何創建索引
- 建立基礎索引：在where、order by、join字段上建立索引。
- 優化，組合索引：基于業務邏輯
- 如果條件經常性出現在一起，那么可以考慮將多字段索引升級為復合索引
- 如果通過增加個別字段的索引，就可以出現索引覆蓋，那么可以考慮為該字段建立索引
- 查詢時，不常用到的索引，應該刪除掉
前綴索引
語法：index(field(10))，使用字段值的前10個字符建立索引，默認是使用字段的全部內容建立索引。
前提：前綴的標識度高。比如密碼就適合建立前綴索引，因為密碼幾乎各不相同。
實操的難度：在于前綴截取的長度。
我們可以利用select count(*)/count(distinct left(password,prefixLen));，通過從調整prefixLen的值(從1自增)查看不同前綴長度的一個平均匹配度，接近1時就可以了(表示一個密碼的前prefixLen個字符幾乎能確定唯一一條記錄)
索引的存儲結構
BTree
btree(多路平衡查找樹)是一種廣泛應用于磁盤上實現索引功能的一種數據結構，也是大多數數據庫索引表的實現。
以add index(first_name,last_name)為例：
BTree的一個node可以存儲多個關鍵字，node的大小取決于計算機的文件系統，因此我們可以通過減小索引字段的長度使結點存儲更多的關鍵字。如果node中的關鍵字已滿，那么可以通過每個關鍵字之間的子節點指針來拓展索引表，但是不能破壞結構的有序性，比如按照first_name第一有序、last_name第二有序的規則，新添加的韓香就可以插到韓康之后。白起 < 韓飛 < 韓康 < 李世民 < 趙奢 < 李尋歡 < 王語嫣 < 楊不悔。
這與二叉搜索樹的思想是一樣的，只不過二叉搜索樹的查找效率是log(2,N)(以2為底N的對數)，而BTree的查找效率是log(x,N)(其中x為node的關鍵字數量，可以達到1000以上)。
從log(1000+,N)可以看出，少量的磁盤讀取即可做到大量數據的遍歷，這也是btree的設計目的。
B+Tree聚簇結構
聚簇結構(也是在BTree上升級改造的)中，關鍵字和記錄是存放在一起的。
在MySQL中，僅僅只有Innodb的主鍵索引為聚簇結構，其它的索引包括Innodb的非主鍵索引都是典型的BTree結構。
哈希索引
在索引被載入內存時，使用哈希結構來存儲。
查詢緩存
緩存select語句的查詢結果
在配置文件中開啟緩存
windows上是my.ini，linux上是my.cnf
在[mysqld]段中配置query_cache_type：
- 0：不開啟
- 1：開啟，默認緩存所有，需要在SQL語句中增加select sql-no-cache提示來放棄緩存
- 2：開啟，默認都不緩存，需要在SQL語句中增加select sql-cache來主動緩存(常用)
更改配置后需要重啟以使配置生效，重啟后可通過show variables like ‘query_cache_type’;來查看：
show variables like 'query_cache_type';query_cache_type DEMAND在客戶端設置緩存大小
通過配置項query_cache_size來設置：
show variables like 'query_cache_size';query_cache_size 0set global query_cache_size=64*1024*1024;show variables like 'query_cache_size';query_cache_size 67108864將查詢結果緩存
select sql_cache * from user;
重置緩存
reset query cache;
緩存失效問題(大問題)
當數據表改動時，基于該數據表的任何緩存都會被刪除。(表層面的管理，不是記錄層面的管理，因此失效率較高)
注意事項
分區
一般情況下我們創建的表對應一組存儲文件，使用MyISAM存儲引擎時是一個.MYI和.MYD文件，使用Innodb存儲引擎時是一個.ibd和.frm(表結構)文件。
當數據量較大時(一般千萬條記錄級別以上)，MySQL的性能就會開始下降，這時我們就需要將數據分散到多組存儲文件，保證其單個文件的執行效率。
最常見的分區方案是按id分區，如下將id的哈希值對10取模將數據均勻分散到10個.ibd存儲文件中：
create table article( id int auto_increment PRIMARY KEY, title varchar(64), content text)PARTITION by HASH(id) PARTITIONS 10查看data目錄：
服務端的表分區對于客戶端是透明的，客戶端還是照常插入數據，但服務端會按照分區算法分散存儲數據。
MySQL提供的分區算法
分區依據的字段必須是主鍵的一部分，分區是為了快速定位數據，因此該字段的搜索頻次較高應作為強檢索字段，否則依照該字段分區毫無意義
hash(field)
相同的輸入得到相同的輸出。輸出的結果跟輸入是否具有規律無關。僅適用于整型字段
key(field)
和hash(field)的性質一樣，只不過key是處理字符串的，比hash()多了一步從字符串中計算出一個整型在做取模操作。
create table article_key( id int auto_increment, title varchar(64), content text, PRIMARY KEY (id,title) -- 要求分區依據字段必須是主鍵的一部分)PARTITION by KEY(title) PARTITIONS 10range算法
是一種條件分區算法，按照數據大小范圍分區(將數據使用某種條件，分散到不同的分區中)。
如下，按文章的發布時間將數據按照2018年8月、9月、10月分區存放：
create table article_range( id int auto_increment, title varchar(64), content text, created_time int, -- 發布時間到1970-1-1的毫秒數 PRIMARY KEY (id,created_time) -- 要求分區依據字段必須是主鍵的一部分)charset=utf8PARTITION BY RANGE(created_time)( PARTITION p201808 VALUES less than (1535731199), -- select UNIX_TIMESTAMP('2018-8-31 23:59:59') PARTITION p201809 VALUES less than (1538323199), -- 2018-9-30 23:59:59 PARTITION p201810 VALUES less than (1541001599) -- 2018-10-31 23:59:59);注意：條件運算符只能使用less than，這以為著較小的范圍要放在前面，比如上述p201808,p201819,p201810分區的定義順序依照created_time數值范圍從小到大，不能顛倒。
insert into article_range values(null,'MySQL優化','內容示例',1535731180);flush tables; -- 使操作立即刷新到磁盤文件由于插入的文章的發布時間1535731180小于1535731199(2018-8-31 23:59:59)，因此被存儲到p201808分區中，這種算法的存儲到哪個分區取決于數據狀況。
list算法
也是一種條件分區，按照列表值分區(in (值列表))。
create table article_list( id int auto_increment, title varchar(64), content text, status TINYINT(1), -- 文章狀態：0-草稿，1-完成但未發布，2-已發布 PRIMARY KEY (id,status) -- 要求分區依據字段必須是主鍵的一部分)charset=utf8PARTITION BY list(status)( PARTITION writing values in(0,1), -- 未發布的放在一個分區 PARTITION published values in (2) -- 已發布的放在一個分區);insert into article_list values(null,'mysql優化','內容示例',0);flush tables;分區管理語法
range/list
增加分區
前文中我們嘗試使用range對文章按照月份歸檔，隨著時間的增加，我們需要增加一個月份：
alter table article_range add partition( partition p201811 values less than (1543593599) -- select UNIX_TIMESTAMP('2018-11-30 23:59:59') -- more);刪除分區
alter table article_range drop PARTITION p201808注意：刪除分區后，分區中原有的數據也會隨之刪除！
key/hash
新增分區
alter table article_key add partition partitions 4銷毀分區
alter table article_key coalesce partition 6key/hash分區的管理不會刪除數據，但是每一次調整(新增或銷毀分區)都會將所有的數據重寫分配到新的分區上。效率極低，最好在設計階段就考慮好分區策略。
分區的使用
當數據表中的數據量很大時，分區帶來的效率提升才會顯現出來。
只有檢索字段為分區字段時，分區帶來的效率提升才會比較明顯。因此，分區字段的選擇很重要，并且業務邏輯要盡可能地根據分區字段做相應調整(盡量使用分區字段作為查詢條件)。
水平分割和垂直分割
水平分割：通過建立結構相同的幾張表分別存儲數據
垂直分割：將經常一起使用的字段放在一個單獨的表中，分割后的表記錄之間是一一對應關系。
分表原因
- 為數據庫減壓
- 分區算法局限
- 數據庫支持不完善(5.1之后mysql才支持分區操作)
id重復的解決方案
- 借用第三方應用如memcache、redis的id自增器
- 單獨建一張只包含id一個字段的表，每次自增該字段作為數據記錄的id
集群
橫向擴展：從根本上(單機的硬件處理能力有限)提升數據庫性能 。由此而生的相關技術：讀寫分離、負載均衡
安裝和配置主從復制
環境
- Red Hat Enterprise Linux Server release 7.0 (Maipo)(虛擬機)
- mysql5.7
安裝和配置
解壓到對外提供的服務的目錄(我自己專門創建了一個/export/server來存放)
tar xzvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz -C /export/servercd /export/servermv mysql-5.7.23-linux-glibc2.12-x86_64 mysql添加mysql目錄的所屬組和所屬者：
groupadd mysqluseradd -r -g mysql mysqlcd /export/serverchown -R mysql:mysql mysql/chmod -R 755 mysql/創建mysql數據存放目錄(其中/export/data是我創建專門用來為各種服務存放數據的目錄)
mkdir /export/data/mysql初始化mysql服務
cd /export/server/mysql./bin/mysqld --basedir=/export/server/mysql --datadir=/export/data/mysql --user=mysql --pid-file=/export/data/mysql/mysql.pid --initialize如果成功會顯示mysql的root賬戶的初始密碼，記下來以備后續登錄。如果報錯缺少依賴，則使用yum instally依次安裝即可
配置my.cnf
vim /etc/my.cnf[mysqld]basedir=/export/server/mysqldatadir=/export/data/mysqlsocket=/tmp/mysql.sockuser=mysqlserver-id=10 # 服務id，在集群時必須唯一，建議設置為IP的第四段port=3306# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group,# customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemd[mysqld_safe]log-error=/export/data/mysql/error.logpid-file=/export/data/mysql/mysql.pid## include all files from the config directory#!includedir /etc/my.cnf.d將服務添加到開機自動啟動
cp /export/server/mysql/support-files/mysql.server /etc/init.d/mysqld啟動服務
service mysqld start配置環境變量，在/etc/profile中添加如下內容
# mysql envMYSQL_HOME=/export/server/mysqlMYSQL_PATH=$MYSQL_HOME/binPATH=$PATH:$MYSQL_PATHexport PATH使配置即可生效
source /etc/profile使用root登錄
mysql -uroot -p# 這里填寫之前初始化服務時提供的密碼登錄上去之后，更改root賬戶密碼(我為了方便將密碼改為root)，否則操作數據庫會報錯
set password=password('root');flush privileges;設置服務可被所有遠程客戶端訪問
use mysql;update user set host='%' where user='root';flush privileges;這樣就可以在宿主機使用navicat遠程連接虛擬機linux上的mysql了
配置主從節點
配置master
以linux(192.168.10.10)上的mysql為master，宿主機(192.168.10.1)上的mysql為slave配置主從復制。(可以參考：數據庫從主備到主主的高可用方案)
修改master的my.cnf如下
[mysqld]basedir=/export/server/mysqldatadir=/export/data/mysqlsocket=/tmp/mysql.sockuser=mysqlserver-id=10port=3306# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group,# customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemdlog-bin=mysql-bin # 開啟二進制日志expire-logs-days=7 # 設置日志過期時間，避免占滿磁盤binlog-ignore-db=mysql # 不使用主從復制的數據庫binlog-ignore-db=information_schemabinlog-ignore-db=performation_schemabinlog-ignore-db=sysbinlog-do-db=test #使用主從復制的數據庫[mysqld_safe]log-error=/export/data/mysql/error.logpid-file=/export/data/mysql/mysql.pid## include all files from the config directory#!includedir /etc/my.cnf.d重啟master
service mysqld restart登錄master查看配置是否生效(ON即為開啟，默認為OFF)：
mysql> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin | ON |+---------------+-------+在master的數據庫中建立備份賬號：backup為用戶名，%表示任何遠程地址，用戶back可以使用密碼1234通過任何遠程客戶端連接master
grant replication slave on *.* to 'backup'@'%' identified by '1234'查看user表可以看到我們剛創建的用戶：
mysql> use mysqlmysql> select user,authentication_string,host from user;+---------------+-------------------------------------------+-----------+| user | authentication_string | host |+---------------+-------------------------------------------+-----------+| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | % || mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || backup | *A4B6157319038724E3560894F7F932C8886EBFCF | % |+---------------+-------------------------------------------+-----------+新建test數據庫，創建一個article表以備后續測試
CREATE TABLE `article` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(64) DEFAULT NULL, `content` text, PRIMARY KEY (`id`)) CHARSET=utf8;重啟服務并刷新數據庫狀態到存儲文件中(with read lock表示在此過程中，客戶端只能讀數據，以便獲得一個一致性的快照)
[root@zhenganwen ~]# service mysqld restartShutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS! [root@zhenganwen mysql]# mysql -uroot -prootmysql> flush tables with read lock;Query OK, 0 rows affected (0.00 sec)查看master上當前的二進制日志和偏移量(記一下其中的File和Position)
mysql> show master status G*************************** 1. row *************************** File: mysql-bin.000002 Position: 154 Binlog_Do_DB: test Binlog_Ignore_DB: mysql,information_schema,performation_schema,sysExecuted_Gtid_Set: 1 row in set (0.00 sec)File表示實現復制功能的日志，即上圖中的Binary log；Position則表示Binary log日志文件的偏移量之后的都會同步到slave中，那么在偏移量之前的則需要我們手動導入。
主服務器上面的任何修改都會保存在二進制日志Binary log里面，從服務器上面啟動一個I/O thread(實際上就是一個主服務器的客戶端進程)，連接到主服務器上面請求讀取二進制日志，然后把讀取到的二進制日志寫到本地的一個Realy log里面。從服務器上面開啟一個SQL thread定時檢查Realy log，如果發現有更改立即把更改的內容在本機上面執行一遍。
如果一主多從的話，這時主庫既要負責寫又要負責為幾個從庫提供二進制日志。此時可以稍做調整，將二進制日志只給某一從，這一從再開啟二進制日志并將自己的二進制日志再發給其它從。或者是干脆這個從不記錄只負責將二進制日志轉發給其它從，這樣架構起來性能可能要好得多，而且數據之間的延時應該也稍微要好一些
手動導入，從master中導出數據
mysqldump -uroot -proot -hlocalhost test > /export/data/test.sql將test.sql中的內容在slave上執行一遍。
配置slave
修改slave的my.ini文件中的[mysqld]部分
log-bin=mysqlserver-id=1 #192.168.10.1保存修改后重啟slave，WIN+R->services.msc->MySQL5.7->重新啟動
登錄slave檢查log_bin是否以被開啟：
show VARIABLES like 'log_bin';配置與master的同步復制：
stop slave; change master to master_host='192.168.10.10', -- master的IP master_user='backup', -- 之前在master上創建的用戶 master_password='1234', master_log_file='mysql-bin.000002', -- master上 show master status G 提供的信息 master_log_pos=154;啟用slave節點并查看狀態
mysql> start slave;mysql> show slave status G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.10 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 154 Relay_Log_File: DESKTOP-KUBSPE0-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 537 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 10 Master_UUID: f68774b7-0b28-11e9-a925-000c290abe05 Master_Info_File: C:ProgramDataMySQLMySQL Server 5.7Datamaster.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:1 row in set (0.00 sec)注意查看第4、14、15三行，若與我一致，表示slave配置成功
測試
關閉master的讀取鎖定
mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)向master中插入一條數據
mysql> use testmysql> insert into article (title,content) values ('mysql master and slave','record the cluster building succeed!:)');Query OK, 1 row affected (0.00 sec)查看slave是否自動同步了數據
mysql> insert into article (title,content) values ('mysql master and slave','record the cluster building succeed!:)');Query OK, 1 row affected (0.00 sec)至此，主從復制的配置成功！：)
https://blog.csdn.net/xlgen157387/article/details/52452394讀寫分離
讀寫分離是依賴于主從復制，而主從復制又是為讀寫分離服務的。因為主從復制要求slave不能寫只能讀(如果對slave執行寫操作，那么show slave status將會呈現Slave_SQL_Running=NO，此時你需要按照前面提到的手動同步一下slave)。
方案一、定義兩種連接
就像我們在學JDBC時定義的DataBase一樣，我們可以抽取出ReadDataBase,WriteDataBase implements DataBase，但是這種方式無法利用優秀的線程池技術如DruidDataSource幫我們管理連接，也無法利用Spring AOP讓連接對DAO層透明。
方案二、使用Spring AOP
如果能夠使用Spring AOP解決數據源切換的問題，那么就可以和Mybatis、Druid整合到一起了。
我們在整合Spring1和Mybatis時，我們只需寫DAO接口和對應的SQL語句，那么DAO實例是由誰創建的呢？實際上就是Spring幫我們創建的，它通過我們注入的數據源，幫我們完成從中獲取數據庫連接、使用連接執行 SQL 語句的過程以及最后歸還連接給數據源的過程。
如果我們能在調用DAO接口時根據接口方法命名規范(增addXXX/createXXX、刪deleteXX/removeXXX、改updateXXXX、查selectXX/findXXX/getXX/queryXXX)動態地選擇數據源(讀數據源對應連接master而寫數據源對應連接slave)，那么就可以做到讀寫分離了。(可以參考：Spring 動態切換、添加數據源實現以及源碼淺析)
負載均衡算法
- 輪詢
- 加權輪詢：按照處理能力來加權
- 負載分配：依據當前的空閑狀態(但是測試每個節點的內存使用率、CPU利用率等，再做比較選出最閑的那個，效率太低)
高可用
在服務器架構時，為了保證服務器7x24不宕機在線狀態，需要為每臺單點服務器(由一臺服務器提供服務的服務器，如寫服務器、數據庫中間件)提供冗余機。(推薦：mysql+mycat搭建穩定高可用集群)
對于寫服務器來說，需要提供一臺同樣的寫-冗余服務器，當寫服務器健康時(寫-冗余通過心跳檢測)，寫-冗余作為一個從機的角色復制寫服務器的內容與其做一個同步；當寫服務器宕機時，寫-冗余服務器便頂上來作為寫服務器繼續提供服務。對外界來說這個處理過程是透明的，即外界僅通過一個IP訪問服務。
典型SQL
線上DDL
DDL(Database Definition Language)是指數據庫表結構的定義(create table)和維護(alter table)的語言。在線上執行DDL，在低于MySQL5.6版本時會導致全表被獨占鎖定，此時表處于維護、不可操作狀態，這會導致該期間對該表的所有訪問無法響應。但是在MySQL5.6之后，支持Online DDL，大大縮短了鎖定時間。
優化技巧是采用的維護表結構的DDL(比如增加一列，或者增加一個索引)，是copy策略。思路：創建一個滿足新結構的新表，將舊表數據逐條導入(復制)到新表中，以保證一次性鎖定的內容少(鎖定的是正在導入的數據)，同時舊表上可以執行其他任務。導入的過程中，將對舊表的所有操作以日志的形式記錄下來，導入完畢后，將更新日志在新表上再執行一遍(確保一致性)。最后，新表替換舊表(在應用程序中完成，或者是數據庫的rename，視圖完成)。
但隨著MySQL的升級，這個問題幾乎淡化了。
數據庫導入語句
在恢復數據時，可能會導入大量的數據。此時為了快速導入，需要掌握一些技巧：
1.導入時先禁用索引和約束：
alter table table-name disable keys待數據導入完成之后，再開啟索引和約束，一次性創建索引
alter table table-name enable keys2.數據庫如果使用的引擎是Innodb，那么它默認會給每條寫指令加上事務(這也會消耗一定的時間)，因此建議先手動開啟事務，再執行一定量的批量導入，最后手動提交事務。
3.如果批量導入的SQL指令格式相同只是數據不同，那么你應該先prepare預編譯一下，這樣也能節省很多重復編譯的時間。
limit offset,rows
盡量保證不要出現大的offset，比如limit 10000,10相當于對已查詢出來的行數棄掉前10000行后再取10行，完全可以加一些條件過濾一下(完成篩選)，而不應該使用limit跳過已查詢到的數據。這是一個offset做無用功的問題。對應實際工程中，要避免出現大頁碼的情況，盡量引導用戶做條件過濾。
select * 要少用
即盡量選擇自己需要的字段select，但這個影響不是很大，因為網絡傳輸多了幾十上百字節也沒多少延時，并且現在流行的ORM框架都是用的select *，只是我們在設計表的時候注意將大數據量的字段分離，比如商品詳情可以單獨抽離出一張商品詳情表，這樣在查看商品簡略頁面時的加載速度就不會有影響了。
order by rand()不要用
它的邏輯就是隨機排序(為每條數據生成一個隨機數，然后根據隨機數大小進行排序)。如select * from student order by rand() limit 5的執行效率就很低，因為它為表中的每條數據都生成隨機數并進行排序，而我們只要前5條。
解決思路：在應用程序中，將隨機的主鍵生成好，去數據庫中利用主鍵檢索。
單表和多表查詢
多表查詢：join、子查詢都是涉及到多表的查詢。如果你使用explain分析執行計劃你會發現多表查詢也是一個表一個表的處理，最后合并結果。因此可以說單表查詢將計算壓力放在了應用程序上，而多表查詢將計算壓力放在了數據庫上。
現在有ORM框架幫我們解決了單表查詢帶來的對象映射問題(查詢單表時，如果發現有外鍵自動再去查詢關聯表，是一個表一個表查的)。
count(*)
在MyISAM存儲引擎中，會自動記錄表的行數，因此使用count(*)能夠快速返回。而Innodb內部沒有這樣一個計數器，需要我們手動統計記錄數量，解決思路就是單獨使用一張表：
limit 1
如果可以確定僅僅檢索一條，建議加上limit 1，其實ORM框架幫我們做到了這一點(查詢單條的操作都會自動加上limit 1)。
慢查詢日志
用于記錄執行時間超過某個臨界值的SQL日志，用于快速定位慢查詢，為我們的優化做參考。
開啟慢查詢日志
配置項：slow_query_log
可以使用show variables like ‘slov_query_log’查看是否開啟，如果狀態值為OFF，可以使用set GLOBAL slow_query_log = on來開啟，它會在datadir下產生一個xxx-slow.log的文件。
設置臨界時間
配置項：long_query_time
查看：show VARIABLES like 'long_query_time'，單位秒
設置：set long_query_time=0.5
實操時應該從長時間設置到短的時間，即將最慢的SQL優化掉
查看日志
一旦SQL超過了我們設置的臨界時間就會被記錄到xxx-slow.log中
profile信息
配置項：profiling
開啟profile
set profiling=on
開啟后，所有的SQL執行的詳細信息都會被自動記錄下來
mysql> show variables like 'profiling';+---------------+-------+| Variable_name | Value |+---------------+-------+| profiling | OFF |+---------------+-------+1 row in set, 1 warning (0.00 sec)mysql> set profiling=on;Query OK, 0 rows affected, 1 warning (0.00 sec)查看profile信息
show profiles
mysql> show variables like 'profiling';+---------------+-------+| Variable_name | Value |+---------------+-------+| profiling | ON |+---------------+-------+1 row in set, 1 warning (0.00 sec)mysql> insert into article values (null,'test profile',':)');Query OK, 1 row affected (0.15 sec)mysql> show profiles;+----------+------------+-------------------------------------------------------+| Query_ID | Duration | Query |+----------+------------+-------------------------------------------------------+| 1 | 0.00086150 | show variables like 'profiling' || 2 | 0.15027550 | insert into article values (null,'test profile',':)') |+----------+------------+-------------------------------------------------------+通過Query_ID查看某條SQL所有詳細步驟的時間
show profile for query Query_ID
上面show profiles的結果中，每個SQL有一個Query_ID，可以通過它查看執行該SQL經過了哪些步驟，各消耗了多場時間
典型的服務器配置
以下的配置全都取決于實際的運行環境
max_connections，最大客戶端連接數
mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+table_open_cache，表文件句柄緩存(表數據是存儲在磁盤上的，緩存磁盤文件的句柄方便打開文件讀取數據)
mysql> show variables like 'table_open_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | table_open_cache | 2000 | +------------------+-------+key_buffer_size，索引緩存大小(將從磁盤上讀取的索引緩存到內存，可以設置大一些，有利于快速檢索)
mysql> show variables like 'key_buffer_size'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | key_buffer_size | 8388608 | +-----------------+---------innodb_buffer_pool_size，Innodb存儲引擎緩存池大小(對于Innodb來說最重要的一個配置，如果所有的表用的都是Innodb，那么甚至建議將該值設置到物理內存的80%，Innodb的很多性能提升如索引都是依靠這個)
mysql> show variables like 'innodb_buffer_pool_size'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | innodb_buffer_pool_size | 8388608 | +-------------------------+---------+innodb_file_per_table(innodb中，表數據存放在.ibd文件中，如果將該配置項設置為ON，那么一個表對應一個ibd文件，否則所有innodb共享表空間)
總結
以上是生活随笔為你收集整理的mysql主键自增长_全面的MySQL优化面试解析的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： bat 复制文件夹_怎么生成电脑上文件夹
- 下一篇： halcon从入门到精通pdf_机器视觉