mysql经验总结

分类 mysql

mysql经验总结


1优化思路

1拿到待优化的服务器,第一步是观察,可以通过脚本或者阿里云的统计图分析。

2如果出现周期性的波动或者故障,一般都是由访问的高峰或者缓存崩溃引起,解决方法是增加缓存,修改缓存失效策略,使失效的时间分散3-6小时即可,第二是在夜晚访问量低的时候定时失效设置

3解决过后在观察统计图,如果仍有不规则的延迟或者卡顿现象 需要set profiring =1 开启mysql的执行计划,或者开启mysql的慢查询日志,slow_query_log=on  以mysqldumpslow工具分析。

4如果是语句等待的时间长 优化mysql的参数比如线程数等。

5如果是mysql执行的时间长,就是sql语句优化问题和表结构问题,如查询的表关联多,没有使用索引或者索引设置不合理,sql语句没有优化

6如果仍然没有解决,在用sql的基准测试工具sysbench检查mysql服务器的性能瓶颈,主要有

吞吐量:mysql执行事务的能力

并发性:mysql执行sql语句的效率

执行时间

满足要求 就再用此流程优化 如果不满足 就增加服务器的硬件。



分步处理:

1mysql服务器分析:

1 show status;显示mysql工作状态

queries 当前的查询数量

threads_connected 进程的连接数

threads_running 进程的工作数;   

2文本分析工具awk

mysqladmin -uroot ext|awk '/Queries/{q=$4}/Threads_connected/{$c=$4}/threads_running/{r=$4}'

执行一次返回出上面3个参数的状态,就可以分析系统的状态

3请求工具  httpd的ab工具 模拟请求 并发数 总次数


2mysql进程状态分析 (mysql5.0版本之后)

set profiling =1;开启mysql的执行计划 为每条mysql语句分析详细的过程

show processlist 执行计划 可以查到执行的时间和执行的sql

show profile for query 2; 为第二个sql执行计划。

show profilings ; 记录分析的语句

sysbench 基准测试工具 sysbench --test=olpt (desc 描述表的结构)

需要注意的几个mysql状态参数:

converting HEAP to MyISAM 查询结果太大时把结果放在临时表中 消耗大量的存储空间和查询时间

create tmp table 创建临时表 group时存储中间数据

Copying to tmp table on disk 内存的临时表复制到磁盘

locked 被其他查询锁住

logging slow query 记录慢查询


3具体的优化措施

    表的优化和列类型的选择

1.字段类型的优先级:整型>date,time>enum>char,varchar>blob

2.大的字段浪费内存

3.避免用NULL 不利于索引,需要用特殊字节标注占用磁盘空间

4.enum列内部是整型存储 enum列与enum列关联速度最快,性别学历配置等建议用此

     索引优化

     1 B-tree索引 用的是平衡树 二叉树 myisam和innodb默认的索引

排好序的能快速查找的结构

     2hash索引

memory 默认hash索引

在磁盘上随机放置数据,无法对范围进行优化,无法用到前缀索引 排休无法优化

必须回行,通过索引拿到数据后还要在表中取到数据

     


      b-tree索引的:

1在where条件上都加上索引,多列上独立的索引 可能只导致使用一个索引。

2多列上建立索引及联合索引,并且满足左前缀要求。

左前缀要求:从左到右

index(a,b,c) a,b,c三列上建立了索引,逐个发挥作用,a索引下建立b索引建立c索引 只有

依次命中索引,后面的列才能命中索引,否则导致索引不连贯 后面的索引级就不能再使用了。

连续的where条件 mysql在不影响查询结果下会自动调优 满足左前缀规则

左前缀索引实战:

reset query cache;清除mysql的查询缓存

alter table goods add index(cat_id,shop_price);

explain select goods_id,shop_price from goods where cat_id =3 and shop_price>3000;


 create table index_index (

    -> c1 char(1) not null default '',

    -> c2 char(1) not null default '',

    -> c3 char(1) not null default '',

    -> c4 char(1) not null default '',

    -> c5 char(1) not null default '',

    -> key(c1,c2,c3,c4,c5)

    -> )engine innodb charset utf8;

 key 建立联合索引



 explain语句分析sql语句执行情况 

key_len 参数 表示用到的索引个数 一个utf占3位 所以次数要除以三  比如用12 用到的索引个数就是12/3 等于4 

rows 扫描的行数

extra using  index 使用索引覆盖。 


 

myisam和innodb索引的区别:

