MySQL中SQL编程、事务、数据库优化

最新推荐文章于 2024-07-22 15:12:37 发布

一斤兮瓜

最新推荐文章于 2024-07-22 15:12:37 发布

阅读量635

点赞数 28

文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/m0_65251623/article/details/139377426

版权

SQL编程

SQL语法，结构化查询语言，主要用在关系型数据库操作上，但是要注意，SQL语句，是一种编程语言。

是否存在变量
存在程序控制流程（三大流程）

SQL编程，也是做数据库相关的编程，如定义数据库函数、数据库存储过程、触发器、游标。

触发器

触发器（trigger）是一个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，
比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

触发器经常用于加强数据的完整性约束和业务规则等。例如，当学生表中增加了一个学生的信息时，学生的总数就应该同时改变。因此可以针对学生表创建一个触发器，每次增加一个学生记录时，就执行一次学生总数的计算操作，从而保证学生总数与记录数的一致性。

触发器的定义语法

create trigger 触发器名称 before|after insert|delete|update
on 表名 for each row
 begin
 -- 触发器对应的代码；
 -- 可能有多行代码；
 -- 每行代码需要使用分号；
 end
--课堂练习
mysql> create table student2(
    -> id int unsigned auto_increment primary key not null,    
    -> name varchar(50));
Query OK, 0 rows affected (0.01 sec)
	
mysql> insert into student2(name) values('jack');
Query OK, 1 row affected (0.01 sec)

