MySQL數(shù)據(jù)庫中的鍵和索引的概念

本篇內(nèi)容主要講解“MySQL數(shù)據(jù)庫中的鍵和索引的概念”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“MySQL數(shù)據(jù)庫中的鍵和索引的概念”吧!

1.鍵的概念

• 鍵：數(shù)據(jù)庫中的鍵（key）又稱為關(guān)鍵字，是關(guān)系模型中的一個(gè)重要概念，它是邏輯結(jié)構(gòu)，不是數(shù)據(jù)庫的物理部分；

• 唯一鍵：即一個(gè)或者一組列，其中沒有重復(fù)的記錄，可以唯一標(biāo)示一條記錄；

• 主鍵：屬于唯一鍵，是一個(gè)比較特殊的唯一鍵，區(qū)別在于主鍵不可為空；

# id為主鍵  name是唯一鍵
create table Mark(id int not null primary key ,name varchar(250) unique key);
create table Mark(id int, name varchar(250), primary key(id), unique key(name));

• 外鍵：一張表外鍵的值一般來說是另一張表主鍵的值，因此，外鍵的存在使得表與表之間可以聯(lián)系起來；

create table students_to_teacher(to_id int, stu_id int, tea_id int, foreign key(stu_id) references students(id), foreign key(tea_id) references teacher(id));

2.索引的概念

• 索引就像是一張表的目錄，在查找內(nèi)容之前可以先在目錄中查找索引位置，以此快速定位查詢數(shù)據(jù)，保存索引數(shù)據(jù)的文件一般會(huì)與保存數(shù)據(jù)的目錄分開；

• 索引應(yīng)該構(gòu)建在經(jīng)常被用作查詢條件的字段上；

3.索引類型

• 普通索引：加速查詢；

# 創(chuàng)建普通索引
create index 索引名稱 on 表名(列名)
# 刪除普通索引
drop index 索引名稱 on 表名
# 查看索引
show index from 索引名

• 唯一索引（UNIQUE KEY）：加速查詢 + 列值唯一（可以有null）；

# 創(chuàng)建唯一索引
create unique index 索引名稱 on 表名(列名)
# 刪除唯一索引
drop unique  index 索引名稱 on 表名

• 主鍵索引（PRIMARY KEY）：用于加速查詢，只能有一個(gè)主鍵字段，不允許重復(fù)且不能為NULL；

• 組合索引：多列值組成一個(gè)索引，專門用于組合搜索，其效率大于索引合并；

• 全文索引：對文本的內(nèi)容進(jìn)行分詞，進(jìn)行搜索；

• 空間索引：指依據(jù)空間對象的位置和形狀或空間對象之間的某種空間關(guān)系按一定的順序排列的一種數(shù)據(jù)結(jié)構(gòu)；

4.引優(yōu)化策略

• 獨(dú)立使用列，盡量避免其參與運(yùn)算；

• 左前綴索引：查詢字段的時(shí)候，條件過濾時(shí)，最左前綴精確匹配；

• 多列索引：AND 連接字段時(shí)適合多列索引，選擇合適的索引次序，將選擇性最高的放在左側(cè)，范圍匹配的放在右側(cè)；

5.Innodb 存儲(chǔ)引擎

• 所有的InnoDB表的數(shù)據(jù)和索引存儲(chǔ)于同一個(gè)表文件中，但是表數(shù)據(jù)和表結(jié)構(gòu)分離；

-rw-rw---- 1 mysql mysql    65 8月  27 14:31 db.opt
    -rw-rw---- 1 mysql mysql  8614 8月  27 14:31 students.frm
    -rw-rw---- 1 mysql mysql 98304 8月  27 14:31 students.ibd
    db.opt文件：主要用來存儲(chǔ)當(dāng)前數(shù)據(jù)庫的默認(rèn)字符集和字符校驗(yàn)規(guī)則
    students.frm文件：存放表結(jié)構(gòu)的
    students.ibd文件：存儲(chǔ)了當(dāng)前表的數(shù)據(jù)和相關(guān)的索引數(shù)據(jù)
    因此，表數(shù)據(jù)和表結(jié)構(gòu)分離, 每個(gè)表單獨(dú)使用一個(gè)表文件來存儲(chǔ)數(shù)據(jù)和索引

