泛谈MYSQL数据库优化方向
从这个主题中,多数人第一反应是怎么优化,从哪优化。
个人观点可以考虑从以下四个方面进行着手进行操作。
-
设计层面优化
-
功能层面优化
-
架构层面优化
-
业务SQL优化
设计层面优化
简明扼要的说就是了解MYSQL这个项目他的基本结构,或者说大家可以了解一下MYSQL的存储引擎,在创建表的过程中DDL语句中可以选择指定一个存储引擎,常用的引擎有 InnoDB 、 myisam 、 Archive、 Memory 等。所有第一步选择合理的存储引擎是至关重要的。
功能层面优化
简单的讲就是MYSQL提供了索引这个功能,我们可以通过创建合适的索引以达到我们数据库优化的目的;
同样还有两个强大的功能:缓存,分区分表。合理的使用索引功能 合理的使用缓存,分区分表功能以达到功能层面的优化。
从索引的定义方式和用途中来看:主键索引,唯一索引,普通索引,全文索引。
普通索引,index:对关键字没有要求。
唯一索引,unique index:要求关键字不能重复。同时增加唯一约束。
主键索引,primary key:要求关键字不能重复,也不能为NULL。同时增加主键约束。
全文索引,fulltext key:关键字的来源不是所有字段的数据,而是从字段中提取的特别关键词。
架构层面优化
主从复制:
Mysql服务器内部支持复制功能,仅仅需要通过配置完成下面的拓扑结构。一主多从典型结果:主服务器负责写数据。从服务器负责读数据。复制功能mysql会自带。
读写分离,负载均衡:
服务端程序不再操作MYSQL数据库服务器,而是去操作读写分离、负载均衡服务器,只要服务器安装了mysql proxy或Ameoba软件就可以实现读写分离和负载均衡,读写分离是指该服务器会判断客户端的操作是读还是写,从而选择操作mysql主服务器还是从服务器。负载均衡算法是指,客户端读操作时,该服务器会根据取余算法去选择一台从服务器。
业务SQL优化
1.对于并发性的SQL
少用(不用)多表操作(子查询,联合查询),而是将复杂的SQL拆分多次执行。如果查询很原子(很小),会增加查询缓存的利用率。
2.大量数据的插入
多条 insert或者Load data into table(从文件里载入数据到表里)
建议,先关闭约束及索引,完成数据插入,再重新生成索引及约束。
针对于myisam,步骤:
Alter table table_name disable keys; 禁用索引约束
大量的插入
Alter table table_name enable keys; 启用
针对innodb,步骤:
Drop index, drop constraint 删除索引及约束,要保留主键
Begin transaction|set autocommit=0; 开启事务,不让他自动提交
[数据本身已经按照主键值排序]
大量的插入
Commit;
Add index, add constraint
Limit 的使用,会大大提升无效数据的检索(被跳过),因为是先检索,检索会检索全部,再取得想要的。好的做法是使用条件等过滤方式,将检索到的数据尽可能精确定位到需要的数据上。
4.随机选一些数据,不要使用Order by Rand()
等
5.可以开启慢sql查询