索引初識一 MySql

2024-07-24 12:36:18

字體：大中小

供稿：網(wǎng)友

  索引初識一 MySql:

1 mysql索引類型【主要分4類索引】

   創(chuàng)建索引：

        1.添加PRIMARY KEY（主鍵索引）【主鍵：一種唯一性索引，必須指定為primary key 】

        mysql> ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

        2.添加UNIQUE(唯一索引)

        mysql> ALTER TABLE `table_name` ADD UNIQUE ( `column` )

        3.添加INDEX(普通索引)

        mysql> ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

        4.添加FULLTEXT(全文索引) 【從3.23.23版開始支持全文索引和全文檢索，F(xiàn)ULLTEXT，可以在char、varchar或text類型的列上創(chuàng)建】

        mysql> ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

        5.添加多列索引【注意:多個單列索引與單個多列索引的查詢效果不同，因為:執(zhí)行查詢時，MySQL只能使用一個索引，會從多個索引中選擇一個限制最為嚴(yán)格的索引】

        mysql> ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, column3`)

    刪除索引：

                DROP INDEX index_name ON talbe_name

                ALTER TABLE table_name DROP INDEX index_name

                ALTER TABLE table_name DROP PRIMARY KEY

2 mysql索引存儲格式

　　存儲引擎可以選擇Hash或BTree索引【默認(rèn)】方法，Hash索引方法只能用于=或<=>的等式比較。

　　B樹跟Hash跟全文搜索下期再細(xì)分。

3 如何根據(jù)sql語句確定索引類型

　　a.性能優(yōu)化過程中，選擇在哪個列上創(chuàng)建索引是最重要的步驟之一。可以考慮使用索引的主要有兩種類型的列：在where子句中出現(xiàn)的列，在join子句中出現(xiàn)的列。

　　b.考慮列中值的分布，索引的列的基數(shù)越大，索引的效果越好。

　　c.使用短索引，如果對字符串列進(jìn)行索引，應(yīng)該指定一個前綴長度，可節(jié)省大量索引空間，提升查詢速度。

　　d.利用最左前綴, 顧名思義，就是最左優(yōu)先，比如我們創(chuàng)建了lname_fname_age多列索引,相當(dāng)于創(chuàng)建了(lname)單列索引，(lname,fname)組合索引以及(lname,fname,age)組合索引。

　　e.不要過度索引，只保持所需的索引。每個額外的索引都要占用額外的磁盤空間，并降低寫操作的性能。

　　在修改表的內(nèi)容時，索引必須進(jìn)行更新，有時可能需要重構(gòu)，因此，索引越多，所花的時間越長。

　　MySQL只對一下操作符才使用索引：<,<=,=,>,>=,between,in,

　　以及某些時候的like(不以通配符%或_開頭的情形)。

4 案例和原理

         以我公司房屋數(shù)據(jù)庫為例，總共700w的數(shù)據(jù)。同樣一條搜索語句SELECT id from t_basic_house where storey_name like '6躍7%';

         非索引：28.525s，有索引：0.263s。

         原理：非索引狀態(tài)下想要搜索 “6躍7”的數(shù)據(jù)，必須整張表的每行數(shù)據(jù)，效率極低。

                   索引狀態(tài)下索引是在storey_name 上排序的。現(xiàn)在，當(dāng)查找storey_name 信息時，就不需要逐行搜索全表，可以利用索引進(jìn)行有序查找（如二分查找法），并快速定位到匹配的值，以節(jié)省大量搜索時間。



拓展：UUID的性能并不比自增ID差很多，這取決于UUID的生成算法。舉個例子MongoDB所采用的ObjectId就是一個比較優(yōu)秀的UUID策略，其組成是時間戳+機(jī)器碼+進(jìn)程碼+自增數(shù)，其中機(jī)器碼和進(jìn)程碼都可以一次性生成，這樣得到一個ObjectId僅僅之比自增ID多了一個時間戳的獲取。另外考慮到自增ID都要做主鍵唯一索引，而UUID可以只做索引，不做唯一索引（利用其特性，可以不考慮唯一性過濾），其性能可以說并不比自增ID差。

           至于使用UUID還是自增ID主要還是看項目是否足夠龐大，數(shù)據(jù)量是否足夠多。從使用方便性上來說自增ID使用簡單，不需要額外支持，而UUID相對麻煩一些，涉及到UUID算法的選取、程序的嵌入等等。而從應(yīng)對龐大系統(tǒng)的效果上來說，UUID就比自增ID顯得優(yōu)秀得多。怎么選擇就是看自己的實際情況，按需選擇。

（編輯：武林網(wǎng)）

上一篇：MySQL 5.7復(fù)制延遲之sync_relay_log

下一篇：MySQL8.0新特征支持原子DDL語句