innodb的(次索引)索引:次级索引指向对主键的引用 ,主键索引(主索引)上每个二叉树的叶子节点直接保存索引的值和数据 次级索引上二叉树叶子节点指向对主键的引用

比如 主键索引上 id为7的二叉树的叶子节点上保存着次行的所有数据,name的二叉树上直接就保存着主键id值,用过主键id直接在主索引就能找到数据。

聚簇索引:

索引的值和此行的数据保存在一起,通过主键查询就能快速定位数据,次索引指向对主键的引用,通过次级索引需要查找到主键id再从主键id找到数据。 

没有主键时 Unique key做主键,或者内部生成rowid做主键。

当叶子节点分裂时,叶子节点保存着数据,节点改变性能消耗较大,所以主键尽量使用整型并且递增,如果无规律的就会产生叶的分裂。

数据量大:1字段类型比较大,存储的数据比较大  2字段数量比较多  这时数据存放在磁盘中,每个叶子节点的数据存放在几个磁盘块中,跨块查询就比较慢

select id from person order by id; //这时查询速度比较慢

select id from person order by id,var //复合索引 id 和 var 叶子节点只存放这主键的依赖,并没有每一行的数据,所以就比较快


myisam的索引:myisam的次索引和主索引指向物理行 每个二叉树上叶子节点都有指向磁盘 物理行的指针和此索引的值,所以查找索引其他的数据就是回行查找,在磁盘中查找的效率比较低

      当叶子节点分裂时,因为每个叶子节点只保存物理行的指针,消耗的性能就很少

覆盖索引:

      如果查询的列恰好是索引的一部分,查询只需要再索引的文件进行,不需要再磁盘上回行查找,查询速度非常快。

      查询的结果索引已经覆盖住,所以叫覆盖索引,或者索引覆盖。


高性能索引优化:


好的索引:

查询频繁  区分度高  长度小  能覆盖常用查询字段

区分度高和长度小是矛盾的


某个字段建立索引:

测试: 为t2表中字段word建立索引 

select count(distinct left(word,1)) / count(*) from t2;

建立索引的字段截取的长度和总长度的比例 一般达到0.1就能够接受 如word最大长度为14 当截取的数字为4-5时 就能达到0.9时 就按照word的左4位建立索引

alert table t2 add index word(word(4));//建立名字为word的索引 占用word字段的左4位

左前缀不易区分的列,建立索引:

如 URL

1倒序存放 左前缀的区别度就高

2伪hash效果  crc32()函数来构造一个伪hash列 把字符串的列转成整型,降低索引的长度




大数据量分页效果:

从业务上优化  不允许翻到100页

limit 查询是逐行查找 先查找 再跳行 大数据分页时就有很长的查询时间

解决方法,1.跳过前面的数据   where id> 100000  limit 50;(前提是id完整 没有物理删除)(一般来说 网站的数据是不物理删除的  只做逻辑删除)。

  2.延迟关联 先查询出满足的id,再通过id查询出对应的数据  id是主键 百万级分页 从6-7秒 优化到1-2秒

  select * from a inner join (select id from a limit 500000,10) as tmp on id=tmp.id;

 

索引与排序:

排序可能出现的情况:

1.覆盖索引直接在索引上查询,就是有序的 useing index

2.先取出数据,形成临时表做filesort(文件排序,有可能在磁盘上,也有可能在内存中)

争取取出来的数据就是有序的,利用索引来排序

比如:goods表 cat_id 和price 组成联合索引

where cat_id =n order_by price 可以利用索引来排序,左前缀规则(using  where) 如果不是联合索引 就不能索引排序  using filesort


 重复索引和冗余索引:

重复索引:在同一个列或者顺序相同的几个列上建立多个索引,重复索引没有任何帮助,增大索引文件

冗余索引:2个索引覆盖的列有重叠  index x(x) index xm(x,m)  x和xm索引重叠,允许存在


索引碎片和维护:

长期的数据更改中,索引文件和数据文件都会产生空洞,形成碎片

nop (不产生对数据实质性影响的操作)操作修改表,比如  alert table goods engine inoodb; 会重新规划数据,表比较大时,非常耗费资源,周期按业务确定




mysql语句优化:


查询慢:等待时间和执行时间


业务层:重构查询 切分查询  分解查询 


explain:

id : 查询的编号

select_type:simlpe  简单查询 primary子查询或者派生查询

table:表名(别名)


type:查询的方式(重要)    

