前言
基本的数据库基础知识和常用的SQL语句在之前的博客中，南国已经描述过了。这一篇，通过南国这段时间的复习和思考，写一篇关于MySQL的优化。
MySQL性能的优化，对于使用Mysql数据库的系统来说，是非常重要的，因为数据是存放在数据库中的，系统要能够快速的获取到数据，并与数据库发生交互，那么数据库的优化也是必不可少的。
影响MySQL性能的因素
1.数据库优化的方面
- 数据库(表)设计合理
我们的表设计要符合3NF 3范式(规范的模式) ,有时我们需要适当的逆范式
-
sql语句的优化(索引，常用小技巧.)
-
数据的配置(缓存设大)
-
适当硬件配置和操作系统 (读写分离.)
2.哪些数据不适合写到MySQL
MySQL是关系型数据库的典型，所以他一般用于存储结构化的数据。
非结构化的数据，如果存储到MySQL中，会降低效率。这主要是因为增加了IO次数，所以，对于系统中的操作日志的话，一般都是按照公司规定的格式写到文件当中，或者使用高吞吐量的数据库，比如redis等。
对于非结构化的数据 更多采用NoSQL的数据库，例如Redis MongoDB或者现在大数据热门框架HBase。
3.合理的cache
对下列数据 可放在cache中 提高MySQL的性能：
1：系统配置信息
2：活跃的用户基本信息（一般会放到session中）
3：活跃的用户的制化信息
4：基于时间段的统计数据
5：读远远大于写的数据
4.减少与数据库的交互
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”方式，这样就减少了重复出现的部门的信息查询了。这是一种比较折中的方法，只是这样的方式对于程序代码会多写一点，但是都是在内存中进行的，所以是相对好的解决方式。
5.MySQL优化的第一原则（重要）
原则：使用MySQL中的查询优化器能够选择出用户所预期的结果方向进行sql的查询执行计划。
完整的SQL语句执行计划如下：
- 1.首先客户端发送一条查询给服务器
- 2.服务器通过权限检查后会先到查询缓存中去查找是否存在相应的内容，如果命中，那么立即从查询缓存中返回结果。否则进入下一步。（命中查询缓存的几率是很小的）
- 3.服务器进行SQL解析，预处理，再由查询优化器根据该SQL所涉及到的数据表的统计信息进行计算，生成对应的执行计划
- 4.Mysql根据优化器生成的执行计划，调用存储引擎的API来进行查询
- 5.将查询结果返回给客户端
SQL执行的最大瓶颈在于磁盘的IO，即数据的读取；不同SQL的写法，会造成不同的执行计划的执行，而不同的执行计划在IO的上面临完全不一样的数量级，从而造成性能的差距；
根据上面的流程，所以说，Mysql的优化，其实就是让查询优化器根据程序员的计划来选择匹配的执行计划，来减少查询过程中的IO次数
6.表结构(schema 也叫做模式) 设计对系统性能的影响
（1）：冗余数据的处理
关系数据库的三范式:
第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库，是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值；
第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。
第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。 (不允许有冗余数据)
（2)：大表拆小表，有大数据的列单独拆成小表
1,在一个数据库中,一般不会设计属性过多的表;
2,在一个数据库中,一般不会有超过500/1000万数据的表(拆表,按照逻辑拆分,按照业务拆分);
3,有大数据的列单独拆成小表(富文本编辑器,CKeditor);
（3）：根据需求的展示设置更合理的表结构
（4）：把常用属性分离成小表
1，减少查询常用属性需要查询的列;
2，便于常用属性的集中缓存;
7.两种典型数据库的应用场景
1.联机事务处理OLTP（on-line transaction processing）：OLTP是传统的关系型数据库的主要应用，主要就是基本的，日常的事务处理，例如银行交易
特点：
- 1：系统总体数量较大，但活动数据量较小
- 2：IO访问频繁，但设计数据量较小，分布离散
- 3：并发很高
- 4：网络交互数据量较小，但交互频繁
系统架构选型：
- 1：大量的合理cache设计，能够大大减少数据库交互；应尽量扩大内存容量
- 2：IOPS（IO频率）指标要求高
- 3：CPU计算能力，并发计算能力要求较高
- 4：对网络要求较高
2：联机分析处理OLAP（on-line Analytical processiong）：OLAP是数据仓库系统的主要应用，支持复杂的分析操作，例如决策支持，并且提供直观易懂的查询结果，数据仓库就是一个典型应用场景，还有天气预报，地震预报等
特点：
- 1：数据量大，数据访问集中，数据活跃度集中
- 2：并发访问低
- 3：每次检索的数据量非常多
系统架构选型：
- 1：硬盘存储容量较大
- 2：对存储设备的IO吞吐量要求较高
- 3：CPU要求较低
- 4：对网络要求不高
SQL优化
这里首先 要讲一下索引的知识点，关于索引可看南国之前的博客MySQL高频知识盘点
索引在数据库中比较重要，属于面试常考的知识点。
索引简单来说 就是把无序的数据变成有序的查询。
索引的物理结构
1,数据库文件存储的位置:my.ini配置文件中dataDir对应的数据目录中;
2,每一个数据库一个文件夹;
1,MYISAM引擎:每一个表(table_name)-->
table_name.MYI:存放的是数据表对应的索引信息和索引内容;
table_name.FRM:存放的是数据表的结构信息;
table_name.MYD:存放的是数据表的内容;
2,InnoDB引擎:每一个表(table_name)-->
table_name.frm:存放的是数据表的结构信息;
数据文件和索引文件都是统一存放在ibdata文件中;
3,索引文件都是额外存在的,对索引的查询和维护都是需要消耗IO的;
索引的结构
1,默认情况下,一旦创建了一个表,这个表设置了主键,那么MYSQL会自动的为这个主键创建一个unique的索引;
2,索引类型:
1,Normal:普通的索引;允许一个索引值后面关联多个行值;
2,UNIQUE:唯一索引;允许一个索引值后面只能有一个行值;之前对列添加唯一约束其实就是为这列添加了一个unique索引;当我们为一个表添加一个主键的时候,其实就是为这个表主键列(设置了非空约束),并为主键列添加了一个唯一索引;
3,Fulltext:全文检索,mysql的全文检索只能用myisam引擎,并且性能较低,不建议使用;
3,索引的方法(规定索引的存储结构): (数据结构,算法基础)
1,b-tree:是一棵树（最好了解下二叉树,平衡二叉树,平衡树(B-TREE)之间的不同和知识点，这些都是比较基础的了)
使用平衡树实现索引，是mysql中使用最多的索引类型；在innodb中，存在两种索引类型，第一种是主键索引（primary key），在索引内容中直接保存数据的地址；第二种是其他索引，在索引内容中保存的是指向主键索引的引用；所以在使用innodb的时候，要尽量的使用主键索引，速度非常快；
b-tree中保存的数据都是按照一定顺序保存的数据,是可以允许在范围之内进行查询;
select * from accountflow where account_id <100;
2,hash:把索引的值做hash运算，并存放到hash表中，使用较少，一般是memory引擎使用；优点:因为使用hash表存储，按照常理，hash的性能比B-TREE效率高很多。
hash索引的缺点：
1，hash索引只能适用于精确的值比较，=，in，或者<>；无法使用范围查询;
2，无法使用索引排序；
3，组合hash索引无法使用部分索引；
4，如果大量索引hash值相同，性能较低
4,索引的问题?
索引需要额外的维护成本；因为索引文件是单独存在的文件,对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效 率;
5,怎么创建索引?
(1)，较频繁的作为查询条件的字段应该创建索引；
(2)，唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
作为索引的列,如果不能有效的区分数据,那么这个列就不适合作为索引列;比如(性别,状态不多的状态列)
举例:SELECT sum(amount) FROM accountflow WHERE accountType = 0;
假如把accountType作为索引列,因为accountType只有14种,所以,如果根据accountType来创建索引,最多只能按照1/14的比例过滤掉数据;但是,如果可能出现,只按照该条件查询,那我们就要考虑到其他的提升性能的方式了;
第一种方案:单独创建一个系统摘要表;在这个表里面有一个列叫做系统总充值金额;每次充值成功,增加这个列的值;以后要查询系统总充值金额,只需要从这个系统摘要表中查询;(缺陷:如果充值频率过快,会导致表的锁定问题;)
第二种方案:流水一旦发生了,是不会随着时间改变的;针对这种信息,我们就可以使用增量查询(结算+增量查询);
1,创建一张日充值表;记录每一天的充值总金额(beginDate,endDate,totalAmount),每天使用定时器对当前的充值记录进行结算;日充值报表里面记录只能记录截止昨天的数据;
2,创建一张月充值表;记录每一个月的充值总金额(beginDate,endDate,totalAmount),每月最后一天使用定时器对当月的充值记录进行结算(数据源从日充值报表来);
3,要查询系统总充值,从月报表中汇总(当前月之前的总充值金额),再从日充值报表中查询当天之前的日报表数据汇总;再从流水中查询当前截止查询时间的流水;使用另外一张当天流水表记录当天的流水;再把三个数据累加;
(3)，更新非常频繁的字段不适合创建索引；原因,索引有维护成本;
(4)，不会出现在WHERE 子句中的字段不该创建索引；
(5), 索引不是越多越好;(只为必要的列创建索引)
1,不管你有多少个索引,一次查询至多采用一个索引;(索引和索引之间是独立的)-------------特别要记住这个，就是因为这个才会引申出复合索引的内容
2,因为索引和索引之间是独立的,所以说每一个索引都应该是单独维护的;数据的增/改/删,会导致所有的索引都要单独维护;
6.索引的使用限制
-
1,BLOB 和TEXT 类型的列只能创建前缀索引
-
2,MySQL 目前不支持函数索引(在MYSQL中,索引只能是一个列的原始值,不能把列通过计算的值作为索引);
实例:请查询1981年入职的员工:
SELECT * FROM emp WHERE year(hire_date)=‘1981’;
问题:查询的列是在过滤之前经过了函数运算;所以,就算hire_date作为索引,year(hire_date)也不会使用索引;
解决方案:
1,SELECT * FROM emp WHERE hire_date BETWEEN ‘1981-01-01’ AND ‘1981-12-31’;
2,在创建一列,这列的值是year(hire_date),然后把这列的值作为索引; -
3,使用不等于（!= 或者<>）的时候MySQL 无法使用索引
-
4,过滤字段使用了函数运算后（如abs(column)），MySQL 无法使用索引
-
5, Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引
-
6,使用LIKE 操作的时候如果条件以通配符开始（ ‘%abc…’）MySQL 无法使用索引
1,字符串是可以用来作为索引的;
2,字符串创建的索引按照字母顺序排序;
3,如果使用LIKE,实例:SELECT * FROM userinfo WHERE realName LIKE ‘吴%’;这种情况是可以使用索引的;
但是LIKE ‘_嘉’ 或者LIKE '%嘉’都是不能使用索引的; -
7,使用非等值查询的时候MySQL 无法使用Hash 索引
单列索引和复合索引
（1）,因为一个查询一次至多只能使用一个索引,所以,如果都使用单值索引(一个列一个索引),在数据量较大的情况下,不能很好的区分数据;
（2）,所以,MYSQL引入了多值索引(复合索引);
复合索引就是由多列的值组成的索引;并且(注意),多列的索引是有顺序的!!!
（3）,复合索引的原理:就是类似orderby(orderby后面可以跟多个排序条件order by hire_date,username desc);
就是在排序和分组(创建倒排表的时候),按照多个列进行排序和合并;（下面这个例子，假设建立的索引是actionTime+account_id）
SELECT * FROM accountflow WHERE actionTime < ‘xxxxx’ AND account_id = 5 可以使用actionTime+account_id的复合索引;
SELECT * FROM accountflow WHERE actionTime < ‘xxxxx’ 可以使用actionTime+account_id的复合索引;
SELECT * FROM accountflow WHERE account_id = 5 不可以使用actionTime+account_id的复合索引;
SELECT * FROM accountflow WHERE account_id = 5 AND actionTime < ‘xxxxx’ 不可以使用actionTime+account_id的复合索引;
（4）,复合索引,在查询的时候,遵守向左原则;只要在查询的时候,是按照复合索引从左到右的顺序依次查询,不管查询条件是否完全满足所有的符合索引的列,都可以使用部分的符合索引;
打个比方：比如有索引A，B，C，D
1：如果现在出现where后面是跟着A and B 的条件，那么可以使用上面的索引
2：如果现在出现where后面是跟着A and C 的条件，那么也可以使用上面的索引-----------因为向左原则，则可以只根据A进行索引，所以这种情况是要特别进行注意的。
3：如果现在出现where后面是跟着B and C 的条件，那么就不可以使用上面的索引
（5）,在实际应用中,基本上都使用复合索引;
Join连接优化
1.Join的原理
在 MySQL中 使用Nested Loop Join来实现join;
A JOIN B：通过A表的结果集作为循环基础，一条一条的通过结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果；
2.Join的优化原则
- 1.尽可能减少Join语句中的Nested Loop的循环总次数，用小结果集驱动大结果集；
注意：结果集，不是说用表的数据大小来进行衡量的。所以，当我们进行join连接的时候，最好能够让join前面的数据集是小的，而后面的结果集是大的，这样才能提高效率。 - 2.优化Nested Loop的内层循环
也就是说，当内层循环比较复杂的时候，那么就要运用其他的方法来对内层进行优化。 - 3.保证Join语句中被驱动表上join条件字段已经被索引
- 4.扩大join buffer的大小； 因为是联表查询，俺们中间集是放在内存的缓存的，所以这个大一点是由优势的。
SQL优化原则
[原则一:选择需要优化的SQL]
1,选择需要优化的SQL:不是所有的SQL都需要优化,在优化的过程中,首选更需要优化的SQL;
怎么选择?优先选择优化高并发低消耗的SQL；
1，1小时请求1W次，1次10个IO；
2，1小时请求10次，1次1W个IO；
考虑:
1,从单位时间产生的IO总数来说,相同的;
2,针对一个SQL,如果我能把10个IO变成7个IO,一小时减少3W个IO;
针对第二个SQL,如果能把1W个IO变成7K个IO,一小时减少3W个IO;
3,从优化难度上讲,1W->7K难的多;
4,从整体性能上来说,第一个SQL的优化能够极大的提升系统整体的性能;第二个SQL慢一点,无非也就是10个连接查询慢一点;
2，定位性能瓶颈；
1，SQL运行较慢有两个影响原因，IO和CPU，明确性能瓶颈所在；
2，明确优化目标；
[原则二:从Explain和Profile入手]
explain关键字用来分析MySQL的执行计划，profile关键字用来分析MySQL执行明细状态。
1，任何SQL的优化，都从Explain语句开始；Explain语句能够得到数据库执行该SQL选择的执行计划；
2，首先明确需要的执行计划，再使用Explain检查；
3，使用profile明确SQL的问题和优化的结果；
[原则三:永远用小结果集驱动大的结果集]
[原则四:在索引中完成排序]
[原则五:使用最小Columns]
1，减少网络传输数据量；
2，特别是需要使用column排序的时候.为什么?MYSQL排序原理，是把所有的column数据全部取出，在排序缓存区排序，再返回结果；如果column数据量大，排序区容量不够的时候，就会使用先column排序，再取数据，再返回的多次请求方式；
[原则六:使用最有效的过滤条件]
1，过多的WHERE条件不一定能够提高访问性能；
2，一定要让where条件使用自己预期的执行计划；
[原则七:避免复杂的JOIN和子查询]
1，复杂的JOIN和子查询，需要锁定过多的资源，MYSQL在大量并发情况下处理锁定性能下降较快；
2，不要过多依赖SQL的功能，把复杂的SQL拆分为简单的SQL；
3，MySQL子查询性能较低，应尽量避免使用；
参考资料：
https://blog.csdn.net/cs_hnu_scw/article/category/7322319
534
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
