慕课-MySQL数据库优化

MySQL优化简介

数据库优化目的

1、避免出现页面访问错误
2、由于慢查询造成页面无法加载
3、增加数据库稳定性

数据库优化

数据准备

下载sakila数据库

链接
然后解压执行sql，参考

mysql> SOURCE C:/temp/sakila-db/sakila-schema.sql;
mysql> SOURCE C:/temp/sakila-db/sakila-data.sql;

MySQL慢查询日志的开启方式和存储格式

如何发现有问题的SQL

使用MySQL慢查日志对有效率问题的SQL进行监控

• show variables like ‘slow_query_log’
  查看慢查日志是否开启
  
  show variables like ‘%log’;
  没看到记录是否使用索引的日志已开启；
  
  查看慢查询日志的位置
  
• set global slow_query_log_file = ‘/home/mysql/sql_log/mysql-slow.log’
• set global log_queries_not_using_indexes=on;
• set global long_query_time=1

慢查询日志包含的信息

慢查询日志分析工具mysqldumpslow

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

如何通过慢查日志发现有问题sql

2 扫描行数,IO
3、扫描行数远远大于rows send说明索引使用不高

explain显示sql执行计划

explain返回各列含义

• table:显示这一行的数据是关于哪张表的
• type：这是重要的列，显示连接使用了何种类型，从最好到最差的连接类型是const（主键或者唯一索引）、eq_reg（范围查找）、ref（）、range（索引范围查找）、index（索引的扫描）和ALL
• possible_keys:显示可能应用在这张表中的索引。如果为空，没有可能的索引。
• key:实际使用的索引。如果为null，则没有使用索引
• key_len：使用索引的长度，在不损失精确性的情况下，长度越短越好
• ref：显示索引的哪一列被使用了，如果可能，是一个常数
• rows：mysql认为必须检查的用来返回请求数据的行数

Count()和Max()优化

建立索引


通过索引查询包含了我们需要的列叫做覆盖索引

子查询优化

把子查询优化为join查询，但优化时需要注意关联键是否有一对多的关系，要注意重复数据。

group by优化

limit优化

limit常用于分页处理，时常会伴随order by从句使用，因此会使用filesorts这样会造成大量的IO问题
优化1：使用有索引的列或主键进行order by操作

索引优化

如何选择合适的索引

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

索引如何优化

1、重复以及冗余索引（主键跟唯一索引重复）
（建立了主键索引，但是联合索引又把主键索引给包含进去了）

2、业务变更，不用的索引要进行删除，记录索引的使用情况，

数据库结构优化

选择合适的数据类型

bigint存储IP地址
1、使用简单的数据类型，int要比varchar类型在mysql处理上简单
2、尽可能要加上not null定义字段
3、尽量少用text类型，非用不可时最好考虑分表

数据库表范式化优化

第三范式：要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖

不符合第三范式：
存在数据冗余，
数据的插入、更新，删除异常（删除饮料容易删没了）

数据表的反范式优化

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

表的垂直拆分

把原来一个有很多列的表拆分成多个表，解决了表的宽度问题，按照如下原则：
1、把不常用的字段单独存放到一个表中
2、把大字段独立存放到一个表中
3、把经常一起使用的字段放到一起

表的水平拆分

为了解决单表的数据量过大的问题，水平拆分每个表的结构都是一致的

系统的配置优化

mysql配置参数：
innodb_buffer_pool_size

服务器硬件优化

一些命令

查看版本

mysql -V

select version();