如何优化mysql数据库配置_MYSQL数据库优化《详细版》

MYSQL数据库优化方向

sql及索引优化，存储优化(程序)

数据库表结构优化

系统配置

硬件

一、SQL语句优化

1、Mysql慢查日志的开启和日志存储格式

查看慢查询日志是否开启：show variables like 'slow_query_log'

查看没索引日志是否记录：show variables like '%log%'

列表项‘log_queries_not_using_indexes’,如果是OFF

开启非索引日志记录：set global log_queries_not_using_indexes=on

查询时间设置(超过多长时间的sql记录慢查询日志)：show variables like 'long_query_time';

这里测试设置为空0：set global long_query_time=0.0  (需要重新链接下mysql才回看到设置的值)

测试：随便查询几个表

找到慢查询日志存放目录：show varibels like '%log%';

| slow_query_log_file                     | /var/lib/mysql/ubuntu-slow.log

退出mysql,

sudo cat /var/lib/mysql/ubuntu-slow.log ，查看慢查日记记录内容

2、慢查询日志管理工具

mysqldumpslow

退出mysql,系统中:mysqldumpslow -h查看工具使用方法，比如：

sudo mysqldumpslow -t 5 /var/lib/mysql/ubuntu-slow.log

pt-query-digest功能比mysqldumpslow强的多

查看帮助命令：pt-query-digest --help

使用实例：sudo pt-query-digest /var/lib/mysql/ubuntu-slow.log

3、慢查询日志如何发现有问题的sql

rows examine扫描行数。

4、explain查询和分析sql执行计划，先计划在执行

using filesort 和using temporary一般是order by 和group by导致的。

5、max()优化

max()直接使用会扫描所有行，创建索引后不需要扫描：

创建索引：explain select max(payment_date) from payment \G;

命：:explain select max(payment_date) from payment \G;

6、子查询优化

优化成 join..on(链接方式，若果一对多，注意重复数据处理ditinct)

7、group by优化

sql:EXPLAIN SELECT actor.first_name,actor.last_name,COUNT(*) FROM film_actor INNER JOIN actor USING(actor_id) GROUP BY film_actor.actor_id;

操作了大量的io(filesort)

优化成子查询：

EXPLAIN SELECT actor.first_name,actor.last_name,c.cnt FROM actor INNER JOIN (SELECT film_actor.actor_id,COUNT(*) AS cnt FROM film_actor GROUP BY actor_id) AS c USING(actor_id);

group 多表链接尽量使用子查询。

8、limit优化

普通sql:EXPLAIN SELECT f.film_id,f.description,f.title FROM film f LIMIT 0,5;扫描全表，type是all

第一步优化：EXPLAIN SELECT f.film_id,f.description,f.title FROM film f ORDER BY f.film_id LIMIT 0,5;

通过一个ID排序，也是扫描全表但是，type是index(索引类型)

第二步优化：EXPLAIN SELECT f.film_id,f.description,f.title FROM film f WHERE film_id<=56 AND       film_id>50 ORDER BY f.film_id LIMIT 0,5;

通过记录上次id位置，避免扫描全表，注意适用于ID递增，没有空缺的表。

二、索引优化

1、选择合适的列建立索引

列的离散度，是指列的唯一性。离散度越大，唯一性越大。比如：SELECT COUNT(DISTINCT p.customer_id), COUNT(DISTINCT p.staff_id) FROM payment AS p，统计的customer_id,比staff_id大，说明，离散密度就大。

2、SQL优化索引

优化重复和冗余的索引。此工具可以显示重复和冗余的索引，以及解决办法。

3、索引维护(删除不用的索引)

三、数据化结构优化

1、选择合适的数据类型

2、范式化优化

比如删除表里面所有饮料的商品，结果饮料分类和描述也删除了。

3、反范式化优化

4、表垂直拆分

5、表的水平拆分

四、系统配置优化

1、数据库操作系统配置的优化

2、mysql常用配置参数

五、服务器硬件优化

1、CPU的选择

通常选择单核更快的CPU

2、磁盘的选择

RAID0,读写最好，RAIA1，数据不会丢失，安全性高。通常选择RAID1+0

大小: 104.2 KB

大小: 102.3 KB

大小: 146.8 KB

大小: 104.1 KB

大小: 132.1 KB

大小: 85.5 KB

大小: 90.2 KB

大小: 57 KB

大小: 94.3 KB

大小: 90.9 KB

大小: 97.3 KB

大小: 99 KB

大小: 174.2 KB

大小: 60.8 KB

大小: 134.5 KB

大小: 124.9 KB

大小: 90.6 KB

大小: 146.7 KB

大小: 49.1 KB

大小: 146.7 KB

大小: 49.1 KB

大小: 139 KB

大小: 132.2 KB

大小: 45.1 KB

大小: 75.3 KB

大小: 117.1 KB

大小: 67.2 KB

大小: 122.7 KB

大小: 139.2 KB

大小: 130.6 KB

大小: 31.7 KB

大小: 27.7 KB

大小: 46.6 KB

大小: 29.4 KB

大小: 39.3 KB

大小: 24.3 KB

大小: 80.3 KB

大小: 44.9 KB

大小: 166.3 KB

分享到：

2014-10-17 17:47

浏览 545

分类:数据库

评论