mysql笔记

Q: 如何提高MySQL的性能?
A: 需要优化,则说明效率不够理想.
因此我们首先要做的,不是优化,而是---诊断.
治病的前提,是诊病,找出瓶颈所在. CPU,内存,IO? 峰值,单条语句?

PS:AWK脚本

Awk是一个简便的直译式的文本处理工具.
擅长处理--多行多列的数据

处理过程:
While(还有下一行) {
1:读取下一行,并把下一行赋给$0,各列赋给$1,$2...$N变量
2: 用指定的命令来处理该行
}

awk命令形式:
awk [-F|-f|-v] ‘BEGIN{} //{command1; command2} END{}’ file
[-F|-f|-v] 大参数，-F指定分隔符，-f调用脚本，-v定义变量 var=value
' ' 引用代码块
BEGIN 初始化代码块，在对每一行进行处理之前，初始化代码，主要是引用全局变量，设置FS分隔符
// 匹配代码块，可以是字符串或正则表达式
{} 命令代码块，包含一条或多条命令
； 多条命令使用分号分隔
END 结尾代码块，在对每一行进行处理之后再执行的代码块，主要是进行最终计算或输出结尾摘要信息

特殊要点:
$0 表示整个当前行
$1 每行第一个字段
NF 字段数量变量
NR 每行的记录号，多文件记录递增
FNR 与NR类似，不过多文件记录不递增，每个文件都从1开始
\t 制表符
\n 换行符
FS BEGIN时定义分隔符
RS 输入的记录分隔符， 默认为换行符(即文本是按一行一行输入)
~ 匹配，与==相比不是精确比较
!~ 不匹配，不精确比较
== 等于，必须全部相等，精确比较
!= 不等于，精确比较
&&　 逻辑与
|| 逻辑或
+ 匹配时表示1个或1个以上
/[0-9][0-9]+/ 两个或两个以上数字
/[0-9][0-9]*/ 一个或一个以上数字
FILENAME 文件名
OFS 输出字段分隔符， 默认也是空格，可以改为制表符等
ORS 输出的记录分隔符，默认为换行符,即处理结果也是一行一行输出到屏幕
-F'[:#/]' 定义三个分隔符

尽管操作可能会很复杂，但语法总是这样，其中 pattern 表示 AWK 在数据中查找的内容，而 action 是在找到匹配内容时所执行的一系列命令。花括号（{}）不需要在程序中始终出现，但它们用于根据特定的模式对一系列指令进行分组。 pattern就是要表示的正则表达式，用斜杠括起来。

awk语言的最基本功能是在文件或者字符串中基于指定规则浏览和抽取信息，awk抽取信息后，才能进行其他文本操作。完整的awk脚本通常用来格式化文本文件中的信息。

通常，awk是以文件的一行为处理单位的。awk每接收文件的一行，然后执行相应的命令，来处理文本。

awk工作流程是这样的：读入有'\n'换行符分割的一条记录，然后将记录按指定的域分隔符划分域，填充域，$0则表示所有域,$1表示第一个域,$n表示第n个域。默认域分隔符是"空白键" 或 "[tab]键",所以$1表示登录用户，$3表示登录用户ip,以此类推

具体详解：http://www.cnblogs.com/xudong-bupt/p/3721210.html

一：mysql性能优化的思路

　　最好的优化--不查询！

如果一台服务器出现长时间负载过高 /周期性负载过大,或偶尔卡住如何来处理?

答:大的思路--------
是周期性的变化还是偶尔问题?
是服务器整体性能的问题, 还是某单条语句的问题?
具体到单条语句, 这条语句是在等待上花的时间,还是查询上花的时间.
唯一的办法-----监测并观察服务器的状态.

1:观察服务器状态, 一般用如下2个命令
Show status; Show processlist;
例: mysql> show status;
#mysqladmin ext

2.不规则的延迟现象往往是由于效率低下的语句造成的,如何抓到这些效率低的语句.可以用show processlist命令长期观察,或用慢查询.

Show processlist;
这个命令是显示当前所有连接的工作状态,

如果state的状态有如下的值，则需要注意：

converting HEAP to MyISAM 查询结果太大时,把结果放在磁盘 (语句写的不好,取数据太多)
create tmp table 创建临时表(如group时储存中间结果,说明索引建的不好)
Copying to tmp table on disk 把内存临时表复制到磁盘 (索引不好,表字段选的不好)
locked 被其他查询锁住 (一般在使用事务时易发生,互联网应用不常发生)
logging slow query 记录慢查询。

