【7天玩轉(zhuǎn)MySQL】華為云RDS for MySQL學(xué)習(xí)筆記 Day02 【7天玩轉(zhuǎn)MySQL】華為云RDS for MySQL學(xué)習(xí)筆記 Day01

【7天玩轉(zhuǎn)Mysql】華為云RDS for Mysql學(xué)習(xí)筆記? Day02

第二天的內(nèi)容主要是：表和索引

目標(biāo)：了解MySQL的數(shù)據(jù)存儲(chǔ)方式、數(shù)據(jù)訪問及數(shù)據(jù)更新機(jī)制，如何定義表結(jié)構(gòu)以有效利用空間；了解如何建立合適的索引以提高對(duì)數(shù)據(jù)整體使用的性能；了解建立索引需要的注意事項(xiàng)等。

一、數(shù)據(jù)庫--庫

庫（Database或者Schema）是一系列相關(guān)的數(shù)據(jù)庫對(duì)象的集合

二、數(shù)據(jù)庫--表

---摘自維基百科：數(shù)據(jù)庫表

數(shù)據(jù)庫表的基本構(gòu)成包括：表屬性、字段、數(shù)據(jù)類型、類型描述

MySQL 支持五大數(shù)據(jù)類型：

數(shù)值型，如 BIGINT,FLOAT 和 DECIMAL 等

日期和時(shí)間類型，如 DATE ， TIME 和 TIMESTAMP 等

字符串類型，如VARCHAR,CHAR 和 BLOB 等

空間數(shù)據(jù)類型，如 GEOMETRY， POINT 和 POLYGON 等

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

數(shù)據(jù)類型的選擇取決于字段存儲(chǔ)的具體數(shù)據(jù)：

確保正確存儲(chǔ)數(shù)據(jù)

空間利用最優(yōu)

同一個(gè)數(shù)據(jù)可能有多種存儲(chǔ)方式，選擇最合適的方式：

如 phone_number ，為什么選擇 VARCHAR （20）?

字段數(shù)限制

MySQL 允許每張表支持最多 4096 個(gè)字段

InnoDB 對(duì)每張表最多字段限制是 1017 個(gè)字段

不建議一個(gè)表包含過多的字段，影響整體的使用體驗(yàn)

行長(zhǎng)度限制

MySQL 允許每條記錄最長(zhǎng)達(dá)到 65535 字節(jié)——不考慮大對(duì)象字段

InnoDB 限制每條記錄的長(zhǎng)度不超過 8K （基于華為云 RDS 配置）

對(duì)于超過長(zhǎng)度的場(chǎng)景，也可以處理，但是影響性能

按需定義字段類型，按需存儲(chǔ)，縮減行數(shù)據(jù)占用空間

分區(qū)表

支持多種分區(qū)方式

RANGE/LIST/COLUMNS/KEY/HASH

支持子分區(qū)

支持分區(qū)操作

添加 / 刪除 / 重整 / 合并 / 交換等操作

分區(qū)表和普通表可以相互轉(zhuǎn)換

支持對(duì)指定分區(qū)進(jìn)行查詢

不是銀彈

性能考慮

功能考慮

上圖分區(qū)表創(chuàng)建后分區(qū)效果如下圖所示：

三、數(shù)據(jù)庫--索引

1）索引是什么

維基百科上對(duì)于索引的定義如下：

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

翻譯如下：

數(shù)據(jù)庫索引是一種數(shù)據(jù)結(jié)構(gòu)，它可以提高對(duì)數(shù)據(jù)庫表的數(shù)據(jù)檢索操作的速度，但要維護(hù)索引數(shù)據(jù)結(jié)構(gòu)，需要額外的寫入和存儲(chǔ)空間。索引用于快速定位數(shù)據(jù)，而不必每次訪問數(shù)據(jù)庫表時(shí)都搜索數(shù)據(jù)庫表中的每一行。可以使用數(shù)據(jù)庫表的一個(gè)或多個(gè)列來創(chuàng)建索引，為快速隨機(jī)查找和高效訪問有序記錄提供了基礎(chǔ)。

MySQL官方對(duì)索引的定義為：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。提取句子主干，就可以得到索引的本質(zhì)：索引是數(shù)據(jù)結(jié)構(gòu)。

2）Btree索引

BTree（多路搜索樹，并不是二叉的）是一種常見的數(shù)據(jù)結(jié)構(gòu)。使用BTree結(jié)構(gòu)可以顯著減少定位記錄時(shí)所經(jīng)歷的中間過程，從而加快存取速度。按照翻譯，B 通常認(rèn)為是Balance的簡(jiǎn)稱。這個(gè)數(shù)據(jù)結(jié)構(gòu)一般用于數(shù)據(jù)庫的索引，綜合效率較高。

什么情況下使用Btree？

全值匹配的查詢

匹配最左前綴的查詢

匹配列前綴查詢

范圍查找

精確匹配左前列并范圍匹配另外一列

只訪問索引的查詢

Btree有什么限制：

如果不是按照索引最左列開始查找，那么無法使用索引。

使用索引時(shí)不能跳過索引中的列。

not in、<>、!=操作無法使用索引。

如果查詢中有某個(gè)列的范圍查詢，則其右邊的所有列都無法使用索引。

3）聚簇索引

