原 MySQL数据库优化

                                                                                           

                                           

一、mysql优化总览

① 存储层：表存储引擎选取、字段选取
② 设计层：索引、分库分表、表设计(逆范式)
③ 架构层：分布式部署(主从模式、共享)
④ sql语句层：结果一样的情况下，要选择效率高、速度快、节省资源的sql语句执行

二、存储引擎

1.概述：数据表存储数据的一种格式。MySQL5.5后默认使用InnoDB。
2.常用的两种存储引擎：InnoDB、Myisam
3.InnoDB介绍
    支持事务、支持行级锁、支持外键
    ①结构、索引、数据文件
    有单独的结构文件，默认情况下所有数据库所有innodb数据表的 索引、数据 文件都合并到一起
    ②数据存入顺序
    数据的写入顺序与存储的顺序不一致，需按照主键的顺序把记录摆放到对应的位置上，速度比Myisam的要稍慢。
    ③并发性
    并发性高，多人同时请求，速度快、效率高。
    锁机制：行锁，每次只锁住一条记录信息。
4.Myisam介绍
    ①结构、索引、数据文件单独分开，可拷贝
        student.frm        表结构文件
        student.MYD        表数据文件
        student.MYI        表索引文件
    ②数据存入的顺序
        Myisam数据表存入数据的时候，不给排序，按照写入的顺序进行存储。好处是速度非常快
    ③并发性
        并发性低，多人同时做请求速度稍慢。
        锁机制：每次都锁住整张数据表 。
    ④压缩机制(节省硬盘空间资源)
        压缩
            myisampack.exe  表名
        重建索引(根据压缩后的数据把索引重建建立起来)
            myisamchk.exe  -rq  表名
        只读特性
            压缩的数据表不能再写数据了，有的数据表适合压缩，数据不频繁发生变化的数据适合
        解压(解压缩的同时，索引会自动重建)
            myisamchk.exe  --unpack  表名
        刷新表数据到硬盘
            flush table 表名
5.Myisam和InnoDB对比
    myisam： 写入数据非常快，适合使用场合dedecms/phpcms/discuz等写入、读取操作多的系统
    innodb:  适合业务逻辑比较强的系统，例如ecshop、crm、办公系统、商城系统

三、字段选取

1.选取占据空间小的字段
例：人的年龄适合使用tinyint(0,255)类型
    乌龟的年龄使用smallint(0,65535)类型
    数据表主键id值在没有超过1600万的时候，可使用mediumint类型
2.内容长度固定字段
    存储汉字(utf-8字符集 3个字节=1个汉字)    

    varchar(可变长度)    1--65535字节        运行速度快，例如char(20)  实际存储16个字符，分配20个空间

    char(固定长度)        1--255字符            运行速度稍慢，例如varchar(20)  实际存储16个字符，分配16个空间

3.整型存储

    底层转为整型存储的类型：
    int    
    date
    time
    datetime
    timestamp
    set(集合)
    enum(枚举)
    ip地址(函数inet_ntoa)

四、表设计

1.逆范式概述：数据表的总体设计要遵守三范式，但是有的时候为了整体性能的考虑，并不遵守这点，称为“逆范式”。

五、索引

1.概述：索引是一种数据结构，数据结构内部有算法，可以帮我们快速、准确定位需要的信息。索引是进行数据库设计时，提升性能最有效的一个技术。索引是数据存储的一种机制，该机制里边可以简单理解有索引字段、字段对应记录的物理地址。索引字段按照一定的规律、规则组织在一起(内部有数据结构/算法),可以加快信息的查找，信息在索引内部被查找出来后，直接对应记录的“物理地址”，根据物理地址就可以快速定位需要的记录信息。
2.索引类型：
    ①主键索引
    ②唯一索引
    ③普通索引
    ④全文索引(给文章的内容创建全文索引,mysql5.6.4之前只有Myisam支持全文索引，之后 Myisam和Innodb都支持。)
    ⑤复合索引(多个字段组成索引)
3.索引创建(索引名称不设置默认与当前索引字段名称一致)：
    a.创建数据表同时设置索引
        primary key [名称] (id),
        unique key [名称] (name),
        key [名称] (height),
        fulltext key [名称] (introduce)
    b.创建数据表后创建索引
        create index index_name on table(column(length))        
    b.给存在的数据表增加索引
        alter table student add primary key (id);
        alter table student add unique key (name);
        alter table student add key (height);
        alter table student add fulltext key (introduce);
        alter table student add nh (name,height);
4.删除索引
    删除主键索引，必须先去除auto_inrement属性
    alter table student modify id not null comment '主键';
    alter table student add drop primary key;
    alter table student add drop key name;
    alter table student add drop key nh;
