MySQL优化
1. 为什么要进行数据库优化
避免网站页面出现访问错误
由于数据库连接timeout产生页面5xx错误 , to many connection
由于慢查询造成页面无法加载 : 由于数据库无法及时的返回数据, 导致页面一直无法返回
由于阻塞造成数据无法提交 :
增加数据库的稳定性
很多数据库问题都是由于低效的查询引起的
优化用户体验
流畅页面的访问速度
良好的网站功能体验
2. MySql数据库优化
可以从哪几个方面进行数据库的优化?如下图所示:
SQL及索引优化
根据需求写出良好的SQL,并创建有效的索引,实现某一种需求可以多种写法,这时候我们就要选择一种效率最高的写法。这个时候就要了解sql优化
数据库表结构优化
根据数据库的范式,设计表结构,表结构设计的好直接关系到写SQL语句。
系统配置优化
大多数运行在Linux机器上,如tcp连接数的限制、打开文件数的限制、安全性的限制,因此我们要对这些配置进行相应的优化。
硬件配置优化
选择适合数据库服务的cpu,更快的IO,更高的内存;cpu并不是越多越好,某些数据库版本有最大的限制,IO操作并不是减少阻塞。
注:通过上图可以看出,该金字塔中,优化的成本从下而上逐渐增高,而优化的效果会逐渐降低。
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; // 执行查询
<