mysql基础优化详细笔记

mysql优化四种方式：
数据库表优化
sql语句及索引优化
系统配置
硬件

一、使用慢查日志查询mysql语句查询的情况

1、关于慢查询日志的启动和设置：
slow_query_log：慢查日志
查看慢查日志是否开启（value为on为开启）
show variables like ‘slow_query_log’;
查看慢查日志记录位置：
show variables like ‘slow_query_log_file’;

设置日志文件的位置
set global slow_query_log_file=‘C:/mysql_slow_query_log/slow_query_log.log’;

是否要把没有使用索引的sql语句记录在慢查询日志中
set global log_queries_not_using_indexes=on;

把大于多少秒的查询记录在慢查询日志中，此为1s
set global long_query_time=1;

开启慢查日志
set global slow_query_log=on;

2、慢查日志所包含的信息：

执行SQL的主机信息
#User@Host:root[root] @ localhost []

SQL的执行信息
Query_time:sql的执行时间
Lock_time:sql的锁定时间
Rows_sent:sql所发送的行数
Rows_examined:sql被扫描的行数

时间戳形式的SQL执行时间
SET timestamp=1234234132

SQL的具体内容

二、MySQL的慢查询分析工具
1、mysqldumpslow
（mysql官方的慢查询分析工具，默认已被安装）
使用：需要安装strawberryperl环境！已安装在c盘
perl是什么？Practical Extraction and Report Language：实用报表提取语言
cmd运行：
cd C:\strawberryperl\stawberryperl\perl\bin
perl C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldumpslow.pl -s c -t 10 C:\mysql_slow_query_log\slow_query_log.log

2、pt-query-digest
安装设置
第一步：
首先需要安装Perl，在windows下安装Perl，安装过程很简单，从官网http://strawberryperl.com/下载windows安装包，安装好之后，测试perl -v，如果能显示版本号，表示安装成功。
第二步：
从https://raw.githubusercontent.com/percona/percona-toolkit/3.0/bin/pt-query-digest上获取脚本，并保存到MySQL的bin目录下文件名为：pt-query-digest.pl即可

三、Count()和Max()统计函数的优化方法

例1：查询最后的支付时间，max()优化
未优化语句：select max(payment_date) from payment;
使用 emplain select max(payment_date) from payment \G
查看这个查询的执行计划，参数如下：
select_type:SIMPLE 表示简单SQL
talbe:… 要查询的表名
type:ALL 索引值
key:主键等
rows：513232 最重要！！扫描的行数

如何优化？
添加一个索引：
create index idx_paydate on payment(payment_date);
再emplain查看计划，发现rows为
Select tables optimized away ，意为已被优化
添加一个索引，只需要通过索引就可以知道max()的结果，不需要再查询
因为索引是顺序排列的，只需知道最后一个索引就知道啦，称为覆盖索引

例2：使用count()查询2006年和2007年电影的数量
未优化的sql：
select count(release_year=‘2006’ or release_year=‘2007’) from film;
结果：只有一个查询结果，2006年和2007年全在一起了
要把这两年分开，第二种查询方式：
select count() from film where release_year=‘2006’ and release_year=‘2007’;
逻辑错误：release_year不可能即等于2006又等于2007
第三种查询方式：
select count(release_year=‘2006’ or null) as ‘2006年电影数量’, count(release_year=‘2007’ or null) as ‘2007年电影数量’ from film;
通过 数值 or null 避免发生以上情况
注：count() 和count(id) 两个查询结果不同，id不包含空值，*包含

四、子查询的优化
未优化的sql：查询t和t1表中id值相同的数据：
select * from t where t.id in (select t1.tid from t1)
优化成连接的形式：
select t.id from t join t1 on t.id=t1.tid;
如果t.id和t1.tid都有且只有1，则子查询会查询出一个数据（1），连接查询会查询出两个数据（1 1），出现了查询重复
此时连接查询使用distinct去重即可
select distinct t.id from t join t1 on t.id=t1.tid;

五、limit优化
limit常用于分页处理，未优化语句：
select * from sakil order by title limit 50,5;
翻译一下：查找sakil表，显示从50往后数5个数据，按照title排序
这样会扫描大量记录
优化步骤1：使用有索引的列或主键进行Order by操作
select * from sakil order by id limit 50,5;
可以减少大量扫描行数
优化步骤2：记录上次返回的主键，在下次查询时使用主键过滤
select * from sakil where id>50 and id<=55 order by id limit 1,5;

