概述
索引是存儲引擎用于快速查找記錄的一種數(shù)據(jù)結(jié)構(gòu)，通過合理的使用數(shù)據(jù)庫索引可以大大提高系統(tǒng)的訪問性能，接下來主要介紹在MySQL數(shù)據(jù)庫中索引類型，以及如何創(chuàng)建出更加合理且高效的索引技巧。
** 這里主要針對的是InnoDB存儲引擎的B+Tree索引數(shù)據(jù)結(jié)構(gòu)。**

1、索引的優(yōu)點(diǎn)

• 大大減輕了服務(wù)器需要掃描的數(shù)據(jù)量，從而提高了數(shù)據(jù)的檢索速度
• 幫助服務(wù)器避免排序和臨時表
• 可以將隨機(jī)I/O變?yōu)轫樞騃/O

2、索引的創(chuàng)建

2.1 主鍵索引

alter table 'table_name' add primary key 'index_name' ('column');

2.2 唯一索引

alter table 'table_name' add unique 'index_name' ('column');

2.3 普通索引

alter table 'table_name' add index 'index_name' ('column');

2.4 全文索引

alter table 'table_name' add fulltext 'index_name' ('column');

2.5 組合索引

alter table 'table_name' add index 'index_name' ('column1','column2');

3、B+Tree的索引規(guī)則

創(chuàng)建一個測試的用戶表:
create table user_test(id int AUTO_INCREMENT PRIMARY KEY, user_name varchar(30) NOT NULL, sex bit(1) NOT NULL DEFAULT b'1', city varchar(50) NOT NULL, age int NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql_index01.png

mysql_index02.png

3.1 索引有效的查詢

3.1.1全值匹配

全值匹配值指的是和索引中的所有列進(jìn)行匹配，如：以上面創(chuàng)建的索引為例，在where條件后可以同時查詢(user_name,city,age)為條件的數(shù)據(jù)。
注：與where后面查詢條件的順序無關(guān)，這里比較容易弄錯。
select * from user_test where user_name='feinik' and age=26 and city='北京';

3.1.2匹配最左前綴

匹配最左前綴是指優(yōu)先匹配最左索引列，如：上面創(chuàng)建的索引可用于查詢條件為：(user_name)、(user_name,city)、(user_name,city,age)
注：滿足最左前綴查詢條件的順序與索引列的順序無關(guān)，如：(city,user_name)、(age,city,user_name)

3.1.3匹配列前綴

指匹配列值的開頭部分，如：查詢用戶名以feinik開頭的所有用戶。
select * from user_test where user_name like 'feinik%';

3.1.4匹配范圍值

如：查詢用戶以feinik開頭的所有用戶，這里使用了索引的第一列。
select * from user_test where user_name like 'feinik%';

3.2索引的限制

• where查詢條件中不包含索引列中的最左索引列，則無法使用到索引查詢，如：
  select * from user_test where city='北京';
  或
  select * from user_test where age='26';
  或
  select * from user_test where city='北京' and age=26;
• 即使where的查詢條件是最左列索引列，也無法使用索引查詢用戶名以feinik 結(jié)尾的用戶
  select * from user_test where user_name like '%feinik';
• 如果where查詢條件中有某個列的范圍查詢，則其右邊的所有列都無法使用索引優(yōu)化查詢，如：
  select * from user_test where user_name='feinik' and city like '北京%';

參考：https://www.zhihu.com/question/36996520?sort=created