企業(yè)多語言網(wǎng)站開源,wordpress 新編輯器,網(wǎng)站建設步驟實踐報告,公司黃頁網(wǎng)1. 什么是索引 一種能幫助mysql提高查詢效率的數(shù)據(jù)結構#xff1a;索引數(shù)據(jù)結構索引優(yōu)點#xff1a; 大大提高數(shù)據(jù)查詢速度 索引缺點#xff1a; 維護索引需要耗費數(shù)據(jù)庫資源索引要占用磁盤空間當對表的數(shù)據(jù)進行增刪改的時候#xff0c;因為要維護索引#xff0c;所以速度…1. 什么是索引一種能幫助mysql提高查詢效率的數(shù)據(jù)結構索引數(shù)據(jù)結構索引優(yōu)點大大提高數(shù)據(jù)查詢速度索引缺點維護索引需要耗費數(shù)據(jù)庫資源索引要占用磁盤空間當對表的數(shù)據(jù)進行增刪改的時候因為要維護索引所以速度收到影響結合索引的優(yōu)缺點得出結論數(shù)據(jù)庫表并不是索引加的越多越好而是僅為那些常用的搜索字段建立索引效果才是最佳的2. 索引的分類主鍵索引PRIMARY KEY設定為逐漸后數(shù)據(jù)庫自動建立索引innodb為聚簇索引主鍵索引列值不能有空(Null)單值索引又叫單列索引、普通索引即一個索引只包含單個列一個表可以有多個單列索引唯一索引索引列的值必須唯一但允許有空值(Null)但只允許有一個空值(Null)復合索引即一個索引可以包含多個列多個列共同構成一個復合索引eg:SELECT id (name age) INDEX WHERE name AND age;全文索引Full Text MySQL5.7之前只有MYISAM存儲引擎支持全文索引全文索引類型為FULLTEXT在定義索引的列上支持值的全文查找允許在這些索引列中插入重復值和空值。全文索引可以在Char、Varchar上創(chuàng)建。3. 索引的基本操作3.1 主鍵索引創(chuàng)建-- 建表語句建表時設置主鍵自動創(chuàng)建主鍵索引 CREATE TABLE t_user ( id VARCHAR(20) PRIMARY KEY, name VARCHAR(20) ); -- 查看索引 SHOW INDEX FROM t_user;AI寫代碼sql篇幅限制下面就只能給大家展示小冊部分內容了。整理了一份核心面試筆記包括了Java面試、Spring、JVM、MyBatis、Redis、MySQL、并發(fā)編程、微服務、Linux、Springboot、SpringCloud、MQ、Kafc需要全套面試筆記及答案【點擊此處即可/免費獲取】?https://docs.qq.com/doc/DQXdYWE9LZ2ZHZ1ho3.2 單列索引創(chuàng)建(普通索引/單值索引)-- 建表時創(chuàng)建單列索引 -- 這種方式創(chuàng)建單列索引其名稱默認為字段名稱name CREATE TABLE t_user ( id VARCHAR(20) PRIMARY KEY, name VARCHAR(20) KEY(name) ); -- 建表后創(chuàng)建單列索引 -- 索引名稱為name_index 格式--- 字段名稱_index CREATE INDEX name_index ON t_user(name) -- 刪除單列索引 DROPINDEX 索引名稱 ON 表名AI寫代碼sql3.3 唯一索引創(chuàng)建-- 建表時創(chuàng)建唯一索引 CREATE TABLE t_user2 ( id VARCHAR(20) PRIMARY KEY, name VARCHAR(20), UNIQUE(name) ); -- 建表后創(chuàng)建唯一索引 CREATE UNIQUE INDEX name_index ON t_user2(name);AI寫代碼sql3.4 復合索引創(chuàng)建-- 建表時創(chuàng)建復合索引 CREATE TABLE t_user3 ( id VARCHAR(20) PRIMARY KEY, name VARCHAR(20), age INT, KEY(name,age) ); -- 建表后創(chuàng)建復合索引 CREATE INDEX name_age_index ON t_user3(name,age); -- 復合索引查詢的2個原則 -- 1.最左前綴原則 -- eg: 創(chuàng)建復合索引時字段的順序為 name,age,birthday -- 在查詢時能利用上索引的查詢條件為 SELECT * FROM t_user3 WHERE name ? SELECT * FROM t_user3 WHERE name ? AND age ? SELECT * FROM t_user3 WHERE name ? AND birthday ? SELECT * FROM t_user3 WHERE name ? AND age ? AND birthday ? -- 而其他順序則不滿足最左前綴原則 ... WHERE name ? AND birthday ? AND age ? -- 不滿足最左前綴原則 ... WHERE name ? AND birthday ? -- 不滿足最左前綴原則 ... WHERE birthday ? AND age ? AND name ? -- 不滿足最左前綴原則 ... WHERE age ? AND birthday ? -- 不滿足最左前綴原則 -- 2.MySQL 引擎在執(zhí)行查詢時為了更好地利用索引在查詢過程中會動態(tài)調整查詢字段的順序 -- 這時候再來看上面不滿足最左前綴原則的四種情況 -- 不滿足最左前綴原則但經(jīng)過動態(tài)調整順序后變?yōu)閚ame age birthday 可以利用復合索引 ... WHERE name ? AND birthday ? AND age ? -- 不滿足最左前綴原則也不能動態(tài)調整因為缺少age字段不可以利用復合索引 ... WHERE name ? AND birthday ? -- 不滿足最左前綴原則但經(jīng)過動態(tài)調整順序后變?yōu)閚ame age birthday 可以利用復合索引 ... WHERE birthday ? AND age ? AND name ? -- 不滿足最左前綴原則也不能動態(tài)調整因為缺少name字段不可以利用復合索引 ... WHERE age ? AND birthday ?AI寫代碼sql4. MySQL索引的數(shù)據(jù)結構(BTree)-- 建表 CREATE TABLE t_emp( id INT PRIMARY KEY, name VARCHAR(20), age INT ); -- 插入數(shù)據(jù)插入時主鍵無序 INSERT INTO t_emp VALUES(5,d,22); INSERT INTO t_emp VALUES(6,d,22); INSERT INTO t_emp VALUES(7,3,21); INSERT INTO t_emp VALUES(1,a,23); INSERT INTO t_emp VALUES(2,b,26); INSERT INTO t_emp VALUES(3,c,27); INSERT INTO t_emp VALUES(4,a,32); INSERT INTO t_emp VALUES(8,f,53); INSERT INTO t_emp VALUES(9,b,13); -- 查詢自動排序有序展示因為主鍵是有主鍵索引的因此會自動排序AI寫代碼sql問題為什么數(shù)據(jù)插入時未按照主鍵順序而查詢時卻是有序的呢原因MySQL底層為主鍵自動創(chuàng)建索引一旦創(chuàng)建了索引就會進行排序實際上這些數(shù)據(jù)在MySQL底層的真正存儲結構變成了下面這種方式問題為什么要排序呢因為排序之后查詢效率就快了比如查詢id 3的數(shù)據(jù)只需要按照順序去找即可而如果不排序就如同大海撈針假如100W條數(shù)據(jù)可能有時候需要隨機查詢100W次才找到這個數(shù)據(jù)也可能運氣好上來第1次就查詢到了該數(shù)據(jù)不確定性太高4.1 原理分析圖上圖這種分層樹結構查詢效率較高因為如果我需要查詢id4的數(shù)據(jù)只需要在頁目錄中匹配大于3且小于5則去3對應的page2中查找數(shù)據(jù)這樣就不需要從第1頁開始檢索數(shù)據(jù)了大大提高了效率從上圖可得出在只有2層的結構下1page 可以存儲記錄總數(shù)為1365 * 455 ≈ 62萬條而如果再加1層結構來存儲page層分頁目錄數(shù)據(jù)的分頁層PAGE的話那么1PAGE可以存儲總page數(shù)為1365 * 1365 ≈ 186萬條page而1PAGE存儲的總記錄數(shù)為1365 * 1365 * 455 ≈ 8.5 億條。因此我們平時使用的話2層結構就已經(jīng)足夠了實際上1個頁存儲的總數(shù)據(jù)樹可能大于理論估計的因為我們分配name字段的VARCHAR(20)占20個字節(jié)而實際上可能存儲的name數(shù)據(jù)并沒有20個字節(jié)可能更小三層結構實例如圖4.2 B樹結構分析上圖4.1 原理分析圖中這種索引結構稱之為B樹數(shù)據(jù)結構那么什么是B樹呢B樹和B樹區(qū)別是什么呢?篇幅限制下面就只能給大家展示小冊部分內容了。整理了一份核心面試筆記包括了Java面試、Spring、JVM、MyBatis、Redis、MySQL、并發(fā)編程、微服務、Linux、Springboot、SpringCloud、MQ、Kafc需要全套面試筆記及答案【點擊此處即可/免費獲取】?https://docs.qq.com/doc/DQXdYWE9LZ2ZHZ1ho問題4.2.1 為什么InnoDB底層使用B樹做索引而不用B樹B樹結構圖從上面的B樹結構圖中分析得出B樹每個節(jié)點中不僅包含數(shù)據(jù)的key還有data數(shù)據(jù)。而每個頁的存儲空間是有限的如果data數(shù)據(jù)較大時講會導致每個節(jié)點(即一個頁16KB)能存儲的key的數(shù)量較少當存儲數(shù)據(jù)量很大時會造成B樹的深度較大增大查詢時的磁盤讀取I/O次數(shù)進而影響查詢效率。(樹的深度影響I/O讀取次數(shù))在上一小節(jié)的B樹結構圖分析中所有數(shù)據(jù)記錄都是按照鍵值大小順序存放在同一層的葉子節(jié)點上而非葉子節(jié)點上只能存儲key值信息這樣可以大大增加每個節(jié)點(即一個頁16KB)能存儲的key的數(shù)量進而可以降低樹的高度進而減少磁盤讀取I/O次數(shù)提高查詢效率所以B樹和B樹的區(qū)別就在于B樹只有葉子節(jié)點存儲數(shù)據(jù)記錄B樹非葉子節(jié)點只存儲鍵值信息B樹的非葉子也存數(shù)據(jù)記錄所有節(jié)點直接都有一個鏈指針I(yè)nnoDB存儲引擎中頁的大小為16KB一般表的主鍵類型為INT(占用4個字節(jié)) 或 BIGINT(占用8個字節(jié))指針類型也一般占4或8個字節(jié)也就是說一個頁(B樹中的一個節(jié)點)中大概可以存儲16KB/(8B8B)1000個鍵值(只是估計值方便計算而已)。也就是說一個深度為3的B樹索引可以維護10^3 * 10^3 * 10^3 10億條記錄。實際情況中每個節(jié)點可能不能填充滿因此在數(shù)據(jù)庫中B樹的高度一般是24層**。**MySQL的InnoDB存儲引擎在設計時是將根節(jié)點常駐在內存中不需要動磁盤I/O**的也就是說**查找某個鍵值的行記錄最多只需要13次I/O操作每查詢一層都需要動用一次磁盤I/O5. 聚簇索引和非聚簇索引5.1 聚簇索引和非聚簇索引分析在表中聚簇索引實際上就是指的是主鍵索引如果表中沒有主鍵的話則MySQL會根據(jù)該表生成一個RoleID拿這個RoleId當做聚簇索引聚簇索引將數(shù)據(jù)存儲與索引放到一起索引結構的葉子節(jié)點保存了每行的數(shù)據(jù)。例如4.1小結分析圖中的data層一個單位就是聚簇索引存儲數(shù)據(jù)的例子主鍵id字段就是聚簇索引4.1小結分析圖就是基于主鍵索引(聚簇索引)構成的B樹結構聚簇索引不一定是主鍵索引但是主鍵索引肯定是聚簇索引非聚簇索引將數(shù)據(jù)與索引分開存儲索引結構的葉子節(jié)點指向了數(shù)據(jù)對應的位置(聚簇索引的值)非聚簇索引檢索數(shù)據(jù)是在自己的 “樹” 上進行查找例如我們根據(jù)表中的非聚簇索引name字段去查找數(shù)據(jù)時流程如下圖再看一張比較正規(guī)的分析圖注意在InnoDB中在聚簇索引之上創(chuàng)建的索引稱之為輔助索引例如復合索引、單列索引、唯一索引。一個表中只能有1個聚簇索引而其他索引都是輔助索引輔助索引的葉子節(jié)點存儲的不再是行的物理位置而是主鍵的值輔助索引訪問數(shù)據(jù)總是需要二次查找的**問題5.1.1 **為什么非聚簇索引(name字段的單列索引)構成的樹其葉子節(jié)點存儲聚簇索引(主鍵id)而不直接存儲行數(shù)據(jù)的物理地址呢換個方式問非聚簇索引檢索數(shù)據(jù)時檢索一次本樹再去聚簇索引樹中檢索一次這樣二次檢索樹結構那么為什么不直接在非聚簇索引樹葉子節(jié)點中存放行數(shù)據(jù)物理地址這樣只需要檢索一次樹結構就拿到行數(shù)據(jù)呢這里畫個圖方便理解一些? 從上圖得出在做新增數(shù)據(jù)時因為底層是需要基于主鍵索引進行排序的那么就可能導致原來某些數(shù)據(jù)對應的物理地址發(fā)生了變化而這時候由于我們的非聚簇索引樹的葉子節(jié)點直接存儲了數(shù)據(jù)的物理地址所以為了保證能獲取到數(shù)據(jù)還需要同時對非聚簇索引樹葉子節(jié)點的地址進行一遍更新修改? 同理如果我們不做插入主鍵id為4這行記錄的操作而是將其刪除的話這個流程可以自己思考一下? 也就是說之所以不在非聚簇索引樹的葉子節(jié)點直接存放行數(shù)據(jù)的物理地址是因為存儲數(shù)據(jù)的物理地址會隨著數(shù)據(jù)庫表的CRUD操作而不斷變更為了保證能獲取到數(shù)據(jù)這時必須要對非聚簇索引樹相關葉子節(jié)點的地址進行一遍修改而存主鍵主鍵不會隨著CRUD操作發(fā)生變化寧愿多查一次樹也不要再修改一次樹的結構5.2 MySQL兩種引擎中的(非)聚簇索引InnoDB中InnoDB中使用的是聚簇索引將主鍵組織到一顆B樹中而行數(shù)據(jù)就存儲在該B樹的葉子節(jié)點上若使用WHERE id 4這樣的條件查找主鍵則按照B樹的檢索算法即可查找對應的葉子節(jié)點之后獲得對應的行數(shù)據(jù)若對使用單列索引(非聚簇索引)的name字段進行搜索則需要執(zhí)行2個步驟第一步在輔助索引B樹中檢索name到達其對應的葉子節(jié)點后獲得該字段對應行記錄的主鍵id第二步使用主鍵id在主索引B樹中再次執(zhí)行一次樹的檢索最終到達對應的葉子節(jié)點并獲取到行記錄數(shù)據(jù)聚簇索引默認是主鍵如果表中沒有定義主鍵InnoDB會選擇一個唯一且非空的索引代替主鍵作為聚簇索引。而如果也沒有這樣的唯一非空索引那么InnoDB就會隱式定義一個主鍵類似于Oracle中的RowId來做為聚簇索引。如果已經(jīng)設置了聚簇索引又希望再單獨設置聚簇索引則必須先刪除主鍵然后添加我們想要的聚簇索引最后再恢復主鍵即可MYISAM中MYISAM使用的是非聚簇索引非聚簇索引的兩顆B樹看上去沒有什么不同節(jié)點的結構完全一致只是存儲的內容不同主鍵索引B樹的節(jié)點存儲了主鍵輔助索引B樹存儲量輔助鍵。表數(shù)據(jù)存儲在獨立的地方這兩顆B樹的葉子節(jié)點都使用一個地址指針指向真正的表數(shù)據(jù)對于表數(shù)據(jù)來說這兩個鍵沒有任何差別。由于索引樹是獨立的通過輔助鍵檢索無需再次檢索主鍵索引樹5.3 聚簇索引和非聚簇索引的優(yōu)/劣勢問題5.3.1 使用聚簇索引的優(yōu)勢問題每次使用輔助索引檢索都需要經(jīng)過2次B樹查找看上去聚簇索引的效率明顯要低于非聚簇索引那么聚簇索引的優(yōu)勢何在呢-- 1.由于行數(shù)據(jù)和聚簇索引樹的葉子節(jié)點存儲在一起同一頁中會有多條行數(shù)據(jù)首次訪問數(shù)據(jù)頁中某條行記錄時會把該數(shù)據(jù)頁數(shù)據(jù)加載到Buffer(緩存器)中當再次訪問該數(shù)據(jù)頁中其他記錄時不必訪問磁盤而直接在內存中完成訪問。 -- 注主鍵id和行數(shù)據(jù)一起被載入內存找到對應的葉子節(jié)點就可以將行數(shù)據(jù)返回了如果按照主鍵id來組織數(shù)據(jù)獲取數(shù)據(jù)效率更快 -- 2.輔助索引的葉子節(jié)點存儲主鍵的值而不是行數(shù)據(jù)的存放地址。這樣做的好處是因為葉子節(jié)點存放的是主鍵值其占據(jù)的存儲空間小于存放行數(shù)據(jù)物理地址的儲存空間AI寫代碼sql1234問題5.3.2 使用聚簇索引需要注意什么-- 當使用主鍵為聚簇索引時而不要使用UUID方式因為UUID的值太過離散不適合排序導致索引樹調整復雜度增加消耗更多時間和資源。 -- 建議主鍵最好使用INT/BIGINT類型且為自增這樣便于排序且默認會在索引樹的末尾增加主鍵值對索引樹的結構影響最小(下面主鍵自增的問題會解釋原因)。而且主鍵占用的存儲空間越大輔助索引中保存的主鍵值也會跟著增大占用空間且影響IO操作讀取數(shù)據(jù)AI寫代碼sql123問題5.3.3 為什么主鍵通常建議使用自增id-- 聚簇索引樹存放數(shù)據(jù)的物理地址(xx1,xx2,xx3,xxx5)與索引順序(1,2,3,5)是一致的即 -- 1.只要索引是相鄰的那么在磁盤上索引對應的行數(shù)據(jù)存放地址也是相鄰的。 -- 2.如果主鍵是自增那么當插入新數(shù)據(jù)時只需要按照順序在磁盤上開辟新物理地址存儲新增行數(shù)據(jù)即可。 -- 3.而如果不是主鍵自增那么當新插入數(shù)據(jù)后會對索引進行重新排序(重新調整B樹結構)磁盤上的物理存儲地址也需要重新分配要存儲的行數(shù)據(jù)AI寫代碼sql問題5.3.4 什么情況下無法利用索引呢-- 1. 查詢語句中使用LIKE關鍵字這種情況主要是針對于單列索引 -- 在使用LIKE關鍵字查詢時如果匹配字符串的第一個字符為%則索引不會被使用而%不在最左邊而是在右邊則索引會被使用到 -- eg: SELECT * FROM t_user WHERE name LIKE xx% -- 可以利用上索引,這種情況下可以拿xx到索引樹上去匹配 SELECT * FROM t_user WHERE name LIKE %xx% -- 不可以利用上索引 SELECT * FROM t_user WHERE name LIKE %xx -- 不可以利用上索引 -- 2. 查詢語句中使用多列索引這種情況主要是針對于聚合索引 -- 多索引是在表的多個字段創(chuàng)建索引只有查詢條件中使用了這些字段中的第一個字段索引才會被使用。即最左前綴原則詳情查看3.4小結聚合索引中的介紹 -- 3. 查詢語句中使用OR關鍵字 -- 查詢條件中有OR關鍵字時如果OR前后的兩個條件列都具有索引則查詢中索引將被使用而如果OR前后有一個或2個列不具有索引那么查詢中索引將不被使用到AI寫代碼sql篇幅限制下面就只能給大家展示小冊部分內容了。整理了一份核心面試筆記包括了Java面試、Spring、JVM、MyBatis、Redis、MySQL、并發(fā)編程、微服務、Linux、Springboot、SpringCloud、MQ、Kafc需要全套面試筆記及答案【點擊此處即可/免費獲取】?https://docs.qq.com/doc/DQXdYWE9LZ2ZHZ1ho6. 什么是約束以及分類約束:作用是為了保證數(shù)據(jù)的完整性而實現(xiàn)的摘自一套機制即(約束是針對表中數(shù)據(jù)記錄的)MySQL中的約束非空約束NOT NULL保證某列數(shù)據(jù)不能存儲NULL 值;唯一約束UNIQUE(字段名)保證所約束的字段數(shù)據(jù)必須是唯一的允許數(shù)據(jù)是空值(Null)但只允許有一個空值(Null)主鍵約束PRIMARY KEY(字段名)主鍵約束 非空約束 唯一約束保證某列數(shù)據(jù)不能為空且唯一外鍵約束FOREIGN KEY(字段名)保證一個表中某個字段的數(shù)據(jù)匹配另一個表中的某個字段可以建立表與表直接的聯(lián)系自增約束AUTO_INCREMENT保證表中新插入數(shù)據(jù)時某個字段數(shù)據(jù)可以依次遞增默認約束DEFALUT保證表中新插入數(shù)據(jù)時如果某個字段未被賦值則會有默認初始化值檢查性約束CHECK保證列中的數(shù)據(jù)必須符合指定的條件示例create table member( id int(10), phone int(15) unsigned zerofill, name varchar(30) not null, constraint uk_name unique(name), constraint pk_id primary key (id), constraint fk_dept_id foreign key (dept_id字段2) references dept(主表1)(dept_id) );AI寫代碼sql7. MySQL索引和約束的區(qū)別索引的作用索引用于快速定位特定數(shù)據(jù)提高查詢效率的。約束的作用約束是為了保證數(shù)據(jù)的完整性即約束是針對表中數(shù)據(jù)記錄的?？偨Y約束是為了保證表數(shù)據(jù)的完整性索引是為了提高查詢效率兩者作用不一樣種類也不太一樣MySQL索引相關面試題1.MySQL索引分類并對比區(qū)別答案參考第2小結2.復合索引查詢時字段排列的先后順序與創(chuàng)建索引時不同能否成功利用索引查詢?考察點復合索引的最左前綴原則-- 假設構成復合索引的字段為 name,age,birthday -- 則下面那種情況可以使用成功利用復合索引查詢 ... WHERE name ? -- 可以利用 ... WHERE name ? AND age ? -- 可以利用 ... WHERE name ? AND birthday ? -- 可以利用 ... WHERE name ? AND age ? AND birthday ? -- 可以利用 ... WHERE name ? AND birthday ? AND age ? -- 不滿足最左前綴原則但經(jīng)過動態(tài)調整后可以利用 ... WHERE birthday ? AND age ? AND name ? -- 不滿足最左前綴原則但經(jīng)過動態(tài)調整后可以利用 ... WHERE age ? AND birthday ? -- 不滿足最左前綴原則不能動態(tài)調整不能利用復合索引AI寫代碼sql3.MySQL索引的數(shù)據(jù)結構是什么MySQL索引數(shù)據(jù)結構: B TreeB樹聚簇索引~4.MySQL中索引和約束的區(qū)別以及各自種類MySQL索引與約束5.為什么InnoDB底層使用B樹做索引而不用B樹答案參考: 4.2小結B樹結構分析中的問題4.2.16.什么是聚簇索引和非聚簇索引答案參考5.1小結聚簇索引和非聚簇索引分析7.為什么非聚簇索引構成的樹的葉子節(jié)點存儲聚簇索引(或主鍵)而不直接存儲數(shù)據(jù)的物理地址呢答案參考5.1小結的問題5.1.18.使用聚簇索引需要注意什么答案參考5.3小結的問題5.3.29.為什么主鍵通常建議使用自增id答案參考5.3小結的問題5.3.310.什么情況下無法利用索引呢答案參考5.3小結的問題5.3.411.聚簇索引相對于非聚簇索引的優(yōu)勢是什么答案參考5.3小結的問題5.3.1