mysql> create table student2_total(total int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student2_total values(1);
Query OK, 1 row affected (0.00 sec)

--将结束符改为$$
mysql> delimiter $$

mysql> create trigger student2_insert_trigger
    -> after insert
    -> on student2 for each row
    -> begin
    -> update student2_total set total=total+1;
    -> end
    -> $$
Query OK, 0 rows affected (0.01 sec)
--查看触发器
--通过SHOW TRIGGERS语句查看
	SHOW TRIGGERS\G
	
--通过系统表triggers查看
    USE information_schema
    SELECT * FROM triggers\G
    SELECT * FROM triggers WHERE TRIGGER_NAME='触发器名称'\G


--删除触发器
--通过DROP TRIGGERS语句删除
mysql>  DROP TRIGGER 解发器名称
--示例2：
创建表tab1
 
DROP TABLE IF EXISTS tab1;
CREATE TABLE tab1(
     id int primary key auto_increment,
     name varchar(50),
     sex enum('m','f'),
     age int
);

创建表tab2
DROP TABLE IF EXISTS tab2;
CREATE TABLE tab2(
     id int primary key auto_increment,
     name varchar(50),
     salary double(10,2)
);
触发器tab1_after_delete_trigger 
作用：tab1表删除记录后，自动将tab2表中对应记录删除

mysql> \d /
mysql> 
create trigger tab1_after_delete_trigger 
	after delete  on  tab1 
	for each row    
	begin      
    	delete from tab2 where name=old.name; 
    end/
触发器tab1_after_update_trigger 
作用：当tab1更新后，自动更新tab2
       
mysql> \d $$
mysql> 
create trigger tab1_after_update_trigger 
    after update on tab1 
    for each row 
begin  
    -- z注意，new和old两个关键字
    -- new代表，当前这张表（执行出发的的表）
    -- old代表，事件出发的表
	update tab2 set name = new.name where name = old.name; 
end$$
	 
Query OK, 0 rows affected (0.19 sec) 

触发器tab1_after_insert_trigger 
作用：当tab1增加记录后，自动增加到tab2
mysql> \d /
mysql> 
create trigger tab1_after_insert_trigger
after insert on tab1 
for each row
begin 
	declare new_name varchar(50);
	set new_name=new.name;
	insert into tab2 values(null,name,salary) values(new_name,5000); 
end/
 
Query OK, 0 rows affected (0.19 sec)

存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段sql语句集合，这些SQL的集合目的是为了实现某一功能。

调用存储过程函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

注意：虽然存储过程、触发器、存储函数都能够提高数据处理的效率，也就是提高数据的并发，但是在互联网项目中，企业一般很少选择使用SQL编程来做复杂的业务，原因就是万一出现了错误，代码层面，是无法处理这些错误的，只能在数据库一层处理。

而在一些传统的IT，如银行等等，它们喜欢使用SQL编程。

存储过程和函数的区别：

函数必须有返回值，而存储过程没有
存储过程的参数可以是IN、OUT、INOUT类型，函数的参数只能是IN

存储过程的优点

存储过程只在创建时进行编译；而SQL语句每执行一次就编译一次，所以使用存储过程可以提高数据库执行速度
简化复杂操作，结合事务一起封装
复用性好
安全性高，可指定存储过程的使用权

注意：并发量少的情况下，很少使用存储过程。并发量高的情况下，为了提高效率，用存储过程比较多。

存储过程创建与调用

-- 创建存储过程的语法结构
create procedure 存储过程名称([参数列表])
begin
   // 存储过程的语法体
end


-- 存储的参数类型（不是数据类型）
-- IN    	表示传入的参数
-- OUT		表示输出的参数【类似于函数的返回值】
-- INOUT	表示既可以当做传入的参数，同时也在当做输出的参数【类似于函数的返回值】


-- 存储存储定义完成后，会自动编译

# 调用存储过程
call 存储过程名称([实参列表])

-- 案例
mysql> 
create procedure p1() 
begin 
	select count(*) from tab1; 
end$

	 
Query OK, 0 rows affected (0.51 sec)
mysql> \d ;
mysql> call p1()
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

-- 创建一个存储过程，快速插入大量数据到表中
create procedure auto_insert_t1()
begin
	# 插入1000条数据
	declare i int default 1;
	while (i <= 1000) do
		insert into t1 values(i, md5(i));
		set i = i + 1;

	end while;
end/


-- 创建一个存储过程，快速插入大量数据到表中，条数由用户控制
create procedure auto_insert2_t1(IN nums int)
begin
	# 插入1000条数据
	declare i int default 1;
	while (i <= nums) do
		insert into t1 values(i, uuid());
		set i = i + 1;

	end while;
end/


select count(*) into num from t1; 

-- out参数，是用来传出参数的
-- into 语句，可以用来给变量赋值
create procedure p2(out num int) 
begin 
	select count(*) into num from db_classes.t1; 
end/

# in 和 out 可以同时存在的
# 要求求出，年龄大于等于N岁的人的数量
create procedure get_count(IN p1 int, OUT num int)
begin
	select count(*) into num from db_hero where age >= p1;
end/

# inout类型， 此时INOUT 表示两种含义，即充当传入的参数，也充当返回的结果
# 要求求出，年龄大于等于N岁的人的数量
create procedure get_count2(INOUT p1 int)
begin
	select count(*) into p1 from db_hero where age >= p1;
end/


create procedure proce_param_inout(inout p1 int) 
begin 
	if (p1 is not null) then 
		set p1=p1+1; 
	else 
		select 100 into p1; 
	end if; 
end/

SQL变量

全局变量（global variable）：由DBMS已经定义好的，具有特殊函数的变量

全局变量也可以通过set修改，但是只是会话级别有效
会话级别变量（session variable）：登录成功后定义的，一般使用set定义，一般命名规范是@开头
```
set @a = 10;

select @a;
```
局部变量（local variable）：局部变量，主要定义在函数（触发器、存储过程、函数）内部，declare定义
```
declare 变量名称 类型 default 默认值

set 修改变量
```

SQL中循环

while循环

while (条件) do
# 循环体

end while;

if语法的使用

if (条件) then
	# if中的语句
	
end if


if (条件) then
	# if中的语句
else
	# 条件不成立的语句
end if

存储函数

存储函数：MySQL存储函数（自定义函数——UDF），函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。函数和存储过程类似。

存储过程和函数的区别：

函数必须有返回值，而存储过程可以没有。
存储过程的参数可以是IN、OUT、INOUT类型，函数的参数只能是IN
存储函数必须要申明必须返回值类型

存储函数创建和调用

-- 存储函数的创建语法
create function 函数名称([参数列表])
RETURNS type（返回值的类型） 

BEGIN
  # 函数体
END

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法基本相同。用户自定义的存储函数与MySQL内部函数性质相同。区别在于，存储函数是用户自定义的。而内部函数由MySQL自带。其语法结构如下：

select 函数名称([实参列表]);

-- 案例
-- 定一个函数，求所有员工的数量
create function get_all_user()
returns int
deterministic
begin
	declare num int default 0;
	select count(1) into num from college;
	return num;
end/


-- 定一个函数，查询名称叫做刘建宏的共多少个
create function get_name_count(uname varchar(50))
returns int
deterministic
begin
	declare num int default 0;
	select count(1) into num from college where name = uname;
	return num;
end
-- 出现ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)该问题的解决方式：
-- 1.添加deterministic，DETERMINISTIC是一个属性，用于指定函数或存储过程的行为。如果一个函数被声明为DETERMINISTIC，那么它保证对于相同的输入参数，每次调用都会返回相同的结果，而不会有任何副作用（例如修改数据或生成随机结果）。这使得MySQL优化器可以更安全地优化和缓存DETERMINISTIC函数的调用，从而提高性能。
-- 2.将log_bin_trust_function_creators系统变量设置为1

