MySQL数据库优化

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle旗下产品。

MySQL版本
2001年3.23
2003年4.0
2005年4.1
2006年5.0
2008年5.1
2010年5.5
2012年5.6
2015年5.7
2018年8.0

MySQL的存储引擎
1.InnoDB存储引擎
InnoDB是MySQL5.5开始默认的存储引擎，被设计用来处理大量的短期事物；有自动崩溃恢复的特性。
2.MyISAM存储引擎
在MySQL5.5，MyISAM是默认的存储引擎。MyISAM提供了全文检索、压缩、空间函数等特性，但MyISAM不支持事物和行级锁，而且崩溃后无法安全恢复。

隔离级别（MySQL默认是可重复读）
1.读未提交
事务中的修改，即使没有提交，对其他事务也都是可见的。会导致脏读、不可重复读、幻读
2.读已提交
会导致不可重复读、幻读
3.可重复读
会导致幻读，InnoDB通过多版本并发控制（MVCC）解决幻读问题
4.串行化
避免了脏读、可重复读、幻读

InnoDB逻辑存储结构
在InnoDB存储引擎中，所有数据都被逻辑地存放在表空间(tablespace)里面。表空间又由段(segment)、区(extent)、页(page)组成。
表空间：InnoDB存储引擎逻辑结构的最高层，默认为ibdata1。
段：常见的段有数据段，索引段，回滚段等。
区：每64个连续的页组成区，因此区大小正好为1M。
页：页是InnoDB存储引擎中最小的磁盘单位，默认大小为16K。

常见的页类型有
数据页(B-tree Node)
Undo页(Undo Log Page)
系统页(System Page)
事务数据页(Transaction system Page)
插入缓冲位图页(Insert Buffer Bitmap)
插入缓冲空闲列表页(Insert Buffer Free List)
未压缩的二进制大对象页(Uncompressed BLOB Page)
压缩的二进制大对象页(Compressed BLOB Page)

InnoDB数据页组成部分
File Header(文件头)
Page Header(页头)
Infimun + Supremum Records
User Records(用户记录，即行记录)
Free Space(空闲空间)
Page Directory(页目录)
File Trailer(文件结尾信息)

索引
索引按数据结构划分(show index from 表名;可通过该语句查询索引类型)
1.B+树索引
B+树是一个平衡多叉查找树，左右子树的高度之差不超过1。B+树对索引列进行排序存储，因此很适合查找范围数据。

2.哈希索引
哈希索引把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置。但是哈希索引不支持范围查询、不支持索引排序、不支持联合索引的最左匹配规则。

3.R-Tree(空间数据索引)
R-Tree用作地理数据存储，必须使用MySQL的GIS相关函数来维护。很少使用。

4.全文索引
MySQL5.6开始，InnoDB也支持全文索引。全文索引是一种特殊类型的索引，它查找的是文本中的关键字，而不是直接比较索引中的值。全文索引更类似于搜索引擎做的事情，而不是简单的where条件匹配。

索引按表现形式划分
主键、唯一、全文、普通、组合
alter table a add primary key(column);
create unique index index_a on a(column);
create fulltext index index_a on a(column);
create index index_a on a(column);
create index index_a on a(column,column);

MySQL数据库优化
一.SQL语句优化
1.1避免使用*号，需要什么列就返回什么列。
1.2where和order by涉及的列上考虑建立索引。
1.3尽量避免在where子句中对列进行null值判断。
1.4尽量避免在where子句中使用!=或<>操作符。
1.5尽量避免在where子句中使用or来连接条件。
1.6尽量避免在where子句中对列进行函数操作。
1.7避免使用in和not in，否则会导致全表扫描，对于连续的数值，可用between代替in。
1.8使用like模糊查询时，“%”放在前面会导致索引失效。
1.9尽量用exists代替in。
1.10尽量使用“>=”，不要使用“>”。
1.11使用union all替代union。union会排除重复记录。

二.索引优化
索引类型:主键、唯一、全文、普通、组合
alter table a add primary key(column);
create unique index index_a on a(column);
create fulltext index index_a on a(column);
create index index_a on a(column);
create index index_a on a(column,column);

索引使用场合
1.频繁作为查询条件的列应该创建索引。
2.频繁更新的列不适合创建索引。
3.唯一性太差的列不适合创建索引。

哪些情况会使用索引？
1.表中有复合索引的时，命中了最左边的列，就会使用索引。
2.模糊查询时，当左边有%时不会使用索引。
3.如果查询条件有or，其中一个条件没有索引，都不使用索引。
4.如果是字符串类型一定要使用‘’ ，否则不使用索引。

三.读写分离
读写分离解决方案：应用层解决和中间件解决

应用层
优点：
多数据源切换方便，由程序自动完成。
不需要引入中间件。
理论上支持任何数据库。
缺点：
由程序员完成，运维参与不到。
不能做到动态增加数据源。

中间件
优点：
源程序不需要做任何改动就可以实现读写分离。
动态添加数据源不需要重启程序。
缺点：
程序依赖于中间件，会导致切换数据库变得困难。
由中间件做了中转代理，性能有所下降。

四.垂直切分
把数据按模块划分到不同数据库表中，如果一个模块的数据量太大就会存在性能瓶颈。

五.水平切分
同一个模块下，把数据按某种规则划分到不同的数据库表中。垂直切分可以使模块的划分更清晰，分成功能不同的表；水平切分可以解决大数据下大表性能的瓶颈问题。