mysql建立索引實例,mysql索引如何實現

本文目錄一覽：

• 1、mysql資料庫是否可以為視圖創建索引,可以的話，請問如果創建
• 2、Mysql建立索引經驗
• 3、mysql索引
• 4、mysql聯合索引如何創建？
• 5、如何為表建立索引？
• 6、mysql如何建立索引

mysql資料庫是否可以為視圖創建索引,可以的話，請問如果創建

mysql資料庫可以為視圖創建索引，創建方法為：

1、在mysql資料庫中為數據表創建索引時，可在創建表的時候直接創建索引，如下圖創建普通索引。

2、通過explain命令可以查看正在被使用的索引。

3、另外還可以創建唯一索引，唯一索引要求被約束列的值是唯一值，不能重複。

4、如果唯一索引列的值在創建時出現重複是無法正常插入的，如下圖。

5、另外還可以通過multiidx在同一個表中創建複合索引，如下圖。

6、在已經存在的表上，也可以直接通過create來添加索引，如下圖。

Mysql建立索引經驗

在實際開發中使用資料庫時，難免會遇到一些大表數據，對這些數據進行查詢時，有時候SQL會查詢得特別慢，這時候，有經驗的老師傅會告訴你，你看一下哪幾個欄位查的多，加一個索引就好了。

那麼，怎麼合理地建立索引呢？這裡分享一下我的一些經驗，如有不妥之處，歡迎批評指正。

1、不要盲目建立索引 ， 先分析再創建

索引雖然能大幅度提升我們的查詢性能，但也要知道，在你進行增刪改時，索引樹也要同樣地進行維護。所以，索引不是越多越好，而是按需建立。最好是在一整塊模塊開發完成後，分析一下，去針對大多數的查詢，建立聯合索引。

2、使用聯合索引盡量覆蓋多的條件

這是說在一個慢sql里假如有五個where ，一個 order by ，那麼我們的聯合索引盡量覆蓋到這五個查詢條件，如果有必要，order by 也覆蓋上 。

3、小基數欄位不需要索引

這個意思是，如果一張表裡某個欄位的值只有那麼幾個，那麼你針對這個欄位建立的索引其實沒什麼意義，比如說，一個性別欄位就兩種結果，你建了索引，排序也沒什麼意思（也就是索引里把男女給分開了）

所以說，索引盡量選擇基數大的數據去建立，能最大化地利用索引

4、長字元串可以使用前綴索引

我們建立索引的欄位盡量選擇欄位類型較小的，比如一個varchar(20)和varchar(256)的，我們在20的上面建立的索引和在256上就有明顯的差距（字元串那麼長排序也不好排呀，唉）。

當然，如果一定是要對varchar(256)建立索引,我們可以選擇裡面的前20個字元放在索引樹里（這裡的20不絕對，選擇能盡量分辨數據的最小字元欄位設計），類似這樣KEY index(name(20),age,job) ，索引只會對name的前20個字元進行搜索，但前綴索引無法適用於order by 和 group by。

5、對排序欄位設計索引的優先順序低

如果一個SQL里我們出現了範圍查找，後邊又跟著一個排序欄位，那麼我們優先給範圍查找的欄位設置索引，而不是優先排序。

6、如果出現慢SQL，可以設計一個只針對該條SQL的聯合索引。

不過慢SQL的優化，需要一步步去進行分析，可以先用explain查看SQL語句的分析結果，再針對結果去做相應的改進。explain的東西我們下次再講。

PS：在 select 語句之前增加 explain 關鍵字，MySQL 會在查詢上設置一個標記，執行查詢會返回執行計劃的信息，而不是 執行這條SQL。

mysql索引

二叉搜索樹、N叉樹

頁分裂：B+樹的插入可能會引起數據頁的分裂，刪除可能會引起數據頁的合併，二者都是比較重的IO消耗，所以比較好的方式是順序插入數據，這也是我們一般使用自增主鍵的原因之一。

頁分裂逆過程：頁合併，當刪除數據後，相鄰的兩個數據頁利用率很低的時候會做數據頁合併

主鍵索引：key：主鍵，value：數據頁，存儲每行數據

非主鍵索引：key：非主鍵索引，value：主鍵key，導致回表

最左匹配：優先將區分度高的列放到前面，這樣可以高效索引，

最左匹配原則遇到範圍查詢就停止匹配，範圍查詢(、、between、like)為什麼？因為出現範圍匹配後，後面的索引欄位無法保證有序，局部有序失去，順序失去則無法提高查詢效率

