1 數(shù)據(jù)庫(kù)介紹

1.1 什么是數(shù)據(jù)庫(kù)

數(shù)據(jù)庫(kù)就是一個(gè)存放計(jì)算機(jī)數(shù)據(jù)的倉(cāng)庫(kù)，這個(gè)倉(cāng)庫(kù)是按照一定的數(shù)據(jù)結(jié)構(gòu)（數(shù)據(jù)結(jié)構(gòu)是指數(shù)據(jù)的組織形式或數(shù)據(jù)之間的聯(lián)系）來(lái)對(duì)數(shù)據(jù)進(jìn)行組織和存儲(chǔ)的，可以通過(guò)數(shù)據(jù)庫(kù)提供的多種方法來(lái)管理其中的數(shù)據(jù)。

1.2 數(shù)據(jù)庫(kù)的種類

關(guān)系型數(shù)據(jù)庫(kù)和非關(guān)系型數(shù)據(jù)庫(kù)。

1.3 常用數(shù)據(jù)庫(kù)

• 關(guān)系型數(shù)據(jù)庫(kù)： Oracle、Microsoft SQL Server、MySQL/MariaDB等。
• 非關(guān)系型數(shù)據(jù)庫(kù)： MongoDB Memcached Redis

1.4、關(guān)系型數(shù)據(jù)庫(kù)

關(guān)系型數(shù)據(jù)庫(kù)模型是把復(fù)雜的數(shù)據(jù)結(jié)構(gòu)歸結(jié)為簡(jiǎn)單的二元關(guān)系（即二維表格形式）。在關(guān)系型數(shù)據(jù)庫(kù)中，對(duì)數(shù)據(jù)的操作幾乎全部建立在一個(gè)或多個(gè)關(guān)系表格上，通過(guò)這些關(guān)聯(lián)的表格分類、合并、連接或選取等運(yùn)算來(lái)實(shí)現(xiàn)數(shù)據(jù)的管理。 關(guān)系型數(shù)據(jù)可以很好地存儲(chǔ)一些關(guān)系模型的數(shù)據(jù)，比如一個(gè)老師對(duì)應(yīng)多個(gè)學(xué)生的數(shù)據(jù)（“一對(duì)多”），一本書(shū)對(duì)應(yīng)多個(gè)作者（“一對(duì)多”），一本書(shū)對(duì)應(yīng)一個(gè)出版日期（“一對(duì)一”） 關(guān)系型數(shù)據(jù)庫(kù)誕生距今已有 40 多年了，從理論產(chǎn)生到發(fā)展到實(shí)現(xiàn)產(chǎn)品，例如：常見(jiàn)的 MySQL 和 Oracle 數(shù)據(jù)庫(kù)，Oracle 在數(shù)據(jù)庫(kù)領(lǐng)域里上升到了霸主地位，形成每年高達(dá)數(shù)百億美元的龐大產(chǎn)業(yè)市場(chǎng)，而 MySQL 也是不容忽視的數(shù)據(jù)庫(kù)，以至于被 Oracle 重金收購(gòu)了。

1.5、非關(guān)系型數(shù)據(jù)庫(kù)

非關(guān)系型數(shù)據(jù)庫(kù)也被稱為 NoSQL 數(shù)據(jù)庫(kù)，NoSQL 的本意是 “Not Only SQL”，指的是非關(guān)系型數(shù)據(jù)庫(kù)，而不是“NO SQL”的意思，NoSQL 的產(chǎn)生并不是要徹底否定關(guān)系型數(shù)據(jù)庫(kù)，而是作為傳統(tǒng)數(shù)據(jù)庫(kù)的一個(gè)有效補(bǔ)充。NoSQL 數(shù)據(jù)庫(kù)在特定的場(chǎng)景下可以發(fā)揮難以想象的高效率和高性能。特別是對(duì)于規(guī)模日益擴(kuò)大的海量數(shù)據(jù)，超大規(guī)模和高并發(fā)的微博、微信、SNS 類型的純動(dòng)態(tài)網(wǎng)站已經(jīng)顯得力不從心，暴露了很多難以克服的問(wèn)題，例如：傳統(tǒng)的關(guān)系型數(shù)據(jù)庫(kù)IO瓶頸、性能瓶頸都難以有效突破，于是開(kāi)始出現(xiàn)了大批針對(duì)特定場(chǎng)景，以高性能和使用便利為目的功能特異化的數(shù)據(jù)庫(kù)產(chǎn)品。NoSQL（非關(guān)系型）類的數(shù)據(jù)庫(kù)就是這樣的情景中誕生并得到了非常迅速的發(fā)展。

