選擇mysql理由

低成本耀态，高效能
通用巍举，易安裝觉义，易使用

mysql架構(gòu)

應(yīng)用層：負(fù)責(zé)和客戶端薪捍，用戶進(jìn)行交互，需要和不同的客戶端和中間服務(wù)器進(jìn)行交互，建立連接杂靶，記住連接狀態(tài)
坞古，響應(yīng)狀態(tài)
邏輯層：負(fù)責(zé)具體的邏輯查詢备韧，事務(wù)管理，存儲管理痪枫，恢復(fù)管理织堂，以及其他的附加功能
        查詢處理器負(fù)責(zé)查詢的解析，執(zhí)行奶陈；
        接到查詢請求時易阳，數(shù)據(jù)庫分配線程來處理它，先由查詢處理器生成執(zhí)行計劃吃粒，然后交由計劃執(zhí)行器來執(zhí)行潦俺；
        由事務(wù)管理器來執(zhí)行ACID特性；
        鎖管理器控制并發(fā)

物理層： 實際物理磁盤上的數(shù)據(jù)庫文件，比如數(shù)據(jù)文件事示，日志文件早像。

mysql查詢執(zhí)行過程概述

客戶端發(fā)布查詢的流程如下，首先連接MYSQL(Connection Handling)肖爵，然后發(fā)布查詢卢鹦，如果緩存(query ache)有結(jié)果集，則直接返回劝堪，如果沒有冀自，Mysql解析查詢(Parser)將通過優(yōu)化器Optimizer生成執(zhí)行計劃，然后執(zhí)行執(zhí)行計劃通過API(Pluggable Storage Engine API) 從存儲引擎獲取數(shù)據(jù)幅聘，并返回給客戶端

什么是執(zhí)行計劃凡纳， 執(zhí)行計劃就是一系列操作步驟，sql是聲明行語言帝蒿，只需要告訴數(shù)據(jù)庫查什么荐糜，不告訴數(shù)據(jù)庫如何去查，數(shù)據(jù)庫要做的就是基于算法和統(tǒng)計信息來計算最佳訪問路徑葛超。

相關(guān)概念

短連接
程序和數(shù)據(jù)庫建立連接,操作后,關(guān)閉連接;
基本步驟:連接-數(shù)據(jù)傳輸-關(guān)閉連接
對于高并發(fā)業(yè)務(wù),如果碰到連接的沖擊,推薦使用長連接和連接池.

長連接
程序建立連接之后,就一直打開,被后續(xù)程序重用,盡管 mysql 連接比其他數(shù)據(jù)庫快得多

連接池
數(shù)據(jù)庫創(chuàng)建和銷毀連接的開銷很大,可以使用連接池改進(jìn)性能,
將所有連接的客戶端共享使用,連接池可以加速連接,也可以減少數(shù)據(jù)庫連接,降低服務(wù)器的負(fù)載

持久連接和連接池的區(qū)別
長連接是一些驅(qū)動,驅(qū)動框架, ORM 工具的特性,由驅(qū)動來保持連接句柄的打開;
連接池是應(yīng)用服務(wù)器的組件,可以通過參數(shù)來配置連接數(shù),連接檢測,連接的生命周期;

如果連接池或者長連接的使用的連接數(shù)過多,有可能超過數(shù)據(jù)庫實例的限制.

單點故障： 系統(tǒng)某個部分暴氏，一旦失敗，將會導(dǎo)致整個系統(tǒng)無法工作绣张。一般需要增加冗余系統(tǒng)答渔，消除故障

讀寫分離：數(shù)據(jù)庫只能接受有限的讀請求，對于讀請求很多的應(yīng)用侥涵，數(shù)據(jù)庫可能會成為瓶頸沼撕，為了增加讀的能力，引入讀寫分離的技術(shù)芜飘，比如利用復(fù)制技術(shù)配置多個從庫

相關(guān)基礎(chǔ)概念