SELECT * FROM table WHERE a IN (1,2,3) and b 1;

如何建立索引？

還是對(a，b)建立索引，因為IN在這裡可以視為等值引用，不會中止索引匹配，所以還是(a,b)!

索引組織表

索引用頁存儲：key【10】-point【6】，通過調整key大小，當頁大小固定的情況下，通過調整key大小，使得N叉樹變化；

如key 10, point 6則單個索引16位元組，頁大小為16k，則頁面總共可以存儲1024個索引，即N大小

覆蓋索引: 二級索引的信息已經存在想要的列，例如主鍵

如果現在有一個高頻請求，要根據市民的身份證號查詢他的姓名，這個聯合索引就有意義了。它可以在這個高頻請求上用到覆蓋索引，不再需要回表查整行記錄，減少語句的執行時間。

索引下推優化：可以在索引遍歷過程中，對索引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。

整理索引碎片，重建表：alter table T engine=InnoDB

  首先是看key的大小，另外是數據頁的大小，如果需要改變N，則需要從這兩個方面做改動；

一個innoDB引擎的表，數據量非常大，根據二級索引搜索會比主鍵搜索快，文章闡述的原因是主鍵索引和數據行在一起，非常大搜索慢，我的疑惑是：通過普通索引找到主鍵ID後，同樣要跑一邊主鍵索引，對於使用覆蓋索引的情況下，使用覆蓋索引可以直接解決問題

mysql聯合索引如何創建？

CREATE TABLE `test` (‘aaa’ varchar(16) NOT NULL default ”, ‘bbb’ varchar(16) NOT NULL default ”, ‘ccc’ int(11) UNSIGNED NOT NULL default 0, KEY `sindex` (`aaa`,`bbb`,`ccc`) ) ENGINE=MyISAM COMMENT=”;\x0d\x0a\x0d\x0a這樣就在 aaa、bbb、ccc 3列上建立聯合索引了。\x0d\x0a\x0d\x0a如果表已經建好了，那麼就在phpmyadmin裡面執行：\x0d\x0aalert table test add INDEX `sindex` (`aaa`,`bbb`,`ccc`) \x0d\x0a\x0d\x0a就可以在這3列上建立聯合索引了。

如何為表建立索引？

建立索引的優缺點:

為什麼要創建索引呢？

這是因為，創建索引可以大大提高系統的性能。

第一、通過創建唯一性索引，可以保證資料庫表中每一行數據的唯一性。

第二、可以大大加快 數據的檢索速度，這也是創建索引的最主要的原因。

第三、可以加速表和表之間的連接，特別是在實現數據的參考完整性方面特別有意義。

第四、在使用分組和排序子句進行數據檢索時，同樣可以顯著減少查詢中分組和排序的時間。

第五、通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。

也許會有人要問：增加索引有如此多的優點，為什麼不對表中的每一個列創建一個索引呢？這種想法固然有其合理性，然而也有其片面性。雖然，索引有許多優點， 但是，為表中的每一個列都增加索引，是非常不明智的。

這是因為，增加索引也有許多不利的一個方面:

第一、創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加。

第二、索引需要佔物理空間，除了數據表占數據空間之外，每一個索引還要佔一定的物理空間。如果要建立聚簇索引，那麼需要的空間就會更大。

第三、當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了數據的維護速度。

什麼樣的欄位適合創建索引:

索引是建立在資料庫表中的某些列的上面。因此，在創建索引的時候，應該仔細考慮在哪些列上可以創建索引，在哪些列上不能創建索引。

一般來說，應該在這些列上創建索引，例如：

第一、在經常需要搜索的列上，可以加快搜索的速度；

第二、在作為主鍵的列上，強制該列的唯一性和組織表中數據的排列結構；

第三、在經常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；

第四、在經常需要根據範圍進行搜索的列上創建索引，因為索引已經排序，其指定的範圍是連續的；

第五、在經常需要排序的列上創建索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；

第六、在經常使用在WHERE子句中的列上面創建索引，加快條件的判斷速度。

建立索引，一般按照select的where條件來建立，比如： select的條件是where f1 and f2，那麼如果我們在欄位f1或欄位f2上簡歷索引是沒有用的，只有在欄位f1和f2上同時建立索引才有用等。

什麼樣的欄位不適合創建索引:

