MySQL 性能分析 之 联合索引(复合索引)实践分析

作为开发者,大家都知道,一个服务器、一个数据库的性能是项目的重中之重,后台架构、写法与数据库设计的好坏往往直接影响到整个项目的性能。

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

这里给分析一下MySQL的索引;索引分:普通索引和联合索引,而索引的关键相信很多人也知道,用非主键字段进行查询的时候MySQL在查询的时候就是扫表行为,如果有索引的话,情况就得到了大大的改善。

加索引的时候,先建议使用单列索引一个一个加!然后再改进使用联合索引!而且最好是根据自己的项目业务查询来统计 哪些字段的使用率是多少 哪些字段组合在一次使用了多少次,因为今天分析的就是联合索引并不是在任何情况下都命中的

表结构

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=1001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)  

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

模拟了数据后 我们来查看结果

  • [1 3 命中]

select * from m_user where name='leal' and status=1

mysql> desc select * from m_user where name='leal' 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)  
  • [1 3 order by 3 命中]

select * from m_user where name='leal' and status=1 order by status desc

mysql> desc select * from m_user where name='leal' 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)  
  • [2 3 不命中]

select * from m_user where age=10 and status=1

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: 1000
     filtered: 1.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)  
  • [1 in 命中]

select * from m_user where name in ('leal') and age<10

select * from m_user where name in ('leal') and age<10 order by school

mysql> desc select * from m_user where name in ('leal') 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)  
  • [1 between 不命中]

select * from m_user where name between 'leal' and 'tom'

select * from m_user where name between 'leal' and 'tom' order by school desc

mysql> desc select * from m_user where name between 'leal' and  'tom' 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: 1000
     filtered: 22.38
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)  
  • [1 <> 不命中]

select * from m_user where name<>'leal'

select * from m_user where name<>'leal' and age<10

select * from m_user where name<>'leal' and age<10 order by school desc

mysql> desc select * from m_user where name<>'leal' 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: 1000
     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<>'leal'\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: 1000
     filtered: 99.90
        Extra: Using where
1 row in set, 1 warning (0.00 sec)  
  • [1 < 或 <= 命中]

select * from m_user where name < 'leal'

select * from m_user where name <= 'leal'

select * from m_user where name <= 'leal' and age<10

select * from m_user where name <= 'leal' and age<10 order by school desc

mysql> desc select * from m_user where name < 'leal' 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)  
  • [1 > 或 >= 不命中]

select * from m_user where name>'leal'

select * from m_user where name>='leal'

mysql> desc select * from m_user where name >= 'leal' 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: 1000
     filtered: 33.30
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)  
  • [无where条件 直接order by 不命中]

select * from m_user order by name desc

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: 1000
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.13 sec)  

结束语

一般人和一个项目都是从小到大逐渐健壮的,前期一般的项目 直接加索引就够了,保证命中率;当项目业务复杂到一定程度或者负载到一定程度的时候,就得删除索引,自建索引表来管理数据库的索引了。

本文为作者原创,手码不易,允许转载,转载后请以链接形式说明文章出处. 如转载但不标明来源,后果自负。

您的支持是对我最大的鼓励!