php数据库--mysql优化

mysql优化概述：

mysql优化概述
    方针：
        ① 存储层：数据表”存储引擎”选取、字段类型选取、逆范式(3范式)
        ② 设计层：索引、分区/分表、存储过程，sql语句的优化
        ③ 架构层：分布式部署(集群)(读写分离)，需要增加硬件
        ④ sql语句层：结果一样的情况下，要选择效率高、速度快、节省资源的sql语句执行

存储引擎、优化语句、索引讲解：

存储引擎：
    存储引擎：数据表存储数据的一种格式。
    不同的格式，体现的特性也是不一样的。事务、表、行级锁、压缩机制
    位置：客户端-》网络连接层-》业务逻辑层（编译，优化，执行SQL）-》存储引擎层、show engines
    ① Myisam：表锁，全文索引、 <V5.5、数据，索引，结构不同文件
        数据，索引，结构同一文件
         <V5.5、数据，索引，结构不同文件、表锁
    ② Innodb：行(记录)锁，事务（回滚），外键   V5.5、*.frm、数据，索引，结构同一文件
        数据是按照主键顺序存储
        擅长处理并发的:锁机制、读锁、写锁
        表级锁：开销小，加锁快，发生锁冲突的概率最高，并发度最低。myisam和innodb都支持。
        行级锁：开销大，加锁慢，发生锁冲突的概率最低，并发度也最高。innodb支持
    ③ Memory：内存存储引擎，速度快、数据容易丢失
        内存存储引擎，
        特点：内部数据运行速度非常快，临时存储一些信息
        缺点：服务器如果断电，重启，就会清空该存储引擎的全部数据
查询需要优化语句：
    慢查询日志：
        mysql提供的日志，记录所有执行时间超过某个时间界限的sql的语句。
        关闭服务
        mysqld.exe --safe-mode --slow-query-log、默认保存位置在data目录下
        慢查询日志时间：
            查询：show variables like ‘long_query_time’;
            修改：set  long_query_time=0.5;
            测试：select benchmark(10000,90000000*4) 次数、操作
            一般情况下，一个sql语句执行比较慢，原因是没有索引
                alter  table  emp add index(empno)
        配置文件：
            log-slow-queries="d:/slow-log"  ：慢查询日志文件存储的路径
            long_query_time=1               ：指定慢查询的时间
    精确记录查询时间：
        profile记录每次执行的sql语句的具体时间：
        开启 set profiling = 1;
        查看记录：show profiles;
        关闭 set profiling=0;
索引讲解：
    索引的作用：是用于快速定位实际数据位置的一种机制。
    类型：普通、唯一、主键、全文、复合索引
    索引管理：
        创建：
            create table index1(
                id int primary auto_increment, # 主键索引
                name varchar(32) not null,
                age tinyint not null,
                unique key (name), # 唯一索引
                index(age), # 普通
                fulltext index(intro), # 全文
                index (name,age) # 复合
            )engine myisam charset utf8;
        更新：
            alter table index2 add unique key (name),add index(age),add fulltext index(intro),add index(name,age);
        删除：
            alter table  table_name drop primary key
            alter table 表名 modify id int unsigned not null comment '主键'
            alter table 表名  drop index 索引的名称；
        查看：
            show indexes from table_name;
            show index from table_name\G
            show create table table_name
        注意事项：
            频繁查询、唯一性太差（不适合）、更新频繁（不适合）、where不出现（不合适）
执行计划：
    分析：explain  sql语句\G   或 desc sql语句\G

索引的数据结构：【略】
    show keys from 表名;
    B-tree
    myisam的存储引擎索引结构：
    innodb的存储引擎的索引结构：

索引覆盖
    索引覆盖是指：如果查询的列恰好是索引的一部分，那么查询只需要在索引区上进行，不需要到数据区再找数据，这种查询速度非常快，称为“索引覆盖”

索引的使用原则【略】
    列独立
    like查询
    OR运算都具有索引
    复合索引使用
    mysql 智能选择
    优化group by语句。

锁机制、缓存及分区、分表技术：

mysql中锁机制
    应用场合：
    mysql里面的锁的几种形式
    表锁、行锁
    php代码实现锁机制

