简单的MySQL数据库优化（mooc）

MySql数据库优化

SQL及索引优化
开启慢查询日志：

show variables like 'slow_query_log'	//查看是否使用慢查询日志
set global show_query_log_file ='/home/mysql/sql_log/mysql-show.log'//慢查询存储文件位置
set global log_queries_not_using_indexes=on;//是否要把没有使用索引的sql放入到慢查询中
set global long_query_time=1;//超过多少秒的查询放入

慢查日志所包含的内容

慢查询日志工具
查看工具的配置信息

mysqldumpslow -h

分析慢查询日志

mysqldumpslow -t /home/mysql/data/mysql-slow.log | more

pt-query-digest工具分析

分析慢查询日志

pt-query-digest -t /home/mysql/data/mysql-slow.log | more

输出



通过慢查询日志的分析结果发现有问题的SQL

1. 查询次数多而且每次查询占用事件长的SQL
   通常为pt-query-digest分析的前几个查询
2. IO大的SQL
   注意pt-query-digest分析中的Rows examine项
3. 未命中索引的SQL
   注意pt-query-digest分析中Rows examine和Rows Send的对比

使用explain查询SQL的执行计划

explain select customer_id ,first_name,last_name from customer;

Count()和Max()的优化方法
查询最后支付时间-优化max()函数

select max(payment_date) from payment;

Max()优化:
建立索引

create index idx_paydate on payment(payment_date);

Count()方法:
在一条SQL中同时查出2006年和2007年电影的数量-优化count()函数
错误的方式:

select count(release year='2006' or release year='2007') from file;

无法分开计算2006和2007年的电影数量

select count(*) from file where release year='2006' and release year='2007';

Release year 不可能同时为2006和2007，因此上有逻辑错误
正确方式:

select count（release_tear='2006' or null) as '2006年电影数量',
count(release_year='2007' or null) as '2007年电影数量' from file;

子查询优化

查询在t中的id根t1中id相同的结果

select * from t where t.id in (select t1.tid from t1);

改成join

select t.id from t join t1 on t.id = t1.tid;

如果存在一对多关系时需要用distinct去重

去重后

group by查询优化
查询每一个演员所参演的演员数量

explain SELECT actor.first_name,actor.last_name,COUNT(*)
FROM sakila.file_actor
INNER JOIN sakila.actor USING(actor.id)
GROUP BY file_actor.actor_id;

改写SQL避免临时表操作

Explain SELECT actor.first name,actor.last name,c.cnt 
FROM sakila.actor INNER JOIN(
SELECT actor id, COUNT(*) AS cnt FROM sakila.film actor GROUP BY
actor id
) AS c USING(ACTOR id);

优化Limit查询
limit常用于分页处理，时常会伴随order by从句使用，因此大多时候会使用Filesorts 这样会造成大量的IO问题。
列出影片的描述信息：

SELECT film_id ,description FROM sakila.film ORDER BY title LIMIT 50,5;

优化步骤1：使用有索引的列或主键进行Order by操作

select film_id ,description from sakila.film ORDER BY film_id LIMIT 50,5;

优化步骤2:记录上次返回的主键，在下次查询时使用主键过滤
(避免了数据量大时扫描过多的记录)

SELECT film_id ,description FROM sakila.film WHERE film_id >55 and film_id<=60 
ORDER BY film_id LIMIT 1,5;

选择合适的列建立索引

1. 在where从句，group by从句，order by 从句，on从句中出现的列
2. 索引字段越小越好
3. 离散度大的列放到联合索引的前面

select * from payment where staff_id =2 and customer_id =584;

是index (sftaff_id ,customer_id) 好？还是index(customer_id ,staff_id)好？

->由于customer-id的离散度更大，索引应该使用index(customer_id,staff_id);

索引的维护及优化-- 查找重复及冗余索引

使用pt-duplicate-key-checker工具检查重复及冗余索引

pt-duplicate-key-checker \
-uroot \
-p "\
-h 127.0.0.1

删除不用索引

数据库结构优化
选择合适的数据类型
数据类型的选择，重点在于合适二字，如何确定选择的数据类型是否合适？

1. 使用可以存下你的数据的最小哦啊的数据类型
2. 使用简单的数据类型。Int要比varchar类型在mysql处理上简单
3. 尽可能的使用not null定义字段。
4. 尽量少用text类型，非用不可时最好考虑分表

使用int来存储日期时间，利用FROM_UNIXTIME()，UNIX_TIMESTAMP()两个函数来进行转换

使用bigint来记录IP地址，利用INET_ATON(),INET-BTOA()两个函数来进行转换

表的范式化和反范式化

不符合第三范式要求的表存在下列问题：

1. 数据冗余：（分类，分类描述）对于每一个商品都会进行记录
2. 数据的插入异常
3. 数据的更新异常
4. 数据的删除异常

反范式化是指为了查询效率的考虑把原本符合第三范式的表适当的增加冗余，以达到优化查询效率的目的，反范式化是一种以空间来换取时间的操作。


表的垂直拆分
所谓的垂直拆分，就是把原来一个有很多列的表拆分称多个表，这解决了表的宽度问题。通常垂直吃啊分可以按以下原则进行：

1. 把不常用的字段单独存放到一个表中
2. 把大字段独立存放到一个表中
3. 把经常一起使用的字段放到一起

表的水平拆分
水平拆分主要是为了解决单表的数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的。
常用的水平拆分方法

1. 对主键进行hash运算，如果要拆分成5各表则使用mod(customer_id,5)取出0-4个值
2. 针对不同的hashID把数据存到不同的表中

问题：

1. 跨分区表进行数据查询
2. 统计及后台报表操作

操作系统配置优化
数据库是基于操作系统的，没有钱大多数MySQL都是安装在Linux系统上，所以对于操作系统的一些参数配置也会影响到MySQL的性能，下面就列出一些常用到系统配置。


MySQL配置文件


innodb_buffer_pool_instances
MySQL5.5中新增加参数，可以控制缓冲池的个数，默认情况下只有一个缓冲池
innodb log缓冲的大小，由于日志最长每秒钟就会刷新所以一般不用太大
innodb_flush_log_at_trx_commit
关键参数，对innodb的IO效率影响很大。默认值为1，可以取0，1，2三个值，一般建议设为2，但如果数据安全性要求比较高则使用默认值1
innodb_file_per_table
关键参数，控制Innodb每一个表使用独立的表空间，默认为OFF,也就是所有表都会建立在共享表空间中，建议设置为ON
innodb)stats_on_metadata
决定了MySQL在上面情况下会刷新innodb表的统计信息(设置为OFF)

系统配置优化–第三方配置工具
Percon Configguraion Wizard

服务器硬件优化

1. MySQL有一些工作只能使用到单核CPU
2. MySQL对CPU核数的支持并不是越多越快
   MySQL5.5使用的服务器不要超过32核