mysql优化总结

清疚 2022-01-05 23:49 311阅读 0赞

一, 优化思路

1  拿到待优化的服务器，第一步是观察，可以通过脚本或者阿里云的统计图分析。或者使用top命令查询服务器信息。

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

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

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

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

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

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

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

执行时间 等

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

二, 分步处理

mysql服务器分析：

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\}END\{printf("%d %d %d\\n",q,c,r)\}' >>status.txt

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

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

ab -c 50 -n 200000 http://www.baidu.com 50个并发 200000个请求

mysql进程状态分析 （mysql5.0版本之后）

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

2.show processlist 执行计划 这个命令是显示当前所有连接的工作状态.

需要注意的state状态参数:

converting HEAP to MyISAM    查询结果太大时,把结果放在磁盘 (语句写的不好,取数据太多)

create tmp table             创建临时表(如group时储存中间结果,说明索引建的不好),临时表在内存中，数据特别大时会写入磁盘，数据磁盘异常爆满时考虑这个情况

形成临时表的情况:

group by 的列没有索引,必产生内部临时表

order by 与group by为不同列时,或多表联查时order by ,group by 包含的列不是第一张表的列,将会产生临时表

Copying to tmp table on disk   把内存临时表复制到磁盘 (索引不好,表字段选的不好)

locked                         被其他查询锁住 (一般在使用事务时易发生,互联网应用不常发生)

logging slow query             记录慢查询

3.show profiles;            mysql 5.5 以后加了一个profile设置,可以观察到具体语句的执行步骤.

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

也可以格式化输出：set @query\_id=2;

详细的展示消耗的时间组成部分

5.show full processlist  查看mysql查询的状态，一个查询生命周期中，状态会变化多次。

sleep：线程等待客户端发送新的请求

query：正在执行查询或者正在返回结果给客户端。

locked:在服务器层表示等待表锁，存储引擎层的锁不会显示在线程状态中。

analyzing and statistics 线程正在收集存储引擎统计信息，并生成查询的执行计划。

copying to tmp table \[on disk\] 结果集复制到临时表中，group by，order，union操作，on disk将内存表写入磁盘。

sorting result 线程正在对结果集进行排序。

sending data 线程可能在多个状态间传递数据，或者生成结果集 或者向客户端返回数据。

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

1.基准测试的内容：

cpu性能

磁盘io性能

调度程序性能

内存分配及传输速度

POSIX线程性能

数据库性能(OLTP基准测试)

2基准测试的指标：

吞吐量：单位时间内的事务处理数。

相应时间或者延迟：百分比响应时间。

并发性：工作中的并发操作，或者同时工作中的线程数或者连接数。

可扩展性：给系统增加一倍的资源，比如两倍的cpu数，就可以获得两倍的吞吐量，i/o密集型应用 或者是 cpu密集型应用

3安装

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/

4测试命令

查找20000以内的素数  /usr/bin/sysbench --test=cpu --cpu-max-prime=20000 --num-threads=4 run

4.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压力测试工具

五, 具体的优化措施

*  尽量使用可以正确存储数据的最小数据类型。
 *  简单就好，整型比字符串操作代价更低，因为字符集和校对规则（排序规则）使字符串比较比整型比较更复杂，登录ip保存为整型。ip保存为int 用 inet\_aton() 和 iner\_ntoa()相互切换。
 *   尽量避免null，不利于索引。

1. 表的优化和列类型的选择

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

int: 定长,没有国家/地区之分,没有字符集的差异

time:定长,运算快,节省空间. 考虑时区,写sql时不方便 where > ‘2005-10-12’;

enum:能起来约束值的目的, 内部用整型来存储,但与char联查时,内部要经历串与值的转化

Char:定长, 考虑字符集和(排序)校对集，会删除末尾的空格。密码的md5值。

varchar:不定长 要考虑字符集的转换与排序时的校对集,速度慢.需要一（255字节）到两个额外的字节保存长度。如果使用ROW\_FORMART = FIXED 创建表的的话 varchar使用定长存储 很浪费空间

text/Blob 无法使用内存临时表,会写入磁盘

大的字段浪费内存

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

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

表结构的拆分,如核心字段都用int,char,enum等定长结构

非核心字段,或用到text,超长的varchar,拆出来单放一张表

如果需要保存精度较高的，参与计算的 可以使用decimal 如果有特别的高的精度，比如小数点后好几位，可以使用bigint，乘于相应的倍数即可。

2.表的优化

1: 定长与变长分离

如 id int, 占4个字节, char(4) 占4个字符长度,也是定长, time 即每一单元值占的字节是固定的.

核心且常用字段,宜建成定长,放在一张表.而varchar, text,blob,这种变长字段,适合单放一张表, 用主键与核心表关联起来.

注意：⚠️ 如果使用ROW\_FORMART = FIXED 创建表的的话 varchar使用定长存储 很浪费空间

VARCHAR 需要1-2个字节记录字符串的长度  字符串<= 255 一个字节 > 255 两个字节

2:常用字段和不常用字段要分离.

需要结合网站具体的业务来分析,分析字段的查询场景,查询频度低的字段,单拆出来.

3:合理添加冗余字段.

六  索引

索引 是排好序的，帮助数据库快速获取数据的数据结构

创建索引：

CREATE \[UNIQUE\] INDEX indexName on tableName (columename(length));