• Mariadb默認(rèn)的存儲(chǔ)引擎是XtraDB，但是為了與MySQL兼容，因此也取名做InnoDB，因?yàn)镸ySQL的默認(rèn)存儲(chǔ)引擎是InnoDB；

• 使用聚集索引（數(shù)據(jù)和索引在一起），也支持自適應(yīng)hash索引，鎖粒度為行級別，支持支持熱備工具；

• 支持事務(wù)的存儲(chǔ)引擎，適合處理大量的短期事務(wù)；

6.MyISAM 存儲(chǔ)引擎

• 所有的MyISAM表的數(shù)據(jù)和索引存放在不同的文件中，表結(jié)構(gòu)也分離；

-rw-rw---- 1 mysql mysql  10630 8月  27 13:12 user.frm
-rw-rw---- 1 mysql mysql    504 8月  27 13:15 user.MYD
-rw-rw---- 1 mysql mysql   2048 8月  27 14:30 user.MYI
user.frm：為表結(jié)構(gòu)
user.MYD：為表數(shù)據(jù)
user.MYI：為表索引

• 支持全文索引（fulltext index），壓縮，空間函數(shù)；

• 不支持事物，表級鎖，適用于只讀，讀多寫少；

7.MySQL的并發(fā)訪問控制

• 任何的數(shù)據(jù)集只要支持并發(fā)訪問模型就必須基于鎖機(jī)制進(jìn)行訪問控制；

• 讀鎖：共享鎖，允許給其他人讀，不允許他人寫；

• 寫鎖：獨(dú)占鎖, 不允許其他人讀和寫；

• 顯示鎖：用戶手動(dòng)請求讀鎖或?qū)戞i；

• 隱式鎖：由存儲(chǔ)引擎自行根據(jù)需要加的，無需我們管理；

• 給表施加鎖機(jī)制

# lock tables 方式施加鎖
lock tables 表名稱 read   # 讀鎖
lock tables 表名稱 write  # 寫鎖
# 給表解鎖    
unlock tables;    
# flush tables 方式施加鎖
flush tables 表名稱 with read lock;   #讀鎖
flush tables 表名稱 with write lock;  # 寫鎖

8.MySQL事務(wù)機(jī)制

• 一組原子性的SQL查詢，或多個(gè)SQL語句組成了一個(gè)獨(dú)立的單元，要么這一組SQL語句全部執(zhí)行，要么全部不執(zhí)行；

事物日志：

• 管理事物機(jī)制的日志；

• redo日志：記錄SQL執(zhí)行的語句，這些SQL語句還沒有同步到磁盤上，沒有修改數(shù)據(jù)。如果數(shù)據(jù)奔潰，可以通過撤銷SQL執(zhí)行的語句來進(jìn)行還原。但是，如果已經(jīng)同步到磁盤上的SQL語句而言，就只能使用undo來回滾之前的數(shù)據(jù)了；

• undo日志：記錄沒有執(zhí)行SQL的樣子，也就是記錄修改數(shù)據(jù)之前的數(shù)據(jù)記錄下來；

ACID機(jī)制：

• automicity：原子性，整個(gè)事物中的所有操作要么全部成功提交，要么全部失敗回滾；

• consistency：一致性，數(shù)據(jù)庫總是從一個(gè)一致性狀態(tài)轉(zhuǎn)化為另一個(gè)一致性狀態(tài)；

• isolation： 隔離性，事物不會(huì)相互影響，一個(gè)事物所作出的操作在提交之前，是不能為其他事物所見，隔離有多種級別，主要是為了并發(fā)；

• durability：持久性，事物一旦提交，其所作的修改會(huì)保存在數(shù)據(jù)庫中，不能丟失；

