原 MySQL数据库优化

柔情只为你懂 2021-11-02 04:46 529阅读 0赞

#####  #####

##### 一、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语句**

**![20180409224758286][]**

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

[20180409224758286]: /images/20211102/e97dd5a741584b6bb15f63b62821a5b5.png