1、B 樹索引
顧名思義,結構是B 樹的索引就是B 樹索引,一般情況下,InnoDb引擎中建立的常規索引都是B 的結構。
B 樹索引就是以下這幾種。
1.1、聚集索引/叢集索引
定義主鍵時,主鍵上自動追加的索引就是聚集索引,也稱為叢集索引。
在Mysql中,使用元件建立B 樹結構,如圖所示,每個葉子節點對應一個主鍵,同時也對應著其他相關資料。
如果我們建立表格時沒有定義主鍵,Mysql也會自動建立一個主鍵和對應的索引,主鍵名稱是rowId
1.2、輔助索引/二級索引
輔助索引,也稱為二級索引,是指對於非主鍵列column所建立的索引。同樣的,Mysql會為這個索引建立一個B 樹,樹的葉子節點除了包含這個列column的值以外,就只包含這個列所在行的主鍵值,這樣透過列的索引就可以查到葉子節點,然後葉子節點中的主鍵資訊再從主鍵的索引中搜索,最後得到一整行的資料。
透過二級索引找到主鍵,再從主鍵得到一整行資料的行為叫做回表。
1.3、聯合索引/複合索引
1.3.1、什麼是複合索引
聚合索引可以說是二級索引的一種特殊情況。一般二級索引都是只對一個非主鍵的欄位新增索引,而聚合索引則是一次同時對多個欄位同時新增索引。
一般的二級索引用這樣的語句建立:
CREATE INDEX order_name_index on t_order(order_name);
複合索引則是這樣建立:
CREATE INDEX order_name_and_order_type_index on t_order(order_name, order_type);
對於複合索引,Mysql會也會建立一個B 樹,但因為是多個欄位的索引,所以B 樹的排序規則比較特殊,是遵循最左原則。下面會講到什麼是最左原則。
之後葉子節點包含的資訊有多個,一個是作為索引的各個列的值,另一個就是主鍵的值。
1.3.2、最左原則
所謂的最左原則是,B 樹的排序規則是根據索引定義時,定義的語句中的列名從左到右進行排序。
例如定義語句如下:
CREATE INDEX joint_index on t_order(order_name, order_type, submit_time);
那排序規則是先排order_name
,如果order_name
相同,再排order_type
,最後排submit_time
。
那當我們查詢時,根據定義時列的順序從左到右,where
子句或order by
等子句應該盡量先從order_name
開始,然後以此類推。
比如說,我們已經定義了上面的三個欄位組成的複合索引,那麼查詢或是排序的時候盡量先order_name
,再order_type
,最後submit_time
。
select * from t_order where order_name = 'order1' and order_type = 1 and submit_time = str_to_date('2022-08-02 00:52:26', '%Y-%m-%d %T')
原因很簡單,因為聯合索引的排序規則是先排order_name
,如果order_name
相同,再排order_type
,最後排submit_time
。所以只有在查詢排序時也遵循這個規則,我們才能用上索引。
如果我們不完全遵守最左原則,例如查詢排序只排兩個列,忽略中間那個order by order_name, submit_time
。那這個時候Mysql會有智慧化的處理,他會自己判斷是用索引快還是不用索引快。
1.3.3、聯合索引的查詢最佳化
盡量使用到組成聯合索引的列,並且保證順序。可以透過查詢索引查看列的順序。查看sql_in_index
show index from t_order;
查詢傳回的欄位盡量只傳回組成聯合索引的欄位和主鍵,不要傳回其它的資料列,以免造成回表。
這應該很容易理解,因為聯合索引的B 樹的葉子節點就只包含主鍵和組成聯合索引的列的值,如果傳回的欄位就這幾列,那在一個B 樹種查詢就完事了。如果還要回傳其它的列的話,就又要去主鍵的索引中查找,有回表操作。
2、雜湊索引
一般資料庫都會用B 樹索引查詢數據,但是當資料庫使用一段時間後,InnoDB 會記錄一些使用頻率較高的熱數據,然後為這些熱數據建立哈希結構的索引,這就是哈希索引的應用場景。
這個索引在Mysql 5.7開始預設為開啟。
2.1、查看哈希索引的命中率等資訊
使用語句:
show engine innodb status;
status有許多訊息,其中就包括哈希索引的情況。我們把資訊複製到編輯器中查看。其中的這一段就是哈希索引的資訊。
------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 5 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 1 buffer(s) -- 哈希索引的命中率,可根据这个来决定是否使用哈希索引 0.00 hash searches/s, 0.00 non-hash searches/s ---
3、索引的创建策略
3.1、 单列索引的策略
3.1.1、列的类型占用的空间越小,越适合作为索引
因为B+树也是占用空间的,所以在固定空间中,如果列的类型占用的空间越小,那我们一次就能读取更多的B+树节点,这样自然就加快了效率。
3.1.2、根据列的值的离散性
离散性是指数据的值重复的程度高不高,假如有N条数据的话,那离散性就可以用数值表示,范围是1/N 到 1。
比如说某个列在数据库中有下面几条数据(1, 2, 3, 4, 5, 5, 3),其中5和3都有重复,去重后应该是(1, 2, 3, 4, 5)。我们将去重后的条数除以总条数就得到离散性。这里是5/7。列中重复数据较多时,对应的数值较小,而重复数据较少时,数值相应较大。
如果一个列的数据的重复性越低,那么这个列就越适合加索引。
因为索引是需要起到筛选的作用。比如我们有个where
条件是where id = 1
,如果数据重复性较高,那可能根据索引会返回100条数据,然后我们在根据其他where
条件在100条数据中再筛选。
如果数据重复性较低,那可能就只返回1条数据,那之后的运算量明显小得多。
所以一个列的数据离散性越高,那这个列越适合添加索引。
我们可以用下面的语句得到某个列的离散性程度。
select count(distinct id)/count(*) form t_table;
3.1.3、前缀索引
前缀索引和后缀索引:
有些列的值比较长,比如一些备注日志信息也会记录在数据库当中,这类信息的长度往往比较长,如果我们需要对这类列加索引,那索引并不是索引字符串的全部长度。这时候我们就可以建立前缀索引,即对字符串的前面几位建立索引。
所以前缀索引就是建立范围更小索引,选择一个好前缀位数就能有一个更好的查询效率。
不过有一些缺点,就是这类索引无法应用到order by
和group
语句上。
Mysql没有后缀索引,如果非要实现后缀索引,那在数据存储时我们应该将数据反转,这样就能用前缀索引达到后缀索引的效果。后缀索引的一个经典应用就是邮箱,快速查询某种类型的邮箱。
选择前缀索引的位数:
这里的逻辑和列的离散性类似,我们需要看看字符串的前面几位的子字符串的离散性如何。比如对于下面的表,内容是电影票的相关信息,我们需要对order_note
建立前缀索引。
来比较一下各个位的子字符串的离散性。
SELECT COUNT(DISTINCT LEFT(order_note,3))/COUNT(*) AS sel3, COUNT(DISTINCT LEFT(order_note,4))/COUNT(*)AS sel4, COUNT(DISTINCT LEFT(order_note,5))/COUNT(*) AS sel5, COUNT(DISTINCT LEFT(order_note, 6))/COUNT(*) As sel6, COUNT(DISTINCT LEFT(order_note, 7))/COUNT(*) As sel7, COUNT(DISTINCT LEFT(order_note, 8))/COUNT(*) As sel8, COUNT(DISTINCT LEFT(order_note, 9))/COUNT(*) As sel9, COUNT(DISTINCT LEFT(order_note, 10))/COUNT(*) As sel10, COUNT(DISTINCT LEFT(order_note, 11))/COUNT(*) As sel11, COUNT(DISTINCT LEFT(order_note, 12))/COUNT(*) As sel12, COUNT(DISTINCT LEFT(order_note, 13))/COUNT(*) As sel13, COUNT(DISTINCT LEFT(order_note, 14))/COUNT(*) As sel14, COUNT(DISTINCT LEFT(order_note, 15))/COUNT(*) As sel15, COUNT(DISTINCT order_note)/COUNT(*) As total FROM order_exp;