存储修改函数

MySQL中，通过ALTER FUNCTION 语句来修改存储函数，其语法格式如下：

ALTER FUNCTION func_name [characteristic ...]
characteristic:
 	 COMMENT 'string'
	| LANGUAGE SQL
	| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
	| SQL SECURITY { DEFINER | INVOKER }

上面这个语法结构是MySQL官方给出的，修改的内容可以包SQL语句也可以不包含。

删除存储函数

MySQL中使用DROP FUNCTION语句来删除存储函数。

示例：删除存储函数。

DROP FUNCTION IF EXISTS func_user;

事务

事务（transaction），是一种数据库对象，是一个最小的不可分割的工作单元。也就是一个完整的业务流程中，可能会出现很多DML（增删改），如果有些成功，有些失败，数据出错，这是绝对不能出现的。所以就有事务。

转账业务：需要事务来协助完成。

update account  set money = money - 2000 where id='10001'；
update account set money = money + 2000 where id = '10002';

事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。

事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；

要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。

为确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。

事务的特性

事务存在四大特性（ACID）

原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
隔离性（Isolation）：每一个事务都是一个独立的行为，事务与事务之间是隔离开的。
持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

MySQL使用事务

MySQL中默认开启了自动提交，这就意味着，，每一条sql就是一个事务。

如何开启事务

关闭自动提交
begin开启事务

begin  # 开启事务

--大量同一事务的sql执行了

--如果没有问题
commit;    // 提交所有的SQL（一个完整事务）	

--如果存在问题，则回滚到begin之前
rollback;


begin
--说明:在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令。

commit：提交事务
--完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了。

rollback ：回滚事务
--将内存中，已执行过的操作，回滚回去

自动提交策略：

MySQL默认已经开启自动提交，我们可以通过对应的设置来开启或者关闭自动提交。

db01 [(none)]>select @@autocommit;
db01 [(none)]>set autocommit=0;
db01 [(none)]>set global autocommit=0;

--注：
--自动提交是否打开，一般在有事务需求的MySQL中，将其关闭
--不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能

--(1)
set autocommit=0;   
set global autocommit=0;

--(2)
vim /etc/my.cnf
autocommit=0

隐式提交语句：

--用于隐式提交的 SQL 语句：
begin 
a
b
begin
SET AUTOCOMMIT = 1