NoSQL 是非關(guān)系型數(shù)據(jù)庫(kù)的廣義定義。它打破了長(zhǎng)久以來(lái)關(guān)系型數(shù)據(jù)庫(kù)與ACID理論大一統(tǒng)的局面。NoSQL數(shù)據(jù)存儲(chǔ)不需要固定的表結(jié)構(gòu)，通常也不存在連續(xù)操作。

1.鍵值（Key-Value）存儲(chǔ)數(shù)據(jù)庫(kù)

• 鍵值數(shù)據(jù)庫(kù)就類似傳統(tǒng)語(yǔ)言中使用的哈希表?？梢酝ㄟ^(guò)key來(lái)添加、查詢或者刪除數(shù)據(jù)，因?yàn)槭褂胟ey主鍵訪問(wèn)，所以會(huì)獲得很高的性能及擴(kuò)展性。這個(gè)表中有一個(gè)特定的鍵和一個(gè)指針指向特定的數(shù)據(jù)。Key-Value模型對(duì)于IT系統(tǒng)來(lái)說(shuō)的優(yōu)勢(shì)在于簡(jiǎn)單、易部署、高并發(fā)。
• 典型產(chǎn)品：Memcached、Redis、MemcachedB

2、列存儲(chǔ)（Column-Oriented）數(shù)據(jù)庫(kù)

• 列存儲(chǔ)數(shù)據(jù)庫(kù)將數(shù)據(jù)存儲(chǔ)存在列族（Column Family）中，一個(gè)列族存儲(chǔ)經(jīng)常被一起查詢的相關(guān)數(shù)據(jù)。舉個(gè)例子，如果有一個(gè) Person 類，通常會(huì)一起查詢他們的姓名和年齡而不是薪資。這種情況下，姓名和年齡就會(huì)被放入一個(gè)列族中，而薪資則在另一個(gè)列族中。這部分?jǐn)?shù)據(jù)庫(kù)通常用來(lái)應(yīng)對(duì)分布式存儲(chǔ)的海量數(shù)據(jù)。鍵仍然存在，但是他們的特點(diǎn)是指向了多個(gè)列。這些列是由列家族來(lái)安排的。
• 典型產(chǎn)品：Cassandra，HBase

3、面向文檔（Document-Oriented）的數(shù)據(jù)庫(kù)

• 面向文檔數(shù)據(jù)庫(kù)會(huì)將數(shù)據(jù)以文檔的形式存儲(chǔ),數(shù)據(jù)存儲(chǔ)的最小單位是文檔，同一個(gè)表中存儲(chǔ)的文檔屬性可以是不同的，數(shù)據(jù)可以使用XML、JSON等多種形式存儲(chǔ)。
• 典型產(chǎn)品：MongDB、CouchDB

4、圖形（Graph）數(shù)據(jù)庫(kù)

• 圖形數(shù)據(jù)庫(kù)允許我們將數(shù)據(jù)以圖的方式存儲(chǔ)。圖形結(jié)構(gòu)的數(shù)據(jù)庫(kù)同其他行列以及剛性結(jié)構(gòu)的 SQL 數(shù)據(jù)庫(kù)不同，它是使用靈活的圖形模型，并且能夠擴(kuò)展到多個(gè)服務(wù)器上。
• 典型產(chǎn)品：Neo4J、InfoGr id

2 MySQL數(shù)據(jù)庫(kù)

2.1 MySQL簡(jiǎn)介

MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQL AB 公司開(kāi)發(fā)，目前屬于 Oracle 旗下產(chǎn)品。MySQL 是最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)之一，在 WEB 應(yīng)用方面，MySQL是最好的 RDBMS (Relational Database Management System，關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)) 應(yīng)用軟件之一。關(guān)系數(shù)據(jù)庫(kù)將數(shù)據(jù)保存在不同的表中，而不是將所有數(shù)據(jù)放在一個(gè)大倉(cāng)庫(kù)內(nèi)，這樣就增加了速度并提高了靈活性。

