mysql优化

一、数据库优化的目的

    (一)避免出现页面访问错误

        1. 由于数据库连接timeout产生页面5××错误

        2. 由于慢查询造成页面无法加载

        3. 由于阻塞造成数据无法提交

  (二) 增加数据库的稳定行

        很多数据问题都是由于低效的查询引起的

   (三)优化用户体验

        流畅页面的访问速度

        良好的网站功能体验

二、数据优化的几方面

    1. sql及索引的优化

    2. 数据库表结构设计

    3. 系统配置

    4. 硬件

Mysql慢查询日志的开启方式及存储格式

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

show variables like 'slow_query_log' 查询慢查询日志存在的路径

慢查询日志存放位置

set global slow_query _log_file = '/home/mysql/sql_log/mysql-slow.log'

没有使用的索引的sql记录到慢查询日志中

set global log_queries_not_using_indexes=on 

把sql执行时间多长的设置到慢查询日志中这里是执行大于1秒的sql记录到慢查询日志中一般在100毫秒

set globl long_query_time=1

慢查询日志所包含的内容

执行的sql的主机信息

sql的执行信息

sql的执行时间

sql的内容

如何查看慢查询日志和分析

1.mysqldumpslow 工具

工具的使用  

 mysqldumpslow -h 列出它的参数都是那些

2. pt-query-digest 工具

 

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

1.查询次数多且每次查询都占用时间长的sql

通过pt-query-digest 分析的前几个查询

2.IO大的sql

注意 pt-query-digest 分析中的Rows examine 项

3.未命中索引的sql 

注意 pt-query-digest 分析中Rows examine 和 Rows Send 的对比

Rows examine 扫描行数

Rows Send 发送的行数

如果 rows examine 远远大于Rows send 说明 扫描行数远远大于发送的行数

使用explain查询sql 的执行计划

table :显示这一行的数据是关于那些张表的

type : 这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、 ref、 range、 index 和all

possible_keys :显示可能应用在这张表里的索引。如果为空没有索引

key : 实际使用的索引。如果为null 则没有使用索引

key_len :使用的做引长度,在不损失精确性的情况下。越短越好

ref: 显示索引的那一列被使用了,如果可能的话,是一个常数

rows : mysql 认为必须检查的用来返回请求数据的行数

extra需要注意返回值

1、Using filesort :看到这个时候,查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

2、 Using temporary : 看到这个时候,查询就需要优化了。这里,mysql 需要创建一个临时表来存储结果。这通常发生在对不同的列集进行order by 上。而不是group by 上

例如 count 和Max()的优化方法

explain select max(create_time) from  count \G 

如何选择合理的列建立索引

1、在where从句,group by从句,order by 从句,on 从句中出现的列

2、索引字段越小越好

3、离散度大的列放到联合索引前面

例如 select * from payment where staff_id=2 and customer _id = 584;

是index(staff_id,costomer_id)好?还是index(costomer_id,staff_id) 好

由于costomer_id 的离散度大 将costomer_id放在前面是最好的

重复及冗余索引

冗余索引是指多个索引的前缀相同,或是在联合索引中包含了主键的索引。因为主键本身就是一个索引,没有必要在创建索引时加上主键。

例如

create table test(id int not null primay key,

name varchar(10) not null,

titile varchar(50)

key(name,id)

)engine=innodb;

这里的key(name,id)就是一个冗余索引 

注:这是因为innodb特性会在每个索引上都加上主键。

查找冗余索引的sql

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 jion statistics b on

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 where a.seq_in_index=1 abd a.index_name<>b.index_name

工具 pt_duplicate-key-checker 工具查询冗余索引(怎么使用百度)

二、数据库结构优化

(一)、选择合适的数据类型

数据类型的选择,重点在于合适二字,如何确定选择的数据类型是否合适?

1、使用可以存下你的数据的最小的数据类型

2、使用简单的数据类型。int要比varchar类型在mysql处理上简单

3、尽可能的使用not null 定义字段

4、尽量少用text类型。非用不可时最好考虑分表。

(二)表的范式化和反范式化

        范式化是指数据库设计的规范,目前说道范式化一般是指第三设计范式,也就是要求数据表中不存在非关键字段对任意候选字段的传递函数依赖则符合第三范式

商品名称价格重量有效期分类分类描述
可乐3.0015ml2018-09饮料碳酸饮料
苹果8.004kg2018-09生鲜食品水果





阅读更多 登录后自动展开
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页