据库优化

对于一个以数据为中心的应用，数据库的好坏直接影响到程序的性能，因此数据库性能至关重要。一般来说，要保证数据库的效率，要做好以下四个方面的工作：

① 数据库设计

② sql语句优化

③ 数据库参数配置

④ 恰当的硬件资源和操作系统

这个顺序也表现了这四个工作对性能影响的大小

SQL优化的一般步骤

通过show status命令了解各种SQL的执行频率。

定位执行效率较低的SQL语句-（重点select）通过explain分析低效率的SQL语句的执行情况，确定问题并采取相应的优化措施

u sql语句的优化

sql语句有几类

ddl (数据定义语言) [create alter drop]

dml(数据操作语言)[insert delete upate ] select

dtl(数据事务语句) [commit rollback savepoint]

dcl(数据控制语句) [grant revoke]

show status命令

该命令可以显示你的mysql数据库的当前状态.我们主要关心的是 “com”开头的指令

show status like ‘Com%’ <=> show session status like ‘Com%’ //显示当前控制台的情况

show global status like ‘Com%’ ; //显示数据库从启动到查询的次数

MySQL客户端连接成功后，通过使用show [session|global] status 命令可以提供服务器状态信息。其中的session来表示当前的连接的统计结果，global来表示自数据库上次启动至今的统计结果。默认是session级别的。
下面的例子：
show status like ‘Com_%’;

其中Com_XXX表示XXX语句所执行的次数。
重点注意：Com_select,Com_insert,Com_update,Com_delete通过这几个参数，可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各类的SQL大致的执行比例是多少。

还有几个常用的参数便于用户了解数据库的基本情况。
Connections：试图连接MySQL服务器的次数
Uptime：服务器工作的时间（单位秒）
Slow_queries：慢查询的次数 (默认是10)

显示连接数据库次数

show status like 'Connections';

创建表

CREATE TABLE dept( /*部门表*/

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

dname VARCHAR(20) NOT NULL DEFAULT "",

loc VARCHAR(13) NOT NULL DEFAULT ""

) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

这里我创建了一个预处理函数

create function rand_string(n INT)

returns varchar(255)

begin

declare chars_str varchar(100) default

'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';

declare return_str varchar(255) default '';

declare i int default 0;

while i < n do

set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));

set i = i + 1;

end while;

return return_str;

end $$

delimiter ;

select rand_string(6);

# 随机产生部门编号

delimiter $$

drop function rand_num $$

#这里我们又自定了一个函数

create function rand_num( )

returns int(5)

begin

declare i int default 0;

set i = floor(10+rand()*500);

return i;

end $$

delimiter ;

select rand_num();

#向emp表中插入记录(海量的数据)

delimiter $$

drop procedure insert_emp $$

create procedure insert_emp(in start int(10),in max_num int(10))

begin

declare i int default 0;

set autocommit = 0;

repeat

set i = i + 1;

insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());

until i = max_num

end repeat;

commit;

end $$

delimiter ;

#调用刚刚写好的函数, 1800000条记录,从100001号开始

call insert_emp(100001,1800000);

索引

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行个正确的’create index’，查询速度就可能提高百倍千倍，这可真有诱惑力。可是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的I/O。

目的，就是看看怎样处理，在海量表中，查询的速度很快!

select * from emp where empno=123456;

测试 ,比如我们把

select * from emp where empno=34678 ；

用了1.5秒，我现在优化.

快速体验: 在emp表的 empno

alter table emp add primary key(empno); //建立索引.

alter table emp drop primary key //删除主键索引

然后，再查速度变快.

Explain

介绍一款非常重要工具 explain, 这个分析工具可以对 sql语句进行分析,可以预测你的sql执行的效率.

他的基本用法是:

explain sql语句\G

//根据返回的信息，我们可知,该sql语句是否使用索引，从多少记录中取出,可以看到排序的方式.

会产生如下信息：
select_type:表示查询的类型。
table:输出结果集的表
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
rows:扫描的行数
Extra:执行情况的描述和说明

在什么列上添加索引比较合适

① 在经常查询的列上加索引.

② 列的数据，内容就只有少数几个值,不太适合加索引.

③ 内容频繁变化，不合适加索引

create index myind on 表名 (列1,列2);

show indexes from 表名 //查询某表是否有索引。

l 如何检测你的索引是否有效

结论: Handler_read_key 越大越少

Handler_read_rnd_next 越小越好

注意

下列的表将不使用索引：
1，如果条件中有or，即使其中有条件带索引也不会使用。
2，对于多列索引，不是使用的第一部分，则不会使用索引。
3，like查询是以%开头
4，如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。
5，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。