MySQL 优化问题

1.执行流程：

• 查询缓存
• 解析器生成解析树
• 预处理再次生成解析树
• 查询优化器
• 查询执行计划
• 查询执行引擎
• 查询数据返回结果

2.表结构对性能的影响：

1. 冗余数据的处理（可以提高系统的整体查询性能<三范式>）
   1. 每一列只能有一个值
   2. 每一行可以被唯一的区分
   3. 不包含其他表的已包含的非关键信息
2. 大表拆小表
   1. 一般不会设计属性过多的表
   2. 一般不会超过500到1000万数据的表
   3. 有大数据的列单独拆为小表
3. 根据需求展示更加合理的表结构
4. 常用属性分离为小表

3.sql优化原则：
1、选择需要优化的SQL
2、Explain和Profile入手
3、使用profile明确SQL的问题和优化的结果；
3、永远用小结果集驱动大的结果集
4、在索引中完成排序
5、使用最小Columns
6、使用最有效的过滤条件
7、避免复杂的JOIN和子查询

4.JOIN的原理：

原理:

• 在mysql中使用Nested Loop Join来实现join；
• A JOIN B：通过A表的结果集作为循环基础，一条一条的通过结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果；

JOIN的优化原则:

• 尽可能减少Join 语句中的Nested Loop 的循环总次数，用小结果集驱动大结果集；
• 优先优化Nested Loop 的内层循环；
• 保证Join 语句中被驱动表上Join 条件字段已经被索引；
• 扩大join buffer的大小；

 

5.执行计划与执行明细：

• Explain:可以让我们查看MYSQL执行一条SQL所选择的执行计划；
• Profiling:可以用来准确定位一条SQL的性能瓶颈；

6.索引:

1、类型

• Normal:普通的索引;允许一个索引值后面关联多个行值;
• UNIQUE:唯一索引;允许一个索引值后面只能有一个行值;之前对列添加唯一约束其实就是为这列添加了一个unique索引;当我们为一个表添加一个主键的时候,其实就是为这个表主键列(设置了非空约束),并为主键列添加了一个唯一索引;
• Fulltext:全文检索,mysql的全文检索只能用myisam引擎,并且性能较低,不建议使用;

2、方法
1,b-tree:是一颗树(二叉树,平衡二叉树,平衡树(B-TREE))
使用平衡树实现索引，是mysql中使用最多的索引类型；在innodb中，存在两种索引类型，第一种是主键索引（primary key），在索引内容中直接保存数据的地址；第二种是其他索引，在索引内容中保存的是指向主键索引的引用；所以在使用innodb的时候，要尽量的使用主键索引，速度非常快；
2,hash:把索引的值做hash运算，并存放到hash表中，使用较少，一般是memory引擎使用；优点:因为使用hash表存储，按照常理，hash的性能比B-TREE效率高很多。
hash索引的缺点：
1，hash索引只能适用于精确的值比较，=，in，或者<>；无法使用范围查询;
2，无法使用索引排序；
3，组合hash索引无法使用部分索引；
4，如果大量索引hash值相同，性能较低；
3、创建

• 较频繁的作为查询条件的字段应该创建索引；
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
• 作为索引的列,如果不能有效的区分数据,那么这个列就不适合作为索引列;比如(性别,状态不多的状态列)举例:SELECT sum(amount) FROM accountflow WHERE accountType = 0;假如把accountType作为索引列,因为accountType只有14种,所以,如果根据accountType来创建索引,最多只能按照1/14的比例过滤掉数据;但是,如果可能出现,只按照该条件查询,那我们就要考虑到其他的提升性能的方式了;
• 更新非常频繁的字段不适合创建索引；原因,索引有维护成本;

4，不会出现在WHERE 子句中的字段不该创建索引；

5, 索引不是越多越好;(只为必要的列创建索引)

• 不管你有多少个索引,一次查询至多采用一个索引;(索引和索引之间是独立的)
• 因为索引和索引之间是独立的,所以说每一个索引都应该是单独维护的;数据的增/改/删,会导致所有的索引都要单独维护;