Java菜鸟面试突破系列之MySQL优化

Java菜鸟面试突破系列之MySQL优化

前言：数据库优化问题历来都是技术面面试官们屡问不爽的话题，每每碰到诸如此类问题，我们在事后往往对自己的回答会有一些不大满意，鉴于此，特在这个面试突破系列里总结一下数据库优化的方方面面，以与诸君共勉，有不当或者错误之处，烦请诸君不吝赐教！！！
总结计划：查询优化、索引的使用、存储优化、数据库结构优化、硬件优化、MySQL缓存、服务器参数。

下面将分别按照上述计划一一展开：

---

1、查询优化
在优化mysql时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，explain分析查询，通过定位分析性能的瓶颈。
1）慢查询
开启慢查询：log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=5
PS:log-slow-queries参数为慢查询日志存放的位置，一般这个目录要有mysql的运行账号的可写权限，一般都把这个目录设置为mysql的数据存放目录；long_query_time=5中的5表示查询超过5s才记录。

2）Explain
在MySQL中可以用Explain查看sql执行计划：EXPLAIN select * from products
id为标识符 就一个序列号 select_type为select语句的查询类型，比如有simple、primary（主查询）、union（连接查询）等类型

---

2、索引的使用
1）B-tree索引（用的比较多）
一般，mysql的B-tree索引的物理文件大多都是以二叉树的结构来存储的，也就是所有实际需要的数据都存放在树的叶子节点，而且到任何一个叶子结点的最短路径的长度都是完全相同的。
a、创建索引：
是否需要创建索引，几点原则：
较为频繁的作为查询条件的字段应该创建索引；
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
更新非常频繁的字段不适合创建索引；
不会出现在where子句中的字段不该创建索引。

好处：
索引能够极大提高数据检索效率，也能改善排序分组操作的性能！
但是索引是一个完全独立于基础数据之外的一部分数据，更新数据会带来的IO量和调整索引所致的计算量的资源消耗！

b、使用索引
可以使用联合索引查询（一个所以可以包含多个字段 使用联合索引不用考虑字段的唯一性）以及OR关键字查询。

2）Hash索引（用的不多）
Hash索引主要是Memory存储引擎使用，是其默认索引类型。它利用Hash算法，将需要索引的键值进行hash运算，然后将得到的hash值存入一个hash表中，然后每次需要检索的时候，都会将检索条件进行相同算法的hash运算，然后再和hash表中的hash值进行比较并得出相应的信息。
PS：hash索引仅仅只能满足 = IN <=>查询 不能使用范围查询
hash索引无法被利用来避免数据的排序操作，不能用部分索引键查询，在任何时候不能避免表扫描，遇到大量hash值相等的情况后性能不一定会比B-tree索引高。
3）Full-text索引
全文索引，目前在MyISAM存储引擎支持！目前仅有char、varchar、text三种数据类型的列可以建Full-text索引。

---

3、存储优化
存储数据时，影响存储速度的主要是索引、唯一性校验、一次存储的数据条数等。
在MySQL中常用的存储引擎主要有：MyISAM和InnoDB，两者的区别：MyISAM存储引擎（非事物性引擎）提供高速存储和检索以及全文搜索的能力，适合数据库查询比较频繁的应用（应用场景），不适合增删改查频繁的；InnoDB存储引擎是事物安全的（支持事物ACID），适合数据需要执行大量插入修改的应用（应用场景）！mysql现在默认的存储引擎是InnoDB！
PS：MyISAM是非事物安全型的，而InnoDB是事物安全型的；MyISAM锁的粒度是表级，而InnoDB支持行级锁定；MyISAM支持全文类型索引，而InnoDB不支持全文索引；MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM；InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事物表到事物表。
a）MyISAM优化
禁用索引：对于非空表，插入记录时，mysql会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入数据速度。为了解决这个问题，可以在批量插入数据之前禁用索引，数据插入完成后再开启索引！
禁用索引：alter table table_name disable keys
对于空表批量插入数据，则不需要进行索引禁用，因为MyISAM引擎的表在导入数据后才建立索引。
禁用唯一性检查：唯一性校验会降低插入记录速度，可以在插入记录之前禁用唯一性检查，插入数据之后再开启。
禁用：set unique_checks = 0;
开启：set unique_checks = 1;
批量插入数据！

b）InnoDB优化（提高速度）
禁用唯一性检查：同MyISAM
禁用外键检查： set foreign_key_checks=0;
开启外键检查：set foreign_key_checks=1;
禁止自动提交 set autocommit = 0；
开启自动提交 set autocommit = 1；

---

4、数据库结构优化
这个主要是可以有 表拆分（水平拆分和垂直拆分）、分区（range分区、list预定义列表分区、hash分区、key键值分区）、读写分离、数据库集群。

---

5、硬件优化
这里就不多说了！毕竟服务器的硬件性能直接决定数据库的性能和瓶颈的。也决定其运行速度和效率。具体表现在以下几个方面：
1）配置较大的内存
2）磁盘I/O相关
3）配置CPU相关