ALTER  TABLE tableName  ADD \[UNIQUE\] INDEX  indexNanme on  columename(length));

删除索引：

DROP INDEX indexName on tableName;

1 .索引优点：

1.   大大减少服务器需要扫描的数据量
2.   帮助服务器避免排序和临时表
3.   将随机i/o 变为顺序i/o
4.

2. 索引介绍

B-tree索引,用的是平衡树 二叉树 myisam和innodb默认的索引， 排好序的能快速查找的数据结构

*  在where条件上都加上索引，多列上独立的索引 可能只导致使用一个索引。
 *  多列上建立索引及联合索引，并且满足左前缀要求。

左前缀要求：从左到右

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;

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

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

rows 扫描的行数

extra using  index 使用索引覆盖。

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 建立联合索引

hash索引, memory 默认hash索引,在磁盘上随机放置数据，无法对范围进行优 ,无法用到前缀索引 ,必须回行，通过索引拿到数据后还要在表中取到数据

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通信协议：

‘半双工’通信，在任一时刻，要么是由服务器像客户端发送数据，要么是客户端像服务器发送数据，不能同时发生。

mysql需要等所有的数据都返回给客户端才会释放此查询占用的资源，所以增加limit是有效的限制。

九   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是mysql优化器对用户查询sql进行优化。

explain  表的读取顺序，数据读取操作的操作类型，哪些索引可以被使用，实际使用了哪些索引，表之间的引用，每张表有多少行被优化器查询

explain:

id ：表的读取顺序  查询的编号

相同 从上到下读取，

不同 从大到小读取

相同又不同 由大到小 从上到下读取

select\_type: 查询类型

simlpe  简单查询，不包含子查询或者union

primary 查询中有复杂的子查询，这是最外层的查询，类似于鸡蛋壳

subquery  select或者where 后中的子查询

derived   衍生查询 from 后包含的子查询，mysql会递归的执行这些子查询，放入临时表中。

union  若第二个select 出现在union之后，标记为union，若union包含在from子句的子查询中，外层的select被标记为derived

union result 从union表中获取结果的select

table:表名(别名)

type:访问的类型(重要)     性能由好到差。system > const > eq\_ref > ref > rang > index > all

all(逐行查找，特别不好)

index(扫描所有的索引节点   index )

range (通过索引 范围查询           between > < = )

ref(通过所引直接引用到所有的数据行 where id = 1 )

eq\_ref(唯一性索引扫描，对每个索引键，表中只有一条数据与之匹配 where id = 1)

const(表示通过索引一次就找到数据，primary key 或者unique索引，只匹配一次数据就得到)

system (表中的数据只有一行)

注意：优化达到range就可以满足要求了

possible\_keys 可能用到的索引

key：最终用到的索引 null没有用到索引，

如果命中索引覆盖，则该索引只出现在key中

key\_len：索引中使用的子节数，长度越短越好

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

ref : 显示索引中那个列被使用，可能是个常量，那些列或者长了被用于查找索引列上的值。

rows ：估计找到所需数据可能被扫描的行树，数据越少越好。

extra:

using index(最好)  表示select使用了covering index 覆盖索引，避免了访问数据行，效率比较高。如果同时存在using where 表示索引被用来执行索引键值的查找，如果没有存在，表示索引读取数据而非执行查找动作。

using where

using  temporary （很差）  使用临时表保存中间的结果，常见于order by 或者group by

using filesort 文件排序（很差） mysql会对数据使用一个外部的索引排序。而不是按照表内的索引顺序进行读取，也是就是mysql中无法利用索引完成的排序操作。

explain误区：

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

5：发现扫描大量的数据但是只返回了很少的行：

使用覆盖索引：把需要的列都放在索引中，存储引擎就不会回行查找。

改变表结构，比如使用汇总表。

重写复杂的查询。

重写复杂的查询：

1:把复杂的查询切分成小查询（切分查询）

比如定期删除数据，如果一次性执行会锁住很多数据，占满整个事务日志，阻塞很多小的但是有用的查询。

可以分批量，每次删除等待一段时间再做下一次删除，就可以把压力分布到很长的时间中，减少服务器的压力，减少删除时持有锁的时间。

2:分解关联查询：

如查询某个商品的信息：包含品牌，分类，价格，供应商等，可以分解为多条查询，程序匹配。

1让缓存的效率更高，mysql有自己的查询缓存，表改变少，就可以重复利用查询缓存。

2减少锁竞争。

3应用层关联，可以对数据库进行拆分，做到高性能和可扩展性。

4减少冗余记录的查询。

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][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 可串行化，每行数据都

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

脏读：事务提取到尾提交的数据。

幻读：事务读取某个范围的记录时，另一个事物又在该范围内插入新的记录，当事务再次读取该范围的记录时会产生幻行。

innodb解决死锁的方法：将持有最少行级排他锁的事务进行回滚。

innodb采用mvcc来支持高并发，通过间隙锁策略防止幻读的出现，它不仅锁定查询涉及的行，还会对索引中的间隙进行

查看隔离级别：

SHOW  VARIABLES LIKE '%isolation%'

开启事务:

start transaction

执行

提交

commit rollback

MVCC:

多版本并发控制，保存数据在某个时间点快照实现的。

[https_downloads.mysql.com_archives_get_file_mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz]: https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz