当前位置: 澳门新濠3559 > 数据库 > 正文

存储过程很强大,本文首先介绍了MySQL的查询计划

时间:2019-12-09 01:06来源:数据库
容我说几句题外话:我的工作日常是用微软系的,SQL SERVICE存储过程很强大,我也很习惯很喜欢用存储过程。和MySQL结缘,是在五年前,因为一些原因,公司要求用开源免费的数据库。很

容我说几句题外话:我的工作日常是用微软系的,SQL SERVICE 存储过程很强大,我也很习惯很喜欢用存储过程。和MySQL结缘,是在五年前,因为一些原因,公司要求用开源免费的数据库。很多时候,用mysql的程序员是不会去用存储过程的,除了调试麻烦外,还有其它各种小问题。说实在的,MySQL这些年发展很快,但和SQL SERVICE的差距还是很大的。好了,不说废话了,言归正转。

MySQL的查询计划中ken_len的含义,mysqlken_len

本文首先介绍了MySQL的查询计划中ken_len的含义;然后介绍了key_len的计算方法;最后通过一个伪造的例子,来说明如何通过key_len来查看联合索引有多少列被使用。

 

key_len的含义

在MySQL中,可以通过explain查看SQL语句所走的路径,如下所示:

  1. mysql> create table t(a int primary key, b int not null, c int not null, index(b));
     Query OK, 0 rows affected (0.01 sec)
     mysql> explain select b from t ;
     +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
     | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
     +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
     |  1 | SIMPLE      | t     | index | NULL          | b    | 4       | NULL |    1 | Using index |
     +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
     1 row in set (0.00 sec)

 

其中,key_len表示使用的索引长度,是以字节为单位。在上面的例子中,由于int型占用4个字节,而索引中只包含了1列,所以,key_len是4。