查询缓存
    mysql服务器提供的，用于缓存select语句结果的一种内部内存缓存系统。
    show variables  like ‘query_cache%’;   //查看缓存使用情况
    query_cache_size:缓存空间大小
    query_cache_type:是否有开启缓存
    my.ini配置设置。
    重启服务器

    缓存失效：
        数据表的数据(数据有修改)有变化 或者 数据表结构(字段的增、减)有变化，则会清空全部的缓存数据，即缓存失效。
        sql语句有变化表达式，则不会生成/使用缓存。如：时间信息、随机数等
        生成缓存的sql语句对“空格”、“大小写”比较敏感（空格、大小写分别生成多个缓存）
        sql_no_cache 不进行缓存
        select  sql_no_cache  * from emp where empno=123456;
        show status like ‘Qcache%’;  //查看缓存使用情况

分区技术：
    基本概念，把一个表，从逻辑上分成多个区域，便于存储数据。
    采用分区的前提，数据量非常大。
    show variables like '%partition%'; # 是否支持分区
    分区算法：
        利用表选项：partition 完成。
        create table  table_name(
            字段信息，
            索引，
        )engine myisam charser utf8
        partition by 分区算法（分区字段）（
            分区选项
        ）；
        分区算法：
        条件分区：list (列表) range(范围)  取模轮询（hash,key）
        list分区：条件值为一个数据列表。通过预定义的列表的值来对数据进行分割
            partition by list (store_id)(
                partition p_north values in (1,4,5,6,17,18),
                partition p_east values in(2,7,9,10,11,13),
                ...
            explain partitions select * from p_list where store_id=20\G
            注意：在使用分区时，where后面的字段必须是分区字段，才能使用到分区。
            查询，没有分区条件，则会到所有的分区里面去查找，即便如此，查询效率也要比单表查询高。
        Range（范围）:
            允许将数据划分不同范围。例如可以将一个表通过月份划分成若干个分区
            列：birthday
            partition by range (month(birthday))(
                partition p_1 values less than (4),
                partition p_2 values less than(7),
            ...
            less than   小于;
            MAXVALUE 可能的最大值
        Hash（哈希）
            允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。
            列：birthday
            partition by hash(month(birthday)) partitions 5;
            partition by key (id) partitions 5;
    分区管理：
        已经存在的分区进行增加、减少操作。
        删除分区：
            在key/hash领域不会造成数据丢失
            在range/list领域会造成数据丢失
            求余方式(key/hash):
            >alter table 表名 coalesce partition 数量;
            范围方式(range/list):
            >alter table 表名 drop partition 分区名称;
            并且，数据没有减少：
            剩余唯一一个分区的时候，就禁止删除了，但是可以drop掉整个数据表，如下图：
            alter table p_hash  coalesce  partition 1;
        增加分区：
            求余方式： key/hash
            > alter table 表名  add  partition partitions  数量;
            范围方式： range/list
            >  alter table 表名 add partition(
                   partition 名称 values  less than (常量)
                   或
                   partition 名称 values  in (n,n,n)
               );
            注意：创建分区的字段必须是主键或唯一索引的一部分


分表技术：
    物理方式分表设计
    自己手动创建多个数据表出来
    水平分表：是把一个表的全部记录信息分别存储到不同的分表之中。
    垂直分表（常见）：是把一个表的全部字段分别存储到不同的表里边。

数据碎片化维护、范式、视图：

数据碎片化与维护：
    在长期的数据更改过程中，索引文件和数据文件，都将产生空洞，形成碎片，我们可以通过一个操作（不产生对数据实质影响的操作）来修改表，
    optimize    table  表名;
    表的引擎为innodb,可以alter table xxx engine innodb
    optimize table 表名，也可以修复。
    这个过程，如果表的行数比较大，也是比较耗费资源的操作，所以，不能频繁的修复。
    如果表的update，delete操作很频繁，可以按周月来修复。

范式讲解
    第一范式：
        表的属性（列）要具有原子性（不可分割）
        表不能有重复的列，
        关系型数据库:有行和列的概念，即为二维表格、关系型数据库，就天然的满足第一范式。
    第二范式：
        表要满足：不能存在完全相同的两条记录，通常是通过设置一个主键来实现，主键一般是非业务逻辑主键。
    第三范式：
        表中不能存在冗余数据，表中列的值，如果可以通过推导出来，则就不应该设置该列。
    反三范式（逆范式）：
        有的时候基于性能考虑，需要有意违反 三范式，适度的冗余，以达到提高查询效率的目的。

视图
    视图的定义：create   view   view_name   as   select 语句
        （1）视图名跟表名是一个级别的名字，隶属于数据库；
        （2）该语句的含义可以理解为：就是将该select命名为该名字（视图名）；
        （3）视图也可以设定自己的字段名，而不是select语句本身的字段名——通常不设置。
        （4）视图的使用，几乎跟表一样！
        可以简化查询。
        可以进行权限控制：
            把表的权限封闭，但是开放相应的视图权限，视图里只开放部分数据
            grant  权限  on 数据库名称.视图名或表名  to  ‘用户名称’@’%’  identified by ‘密码’’
            grant  select  on php.goods_v1  to  ‘xiaolei’@’%’  identified by ‘1234’’
    查询视图：
        select * from 视图名 [where 条件]
    修改视图：
        alter   view   view_name  as   select   XXXX
    删除视图：
        drop  view  视图名称
    查看视图结构：
        desc 视图名称
    查看所有视图：
        show tables;
    视图与表的关系:
        视图是表的查询结果，自然表的数据改变了，影响视图的结果。
        视图的数据与表的数据一一对应时，可以修改。
        视图增删该也会影响表，但是视图并不是总是能增删该的。(计算出来的数据)
        对于视图insert还应注意，视图必须包含表中没有默认值的列。
        注意：在实际的开发中，不要对视图进行增删改。

sql编程、存储过程及存储函数、触发器及事务：

sql编程
    变量：
        会话变量：set   @变量名  =  值；
        普通变量：declare  变量名   类型   【default  默认值】；
        编程环境是指  （1）存储过程 （2）函数 （3）触发器。
        set 变量名 = 表达式;#此语法中的变量必须先使用declare声明，在编程环境中使用
        set @变量名=表达式；
        select @变量名:=表达式；
        select 表达式 into @变量名;#此语句虽然看起来是select语句，但其实并不输出‘结果集’，而是给变量赋值。
    运算符：
        （1）算术运算符
        +、-、*、/、%
        注意：mysql没有++和—运算符
        （2）关系运算符
        >、>=、<、<=、=（等于）、<>（不等于） !=（不等于）
        （3）逻辑运算符
        and（与）、or（或）、not（非）
    语句块包含：
        mysql编程中的语句块包含符是begin   end结构。
    if判断：
        第一个是if判断，第二个 case判断
        if 条件 then
           代码1
        elseif 条件 then
           代码2
        else
           代码3
        end if;
        case  变量
        when 值  then     语句;
        when 值  then     语句;
        else  语句;
        end case ;
    循环：
        MySQL支持的循环有loop、while、repeat循环
       标签名:loop
           leave 标签名    --退出循环
       end loop;
       [标签:]while 条件 do
           //代码
       end while;

存储过程
    存储过程(procedure)
    概念类似于函数，就是把一段代码封装起来，当要执行这一段代码的时候，可以通过调用该存储过程来实现。

    show procedure status # 查看现有的存储过程。

    存储过程的优点：
        存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度
        当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
        存储过程可以重复使用,可减少数据库开发人员的工作量
        安全性高,可设定只有某些用户才具有对指定存储过程的使用权

    create procedure 存储过程名(参数1,参数2,…)
    begin
       //代码;
    end
    参数的类型：
    in（输入参数）： 表示该形参只能接受实参的数据——这是默认值，不写就是in；
    out（输出参数）：表示该形参其实是用于将内部的数据“传出”到外部给实参；
    inout（输入输出参数）：具有上述2个功能。

    说明：
    （1）存储过程中，可有各种编程元素：变量，流程控制，函数调用；
    （2）还可以有：增删改查等各种mysql语句；
    （3）其中select（或show，或desc）会作为存储过程执行后的“结果集”返回；
    （4）形参可以设定数据的“进出方向”：
    （5）存储过程是属于数据库，在哪个数据库里面定义的，就在哪个数据库里面调用。
    如下图，在别的数据库里面调用其他数据库里面定义的存储过程时，会报如下提示。

    调用存储过程：
        call 存储过程名称(参数)
        php：mysql_query(‘call p7(5)’);

    删除存储过程
        drop    procedure    存储过程的名称

存储函数
    存储函数就是函数
    定义语法
        create function 函数名(参数) returns 返回值类型
        begin
            //代码
        end
    说明：
        （1）函数内部可以有各种编程语言的元素：变量，流程控制，函数调用；
        （2）函数内部可以有增删改等语句！
        （3）但：函数内部不可以有select（或show或desc）这种返回结果集的语句！
    调用：
        创建的函数，是隶属于数据库的，只能在创建函数的数据库中使用。
        select 存储函数();
    系统函数：
        数字类
            select rand();、order by rand()
            floor、ceil、round
        大小写：
            ucase、lcase
        截取：
            left、right、substring
        字符串：
            concat、coalesce、length、char_length、replace、trim
        时间：
            timestamp()、curldate()、now()、from_unixtime(unix_timstamp(),'%Y-%m-%d %h:%i:%s')
            date_sub(时间日期时间,interval 数字  时间单位)、
触发器
    （1）触发器是一个特殊的存储过程，它是MySQL在insert、update、delete的时候自动执行的代码块。
    （2）触发器必须定义在特定的表上。
    （3）自动执行，不能直接调用，
    作用：监视某种情况并触发某种操作

    监视地点：就是设置监视的表
    监视事件；设置监视的那张表的insert ,update,delete操作；
    触发时间：设置触发时间，监视表的操作之前，还是之后；
    触发事件：满足条件了，设置的触发的操作；

    创建触发器的语法：
        create  trigger  trigger_name
        after/before  insert /update/delete  on   表名
        for each row
        begin
        sql语句：（触发的语句一句或多句）
        end
    删除：
        drop trigger 触发器的名称
    查看：
        show triggers
    before和after的区别：
         after是先完成数据的增删改，再触发，触发器中的语句晚于监视的增删改，无法影响前面的增删该动作。
         before是先完成触发，再增删改，触发的语句先于监视的增删改发生，我们有机会判断修改即将发生的操作。
事务操作
    MySQL 事务主要用于处理操作量大，复杂度高的数据。
    在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
    事务处理可以用来维护数据库的完整性
    事务用来管理 insert,update,delete 语句
    原子性：事务要么完成要么不完成成
    一致性：开始到结束、完整性没有破坏
    隔离性：多个事务读写互不干扰
    持久性：数据修改持久性的。

    BEGIN 开始一个事务
    ROLLBACK 事务回滚
    COMMIT 事务确认

    $pdo->beginTransaction();//启动事务
    $pdo->commit()提交事务
    $pdo->rollback()回滚事务

读写分离，配置：

读写分离（主从复制）
    至少两台数据库服务器，可以分别设置主服务器和从服务器，对主服务器的任何操作都会同步到从服务器上。
    主要作用：
        （1）分担压力
        （2）备份数据
    mysql中有一种日志，叫做bin日志（二进制日志），会记录下所有修改过数据库的sql语句。
    （1）主从都要开启bin日志
    （2）主服务器需要授权用户
    （3）具体的配置过程；

    账号(用户)管理：
        添加账号：grant  权限  on 数据库.数据表  to  ‘用户名’@’ip地址’   identified  by ‘密码’
        删除账号：drop  user  ‘用户名’@’ip地址’;
        bin-log开启操作：
            开启bin-log日志：my.ini
                log-bin=mysql-bin
                server-id=1
            flush logs 执行该命令，就会产生一个新的log-bin日志
            reset master;  清空所有的log-bin日志，并产生一个新的log-bin日志
            show master status 查看最后（新）的一个log-bin日志
        查看log-bin日志里面的内容:
            mysqlbinlog    --no-defaults  r日志路径
                end_log_pos的理解，用于记录上一个 sql语句的结束，下一个sql语句 的开始位置
            通过show master status命令，能查看到二进制文件里面最后一个pos位置。


具体配置步骤
    两台数据库主机
    开启二进制日志。
    要设置一个server-id（作为一个服务器的编号，是唯一）  该值不能和从服务器相同。
    注意：在my.cnf配置文件里面，配置的区域在[mysqld]与[mysql]之间配置；
    注意：配置完成后，要重启mysql服务
    server-id=1
    log-bin=mysql-bin
    授权一个账号，让从服务器通过该账号读取log-bin日志里面的内容
    grant  replication slave  on *.*  to 'xiongda'@'%' identified by '123456'
    赋予从库权限账号，允许用户在主库上读取日志，也就是Slave机器读取File权限，
    grant FILE on *.* to 'xiongda'@'%' identified by '123456';
    此时，就禁止对主服务器执行增删改的操作，一直到从服务器配置成功。

    配置从服务器
    开启二进制日志。
    要设置一个server-id  该值不能和主服务器的相同。
    停止从服务器
    开始配置，
    change master to master_host=”主服务器的ip地址”,master_user=”授权用户的名称”,master_password=”授权用户的密码”,master_log_file=”二进制日志文件的名称”,master_log_pos=记录的pos位置；
    开启从服务器  执行start slave指令即可。
    查看是否配置成功 执行show slave status;

    撤销从服务器：stop slave、reset slave all
读写分离
    通过业务逻辑来实现读写分离
    TP框架里面实现读写分离
        通过mysql授权账号
        配置TP框架里面配置文件
            database.php
        db()->query('select * from user');
        db()->execute('insert into user values(3,"xiaolong")')