數(shù)據(jù)模型：數(shù)據(jù)的定義和格式务豺，數(shù)據(jù)是如何組織的，關(guān)系數(shù)據(jù)模型以二維表結(jié)構(gòu)表示實體與實體之間的關(guān)系
schema：定義語言描述的數(shù)據(jù)結(jié)構(gòu)嗦明，定義數(shù)據(jù)是如何組織構(gòu)建的笼沥。典型的關(guān)系數(shù)據(jù)模型，是以數(shù)據(jù)庫表的形式來組織數(shù)據(jù)的娶牌，關(guān)系數(shù)據(jù)庫的schema就是數(shù)據(jù)庫各種關(guān)系的結(jié)構(gòu)化描述奔浅。
結(jié)構(gòu)化數(shù)據(jù)：記錄信息的類型，格式等屬性是固定的诗良。業(yè)內(nèi)也有人把關(guān)系型數(shù)據(jù)中能用二維表表示的數(shù)據(jù)成為結(jié)構(gòu)化數(shù)據(jù)
DDL：數(shù)據(jù)定義語言汹桦，負(fù)責(zé)數(shù)據(jù)結(jié)構(gòu)定義與數(shù)據(jù)庫對象定義的語言，例如創(chuàng)建數(shù)據(jù)庫鉴裹，創(chuàng)建表营勤，create灵嫌、alter，drop
DML ： 數(shù)據(jù)操作語言葛作，用來查詢和修改數(shù)據(jù)的語句

關(guān)系數(shù)據(jù)模型： 關(guān)于null，字段未知或者未定義猖凛，數(shù)據(jù)庫小心處理赂蠢；數(shù)據(jù)庫管理系統(tǒng)為了高效地檢索記錄,往往會創(chuàng)建各種索引結(jié)構(gòu)加速檢索記錄,或者按照索引 /key 的順序存儲記
錄,所以基于記錄的索引 /key 會很容易查找到記錄。
XML數(shù)據(jù)模型：可擴展標(biāo)記語言
JSON：儲存半結(jié)構(gòu)化數(shù)據(jù)
  ER建模：實體（對象集合）辨泳，關(guān)系（實體之間關(guān)系）虱岂，屬性

數(shù)據(jù)類型

數(shù)值類型： 整型和實數(shù)
確切精度類型：decimal
近似精度類型：單精度float，雙精度double

整型： TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT/  空間占用依次增大菠红，數(shù)值范圍依次變大
decimal和numeric：定點數(shù)第岖，小數(shù)點后面的位數(shù)是固定的，DECEMAL（M试溯，D）M是精度蔑滓，10進(jìn)制數(shù)位數(shù)，D是標(biāo)度表示小數(shù)點后的位數(shù)遇绞。
FLOAT和double:浮點數(shù)比整型數(shù)值范圍更大键袱，單精度四個字節(jié)，雙精度8個字節(jié)摹闽，浮點數(shù)存在誤差問題蹄咖，盡量不使用浮點數(shù)進(jìn)行比較，

日期和時間類型：表示時間值和時間類型有DATETIME付鹿，DATE澜汤，TIMESTAMP，TIME舵匾，YEAR俊抵，每種類型有其特有范圍，timestamp有特有自動更新特性纽匙，

DATETIME：8個字節(jié)务蝠，同時包含日期和數(shù)字
DATE：只有年月日日期，3個字節(jié)
TIMESTAMP 時間戳烛缔，列用于在進(jìn)行 INSERT 或 UPDATE 操作時記錄日期和時間馏段，保存格式和DATETIME相同，以4字節(jié)保存践瓷，以UTC格式保存院喜，存儲時保存當(dāng)前時區(qū)信息，
若將 TIMESTAMP 類型字段定義為 default current_timestamp ,那么插入一條記錄時,該 TIMESTAMP 字段自動被賦值為當(dāng)前時間晕翠；
若將 TIMESTAMP 類型字段定義為 on update current_timestamp ,那么修改一條記錄時,該 TIMESTAMP 字段自動被修改為當(dāng)前時間喷舀；

字符串類型：CHAR/VARCHAR/BINARY/VARBINARY/BLOB/TEXT/ENUM/SET
CHAR和VARCHAR：類型類似砍濒，保存和檢索數(shù)據(jù)的方式不同，聲明方式char(30),表面占用字符位數(shù)硫麻，超過位數(shù)則對其裁剪爸邢，char存儲固定長度字符，存儲時右邊會增加指定空格達(dá)到指定字符長度拿愧，檢索時會去掉杠河，適合保存長度差不多的字符串，varchar變長字符浇辜，適合存儲長文本券敌。

選擇合適數(shù)據(jù)類型：
    小往往更好，簡單類型更好柳洋，盡可能避免null值待诅，

?

mysql 復(fù)制架構(gòu)

  主從模式 A→B
  主主模式 A←→B
  鏈?zhǔn)綇?fù)制模式 A→B→C
  環(huán)形復(fù)制模式 A→B→C→A

存儲引擎介紹

mysql存儲引擎是可插撥的，其核心代碼和存儲引擎是分離的熊镣，mysql支持不同的表使用不同的引擎

  InnoDB 引擎
  優(yōu)點:
  災(zāi)難恢復(fù)性好;
  支持全部4種級別事務(wù),默認(rèn)事務(wù)隔離級別,可重復(fù)讀;
  使用行級鎖;
  數(shù)據(jù)物理組織形式是簇表,數(shù)據(jù)按主鍵來組織,主鍵和數(shù)據(jù)是一起的;
  實現(xiàn)緩沖管理,緩沖索引和緩沖數(shù)據(jù),自動創(chuàng)建散列索引加快數(shù)據(jù)獲取;
  支持外鍵;
  支持熱備份

如何安裝

mac 安裝下載社區(qū)版本5.7
安裝時顯示臨時密碼:root@localhost: 1I3!6+2oG;S!   隨機

配置文件

  /usr/local/mysql-5.7.17-macos10.12-x86_64/support-files/my-default.cnf 

配置文件主要參數(shù)設(shè)置

  innodb_buffer_pool_size 提升性能,將數(shù)據(jù)先在緩沖區(qū)合并
  
  innodb_log_file_size 每個日志文件的大小,建議將日志文件大小設(shè)置為256mb 或者更大,這樣可滿足需要
  
  innodb_flush_log_at_trx_commit卑雁，建議設(shè)置為2
  這個選項的默認(rèn)值是1。當(dāng)設(shè)置為2時轧钓，在每個事務(wù)提交時序厉，日志緩沖被寫到文件中，但不對日志文件做刷新到磁盤的操作,對日志文件刷新每秒刷新才發(fā)生一次
  
  sync_binlog 建議設(shè)置為0,值為1是最好的選擇

日志文件

命令文件夾

  /usr/local/mysql-5.7.17-macos10.12-x86_64/bin

常用命令行使用

mysql 命令行

啟動MySQL服務(wù)
sudo /usr/local/mysql/support-files/mysql.server start
停止MySQL服務(wù)
sudo /usr/local/mysql/support-files/mysql.server stop
重啟MySQL服務(wù)
sudo /usr/local/mysql/support-files/mysql.server restart
?

mysql -h localhost -P 3306 -u root -p 登錄
show databases;顯示所有數(shù)據(jù)庫
help;

mysql> SELECT VERSION(), CURRENT_DATE;  查看版本,目前日期
mysql> SELECT SIN(PI()/4), (4+1)*5;

use db_name; 使用數(shù)據(jù)庫名稱
select database(); 查看正在使用的數(shù)據(jù)庫
create database db_name; 創(chuàng)建數(shù)據(jù)庫
GRANT select,insert,update,delete ON db_name.* TO 'your_name' @ 'your_client_host';
show tables;  顯示所有的表

status 顯示當(dāng)前連接,客戶端,數(shù)據(jù)庫字符集等信息的命令;
SHOW ENGINES;顯示可用搜索引擎
exit 退出

 mysql -h localhost -P 3306 -u root -p employees < /Users/lx/Downloads/employees.sql
 輸入密碼導(dǎo)入數(shù)據(jù)

 整個數(shù)據(jù)庫數(shù)據(jù)導(dǎo)出到文件
mysqldump -h localhost -u root -p mydb >e:\mysql\mydb.sql

單個表導(dǎo)出到文件
mysqldump -h localhost -u root -p mydb mytable>e:\mysql\mytable.sql

將數(shù)據(jù)庫mydb的結(jié)構(gòu)導(dǎo)出到e:\mysql\mydb_stru.sql文件中：
mysqldump -h localhost -u root -p mydb --add-drop-table >e:\mysql\mydb_stru.sql

sql語法

（1）模式匹配：SQL 有兩個通配符, “-” 匹配任意單個字符, “%” 匹配任意多個字符(包括 0 個字符)
( 2 )邏輯操作符與或非( AND 毕箍、 OR 弛房、 NOT )
（3 )范圍操作符 IN 和 BETWEEN
( 4 )限制獲取記錄數(shù)(使用 LIMIT 子句)
( 5 )排序( ORDER BY )
( 6 )數(shù)據(jù)計算
( 7 )使用 DISTINCT 獲取不重復(fù)的唯一值，SELECT DISTINCT first_name FROM employees ;
( 8 )聚集函數(shù) COUNT 而柑、 MIN 文捶、 MAX 、 AVG 媒咳、 SUM
( 9 )分組統(tǒng)計 GROUP BY 子句
( 10 )并集操作( UNION 和 UNION ALL ) UNION 實際上是 UNION DISTINCT ,在進(jìn)行表連接后會篩選掉重復(fù)的記錄粹排，SELECT * FROM a
UNION ALL
SELECT * FROM b;