下面是联合索引的情况:

  1. mysql> alter table t add index ix(b, c);
    Query OK, 0 rows affected (0.03 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    mysql> explain select b, c from t ;
    +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
    | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
    +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | t     | index | NULL          | ix   | 8       | NULL |    1 | Using index |
    +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
    1 row in set (0.00 sec)

 

联合索引ix包含了2列,并且都使用到了,所以,这里ken_len是8。

到这里,我们已经可以理解key_len的含义了,似乎已经没有什么可讲的了,但是,MySQL中key_len的计算还有很多需要注意的地方。

例如,我们将b这一列的NOT NULL约束去掉,然后ken_len就和我们预期不一样了,如下所示:

  1. mysql> alter table t modify b int;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
     
    mysql> explain select b from t;
    +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
    | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
    +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | t     | index | NULL          | b    | 5       | NULL |    1 | Using index |
    +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
    1 row in set (0.00 sec)

 

首先,描述我的场景

MySQL中key_len计算规则

MySQL中,key_len的计算规则如下:

此外,decimal列的计算方法与上面一样,如果可以为空,则在数据类型占用字节的基础上加1,但是,decimal本身所占用字节数,计算就比较复杂。

根据官方文档可以知道,decimal定义为decimal(M,D),其中,M是总的位数,D是小数点后保留的位数。小数点前与小数点后的数字分开存储,且以9位数为1组,用4个字节保存,如果低于9位数,需要的字节数如下:

  1. Leftover Digits Number of Bytes

  2. |0 |0 |
  3. |1-2 |1 |
  4. |3-4 |2 |
  5. |5-6 |3 |
  6. |7-9 |4 |

 

例如:

  • decimal(20,6)=> 小数点左边14位,小数点右边6位 => 小数点左边分组为5 + 9,需要3个字节+4个字节存储,小数点一个分组,需要3个字节存储 => 总共需要10个字节
  • decimal(18,9)=> 小数点左边9位数,小数点右边9位数 => 分别使用4个字节存储 => 共需要 8个字节
  • decimal(18,2)=> 小数点左边16位数,小数点右边2位数 => 分组为7 + 9,需要8个字节存储,小数点右边1个字节存储 => 共需要9个字节

假如,我们有一张订单表 t_order 结构如下:

通过key_len分析联合索引

如下所示,我们定义了一个表t,表t包含a、b、c、d共4列:

  1. mysql> show create table tG
    *************************** 1. row ***************************
           Table: t
    Create Table: CREATE TABLE `t` (
      `a` int(11) NOT NULL,
      `b` int(11) DEFAULT NULL,
      `c` int(11) DEFAULT NULL,
      `d` int(11) DEFAULT NULL,
      PRIMARY KEY (`a`),
      KEY `ix_x` (`b`,`d`,`c`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

 

现在要执行SQL语句如下:
select a from t where b = 5 and d = 10 order by c;

假设我们有一个索引ix_x(b,d,c),通过explain得到如下输出:

  1. mysql> explain select a from t where b = 5 and d = 10 order by c;
    +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref         | rows | Extra                    |
    +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
    |  1 | SIMPLE      | t     | ref  | ix_x          | ix_x | 10      | const,const |    1 | Using where; Using index |
    +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
    1 row in set (0.00 sec)

 

 

可以看到,查询语句使用了联合索引中的b和d两列来过滤数据。

如果我们定义的联合索引不是`ix_x(b,d,c)`,而是`ix_x(b, c, d)`,通过explain得到的输入如下:

  1. mysql> alter table t drop index ix_x;
    mysql> alter table t add index ix_x(b, c, d);
    mysql> explain select a from t where b = 5 and d = 10 order by c;
    +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
    +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
    |  1 | SIMPLE      | t     | ref  | ix_x          | ix_x | 5       | const |    2 | Using where; Using index |
    +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
    1 row in set (0.00 sec)

 

key_len为5,也就是说,只用到了联合索引中的第一列,可以看到,虽然联合索引包含了我们要查询的所有列,但是,由于定义的顺序问题,SQL语句并不能够充分利用索引。    

本文首先介绍了MySQL的查询计划中ken_len的含义;然后介绍了key_len的计算方法;最后通过一个伪造...

字段名 类型 描述
id int(11) <auto_increment> id,自增,主键
orderNo varchar(20) 订单编号,唯一约束
price decimal(10,2) 价格

 

我们不要去纠结这张表的完整性,也不要去纠结为什么价格不直接用int表示单位为分的额。现在假如,我们写一个生成订单存储过程:

CREATE  PROCEDURE `p_order_create`(IN `_OrderNo` tinytext,IN `_Price` decimal,OUT `_res` int)


BEGIN
   /*
          用途:生成订单

          参数:
          IN `_OrderNo` tinytext,
          IN `_Price` decimal
          _res : 执行结果,0表示失败,成功时返回订单的id

   */


  INSERT INTO t_order
  (`orderNo`,`price`)
  VALUES 
  (_OrderNo,_Price);

  select @@Identity INTO _res;

END;

当我们在调用时,给价格传入的是一个小数时,如:

CALL p_order_create('abc',5.8,@res)

 

我们会发现,存入表 t_order中的这一条数据,price是6,四舍五入了。。。。,说实在的,之前我一直都是用int来表示钱的最小单位(当前业务的最小单位),比如RMB一元,我就存100的整型,所以一直没有留意到MySQL这个坑。当然了,这个坑的解决方案也很简单,我们只要给decimal在申明时指定它的精度,比如decimal(10,2),即存储过程可以改为:

CREATE  PROCEDURE `p_order_create`(IN `_OrderNo` tinytext,IN `_Price` decimal(10,2),OUT `_res` int)


BEGIN
   /*
          用途:生成订单

          参数:
          IN `_OrderNo` tinytext,
          IN `_Price` decimal
          _res : 执行结果,0表示失败,成功时返回订单的id

   */


  INSERT INTO t_order
  (`orderNo`,`price`)
  VALUES 
  (_OrderNo,_Price);

  select @@Identity INTO _res;

END;

 

 

这个坑,记录在这里,也是对自己的一个提醒吧!

 

编辑:数据库 本文来源:存储过程很强大,本文首先介绍了MySQL的查询计划

关键词:

  • 上一篇:没有了
  • 下一篇:没有了