具体查询到某条语句后，可以分析一下是哪一部分慢，mysql 5.5 以后加了一个profile设置,可以观察到具体语句的执行步骤。

     查看是否开启：Show  variables like ‘profiling’ --->set profiling=on;

　　show profiles;-->找到所需语句的queryid，-->  show profile for query queryid;

二：MySQL如何使用内部临时表

在处理请求的某些场景中,服务器创建内部临时表. 即表以MEMORY引擎在内存中处理,或以MyISAM引擎储存在磁盘上处理.如果表过大,服务器可能会把内存中的临时表转存在磁盘上.

用户不能直接控制服务器内部用内存还是磁盘存储临时表

临时表在如下几种情况被创建:

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

explain select * from users group by duty_id(没有索引)

 

2.如果order by 与group by为不同列时，或者连表查询的时候，order by 或者group by的列不是驱动表的字段，会产生临时表。

3.distinct 与order by 一起使用可能会产生临时表

如果一开始在内存中产生的临时表变大,会自动转化为磁盘临时表. 内存中临时表的最大值为tmp_table_size和max_heap_size中较小值.
这和create table时显示指定的内存表不一样:这些表只受max_heap_table_size系统参数影响

当服务器创建内部临时表(无论在内存还是在磁盘),create_tmp_tables变量都会增加.
如果创建了在磁盘上内部临时表(无论是初始创建还是由in-memory转化),
create_tmp_disk_tables 变量都会增加.

建表: 表结构的拆分,如核心字段都用int,char,enum等定长结构
非核心字段,或用到text,超长的varchar,拆出来单放一张表.

建索引: 合理的索引可以减少内部临时表(索引优化策略里详解)

写语句: 不合理的语句将导致大量数据传输以及内部临时表的使用.

三：表的优化与列选择的类型 

　　1.如果表的字段过多，可以考虑垂直拆分，拆分的原则是：

　　　　1.1定长与变长分离

　　　　　int整型是占用4个字节,char(4)占4个字符的长度，timestamp占4个字节，核心且常用的字段，宜建成定长放在一张表里，像超大的varchar,text,blob

　　　　　　这种变长字段,适合单放一张表, 用主键与核心表关联起来.

　　　　1.2 常用的字段和不常用的字段分离

　　　　　　需要结合网站具体的业务来分析,分析字段的查询场景,查询频度低的字段,单拆出来,有时候也可以防止select * 查询数据过多，导致网络传输时间过长。

　　　　1.3 合理增加冗余字段

　　　　　　看如下BBS的效果每个版块里,有N条帖子, 在首页,显示了版块信息,和版块下的帖子数.这是如何做的?　　　　　　

　　如果board表只有前2列,则需要取出版块后,再查post表,select count(*) from post group by board_id,得出每个版块下的帖子数.如果有postnum字段,每发一个帖子时,对postnum字段+1; 再查询版块下的帖子数时, 只需要1条语句直接查boardid,select boradid, boardname,postnum from board;典型的”空间换时间”

　　      1.4列选择的策略

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

　　　　　　列的特点分析:

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

                   time定长,运算快,节省空间. 考虑时区,写sql时不方便 where > ‘2005-10-12’;
                  enum: 能起来约束值的目的, 内部用整型来存储,但与char联查时,内部要经历串与值的转化
                  Char 定长, 考虑字符集和(排序)校对集
                  varchar, 不定长 要考虑字符集的转换与排序时的校对集,速度慢.
                  text/Blob 无法使用内存临时表

　　　　　　性别: 以utf8为例
　　　　　　char(1) , 3个字长字节
　　　　　　enum(‘男’,’女’); // 内部转成数字来存,多了一个转换过程
　　　　　　tinyint() , // 0 1 2 // 定长1个字节.

　　　　　 2: 够用就行,不要慷慨 (如smallint,varchar(N))

                  原因: 大的字段浪费内存,影响速度,

　　　　　　以年龄为例 tinyint unsigned not null ,可以存储255岁,足够. 用int浪费了3个字节,以varchar(10) ,varchar(300)存储的内容相同, 但在表联查时,varchar(300)要花更多内存

　　　　　3: 尽量避免用NULL()

                原因: NULL不利于索引,要用特殊的字节来标注,在磁盘上占据的空间其实更大.统计不到

　　　　　Enum列的说明

