mysql优化的个人经验总结

矫情吗；* 2022-05-26 07:46 300阅读 0赞

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版本之后）  
1.set profiling =1;开启mysql的执行计划 为每条mysql语句分析详细的过程  
2.show processlist 执行计划 这个命令是显示当前所有连接的工作状态.  
需要注意的state状态参数:  
1converting HEAP to MyISAM    查询结果太大时,把结果放在磁盘 (语句写的不好,取数据太多)  
2create tmp table             创建临时表(如group时储存中间结果,说明索引建的不好),临时表在内存中，数据特别大时会写入磁盘，数据磁盘异常爆满时考虑这个情况  
形成临时表的情况:  
2.1如果group by 的列没有索引,必产生内部临时表  
2.2如果order by 与group by为不同列时,或多表联查时order by ,group by 包含的列不是第一张表的列,将会产生临时表  
3.Copying to tmp table on disk   把内存临时表复制到磁盘 (索引不好,表字段选的不好)  
4.locked                         被其他查询锁住 (一般在使用事务时易发生,互联网应用不常发生)   
5.logging slow query             记录慢查询   
 3.show profiles;mysql 5.5 以后加了一个profile设置,可以观察到具体语句的执行步骤.  
 4.show profile for query 2; 为第二个sql执行计划。  
  
  
  
  
  
  
sysbench 基准测试工具 sysbench --test=olpt (desc 描述表的结构)  
  
  
cpu性能  
磁盘io性能  
调度程序性能  
内存分配及传输速度  
POSIX线程性能  
数据库性能(OLTP基准测试)  
  
  
下载:  
git clone  https://github.com/akopytov/sysbench.git  
cd sysbench-1.0  
./autogen.sh  
./configure --prefix=/usr/local/sysbench   
make && make install  
  
  
cp usr/local/sysbench/binsysbench  /usr/bin/  
  
  
测试命令： 查找20000以内的素数  /usr/bin/sysbench --test=cpu --cpu-max-prime=20000 --num-threads=4 run  
  
  
  
  
sysbench测试磁盘IO性能:  
  
  
创建10G的内容,供测试用: sysbench --test=fileio --file-total-size=10G prepare  
  
  
针对10G文件,做随机读写,测试IO:sysbench --test=fileio --file-total-size=10G --file-test-mode=rndrw run  
\--file-test-mode 还可以为  
seqwr：顺序写入  
seqrewq：顺序重写  
seqrd：顺序读取  
rndrd：随机读取  
rndwr：随机写入  
rndrw：混合随机读写  
测试顺序读：sysbench --test=fileio --file-total-size=10G --file-test-mode=seqrd run      
测试随机读：sysbench --test=fileio --file-total-size=10G --file-test-mode=rndrd run      
测试的结果显示：顺序读取的速度远远大于随机读，所以好好利用索引+where语句使mysql尽量达成顺序度。  
  
  
测试mysql事务性能:  
sysbench --test=/path/to/sysbench-source/tests/db/oltp.lua --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --mysql-db=test  --oltp-table-size=3000 --mysql-socket=/var/lib/mysql/mysql.sock prepare  
清除测试数据: sysbench --test=fileio --file-total-size=10G cleanup  
  
  
  
  
ab压力测试工具  
安装   
  
  
  
  
  
  
3具体的优化措施  
    表的优化和列类型的选择  
1.字段类型的优先级:整型>date，time>enum>char,varchar>blob  
  
  
整型: 定长,没有国家/地区之分,没有字符集的差异  
time定长,运算快,节省空间. 考虑时区,写sql时不方便 where > ‘2005-10-12’;  
enum: 能起来约束值的目的, 内部用整型来存储,但与char联查时,内部要经历串与值的转化  
Char 定长, 考虑字符集和(排序)校对集  
varchar, 不定长 要考虑字符集的转换与排序时的校对集,速度慢.  
text/Blob 无法使用内存临时表  
  
1.2.大的字段浪费内存  
1.3.避免用NULL 不利于索引，需要用特殊字节标注占用磁盘空间  
1.4.enum列内部是整型存储 enum列与enum列关联速度最快，性别学历配置等建议用此  
1.5.表结构的拆分,如核心字段都用int,char,enum等定长结构  
1.6.非核心字段,或用到text,超长的varchar,拆出来单放一张表  
  
  
2.表的优化  
1: 定长与变长分离  
如 id int, 占4个字节, char(4) 占4个字符长度,也是定长, time 即每一单元值占的字节是固定的.  
核心且常用字段,宜建成定长,放在一张表.而varchar, text,blob,这种变长字段,适合单放一张表, 用主键与核心表关联起来.  
  
  
2:常用字段和不常用字段要分离.  
需要结合网站具体的业务来分析,分析字段的查询场景,查询频度低的字段,单拆出来.  
  
  
3:合理添加冗余字段.  
  
  
  
  
     索引优化  
     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直接在主索引就能找到数据。  
聚簇索引：  
像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”  
索引的值和此行的数据保存在一起，通过主键查询就能快速定位数据，次索引指向对主键的引用，通过次级索引需要查找到主键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语句优化:  
  
  
  
  
查询慢:等待时间和执行时间  
  
  
1sql语句的时间花在：  
等待时间：  
执行时间：a: 查 ----> 沿着索引查,甚至全表扫描   
  b: 取 ----> 查到行后,把数据取出来(sending data)  
  
  
这两个时间并非孤立的, 如果单条语句执行的快了,对其他语句的锁定的也就少了.  
  
  
sql语句的优化思路？  
1.不查, 通过业务逻辑来计算,   
比如论坛的注册会员数,我们可以根据前3个月统计的每天注册数, 用程序来估算.  
  
  
2.少查, 尽量精准数据,少取行. 我们观察新闻网站,评论内容等,一般一次性取列表 10-30条左右.  
  
  
3.必须要查,尽量走在索引上查询行.  
  
  
取时, 取尽量少的列.  
比如  select \* from tableA,  就取出所有列, 不建议.  
比如  select \* from tableA,tableB, 取出A,B表的所有列.  
  
  
4: 如果定量分析查的多少行,和是否沿着索引查?  
答: 用explain来分析  
  
  
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'@'47.52.33.73' IDENTIFIED by 'repl123456';  
  
\#查看主服务器的状态,  
\#show master status;  从服务器配置需要下面两个参数  
File:mysql-bin.000003(6位) 显示出当前的二进制文件  
Posistion:278 显示出二进制日志的时间点  
  
  
\#二进制日志的文件路劲  
cd /usr/local/mysql/data  
  
  
  
  
\#从服务器配置:  
vim /etc/my.cnf   
server-id=200;  
relay-log=mysql-relay  
  
  
\#链接主服务器  
mysql -uroot -p  
  
  
  
\#告诉从服务器主服务器的信息        主服务器的二进制文件               二进制时间点  
change master to master\_host='47.52.111.153',master\_user='repl',master\_password='repl123456',master\_log\_file='mysql-bin.000001',master\_log\_pos=1042;  
  
  
\#查看从服务器的状态  
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'@'47.52.111.153' IDENTIFIED by 'repl123456';  
  
  
  
change master to master\_host='47.52.33.73',master\_user='repl',master\_password='repl123456',master\_log\_file='mysql-bin.000002',master\_log\_pos=120;  
  
  
  
  
二主:  
server-id=200  
log-bin=mysql-bin  
binlog-format=mixed  
relay-log=mysql-relay  
grant replication client,replication slave on \*.\* to 'repl'@'47.52.33.73' IDENTIFIED by 'repl123456';  
  
  
change master to master\_host='47.52.111.153',master\_user='repl',master\_password='repl123456',master\_log\_file='mysql-bin.000002',master\_log\_pos=858;  
  
  
  
  
注意:主主复制必须要防范主键冲突问题(两台服务器的解决方法)   
global:全局生效 session:是当前链接生效  
一台服务器1357增长    
set global auto\_increment\_increment=2;//增长步长  
set global auto\_increment\_offset=1;//开始增长位  
set session auto\_increment\_increment=2;  
set session auto\_increment\_offset=1;  
一台服务器2468增长  
set global auto\_increment\_increment=2;  
set global auto\_increment\_offset=2;  
set session auto\_increment\_increment=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  https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-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