5.Mysql索引的两种结构
    B+Tree索引
    Hash索引
    B树索引具有范围查找和前缀查找的能力，对于有N节点的B树，检索一条记录的复杂度为O(LogN)。相当于二分查找。哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)。显然，如果值的差异性大，并且以等值查找（=、 <、>、in）为主，Hash索引是更高效的选择，它有O(1)的查找复杂度。如果值的差异性相对较差，并且以范围查找为主，B树是更好的选择，它支持范围查找。
6.索引适合场合
① where条件后边的字段都可以设置索引
② 排序字段适合做索引
③ 索引覆盖
    数据查询只通过索引内容就获得需要的信息，就称为“索引覆盖”。需要的信息，单纯从索引内部就提供好了,其不会去记录中获取其他不相关的信息，因此索引本身就满足我们对数据的获取，这样的索引和查询结合起来运行速度是最快的，也可以称为是“黄金索引”。
④ 连接查询
   select  from 表1  join  表2  on  表1.字段=表2.字段
   上边sql语句的表1.字段 也称为“外键字段”外键字段也适合做索引。
注：有的字段重复内容很多(例如性别字段)，则不要设置索引，作用不大。
7.索引原则        
① 字段独立原则
    select * from emp  where empno=1345629;        //字段独立，可以使用索引
    select * from emp  where empno+2=1345629;  //字段不独立(不能使用索引)
② 左原则
    模糊查询左边内容固定的时候可以用到索引，否则不能使用
③复合索引
    复合索引的第一个字段，单独作为条件可以使用到该索引。
    复合索引的第二个字段，单独作为条件不可以使用到该索引。
    复合索引的两个字段都作为条件，则可以使用该索引。
④or原则
    两边都有索引，则会全部都分别使用。
    一个有索引，另一个没有索引(与顺序无关)，结果都没得使用。
8.索引设计依据
① 被频繁使用的字段设置索引
    字段被频繁用在where和order等条件里边。
    数据表创建完毕，要预估那些字段被经常使用，就给其创建索引
② 执行时间长的sql语句考虑设计索引
    可以利用”慢查询日志”收集这样的sql语句并优化设计索引
③ 逻辑非常重要的sql语句考虑设计索引
④ 字段内容足够花样化，可以考虑设计索引
    反面教材，性别不能设计索引(内容的取值非常少)
9.前缀索引
①概述：如果一个字段的内容的前边的n位信息已经足够可以标识当前的字段内容，就可以把字段的前n位获得出来并创建索引，通过字段内容前n位创建的索引就称为“前缀索引”。
②好处：索引占据的物理空间要比较小，这样的索引运行速度快、效率高，对mysql整体性能提升有很大帮助。
③具体操作：
    alter table  表名  add  key  (字段(位数))
    例：select count(distinct substring(password,1,11)) from emp;    
10.全文索引
① 字段类型必须为varchar/char/text类型
② mysql 5.6.4之前只有Myisam支持，5.6.4之后则Myisam和innodb都支持。
③ mysql中的全文索引目前只支持英文(不支持中文)，如果需要支持中文可以使用sphinx
④ 显示生产中mysql的全文索引不常使用，sphinx常使用。mysql全文索引自作聪明，对关键字的收录有自己的考虑。
⑤示例：
    alter table articles add fulltext index `index_body` (body);
    alter table articles add fulltext index `index_fu` (title,body);
11.索引结构
①Mysql的数据结构都是B+tree结构
②Myisam索引结构
    Myisam索引结构称为“非聚合型”
    索引运行原理：
        快速定位主键id值，获得对应记录物理地址，获得记录信息。
③Innodb索引结构
    Innodb索引结构称为“聚合型”
    主键索引运行原理：
        a.通过索引结构快速定位主键id值对应的”叶子节点”
        b.该叶子节点里边直接与整条记录信息进行对应(而在Myisam里边，叶子节点与物理地址对应)
    其他索引运行原理：
        a.通过索引结构内部的算法快速定位该名字对应的”叶子节点”
        b.叶子节点 里边对应的是关键字的记录主键id值
        c. 通过记录主键id值走主键索引即可
12.索引失效的几种情况
索引不存储null值
不适合键值较少的列（重复数据较多的列）
like查询以%开头
条件中有or
对于多列索引，不是使用的第一部分，则不会使用索引
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来
六、 执行计划explain
1.概述：一条sql语句在没有执行之前，先把需要的资源都计划好，例如cpu、内存等资源的分配预计，该形成就称为“执行计划”。5.6后，select、insert、update、delete均可以使用explain查看执行计划。
  目前只支持select获得语句执行计划