( 11 ) NULL 值，NULL 值的判斷一般使用 IS NULL 或 IS NOT NULL ,不能使用以上的比較操作符 = 涩澡、 < 顽耳、 > ,因為 NULL 是一個特殊的值

JOIN (連接)
鏈接原理：全表掃描驅(qū)動表，用驅(qū)動表結(jié)果集去匹配被驅(qū)動表妙同，可以利用索引射富，數(shù)據(jù)庫基于成本會選擇小表作為驅(qū)動表，然后被驅(qū)動表使用索引進(jìn)行連接粥帚。
連接的表越多,函數(shù)嵌套的層數(shù)就越多胰耗，盡量減少連接表的個數(shù)；

子查詢：查詢語句里面的select語句芒涡，子查詢不宜過多柴灯，否則性能會很差卖漫。

索引學(xué)習(xí)

數(shù)據(jù)庫索引，是數(shù)據(jù)庫排序 的數(shù)據(jù)結(jié)構(gòu)赠群，用于協(xié)助快速查詢羊始，更新數(shù)據(jù)表中的數(shù)據(jù)。
mysql支持索引：B樹索引乎串，散列索引店枣，空間索引，全文索引

從邏輯上叹誉，可以分為單列索引，復(fù)合索引（多列索引）闷旧，唯一索引长豁，和非唯一索引
如果索引鍵值的邏輯順序與索引所服務(wù)的表中相應(yīng)行的物理順序相同,那么該索引被稱為簇索引( cluster index )，也稱為聚集索引忙灼。
InnoDB使用聚集索引匠襟，數(shù)據(jù)和索引在一起，記錄被真實的保存在索引的葉子中该园。

簇索引優(yōu)點：
1酸舍，將相關(guān)數(shù)據(jù)保持在一起，葉子節(jié)點內(nèi)可保存相臨近的記錄里初；
2啃勉，因為索引和數(shù)據(jù)存儲在一起,所以查找數(shù)據(jù)通常比非簇索引更快

何種查詢使用索引

1，支持前導(dǎo)列双妨，篩選記錄where條件能組合復(fù)合索引最左邊的部分淮阐，既按最左前綴原則進(jìn)行篩選。
2刁品，索引列上范圍查找泣特，where betweenand 條件在索引列上，范圍不要太大挑随，in()不屬于范圍查找的范疇状您。
3，where子句的條件列是復(fù)合索引前面的索引列再加上緊跟的另一個列的范圍查找兜挨，舉例：CREATE INDEX idx_a_b_c_d ON tb1(a,b,c,d);只有使用如下條件才能應(yīng)用到復(fù)合索引WHERE a=? AND b=? AND c > 10000; WHERE a=? AND b=? AND c=? AND d<10000;

?

進(jìn)階

應(yīng)用程序性能指標(biāo)(應(yīng)用性能管理 APM)
兩組
終端用戶性能體驗:負(fù)載和響應(yīng)時間
負(fù)載是應(yīng)用程序處理的業(yè)務(wù)量,如每秒事務(wù)數(shù),每秒請求數(shù) qps,每秒 PV.
響應(yīng)時間是指給定的負(fù)載下,應(yīng)用程序響應(yīng)用戶的操作時間

第二組性能指標(biāo):一定負(fù)載下,應(yīng)用程序使用的計算資源是否有足夠的容量來支持給定的負(fù)載,預(yù)測在哪里可能會有性能瓶頸
研發(fā)技巧(mysqlDBA 修煉之道)

* 存儲樹形數(shù)據(jù),比如組織架構(gòu),話題討論,知識管理,商品分類
  ①路徑枚舉,增加一個字段,記錄節(jié)點祖先信息
  ②閉包表,記錄節(jié)點之間的關(guān)系,記錄節(jié)點和父節(jié)點之間關(guān)系,也記錄所有節(jié)點之間關(guān)系
* 轉(zhuǎn)換字符集
  需要修改某個表的字符集.比如 A表的字符集原來是 gbk, 現(xiàn)在將其修改為 utf-8
  ①直接 mysql命令下完成;
  ②使用 mysqldump 工具
  ③使用 ICNV命令轉(zhuǎn)換文件編碼