MySQL所使用的 SQL 語(yǔ)言是用于訪問(wèn)數(shù)據(jù)庫(kù)的最常用標(biāo)準(zhǔn)化語(yǔ)言。MySQL 軟件采用了雙授權(quán)政策，分為社區(qū)版和商業(yè)版，由于其體積小、速度快、總體擁有成本低，尤其是開(kāi)放源碼這一特點(diǎn)，一般中小型網(wǎng)站的開(kāi)發(fā)都選擇 MySQL 作為網(wǎng)站數(shù)據(jù)庫(kù)。

RDBMS即關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)(Relational Database Management System)

2.2 MySQL安裝方式

略

2.3 MySQL數(shù)據(jù)庫(kù)存儲(chǔ)引擎

數(shù)據(jù)庫(kù)存儲(chǔ)引擎是數(shù)據(jù)庫(kù)底層軟件組織，數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS）使用數(shù)據(jù)引擎進(jìn)行創(chuàng)建、查詢、更新和刪除數(shù)據(jù)。不同的存儲(chǔ)引擎提供不同的存儲(chǔ)機(jī)制、索引、鎖表等功能，使用不同的存儲(chǔ)引擎，還可以 獲得特定的功能?，F(xiàn)在許多不同的數(shù)據(jù)庫(kù)管理系統(tǒng)都支持多種不同的數(shù)據(jù)引擎。MySQL的核心就是存儲(chǔ)引擎。

1、InnoDB存儲(chǔ)引擎:默認(rèn)引擎，最常用的。 InnoDB是事務(wù)型數(shù)據(jù)庫(kù)的首選引擎，支持事務(wù)安全表（ACID），支持行鎖定;InnoDB是默認(rèn)的MySQL引擎

InnoDB特點(diǎn)： 支持事務(wù)處理，支持崩潰修復(fù)和并發(fā)控制。如果需要對(duì)事務(wù)的完整性要求比較高（比如銀行），要求實(shí)現(xiàn)并發(fā)控制（比如售票），那選擇InnoDB有很大的優(yōu)勢(shì)。如果需要頻繁的更新、刪除操作的數(shù)據(jù)庫(kù)，也可以選擇InnoDB，因?yàn)橹С质聞?wù)的提交（commit）和回滾（rollback）。

2、MyISAM存儲(chǔ)引擎：（了解）MyISAM基于ISAM存儲(chǔ)引擎，并對(duì)其進(jìn)行擴(kuò)展。它是在Web、數(shù)據(jù)倉(cāng)儲(chǔ)和其他應(yīng)用環(huán)境下最常使用的存儲(chǔ)引擎之一。MyISAM擁有較高的插入、查詢速度，但不支持事務(wù)。

MyISAM特點(diǎn)： 插入數(shù)據(jù)快，空間和內(nèi)存使用比較低。如果表主要是用于插入新記錄和讀出記錄，那么選擇MyISAM能實(shí)現(xiàn)處理高效率。如果應(yīng)用的完整性、并發(fā)性要求比較低，也可以使用。

12306查詢 只生成一條數(shù)據(jù)這種適合。

3、MEMORY內(nèi)存型引擎(了解)MEMORY存儲(chǔ)引擎將表中的數(shù)據(jù)存儲(chǔ)到內(nèi)存中，為查詢和引用其他表數(shù)據(jù)提供快速訪問(wèn)

MEMORY特點(diǎn)： 所有的數(shù)據(jù)都在內(nèi)存中，數(shù)據(jù)的處理速度快，但是安全性不高。如果需要很快的讀寫(xiě)速度，對(duì)數(shù)據(jù)的安全性要求較低，可以選擇MEMOEY。它對(duì)表的大小有要求，不能建立太大的表。所以，這類數(shù)據(jù)庫(kù)只使用在相對(duì)較小的數(shù)據(jù)庫(kù)表。

4、Archive(歸檔引擎)

