Mysql 四種常見的索引 · MySQL DBA

### Mysql 四種常見的索引提到Mysql?的優化，索引優化是必不可少的。其中一種優化方式 ——索引優化，添加合適的索引能夠讓項目的并發能力和抗壓能力得到明顯的提升。我們知道項目性能的瓶頸主要是在"(select)"查詢語句，而如果要提升"查詢"的性能，mysql索引是必不可少的。接下來總結一下mysql常見的四種索引 #### 一. 四種索引(主鍵索引/普通索引/全文索引/唯一索引) 1.索引的添加 1.1主鍵索引的添加當一張表，把某個列設為主鍵的時候，則該列就是主鍵索引 ~~~ create table a ( id int primary key auto_increment, name varchar(20) not null default '' ); ~~~ 這里id就是表的主鍵，如果當創建表時沒有指定主鍵索引，也可以在創建表之后添加： ~~~ alter table table_name add primary key (column_name); ~~~ 1.2普通索引普通索引一般是在建表后再添加的， ~~~ create index 索引名 on table_name(column1,column2); alter table table_name add index 索引名(column1,column2); ~~~ 1.3全文索引首先，全文索引主要針對文本文件，比如文章，標題，全文索引只有MyISAM有效，mysql5.6之后`InnoDB`也支持了全文索引。 ~~~ create table c( id int primary key auto_increment , title varchar(20), content text, fulltext(title,content) ) engine=myisam charset utf8; ~~~ ~~~ insert into c(title,content) values ('MySQL Tutorial','DBMS stands for DataBase ...'), ('How To Use MySQL Well','After you went through a ...'), ('Optimizing MySQL','In this tutorial we will show ...'), ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), ('MySQL vs. YourSQL','In the following database comparison ...'), ('MySQL Security','When configured properly, MySQL ...'); ~~~ 使用全文索引常見的錯誤： ~~~ select * from c where content like "%mysql%"; ~~~ 這里并不會使用全文索引，可以用explain進行查看。正確用法： ~~~ select * from c where match(title,content) against ('MYSQL'); ~~~ 備注: 1. 在mysql中fulltext 索引一般只針對 myisam引擎生效 2. mysql自己提供的fulltext針對英文生效->sphinx(coreseek)技術處理中文 3. 使用方法是： ~~~ match(字段名..) against(‘關鍵字’) ~~~ 1.4唯一索引 ~~~ create table d( id int primary key auto_increment , name varchar(32) unique ) ~~~ d表中name就是唯一索引，唯一索引可以有多個null,不能是重復的內容相比主鍵索引，主鍵字段不能為null，也不能重復 2.查詢索引 ~~~ show indexes from table_name; show keys from table_name; ~~~ 3.刪除索引 ~~~ alter table table_name drop index 索引名; ~~~ #### 二. 索引的機制 2.1 為什么我們添加完索引后查詢速度會變快？傳統的查詢方法，是按照表的順序遍歷的，不論查詢幾條數據，mysql需要將表的數據從頭到尾遍歷一遍在我們添加完索引之后，mysql一般通過BTREE算法生成一個索引文件，在查詢數據庫時，找到索引文件進行遍歷，**使用折半查找的方式能夠大幅地查詢的效率**，找到相應的鍵從而獲取數據。 2.2 索引的代價創建索引是為產生索引文件的，占用磁盤空間。索引文件是一個二叉樹類型的文件，可想而知我們的DML操作((數據操作語言,對表記錄的(增、刪、改)操作)同樣也會對索引文件進行修改，所以性能會相應的有所下降。 2.3 在哪些column上使用索引？ 1. 較頻繁的作為查詢條件字段應該創建索引 2. 唯一性太差的字段不適合創建索引，盡管頻繁作為查詢條件，例如gender性別字段 3. 更新非常頻繁的字段不適合作為索引 4. 不會出現在where子句中的字段不該創建索引總結: 滿足以下條件的字段，才應該創建索引. 1. 在where條件常使用的字段。 2. 該字段的內容不是唯一的幾個可選值，而是有較豐富的取值選項的字段。 3. 該字段內容不是頻繁變化的。 #### 三. 索引使用注意事項 1. 對于創建的多列索引，只要查詢條件使用了最左邊的列，索引一般就會被使用。比如我們對title,content 添加了復合索引 ~~~ select * from table_name where title = 'test';會用到索引 select * from table_name where content = 'test';不會用到索引 ~~~ 2. 對于使用like的查詢，查詢如果是 ‘%a’不會使用到索引 ,而 like 'a%'就會用到索引。因此為了使用索引，查詢條件的最前面不能使用%和_這樣的變化值。 3. 如果查詢條件中有or，即使其中有查詢條件帶索引也不會使用。 4. 如果列類型是字符串，那一定要在條件中將數據使用引號引用起來。 #### 四. 如何查看索引使用的情況: ~~~ show status like 'Handler_read%'; ~~~ 注意： * handler_read_key:這個值越高越好，越高表示使用索引查詢到的次數。 * handler_read_rnd_next:這個值越高，說明查詢比較的低效。