六、如何选择合适的列建立索引
1、从句中需要以where、group by、order by、on关键字搜索
2、索引可以辅助查询内容
3、索引字段越小越好
4、在联合查询时，要考虑哪个列放前面，离散度越大的列放到前面效果越好
如：select * from payment where row1=1 and row2=2；
如何判断列的离散程度？也就是计算出数据库中的记录不重复数量（也就是唯一值），
使用distinct
select count(distinct row1),count(distinct rwo2)from payment;
若得出值：count(row1)=10,count(row2)=20，则表示row列中的不重复数据为10，row为20，row2的离散程度更大，所以该查询语句要优化成 select * from payment where row2=2 and row1=1;(row2放在前面)

七、SQL索引优化
1、索引是不是越多越好？不是，索引过多会影响数据库查询效率
2、什么是重复索引和多余索引？
重复索引是指相同的列以相同的顺序建立的同类型的索引
例：id是主键，主键就是索引，但是又以id建立了一个索引
多余索引指联合索引中有主键或其他索引
例：id是主键，又建立了索引key(name,id)
还有不用到的索引
3、索引的维护及优化工具：
pt-duplicate-key-checker

八、数据库结构优化—选择合适的数据类型
1、使用可以存下数据最小的数据类型
2、使用简单的数据类型，Int要比Varcher类型在mysql上处理简单
int和时间戳是最小的类型
3、尽可能的使用使用not null定义字段，最好给出一个默认值
4、尽量少用text类型，非用不可时最好考虑分表
例：使用from_unixtime()时间类型转换为int类型,
from_timestamp()int类型转换为事件类型设计表结构
字段：time int,
插入：insert into test（timestr) values (unix_timestamp(‘2014-04-01 13:12:22’));
select from_unixtime(time) from …;
例2：使用inet_aton(),将varcher转换为bigint
使用inet_ntoa()将bigint转换为varcher
字段：ipaddress bigint
插入：insert into session (inaddress) values(inet_aton(‘192.168.1.1’))
查询：select inet_ntoa(ipaddress) from session;

九、表的范式化和反范式化
1、什么是范式化？
例：一个表的信息如下：
商品名称 价格 重量 分类 分类描述
在这个表中，商品名称是唯一的，但是分类和分类描述重复了，这样在查询和添加时会进行更多的数据处理，删除时也可能出错，这样就不符合范式化，那么如何优化呢？
将这个表分成三个表：
商品名称 价格 重量 分类
分类 分类描述
分类 商品名称
2、什么时反范式化？
使用范式化表的时候，查询要建立很多的链接，太过麻烦
适当增加多余查询达到优化查询效率的目的，具体如何做？
a:在spingMVC的项目中学过，在对应表的实体类中将所需的子表作为属性传递给实体类
b:适当多加几个字段

十、表的垂直拆分和水平拆分
1、什么是垂直拆分？
把一个表中较大，不常用的字段放在另一张副表中，使用一个字段和主表相连
2、什么是水平拆分？
表的数据太多，将相同表的表复制几个去存储数据，就是对id值进行hash运算

十一、系统配置优化
1、linux系统的一些参数优化
增加tcp支持的队列数 关键
net.ipv4.tcp_max_syn_backlog=65535
设置最大打开文件数
hard nofile 65535
使用硬件防火墙，不使用软件防火墙等
2、myaql的配置文件
innodb_buffer_pool_size
innodb的占内存比例，推荐为75%，有其他表的情况，尽量大

innodb_buffer_pool_instances
innodb的缓冲池数量，默认为1，可以设置成4或8，避免堵塞

innodb_log_buffer_size
innodb log的缓冲大小，由于日志最长每秒钟就会刷新所以不用太大

innodb_flush_log_at_trx_commit
关键参数，对innodb的IO效率影响很大，默认值为1，可以取0，1，2三个值，一般建议设置为2，但如果数据安全性要求较高则使用默认值1

innodb_read_io_threads
innodb_write_io_threds
以上两个参数决定了innodb读写的IO进程数，默认为4，根据具体情况可更改

innodb_file_per_table
关键参数，控制innodb每一个表使用独立的表空间，默认为OFF，也就是所有表都会建立在共享空间中，建议设为ON，避免磁盘空间浪费

innodb_stats_on_metadata
决定了mysql在什么情况会刷新innodb表的统计信息，设为off关闭刷新，人为在空闲时间进行刷新

3、第三方配置工具使用
可以生成数据库配置文件
http://tools.percona.com/wizard
如果该网站打不开，可以寻找其他配置向导网址

十二、硬件优化
1、尽量选择单核速度更快的cpu
2、尽量不要超过32核