聚簇索引也叫簇類索引，是一種對(duì)磁盤上實(shí)際數(shù)據(jù)重新組織以按指定的一個(gè)或多個(gè)列的值排序。由于聚簇索引的索引頁面指針指向數(shù)據(jù)頁面，所以使用聚簇索引查找數(shù)據(jù)幾乎總是比使用非聚簇索引快。每張表只能建一個(gè)聚簇索引，并且建聚簇索引需要至少相當(dāng)該表120%的附加空間，以存放該表的副本和索引中間頁。

聚簇索引的特點(diǎn)：

聚簇索引就是主鍵索引(Primary Key);

聚簇索引/主鍵索引只能一張表一個(gè);

即使表沒有定義主鍵索引，聚簇索引也存在;

聚簇索引一定是 Btree 索引;

聚簇索引直接影響了數(shù)據(jù)的排序;

索引的鍵值必須是唯一的;

聚簇索引生成示意圖：

4）二級(jí)索引

同一張表上非主鍵（聚簇）索引即為二級(jí)索引，二級(jí)索引可以有多個(gè)，按照索引特性可以分為唯一索引和非唯一索引

下圖展示了二級(jí)索引與聚簇索引的關(guān)聯(lián)：

聚簇索引與其說是索引，不如說是InnoDB用來存儲(chǔ)記錄的數(shù)據(jù)容器更為恰當(dāng)。

二級(jí)索引同樣使用B-Tree數(shù)據(jù)結(jié)構(gòu)，不同的是葉節(jié)點(diǎn)只存儲(chǔ)二級(jí)索引的鍵值和聚簇索引鍵值（通常是Primary Key），聚簇索引鍵值是用于回表查詢?cè)摋l記錄。

注意到上圖中二級(jí)索引鍵值的順序和聚簇索引鍵值順序通常不同，所以二級(jí)索引做范圍查詢讀取記錄的性能通常不如聚簇索引高效（回表操作會(huì)有大量的隨機(jī)IO）。因?yàn)槎?jí)索引會(huì)存儲(chǔ)聚簇索引的鍵值，因此儲(chǔ)聚簇索引鍵值的大小也會(huì)影響二級(jí)索引的大小，所以在選擇聚簇索引鍵值時(shí)需要注意這點(diǎn)。

另外當(dāng)SELECT的字段被二級(jí)索引覆蓋的話，MySQL就不需要再回表查詢了，這樣執(zhí)行速度更快。

5）唯一索引

聚簇索引必須是唯一索引

二級(jí)索引可以定義為唯一索引

唯一性約束保證每個(gè)索引鍵值都不會(huì)重復(fù)

定義前要確認(rèn)數(shù)據(jù)能否滿足唯一性

唯一性約束在特殊場(chǎng)景下可能被打破（華為云RDS?for MySQL能最大程度避免這個(gè)問題）

四、最佳實(shí)踐

1）表/聚簇索引

單表大小需要有效控制

太大的表不利于性能

考慮使用分區(qū)表（水平切分）或者拆表（垂直切分）

分區(qū)表的一個(gè)好處是，老的數(shù)據(jù)不用了，可以整個(gè)分區(qū)刪除

聚簇索引必須按照 PK 排序

PK 鍵值盡可能小，減少索引整體大小，必須為 NOT NULL 字段

PK 覆蓋的字段，最好保證數(shù)據(jù)能有序插入

如果表中每個(gè)字段都無法保證唯一、無法保證 NOT NULL ，或者不適合做索引，推薦自定義一個(gè) ID 自增列作為主鍵，自動(dòng)滿足有序插入

定義 PK 的重要性——不定義會(huì)怎樣?

lnnoDB 會(huì)使用隱藏的 ROW_ID 作為主鍵

表的一些訪問會(huì)很低效

影響 Binlog 復(fù)制的應(yīng)用的效率

強(qiáng)烈建議一定要自定義主鍵 !!

常見的就是自增字段：

2）索引的兩面性

索引能加快查詢，是不是越多越全越好?

并不是，取決于業(yè)務(wù)側(cè)的重點(diǎn)和需求

正確的索引才能加速查詢

索引是有代價(jià)的

額外的二級(jí)索引占用額外的存儲(chǔ)空間

多個(gè)二級(jí)索引混合存儲(chǔ)導(dǎo)致 IO 不連續(xù)

二級(jí)索引有維護(hù)的代價(jià)

1）增刪改都需要同步每個(gè)受影響的二級(jí)索引

2）最差的是對(duì) PK 的更新，需要同步每個(gè)二級(jí)索引

3）更新 first_name ，所有索引都要更新

4）更新 age ，所有二級(jí)索引都不涉及

5）更新 phone_number , address 的索引不涉及

3）二級(jí)索引

4）建庫、表和索引語句

//建庫 CREATE?DATABASE?數(shù)據(jù)庫名稱 //建表 CREATE?TABLE?表名稱 ( 列名稱1?數(shù)據(jù)類型, 列名稱2?數(shù)據(jù)類型, ....... ) 如： CREATE?TABLE?Person? ( LastName?varchar, FirstName?varchar, Address?varchar, Age?int )? //建索引 CREATE?UNIQUE?INDEX?索引名稱 ON?表名稱?(列名稱)? 如： CREATE?INDEX?PersonIndex ON?Person?(LastName)

《7天玩轉(zhuǎn)MySQL基礎(chǔ)實(shí)戰(zhàn)營(yíng)》全系列筆記：

【7天玩轉(zhuǎn)MySQL】華為云RDS for MySQL學(xué)習(xí)筆記 Day01

RDS MySQL 華為云數(shù)據(jù)庫

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。