第一章 数据库优化简介
1、 数据库优化的目的
a) 避免出现页面的访问错误
i. 由于数据库连接timeout产生页面出现服务器错误
ii. 由于慢查询造成页面无法加载
iii. 由于阻塞造成数据无法提交
b) 增加数据库的稳定性
i. 很多数据库问题都是由于低效的查询造成的
c) 优化用户体验
i. 流畅的页面访问速度
ii. 良好的网站功能体验
2、 数据库优化的几个方面（成本有高到低，效果由低到高）
a) 硬件
b) 系统配置
c) 数据表结构
d) SQL及索引
第二章 SQL语句的优化
1、 数据库选择
a) 数据库表建立步骤
i. 登陆http://dev.mysql.com/doc/sakila/en/sakila-installation.html
ii. 下载文件，解压
iii. 使用mysql工具运行SQL文件
iv. sakila-schema.sql是建表文件 ；
v. sakila-sata.sql是插入数据文件
vi. 导入数据出现错误：Got a packet bigger than 'max_allowed_packet' bytes
修改相关配置即可：mysql>set global max_allowed_packet = 1024*1024*160;
2、 mysql慢查询日志
a) 查看慢查询日志开启情况：
i. showvariables like ‘slow_query_log’
b) 指定慢查询日志的存储位置
i. setglobal slow_query_log_file= ‘/home/yven/mysql/sql_log/mysql_slow.log’
c) 是否把没有使用索引的的查询记录到慢查询日志中
i. setglobal log_queries_noit_using-indexes = on
d) 把超过一定秒数到查询记录到慢查询日志中（下面为1秒）
i. setglobal long_query_time = 1 (我的默认为10秒)
e) 显示log相关信息：select variables like ‘%log%’;
f) 慢查询日志的格式：
# Time: 180317 23:26:40// 查询时间下面是执行SQL的主机信息
# User@Host: root[root] @ [183.17.232.155] Id: 1352
# Query_time: 22.835337 Lock_time:0.000092 Rows_sent: 1000 Rows_examined: 1000 //查询的执行信息
SET timestamp=1521300400; // 查询的执行时间
select * from film; // 查询的具体SQL
3、 mysql慢查询日志分析工具之mysqldumpslow
a) 直接运行 （-t 3 表示查询3条记录 , 后面是慢查询日志）
# mysqldumpslow -t 3/var/lib/mysql/iz2ze62pukeeygh3orhvv7z-slow.log
4、 mysql慢查询日志分析工具之pt-query-digest
a) 快速安装：
i. wgethttps://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
b) 将查询结果输出到文件
i. pt-query-digestslow-log > slow_log.report
c) 输出到数据库
i. pt-query-digestslow.log –reviewh=120.0.0.1,D=test,p=root.P=3306,u=root,t=query_review
--create-reviewtable –review-history t=hostname_slow
5、 通过慢查询日志发现问题
a) 查询次数多且每次查询占用时间长的SQL
i. 通常为pt-qiery-digest分析的前几个查询
b) IO大的SQL
i. 注意分析Rows examine项
c) 未命中索引的SQL
i. 注意Row examine和Row Send的对比
6、 通过explain查询和分析SQL
a) 使用explain查询SQL的执行计划
i. mysql> explain select * from city;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table |type | possible_keys | key | key_len |ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 600 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
b) a
7、 Count()和Max()的优化
a) 查询max, explain selectmax(payment_date) from payment
+----+-------------+---------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table | type |possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------+
| 1 | SIMPLE | payment | ALL | NULL | NULL | NULL | NULL | 16086 | NULL |
+----+-------------+---------+------+---------------+------+---------+-----
a) 建立索引 createindex idx_paymentdate on payment(payment_date)
b) 再进行explain selctmax(payment_date) from payment;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL |NULL | NULL | NULL | Select tablesoptimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
a) 再一条SQL中同时查询出2006 和 2007 年电影的数量：
select count(release_year = '2006' or null) as '2006',count(release_year ='2007' or null ) as '2007' from film;
b) a
c) count(*) count(id) 的区别。Count(*) 的结果包括了查询为null的记录
8、 子查询的优化
a) 将子查询优化为join查询，但在优化时要住要关联的键是否有一对多关系，要注意重复数据。可以使用distinct去重
b) 查看建表sql: show createtable city
9、 groupby 的优化
a) aaa
10、 limit查询的优化
a) 使用有索引的列或者主键进行orderby操作
select film_id,description from film order by film_idlimt 50,5
b) 记录上一次返回的主键(上面的SQL需要扫面55行记录,下面的SQL只需要扫描5行)，在下一次查询时使用主键过滤，注意这里的排序的主键需要时连续的。
select film_id,description from film wherefilm_id > 55 and film_id <= 60 order by film_id limit 1,5
第三章 索引的优化
1、 如何选择合适的列建立索引
a) a
b)
2、 索引优化SQL的方法
a) 增加索引有利于查询，不利于写入
-- use infomation_schema
select
a.TABLE_SCHEMA AS '数据名',
a.TABLE_NAME AS '表名',
a.INDEX_NAME AS '索引1',
b.INDEX_NAME AS '索引2',
a.COLUMN_NAME as '重复列名'
from STATISTICS a JOIN STATISTICS bON
a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
AND a.COLUMN_NAME =b.COLUMN_NAME
b) 使用工具检查
pt-duplicate-key-checker –u root –p ‘123456’ –h localhost
3、 索引维护的方法
a) 使用pt-index-usage 来分析慢查询日志，查看哪些索引时没有使用的
pt-index-usage –uroot –p ‘123’mysql-slow.log
第四章 数据库结构优化
1、 选择合适的数据类型
a) int类型和时间戳的转换：FROM_UNIXTIME(int),UNIX_TIMESTAP(time)
b) 使用bigint来存储IP地址。
INET_ATON(‘192.1.1.1’),INET_NTOA(ip)
2、 数据库表的范式化优化
3、 数据库表的反范式优化
4、 数据库表的垂直拆分
5、 数据库表的水平拆分
第五章 系统配置优化
1、 数据库配置优化
a) a
2、 mysql配置文件优化
a) a
3、 第三方配置工具使用
a) 生成配置文件
第六章 服务器硬件优化
1、服务器硬件优化
2253
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
