Mysql完全手册(笔记二,使用数据与性能优化)

时间:2021-07-24 08:53:08

一、使用数据

1、使用变量

MySQL也可以让我们以用户自定义的变量来存储select查询的结果,以便在将来select查询中使用。它们只会在客户会话期间存在,但是它们提供一个方便有效的方法来连接查询,然后再另一个查询的子句中使用一个查询的输出。

变量赋值由:=操作符完成,它用来向一个变量分配一个值。下面的例子是把表中的最早日期赋给@oldest变量:

select @oldest:=MIN(accountsfds) from account;

存储在@oldest变量中的值可以通过简单的select进行检索:

select @oldest;

变量名是大小写敏感的

2、控制select行为

可以向select语句添加很多关键字来修改行为:

(1)distinct关键字删除包含结果集中具有重复值的记录

(2)sql_calc_found_rows关键字告诉mysql计算符合查询的总行数。通过调用found_rows()函数可以得到总行数

(3)sql_cache和sql_no_cache关键字告诉mysql查询结果是否需要高速缓存

(4)sql_buffer_result关键字强制MySQL把查询结果存储到一个临时表。这使缓冲器消除了对查询所使用的表的锁定,而且结果被传送给客户,因而可以暂时被其他过程使用

(5)sql_big_result和sql_small_result关键字可以指定结果集的期望大小,因此可帮助找到最佳的方法对返回的记录进行排序和存储(基于磁盘或者内存中的临时表)。

(6)sql_high_priority关键字提升了与update,insert和delete语句相竞争的查询的优先级,因而可以在繁忙的数据库服务器上快速地执行查询。

3、导入数据

insert语句不是向表中插入数据的唯一方法,mysql还允许使用load data infile 命令一次性插入多条记录。

表列为:name和dob

该文件位于服务器的/home/me/目录中,它的内容使用逗号隔开的:

alan p,1928-03-14

john d,1938-08-23

emai y,2000-08-12

现在,这些逗号隔开的数据可以用下面的命令导入birthdays表:

load data infile '/home/me/data.txt' into table birthdays fields terminated by ',' lines terminated by '\r\n' (name,dob)

4、导出记录

可以使用select …into outfile 结构从一个表导出记录到一个文件

select name,dob from accouts where accountBal>5000 into outfile 'accounts.txt' fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

5、插入或替换数据

replace into
replace into table (id,name) values('1','aa'),('2','bb')
此语句的作用是向表table中插入两条记录。如果主键id为1或2不存在
就相当于
insert into table (id,name) values('1','aa'),('2','bb')
如果存在相同的值则不会插入数据

二、性能优化

1、添加索引

2、设置缓存,添加关键字sql_cache

3、减少使用子查询

4、建临时表,临时表是存在于内存中,操作快

5、优化表设计,使用定长字段,能合并则合并

6、调整服务器设置

(1)key_buffer_size变量控制了索引缓冲可以使用的内存的数量。这个值越高,索引可以使用的内存越多,性能越好。一般情况下,可以保持这个值在服务器可以使用内存总量的25%到30%。

(2)table_cache变量控制表告诉缓冲可以使用内存的数量,以及在同一时间内MySQL可以处理表的打开总量。对于有很多数据库和表的非常忙的服务器应该增加这个值,以便MySQL能可靠的支持所有的请求。与此有关的变量还有max_connections变量,MySQL手册建议使用公式table_cache=max_connections * N设置table_cache,其中N为标准连接中表的数量。

(3)可以提高使用order by或group by语句查询的速度,通过增加mysql分类缓冲值得方法分类结果设置,通过sort_buffer变量进行控制。还可以考虑增加read_rnd_buffer_size变量的值,以提高读分类行的速度

(4)增加read_buffer_size变量值可以提高select查询的速度,该变量值增加扩大了mysql读缓冲的规模,从而使select能顺序地扫描表

(5)在执行一项作业时,mysql保存告诉缓存中组成作业的语句,直到收到指示写信息到二进制日志为止,然后将这些信息提交到数据库。对于比较长或者复杂的作业,应该通过binlog_cache_size变量增加高速缓存的规模,以便得到更好的性能。

(6)如果准备采用所谓的“批量插入”,则可以通过增加bulk_insert_buffer_size变量的值来提高速度。注:这只能在MyISAM表类型中使用

(7)如果预计在服务器上会增加许多新的连接,增加thread_cache_size变量值的做法很好。当正在工作的客服机断线时,这个变量控制着服务器线程经过的高速缓存的大小。然后这个高速缓存的线程将重新用来服务一个新连接。这个值越高,可以被高速缓存的线程越多,对新连接申请的响应时间越合适

7、基准技术

其主要功能是为数据库服务器查找弱点,确认对sql标准的符合情况、测量性能等。

为了能运行mysql基准技术套件,必须在系统中安装Perl,包括PerlDBI套件和MySQL数据库驱动程序。