MySQL数据库中group by的实现

有同学问到MySQL数据库group by是如何实现,发现可能存在误解,简单说明一下。

示例

CREATE TABLE `tb` (
`c` int(11) DEFAULT NULL,
`d` int(4) DEFAULT NULL,
`e` varchar(1000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Insert into tb values(2,20,’b’);
Insert into tb values(1,10,’a’);
Insert into tb values(2,20,’b’);
Insert into tb values(1,10,’a’);
Insert into tb values(3,30,’c’);

查询语句和结果

MySQL Cluster 与 MongoDB 复制群集分片设计及原理

分布式数据库计算涉及到分布式事务、数据分布、数据收敛计算等等要求

分布式数据库能实现高安全、高性能、高可用等特征,当然也带来了高成本(固定成本及运营成本),我们通过MongoDB及MySQL Cluster从实现上来分析其中的设计思路,用以抽象我们在设计数据库时,可以引用的部分设计方法,应用于我们的生产系统

 首先说说关系及非关系数据库的特征

 MySQL的Innodb及Cluster拥有完整的ACID属性

A 原子性  整个事务将作为一个整体,要么完成,要么回滚

C 一致性 事务开始之前和事务结束以后,数据库的完整性限制没有被破坏

MySQL数据库InnoDB存储引擎查询优化器实现的分析

投稿者:何登成  作者新浪微博:@何_登成

一、             目的

分析MySQL数据库的查询优化器如何优化InnoDB存储引擎的查询语句:

(1).     如何实现查询优化的功能?

(2).     为了能实现查询优化,存储引擎需要做哪些方面的配合?

二、             测试准备

MySQL数据库软件版本:5.1.49-debug-log

MySQL数据库InnoDB存储引擎查询优化器实现的分析之统计信息

 1       统计信息

1.1      统计信息收集

show index from nkeys;

函数调用流程:

sql_parse.cc::mysql_execute_command(lex->sql_command == SQLCOM_SHOW_KEYS) ->
sql_show.cc::get_schema_stat_record ->

  1. ha_innobase::info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK | HA_STATUS_TIME)(info函数,实现统计信息收集功能,宏定义说明了需要收集的统计信息的类型) ->
  2. dict0dict.c::dict_update_statistics(ib_table)(HA_STATUS_TIME,指定此参数时进行统计信息的重新收集) ->
  3. dict_update_statistics_low(统计信息收集主函数,遍历表上的所有index,进行统计信息收集,设置到dict_index与dict_table结构之中) -> btr_estimate_number_of_different_key_vals ->

MySQL数据库InnoDB存储引擎查询优化器实现的分析之optimizer_search_depth参数

 1.1     optimizer_search_depth参数

以上提到的greedy_search+best_extension_by_limited_search函数,通过search_depth参数控制递归调用的深度。而search_depth参数,可通过optimizer_search_depth来设置。

一般而言,如果optimizer_search_depth设置过大,那么join时,获取最优执行计划的代价十分巨大。

optimizer_search_depth = join tables的数量,一定能获得最优执行计划(根据mysql的代价估计模型),但是计算代价大。

optimizer_search_depth < join tables的数量,获取的执行计划,是局部最优,但是计算代价小。

MySQL数据库InnoDB存储引擎查询优化器实现的分析之best_access_path函数分析

1.1     best_access_path函数分析

同样是使用4.1章节中的测试语句:

select * from nkeys, aaa where nkeys.c3 = aaa.a3 and aaa.a2 = 2;

1.1.1  总流程分析

根据4.1章节的分析,join涉及到两张表,同时search_depth参数设置为62. N < search_depth,复杂度为O(N!) = 2! = 2(任何一张表,都可以做驱动表),同时,查询优化能够找到最优的执行计划。

MySQL数据库InnoDB存储引擎查询优化器实现的分析之多表简单JOIN查询

1       多表查询

1.1     多表简单join

select * from nkeys, aaa where nkeys.c3 = aaa.a3 and aaa.a2 = 2;

具体nkeys,aaa表的表定义,在附录一:aaa表;附录四:nkeys表中给出。

调用主流程:
1. mysql_select ->JOIN::optimize -> make_join_statistics ->SQL_SELECT::test_quick_select ->get_key_scans_params ->

根据查询条件,aaa表指定了查询条件:aaa.a2 = 2,对于指定查询条件的表,可以通过第3章节单表查询的流程找到针对查询条件的最优路径。当然,若aaa.a2列为unique列,那么就通过第3章节单表unique查询来判断。

MySQL数据库InnoDB存储引擎查询优化器实现的分析之单表unique查询

1.1     单表unique查询

select * from nkeys where c3 = 3;

调用流程:
mysql_execute_command -> handle_select -> mysql_select -> JOIN_optimize -> make_join_statistics ->
if ((table->key_info[key].flags & (HA_NOSAME | HA_END_SPACE_KEY)) == HA_NOSAME) ->

  1. 若当前索引为unique索引,同时

if (const_ref == eq_part) ->

  1. 指定的等值条件与当前索引的unique key一致

MySQL数据库InnoDB存储引擎查询优化器实现的分析之单表查询

1       单表查询

1.1     单表range查询

1)       select * from nkeys where c3 > 3;    不能进行索引覆盖扫描         index range scan
2)       select c3 from nkeys where c3 > 3; 可以进行索引覆盖扫描         index only range scan

调用流程:

msyql_select -> JOIN::optimize -> make_join_statistics ->get_quick_record_count -> SQL_SELECT::test_quick_select–>ha_innobase::scan_time ->get_key_scans_params ->check_quick_select–>Opt_range.cc::check_quick_keys ->ha_innobase::records_in_range-> get_index_only_read_time -> ha_innobase::read_time ->get_best_ror_intersect ->get_best_covering_ror_intersect ->

a)         ha_innobase::scan_time函数,给出全表扫描read_time