　　　　   1: enum列在内部是用整型来储存的
              2: enum列与enum列相关联速度最快
              3: enum列比(var)char 的弱势---在碰到与char关联时,要转化. 要花时间.
              4: 优势在于,当char非常长时,enum依然是整型固定长度.当查询的数据量越大时,enum的优势越明显.

　　　　  5: enum与char/varchar关联 ,因为要转化,速度要比enum->enum,char->char要慢,但有时也这样用-----就是在数据量特别大时,可以节省IO.

             无论enum(‘manmaman’,’womanwomanwoman’) 枚举的字符多长,内部都是用整型表示, 在内存中产生的数据大小不变,而char型,却在内存中产生的数据越来越多.

　　总结: enum 和enum类型关联速度比较快
　　Enum 类型 节省了IO

 

四:索引优化策略

1:索引类型

1.1 B-tree索引

注: 名叫btree索引,大的方面看,都用的平衡树,但具体的实现上, 各引擎稍有不同,比如,严格的说,NDB引擎,使用的是T-tree，Myisam,innodb中,默认用B-tree索引

但抽象一下---B-tree系统,可理解为”排好序的快速查找结构”.

1.2 hash索引
在memory表里,默认是hash索引, hash的理论查询时间复杂度为O(1)

疑问: 既然hash的查找如此高效,为什么不都用hash索引?
答:
1:hash函数计算后的结果,是随机的,如果是在磁盘上放置数据,
以主键为id为例, 那么随着id的增长, id对应的行,在磁盘上随机放置.
2: 无法对范围查询进行优化.
3: 无法利用前缀索引. 比如 在btree中, field列的值“hellopworld”,并加索引
查询 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引. (左前缀索引)
因为hash(‘helloword’),和hash(‘hello’),两者的关系仍为随机
4: 排序也无法优化.
5: 必须回行.就是说 通过索引拿到数据位置,必须回到表中取数据

2: btree索引的常见误区
2.1 在where条件常用的列上都加上索引
例: where cat_id=3 and price>100 ; //查询第3个栏目,100元以上的商品
误: cat_id上,和, price上都加上索引.
错: 只能用上cat_id或Price索引,因为是独立的索引,同时只能用上1个.(一次查询只能用到1个索引)

联合的索引遵守左前缀的原则

为便于理解, 假设ABC各10米长的木板, 河面宽30米.
全值索引是则木板长10米,
Like,左前缀及范围查询, 则木板长6米,

自己拼接一下,能否过河对岸,就知道索引能否利用上.
如上例中, where a=3 and b>10, and c=7,
A板长10米,A列索引发挥作用
A板正常接B板, B板索引发挥作用
B板短了,接不到C板, C列的索引不发挥作用.

五:聚簇索引与非聚簇索引

　　myisam引擎是非聚簇索引，也就是说索引文件和数据文件不是聚在一块的，所以也就没有什么主索引和次索引的说法，都是从索引文件定位数据的位置，然后在磁盘上找到此位置的数据

　　innodb是聚簇索引，也就是说索引和数据都聚在了一块，也分为主索引和次索引，主键索引是一颗大的树，所有的数据都存在了这棵树的叶子节点上，而次索引的叶子节点上存储的是相对应的主键ID，然后ID再去主索引里找数据，次索引会比主索引多了一个步骤。

innodb的主索引文件上 直接存放该行数据,称为聚簇索引,次索引指向对主键的引用
myisam中, 主索引和次索引,都指向物理行(磁盘位置).

注意: innodb来说,
1: 主键索引 既存储索引值,又在叶子中存储行的数据
2: 如果没有主键, 则会Unique key做主键
3: 如果没有unique,则系统生成一个内部的rowid做主键.
4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

聚簇索引
优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)
劣势: 如果碰到不规则数据插入时,造成频繁的页分裂.

五：高性能索引策略

　　1.对于innodb而言,因为节点下有数据文件,因此节点的分裂将会比较慢.对于innodb的主键,尽量用整型,而且是递增的整型.

如果是无规律的数据,将会产生的页的分裂,影响速度.

　　2.索引覆盖:索引覆盖是指 如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.

这种查询速度非常快,称为”索引覆盖”

　   3.理想的索引：1:查询频繁 2:区分度高 3:长度小 4: 尽量能覆盖常用查询字段.