all(逐行查找,特别不好)        index(扫描所有的索引节点)       range (范围查询)  

ref(通过所引直接引用到数据行)  eq_ref(通过索引直接引用到一行)  const system null (常量数据 某一行,不再需要优化了)


possible_keys 可能用到的索引

key:最终用到的索引

key_len:索引长度  越短速度越快

rows :估计扫描的行


extra: using index(最好)   using where    using  temporary  using filesort 


explain误区:

explain不产生查询,复杂的还是要产生查询(5.6版本就不产生查询)   explain是执行过程(explain 不是执行过程)



in子查询陷阱:

select id,name from a where id in ();

先扫面a中的id,再b表中查询是否存在,直接扫描了a表中的所有数据。

用连接查询

select id,name from a inner join b on a.id=b.id;

select id,name from a inner join (select id from b where id =6) as tmp  on tmp.id=b.id;


exists查询:

in 和 join exists 效率要看业务逻辑,在用mysql的执行计划分析,不要扫描更多的表 不要零时表 不要文件排序


max min 优化技巧:

select id from a use index(primary) where pid =100 limit 1;

直接使用主键索引,按主键索引排序 就取出pid为100的最小的值 但是不建议这么做,语义模糊。


count 优化技巧:

count 总行数会缓存到内存中,所以没有条件的直接查询就会快

有条件的count 查询

取出大于等于100的个数

(select count(*) from a) - (select count(*) where id <100);


gorup by:

用于分组统计 不要筛选数据 使用所以避免临时表和文件排序




mysql主从集群设置:

主从: 主服务器建立授权复制账号,从服务器利用复制账号监听主服务器的日志

1主服务器建立二进制日志,没产生语句或者磁盘变化写进日志

2从服务器在主服务器中读取二进制日志,形成中继日志(relay_log),再写入

3保证主从服务器的3306端口互通

流程:

主服务器配置:

vim /etc/my.cnf 


#开启二进制日志,文件名

log-bin=mysql-bin


#指定日志格式

binlog-format=mixed #statement:语句变化 row:行的变化  mixed:混合的

#配置server_id 唯一的服务器id

server-id = 153 #一般配置为ip的最后位

#建立授权账号

mysql -uroot -p

grant replication client,replication slave on *.* to 'repl'@'192.168.%.%' indenified by 'repl';


#二进制日志的文件路劲

cd /var/lib/mysql


#查看主服务器的状态,

#show master status;  从服务器配置需要下面两个参数

File:mysql-bin.000003(6位) 显示出当前的二进制文件

Posistion:278 显示出二进制日志的时间点


#从服务器配置:

vim /etc/my.cnf 

server-id=200;

ralay-log=mysql-relay


#链接主服务器

mysql -uroot -p


#告诉从服务器主服务器的信息        主服务器的二进制文件               二进制时间点

change master to master_host='47.52.111.153',master_user='repl',master_password='repl',master_log_file='mysql-bin.000003',master_log_pos=278;


#查看从服务器的状态

show slave status /G;


#重置slave状态

reset slave 


#启动slave 开始监听master的变化

start slave 


#停止slave状态

stop slave  




主服务器日志格式:

statement  复制语句 :很多行的数据都会变化,用语句的格式

row        复制磁盘 :修改或者新增一行数据,对其他行没有影响,直接复制磁盘1行的变化

mixed      混合的   :语句不通自动选择混合的格式



mysql主主复制:

两台服务器能相互复制

    思路:

2台服务器都设置二进制日志和relay-log中继日志

都设置replication 账号

都设置对方为自己的master

   

    配置:

一主:

server-id=199

log-bin=mysql-bin

binlog-format=mixed

relay-log=mysql-relay

grant replication client,replication slave on *.* to 'repl'@'192.168.%.%' indenified by 'repl';

change master to master_host='47.52.111.153',master_user='repl',master_password='repl',master_log_file='mysql-bin.000003',master_log_pos=278;



二主:

server-id=200

log-bin=mysql-bin

binlog-format=mixed

relay-log=mysql-relay

grant replication client,replication slave on *.* to 'repl'@'192.168.%.%' indenified by 'repl';

change master to master_host='47.52.111.153',master_user='repl',master_password='repl',master_log_file='mysql-bin.000003',master_log_pos=278;



注意:主主复制必须要防范主键冲突问题(两台服务器的解决方法)

global:全局生效 session:是当前链接生效

一台服务器1357增长  

set global auto_increment_incrementment=2;//增长步长

set global auto_increment_offset=1;//开始增长位

set session auto_increment_incrementment=2;

set session auto_increment_offset=1;

一台服务器2468增长

set global auto_increment_incrementment=2;

set global auto_increment_offset=2;

set session auto_increment_incrementment=2;

set session auto_increment_offset=2;

多台服务器的解决方法:

业务上解决:设计一个redis,构建global:userid

每次插入前 incr->global:userid 得到不重复的userid 


被动主主复制:

指两台服务器地位一样,但是其中一台为只读,并且业务中也只写某一台服务器,用于故障切换

一台服务器 配置:

read-only=on;


路由到sql语句上,集群中间件:

mysql_proxy 



官方mysql_proxy中间件实现mysql的负载均衡和读写分离:

1下载: wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz

2解压:tar axvf 

3帮助文档  ./bin/mysql-proxy --help-all


4创建连接

./bin/mysql-proxy -p 47.52.111.153:4040 

--proxy-backend-addresses=47.52.111.153:3306 #一台服务器 

--proxy-backend-addresses=47.52.111.153:3306 #另一台服务器


5连接mysql-proxy

mysql -h 47.52.111.153 -uroot -p  -P 4040 


6 4条插入语句没有均衡到两台服务器上(insert into ):

均衡:不是指语句单位的均衡,容易带来数据不一致,指的是连接的均衡。

      100次连接命中到不同服务器上,然后每个连接在相同的数据库服务器上操作


7创建读写分离

./bin/mysql-proxy 

-b 47.52.111.153:3306  -b是proxy-backend-addresses简写

-r 47.52.111.153:3306  -r是proxy-read-only-backend-address简写,注明这是只读服务器

-s /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-spliting.lua  -s是proxy-lua-script简写,注明是利用哪个脚本进行读写分离  

-daemon  后台运行





表分区:

partition 从mysql层来进行分表,而不是从业务层进行分表,mysql存储时按照规律存放到不同的文件上,但是显示时还是一张表


范围分区,创建表的时候指定:

CREATE TABLE topic(

tid INT PRIMARY KEY AUTO_INCREMENT,

title CHAR(20) NOT NULL DEFAULT ''

)ENGINE = INNODB CHARSET utf8

PARTITION BY RANGE(tid)(

PARTITION t0 VALUES LESS THAN(10),

PARTITION t1 VALUES LESS THAN(20),

PARTITION t2 VALUES LESS THAN(30),

PARTITION t3 VALUES LESS THAN(MAXVALUE)

);

说明:

表示以替代分区 低于10的放在t0分区 低于20的放在t1分区  MAXVALUE表示常量,指最大的值


区域分区:

1创建一张区域表

CREATE TABLE AREA(

aid INT PRIMARY KEY AUTO_INCREMENT,

zone CHAR(20) NOT NULL DEFAULT ''

)ENGINE INNODB CHARSET utf8;


2插入数据

insert into area (zone) values ("北京"); 

insert into area (zone) values ("上海"); 

insert into area (zone) values ("重庆"); 

insert into area (zone) values ("天津"); 



3创建用户表

CREATE TABLE USER(

uid INT  ,

NAME CHAR(20) NOT NULL DEFAULT '',

aid INT PRIMARY KEY

)ENGINE INNODB CHARSET utf8

PARTITION BY LIST(aid)(

PARTITION t1 VALUES IN(1),

PARTITION t2 VALUES IN(2),

PARTITION t3 VALUES IN(3),

PARTITION t4 VALUES IN(4)

);

注:分区列不要为null 否则mysql会理解为0


表中有主键时,分区键也要加入主键 形成复合主键

ALTER TABLE `user` ADD PRIMARY KEY(uid,aid);

ALTER TABLE `user` CHANGE uid uid INT NOT NULL AUTO_INCREMENT;


事务:

原子性:sql语句要么全部成功要么全部失败

一致性:执行前和执行后的状态不变

隔离性:事务进行中其他事务看不到效果

read uncommitted:未提交度 脏读

read committed : 提交度

repeatable read:可重复读 一个事务过程中 所有的信息都是事务开始一瞬间的信息,不受其他已提交的事务的影响 这个是mysql默认的隔离级别

serializable 可串行化,每行数据都

持久性:事务提交后不能撤销,只能通过补偿性事务抵消效果

查看隔离级别:

SHOW  VARIABLES LIKE '%isolation%'


开启事务:

start transaction

执行 


提交

commit rollback