可以看出,前面几位的子字符串的离散程度较低,后面sel13
开始就比较高,那我们可以根据实际情况,建立13~15位的前缀索引。
建立前缀索引SQL语句:
alter table order_exp add key(order_note(13));
3.1.2、只为搜索、排序和分组的列建索引
这个理由很简单,不解释了。
3.2、 多列索引的策略
3.2.1、离散性最高的列放前面
原因很简单,查询时根据定义复合索引时的列的顺序来查询的,离散性高的列放在前面的话,就能更早的将更多的数据排除在外。
3.2.2、三星索引
三星索引是一种策略。有三种条件,满足一条则索引获得一颗星,三颗星则是很好的索引。
三条策略分别是
索引将相关记录放在一起。
意思是查询需要的数据在索引树的叶子节点中连续或者足够靠近。举个例子,下面是某个索引的B+树。查询所需数据仅在叶节点的前两个范围内,即0000至a。这很明显,后面的片我们就没必要再去查询了,这无疑增加了效率。当所需数据分布在每个片上时,查询次数就会显著增加。
所以查询需要的数据在叶子节点上越连续,越窄就越好。
索引中的数据顺序与查找中的数据排序一致。
这容易理解,讲解联合索引中说过,B+树的排序顺序和索引中的数据一样,所以查询时的where
的数据顺序越贴近索引中的顺序,就越能更好地利用B+树。
索引的列包含查询中的所有列。
这个可以避免回文操作,不多解释。
三星索引的权重:
一般来说第三个策略权重占到50%,之后是第一个策略27%, 第二个策略23%。
三星索引实例:
CREATE TABLE customer ( cno INT, lname VARCHAR (10), fname VARCHAR (10), sex INT, weight INT, city VARCHAR (10) ); CREATE INDEX idx_cust ON customer (city, lname, fname, cno);
我们创建以上的索引,那么对于下面的查询语句,这个索引就是三星索引。
select cno,fname from customer where lname='xx' and city ='yy' order by fname;
首先,查询条件中有lname=’xx’ and city =’yy’
,这条件让我们这需要在lname=’xx’ and city =’yy’
的那一片B+树的叶子节点中查询,让我们的查询变窄了很多,并且这部分的数据是连续的,因为B+树是先根据city
排序,再根据lname
查询。
另外,因为已经锁定lname=’xx’ and city =’yy’
,所以这部分的数据是根据fname和cno
排序。查询语句正好是根据`fname```排序,所以第二点也满足。
最后是查询的结果都包含正在索引中,不会有回文,第三点也满足,所以这个索引是三星索引。
以上是MySql索引怎麼創建的詳細內容。更多資訊請關注PHP中文網其他相關文章!

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于架构原理的相关内容,MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层,下面一起来看一下,希望对大家有帮助。

