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

最新推荐文章于 2024-10-30 10:59:51 发布

weixin_30888027

最新推荐文章于 2024-10-30 10:59:51 发布

阅读量115

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/jhcyzxx/p/10480090.html

版权

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)