--导致提交的非事务语句：
DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）

--导致隐式提交的语句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

开始事务流程：

-- 检查autocommit是否为关闭状态
select @@autocommit;
或者：
show variables like 'autocommit';

--开启事务,并结束事务
begin
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
rollback;

begin
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
commit;

InnoDB事务的ACID如何保证

这两个日志就是事务日志

redo.log：重做日志，记录的是数据页的物理修改操作，保证在数据库系统崩溃后能够恢复并完成已经提交的事务。主要用于数据库崩溃恢复和提高写入性能。

undo.log：回滚日志，记录了对数据库所做更改的相反操作，用以实现事务的回滚和多版本并发控制（MVCC），从而提供一致性读视图。主要用于事务的回滚和实现非锁定读，即允许多个事务看到其他事务未提交之前的旧版本数据。

redo log

Redo是什么？

redo,顾名思义“重做日志”，是事务日志的一种。

作用是什么？

在事务ACID过程中，实现的是“D”持久化的作用。对于AC也有相应的作用

redo日志位置

redo的日志文件：iblogfile0 iblogfile1

redo buffer

redo的buffer:数据页的变化信息+数据页当时的LSN号

LSN：日志序列号  磁盘数据页、内存数据页、redo buffer、redolog

redo的刷新策略

commit;
刷新当前事务的redo buffer到磁盘
还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘

MySQL CSR——前滚

MySQL : 在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致

情况一:
我们做了一个事务,begin;update;commit.

在begin ,会立即分配一个TXID=tx_01.
update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中
DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102
LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redobuffer
执行commit时,LGWR日志写线程会将redobuffer信息写入redolog日志文件中,基于WAL原则,在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)
假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失
MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是
LSN=101,dp_01,TXID=tx_01,redolog中LSN=102

MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正长启动

以上的工作过程,我们把它称之为基于REDO的"前滚操作"

undo 回滚日志

undo是什么？

undo,顾名思义“回滚日志”

作用是什么？

在事务ACID过程中，实现的是“A” 原子性的作用
另外CI也依赖于Undo
在rolback时,将数据恢复到修改之前的状态
在CSR实现的是,将redo当中记录的未提交的时候进行回滚.
undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备

对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含 3 个隐藏列

db_row_id：隐藏的行 ID。在没有自定义主键也没有 Unique 键的情况下，会使用该隐藏列作为主键。

db_trx_id：操作这个数据的事务 ID，也就是最后一个对该数据进行插入或更新的事务 ID。

db_roll_ptr：回滚指针，也就是指向这个记录的 Undo Log 信息。Undo Log 中存储了回滚需要的数据

是保证MVCC三个核心字段。

事物的隔离级别

事务四大特性：隔离性。

隔离级别越高，数据越完整，但是并发度越低。

多个线程开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个线程在获取数据时的准确性。

如果不考虑隔离性，可能会引发如下问题：

幻读
不可重复读取
脏读

脏读

指一个事务读取了另外一个事务未提交的数据。

脏数据一定不要出现。

不可重复读

不可重复读，是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。

更新引起数据不一致。

幻读

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

插入数据引起的不一致！！！

事务的隔离级别

为了处理这些问题，SQL标准定义了以下几种事务隔离级别

	脏读	不可重复读	幻读
Read uncommitted（RU）	√	√	√
Read committed（RC）	×	√	√
Repeatable read（RR）	×	×	√
Serializable	×	x	x

随着隔离性的升高，并发度是在降低的，所以需要综合实际项目来决定采用什么级别的隔离。

MySQL数据库，官方默认采用的RR这种隔离级别，注意：RR这种级别，幻读是没有解决掉的，所以MySQL自身提供了间隙锁的锁，解决了幻读问题。

查询MySQL自身的隔离级别

set  transaction_isolation level 设置事务隔离级别