mysql的msi与zip版本的区别:1、zip包含的安装程序是一种主动安装,而msi包含的是被installer所用的安装文件以提交请求的方式安装;2、zip是一种数据压缩和文档存储的文件格式,msi是微软格式的安装包。

方法:1、利用right函数,语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”;2、利用substring函数,语法为“select substring(指定字段,2)..”。

在mysql中,可以利用char()和REPLACE()函数来替换换行符;REPLACE()函数可以用新字符串替换列中的换行符,而换行符可使用“char(13)”来表示,语法为“replace(字段名,char(13),'新字符串') ”。

转换方法:1、利用cast函数,语法“select * from 表名 order by cast(字段名 as SIGNED)”;2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于MySQL复制技术的相关问题,包括了异步复制、半同步复制等等内容,下面一起来看一下,希望对大家有帮助。

在mysql中,可以利用REGEXP运算符判断数据是否是数字类型,语法为“String REGEXP '[^0-9.]'”;该运算符是正则表达式的缩写,若数据字符中含有数字时,返回的结果是true,反之返回的结果是false。

在mysql中,可利用“ALTER TABLE 表名 DROP INDEX unique key名”语句来删除unique key;ALTER TABLE语句用于对数据进行添加、删除或修改操作,DROP INDEX语句用于表示删除约束操作。


熱AI工具

Undresser.AI Undress
人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

AI Hentai Generator
免費產生 AI 無盡。

熱門文章

熱工具

SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)

SAP NetWeaver Server Adapter for Eclipse
將Eclipse與SAP NetWeaver應用伺服器整合。

MinGW - Minimalist GNU for Windows
這個專案正在遷移到osdn.net/projects/mingw的過程中,你可以繼續在那裡關注我們。 MinGW:GNU編譯器集合(GCC)的本機Windows移植版本,可自由分發的導入函式庫和用於建置本機Windows應用程式的頭檔;包括對MSVC執行時間的擴展,以支援C99功能。 MinGW的所有軟體都可以在64位元Windows平台上運作。

Dreamweaver CS6
視覺化網頁開發工具

WebStorm Mac版
好用的JavaScript開發工具