mysql

sql优化

写的更详细的https://blog.csdn.net/zh15732621679/article/details/80394790

explain select c.id from (select * from course) c;

执行过程：from  on  join where   group by    having    selcet   distinct  order by limit

• select_type:

primary--subquery在内层，primary在外层

simple--只有一句

derived--衍生查询。 subquery只有一张表即course，它生成临时表，最外层的primary就在临时表上查询。

第二种情况是：select c.id from (select * from course where id=1 union  select * from course where id=2) c;

左表为derived表，右表为union表

union-result:告知表的union关系

• type:

效率：system>const>eq_ref>ref(达到这个)>range>index>all

const:只能查到一条数据， select * from comments where id(primarykey) =1;用于primary key 和unique index.

若为其他非主键，非唯一索引则为ref。select * from comments where user_id=1;

eq_ref:唯一索引。结果多条，数据唯一。如果是两张表，where t1.id=t2.id,必须满足数量且值对应, 1 2 3 == 1 2 3,其他不行。

ref:结果多条，数据0或多。非唯一索引。select * from comments where user_id=1;

range：范围查找。where 后 between ，>,<.in 有时候会失效

index：检查全部索引。

all：所有数据

• possible_keys

可能用到的索引，key=实际用的

• key_len

用到的索引的长度。utf8=3个字节，gbk=2字节，Latin1=1字节

 char(20=字符数) not null  key_len=60.utf8 3个字节，20*3=60

alter table tmp_0612 convert to charset latin1;改成Latin1会变成23(1null+2变)

char(20),key_len=61.若可为空，则还需要多1个字节

改成varchar，会变成63(1 null+2变)

• ref

select * from t1,t2 where t1.id=t2.id and t2.id=3;

两个值:1是const.2是实际引用变量。const=3,t1.id=<tablename>.t2.id;

• rows

优化查询用到的行数

• extra

若为usefilesort则表示性能消耗大

出现usefilesort的可能情况

1.查询的字段和排序的字段不一样 。explain select c.id(索引) from comments c order by c.comment_body(非索引);

2.复合索引跨列使用. explain select c.id(复合索引1） from comments c order by c.comment_body（复合索引3);

若为usetemprary则表示性能消耗大

出现usetemprary的可能情况

1.group by。查询和分组用的字段不一样，分组新添一张表group导致。explain select parent_id(非索引) from comments group by  parent_id;

useing_index

性能提升，不需要回表查询，索引覆盖。只从索引文件里寻找，不从原文件查找。

using where

需要回原表查询。select a，b(index) from t where a=1;

impossible where

不可能的。select   a(index) from t where a=1 and a=3;a为索引。用非索引测试，没有出现。

优化

1.最佳左边前缀。复合索引不要跨列，in可能会失效要放where后，没有in最好。

2.避免索引失效。复合索引尽量全使用（学校-班级），不要在索引上用计算，函数，转换。复合索引若左失效，则右侧失效。

3.复合索引不能使用 ！=或者 is null。否则自身以及右侧都失效。

优化步骤

1、测试时打开慢查询日志

show status ’%slow_queries%‘,  #慢查询状态

show variables like '%slow_query_log%';#慢查询日志

set global slow_query_log=1;#打开慢查询记录

show variables like '%long_query_time%';#修改慢查询时间，默认10s

set global long_query_time=2；#需重启客户端，不是重启服务

若要永久修改

"/etc/mysql/my.cnf" to set global options,
- "~/.my.cnf" to set user-specific options

[mysqld]

character-set-server=utf8

long_query_time=2

2、日志分析

mysqldumpslow --help

锁

 

 

主从复制

实现主从同步（主从复制）：

同步的核心：二进制日志

1.  master   将改变的数  记录在本地的  二进制日志中（binary log）;该过程称之为：二进制日志事件

2.slave  将master的binary log 拷贝到自己的relay log (中继日志文件)中 

3.中继日志事件，将数据读取到自己数据库之中

MySQL主从复制   是异步的，串行化的，有延迟

master：slave  =1:n

 

 

常见问题

设置utf-8编码

#修改已建立的表
alter table post convert to character set utf8;

#设置全局 
/etc/mysql 新建conf文件
[mysqld]
character-set-server=utf8 
[client]
default-character-set=utf8 
[mysql]
default-character-set=utf8
#创建
create table mytable (id int primary key ， name varchar(30), sign varchar(50)) default charset=utf8;

变量

status #查看操作表的状态，使用的数据库
show  variables like 'character_set_%'; #字符集编码