1、申 请 I D ：Mrsakuras丶2、个人邮箱：liyisheyi@126.com3、原创技术文章： MySQL性能的优化，对于使用Mysql数据库的系统来说，是非常重要的，因为数据是存放在数据库中的，系统要能够快速的获取到数据，并与数据库发生交互，那么数据库的优化也是必不可少的。自己在开发过程中，对于项目的设计的时候，虽然自己主要做的是后台的开发，但是对于数据库的设计，也是需要了解的，所以，自己就根据自己在开发过程中，将一些Mysql方面的知识进行一下描述，来方便更多的人一起学习，如果有什么不妥之处，欢迎大家进行指正~~~~~~~~~~~ 主要从下面这个思维导图来进行分析，并且也是面试经常爱问的知识点：
知识点一：影响性能的因素一：数据库优化的方面1. 数据库(表)设计合理我们的表设计要符合3NF 3范式(规范的模式) ,有时我们需要适当的逆范式2. sql语句的优化(索引，常用小技巧.)3. 数据的配置(缓存设大)4. 适当硬件配置和操作系统 (读写分离.)二：哪些数据不适合写到数据库中1：二进制文件（图片，声音）2：流水队列数据3：超大文本因为这些都会降低效率，主要就是增加了IO次数，所以，对于系统中的操作日志的话，一般都是按照公司规定的格式写到文件当中，或者使用高吞吐量的数据库，比如redis等。三：合理的cache下来数据适合放入到cache中：1：系统配置信息2：活跃的用户基本信息（一般会放到session中）3：活跃的用户的制化信息4：基于时间段的统计数据5：读远远大于写的数据四：减少与数据库的交互这里说一个非常经典的问题，在面试数据库的时候肯定是必问，而且对于Hibernate和Mybatis中，都存在这样的问题。问题：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优化的第一原则（非常重要）原则：使Mysql中的查询优化器能够选择出程序员所预期的结果方向进行sql的查询执行计划。下面来说一下Mysql的执行流程的一个过程，
执行过程：（1）首先客户端发送一条查询给服务器（2）服务器通过权限检查后会先到查询缓存中去查找是否存在相应的内容，如果命中，那么立即从查询缓存中返回结果。否则进入下一步。（命中查询缓存的几率是很小的）（3）服务器进行SQL解析，预处理，再由查询优化器根据该SQL所涉及到的数据表的统计信息进行计算，生成对应的执行计划（4）Mysql根据优化器生成的执行计划，调用存储引擎的API来进行查询（5）将查询结果返回给客户端SQL执行的最大瓶颈在于磁盘的IO，即数据的读取；不同SQL的写法，会造成不同的执行计划的执行，而不同的执行计划在IO的上面临完全不一样的数量级，从而造成性能的差距；
根据上面的流程，所以说，Mysql的优化，其实就是让查询优化器根据程序员的计划来选择匹配的执行计划，来减少查询过程中的IO次数六“：Mysql的存储引擎（1）：MyISAM：MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。MyISAM主要特性有：
[java] view plain copy- 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列可以多达64KB
使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）（2）：lnnoDB：InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。InnoDB主要特性有：
[java] view plain copy- 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大小的日志文件
（3）：memory：MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。MEMORY主要特性有：
[java] view plain copy- 1、MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度
- 2、MEMORY存储引擎执行HASH和BTREE缩影
- 3、可以在一个MEMORY表中有非唯一键值
- 4、MEMORY表使用一个固定的记录长度格式
- 5、MEMORY不支持BLOB或TEXT列
- 6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引
- 7、MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）
- 8、MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享
- 9、当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）
存储引擎的选择
如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive
使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能
七：表结构（schema）设计对系统性能的影响（1）：冗余数据的处理关系数据库的三范式:
第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库，是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值；
第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。
第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。 (不允许有冗余数据)
（2)：大表拆小表，有大数据的列单独拆成小表 1,在一个数据库中,一般不会设计属性过多的表;
2,在一个数据库中,一般不会有超过500/1000万数据的表(拆表,按照逻辑拆分,按照业务拆分);
3,有大数据的列单独拆成小表(富文本编辑器,CKeditor);
（3）：根据需求的展示设置更合理的表结构（4）：把常用属性分离成小表 1，减少查询常用属性需要查询的列;
2，便于常用属性的集中缓存;
八：两种典型数据库的应用场景（1）：联机事务处理OLTP（on-line transaction processing）：OLTP是传统的关系型数据库的主要应用，主要就是基本的，日常的事务处理，例如银行交易特点：
[java] view plain copy- 1：系统总体数量较大，但活动数据量较小
- 2：IO访问频繁，但设计数据量较小，分布离散
- 3：并发很高
- 4：网络交互数据量较小，但交互频繁
系统架构选型：
[java] view plain copy- 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优化 一：合理使用索引首先讲一下关于索引的内容、、、、、、、、、、、、1,索引的原理:把无序的数据变成有序的查询;
索引的基本知识:（重点的重点啊。。。面试必问）
（1）,索引的物理结构:
1,数据库文件存储的位置:my.ini配置文件中dataDir对应的数据目录中;
2,每一个数据库一个文件夹;
[java] view plain copy- 1,MYISAM引擎:每一个表(table_name)-->
- table_name.MYI:存放的是数据表对应的索引信息和索引内容;
- table_name.FRM:存放的是数据表的结构信息;
- table_name.MYD:存放的是数据表的内容;
- 2,InnoDB引擎:每一个表(table_name)-->
- table_name.frm:存放的是数据表的结构信息;
- 数据文件和索引文件都是统一存放在ibdata文件中;
- 3,索引文件都是额外存在的,对索引的查询和维护都是需要消耗IO的;
（2）,索引的结构:
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效率高很多。
[java] view plain copy- 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，D1：如果现在出现where后面是跟着A and B 的条件，那么可以使用上面的索引2：如果现在出现where后面是跟着A and C 的条件，那么也可以使用上面的索引-----------因为向左原则，则可以只根据A进行索引，所以这种情况是要特别进行注意的。3：如果现在出现where后面是跟着B and C 的条件，那么就不可以使用上面的索引
（5）,在实际应用中,基本上都使用复合索引;
OK，后续可能会新增补充更新~ |
吾爱游客
发表于 2017-12-24 01:16
发表于 2017-12-25 12:45