　　4. 索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多).
　　针对列中的值,从左往右截取部分,来建索引
　　　　1: 截的越短, 重复度越高,区分度越小, 索引效果越不好
　　　　2: 截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大--增删改变慢,并间影响查询速度.所以, 我们要在 区分度 + 长度 两者上,取得一个平衡.

　　　　惯用手法: 截取不同长度,并测试其区分度: select count(distinct left(word,6))/count(*) from dict; 

　　5:对于左前缀不易区分的列 ,建立索引的技巧

　　如 url列，http://www.baidu.com，http://www.zixue.it，列的前11个字符都是一样的,不易区分, 可以用如下2个办法来解决

　　1: 把列内容倒过来存储,并建立索引 Moc.udiab.www//:ptth Ti.euxiz.www//://ptth 这样左前缀区分度大,

　　2: 伪hash索引效果，同时存 url_hash列，或者MD5列，MD5列值是唯一的，对这个字段建立索引。

六：索引和排序

　　mysql有两种方式可以生成有序的结果，通过排序操作或者按照索引顺序扫描，如果explain的type列的值为index，则说明mysql使用了索引扫描来做排序（不要和extra列的Using index搞混了，那个是使用了覆盖索引查询）。扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录，但如果索引不能覆盖查询所需的全部列，那就不得不扫描一条索引记录就回表查询一次对应的整行，这基本上都是随机IO，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在IO密集型的工作负载时。

　　mysql可以使用同一个索引既满足排序，又用于查找行，因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的。只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向（倒序或升序，创建索引时可以指定ASC或DESC）都一样时，mysql才能使用索引来对结果做排序，如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序，order by子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，否则mysql都需要执行排序操作，而无法使用索引排序。

例子：这是一个联合索引。

1.explain select operator,city_id from sale_clue_queue where operator=132262 order by appoint_status,next_call_type-->

　可以看到并没有出现文件排序.

注意：where条件列可以不按照索引定义的顺序出现，不管按照什么顺序出现索引列，只要出现的索引列在索引定义顺序的列上能连起来就行，(也就是A and B 和 B and A 是一样的，mysql内部查询优化器会优化符合索引顺序的)但是order by列不同，出现顺序一定得按照索引定义的顺序，否则无法使用索引进行排序，（ order by A,B可以使用索引，order by B,A 就无法使用）如，把next_call_type和appoint_status交换一下，就会出现filesort，因为索引是按照定义时的顺序排序，order by列打乱这个排序顺序就无法使用索引进行排序了.

2.explain select operator,city_id from sale_clue_queue where operator=132262 order by next_call_type,appoint_status

3.排序的两个列虽然符合左前缀的原则，但是一个是升序，一个是降序，也无法使用到排序索引。

explain select operator,city_id from sale_clue_queue where operator=132262 order by appoint_status desc,next_call_type asc

4.下面这个查询在索引的第二列上有多个等于条件，对where 查询来说 in 操作 相当于常量操作，可以使用后续索引，但是对于orderby来说，in操作就是范围操作，是无法使用后续索引的。

explain select operator,city_id from sale_clue_queue where operator=132262 and appoint_status =3 order by next_call_type 

explain select operator,city_id from sale_clue_queue where operator=132262 and appoint_status in (1,2,3) order by next_call_type

索引碎片与维护

　　在长期的数据更改过程中, 索引文件和数据文件,都将产生空洞,形成碎片.我们可以通过一个nop操作(不产生对数据实质影响的操作), 来修改表.比如: 表的引擎为innodb , 可以 alter table xxx engine innodb,---->optimize table 表名

注意: 修复表的数据及索引碎片,就会把所有的数据文件重新整理一遍,使之对齐.这个过程,如果表的行数比较大,也是非常耗费资源的操作.所以,不能频繁的修复.如果表的Update操作很频率,可以按周/月,来修复.不频繁,可以更长的周期来做修复.

七：sql语句优化

1: sql语句的时间花在哪儿?

　　答: 等待时间 , 执行时间.

　　这两个时间并非孤立的, 如果单条语句执行的快了,对其他语句的锁定的也就少了.所以,我们来分析如何降低执行时间.

2: sql语句的执行时间,又花在哪儿了?
答：
a: 查 ----> 沿着索引查,甚至全表扫描
b: 取 ----> 查到行后,把数据取出来(sending data)

3: sql语句的优化思路？
答: 不查, 通过业务逻辑来计算,
比如论坛的注册会员数,我们可以根据前3个月统计的每天注册数, 用程序来估算.