2.命令(或者Navicat查看)：

  explain sql语句

3.执行计划的查看
    Id：包含一组数字，表示查询中执行select子句或操作表的顺序；
    Select_type:表示查询中每个select子句的类型（简单OR复杂），有以下几种：
        SIMPLE：查询中不包含子查询或者UNION。
        PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
        SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为SUBQUERY。
        DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）。
        若第二个SELECT出现在UNION之后，则被标记为UNION。
        若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED。
        从UNION表获取结果的SELECT被标记为：UNION RESULT。
    Type：表示MySQL在表中找到所需行的方式，又称“访问类型”，常见有以下几种：
        ALL：Full Table Scan， MySQL将进行全表扫描。
        index：Full Index Scan，index与ALL区别为index类型只遍历索引树。
        range：range Index Scan，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询。
        ref：非唯一性索引扫描，返回匹配摸个单独值的所有行。常见于使用非唯一索引或唯一索引的非唯一前缀进行的查找。
        eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
        const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
        NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引。
        注：通常需要达到ref级别以上才不需要优化。访问类型是sql查询优化中一个重要的指标，结果值从好到坏依次是：
            system > const > eq_ref > ref > range > index > ALL
    possible_keys：指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。
    key：显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。当查询中若使用了覆盖索引，则该索引仅出现在key列表中。
    key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
    ref：表示上述表的连接匹配条件，即那些列或常量被用于查找索引列上的值。
    rows：表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。
    Extra：包含不适合在其他列中显示但十分重要的额外信息。
        Using where：表示MySQL服务器在存储引擎受到记录后进行“后过滤”（Post-filter）,如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集。
        Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。
        Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”。

七、MySQL缓存

1.开启缓存空间即可使用：
    查询缓存空间是否开启
        show variables like 'query_cache%';
    设置缓存空间大小
        set global qeury_cache_size=64*1024*1024
    查看缓存空间使用情况
        show status like 'Qcache%';
2.数据表的数据有变化 或者 数据表结构有变化，则缓存失效。
3.生成缓存的sql语句对“空格”、“大小写”比较敏感
4.相同结果的sql语句，由于空格、大小写问题就会分别生成缓存。
5.不进行缓存    sql_no_cache
    例：select sql_no_cache * from emp where empno=154212;

八、分表设计

1.分表方式：
    水平分表：是把一个表的全部数据信息分别存储到不同的分表之中。
    垂直分表：是把一个表的全部字段分别存储到不同的表里边。
2.四种格式的逻辑分表(mysql本身的分表技术)
    key     hash    ---->[求余方式]
    range   list    ---->[范围方式]
    1)key分表
        partition by key(条件字段id) partitions 10;
    2)hash分表
        partition by hash(表达式/字段) partitions 数量;
    3)range分表
        partition by range(year(pubdate))(
            partition hou90  values  less than (2000),
            partition hou00  values  less than (2010)
        )
    4)list分表
        partition by list(month(pubdate))(
            partition spring  values  in (3,4,5),
            partition summer  values  in (6,7,8)
        )
    注：条件字段必须是主键 或者 是主键的一部分
3.增加分表
    求余方式： key/hash
        alter table 表名  add  partitions  数量;
    范围方式： range/list
        alter table 表名 add partition(
           partition 名称 values less than (常量)
           或
           partition 名称 in (n,n,n)
        );
4.删除分表
    求余方式(key/hash):
        alter table 表名 coalesce partition 12;
    范围方式(range/list):
        alter table 表名 drop partition 分区名称;
    注：key/hash方式不会丢失数据
        range/list会丢失数据
5.物理方式分表
    需要计算记录从哪个表读、给哪个表写。 
    查询：求余算法
    插入：增加辅表

九、架构设计

1.概述：架构设计也称为集群设计，由多台mysql服务器共同支撑网站的运行，每台服务器分担的工作就比较少，运行速度快、效率高。mysql数据库在运行的时候一般查询/写入的sql语句比例为：7/1，并且查询消耗的资源比写入要更多。可以设计一个“主从模式”的集群。
2.维护备份的集群架构：
图
3.主从模式的集群架构：
图

十、慢SQL查询日志收集

①概述：把系统里边一些执行速度非常慢的sql语句给收起起来，并做分析优化，使得其执行速度加快。
②慢查询分析：
    慢查询日志的开启、日志位置
        show variables like 'slow_query%';
    快慢时间临界点
        show variables like 'long_query_time';
    设置开启、时间临界点
        set global slow_query_log = 1;
        set long_query_time = 1;          --没有global