9.MySQL 查詢緩存機(jī)制

• 緩存的是查詢語句的整個(gè)查詢結(jié)果，是一個(gè)完整的select語句的緩存結(jié)果；

• 哪些查詢可能不會(huì)被緩存 ：查詢中包含UDF、存儲(chǔ)函數(shù)、用戶自定義變量、臨時(shí)表、mysql庫中系統(tǒng)表、或者包含列級別的權(quán)限表、有著不確定值的函數(shù)；

1.query_cache_min_res_unit：查詢緩存分配內(nèi)存塊的最小的分配單位，較小的值較少內(nèi)存浪費(fèi)，但是會(huì)導(dǎo)致更加平凡的內(nèi)存分配操作 ，較大的值會(huì)導(dǎo)致浪費(fèi)
2.query_cache_limit：能夠緩存的最大查詢結(jié)果，對有較大結(jié)果的查詢語句，建議在select中使用SQL_NO_CACHE
3.query_cache_size：查詢緩存總共可用的內(nèi)存空間，單位是字節(jié)，必須是1024整數(shù)倍
4.query_cache_type：ON , OFF , DEMAND
5.query_cache_wlock_invalidate：如果某個(gè)數(shù)據(jù)表被其他的連接鎖定，是否仍然可以從查詢緩存中返回結(jié)果，默認(rèn)值為off，表示可以返回?cái)?shù)據(jù)，on為不允許

• 緩存命中率

緩存命中率計(jì)算公式： Qcache_hits / (Qcache_hits+Com_select)

10.MySQL 日志分類

• 查詢?nèi)罩?/strong> ：query log ，一般不啟用；

general_log = {ON|OFF}  # 是否啟用查詢?nèi)罩?
general_log_file = /logs/mysql/general_log  # 當(dāng)log_output為FILE類型時(shí)，日志信息的記錄位置；
log_output = {TABLE|FILE|NONE} 
log_output = TABLE,FILE

• 慢查詢?nèi)罩?/strong>：slow_query_log ，用于對執(zhí)行速率較慢的SQL語句就像過濾，有利于SQL代碼的優(yōu)化；

1.執(zhí)行時(shí)長超出指定時(shí)長的操作 
show global variables like 'long_query_time'; 查看指定的時(shí)長
set global long_query_time = 自定義時(shí)長
2.slow_query_log = {ON|OFF}：是否啟用慢查詢?nèi)罩?
set global slow_query_log = ON
3.slow_query_log_file = mariadb1-slow.log
# 過濾條件
4.log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk 
5.log_slow_rate_limit = 1 指定記錄速率
6.log_slow_verbosity = 指定內(nèi)容級別

• 錯(cuò)誤日志：error log ，必須啟用，二進(jìn)制日志可以反應(yīng)MySQL數(shù)據(jù)庫的錯(cuò)誤信息，用于調(diào)試；

# 錯(cuò)誤日志信息產(chǎn)生的來源
    mysqld啟動(dòng)和關(guān)閉過程中輸出的信息；
    mysqld運(yùn)行中產(chǎn)生的錯(cuò)誤信息；
    event scheduler運(yùn)行一個(gè)event時(shí)產(chǎn)生的日志信息
    在主從復(fù)制架構(gòu)中的從服務(wù)器上啟動(dòng)從服務(wù)器線程時(shí)產(chǎn)生的日志信息；
# 如何開啟錯(cuò)誤日志
    log_error = /path/to/somefile
    log_warnings = {ON|OFF}：是否記錄警告信息于錯(cuò)誤日志中；

• 二進(jìn)制日志：binary log，用于通過’重新執(zhí)行’日志文件中的記錄的事件（SQL語句）來生成數(shù)據(jù)副本，也就是用于主從復(fù)制；

# 日志記錄的格式分類
    基于“SQL語句”記錄: statement
    基于“行”記錄：row
    “混合模式” ：mixed，系統(tǒng)自行判斷
