MySQL优化

MySQL优化

1. 为什么要进行数据库优化

避免网站页面出现访问错误
    由于数据库连接timeout产生页面5xx错误 , to many connection
    由于慢查询造成页面无法加载 : 由于数据库无法及时的返回数据, 导致页面一直无法返回
    由于阻塞造成数据无法提交 :
增加数据库的稳定性
    很多数据库问题都是由于低效的查询引起的
优化用户体验
    流畅页面的访问速度
    良好的网站功能体验

2. MySql数据库优化

可以从哪几个方面进行数据库的优化？如下图所示：

SQL及索引优化

根据需求写出良好的SQL，并创建有效的索引，实现某一种需求可以多种写法，这时候我们就要选择一种效率最高的写法。这个时候就要了解sql优化

数据库表结构优化

根据数据库的范式，设计表结构，表结构设计的好直接关系到写SQL语句。

系统配置优化

大多数运行在Linux机器上，如tcp连接数的限制、打开文件数的限制、安全性的限制，因此我们要对这些配置进行相应的优化。

硬件配置优化

选择适合数据库服务的cpu，更快的IO，更高的内存；cpu并不是越多越好，某些数据库版本有最大的限制，ＩＯ操作并不是减少阻塞。

注：通过上图可以看出，该金字塔中，优化的成本从下而上逐渐增高，而优化的效果会逐渐降低。

3. SQL及索引优化

3.1 准备数据

在mysql官网中, 给我们提供了一个实例数据库, 直接将这个实例数据库导入即可

1、页面地址: https://dev.mysql.com/doc/sakila/en/sakila-installation.html
2、点击实例数据下载地址, 下载即可 3、关于sakila-db.zip压缩包所包含的文件如下解释

sakila-schema.sql文件包含创建Sakila数据库结构所需的所有CREATE语句，包括表、视图、存储过程和触发器。
sakila-data.sql文件包含填充sakila-schema创建的结构所需的INSERT语句。以及必须在初始数据加载之后创建的触发器的定义。
sakila.mwb文件是一个MySQL工作台数据模型，您可以在MySQL Workbench中打开它来检查数据库结构。

4、将 sakila-db.zip 上传到linux, 并解压

使用rz命令上传即可: 如果使用rz显示没有此命令, 输入 yum -y install lrzsz 下载即可

由于是一个zip文件, 需要下载 unzip 才能解压压缩包: yum -y install unzip

解压压缩包: unzip sakila-db.zip

5、在数据库中创建对应表和库

连接数据库: shell> mysql -u root -p123456
导入数据:  mysql> SOURCE /root/sakila-db/sakila-schema.sql;

6、导入数据

mysql> SOURCE /root/sakila-db/sakila-data.sql;

7、 验证是否成功

USE sakila;  # 使用数据库
SHOW TABLES; # 查看所有表

SELECT COUNT(*) FROM film;
SELECT COUNT(*) FROM film_text;

3.2 查看表结构

可通过sqlyog查看基本结构关系

3.3 如何发现有问题的SQL

   mysql提供了慢查询日志查看功能, 可以帮助查询某一条SQL执行的一些状态

1、查询mysql是否开启慢查询日志

show variables like 'slow_query_log';

2、开启mysql的慢查询日志:

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

set global log_queries_not_using_indexes=on; // 将不使用索引的慢查询日志进行记录
set global slow_query_log=on;  //开启慢查询日志

show variables like 'slow_query_log_file';
//查看慢查询日志存储的位置

set global long_query_time=1;  
//大于1秒钟的数据记录到慢日志中，如果设置为默认0，则会有大量的信息存储在磁盘中，磁盘很容易满掉

3、 检测是否已经开启:

Show databases;
Use sakila;
select * from store;  // 执行查询
select * from staff;  // 执行查询