如何選擇引擎：如果要提供提交、回滾、并要求實(shí)現(xiàn)并發(fā)控制，InnoDB是一個(gè)好的選擇；如果數(shù)據(jù)表主要用來(lái)插入和查詢記錄，則MyISAM引擎能提供較高的處理效率；如果只是臨時(shí)存放數(shù)據(jù)，數(shù)據(jù)量不大，并且不需要較高的數(shù)據(jù)安全性，可以選擇將數(shù)據(jù)保存在內(nèi)存中的Memory引擎；MySQL中使用該引擎作為臨時(shí)表，存放查詢的中間結(jié)果；

使用哪一種引擎需要靈活選擇，一個(gè)數(shù)據(jù)庫(kù)中多個(gè)表可以使用不同引擎以滿足各種性能和實(shí)際需求，使用合適的存儲(chǔ)引擎，將會(huì)提高整個(gè)數(shù)據(jù)庫(kù)的性能。

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

MySQL支持多種類型，大致可以分為三類：數(shù)值、日期/時(shí)間和字符串(字符)類型。

3.1 MySQL數(shù)值類型

MySQL支持所有標(biāo)準(zhǔn)SQL數(shù)值數(shù)據(jù)類型。

這些類型包括嚴(yán)格數(shù)值數(shù)據(jù)類型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數(shù)值數(shù)據(jù)類型(FLOAT、REAL和DOUBLE PRECISION)。

關(guān)鍵字INT是INTEGER的同義詞，關(guān)鍵字DEC是DECIMAL的同義詞。

BIT數(shù)據(jù)類型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作為SQL標(biāo)準(zhǔn)的擴(kuò)展，MySQL也支持整數(shù)類型TINYINT、MEDIUMINT和BIGINT。下面的表顯示了需要的每個(gè)整數(shù)類型的存儲(chǔ)和范圍。

|

類型

|

大小

|

范圍（有符號(hào)）

|

范圍（無(wú)符號(hào)）

|

用途

|
|

TINYINT

|

1 字節(jié)

|

(-128，127)

|

(0，255)

|

小整數(shù)值

|
|

SMALLINT

|

2 字節(jié)

|

(-32 768，32 767)

|

(0，65 535)

|

大整數(shù)值

|
|

MEDIUMINT

|

3 字節(jié)

|

(-8 388 608，8 388 607)

|

(0，16 777 215)

|

大整數(shù)值

|
|

INT或INTEGER

|

4 字節(jié)

|

(-2 147 483 648，2 147 483 647)

|

(0，4 294 967 295)

|

大整數(shù)值

|
|

BIGINT

|

8 字節(jié)

|

(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)

|

(0，18 446 744 073 709 551 615)

|

極大整數(shù)值

|
|

FLOAT

|

4 字節(jié)

|

(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)

|

0，(1.175 494 351 E-38，3.402 823 466 E+38)

|

單精度 浮點(diǎn)數(shù)值

|
|

DOUBLE

|

8 字節(jié)

|

(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)

|

0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)

|

雙精度 浮點(diǎn)數(shù)值

|
|

DECIMAL

|

對(duì)DECIMAL(M,D) ，如果M>D，為M+2否則為D+2

|

依賴于M和D的值

|

依賴于M和D的值

|

小數(shù)值

|

3.2 MySQL日期和時(shí)間類型

表示時(shí)間值的日期和時(shí)間類型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每個(gè)時(shí)間類型有一個(gè)有效值范圍和一個(gè)"零"值，當(dāng)指定不合法的MySQL不能表示的值時(shí)使用"零"值。

TIMESTAMP類型有專有的自動(dòng)更新特性，將在后面描述。

|

類型

|

大小(字節(jié))

|

范圍

|

格式

|

用途

|
|

DATE

|

3

|

1000-01-01/9999-12-31

|

YYYY-MM-DD

|

日期值

|
|

TIME

|

3

|

'-838:59:59'/'838:59:59'

|

HH:MM:SS

|

時(shí)間值或持續(xù)時(shí)間

|
|

YEAR

|

1

|

1901/2155

|

YYYY

|

年份值

|
|

DATETIME

|

8

|

1000-01-01 00:00:00/9999-12-31 23:59:59

|

YYYY-MM-DD HH:MM:SS

|

混合日期和時(shí)間值

|
|

TIMESTAMP

|

4

|

1970-01-01 00:00:00/2038

