java开发之MySQL数据库性能优化 - 好文

一、MySQL实现优化
1)数据库设计要合理(遵循3F式)
2).添加索引()
索引分为：普通索引、主键索引、唯一索引、全文索引
3)分表分库技术(取模分表、水平分割、垂直分割)
4).读写分离
5).存储过程
6).配置最大连接数
7).服务器升级
8).随时清理碎片化
9).SQL语句调优
二、数据库设计
1).减少冗余量
2).遵循3F式
三、三范式
1F：原子约束每列不可再分
2F：保证唯一性
3F：不要冗余数据
四、分表分库
1).垂直切分
将一个项目拆分成多个小项目，每个小项目都有单独的数据库，好处是互不影响。
2)水平切分
通过取模算法，均匀分配
五、定位慢查询
1)什么是慢查询？
MySQL默认慢查询是10秒，如果10秒后还没响应回来这种现象称为慢查询
2)慢查询次数命令
show status like ‘slow_queries’;
六、索引

索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍

七、索引分类
1)主键索引
主键是一种唯一性索引，但它必须指定为“PRIMARY
KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定，例如“CREATE TABLE
tablename ( […], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE
tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。
2)唯一索引
索引列的所有值都只能出现一次，即必须唯一。
3)组合索引
4)全文索引
停止词, 因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词，称为停止词
5)普通索引

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。
八、索引实现原理
底层通过B叉树实现，首先创建索引文件，索引文件中每个值都有一个下标位置
九、索引会提交效率
索引是一个折半查找，首先会取一个中间数，然后左边为小，右边为大，如果查询的数据比它大就找右边，效率是2的N次方，索引缺点是增加、删除、索引文件也会更新
九、存储引擎
inndb
myisam
memory
十、inndb和myisam的区别
1)批量添加时myisam效率高
2)inndb事务机制安全
3)myisam是表锁
4)inndb是行锁
5)myisam支持全文检索
6)inndb不支持全文检索
7)都支持B叉树数据结构
8)支持索引缓存
十一、SQL优化技巧
① 使用group by 分组查询是，默认分组后，还会排序，可能会降低速度，
在group by 后面增加 order by null 就可以防止排序.
explain select * from emp group by deptno order by null;
② 有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。
select * from dept, emp where dept.deptno=emp.deptno; [简单处理方式]
select * from dept left join emp on dept.deptno=emp.deptno; [左外连接，更ok!]

③ 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
最好不要给数据库留 NULL，尽可能的使用 NOT NULL 填充数据库.
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用 NULL。
不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了，不管是否插入值（NULL 也包含在内），都是占用 100
个字符的空间的，如果是 varchar 这样的变长字段， null 不占用空间。
可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询：
select id from t where num = 0
十二、MySQL读写分离

在数据库集群架构中，让主库负责处理事务性查询，而从库只负责处理select查询，让两者分工明确达到提高数据库整体读写性能。当然，主数据库另外一个功能就是负责将事务性查询导致的数据变更同步到从库中，也就是写操作。
十三、读写分离的好处
1)分摊服务器压力，提高机器的系统处理效率

读写分离适用于读远比写的场景，如果有一台服务器，当select很多时，update和delete会被这些select访问中的数据堵塞，等待select结束，并发性能并不高，而主从只负责各自的写和读，极大程度的缓解X锁和S锁争用
2)增加冗余，提高服务可用性，当一台数据库服务器宕机后可以调整另外一台从库以最快速度恢复服务
十四、主从复制原理
依赖于二进制日志，binary-log. 二进制日志中记录引起数据库发生改变的语句
十五、Scale-up与Scale-out区别
Scale Out是指Application可以在水平方向上扩展。一般对数据中心的应用而言，Scale
out指的是当添加更多的机器时，应用仍然可以很好的利用这些机器的资源来提升自己的效率从而达到很好的扩展性。
Scale Up是指Application可以在垂直方向上扩展。一般对单台机器而言，Scale Up值得是当某个计算节点（机器）添加更多的CPU
Cores，存储设备，使用更大的内存时，应用可以很充分的利用这些资源来提升自己的效率从而达到很好的扩展性
十六、MyCat

是一个开源的分布式数据库系统，因为数据库一般都有自己的数据库引擎，而Mycat并没有属于自己的独有数据库引擎，所有严格意义上说并不能算是一个完整的数据库系统，是一个在应用和数据库之间起桥梁作用的中间件

热门工具换一换