# 低版本中
select @@tx_isolation   查询当前事务隔离级别



# 高版本
select @@transaction_isolation;

MySQL数据库引擎

MySQL的数据库设计插件插入的形式设计的
@Alt

数据库锁

悲观锁：表锁、行锁、间隙锁

乐观锁：MVCC

写锁（X锁）：排它锁

读锁（S锁）：共享锁

数据库优化

硬件方面

软件方面

系统优化：硬件、架构

服务优化
应用优化

影响性能的因素

应用程序
查询
事务管理
数据库设计
数据分布
网络
操作系统
硬件

谁参与优化

数据库管理员
业务部门代表
应用程序架构师
应用程序设计人员
应用程序开发人员
硬件及系统管理员
存储管理员

1、系统优化

1、硬件优化
    cpu 64位 一台机器8-16颗CPU
    内存 96-128G 3-4个实例
    硬盘：数量越多越好 性能：ssd（高并发业务） > sas （普通业务）>sata（线下业务）
    raid 4块盘，性能 raid0 > raid10 > raid5 > raid1
    网卡：多块网卡bond 
2、软件优化
    操作系统：使用64位系统
    软件：MySQL 编译优化

2、服务优化

MySQL配置原则
    配置合理的MySQL服务器，尽量在应用本身达到一个MySQL最合理的使用
    针对 MyISAM 或 InnoDB 不同引擎进行不同定制性配置
    针对不同的应用情况进行合理配置
    针对 my.cnf 进行配置，后面设置是针对内存为2G的服务器进行的合理设置

公共选项：

