MySQL数据库优化-CSDN博客

本文链接：https://blog.csdn.net/CodeAries/article/details/102953561

一、明确进行数据库优化的必要性

1.避免网站页面出现访问错误

由于数据库连接超时产生页面5xx错误
由于慢查询，导致数据库崩溃以至于页面无法加载
由于IO阻塞造成数据无法提交，产生各种事务与数据问题

2.增加数据库的稳定性

3.优化用户体验

为了给用户流畅的页面访问速度
给用户良好的网站功能体验。

二、数据优化主要入手方面

1.SQL以及索引优化[效果高而且成本低]

1.1SQL优化

如何发现有问题的SQL?

# 启动mysql
service mysql start
mysql ‐uroot ‐p
# 查看慢查询是否开启,并且查看日志存放位置
show variables like 'slow_query_log%';
# 如果是OFF,那么进行开启
set global log_queries_not_using_indexes=on;
#设置慢查询日志位置
set global slow_query_log_file=' /usr/share/mysql/sql_log/mysql-slow.log';
#大于1秒钟的数据记录到慢日志中，如果设置为默认0，则会有大量的信息存储在磁盘中，磁盘很容易满掉
set global long_query_time=2;

得到日志文件之后使用pt-query-digest进行分析

wget https://www.percona.com/downloads/percona-toolkit/2.2.16/RPM/percona-toolkit-2.2.16-1.noarch.rpm && yum localinstall -y  percona-toolkit-2.2.16-1.noarch.rpm
#查看是否安装成功
pt-query-digest --help
wget http://percona.com/get/pt-summary
#查看服务器信息
pt-summary
#查看磁盘开销使用信息
pt-diskstats
#查看mysql数据库信息
pt-mysql-summary --user=root --password=xx
#分析慢查询
pt-query-digest /data/mysql/data/db-3-12-slow.log
#分析死锁
pt-deadlock-logger --user=root --password=xx localhost
#从慢查询日志中分析索引使用情况
pt-index-usage slow_20131009.log
#分析重复索引
pt-duplicate-key-checker --host=localhost --user=root --password=123456

也可以使用explain查询SQL的执行计划

explain select * from table1;

具体优化实战案例:

对于max() 的优化:

explain select max(payment_date) from payment \G
--通过explain分析可以看出扫描了全表,效率不高
--所以我们可以加入索引

子查询的优化

#需求:查询t表中id在t1表中tid的所有数据；
#不推荐的做法
select * from t where t.id in (select t1.tid from t1);
#推荐使用join操作
#特别注意:为了防止数据重复，需要使用distinct关键词进行去重
select distinct id from t join t1 on t.id =t1.tid;

group by的优化

#优化之前,group by没有使用索引
select actor.first_name,actor.last_name,count(*)
	from sakila.film_actor
inner join sakila.actor using(actor_id)
group by film_actor.actor_id;
#优化之后
select actor.first_name,actor.last_name,c.cnt
	from sakila.actor inner join (
	select actor_id,count(*) as cnt from sakila.film_actor group by actor_id
)as c using(actor_id);

在用Join进行多表联合查询时，我们通常使用On来建立两个表的关系。其实还有一个更方便的关键字，那就是Using。如果两个表的关联字段名是一样的，就可以使用Using来建立关系，简洁明了。

limit进行分页的查询的优化

#order by的字段最好可以使用有索引的列或者主键进行
select film_id,description from sakila.film order by title limit 50,5;

但是随着我们翻页,IO操作会越来越大,所以还需要进行优化

#记录上次返回的主键， 在下次查询时使用主键过滤。
select film_id,description from sakila.film  where film_id >55 and film_id<=60 order by film_id limit 1,5;
#结论：扫描行数不变，执行计划是很固定，效率也是很固定的

主键要顺序排序并连续的，如果主键中间空缺了某一列，或者某几列，会出现列出数据不足5行的数据；如果不连续的情况，建立一个附加的列index_id列，保证这一列数据要自增的，并添加索引即可。

1.2索引优化

在where从句，group by从句，order by从句，on从句中的列添加索引
索引字段越小越好,比如对一个varchar(255)字段建立索引,如果通过前10个就可以确定一个数据,那么索引大小尽量小点
离散度大的列放到联合索引的前面.多用在关联表中.比如一个选课表,里面存在主键id,student_id和course_id,那么我们该用index(student_id,course_id)还是index(course_id,student_id)呢?推荐使用index(student_id,course_id),因为相对于课程数目来讲,学生数目离散程度更高,数量更多.
左侧最优原则.比如建立了一个index(column1,column2,column3),那么数据库会默认创建一个column1,column2,column3的索引,一个column1,column2的索引,一个colunm1的索引,总共3个.所以我们应该把查询次数最多的放在左边,在进行where条件语句编写的时候也要注意这个顺序.
数据量很少或者查询少,插入,修改多的数据不适合建立索引.
索引如果创建过多不仅不会增快查询速度反而会对查询造成不利影响
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like ‘%aaa%’ 不会使用索引而like 'aaa%'可以使用索引。