同樣，對於有些列不應該創建索引。一般來說，不應該創建索引的的這些列具有下列特點：

第一，對於那些在查詢中很少使用或者參考的列不應該創建索引。這是因為，既然這些列很少使用到，因此有索引或者無索引，

並不能提高查詢速度。相反，由於增加了索引，反而降低了系統的維護速度和增大了空間需求。

第二，對於那些只有很少數據值的列也不應該增加索引。這是因為，由於這些列的取值很少，例如人事表的性別列，

在查詢的結果中，結果集的數據行佔了表中數據行的很大比 例，即需要在表中搜索的數據行的比例很大。

增加索引，並不能明顯加快檢索速度。

第三，對於那些定義為text, p_w_picpath和bit數據類型的列不應該增加索引。這是因為，這些列的數據量要麼相當大，要麼取值很少。

第四，當修改性能遠遠大於檢索性能時，不應該創建索 引。這是因為，修改性能和檢索性能是互相矛盾的。

當增加索引時，會提高檢索性能，但是會降低修改性能。當減少索引時，會提高修改性能，降低檢索性能。

因此，當修改性能遠遠大於檢索性能時，不應該創建索引。

創建索引的方法::

1、創建索引，例如 create index 索引的名字 on table_name (列的列表);

2、修改表，例如 alter table table_name add index[索引的名字] (列的列表);

3、創建表的時候指定索引，例如create table table_name ( […], INDEX [索引的名字] (列的列表) );

查看錶中索引的方法:

show index from table_name; 查看索引

索引的類型及創建例子::

1.PRIMARY KEY （主鍵索引）

mysql alter table table_name add primary key ( `column` )

2.UNIQUE 或 UNIQUE KEY (唯一索引)

mysql alter table table_name add unique (`column`)

3.FULLTEXT (全文索引)

mysql alter table table_name add fulltext (`column` )

4.INDEX (普通索引)

mysql alter table table_name add index index_name ( `column` )

5.多列索引 (聚簇索引)

mysql alter table `table_name` add index index_name ( `column1`, `column2`, `column3` )

修改表中的索引：

alter table tablename drop primary key,add primary key(fileda,filedb)

mysql如何建立索引

我們可以通過查看索引的屬性來判斷創建索引的方法。

查看索引的語法格式如下：

SHOW INDEX FROM 表名 [ FROM 資料庫名]

語法說明如下：

表名：指定需要查看索引的數據表名。

資料庫名：指定需要查看索引的數據表所在的資料庫，可省略。比如，SHOW INDEX FROM student FROM test; 語句表示查看 test 資料庫中 student 數據表的索引。

示例

使用 SHOW INDEX 語句查看《MySQL創建索引》一節中 tb_stu_info2 數據表的索引信息，SQL 語句和運行結果如下所示。

mysql SHOW INDEX FROM tb_stu_info2\G

1. row

Table: tb_stu_info2

Non_unique: 0

Key_name: height

Seq_in_index: 1

Column_name: height

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.03 sec)

其中各主要參數說明如下：

參數 說明

Table 表示創建索引的數據表名，這裡是 tb_stu_info2 數據表。

Non_unique 表示該索引是否是唯一索引。若不是唯一索引，則該列的值為 1；若是唯一索引，則該列的值為 0。

Key_name 表示索引的名稱。

Seq_in_index 表示該列在索引中的位置，如果索引是單列的，則該列的值為 1；如果索引是組合索引，則該列的值為每列在索引定義中的順序。

Column_name 表示定義索引的列欄位。

Collation 表示列以何種順序存儲在索引中。在 MySQL 中，升序顯示值「A」（升序），若顯示為 NULL，則表示無分類。

Cardinality 索引中唯一值數目的估計值。基數根據被存儲為整數的統計數據計數，所以即使對於小型表，該值也沒有必要是精確的。基數越大，當進行聯合時，MySQL 使用該索引的機會就越大。

Sub_part 表示列中被編入索引的字元的數量。若列只是部分被編入索引，則該列的值為被編入索引的字元的數目；若整列被編入索引，則該列的值為 NULL。

Packed 指示關鍵字如何被壓縮。若沒有被壓縮，值為 NULL。

Null 用於顯示索引列中是否包含 NULL。若列含有 NULL，該列的值為 YES。若沒有，則該列的值為 NO。

Index_type 顯示索引使用的類型和方法（BTREE、FULLTEXT、HASH、RTREE）。

Comment 顯示評註。