首页 数据库

mysql数据库简单优化

发布时间:2015年06月03日 评论数：抢沙发 阅读数：4993

sql语句优化

1、优化insert语句
一次插入多值：insert into table_name values(1、2)，(1、2)，(1、2)….
使用文本文件装载一个表的数据:LOAD DATA INFILE ‘C:/Users/907379/Desktop/table.txt’ into TABLE asset character set utf8;（导出时采用文本文件）

2、优化group by语句
在使用group by时，后面加上order by null可以禁止结果排序，减少查询时间
可以使用 EXPLAIN SELECT * FROM `t_asset` GROUP BY LANGUAGE_CODE ORDER BY NULL;查看到Extra中不包含filesort

3、优化order by语句
对于order by后面接多个需要排序的字段，所有字段尽量都按相同的顺序排序，都是升序或都是降序

4、优化嵌套语句
如从table1查找table2中不存在的数据，一般的话我们会这样写：select * from table1 where tid not in(select tid from table2)
其实，这时可以用连接（join）来优化子查询：select * from table1 a left join table2 b on a.tid = b.tid where b.tid is null;

5、优化or条件，最好使用索引，并且每个or条件都必须用到索引，如果没有的话，可以考虑添加索引

6、使用sql提示
生成一个临时的结果集，减少表锁时间：select sql_buffer_results * from table1
使用use index希望mysql参考索引列表：select * from table1 use index (ind_id) where id=1
使用ignore index使mysql忽略一个或多个索引：select * from table1 ignore index (ind_id) where id=1
使用force index使mysql强制使用特定的索引,比如id>0默认mysql是扫描全盘：select * from table1 force index (ind_id) where id>0

表优化

1、优化表的数据类型
使用procedure analyse() 确认要优化的列：select * from table1 procedure analyse();结果中Optimal_fieldtype就是给出的优化建议
然后通过alter table table1 modify id INT(9) UNSIGNED NOT NULL (Optimal_fieldtype给出的建议)

2、通过拆分提高表的访问效率
a、垂直拆分：把主码和常用的列放到一个表，把主码和不常用的一些列放到一个表（不过会出现冗余列）
b、水平拆分：把一列或者多列的数据行放到两个独立的表中（比如近3个月的数据放到一个表，历史数据放到另外的表），不过会增加程序的复杂度，一般情况不建议使用

3、逆规范化
比如增加冗余列，增加派生列，重新组表，拆分表等提高性能，但需要通过批处理维护，应用逻辑和触发器（最好用的方法）来维护数据的完整性

4、使用中间表提高统计查询速度
使用中间复制源表部分数据，与源表相隔离，对源表不会有影响；中间表可以灵活增加临时用的字段，提高效率