索引:是當(dāng)你的業(yè)務(wù)完成后，跟據(jù)查詢條件來建立的。當(dāng)你的數(shù)據(jù)量大（一般是10萬條數(shù)據(jù)）了之后，我們會再把普通索引刪除，使用自建索引表。因為數(shù)據(jù)量大的時候你要批量修改（索引表也會修改）會變的非常的慢！

加索引的時候，先建議使用單列索引一個一個加！然后再改進使用聯(lián)合索引！

本文是針對mysql 5.7多列[單]索引進行驗證測試!
版本：mysql 5.7
測試結(jié)果日期：2017-01-05

表結(jié)構(gòu)：

mysql> show create table m_user2\G;
*************************** 1. row ***************************
       Table: m_user2
Create Table: CREATE TABLE `m_user2` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `name` char(32) NOT NULL,
  `age` tinyint(4) NOT NULL,
  `school` char(128) NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '1',
  KEY `name` (`name`),
  KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

多列[單]索引字段：
KEY name (name),
KEY age (age)

結(jié)果：

1.[1 and 2 命中最嚴(yán)格的一個]
select * from m_user2 where name='feng1' and age=10

2.[1 or 2 兩個都命中]
select * from m_user2 where name='feng1' or age=10

3.[1 or 2 order by 1 命中]
select * from m_user2 where name='feng1' or age=10 order by name desc

4.[between 命中]
select * from m_user2 where name between 'feng1' and 'feng123' order by name desc
select * from m_user2 where age between 10 and 20 order by name desc

5.[in 命中]
select * from m_user2 where name in ('feng1','feng123') order by name desc

6.[無where條件 直接order by 不命中]
select * from m_user2 order by name desc

測試過程

1.[1 and 2 命中最嚴(yán)格的一個]

mysql> explain select * from m_user2 where name='feng1' and age=10\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m_user2
   partitions: NULL
         type: ref
possible_keys: name,age
          key: name
      key_len: 96
          ref: const
         rows: 1
     filtered: 5.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

2.[1 or 2 兩個都命中]

mysql> explain select * from m_user2 where name='feng1' or age=10\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m_user2
   partitions: NULL
         type: index_merge
possible_keys: name,age
          key: name,age
      key_len: 96,1
          ref: NULL
         rows: 17
     filtered: 100.00
        Extra: Using union(name,age); Using where
1 row in set, 1 warning (0.01 sec)

3.[1 or 2 order by 1 命中]

mysql> explain select * from m_user2 where name='feng1' or age=10 order by name desc\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m_user2
   partitions: NULL
         type: index_merge
possible_keys: name,age
          key: name,age
      key_len: 96,1
          ref: NULL
         rows: 17
     filtered: 100.00
        Extra: Using union(name,age); Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

4.[between 命中]

mysql> explain select * from m_user2 where name between 'feng1' and 'feng123' order by name desc\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m_user2
   partitions: NULL
         type: range
possible_keys: name
          key: name
      key_len: 96
          ref: NULL
         rows: 29
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
---------------------------------------------------------------------
mysql> explain select * from m_user2 where age between 10 and 20 order by name desc\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m_user2
   partitions: NULL
         type: range
possible_keys: age
          key: age
      key_len: 1
          ref: NULL
         rows: 134
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.01 sec)

5.[in 命中]

mysql> explain select * from m_user2 where name in ('feng1','feng123') order by name desc\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m_user2
   partitions: NULL
         type: range
possible_keys: name
          key: name
      key_len: 96
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.01 sec)

6.[無where條件 直接order by 不命中]

mysql> explain select * from m_user2 order by name desc\G; 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m_user2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1001
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)