第三个模块 MySQL 5.7之联合(复合)索引实践

论mysql5.7.13性能优化之索引优化

索引:是当你的业务完成后,跟据查询条件来建立的。当你的数据量大(一般是10万条数据)了之后,我们会再把普通索引删除,使用自建索引表。因为数据量大的时候你要批量修改(索引表也会修改)会变的非常的慢!

加索引的时候,先建议使用单列索引一个一个加!然后再改进使用联合索引!

本文是针对mysql 5.7联合(复合)索引进行验证测试!
版本:mysql 5.7
测试结果日期:2017-01-02

表结构:

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

联合索引字段:
KEY name (name,age,status)

序号 字段名 类型
1 name char
2 age tinyint
3 status tinyint

结果:

  1. [1 3 命中]
    select * from m_user where name='feng1' and status=1

  2. [1 3 order by 3 命中]
    select * from m_user where name='feng1' and status=1 order by status desc

  3. [2 3 不命中]
    select * from m_user where age=10 and status=1

  4. [1 in 命中]
    select * from m_user where name in ('feng1') and age<10
    select * from m_user where name in ('feng1') and age<10 order by school

  5. [1 between 不命中]
    select * from m_user where name between 'feng1' and 'feng3'
    select * from m_user where name between 'feng1' and 'feng3' order by school desc

  6. [1 <> 不命中]
    select * from m_user where name<>'feng1'
    select * from m_user where name<>'feng1' and age<10
    select * from m_user where name<>'feng1' and age<10 order by school desc

  7. [1 < 或 <= 命中]
    select * from m_user where name < 'feng1'
    select * from m_user where name <= 'feng1'
    select * from m_user where name <= 'feng1' and age<10
    select * from m_user where name <= 'feng1' and age<10 order by school desc

  8. [1 > 或 >= 不命中]
    select * from m_user where name>'feng1'
    select * from m_user where name>='feng1'

  9. [无where条件 直接order by 不命中]
    select * from m_user order by name desc


测试过程

1.[1 3 命中]

mysql> desc select * from m_user where name='feng1' and status=1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m_user
   partitions: NULL
         type: ref
possible_keys: name
          key: name
      key_len: 96
          ref: const
         rows: 1
     filtered: 10.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

2.[1 3 order by 3 命中]

mysql> desc select * from m_user where name='feng1' and status=1 order by status desc \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m_user
   partitions: NULL
         type: ref
possible_keys: name
          key: name
      key_len: 96
          ref: const
         rows: 1
     filtered: 10.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

3.[2 3 不命中]

mysql> desc select * from m_user where age=10 and status=1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m_user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1001
     filtered: 1.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

4.[1 in 命中]

mysql> desc select * from m_user where name in ('feng1') and age<10 order by school
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m_user
   partitions: NULL
         type: range
possible_keys: name
          key: name
      key_len: 97
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)

5.[1 between 不命中]

mysql> desc select * from m_user where name between 'feng1' and  'feng3' order by school desc \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m_user
   partitions: NULL
         type: ALL
possible_keys: name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1001
     filtered: 22.38
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

6.[1 <> 不命中]

mysql> desc select * from m_user where name<>'feng1' and age<10 order by school desc \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m_user
   partitions: NULL
         type: ALL
possible_keys: name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1001
     filtered: 33.30
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)
--------------------------------------------------------------

mysql> desc select * from m_user where name<>'feng1'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m_user
   partitions: NULL
         type: ALL
possible_keys: name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1001
     filtered: 99.90
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

7.[1 < 或 <= 命中]

mysql> desc select * from m_user where name < 'feng1' and age<10 order by school desc \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m_user
   partitions: NULL
         type: range
possible_keys: name
          key: name
      key_len: 96
          ref: NULL
         rows: 1
     filtered: 33.33
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)

8.[1 > 或 >= 不命中]

mysql> desc select * from m_user where name >= 'feng1' and age<10 order by school desc \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m_user
   partitions: NULL
         type: ALL
possible_keys: name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1001
     filtered: 33.30
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

9.[无where条件 直接order by 不命中]

mysql> explain select * from m_user order by name desc\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m_user
   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.13 sec)

推荐阅读更多精彩内容