結(jié)束時(shí)間是第 2147483647 秒，北京時(shí)間 2038-1-19 11:14:07，格林尼治時(shí)間 2038年1月19日 凌晨 03:14:07

|

YYYYMMDD HHMMSS

|

混合日期和時(shí)間值，時(shí)間戳

|

3.3 MySQL字符串類型

字符串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節(jié)描述了這些類型如何工作以及如何在查詢中使用這些類型。

|

類型

|

大小

|

用途

|
|

CHAR

|

0-255字節(jié)

|

定長(zhǎng)字符串

|
|

VARCHAR

|

0-65535 字節(jié)

|

變長(zhǎng)字符串

|
|

TINYBLOB

|

0-255字節(jié)

|

不超過(guò) 255 個(gè)字符的二進(jìn)制字符串

|
|

TINYTEXT

|

0-255字節(jié)

|

短文本字符串

|
|

BLOB

|

0-65 535字節(jié)

|

二進(jìn)制形式的長(zhǎng)文本數(shù)據(jù)

|
|

TEXT

|

0-65 535字節(jié)

|

長(zhǎng)文本數(shù)據(jù)

|
|

MEDIUMBLOB

|

0-16 777 215字節(jié)

|

二進(jìn)制形式的中等長(zhǎng)度文本數(shù)據(jù)

|
|

MEDIUMTEXT

|

0-16 777 215字節(jié)

|

中等長(zhǎng)度文本數(shù)據(jù)

|
|

LONGBLOB

|

0-4 294 967 295字節(jié)

|

二進(jìn)制形式的極大文本數(shù)據(jù)

|
|

LONGTEXT

|

0-4 294 967 295字節(jié)

|

極大文本數(shù)據(jù)

|

CHAR 和 VARCHAR 類型類似，但它們保存和檢索的方式不同。它們的最大長(zhǎng)度和是否尾部空格被保留等方面也不同。在存儲(chǔ)或檢索過(guò)程中不進(jìn)行大小寫(xiě)轉(zhuǎn)換。

BINARY 和 VARBINARY 類似于 CHAR 和 VARCHAR，不同的是它們包含二進(jìn)制字符串而不要非二進(jìn)制字符串。也就是說(shuō)，它們包含字節(jié)字符串而不是字符字符串。這說(shuō)明它們沒(méi)有字符集，并且排序和比較基于列值字節(jié)的數(shù)值值。

BLOB 是一個(gè)二進(jìn)制大對(duì)象，可以容納可變數(shù)量的數(shù)據(jù)。有 4 種 BLOB 類型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它們區(qū)別在于可容納存儲(chǔ)范圍不同。

有 4 種 TEXT 類型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對(duì)應(yīng)的這 4 種 BLOB 類型，可存儲(chǔ)的最大長(zhǎng)度不同，可根據(jù)實(shí)際情況選擇。

4 MySQL表完整性約束

• PRIMARY KEY (PK) 標(biāo)識(shí)該字段為該表的主鍵，可以唯一的標(biāo)識(shí)記錄，不可以為空 UNIQUE + NOT NULL
• FOREIGN KEY (FK) 標(biāo)識(shí)該字段為該表的外鍵，實(shí)現(xiàn)表與表之間的關(guān)聯(lián)
• NULL 標(biāo)識(shí)是否允許為空，默認(rèn)為NULL。
• NOT NULL 標(biāo)識(shí)該字段不能為空，可以修改。
• UNIQUE KEY (UK) 標(biāo)識(shí)該字段的值是唯一的，可以為空，一個(gè)表中可以有多個(gè)UNIQUE KEY
• AUTO_INCREMENT 標(biāo)識(shí)該字段的值自動(dòng)增長(zhǎng)（整數(shù)類型，而且為主鍵）
• DEFAULT 為該字段設(shè)置默認(rèn)值
• UNSIGNED 無(wú)符號(hào)，正數(shù)

5 MySQL 索引

5.1 MySQL 索引的分類

• 普通索引（INDEX）：索引列值可重復(fù)
• 唯一索引（UNIQUE）：索引列值必須唯一，可以為NULL
• 主鍵索引（PRIMARY KEY）：索引列值必須唯一，不能為NULL，一個(gè)表只能有一個(gè)主鍵索引
• 全文索引（FULL TEXT）：給每個(gè)字段創(chuàng)建索引