选项	缺省值	推荐值	说明
max_connections	100	1024	MySQL服务器同时处理的数据库连接的最大数量
query_cache_size	0 (不开启）	16M	查询缓存区的最大长度，按照当前需求，一倍一倍增加，本选项比较重要
sort_buffer_size	512K	16M	每个线程的排序缓存大小，一般按照内存可以设置为2M以上，推荐是16M，该选项对排序order by，group by起作用
record_buffer	128K	16M	每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区，可以设置为2M以上
table_cache	64	512	为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符。

MyISAM 选项：

选项	缺省值	推荐值	说明
key_buffer_size	8M	256M	用来存放索引区块的缓存值, 建议128M以上，不要大于内存的30%
read_buffer_size	128K	16M	用来做MyISAM表全表扫描的缓冲大小. 为从数据表顺序读取数据的读操作保留的缓存区的长度
myisam_sort_buffer_size	16M	128M	设置,恢复,修改表的时候使用的缓冲大小，值不要设的太大

InnoDB 选项：

选项	缺省值	推荐值	说明
innodb_buffer_pool_size	32M	1G	InnoDB使用一个缓冲池来保存索引和原始数据, 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少，一般是内存的一半，不超过2G，否则系统会崩溃，这个参数非常重要
innodb_additional_mem_pool_size	2M	128M	InnoDB用来保存 metadata 信息,如果内存是4G，最好本值超过200M
innodb_flush_log_at_trx_commit	1	0	0 代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘; 1 为执行完没执行一条SQL马上commit; 2代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上. 对速度影响比较大，同时也关系数据完整性
innodb_log_file_size	8M	256M	在日志组中每个日志文件的大小, 一般是innodb_buffer_pool_size的25%，官方推荐是innodb_buffer_pool_size 的 40-50%, 设置大一点来避免在日志文件覆写上不必要的缓冲池刷新行为
innodb_log_buffer_size	128K	8M	用来缓冲日志数据的缓冲区的大小.推荐是8M，官方推荐该值小于16M，最好是 1M-8M 之间

参数优化：

Max_connections

（1）简介
Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的
情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当
的调整该值，不能随便去提高设值。
（2）判断依据
show variables like 'max_connections';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | max_connections | 151   |
    +-----------------+-------+
show status like 'Max_used_connections';
    +----------------------+-------+
    | Variable_name        | Value |
    +----------------------+-------+
    | Max_used_connections | 101   |
     +----------------------+-------+
（3）修改方式举例
vim /etc/my.cnf 
Max_connections=1024
补充:
    1.开启数据库时,我们可以临时设置一个比较大的测试值
    2.观察show status like 'Max_used_connections';变化
    3.如果max_used_connections跟max_connections相同,
    那么就是max_connections设置过低或者超过服务器的负载上限了，
    低于10%则设置过大.

back_log

（1）简介
mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如
果mysql的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，
该推栈的数量及back_log,如果等待连接的数量超过back_log，将不被授予连接资源。
back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期
望在一个短时间内有很多连接的时候需要增加它
（2）判断依据
show full processlist
发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值
（3）修改方式举例
vim /etc/my.cnf 
back_log=1024

wait_timeout和interactive_timeout

（1）简介
wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数
interactive_timeout：指的是mysql在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进
行mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时
间就会自动的断开，默认的是28800，可调优为7200。
wait_timeout:如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用
（2）设置建议
如果设置太大，容易造成连接打开时间过长，在show processlist时候，能看到很多的连接 ，一般希望
wait_timeout尽可能低
（3）修改方式举例
wait_timeout=60
interactive_timeout=1200
长连接的应用，为了不去反复的回收和分配资源，降低额外的开销。
一般我们会将wait_timeout设定比较小，interactive_timeout要和应用开发人员沟通长链接的应用是
否很多。如果他需要长链接，那么这个值可以不需要调整。
另外还可以使用类外的参数弥补。

max_allowed_packet

（1）简介：
mysql根据配置文件会限制，server接受的数据包大小。
（2）配置依据：
有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，更大值是1GB，必须
设置1024的倍数
（3）配置方法：
max_allowed_packet=32M

参数优化示例：

[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
[client]
socket=/tmp/mysql.sock

压力测试：

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='db1' --query="select * from db1.t_100w where k2='FGCD'" 
engine=innodb --number-of-queries=200000 -uroot -proot -verbose

3、应用优化

设计合理的数据表结构：适当的数据冗余
对数据表建立合适有效的数据库索引
数据查询：编写简洁高效的SQL语句

表结构设计原则
    选择合适的数据类型：如果能够定长尽量定长
    使用 ENUM 而不是 VARCHAR,ENUM类型是非常快和紧凑的，在实际上，其保存的是 TINYINT，但其
    外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美 。
    不要使用无法加索引的类型作为关键字段，比如 text类型
    为了避免联表查询，有时候可以适当的数据冗余，比如邮箱、姓名这些不容易更改的数据
    选择合适的表引擎，有时候 MyISAM 适合，有时候InnoDB适合
    为保证查询性能，最好每个表都建立有 auto_increment 字段， 建立合适的数据库索引
    最好给每个字段都设定 default 值
    
索引建立原则（一）
    一般针对数据分散的关键字进行建立索引，比如ID、QQ，•  
    像性别、状态值等等建立索引没有意义字段唯一，最少，不可为null
     对大数据量表建立聚集索引，避免更新操作带来的碎片。
     尽量使用短索引，一般对int、char/varchar、date/time 等类型的字段建立索引
     需要的时候建立联合索引，但是要注意查询SQL语句的编写
     谨慎建立 unique 类型的索引（唯一索引）
     大文本字段不建立为索引，如果要对大文本字段进行检索，
     可以考虑全文索引
     频繁更新的列不适合建立索引
     
索引建立原则（二）
    order by 字句中的字段，where 子句中字段，最常用的sql语句中字段，应建立索引。           
        
    唯一性约束，系统将默认为改字段建立索引。
    对于只是做查询用的数据库索引越多越好，但对于在线实时系统建议控制在5个以内。
    索引不仅能提高查询SQL性能，同时也可以提高带where字句的update，Delete SQL性能。
    Decimal 类型字段不要单独建立为索引，但覆盖索引可以包含这些字段。
    只有建立索引以后，表内的行才按照特地的顺序存储，按照需要可以是asc或desc方式。
    如果索引由多个字段组成将最用来查询过滤的字段放在前面可能会有更好的性能。
    
编写高效的 SQL （一）
    能够快速缩小结果集的 WHERE 条件写在前面，如果有恒量条件，也尽量放在前面
    尽量避免使用 GROUP BY、DISTINCT 、OR、IN 等语句的使用，避免使用联表查询和子查询，
    因为将使执行效率大大下降
    能够使用索引的字段尽量进行有效的合理排列，如果使用了联合索引，请注意提取字段的前后顺序
    针对索引字段使用 >, >=, =, <, <=, IF NULL和BETWEEN 将会使用索引，
    如果对某个索引字段进行 LIKE 查询，使用 LIKE  ‘%abc%’不能使用索引，使用 LIKE ‘abc%’
    将能够使用索引    如果在SQL里使用了MySQL部分自带函数，索引将失效，
    同时将无法使用 MySQL 的 Query Cache，
    比如 LEFT(), SUBSTR(), TO_DAYS()，DATE_FORMAT(), 等，
	如果使用了 OR 或 IN，索引也将失效
    使用 Explain 语句来帮助改进我们的SQL语句
    
    
编写高效的 SQL （二）
    不要在where 子句中的“=”左边进行算术或表达式运算，否则系统将可能无法正确使用索引
    尽量不要在where条件中使用函数，否则将不能使用索引
    避免使用 select *, 只取需要的字段
    对于大数据量的查询，尽量避免在SQL语句中使用order by 字句，避免额外的开销
    如果插入的数据量很大，用select into 替代 insert into 能带来更好的性能
    采用连接操作，避免过多的子查询，产生的CPU和IO开销
    只关心需要的表和满足条件的数据
    适当使用临时表或表变量
    对于连续的数值，使用between代替in
    where 字句中尽量不要使用CASE条件
    尽量不用触发器，特别是在大数据表上
    更新触发器如果不是所有情况下都需要触发，应根据业务需要加上必要判断条件   
    使用union all 操作代替OR操作，注意此时需要注意一点查询条件可以使用聚集索引，
    如果是非聚集索引将起到相反的结果
    当只要一行数据时使用 LIMIT 1
    尽可能的使用 NOT NULL填充数据库
    拆分大的 DELETE 或 INSERT 语句
    批量提交SQL语句

4、架构优化

1）业务拆分：搜索功能，like ，前后都有%，一般不用MySQL数据库
2）业务拆分：某些应用使用nosql持久化存储，例如memcahcedb、redis、ttserver 比如粉丝关注、好
友关系等；
3）数据库前端必须要加cache，例如memcached，用户登录，商品查询
4）动态数据静态化。整个文件静态化，页面片段静态化
5）数据库集群与读写分离；
6）单表超过2000万，拆库拆表，人工或自动拆分（登录、商品、订单等）

5、流程、制度、安全优化

任何一次人为数据库记录的更新，都要走一个流程
1）人的流程：开发-->核心开发-->运维或DBA
2）测试流程：内网测试-->IDC测试-->线上执行
3）客户端管理：phpmyadmin等

作业

一、创建表并插入数据：

        字段名 数据类型 主键 外键 非空 唯一 自增
        id INT 是 否 是  是 否
        name VARCHAR(50) 否 否 是  否 否
        glass  VARCHAR(50) 否 否 是  否 否


stu3表内容
id name glass
1 xiaommg glass 1
2 xiaojun glass 2

1、创建一个可以统计表格内记录条数的存储函数，函数名为count_stu3()
@ALt
2、创建一个存储过程avg_sai，有3个参数，分别是deptno，job，接收平均工资，功能查询emp2表dept为30，job为销售员的平均工资。
@Alt