mysql数据库优化

mysql数据库优化

1、数据库优化的目的：

避免出现页面访问错误：

• 由于数据库连接timeout产生页面5xx错误
• 由于慢查询造成页面无法加载
• 由于阻塞造成数据无法提交

增加数据库的稳定性：

• 很多数据库问题都是由于低效率的查询引起的

优化用户体验：

• 流程页面的访问速度
• 良好的网站功能体验

2、可以从几方面进行数据库优化

 3、SQL及索引优化

如何发现有问题的SQL？

使用MySQL慢查询日志对有效率问题的SQL进行监控

1、首先：show VARIABLES LIKE "slow_query_log";　　查看服务器是否开启慢查询日志，如果：slow_query_log为off，就代表没有开启慢查询日志。

2、在开启慢查询日志之前，要通过命令show VARIABLES LIKE "%log%";      查看是否没有记录的索引记录在慢查询日志中，如果log_queries_not_using_indexes为off表示没有开启，通过命令SET GLOBAL log_queries_not_using_indexes=on;可以设置开启

3、设置慢查询日志记录的时间，通过命令SHOW VARIABLES LIKE "long_query_time";   查询时间为大于10秒的查询记录在慢查询日志中。可以通过SET GLOBAL long_query_time=1;来修改时间。

4、最后，如果上面设置都设置好了，就可以SET GLOBAL slow_query_log=on;来开启慢查询日志的记录了。

5、通过命令：SHOW VARIABLES LIKE "slow%";  查看慢日志存放的位置，如图：

 4、慢查日志的存储格式

SQL执行信息：

Query_time:查询执行时间　　　　lock_time:查询锁定时间 　　　　Rows_sent:查询发送的行数　　　　Rows_examined:查询扫描的行数

SQL执行时间：

SET timestamp：以时间戳形式的记录的执行时间。

 5、SQL及索引优化

如何分析SQL查询（数据库基于MySQL5.5版本，不同MySQL版本的优化器有一定的差别）

6、慢查日志的分析工具

　　1、mysqldumpslow工具

 　   2、pt-query-digest 工具

 

7、如何通过慢查日志发现有问题的SQL？

8、如何分析SQL查询

　　　　1、使用explan查询SQL的执行计划

　1、explain返回各列的含义，如下：

     首先：type中：

　　const：一般指常数查找，一般对于主键或者唯一索引查找。

　　eq_reg：一种范围查找，一般对于主键或者唯一索引范围查找。

　　ref ：常见于连接的查询中，一个表是基于索引的查找。

　　range：基于索引的范围查找

　　index ：对于索引的扫描，来进行的查找

　　ALL：对于表的扫描，来进行的查找

 

　　其次、extra列需要注意的返回值，如下：