MySQL数据库优化
1-1MySQL优化简介
数据库优化的目的
避免出现页面访问错误
- 由于数据库连接timeout产生5XX错误
- 由于慢查询造成页面无法加载
- 由于阻塞造成数据无法提交
增加数据库的稳定性
- 很多数据库的问题都是由于低效查询引起的
优化用户体验
- 流畅页面的访问速度
- 良好的网站功能体验
可以从以下几个方面进行数据库优化
MySQL数据库优化:
1.SQL语句优化
2.有效的索引
3.数据库的表结构
4.Linux系统配置优化:打开的文件数等
5.硬件:更加适合数据库系统的cpu、更快的io:ssd等、更多的内存...
2-1数据准备
Sakila样本数据库介绍
- 下载Sakila样本数据库,下载地址http://downloads.mysql.com/docs/sakila-db.tar.gz(下载页面http://dev.mysql.com/doc/index-other.html)。
- 导入sakila-schema.sql和sakila-data.sql文件
首先下载mysql5.7.18 zip安装包,配置环境变量
bin文件夹下建立my.ini
[mysqld]
basedir=E:\Program Files (x86)\mysql-5.7.24-winx64\mysql-5.7.24-winx64\bin
datadir=E:\Program Files (x86)\mysql-5.7.24-winx64\mysql-5.7.24-winx64\data
port=3306
character-set-server=utf8
[client]
port=3306
character-set=utf8
在mysql根目录下新建data文件夹
以管理员身份运行cmd命令
输入cd G:\Program Files\MySQL\MySQL Server 5.7\bin进入bin目录下
输入mysqld --initialize --user=mysql --console
记下最后一行产生的随机密码
接着输入mysqld --install
成功会显示Servers Successfully installed
控制台下输入net start mysql 启动mysql服务
修改密码:
mysql> set password=password('');
Query OK, 0 rows affected, 1 warning (0.00 sec)
source C:\Users\jiang\Desktop\sakila-db\sakila-db\sakila-schema.sql
source C:\Users\jiang\Desktop\sakila-db\sakila-db\sakila-data.sql
完成数据导入。
2-2 MySQL慢查日志的开启方式和存储格式
查看是否开启慢查询日志 show variables like 'slow_query_log' set global slow_query_log =on
设置没有索引的记录到慢查询日志 set global log_queries_not_using_indexes=on
查看日志 show variables like '%log%';
查看超过多长时间的sql进行记录到慢查询日志 show variables like 'long_query_time'
设置慢查询的时间 set long_query_time=1
2-3 MySQL慢查日志分析工具之mysqldumpslow
慢查日志分析软件:mysqldumpslow
mysql官方分析软件 启用mysqldumpslow时首先要在workbench中设置basedir的路径才能避免报错
mysqldumpslow 在windows中是一个perl文件,所以需要你配置perl环境变量,并使用perl运行
2-4 MySQL慢查日志分析工具之pt-query-digest
慢查询分析工具2:pt-query-digest
输出到文件:pt-query-digest show-log > slow_log.report
输出到数据库表 pt-query-digest show.log -review \ h=127.0.0.1,D=test,p=root.P=3306,u=root,t=query_review \ --create-reviewtable \ --review-history t=hostname_show
具体参考:
下载地址: wget http://www.percona.com/downloads/percona-toolkit/2.2.4/percona-toolkit-2.2.4.tar.gz
tar percona-toolkit-2.2.4.tar.gz
2-5 如何通过慢查日志发现有问题的SQL
1.查询次数多且每次查询占用时间长的SQL 通常为pt-query-digest分析的前几个查询
2.IO大的SQL 注意pt-query-digest分析中的Rows examine项
3.未命中索引的SQL 注意pt-query-digest分析中的Rows examine和Row send 的对比
2-6 通过explain查询和分析SQL的执行计划
table:表名
type:连接的类型,const、eq_reg、ref、range、index和ALL
const:主键、唯一索引;eq_reg:主键、索引的范围查找;ref:连接的查找( join),
range:索引的范围查找;index:索引的扫描;All:表扫描
possible_keys:可能用到的索引
key:实际使用的索引
key_len:使用索引的长度,长度越短越好
rows:表扫描的行数
extra:Using filesort Using temporary 使用外部文件或临时表存储结果,看到这两个就代表是要进行优化的
2-7 Count()和Max()的优化
1.对max()查询,可以为表创建索引,
例如 select max(payment_date) from payment;
可以对payment建立索引
create index idx_paydate on payment(payment_date); 然后在进行查询
max()使用索引,不需要扫描行了。称为覆盖索引
2、COUNT()的优化方法:
在一条SQL中同时查出2006年和2007年电影的数量——优化count()函数
错误的方式: SELECT COUNT(release_year='2006' OR release_year='2007') from film;
正确的方式:(利用NULL值不会被计数的特性)
SELECT COUNT(release_year='2006' OR NULL) as '2006年电影数量', count(release_year='2007' OR NULL) as '2007年电影数量' FROM film;
select count(year='2006' or null) as xx from tablename; 后面的or null会把为null的值跳过,不计入数量中。
这是利用了select count(col_name)的特点,指定列不为空的行数;
select count(*) 为所有行数。
EXPLAIN SQL \G 显示好看
2-8 子查询的优化
子查询可以用join on查询优化(注意:如果两张表连接字段存在一对多的关系,需要进行去重distinct)
2-9 group by的优化
可以把
explain select actor.first_name,actor.last_name,count(*) from film_actor inner join actor on actor.actor_id = film_actor.actor_id group by actor.actor_id\G
改写为
explain select actor.first_name,actor.last_name,c.cnt from actor inner join (select actor_id,count(*) AS cnt from film_actor GROUP BY actor_id) AS c USING(actor_id)\G
2-10 limit的优化
limit常用于分页处理,时常会伴随order by从句使用,因此大多时候会使用Filesorts这样会造成大量的io问题
1.使用有索引的列或主键进行order by操作
2.记录上次返回的主键,在下次查询时使用主键过滤
即将:select film_id,description from sakila.film order by film_id limit 50,5;
改为:select film_id,description from sakila.film where file_id >55 and film_id<=60 order by film_id limit 1,5;
使用这种方式有一个限制,就是主键一定要顺序排序和连续的,如果主键出现空缺可能会导致最终页面上显示的列表不足5条,解决办法是附加一列,保证这一列是自增的并增加索引就可以了