mysql里sql优化和表结构优化

开启慢查询日志 计入sql

show variables like 'slow_query_log';//慢查询查看状态 OFF未开启 ON开启

show variables like '%log%';//没有使用索引的sql计入慢查询日志中

set global log_queries_nor_using_indexes = on;

//开启log_queries_nor_using_indexes 为ON 记录未使用索引的查询
show variables like 'long_query_time';

//超过多少时间就记录到慢查询中
//开启慢查询
set global slow_query_log = on;
//慢查询日志的位置
show variables like 'slow%';

left join :eft join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
right join:和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.
inner join:这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.

sql索引优化

explain select 字段 from 表名 ；

table 代表当前表
type 显示类型 重要列 ！性能最好到最坏排序 const(主键唯一索引查找)，eq_reg（范围查找）,ref（一个表基于一个索引查找）,range（基于索引范围查找）,index（对索引的扫描）,all（表扫描）
possible_keys 当前表可用到的索引有哪些，如果为null就是没有可能的索引
key 实际使用的索引，为null就是没有可能的索引
key_len 使用索引的长度 越小越好
ref 显示索引的哪一列被使用
rows mysql必须检查用来返回请求的数据行数
extra 返回值
using filesort:查询需要优化，mysql需要进行额外步骤，文件排序进行优化
using temporary ：查询需要优化，mysql需要创建一个临时表来存储结果

• sql优化例子 *
  Where查询条件，on内外连接时候用,as作为别名,in查询某值是否在某条件里
  

max优化方法：查找最大的 最后的

select max(字段data) from 表;

优化方法：在data上建立索引

create index idx_data on 表(字段data) ;

count优化方法:全部
查找2006-2007的数量全部的数量
优化方法：
select count(year='2006' or year='2007') from 表;

查找2006-2007的分开的数量
优化方法：
select count(year='2006' or null),count(year='2007' or null) from 表;

子查询优化:

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

优化方法： 需要把子查询优化为join查询 join on 后边的条件只针对附表

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

去重distinct

select distinct t.id from t jion t1 on t.id = t1.tid;

group by查询

select actor.first_name,actor.last_name, count(*) from filem_actor inner actor using(actor_id) group by filem_actor.actor_id;

优化后group by查询

select actor.first_name,actor.last_name,c.cnt from actor inner join (select actor_id,count(*)as cnt from film_actor group by actor_id ) as c using(actor_id);

limit优化方法：

select film_id,dd from film order by title limit 600,5;//从第600行开始的5条记录

优化后

select film_id,dd from film where film_id >600 and film_id <=605 oder by film_id limit 1,5;

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

优化查询效率，对插入 修改 删除语句有影响
索引越多查询越慢

如何找到重复索引
列子：
id primay key主键索引 unique(id)又生成一个唯一索引 这就重复索引！
如何找到zongyu索引
多个索引的前缀列是相同的
id primay key主键索引 又生成一个key（name,id）联合索引！

表结构优化
如何选择合适的数据类型：
1.可以存下数据的最小的数据类型
2.使用简单的数据类型。 int比varchar在mysql处理上简单
3.尽可能的使用not null定义字段
4.尽量少于text类型，非用时考虑分表

范式化优化
数据表结构
传递函数依赖关系 商品名称-》分类-》分类描述

商品名称 价格 重量 有效期 分类 分类描述

把一张表分成3张表
1商品表
商品名称 价格 重量 有效期
2分类表
分类 分类描述
3关系表 **
商品名称 分类

反范式化优化
操作前：
用户表：
用户id 姓名 电话 地址 邮编
订单表：
订单id 用户id 下单时间 支付类型 订单状态
订单商品表：
订单id 商品id 商品数量 商品价格
商品表：
商品id 名称 描述 过期时间

操作后：
用户表：
用户id 姓名 电话 地址 邮编

订单表：合并一个表中查询

订单id 用户id 下单时间 支付类型 订单状态 订单价格 用户名 电话 地址

订单商品表：

订单id 商品id 商品数量 商品价格

商品表：

商品id 名称 描述 过期时间

执行sql查询订单信息：
select a.用户名，a.电话,a.地址，a.订单id，a.订单价格 from 订单表 a

表的垂直拆分：
例：

id
title varvhar
description text
..字段
如果表中有包含 title和description text数据类型的话，拆分出去
用拆分后的付加表中的表id关联拆分前的表id
付加表：

id
title varvhar
description text

表的水平拆分：
解决表单的数据量过大

系统优化：
1操作系统的优化 2mysql系统本身优化
mysql服务器上关闭iptables，selinux等防火墙软件

mysql优化：
linux下mysql配置文件位置 /etc/my.cnf 或者 /etc/mysql/my.cnf
windows下mysql配置文件位置 c：/ windows/my.ini文件

服务器硬件优化
mysql一些工作只能用到单核cpu的
选择单核频率更快的cpu

转载于:https://blog.51cto.com/kangjunfei/2051695