13518219792
1.当我们请求mysql服务器的时候,MySQL前端会有一个监听,请求到了之后,服务器得到相关的SQL语句,执行之前(虚线部分为执行),还会做权限的判断
怀来网站制作公司哪家好，找成都创新互联公司！从网页设计、网站建设、微信开发、APP开发、响应式网站设计等网站项目制作，到程序开发，运营维护。成都创新互联公司从2013年创立到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选成都创新互联公司。
2.通过权限之后,SQL就到MySQL内部,他会在查询缓存中,看该SQL有没有执行过,如果有查询过,则把缓存结果返回,说明在MySQL内部,也有一个查询缓存.但是这个查询缓存,默认是不开启的,这个查询缓存,和我们的Hibernate，Mybatis的查询缓存是一样的,因为查询缓存要求SQL和参数都要一样,所以这个命中率是非常低的（没什么卵用的意思）。
3.如果我们没有开启查询缓存,或者缓存中没有找到对应的结果,那么就到了解析器,解析器主要对SQL语法进行解析
4.解析结束后就变成一颗解析树,这个解析树其实在Hibernate里面也是有的,大家回忆一下,在以前做过Hibernate项目的时候,是不是有个一个antlr.jar。这个就是专门做语法解析的工具.因为在Hibernate里面有HQL，它就是通过这个工具转换成SQL的,我们编程语言之所以有很多规范、语法,其实就是为了便于这个解析器解析,这个学过编译原理的应该知道.
5.得到解析树之后,不能马上执行,这还需要对这棵树进行预处理,也就是说,这棵树,我没有经过任何优化的树,预处理器会这这棵树进行一些预处理,比如常量放在什么地方,如果有计算的东西,把计算的结果算出来等等...
6.预处理完毕之后,此时得到一棵比较规范的树,这棵树就是要拿去马上做执行的树,比起之前的那棵树,这棵得到了一些优化
7.查询优化器，是MySQL里面最关键的东西,我们写任何一条SQL,比如SELECT * FROM USER WHERE USERNAME = toby AND PASSWORD = 1,它会怎么去执行?它是先执行username = toby还是password = 1?每一条SQL的执行顺序查询优化器就是根据MySQL对数据统计表的一些信息,比如索引,比如表一共有多少数据,MySQL都是有缓存起来的,在真正执行SQL之前,他会根据自己的这些数据,进行一个综合的判定,判断这一次在多种执行方式里面,到底选哪一种执行方式,可能运行的最快.这一步是MySQL性能中,最关键的核心点,也是我们的优化原则.我们平时所讲的优化SQL,其实说白了,就是想让查询优化器,按照我们的想法,帮我们选择最优的执行方案,因为我们比MySQL更懂我们的数据.MySQL看数据,仅仅只是自己收集到的信息,这些信息可能是不准确的,MySQL根据这些信息选了一个它自认为最优的方案,但是这个方案可能和我们想象的不一样.
8.这里的查询执行计划,也就是MySQL查询中的执行计划,比如要先执行username = toby还是password = 1
9.这个执行计划会传给查询执行引擎,执行引擎选择存储引擎来执行这一份传过来的计划,到磁盘中的文件中去查询,这个时候重点来了,影响这个查询性能最根本的原因是什么?就是硬盘的机械运动,也就是我们平时熟悉的IO,所以一条查询语句是快还是慢,就是根据这个时间的IO来确定的.那怎么执行IO又是什么来确定的?就是传过来的这一份执行计划.(优化就是制定一个我们认为最快的执行方案,最节省IO,和执行最快)
10.如果开了查询缓存,则返回结果给客户端,并且查询缓存也放一份。
在开始演示之前，我们先介绍下两个概念。
概念一，数据的可选择性基数，也就是常说的cardinality值。
查询优化器在生成各种执行计划之前，得先从统计信息中取得相关数据，这样才能估算每步操作所涉及到的记录数，而这个相关数据就是cardinality。简单来说，就是每个值在每个字段中的唯一值分布状态。
比如表t1有100行记录，其中一列为f1。f1中唯一值的个数可以是100个，也可以是1个，当然也可以是1到100之间的任何一个数字。这里唯一值越的多少，就是这个列的可选择基数。
那看到这里我们就明白了，为什么要在基数高的字段上建立索引，而基数低的的字段建立索引反而没有全表扫描来的快。当然这个只是一方面，至于更深入的探讨就不在我这篇探讨的范围了。
概念二，关于HINT的使用。
这里我来说下HINT是什么，在什么时候用。
HINT简单来说就是在某些特定的场景下人工协助MySQL优化器的工作，使她生成最优的执行计划。一般来说，优化器的执行计划都是最优化的，不过在某些特定场景下，执行计划可能不是最优化。
比如：表t1经过大量的频繁更新操作，（UPDATE,DELETE,INSERT），cardinality已经很不准确了，这时候刚好执行了一条SQL，那么有可能这条SQL的执行计划就不是最优的。为什么说有可能呢？
来看下具体演示
譬如，以下两条SQL，
A：
select * from t1 where f1 = 20;
B：
select * from t1 where f1 = 30;
如果f1的值刚好频繁更新的值为30，并且没有达到MySQL自动更新cardinality值的临界值或者说用户设置了手动更新又或者用户减少了sample page等等，那么对这两条语句来说，可能不准确的就是B了。
这里顺带说下，MySQL提供了自动更新和手动更新表cardinality值的方法，因篇幅有限，需要的可以查阅手册。
那回到正题上，MySQL 8.0 带来了几个HINT，我今天就举个index_merge的例子。
示例表结构：
mysql desc t1;+------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+------------+--------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || rank1 | int(11) | YES | MUL | NULL | || rank2 | int(11) | YES | MUL | NULL | || log_time | datetime | YES | MUL | NULL | || prefix_uid | varchar(100) | YES | | NULL | || desc1 | text | YES | | NULL | || rank3 | int(11) | YES | MUL | NULL | |+------------+--------------+------+-----+---------+----------------+7 rows in set (0.00 sec)
表记录数：
mysql select count(*) from t1;+----------+| count(*) |+----------+| 32768 |+----------+1 row in set (0.01 sec)
这里我们两条经典的SQL：
SQL C：
select * from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2;
SQL D：
select * from t1 where rank1 =100 and rank2 =100 and rank3 =100;
表t1实际上在rank1,rank2,rank3三列上分别有一个二级索引。
那我们来看SQL C的查询计划。
显然，没有用到任何索引，扫描的行数为32034，cost为3243.65。
mysql explain format=json select * from t1 where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "3243.65" }, "table": { "table_name": "t1", "access_type": "ALL", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "rows_examined_per_scan": 32034, "rows_produced_per_join": 115, "filtered": "0.36", "cost_info": { "read_cost": "3232.07", "eval_cost": "11.58", "prefix_cost": "3243.65", "data_read_per_join": "49K" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" } }}1 row in set, 1 warning (0.00 sec)
我们加上hint给相同的查询，再次看看查询计划。
这个时候用到了index_merge,union了三个列。扫描的行数为1103，cost为441.09，明显比之前的快了好几倍。
mysql explain format=json select /*+ index_merge(t1) */ * from t1 where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "441.09" }, "table": { "table_name": "t1", "access_type": "index_merge", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "key": "union(idx_rank1,idx_rank2,idx_rank3)", "key_length": "5,5,5", "rows_examined_per_scan": 1103, "rows_produced_per_join": 1103, "filtered": "100.00", "cost_info": { "read_cost": "330.79", "eval_cost": "110.30", "prefix_cost": "441.09", "data_read_per_join": "473K" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" } }}1 row in set, 1 warning (0.00 sec)
我们再看下SQL D的计划：
不加HINT，
mysql explain format=json select * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "534.34" }, "table": { "table_name": "t1", "access_type": "ref", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "key": "idx_rank1", "used_key_parts": [ "rank1" ], "key_length": "5", "ref": [ "const" ], "rows_examined_per_scan": 555, "rows_produced_per_join": 0, "filtered": "0.07", "cost_info": { "read_cost": "478.84", "eval_cost": "0.04", "prefix_cost": "534.34", "data_read_per_join": "176" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100))" } }}1 row in set, 1 warning (0.00 sec)
加了HINT，
mysql explain format=json select /*+ index_merge(t1)*/ * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "5.23" }, "table": { "table_name": "t1", "access_type": "index_merge", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "key": "intersect(idx_rank1,idx_rank2,idx_rank3)", "key_length": "5,5,5", "rows_examined_per_scan": 1, "rows_produced_per_join": 1, "filtered": "100.00", "cost_info": { "read_cost": "5.13", "eval_cost": "0.10", "prefix_cost": "5.23", "data_read_per_join": "440" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100) and (`ytt`.`t1`.`rank1` = 100))" } }}1 row in set, 1 warning (0.00 sec)
对比下以上两个，加了HINT的比不加HINT的cost小了100倍。
总结下，就是说表的cardinality值影响这张的查询计划，如果这个值没有正常更新的话，就需要手工加HINT了。相信MySQL未来的版本会带来更多的HINT。
mysql里面存储过程能不用游标就不要用游标。 mysql里面的游标性能是最慢的，oracle或者sqlserver或者db2，游标遍历数一次超过10000，性能都会很差的。
而mysql的游标性能比oracle他们来说性能更差了。
存储过程里面如果很多insert，可以设置一下set autocommit=0; 然后自己控制何时commit。
如果数据量大，注意索引。比如select 里面有5W条数据，又没有orderdetailid 索引，就会很慢，加了索引之后，整个存储过程就很快了。
数据千万级别之多，占用的存储空间也比较大，可想而知它不会存储在一块连续的物理空间上，而是链式存储在多个碎片的物理空间上。可能对于长字符串的比较，就用更多的时间查找与比较，这就导致用更多的时间。
可以做表拆分，减少单表字段数量，优化表结构。
在保证主键有效的情况下，检查主键索引的字段顺序，使得查询语句中条件的字段顺序和主键索引的字段顺序保持一致。
主要两种拆分 垂直拆分，水平拆分。
垂直分表
也就是“大表拆小表”，基于列字段进行的。一般是表中的字段较多，将不常用的， 数据较大，长度较长（比如text类型字段）的拆分到“扩展表“。 一般是针对 那种 几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。
垂直分库针对的是一个系统中的不同业务进行拆分，比如用户User一个库，商品Product一个库，订单Order一个库。 切分后，要放在多个服务器上，而不是一个服务器上。为什么？ 我们想象一下，一个购物网站对外提供服务，会有用户，商品，订单等的CRUD。没拆分之前， 全部都是落到单一的库上的，这会让数据库的单库处理能力成为瓶颈。按垂直分库后，如果还是放在一个数据库服务器上， 随着用户量增大，这会让单个数据库的处理能力成为瓶颈，还有单个服务器的磁盘空间，内存，tps等非常吃紧。 所以我们要拆分到多个服务器上，这样上面的问题都解决了，以后也不会面对单机资源问题。
数据库业务层面的拆分，和服务的“治理”，“降级”机制类似，也能对不同业务的数据分别的进行管理，维护，监控，扩展等。 数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于Web和应用服务器来讲，是比较难实现“横向扩展”的。 数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。
水平分表
针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。不建议采用。
水平分库分表
将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。
水平分库分表切分规则
1. RANGE
从0到10000一个表，10001到20000一个表；
2. HASH取模
一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。 取用户id，然后hash取模，分配到不同的数据库上。
3. 地理区域
比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此。
4. 时间
按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。
分库分表后面临的问题
事务支持
分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。
跨库join
只要是进行切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。
跨节点的count,order by,group by以及聚合函数问题
这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。
数据迁移，容量规划，扩容等问题
来自淘宝综合业务平台团队，它利用对2的倍数取余具有向前兼容的特性（如对4取余得1的数对2取余也是1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了Sharding扩容的难度。
ID问题
一旦数据库被切分到多个物理结点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的ID无法保证在全局上是唯一的；另一方面，应用程序在插入数据之前需要先获得ID,以便进行SQL路由.
一些常见的主键生成策略
UUID
使用UUID作主键是最简单的方案，但是缺点也是非常明显的。由于UUID非常的长，除占用大量存储空间外，最主要的问题是在索引上，在建立索引和基于索引进行查询时都存在性能问题。
Twitter的分布式自增ID算法Snowflake
在分布式系统中，需要生成全局UID的场合还是比较多的，twitter的snowflake解决了这种需求，实现也还是很简单的，除去配置信息，核心代码就是毫秒级时间41位 机器ID 10位 毫秒内序列12位。
跨分片的排序分页
一般来讲，分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。
我们都知道，服务器数据库的开发一般都是通过java或者是PHP语言来编程实现的，而为了提高我们数据库的运行速度和效率，数据库优化也成为了我们每日的工作重点，今天，昌平IT培训就一起来了解一下mysql服务器数据库的优化方法。
为什么要了解索引真实案例案例一：大学有段时间学习爬虫，爬取了知乎300w用户答题数据，存储到mysql数据中。
那时不了解索引，一条简单的“根据用户名搜索全部回答的sql“需要执行半分钟左右，完全满足不了正常的使用。
案例二：近线上应用的数据库频频出现多条慢sql风险提示，而工作以来，对数据库优化方面所知甚少。
例如一个用户数据页面需要执行很多次数据库查询，性能很慢，通过增加超时时间勉强可以访问，但是性能上需要优化。
索引的优点合适的索引，可以大大减小mysql服务器扫描的数据量，避免内存排序和临时表，提高应用程序的查询性能。
索引的类型mysql数据中有多种索引类型，primarykey，unique，normal，但底层存储的数据结构都是BTREE;有些存储引擎还提供hash索引，全文索引。
BTREE是常见的优化要面对的索引结构，都是基于BTREE的讨论。
B-TREE查询数据简单暴力的方式是遍历所有记录;如果数据不重复，就可以通过组织成一颗排序二叉树，通过二分查找算法来查询，大大提高查询性能。
而BTREE是一种更强大的排序树，支持多个分支，高度更低，数据的插入、删除、更新更快。
现代数据库的索引文件和文件系统的文件块都被组织成BTREE。
btree的每个节点都包含有key，data和只想子节点指针。
btree有度的概念d=1。
假设btree的度为d，则每个内部节点可以有n=[d+1，2d+1)个key，n+1个子节点指针。
树的大高度为h=Logb[(N+1)/2]。
索引和文件系统中，B-TREE的节点常设计成接近一个内存页大小(也是磁盘扇区大小)，且树的度非常大。
这样磁盘I/O的次数，就等于树的高度h。
假设b=100，一百万个节点的树，h将只有3层。
即，只有3次磁盘I/O就可以查找完毕，性能非常高。
索引查询建立索引后，合适的查询语句才能大发挥索引的优势。
另外，由于查询优化器可以解析客户端的sql语句，会调整sql的查询语句的条件顺序去匹配合适的索引。
在数据库的开发过程中，经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用存储过程来封装数据库操作。如果项目的存储过程较多，书写又没有一定的规范，将会影响以后的系统维护困难和大存储过程逻辑的难以理解，另外如果数据库的数据量大或者项目对存储过程的性能要求很，就会遇到优化的问题，否则速度有可能很慢，经过亲身经验，一个经过优化过的存储过程要比一个性能差的存储过程的效率甚至高几百倍。下面介绍某一个MySQL存储过程优化的整个过程。
在本文中，需要被优化的存储过程如下：
drop procedure if exists pr_dealtestnum;
delimiter //
create procedure pr_dealtestnum
(
in p_boxnumber varchar(30)
)
pr_dealtestnum_label:begin
insert into tb_testnum select boxnumber,usertype from tb_testnum_tmp where boxnumber= p_boxnumber;
leave pr_dealtestnum_label;
end;
//
delimiter ;
select 'create procedure pr_dealtestnumok';
在存储过程中使用到的表tb_testnum结构如下：
drop table if exists tb_testnum;
create table tb_testnum
(
boxnumber varchar(30) not null,
usertype int not null
);
create unique index idx1_tb_testnum ontb_testnum(boxnumber);
在存储过程中使用到的另外一张表tb_testnum_tmp结构如下：
drop table if exists tb_testnum_tmp;
create table tb_testnum_tmp
(
boxnumber varchar(30) not null,
usertype int not null
);
create unique index idx1_tb_testnum_tmp ontb_testnum_tmp(boxnumber);
从两个表的结构可以看出，tb_testnum和tb_testnum_tmp所包含的字段完全相同，存储过程pr_dealtestnum的作用是根据输入参数将tb_testnum_tmp表的数据插入到tb_testnum表中。
很明显，虽然能够实现预期的功能，但存储过程pr_dealtestnum的代码还有改进的地方。
下面，我们一步一步来对其进行优化。
优化一
存储过程pr_dealtestnum的主体是一条insert语句，但这条insert语句里面又包含了select语句，这样的编写是不规范的。因此，我们要把这条insert语句拆分成两条语句，即先把数据从tb_testnum_tmp表中查找出来，再插入到tb_testnum表中。修改之后的存储过程如下：
drop procedure if exists pr_dealtestnum;
delimiter //
create procedure pr_dealtestnum
(
in p_boxnumber varchar(30)
)
pr_dealtestnum_label:begin
declare p_usertype int;
select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber;
insert into tb_testnum values(p_boxnumber,p_usertype);
leave pr_dealtestnum_label;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';
优化二
在向tb_testnum表插入数据之前，要判断该条数据在表中是否已经存在了，如果存在，则不再插入数据。同理，在从tb_testnum_tmp表中查询数据之前，要先判断该条数据在表中是否存在，如果存在，才能从表中查找数据。修改之后的存储过程如下：
drop procedure if exists pr_dealtestnum;
delimiter //
create procedure pr_dealtestnum
(
in p_boxnumber varchar(30)
)
pr_dealtestnum_label:begin
declare p_usertype int;
declare p_datacount int;
select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber;
if p_datacount 0 then
begin
select usertype into p_usertype fromtb_testnum_tmp where boxnumber=p_boxnumber;
end;
else
begin
leave pr_dealtestnum_label;
end;
end if;
select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber;
if p_datacount = 0 then
begin
insert into tb_testnum values(p_boxnumber,p_usertype);
leave pr_dealtestnum_label;
end;
else
begin
leave pr_dealtestnum_label;
end;
end if;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';
优化三
不管向tb_testnum表插入数据的操作执行成功与否，都应该有一个标识值来表示执行的结果，这样也方便开发人员对程序流程的追踪和调试。也就是说，在每条leave语句之前，都应该有一个返回值，我们为此定义一个输出参数。修改之后的存储过程如下：
drop procedure if exists pr_dealtestnum;
delimiter //
create procedure pr_dealtestnum
(
in p_boxnumber varchar(30),
out p_result int -- 0-succ, other-fail
)
pr_dealtestnum_label:begin
declare p_usertype int;
declare p_datacount int;
select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber;
if p_datacount 0 then
begin
select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber;
end;
else
begin
set p_result = 1;
leave pr_dealtestnum_label;
end;
end if;
select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber;
if p_datacount = 0 then
begin
insert into tb_testnum values(p_boxnumber,p_usertype);
set p_result = 0;
leave pr_dealtestnum_label;
end;
else
begin
set p_result = 2;
leave pr_dealtestnum_label;
end;
end if;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';
优化四
我们注意到“insert into tb_testnum values(p_boxnumber,p_usertype);”语句中，tb_testnum表之后没有列出具体的字段名，这个也是不规范的。如果在以后的软件版本中，tb_testnum表中新增了字段，那么这条insert语句极有可能会报错。因此，规范的写法是无论tb_testnum表中有多少字段，在执行insert操作时，都要列出具体的字段名。修改之后的存储过程如下：
drop procedure if exists pr_dealtestnum;
delimiter //
create procedure pr_dealtestnum
(
in p_boxnumber varchar(30),
out p_result int -- 0-succ, other-fail
)
pr_dealtestnum_label:begin
declare p_usertype int;
declare p_datacount int;
select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber;
if p_datacount 0 then
begin
select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber;
end;
else
begin
set p_result = 1;
leave pr_dealtestnum_label;
end;
end if;
select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber;
if p_datacount = 0 then
begin
insert into tb_testnum(boxnumber,usertype) values(p_boxnumber,p_usertype);
set p_result = 0;
leave pr_dealtestnum_label;
end;
else
begin
set p_result = 2;
leave pr_dealtestnum_label;
end;
end if;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';
优化五
在执行insert语句之后，要用MySQL中自带的@error_count参数来判断插入数据是否成功，方便开发人员跟踪执行结果。如果该参数的值不为0，表示插入失败，那么我们就用一个返回参数值来表示操作失败。修改之后的存储过程如下：
drop procedure if exists pr_dealtestnum;
delimiter //
create procedure pr_dealtestnum
(
in p_boxnumber varchar(30),
out p_result int -- 0-succ, other-fail
)
pr_dealtestnum_label:begin
declare p_usertype int;
declare p_datacount int;
select count(*) into p_datacount from tb_testnum_tmp where boxnumber=p_boxnumber;
if p_datacount 0 then
begin
select usertype into p_usertype from tb_testnum_tmp where boxnumber=p_boxnumber;
end;
else
begin
set p_result = 1;
leave pr_dealtestnum_label;
end;
end if;
select count(*) into p_datacount from tb_testnum where boxnumber=p_boxnumber;
if p_datacount = 0then
begin
insert into tb_testnum(boxnumber,usertype) values(p_boxnumber,p_usertype);
if @error_count0 then
begin
set p_result= 3;
end;
else
begin
set p_result= 0;
end;
end if;
end;
else
begin
set p_result = 2;
end;
end if;
leave pr_dealtestnum_label;
end;
//
delimiter ;
select 'create procedure pr_dealtestnum ok';