# 二進(jìn)制日志文件的構(gòu)成
    日志文件：mysql-bin.文件序號 
        例如： mysql-bin.000001
    索引文件：mysql-bin.index 
        例如：mysql-bin.index

• 中繼日志：relay log ，在主從復(fù)制架構(gòu)中，從服務(wù)器用于保存從主服務(wù)器的二進(jìn)制日志中讀取到的時(shí)間；

• 事務(wù)日志：transaction log ，事物日志由事物型存儲(chǔ)引擎自行管理和使用，無需手動(dòng)管理；

11.MySQL 備份策略的注意點(diǎn)

• 可容忍丟失多少數(shù)據(jù)；

• 恢復(fù)需要在多長時(shí)間內(nèi)；

• 備份的對象: 數(shù)據(jù)、二進(jìn)制日志和InnoDB的事務(wù)日志、SQL代碼（存儲(chǔ)過程和存儲(chǔ)函數(shù)、觸發(fā)器、事件調(diào)度器等）、服務(wù)器配置文件；

• 備份類型

1.站在數(shù)據(jù)集是否完整的角度上
    完全備份，部分備份 
2.站在完全備份的基礎(chǔ)上
    增量備份，差異備份 
3.站在是否影響數(shù)據(jù)集讀寫的角度上
    熱備份：在線備份，讀寫操作不受影響；
    溫備份：在線備份，讀操作可繼續(xù)進(jìn)行，但寫操作不允許
    冷備份：離線備份，數(shù)據(jù)庫服務(wù)器離線，備份期間不能為業(yè)務(wù)提供讀寫服務(wù)
    MyISAM存儲(chǔ)引擎: 能夠?qū)崿F(xiàn)溫備
    InnoDB存儲(chǔ)引擎: 能夠?qū)崿F(xiàn)熱備
4.站在數(shù)據(jù)存儲(chǔ)角度上
    物理備份：直接復(fù)制數(shù)據(jù)文件進(jìn)行的備份
    邏輯備份：從數(shù)據(jù)庫中“導(dǎo)出”操作數(shù)據(jù)的SQL語句，再執(zhí)行，實(shí)現(xiàn)備份

12.備份策略需要考慮的因素

• 持鎖的時(shí)長；

• 備份過程時(shí)長；

• 備份負(fù)載；

• 恢復(fù)過程時(shí)長；

13.數(shù)據(jù)庫備份具體解決方案

• 數(shù)據(jù)：完全備份 + 增量備份

• 備份：物理 + 邏輯

14.備份工具介紹

• mysqldump：邏輯備份工具，適用于所有存儲(chǔ)引擎，溫備；但是對InnoDB存儲(chǔ)引擎支持熱備；

• scp, tar 等文件系統(tǒng)工具：物理備份工具，適用于所有存儲(chǔ)引擎；冷備；完全備份，部分備份，不適用于Innodb存儲(chǔ)引擎；

• lvm2的快照：幾乎熱備；借助于文件系統(tǒng)工具實(shí)現(xiàn)物理備份；

• mysqlhotcopy： 幾乎冷備；僅適用于MyISAM存儲(chǔ)引擎

15.MySQL 半同步復(fù)制模型

• 所謂的半同步復(fù)制指的是一臺(tái)主節(jié)點(diǎn)有多個(gè)從節(jié)點(diǎn)，在眾多的從節(jié)點(diǎn)之中有一個(gè)從節(jié)點(diǎn)在收到主節(jié)點(diǎn)的二進(jìn)制日志信息之后，存儲(chǔ)在中繼日志中，執(zhí)行中繼日志后，給主節(jié)點(diǎn)一個(gè)反饋信息，直接點(diǎn)收到這個(gè)反饋信息之后，返回給執(zhí)行這句SQL的ORM語句，表示數(shù)據(jù)已經(jīng)存儲(chǔ)完畢；

到此，相信大家對“MySQL數(shù)據(jù)庫中的鍵和索引的概念”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！