5.2 MySQL 不同類型索引用途和區(qū)別

• 普通索引常用于過(guò)濾數(shù)據(jù)。例如，以商品種類作為索引，檢索種類為“手機(jī)”的商品。
• 唯一索引主要用于標(biāo)識(shí)一列數(shù)據(jù)不允許重復(fù)的特性，相比主鍵索引不常用于檢索的場(chǎng)景。
• 主鍵索引是行的唯一標(biāo)識(shí)，因而其主要用途是檢索特定數(shù)據(jù)。
• 全文索引效率低，常用于文本中內(nèi)容的檢索。

5.3 MySQL 索引操作

5.3.1 創(chuàng)建索引

5.3.1.1 普通索引（INDEX）

<pre data-language="sql" id="IHGDa" class="ne-codeblock language-sql" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959"># 在創(chuàng)建表時(shí)指定
mysql> create table student1(id int not null, name varchar(100) not null, birthdy date, sex char(1) not null, index nameindex (name(50)));
Query OK, 0 rows affected (0.02 sec)

基于表結(jié)構(gòu)創(chuàng)建

mysql> create table student2(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> create index nameindex on student2(name(50));

修改表結(jié)構(gòu)創(chuàng)建

mysql> create table student3(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE student3 ADD INDEX nameIndex(name(50));

mysql> show index from student3; //查看某個(gè)表格中的索引</pre>

5.3.1.2 唯一索引（UNIQUE）

<pre data-language="sql" id="ln9Np" class="ne-codeblock language-sql" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959"># 在創(chuàng)建表時(shí)指定
mysql> create table student4(id int not null, name varchar(100) not null, birthday date, sex char(1) not null, unique index id_idex (id));
Query OK, 0 rows affected (0.00 sec)

基于表結(jié)構(gòu)創(chuàng)建

mysql> create table student5(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE unique INDEX idIndex ON student5(id);

基于表結(jié)構(gòu)創(chuàng)建

mysql> create table student18(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);

mysql> alter table student18 add unique index idIndex(id);
Query OK, 0 rows affected (0.02 sec)</pre>

5.3.1.3 主鍵索引（PRIMARY KEY）

<pre data-language="sql" id="agTAC" class="ne-codeblock language-sql" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959"># 創(chuàng)建表時(shí)時(shí)指定
mysql> create table student6(id int not null, name varchar(100) not null, birthday date, sex char(1) not null, primary key (id));
Query OK, 0 rows affected (0.01 sec)

修改表結(jié)構(gòu)創(chuàng)建

mysql> create table student7(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE student7 ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0</pre>

5.3.2 刪除索引

5.3.2.1 普通索引（INDEX）

<pre data-language="plain" id="52f4fbd7" class="ne-codeblock language-plain" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959"># 直接刪除
mysql> DROP INDEX nameIndex ON student1;

修改表結(jié)構(gòu)刪除

mysql> ALTER TABLE student2 DROP INDEX nameIndex;</pre>

5.3.2.2 唯一索引（UNIQUE）

<pre data-language="plain" id="bad7d820" class="ne-codeblock language-plain" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959"># 直接刪除
mysql> drop index id_idex on student4;

修改表結(jié)構(gòu)刪除

mysql> ALTER TABLE student5 DROP INDEX idIndex;</pre>

5.3.2.3 主鍵索引（PRIMARY KEY）

<pre data-language="sql" id="e3caa170" class="ne-codeblock language-sql" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959">mysql> ALTER TABLE student7 DROP PRIMARY KEY;</pre>

主鍵不能采用直接刪除的方式刪除。

5.3.3 查看索引

<pre data-language="sql" id="6b2d4e3d" class="ne-codeblock language-sql" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959">mysql> SHOW INDEX FROM tab_name;</pre>

5.4 選擇索引的原則

• 常用于查詢條件的字段較適合作為索引，例如WHERE語(yǔ)句和JOIN語(yǔ)句中出現(xiàn)的列
• 唯一性太差的字段不適合作為索引，例如性別,年齡
• 更新過(guò)于頻繁（更新頻率遠(yuǎn)高于檢索頻率）的字段不適合作為索引
• 使用索引的好處是索引通過(guò)一定的算法建立了索引值與列值直接的聯(lián)系，可以通過(guò)索引直接獲取對(duì)應(yīng)的行數(shù)據(jù)，而無(wú)需進(jìn)行全表搜索，因而加快了檢索速度
• 但由于索引也是一種數(shù)據(jù)結(jié)構(gòu)，它需要占據(jù)額外的內(nèi)存空間，并且讀取索引也加會(huì)大IO資源的消耗，因而索引并非越多越好，且對(duì)過(guò)小的表也沒(méi)有添加索引的必要

6 MySQL 數(shù)據(jù)備份與恢復(fù)

備份：能夠防止由于機(jī)械故障以及人為誤操作帶來(lái)的數(shù)據(jù)丟失，例如將數(shù)據(jù)庫(kù)文件保存在了其它地方。

冗余： 數(shù)據(jù)有多份冗余，但不等備份，只能防止機(jī)械故障帶來(lái)的數(shù)據(jù)丟失，例如主備模式、數(shù)據(jù)庫(kù)集群。

6.1 數(shù)據(jù)庫(kù)備份內(nèi)容

備份內(nèi)容 databases　Binlog my.cnf

所有備份數(shù)據(jù)都應(yīng)放在非數(shù)據(jù)庫(kù)本地，而且建議有多份副本。

測(cè)試環(huán)境中做日?；謴?fù)演練，恢復(fù)較備份更為重要。

6.2 數(shù)據(jù)庫(kù)備份類型

<pre data-language="sql" id="rmW8X" class="ne-codeblock language-sql" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959">1.物理備份： 直接復(fù)制數(shù)據(jù)庫(kù)文件，適用于大型數(shù)據(jù)庫(kù)環(huán)境，不受存儲(chǔ)引擎的限制，但不能恢復(fù)到不同的MySQL版本。

1、熱備(hot backup)

在線備份，數(shù)據(jù)庫(kù)處于運(yùn)行狀態(tài)，這種備份方法依賴于數(shù)據(jù)庫(kù)的日志文件

• 對(duì)應(yīng)用基本無(wú)影響(但是性能還是會(huì)有下降,所以盡量不要在主庫(kù)上做備份,在從庫(kù)上做)

2、冷備(cold backup)

備份數(shù)據(jù)文件,需要停機(jī)，是在關(guān)閉數(shù)據(jù)庫(kù)的時(shí)候進(jìn)行的

• 備份 datadir 目錄下的所有文件

3、溫備(warm backup)

• 針對(duì)myisam的備份(myisam不支持熱備),備份時(shí)候?qū)嵗蛔x不可寫(xiě)，數(shù)據(jù)庫(kù)鎖定表格（不可寫(xiě)入但可讀）的狀態(tài)下進(jìn)行的
• 對(duì)應(yīng)用影響很大
• 通常加一個(gè)讀鎖

2.邏輯備份： 備份的是建表、建庫(kù)、插入等操作所執(zhí)行SQL語(yǔ)句（DDL DML DCL），適用于中小型數(shù)據(jù)庫(kù)，效率相對(duì)較低。

3.物理和邏輯備份的區(qū)別

6.3 MySQL 備份工具

6.3.1 ibbackup

• 官方備份工具
• 收費(fèi)
• 物理備份

6.3.2 xtrabackup

• 開(kāi)源社區(qū)備份工具
• 開(kāi)源免費(fèi),上面的免費(fèi)版本(老版本有問(wèn)題,備份出來(lái)的數(shù)據(jù)可能有問(wèn)題)
• 物理備份

6.3.3 mysqldump

• 官方自帶備份工具 開(kāi)源免費(fèi)
• 邏輯備份(速度慢)

6.3.4 mysqlbackup

• mysql 官方備份工具
• innodb 引擎的表mysqlbackup可以進(jìn)行熱備
• 非innodb表mysqlbackup就只能溫備
• 物理備份，備份還原速度快
• 適合大規(guī)模數(shù)據(jù)使用

6.4 備份數(shù)據(jù)庫(kù)

語(yǔ)法：*mysqldump -h 主機(jī) -P 端口 -u 用戶名 -p 密碼 --databases 數(shù)據(jù)庫(kù) > 文件名.sql*

mysqldump命令參數(shù)

|

參數(shù)

|

描述

|

備注

|
|

-h

|

目標(biāo)主機(jī)

|

可選參數(shù)

|
|

-P

|

端口

|

可選參數(shù)，默認(rèn)3306

|
|

-u

|

用戶名

| |
|

-p

|

密碼

| |
|

-database

|

指定單個(gè)數(shù)據(jù)庫(kù)

|

高版本可能會(huì)廢棄，使用databases替代

|
|

--databases

|

指定多個(gè)數(shù)據(jù)庫(kù)

|

導(dǎo)出SQL文件支持庫(kù)操作語(yǔ)法，多個(gè)庫(kù)之間使用空格分隔

|
|

--socket

|

mysql.sock路徑

|

執(zhí)行命令提示sock錯(cuò)誤時(shí)，通過(guò)netstat命令獲取mysql.sock路徑

|
|

--add-drop-table

|

表操作語(yǔ)法

|

可省略，使用--databases參數(shù)即可

|
|

|

備份文件

|

指定備份文件路勁及文件名

|

6.4.1 普通備份

導(dǎo)出可執(zhí)行SQL腳本文件

<pre data-language="sql" id="EoN02" class="ne-codeblock language-sql" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959">mysqldump -uroot -proot --databases db_test > db_back.sql</pre>

6.4.2 壓縮備份

導(dǎo)出GZIP壓縮格式SQL腳本文件

<pre data-language="sql" id="re4WH" class="ne-codeblock language-sql" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959">mysqldump -uroot -proot --databases db_test | gzip > db_back.sql.gz</pre>

6.4.3 多庫(kù)備份

多個(gè)庫(kù)腳本導(dǎo)出到一個(gè)SQL文件，多個(gè)庫(kù)之間使用空格分隔

<pre data-language="sql" id="wGb00" class="ne-codeblock language-sql" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959">mysqldump -uroot -proot --databases db_test db_test1 > db_back.sql</pre>

6.4.4 多表備份

備份數(shù)據(jù)庫(kù)中的指定表，多個(gè)表之間使用空格分隔

<pre data-language="sql" id="UL8E4" class="ne-codeblock language-sql" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959">mysqldump -uroot -proot db_test tb_user tb_admin > back_user_admin.sql</pre>

6.4.5 備份所有庫(kù)

備份服務(wù)器上所有數(shù)據(jù)庫(kù)

<pre data-language="sql" id="T8v8e" class="ne-codeblock language-sql" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959">mysqldump -uroot -proot --all-databases > db_back.sql</pre>

6.4.6 備份表結(jié)構(gòu)

僅備份表結(jié)構(gòu)，不備份數(shù)據(jù)

<pre data-language="sql" id="GtUIK" class="ne-codeblock language-sql" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959">mysqldump -uroot -proot --no-data --databases db_test > db_back.sql</pre>

6.5 還原數(shù)據(jù)庫(kù)

語(yǔ)法：*mysql -h 主機(jī) -P 端口 -u 用戶名 -p 密碼 數(shù)據(jù)庫(kù)名稱 < SQL文件*

6.5.1 普通還原

還原原備份SQL文件，如果數(shù)據(jù)庫(kù)不存在需要先創(chuàng)建

<pre data-language="sql" id="Uw67o" class="ne-codeblock language-sql" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959">mysql -uroot -proot db_test < db_back.sql</pre>

6.5.2 數(shù)據(jù)庫(kù)還原

使用source命令還原數(shù)據(jù)庫(kù)，不需要先創(chuàng)建數(shù)據(jù)庫(kù)，首先使用mysql命令登陸mysql，然后執(zhí)行source命令還原數(shù)據(jù)庫(kù)

<pre data-language="sql" id="p7rjo" class="ne-codeblock language-sql" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959">source /home/data/db_back.sql</pre>

6.5.3 GZIP還原

還原GZIP壓縮SQL文件，如果數(shù)據(jù)庫(kù)不存在需要先創(chuàng)建

<pre data-language="sql" id="Dtt9o" class="ne-codeblock language-sql" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959">gunzip < db_back.sql.gz | mysql -uroot -proot db_test</pre>