MySQL数据库优化（一）

        MySQL性能的优化，对于使用Mysql数据库的系统来说，是非常重要的，因为数据是存放在数据库中的，系统要能够快速的获取到数据，并与数据库发生交互，那么数据库的优化也是必不可少的。自己在开发过程中，对于项目的设计的时候，虽然自己主要做的是后台的开发，但是对于数据库的设计，也是需要了解的，所以，自己就根据自己在开发过程中，将一些Mysql方面的知识进行一下描述，来方便更多的人一起学习，如果有什么不妥之处，欢迎大家进行指正~~~~~~~
     

      主要从下面这个思维导图来进行分析，并且也是面试经常爱问的知识点

       

 

 

知识点一： 影响性能的因素

 一、数据库优化方面：

   1、数据库（表）设计合理，我们设计的表要符合3NF

   2、sql语句的优化（索引，常用的小技巧）

   3、数据的配置（缓存设大）

   4、适当的硬件配置和操作系统

二、那些数据不能写到数据库中

   1、二进制文件（声音、图片）

   2、流水队列数据

   3、超大文本

因为这些都会降低效率，主要就是增加了IO次数，所以，对于系统中的操作日志的话，一般都是按照公司规定的格式写到文件当中，或者使用高吞吐量的数据库，比如redis等。

三、合理的cache

下面的数据适合放到cache中：

    1、系统的配置文件

    2、活跃的用户信息（一般会放在session中）

    3、活跃用户的制化信息

    4、基于时间段的统计数据

    5、读远远大于写的数据

四、减少和数据库的交互

        N+1 问题：

解释：比如一个A对象中，关联着都关联着一个对象B，也就是一种1：1的关系，那么如果要首先查询出A对象的信息，那么是不是就需要首先查询A的基本数据，然后再一次用N次去查找与其相关的B的数据呢？这样，是不是就出现了N+1的情况。就比如，user关联depart，首先，查询出所有的员工就是一次查询（然后有N个user对象)，然后需要把每个员工中的部门信息查询出来，那么是不是就会有N次的查询depart操作，那么这就是N+1问题了，其实叫做1+N问题更好理解些。

解决办法：

1：使用链接查询：内联查询；缺点就是如果关联的对象过于，那么性能不是很好；另外会造成结果集过大，   

2：使用冗余字段：也就是在A表中，增加B表的数据段，也就是常说的冗余字段，这个方法很明显，就是会增加冗余内容，而且在修改B的时候，就需要操作两张表的内容，对于数据一致性的问题，就需要进行严格的控制，而且对于缓存中的数据就会造成失效。

3：使用1+1查询：也就是说，比如我要查员工，对应的部门信息，那么首先查询一次把所有员工表中的数据都查询出来，然后把员工中的部门字段的id，放入到一个set集合中，然后在对set集合中非重复的数据进行查询部门表，通过sql语句的“IN”方式，这样就减少了重复出现的部门的信息查询了。这是一种比较折中的方法，只是这样的方式对于程序代码会多写一点，但是都是在内存中进行的，所以是相对好的解决方式。

 

五、数据库优化的第一原则(!!!重要)

   原则：使Mysql中的查询优化器能够选择出程序员所预期的结果方向进行sql的查询执行计划。

   Mysql的执行流程:

                                      

            

执行过程：

（1）首先客户端发送一条查询给服务器

（2）服务器通过权限检查后会先到查询缓存中去查找是否存在相应的内容，如果命中，那么立即从查询缓存中返回结果。否则进入下一步。（命中查询缓存的几率是很小的）

（3）服务器进行SQL解析，预处理，再由查询优化器根据该SQL所涉及到的数据表的统计信息进行计算，生成对应的执行计划

（4）Mysql根据优化器生成的执行计划，调用存储引擎的API来进行查询

（5）将查询结果返回给客户端

SQL执行的最大瓶颈在于磁盘的IO，即数据的读取；不同SQL的写法，会造成不同的执行计划的执行，而不同的执行计划在IO的上面临完全不一样的数量级，从而造成性能的差距；

根据上面的流程，所以说，Mysql的优化，其实就是让查询优化器根据程序员的计划来选择匹配的执行计划，来减少查询过程中的IO次数

六、MySQL的存储引擎

      MyISAM：MyISAM拥有较高的插入、查询速度，但不支持事物。MyISAM主要特性有：

1、大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持
2、当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成
3、每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16
4、最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上
5、BLOB和TEXT列可以被索引
6、NULL被允许在索引的列中，这个值占每个键的0~1个字节
7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩
8、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快
9、可以把数据文件和索引文件放在不同目录
10、每个字符列可以有不同的字符集
11、有VARCHAR的表可以固定或动态记录长度
12、VARCHAR和CHAR列可以多达64K

      lnnoDB：InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。InnoDB主要特性有：

1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合
2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上
4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
5、InnoDB被用在众多需要高性能的大型数据库站点上
InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件

七、表结构（schema）设计对系统性能的影响

     （1）冗余数据的处理

       关系数据库的三范式：

        第一范式：确保每列的原子性，是指数据库表的每一列都是不可分割的基本数据项。

        第二范式：确保每行的唯一性（主键），要求数据库表中的每个实例或行必须可以被惟一地区分。

        第三范式：除主键外，每列和主键是直接的关系，而不是间接的关系（外键）。要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。 (不允许有冗余数据) 

      （2）大表拆小表，有大数据的单独拆成小表

               1,在一个数据库中,一般不会设计属性过多的表;
               2,在一个数据库中,一般不会有超过500/1000万数据的表(拆表,按照逻辑拆分,按照业务拆分);
               3,有大数据的列单独拆成小表(富文本编辑器,CKeditor);

     （3）、把常用属性分离成小表

                1，减少查询常用属性需要查询的列;
                2，便于常用属性的集中缓存;   

博客转载自：https://blog.csdn.net/Cs_hnu_scw/article/details/78580444