1 數(shù)據(jù)庫介紹
1.1 什么是數(shù)據(jù)庫
數(shù)據(jù)庫就是一個存放計算機數(shù)據(jù)的倉庫，這個倉庫是按照一定的數(shù)據(jù)結(jié)構(gòu)（數(shù)據(jù)結(jié)構(gòu)是指數(shù)據(jù)的組織形式或數(shù)據(jù)之間的聯(lián)系）來對數(shù)據(jù)進(jìn)行組織和存儲的，可以通過數(shù)據(jù)庫提供的多種方法來管理其中的數(shù)據(jù)。
1.2 數(shù)據(jù)庫的種類
關(guān)系型數(shù)據(jù)庫和非關(guān)系型數(shù)據(jù)庫。
1.3 常用數(shù)據(jù)庫
- 關(guān)系型數(shù)據(jù)庫： Oracle钩骇、Microsoft SQL Server、MySQL/MariaDB等铝量。
- 非關(guān)系型數(shù)據(jù)庫： MongoDB Memcached Redis
1.4倘屹、關(guān)系型數(shù)據(jù)庫
關(guān)系型數(shù)據(jù)庫模型是把復(fù)雜的數(shù)據(jù)結(jié)構(gòu)歸結(jié)為簡單的二元關(guān)系（即二維表格形式）。在關(guān)系型數(shù)據(jù)庫中慢叨，對數(shù)據(jù)的操作幾乎全部建立在一個或多個關(guān)系表格上纽匙，通過這些關(guān)聯(lián)的表格分類、合并插爹、連接或選取等運算來實現(xiàn)數(shù)據(jù)的管理哄辣。 關(guān)系型數(shù)據(jù)可以很好地存儲一些關(guān)系模型的數(shù)據(jù)，比如一個老師對應(yīng)多個學(xué)生的數(shù)據(jù)（“一對多”）赠尾，一本書對應(yīng)多個作者（“一對多”）力穗，一本書對應(yīng)一個出版日期（“一對一”） 關(guān)系型數(shù)據(jù)庫誕生距今已有 40 多年了，從理論產(chǎn)生到發(fā)展到實現(xiàn)產(chǎn)品气嫁，例如：常見的 MySQL 和 Oracle 數(shù)據(jù)庫当窗，Oracle 在數(shù)據(jù)庫領(lǐng)域里上升到了霸主地位，形成每年高達(dá)數(shù)百億美元的龐大產(chǎn)業(yè)市場寸宵，而 MySQL 也是不容忽視的數(shù)據(jù)庫崖面，以至于被 Oracle 重金收購了元咙。
1.5、非關(guān)系型數(shù)據(jù)庫
非關(guān)系型數(shù)據(jù)庫也被稱為 NoSQL 數(shù)據(jù)庫巫员，NoSQL 的本意是 “Not Only SQL”庶香，指的是非關(guān)系型數(shù)據(jù)庫，而不是“NO SQL”的意思简识，NoSQL 的產(chǎn)生并不是要徹底否定關(guān)系型數(shù)據(jù)庫赶掖，而是作為傳統(tǒng)數(shù)據(jù)庫的一個有效補充。NoSQL 數(shù)據(jù)庫在特定的場景下可以發(fā)揮難以想象的高效率和高性能七扰。特別是對于規(guī)模日益擴大的海量數(shù)據(jù)奢赂，超大規(guī)模和高并發(fā)的微博、微信颈走、SNS 類型的純動態(tài)網(wǎng)站已經(jīng)顯得力不從心膳灶，暴露了很多難以克服的問題，例如：傳統(tǒng)的關(guān)系型數(shù)據(jù)庫IO瓶頸立由、性能瓶頸都難以有效突破轧钓，于是開始出現(xiàn)了大批針對特定場景，以高性能和使用便利為目的功能特異化的數(shù)據(jù)庫產(chǎn)品拆吆。NoSQL（非關(guān)系型）類的數(shù)據(jù)庫就是這樣的情景中誕生并得到了非常迅速的發(fā)展聋迎。
NoSQL 是非關(guān)系型數(shù)據(jù)庫的廣義定義脂矫。它打破了長久以來關(guān)系型數(shù)據(jù)庫與ACID理論大一統(tǒng)的局面渤闷。NoSQL數(shù)據(jù)存儲不需要固定的表結(jié)構(gòu)阱冶，通常也不存在連續(xù)操作。
1.鍵值（Key-Value）存儲數(shù)據(jù)庫
- 鍵值數(shù)據(jù)庫就類似傳統(tǒng)語言中使用的哈希表∽淼可以通過key來添加、查詢或者刪除數(shù)據(jù)婚度，因為使用key主鍵訪問皱卓，所以會獲得很高的性能及擴展性。這個表中有一個特定的鍵和一個指針指向特定的數(shù)據(jù)恨搓。Key-Value模型對于IT系統(tǒng)來說的優(yōu)勢在于簡單院促、易部署、高并發(fā)斧抱。
- 典型產(chǎn)品：Memcached常拓、Redis、MemcachedB
2辉浦、列存儲（Column-Oriented）數(shù)據(jù)庫
- 列存儲數(shù)據(jù)庫將數(shù)據(jù)存儲存在列族（Column Family）中弄抬，一個列族存儲經(jīng)常被一起查詢的相關(guān)數(shù)據(jù)。舉個例子宪郊，如果有一個 Person 類掂恕，通常會一起查詢他們的姓名和年齡而不是薪資拖陆。這種情況下，姓名和年齡就會被放入一個列族中懊亡，而薪資則在另一個列族中依啰。這部分?jǐn)?shù)據(jù)庫通常用來應(yīng)對分布式存儲的海量數(shù)據(jù)。鍵仍然存在店枣，但是他們的特點是指向了多個列孔飒。這些列是由列家族來安排的。
- 典型產(chǎn)品：Cassandra艰争，HBase
3坏瞄、面向文檔（Document-Oriented）的數(shù)據(jù)庫
- 面向文檔數(shù)據(jù)庫會將數(shù)據(jù)以文檔的形式存儲,數(shù)據(jù)存儲的最小單位是文檔，同一個表中存儲的文檔屬性可以是不同的甩卓，數(shù)據(jù)可以使用XML鸠匀、JSON等多種形式存儲。
- 典型產(chǎn)品：MongDB逾柿、CouchDB
4缀棍、圖形（Graph）數(shù)據(jù)庫
- 圖形數(shù)據(jù)庫允許我們將數(shù)據(jù)以圖的方式存儲。圖形結(jié)構(gòu)的數(shù)據(jù)庫同其他行列以及剛性結(jié)構(gòu)的 SQL 數(shù)據(jù)庫不同机错，它是使用靈活的圖形模型爬范，并且能夠擴展到多個服務(wù)器上。
- 典型產(chǎn)品：Neo4J弱匪、InfoGr id
2 MySQL數(shù)據(jù)庫
2.1 MySQL簡介
MySQL是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)青瀑，由瑞典MySQL AB 公司開發(fā)，目前屬于 Oracle 旗下產(chǎn)品萧诫。MySQL 是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一斥难，在 WEB 應(yīng)用方面，MySQL是最好的 RDBMS (Relational Database Management System帘饶，關(guān)系數(shù)據(jù)庫管理系統(tǒng)) 應(yīng)用軟件之一哑诊。關(guān)系數(shù)據(jù)庫將數(shù)據(jù)保存在不同的表中，而不是將所有數(shù)據(jù)放在一個大倉庫內(nèi)及刻，這樣就增加了速度并提高了靈活性镀裤。
MySQL所使用的 SQL 語言是用于訪問數(shù)據(jù)庫的最常用標(biāo)準(zhǔn)化語言。MySQL 軟件采用了雙授權(quán)政策缴饭，分為社區(qū)版和商業(yè)版暑劝，由于其體積小、速度快茴扁、總體擁有成本低铃岔，尤其是開放源碼這一特點，一般中小型網(wǎng)站的開發(fā)都選擇 MySQL 作為網(wǎng)站數(shù)據(jù)庫。
RDBMS即關(guān)系數(shù)據(jù)庫管理系統(tǒng)(Relational Database Management System)
2.2 MySQL安裝方式
略
2.3 MySQL數(shù)據(jù)庫存儲引擎
數(shù)據(jù)庫存儲引擎是數(shù)據(jù)庫底層軟件組織毁习，數(shù)據(jù)庫管理系統(tǒng)（DBMS）使用數(shù)據(jù)引擎進(jìn)行創(chuàng)建智嚷、查詢、更新和刪除數(shù)據(jù)纺且。不同的存儲引擎提供不同的存儲機制盏道、索引、鎖表等功能载碌，使用不同的存儲引擎猜嘱，還可以 獲得特定的功能。現(xiàn)在許多不同的數(shù)據(jù)庫管理系統(tǒng)都支持多種不同的數(shù)據(jù)引擎嫁艇。MySQL的核心就是存儲引擎朗伶。
1、InnoDB存儲引擎:默認(rèn)引擎步咪，最常用的论皆。 InnoDB是事務(wù)型數(shù)據(jù)庫的首選引擎，支持事務(wù)安全表（ACID）猾漫，支持行鎖定;InnoDB是默認(rèn)的MySQL引擎
InnoDB特點： 支持事務(wù)處理点晴，支持崩潰修復(fù)和并發(fā)控制。如果需要對事務(wù)的完整性要求比較高（比如銀行）悯周，要求實現(xiàn)并發(fā)控制（比如售票）粒督，那選擇InnoDB有很大的優(yōu)勢。如果需要頻繁的更新禽翼、刪除操作的數(shù)據(jù)庫屠橄，也可以選擇InnoDB，因為支持事務(wù)的提交（commit）和回滾（rollback）捐康。
2仇矾、MyISAM存儲引擎：（了解）MyISAM基于ISAM存儲引擎，并對其進(jìn)行擴展解总。它是在Web、數(shù)據(jù)倉儲和其他應(yīng)用環(huán)境下最常使用的存儲引擎之一姐仅。MyISAM擁有較高的插入花枫、查詢速度，但不支持事務(wù)掏膏。
MyISAM特點： 插入數(shù)據(jù)快劳翰，空間和內(nèi)存使用比較低。如果表主要是用于插入新記錄和讀出記錄馒疹，那么選擇MyISAM能實現(xiàn)處理高效率佳簸。如果應(yīng)用的完整性、并發(fā)性要求比較低，也可以使用生均。
12306查詢 只生成一條數(shù)據(jù)這種適合听想。
3、MEMORY內(nèi)存型引擎(了解)MEMORY存儲引擎將表中的數(shù)據(jù)存儲到內(nèi)存中马胧，為查詢和引用其他表數(shù)據(jù)提供快速訪問
MEMORY特點： 所有的數(shù)據(jù)都在內(nèi)存中汉买，數(shù)據(jù)的處理速度快，但是安全性不高佩脊。如果需要很快的讀寫速度蛙粘，對數(shù)據(jù)的安全性要求較低，可以選擇MEMOEY威彰。它對表的大小有要求出牧，不能建立太大的表。所以歇盼，這類數(shù)據(jù)庫只使用在相對較小的數(shù)據(jù)庫表崔列。
4、Archive(歸檔引擎)
如何選擇引擎：如果要提供提交旺遮、回滾赵讯、并要求實現(xiàn)并發(fā)控制，InnoDB是一個好的選擇耿眉；如果數(shù)據(jù)表主要用來插入和查詢記錄边翼，則MyISAM引擎能提供較高的處理效率；如果只是臨時存放數(shù)據(jù)鸣剪，數(shù)據(jù)量不大组底，并且不需要較高的數(shù)據(jù)安全性，可以選擇將數(shù)據(jù)保存在內(nèi)存中的Memory引擎筐骇；MySQL中使用該引擎作為臨時表债鸡，存放查詢的中間結(jié)果；
使用哪一種引擎需要靈活選擇铛纬，一個數(shù)據(jù)庫中多個表可以使用不同引擎以滿足各種性能和實際需求厌均，使用合適的存儲引擎，將會提高整個數(shù)據(jù)庫的性能告唆。
3 MySQL數(shù)據(jù)類型
MySQL支持多種類型棺弊，大致可以分為三類：數(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)的擴展溺忧，MySQL也支持整數(shù)類型TINYINT咏连、MEDIUMINT和BIGINT。下面的表顯示了需要的每個整數(shù)類型的存儲和范圍鲁森。
|
類型
|
大小
|
范圍（有符號）
|
范圍（無符號）
|
用途
|
|
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)
|
單精度 浮點數(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)
|
雙精度 浮點數(shù)值
|
|
DECIMAL
|
對DECIMAL(M,D) ，如果M>D姻蚓，為M+2否則為D+2
|
依賴于M和D的值
|
依賴于M和D的值
|
小數(shù)值
|
3.2 MySQL日期和時間類型
表示時間值的日期和時間類型為DATETIME、DATE匣沼、TIMESTAMP狰挡、TIME和YEAR。
每個時間類型有一個有效值范圍和一個"零"值，當(dāng)指定不合法的MySQL不能表示的值時使用"零"值加叁。
TIMESTAMP類型有專有的自動更新特性倦沧，將在后面描述。
|
類型
|
大小(字節(jié))
|
范圍
|
格式
|
用途
|
|
DATE
|
3
|
1000-01-01/9999-12-31
|
YYYY-MM-DD
|
日期值
|
|
TIME
|
3
|
'-838:59:59'/'838:59:59'
|
HH:MM:SS
|
時間值或持續(xù)時間
|
|
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
|
混合日期和時間值
|
|
TIMESTAMP
|
4
|
1970-01-01 00:00:00/2038
結(jié)束時間是第 2147483647 秒它匕，北京時間 2038-1-19 11:14:07展融，格林尼治時間 2038年1月19日 凌晨 03:14:07
|
YYYYMMDD HHMMSS
|
混合日期和時間值，時間戳
|
3.3 MySQL字符串類型
字符串類型指CHAR豫柬、VARCHAR告希、BINARY、VARBINARY烧给、BLOB燕偶、TEXT、ENUM和SET础嫡。該節(jié)描述了這些類型如何工作以及如何在查詢中使用這些類型指么。
|
類型
|
大小
|
用途
|
|
CHAR
|
0-255字節(jié)
|
定長字符串
|
|
VARCHAR
|
0-65535 字節(jié)
|
變長字符串
|
|
TINYBLOB
|
0-255字節(jié)
|
不超過 255 個字符的二進(jìn)制字符串
|
|
TINYTEXT
|
0-255字節(jié)
|
短文本字符串
|
|
BLOB
|
0-65 535字節(jié)
|
二進(jìn)制形式的長文本數(shù)據(jù)
|
|
TEXT
|
0-65 535字節(jié)
|
長文本數(shù)據(jù)
|
|
MEDIUMBLOB
|
0-16 777 215字節(jié)
|
二進(jìn)制形式的中等長度文本數(shù)據(jù)
|
|
MEDIUMTEXT
|
0-16 777 215字節(jié)
|
中等長度文本數(shù)據(jù)
|
|
LONGBLOB
|
0-4 294 967 295字節(jié)
|
二進(jìn)制形式的極大文本數(shù)據(jù)
|
|
LONGTEXT
|
0-4 294 967 295字節(jié)
|
極大文本數(shù)據(jù)
|
CHAR 和 VARCHAR 類型類似，但它們保存和檢索的方式不同榴鼎。它們的最大長度和是否尾部空格被保留等方面也不同伯诬。在存儲或檢索過程中不進(jìn)行大小寫轉(zhuǎn)換。
BINARY 和 VARBINARY 類似于 CHAR 和 VARCHAR巫财，不同的是它們包含二進(jìn)制字符串而不要非二進(jìn)制字符串盗似。也就是說，它們包含字節(jié)字符串而不是字符字符串翁涤。這說明它們沒有字符集桥言，并且排序和比較基于列值字節(jié)的數(shù)值值。
BLOB 是一個二進(jìn)制大對象葵礼，可以容納可變數(shù)量的數(shù)據(jù)号阿。有 4 種 BLOB 類型：TINYBLOB、BLOB鸳粉、MEDIUMBLOB 和 LONGBLOB扔涧。它們區(qū)別在于可容納存儲范圍不同。
有 4 種 TEXT 類型：TINYTEXT届谈、TEXT枯夜、MEDIUMTEXT 和 LONGTEXT。對應(yīng)的這 4 種 BLOB 類型艰山，可存儲的最大長度不同湖雹，可根據(jù)實際情況選擇。
4 MySQL表完整性約束
- PRIMARY KEY (PK) 標(biāo)識該字段為該表的主鍵曙搬，可以唯一的標(biāo)識記錄摔吏，不可以為空 UNIQUE + NOT NULL
- FOREIGN KEY (FK) 標(biāo)識該字段為該表的外鍵鸽嫂，實現(xiàn)表與表之間的關(guān)聯(lián)
- NULL 標(biāo)識是否允許為空，默認(rèn)為NULL征讲。
- NOT NULL 標(biāo)識該字段不能為空据某，可以修改。
- UNIQUE KEY (UK) 標(biāo)識該字段的值是唯一的诗箍，可以為空癣籽，一個表中可以有多個UNIQUE KEY
- AUTO_INCREMENT 標(biāo)識該字段的值自動增長（整數(shù)類型，而且為主鍵）
- DEFAULT 為該字段設(shè)置默認(rèn)值
- UNSIGNED 無符號滤祖，正數(shù)
5 MySQL 索引
5.1 MySQL 索引的分類
- 普通索引（INDEX）：索引列值可重復(fù)
- 唯一索引（UNIQUE）：索引列值必須唯一筷狼，可以為NULL
- 主鍵索引（PRIMARY KEY）：索引列值必須唯一，不能為NULL氨距，一個表只能有一個主鍵索引
- 全文索引（FULL TEXT）：給每個字段創(chuàng)建索引
5.2 MySQL 不同類型索引用途和區(qū)別
- 普通索引常用于過濾數(shù)據(jù)桑逝。例如，以商品種類作為索引俏让，檢索種類為“手機”的商品楞遏。
- 唯一索引主要用于標(biāo)識一列數(shù)據(jù)不允許重復(fù)的特性，相比主鍵索引不常用于檢索的場景首昔。
- 主鍵索引是行的唯一標(biāo)識寡喝，因而其主要用途是檢索特定數(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)建表時指定
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; //查看某個表格中的索引</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)建表時指定
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)建表時時指定
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語句和JOIN語句中出現(xiàn)的列
- 唯一性太差的字段不適合作為索引格二，例如性別,年齡
- 更新過于頻繁（更新頻率遠(yuǎn)高于檢索頻率）的字段不適合作為索引
- 使用索引的好處是索引通過一定的算法建立了索引值與列值直接的聯(lián)系，可以通過索引直接獲取對應(yīng)的行數(shù)據(jù)竣蹦，而無需進(jìn)行全表搜索顶猜，因而加快了檢索速度
- 但由于索引也是一種數(shù)據(jù)結(jié)構(gòu)，它需要占據(jù)額外的內(nèi)存空間痘括，并且讀取索引也加會大IO資源的消耗长窄，因而索引并非越多越好，且對過小的表也沒有添加索引的必要
6 MySQL 數(shù)據(jù)備份與恢復(fù)
備份：能夠防止由于機械故障以及人為誤操作帶來的數(shù)據(jù)丟失纲菌，例如將數(shù)據(jù)庫文件保存在了其它地方挠日。
冗余： 數(shù)據(jù)有多份冗余，但不等備份翰舌，只能防止機械故障帶來的數(shù)據(jù)丟失嚣潜，例如主備模式、數(shù)據(jù)庫集群椅贱。
6.1 數(shù)據(jù)庫備份內(nèi)容
備份內(nèi)容 databases Binlog my.cnf
所有備份數(shù)據(jù)都應(yīng)放在非數(shù)據(jù)庫本地郑原，而且建議有多份副本唉韭。
測試環(huán)境中做日骋固椋恢復(fù)演練犯犁，恢復(fù)較備份更為重要。
6.2 數(shù)據(jù)庫備份類型
<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ù)庫文件女器，適用于大型數(shù)據(jù)庫環(huán)境酸役，不受存儲引擎的限制，但不能恢復(fù)到不同的MySQL版本驾胆。
1涣澡、熱備(hot backup)
在線備份，數(shù)據(jù)庫處于運行狀態(tài)丧诺，這種備份方法依賴于數(shù)據(jù)庫的日志文件
- 對應(yīng)用基本無影響(但是性能還是會有下降,所以盡量不要在主庫上做備份,在從庫上做)
2入桂、冷備(cold backup)
備份數(shù)據(jù)文件,需要停機，是在關(guān)閉數(shù)據(jù)庫的時候進(jìn)行的
- 備份 datadir 目錄下的所有文件
3驳阎、溫備(warm backup)
- 針對myisam的備份(myisam不支持熱備),備份時候?qū)嵗蛔x不可寫抗愁，數(shù)據(jù)庫鎖定表格（不可寫入但可讀）的狀態(tài)下進(jìn)行的
- 對應(yīng)用影響很大
- 通常加一個讀鎖
2.邏輯備份： 備份的是建表、建庫呵晚、插入等操作所執(zhí)行SQL語句（DDL DML DCL）蜘腌，適用于中小型數(shù)據(jù)庫，效率相對較低饵隙。
3.物理和邏輯備份的區(qū)別
| 邏輯備份 | 物理備份 | |
|---|---|---|
| 備份方式 | 備份數(shù)據(jù)庫建表撮珠、建庫、插入sql語句 | 備份數(shù)據(jù)庫物理文件 |
| 優(yōu)點 | 備份文件相對較小,只備份表中的數(shù)據(jù)與結(jié)構(gòu) | 恢復(fù)速度比較快 |
| 缺點 | 恢復(fù)速度較慢(需要重建索引,存儲過程等) | 備份文件相對較大(備份表空間,包含數(shù)據(jù)與索引) |
| 對業(yè)務(wù)影響 | I/O負(fù)載加大 | I/O負(fù)載加大 |
| 代表工具 | mysqldump | ibbackup金矛、xtrabackup芯急，mysqlbackup</pre> |
6.3 MySQL 備份工具
6.3.1 ibbackup
- 官方備份工具
- 收費
- 物理備份
6.3.2 xtrabackup
- 開源社區(qū)備份工具
- 開源免費,上面的免費版本(老版本有問題,備份出來的數(shù)據(jù)可能有問題)
- 物理備份
6.3.3 mysqldump
- 官方自帶備份工具 開源免費
- 邏輯備份(速度慢)
6.3.4 mysqlbackup
- mysql 官方備份工具
- innodb 引擎的表mysqlbackup可以進(jìn)行熱備
- 非innodb表mysqlbackup就只能溫備
- 物理備份，備份還原速度快
- 適合大規(guī)模數(shù)據(jù)使用
6.4 備份數(shù)據(jù)庫
語法：*mysqldump -h 主機 -P 端口 -u 用戶名 -p 密碼 --databases 數(shù)據(jù)庫 > 文件名.sql*
mysqldump命令參數(shù)
|
參數(shù)
|
描述
|
備注
|
|
-h
|
目標(biāo)主機
|
可選參數(shù)
|
|
-P
|
端口
|
可選參數(shù)驶俊，默認(rèn)3306
|
|
-u
|
用戶名
| |
|
-p
|
密碼
| |
|
-database
|
指定單個數(shù)據(jù)庫
|
高版本可能會廢棄娶耍，使用databases替代
|
|
--databases
|
指定多個數(shù)據(jù)庫
|
導(dǎo)出SQL文件支持庫操作語法，多個庫之間使用空格分隔
|
|
--socket
|
mysql.sock路徑
|
執(zhí)行命令提示sock錯誤時废睦，通過netstat命令獲取mysql.sock路徑
|
|
--add-drop-table
|
表操作語法
|
可省略伺绽，使用--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 多庫備份
多個庫腳本導(dǎo)出到一個SQL文件，多個庫之間使用空格分隔
<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ù)庫中的指定表嗜湃，多個表之間使用空格分隔
<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 備份所有庫
備份服務(wù)器上所有數(shù)據(jù)庫
<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ù)庫
語法：*mysql -h 主機 -P 端口 -u 用戶名 -p 密碼 數(shù)據(jù)庫名稱 < SQL文件*
6.5.1 普通還原
還原原備份SQL文件，如果數(shù)據(jù)庫不存在需要先創(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ù)庫還原
使用source命令還原數(shù)據(jù)庫购披，不需要先創(chuàng)建數(shù)據(jù)庫杖挣，首先使用mysql命令登陸mysql，然后執(zhí)行source命令還原數(shù)據(jù)庫
<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ù)庫不存在需要先創(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>