* 處理重復(fù)值
  ①防止出現(xiàn)重復(fù)記錄:主鍵或者唯一索引,插入時使用 insert ignore 語句,不重復(fù)正常插入,重復(fù)舍掉;也可以 replace into 語句,新記錄則插入,重復(fù)記錄則替換.
  ②統(tǒng)計和識別重復(fù)值;
  SELECT COUNT (*) AS repetitions, last_name, first_name FROM person_tbl
  GROUP BY last_name, first_name HAVING repetitions > 1;
  ③從結(jié)果集中消除重復(fù)記錄
  使用 DISTINCT
* 分頁算法
  mysql> SELECT col_1,col_2 FROM profiles WHERE sex='M' ORDER BY rating limit 100000, 10;
  大偏距值查詢很慢,方法:限制用戶看到的頁,比如只提供最新的幾頁
  覆蓋索引

?

查詢優(yōu)化技巧

優(yōu)化策略

優(yōu)化數(shù)據(jù)訪問:可以緩存就不需要從數(shù)據(jù)庫讀取
重寫 sql:復(fù)雜查詢嚴(yán)重降低并發(fā)性,建議將復(fù)雜查詢分為多個簡單查詢;連接 join 嚴(yán)重降低并發(fā)性.
重新設(shè)計表:可以增加緩存表,暫存統(tǒng)計數(shù)據(jù),增加冗余列,減少連接
添加索引:生產(chǎn)環(huán)境80%性能問題是性能問題.

group by/Distinct/order by 語句優(yōu)化
* 盡量對較少的行進(jìn)行排序;
* 如果連接多張表, order by 列應(yīng)該屬于連接順序的第一張表
* 利用索引排序
* groupby,orderby 語句參考的列應(yīng)該盡量在一張表上,如果不在,可以考慮冗余一些列,要么合并表
* 指定 order by null: 默認(rèn) mysql 排序所有 group by 的查詢,為避免排序帶來消耗,可以指定 order by null
優(yōu)化 limit 字句 效率差 兩點:

1)限制頁數(shù),只顯示前幾頁,查過也術(shù)后,直接顯示更多( more)

2)避免設(shè)置 offset 值,也就是避免丟棄記錄,可以使用條件限制要排序的結(jié)果集

索引

索引中字段不超過5個
單張表索引數(shù)量建議控制5個以內(nèi)
唯一鍵和主鍵不要重復(fù)
索引字段的順序需要考慮唯一值得個數(shù),選擇性高的字段一般放在前面
復(fù)合索引的前面部分用于等值查詢,后面的部分用于排序
使用 explain 判斷 sql 語句是否合理使用了索引,盡量避免 Extra 列出現(xiàn) Using File Sort,Using Temporary
UPDATE,DELETE 語句需要根據(jù) WHERE 條件添加索引(啥意思)
建議不要使用 like%value 的形式, mysql 僅支持最左前綴索引
對長度過長的 VARCHAR 字段,比如網(wǎng)頁地址建立索引時,需要增加散列字段,對varchar 使用散列算法后,散列后字段最好是整型,然后對該字段建立索引.
覆蓋索引,覆蓋索引一般常駐于內(nèi)存中,因此可以大大提高查詢效率;
把范圍條件放到復(fù)合索引的最后膏孟，WHERE條件中的范圍條件(BETWEEN、<暑劝、<=骆莹、>、>=)會導(dǎo)致后面的條件使用不了索引.

表設(shè)計

盡量將字段設(shè)置成 not null ,null值存儲需要額外空間,且會導(dǎo)致比較運算較為復(fù)雜,這會使優(yōu)化器難以優(yōu)化 sql
使用更短小的列,比如短整型,短整型執(zhí)行速度往往更快
存儲精確浮點數(shù)時,使用decimal 替代 float 和 double
建議使用 unsigned 類型存儲非負(fù)值
建議使用 INT unsighed 存儲 IPV4
整型定義中不添加顯示長度的值,比如使用 int, 而不是 int(4)
建議不要使用 ENUM 枚舉類型
盡可能不要使用 TEXT,BLOB 類型
字符集建議使用 UTF-8
存儲年時使用 YEAR 類型
存儲日期時使用 DATE 類型
存儲時間時建議使用 TIMESTAMP 類型,
join 字段在不同表上類型和命名要一致

• 常見問題