少查, 尽量精准数据,少取行. 我们观察新闻网站,评论内容等,一般一次性取列表 10-30条左右.

必须要查,尽量走在索引上查询行.

取时, 取尽量少的列.
比如 select * from tableA, 就取出所有列, 不建议.
比如 select * from tableA,tableB, 取出A,B表的所有列.

4.经常查看查询计划 explain 

八：事务

事务的4个特性:ACID 原子性(Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

以银行汇款为例,张三给李四转款300元：

原子性: 是指某几句sql的影响,要么都发生,要么都不发生.即:张三减300, 李四+300 , insert银行流水, 这3个操作,必须都完成,或都不产生效果。

一致性: 事务前后的数据,保持业务上的合理一致.(汇款前)张三的余额+李四的余额 ====== (汇款后) 张三的余额+李四余额，比如: 张三只有280元, 280-300=-20,储蓄卡不是信用卡,不能为负,因此张三余0元.将导致, 汇款后,2者余额,汇款前,差了20元.

隔离性: 在事务进行过程中, 其他事务,看不到此事务的任何效果.

持久性: 事务一旦发生,不能取消. 只能通过补偿性事务,来抵消效果

myisam引擎不支持事务, innodb和BDB引擎支持

事务的隔离级别：

read uncommitted:  读未提交的事务内容,显然不符原子性, 称为”脏读”. 在业务中,没人这么用

read commited:   在一个事务进行过程中, 读不到另一个进行事务的操作,但是,可以读到另一个结束事务的操作影响

repeatable read: 可重复读,即在一个事务过程中,所有信息都来自事务开始那一瞬间的信息,不受其他已提交事务的影响. (大多数的系统,用此隔离级别)

serializeable 串行化 , 所有的事务,必须编号,按顺序一个一个来执行,也就取消了冲突的可能.这样隔离级别最高,但事务相互等待的等待长. 在实用,也不是很多.

九：mysql 读写分离  ,mysql_proxy实现

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

1: 利用mysql_proxy实现负载均衡
执行mysql_proxy
./mysql-proxy-path/bin/mysql-proxy \
--proxy-backend-addresses=192.168.1.199:3306 \
--proxy-backend-addresses=192.168.1.200:3306

2: 连接mysql_proxy,用mysql客户就可以,因为proxy是mysql的前端代理
注意proxy的端口是4040
mysql -h “proxy的IP” -P 4040 -u username -p password

在连接上之后,做sql查询,却总是往某1台mysql server来发送------负载均衡没体现出来?
答: 不是没体现出来.
均衡不是体现在sql语句,一会请求mysqlA服,一会请求mysqlB服.
均衡是体现”连接”的均衡上.,

mysql_proxy会把连接mysql服务器的tcp/IP连接缓存进连接池,以提高性能.
在缓存池里, 缓存的连接大致是平均分配在每台mysql服务器上.
但具体的每一个连接,始终连某台服务器.

./bin/mysql-proxy \
--proxy-backend-addresses=192.168.1.199:3306 \
--proxy-read-only-backend-addresses=192.168.1.200:3306 \
--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

简写:
./bin/mysql-proxy -b=192.168.0.199:3306 -r=192.168.0.200:3306 -s=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

十：表分区

当一张表的数据非常多的时候,比如单个.myd文件都达到10G, 这时,必然读取起来效率降低.可不可以把表的数据分开在几张表上?

1: 从业务角度可以解决. (分表)

比如, 通过id%10 , user0 , user1....user9, 这10张表,根据不同的余数,来插入或查询某张表.

2: 通过mysql的分区功能

mysql将会根据指定的规则,把数据放在不同的表文件上,相当于在文件上,被拆成了小块.但是,给客户的界面,还是1张表.

常用的规则:
根据某列的范围来分区, 也可以某列的散点值来分区.

示例: 按列的范围来分区
以用户表为例, uid
uid [1,10) ---> user partition u0
uid[10, 20) ---> user partition u1
uid [20, MAX] --> user partion u2

分区 按range分区
create table goods (
id int,
uname char(10)
)engine myisam
partition by range(id) (
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than MAXVALUE
);

create table user (
uid int,
pid int,
uname
)engine myisam
partition by list(pid) (
partition bj values in (1),
partition ah values in (2),
partition xb values in (4,5,6)
);

注意: 在使用分区的时候,注意,分区的那个列,值不要为NULL
(如果不小心为NULL,mysql为理解为0,尽量执行之)