MySQL优化（三）

一、存储函数

存储函数就是函数

1、自定义函数

（1）定义语法

create function 函数名(参数) returns 返回值类型
begin 
    //代码
end

说明：

（1）函数内部可以有各种编程语言的元素：变量，流程控制，函数调用；

（2）函数内部可以有增删改等语句！

（3）但：函数内部不可以有select（或show或desc）这种返回结果集的语句！

（2）调用

跟系统函数调用一样：任何需要数据的位置，都可以调用该函数。

案例1：返回两个数的和

create function sumhe(num1 int,num2 int) returns int

begin

    return num1+num2;

end$

案例2：定义一个函数，返回1到n的和。

create function nhe(n int) returns int
begin
    declare i int default 1;
    declare s int default 0;
    while i<=n do 
        set s=s+i;
        set i=i+1;
    end while;
    return s;
end$

注意点：创建的函数，是隶属于数据库的，只能在创建函数的数据库中使用。

select sumhe(345,34435)$

2、系统函数

（1）数字类

mysql>select rand(); // 返回0到1间的随机数

mysql>select * from it_goods order by rand() limit 2; // 随机取出件商品

mysql>select floor(3.9) // 输出3 
mysql>select ceil(3.1) // 输出4 
mysql>select round(3.5) // 输出4四舍五入
select goods_name,round(shop_price) from goods limit 10;

（2）大小写转换

mysql> select ucase('I am a boy!') //  --转成大写

mysql> select lcase('I am a boy!') //  --转成小写

3）截取字符串

mysql> select left('abcde',3) // --从左边截取

mysql> select right('abcde',3) // --从右边截取

mysql> select substring('abcde',2,3) // --从第二个位置开始，截取3个，位置从1开始

select left(goods_name,1),round(shop_price) from goods limit 10

mysql> select concat(10,':锄禾日当午') // --字符串相连

select concat(left(goods_name,1),'...'),round(shop_price) from goods limit 10;

// coalesce(str1,str2)：如果第str1为null，就显示str2
mysql>select coalesce(null,123); 
 
select goods_name,coalesce(goods_thumb,'无图')from goods limit 10;

mysql> select length('锄禾日当午') // 输出10   显示字节的个数
mysql> select char_length('锄禾日当午') // 输出5   显示字符的个数
mysql> select length(trim('  abc  ')) // trim用来去字符串两边空格
mysql> select replace('abc','bc','pache') // 将bc替换成pache

（4）时间类

mysql> select unix_timestamp() // --时间戳

mysql> select from_unixtime(unix_timestamp()) //  --将时间戳转成日期格式 

from_unixtime(unix_timestamp(),'%Y-%m-%d-%h-%i-%d')

mysql>select curdate(); // 返回今天的时间日期： 

mysql> select now() // --取出当前时间

案例1：比如一个电影网站，求出今天添加的电影；在添加电影时，有一个添加的时间戳。

select id,title from dede_archives where (from_unixtime(添加时间,'%Y-%m-%d')=curdate());

案例2：比如一个电影网站，求出昨天添加的电影；在添加电影时，有一个添加的时间戳。

扩展，如何取出昨天或者指定某个时间的电影：date_sub

基本用法：

date_sub(时间日期时间,interval 数字 时间单位) 

说明：

（1）时间单位：可以是year month day hour minute second

（2）数字：可以是正数和负数。

比如：取出昨天的日期：

mysql> select date_sub(curdate(),interval 1 day);

比如：取出上一个月日期：

mysql> select date_sub(curdate(),interval 1 month);

如下案例是：求出前第2天添加的电影数据

select id,title,from_unixtime(add_time,'%Y-%m-%d') from movie where (from_unixtime(add_time,'%Y-%m-%d'))=date_sub(curdate(),interval 2 day);

二、触发器

1、简介

（1）触发器是一个特殊的存储过程，它是MySQL在insert、update、delete的时候自动执行的代码块。

（2）触发器必须定义在特定的表上。

（3）自动执行，不能直接调用。

作用：监视某种情况并触发某种操作。

触发器的思路：

监视it_order表，如果it_order表里面有增删改的操作，则自动触发it_goods里面里面增删该的操作。

比如新添加一个订单，则it_goods表，就自动减少对应商品的库存。

比如取消一个订单，则it_goods表，就自动增加对应商品的库存减少的库存。

2、触发器四要素

监视地点：就是设置监视的表

监视事件：设置监视的那张表的insert ,update,delete操作；

触发时间：设置触发时间，监视表的操作之前，还是之后；

触发事件：满足条件了，设置的触发的操作；

准备测试数据:

3、创建触发器

创建触发器的语法：

create  trigger trigger_name 
    after/before insert /update/delete on  表名
    for each row 
    begin 
    sql语句：（触发的语句一句或多句）
end 

案例1：第一个触发器，购买一头猪，减少1个库存。

分析：

监视地点：it_order表

监视事件：it_order表的insert 操作；

触发时间：it_order表的insert 操作之后

触发事件：it_goods表猪的库存减1操作；

create trigger t1
    after insert on it_order
    for each row
    begin
        update it_goods set goods_number=goods_number-1 where id=1;
end$

注意：以上触发器是有问题的， 无论买谁，都是减少的猪的数量，而且数量是1

案例2：购买商品，减少对应库存

create trigger t1
    after insert on it_order
    for each row
    begin
        update it_goods set goods_number=goods_number-new.much where id=new.goods_id;
end$

注意：如果在触发器中引用行的值。

对于insert而言，新增的行用new来表示，行中的每一列的值用 new.列名 来表示

测试结果

特别注意：

案例3：取消订单时，减掉的库存要添加回来

分析：

监视地点：it_order表

监视事件：it_order表的delete操作；

触发时间：it_order表的delete操作之后

触发事件：it_goods表减掉库存再加回来；

注意：

对于delete而言，it_order表删除的行用old来表示，行中的每一列的值，用 old.列名来表示。

create trigger t2
    after delete on it_order
    for each row
    begin
        update it_goods set goods_number=goods_number+old.much where id=old.goods_id;
end$

案例4：修改订单时，库存也要做对应修改(修改的数据，有商品的数量，类型)

注意：

对于update而言，修改之前行用old来表示，行中的每一列的值，用 old.列名来表示。

修改之后，用new来表示，行中的每一列的值，用 new.列名来表示

思路：如何完成修改订单，触发it_goods表的操作，

（1）取消订单

（2）重新下单

create trigger t3
    after update on it_order
    for each row
    begin
        update it_goods set goods_number=goods_number+old.much where id=old.goods_id;
        update it_goods set goods_number=goods_number-new.much where id=new.goods_id;
end$

4、删除触发器

语法：

drop trigger 触发器的名称

5、查看触发器

语法：

show triggers

6、before和after的区别

after是先完成数据的增删改，再触发，触发器中的语句晚于监视的增删改，无法影响前面的增删该动作。

就类似于先吃饭，再付钱。

before是先完成触发，再增删改，触发的语句先于监视的增删改发生，我们有机会判断修改即将发生的操作。

就类似于先付钱，再吃饭

典型案例：对于已下的订单，进行判断，如果订单的数量>5，就认为是恶意订单，强制把所定的商品数量改成5

分析：

监视的表 :it_order

监视的事件：it_order表的insert操作

触发的时间：it_order表的insert操作之前

触发的事件：如果订单数量大于5，则改成5

create trigger t4
    before  insert on it_order
    for each row
    begin
        if new.much>5 then
        set new.much=5;
        end if;
end$

三、事务操作

​ MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

​ 在 MySQL 中只有使用了Innodb数据库引擎的数据库或表才支持事务。

​ 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

​ 事务用来管理 insert,update,delete 语句

事务特点：

（1）原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

（2）一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。

（3）隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

（4）持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

语法：

BEGIN 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认

// php
$pdo = new PDO('mysql:host=localhost;dbname=php70','xiaolong','123456');
$pdo->exec('set names utf8');
$pdo->beginTransaction(); // 启动事务
$pdo->commit() // 提交事务
$pdo->rollback() // 回滚事务

测试数据如下；

$pdo = new PDO('mysql:host=localhost;dbname=php69','root','root');
$pdo->exec('set names utf8');
$pdo->beginTransaction();
$res1 = $pdo->exec("insert into user values(null,'name3',12,'email1',1)");
$res2 = $pdo->exec("insert into user values(null,'name4',12,'email2',2))");
if(!$res1 || !$res2 ){
    $pdo->rollback();
}else {
    $pdo->commit();
}
echo 'ok';

四、读写分离（主从复制）

1、什么是主从复制

至少两台数据库服务器，可以分别设置主服务器和从服务器，对主服务器的任何操作都会同步到从服务器上。

主要作用：

（1）分担压力

（2）备份数据

2、实现原理

mysql中有一种日志，叫做bin日志（二进制日志），会记录下所有修改过数据库的sql语句。主从复制的原理实际是多台服务器都开启bin日志，然后主服务器会把执行过的sql语句记录到bin日志中，之后从服务器读取该日志，在从服务器再把bin日志中记录的sql语句同样的执行一遍。这样从服务器上的数据就和主服务器相同了。

实现读写分离使用的知识点

（1）主从都要开启bin日志

（2）主服务器需要授权用户

（3）具体的配置过程；

3、账号(用户)管理

（1）添加账号（用户）

语法：grant 权限 on 数据库.数据表 to ‘用户名’@’ip地址’ identified by ‘密码’

比如：

grant all on  *.*  to ‘xiaohei’@’%’ identified by ‘1234’

注意：创建的用户信息，是保存在mysql库下面的user表里面的。

select user,host from mysql.user;

案例：

第一步：在windows的虚拟主机里面，添加一个账号如下

注意：mysql里面用户信息是存储到mysql库下面user表中

第二步：在linu中里面使用window中新建的用户(xiaoqian)登录window中 mysql服务器。

mysql -h192.168.1.10 –uxiaomei –p123456

（2）删除账号

语法：drop user ‘用户名’@’ip地址’;

4、bin-log开启操作

（1）开启bin-log日志

打开mysql的配置文件，

window下面 my.ini

linux下面 my.cnf

log-bin=mysql-bin

server-id=1

注意：修改完成mysql的配置后，要重启mysql服务

注意：mysql 里面数据表的存储位置，要看配置文件

开启配置后，产生的二进制日志文件如下

（2）与log-bin日志相关的函数

flush logs

执行该命令，就会产生一个新的log-bin日志

产生的文件如下

reset master;

清空所有的log-bin日志，并产生一个新的log-bin日志

show master status

查看最后（新）的一个log-bin日志

（3）查看log-bin日志里面的内容

新建一张表，测试log-bin日志是否记录增删改的sql语句

注意：使用mysql安装目录下面的bin目录下面mysqlbinlog命令，来查看日志内容。

语法：mysqlbinlog --no-defaults 二进制日志的名称（全路径）

MySQL中二进制文件所在目录

/var/lib/mysql

注意：end_log_pos的理解，用于记录上一个 sql语句的结束，下一个sql语句 的开始位置

通过show master status命令，能查看到二进制文件里面最后一个pos位置

五、具体的配置步骤

实验规划，需要两台主机

第一台主机：ip地址 192.168.1.69 配置为master服务器

第二台主机：ip地址 192.168.1.70 配置为slave 服务器

1、配置主服务器

（1）开启二进制日志。

（2）要设置一个server-id（作为一个服务器的编号，是唯一） 该值不能和从服务器相同。

注意：在my.cnf配置文件里面，配置的区域在[mysqld]与[mysql]之间配置；

注意：配置完成后，要重启mysql服务

（3）授权一个账号，让从服务器通过该账号读取log-bin日志里面的内容

grant replication slave on *.* to 'xiongda'@'%' identified by '123456'

赋予从库权限账号，允许用户在主库上读取日志，也就是Slave机器读取File权限，

grant FILE on *.* to 'xiongda'@'%' identified by '123456';

（4）记录主服务器里面的最新的二进制的名称和pos位置

注意：此时，就禁止对主服务器执行增删改的操作，一直到从服务器配置成功。

2、配置从服务器

（1）开启二进制日志。

（2）要设置一个server-id 该值不能和主服务器的相同。

注意：配置好配置文件后，要重启mysql服务器；

（3）停止从服务器

执行，stop slave 指令即可。

（4）开始配置，

配置的语法：

change master to master_host=”主服务器的ip地址”,master_user=”授权用户的名称”,master_password=”授权用户的密码”,master_log_file=”二进制日志文件的名称”,master_log_pos=记录的pos位置；

change master to master_host=”192.168.1.10”,master_user=’xiaogang’,master_password=’123456’,master_log_file=”mysql-bin.000001”,master_log_pos=611

（5）开启从服务器

执行start slave指令即可。

（6）查看是否配置成功

执行show slave status;

Slave_IO_Running:Yes

此进程负责从服务器从主服务器上读取binlog 日志，并写入从服务器上的中继日志。

Slave_SQL_Running:Yes

此进程负责读取并且执行中继日志中的binlog日志，

注：以上两个都为yes则表明成功，只要其中一个进程的状态是no，则表示复制进程停止，错误原因可以从”last_error”字段的值中看到。

3、测试主从复制

在主服务器创建一个新库，并添加一张新表，并插入新数据

在从服务器上面查看是否有该库，该表，该记录。

4、撤销从服务器

在从服务器上执行如下两个指令。

（1）stop slave

（2）reset slave all

六、实现读写分离

1、通过业务逻辑来实现读写分离

class mysql{
    $dbm=主服务器 
    $dbs1=从服务器 
    $dbs2=从服务器 
    public function query(){
       在query里面进行语句判断，分析连接不同的mysql服务器。 
    }
}

2、TP框架里面实现读写分离

具体的步骤：

（1）通过mysql授权账号，

注意：授权的账号，是给php代码连接的。

主服务器：192.168.1.69的主机授权账号如下：

从服务器192.168.1.70的主机授权账号如下：

（2）步骤TP框架里面配置文件

（3）在TP里面，测试读写分离的配置，

db()->query('select * from user');

db()->execute('insert into user values(3,"xiaolong")')