北大青鸟设计培训：mysql数据库的优化方法？

我们都知道，服务器数据库的开发一般都是通过java或者是PHP语言来编程实现的，而为了提高我们数据库的运行速度和效率，数据库优化也成为了我们每日的工作重点，今天，昌平IT培训http://www.kmbdqn.cn/就一起来了解一下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_count<>0 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'

---