7月 092018
 

 

索引滥用

 

表索引情况如下:

我们发现,buss_no 开头的索引就有三个,从名字上来看,是个类似于唯一号的字段,从buss_no 的索引类型来看,不是唯一索引,很可能就是不唯一的;但是,直觉判断,不能看像类型、状态之类的字段有超大的重复度。实际分析发现,每个buss_no 不会超过10条记录;对于这种情况,下面的两个buss_no开头的联合索引就是非常多余的,因为后面的两个联合索引总是先定位buss_no,在定位其他字段的;由于buss_no已经把结果集缩小到10条记录一下了,对于10条记录集的数据量完全可以自己在程序中进行排序、查找等处理,大可不必再创建联合索引,这样会导致索引很大,现在这个表的索引数据情况为:

不难发现,索引空间比表数据空间都大很多了

 

注意: 有些情况下,使用的覆盖索引和这种情况有些许类似,但不可等而视之

 

不给唯一性小的字段创建索引

如: 类型、状态字段,值往往只有很少的几个,创建索引的意义不大

如何忽略指定索引

面对已经存在了大量索引,如何验证使用索引和不使用索引的执行效率差别的大小呢?

直接删掉不想用的索引自然是个办法,但是我们的操作可能是线上的,随意删除和创建索引太不专业了;幸好mysql早就帮我们想好了,我们可以在sql语句中指定是否启用和禁用指定的索引:https://www.cnblogs.com/lcngu/p/6023179.html

 

参考:

 Posted by at 上午 10:47
1月 132017
 

max_allowed_packet 定义的是所允许的单条sql语句的大小。

引用官方的说法:http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet


You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

解释一下:

  1. 该值的类型为integer,允许的最大值为1G (理论上4个字节的表达能力的上限是4G,也或许协议实现上硬编码了吧)
  2. 该变量的默认值为4MB,一般来讲是够的,如果存储大的BLOB列,可能不够,需要修改该配置
  3. 该值总是1KB的整数倍,最小值为1KB;修改的值应该是1024的整数倍,如果不是整数倍,则会按照小于该值的最接近的那个1024的整数倍进行截断处理,并产生1个warning;(注意: 如果设置的值小于1024,则自动调整为1024)如下:

    其实,将max_allowed_packet 设置为比 net_buffer_length 小的意义不大,所以,这里同样会出现一个warning,只是不会强制将max_allowed_packet修改为大于等于net_buffer_length的
    比较专业的设置该值的方法为:
  4. session的max_allowed_packet是不允许修改的,修改了全局配置对当前session也不会生效的,只有重新连接才能看到变化
 Posted by at 下午 12:00
1月 112017
 

一大早到公司,加班一宿的同事就跟我抱怨,说,一个表只有8w条数据,需要更新一列,根据目前进度估算,大概需要3个小时,即使不需要更新,查询每条数据也需要400ms左右;

或许确实配置太低,或许确实表的字段太多,我并没有过于吃惊,但是3小时确实太多,便一起查了一下:

  1. iops并不高,说明内存够用
  2. cpu很高,很可能没有合适的索引,说是用了索引了,还是确认一下吧
  3. 果然搞错了,没有用到索引
  4. 8w条数据,添加索引也应该很快,于是添加了一个索引,耗时不到10s
  5. 执行sql语句,还是很慢,explain显示可能能用到我们刚才添加的索引,但是rows上来看还是全表扫描,为什么?
  6. sql语句大致为: update table1 set bb=1 where aa=123
  7. 现在已经基本可以怀疑aa的类型应该不是int的了,查看表结构,果然aa的类型为varchar
  8. 修改sql语句:update table1 set bb=1 where aa=’123′
  9. 重新开始更新8w条数据,共耗时不到10s

分析:

因为aa是varchar类型,如果要和一个数字比较,势必要对aa进行转换,如此就用不到索引了;这种错误太容易出现了

 Posted by at 上午 9:45
7月 252016
 

httpry:

An open-source HTTP packet sniffing tool which captures live HTTP packets with libpcap library, and displays HTTP requests and responses in a human-readable format. It comes with a collection of parsing Perl scripts for mining various information from its standard output.

效果:

安装:

  1. linux下yum可以安装
  2. 其它系统编译安装,源码: https://github.com/jbittel/httpry (从源码来看,虽然是c写的,似乎可以写perl插件)

 

 

mysql sniffer: https://phpor.net/blog/post/9562

 Posted by at 下午 6:36
7月 042016
 

场景:

mysql导出导入大数据文件时,如果文件很大,导入时最好先把索引都去掉,如果导出时包含表结构,则在一个很大的文件上编辑表结构将非常麻烦,所以正确的做法:

1. 导出表结构(不包含数据)

2. 编辑表结构(去掉索引)

3. 创建表

4. 导出表数据 (不包含表结构)

5. 导入数据

6. 添加索引

 

附:

解压大文件直接到mysql:

如何知道灌到什么程度了?

tar 在解压的时候,使用的也是外部的解压程序(如:gzip),通过管道交互,可以查看gzip读的那个文件读到哪里了,如:

 

 Posted by at 下午 6:56
6月 102016
 

sql_mode 之影响:

  1.  sql_mode=’ansi’; 则当插入时,如果数据超长,则截断插入;有时候,这不是我们想要的

总结:

  • sql_mode=’TRADITIONAL’; 可以避免很多不必要的意外;

    TRADITIONAL 等价于 下面一系列标准的集合
  • 注意: 与innodb_strict_mode的区别

实际应用:

  1. 可以直接在 /etc/my.cnf 中修改sql_mode,如下:(需要重启mysql)
  2. 如果不方便重启mysql,则可以在执行sql语句前进行回话级别的设置,如下:

    (每次连接后都需要执行,比较麻烦;对于长连接的场景,该设置的花费可以忽略)
  3. 设置全局sql_mode:

    注意:
    1) 需要有超级权限
    2) 对当前session无效;要么退出再进来,要么再设置一下当前回话的sql_mode
    3)  该设置重启mysqld就没了,最好在my.cnf 中写一下

参考资料:

 Posted by at 下午 1:57
6月 102016
 

在 /etc/my.cnf 中添加:

当然:

  1. 上面修改需要重启
  2. 对已有的表不生效,对于已有的数据库上创建新表是有效的
 Posted by at 下午 1:34
2月 142016
 

INNOTOP是一个通过文本模式显示MySQL和InnoDB的监测工具。INNOTOP是用PERL语言写成的,这使它能更加灵活的使用在各种操作平台之上,它能详细的的监控出当前MYSQL和INNODB运行的状态,以DBA根据结果,可以合理的优化MYSQL,让MYSQL更稳定更高效的运行。

官方地址: http://innotop.googlecode.com/svn/html/index.html

github地址: https://github.com/innotop/innotop

 

安装: 先试试yum search innotop

 

参考资料: http://blog.csdn.net/wyzxg/article/details/8609981

 

 Posted by at 下午 3:51
12月 252015
 

锁的类型: 表锁、页锁、行锁、间隙锁【读锁(共享锁)、写锁(排他锁)】、乐观锁、悲观锁(晕,其实没这么多东西)

mysql.INNODB_LOCKS 这张表记录了发生了锁争用的信息;虽然一个事务正在加了一些锁,如果没有其它session等待这些锁的话,这个表里面也是查不到的哦

但是,A表正在事务中被更新时,如果:

lock tables A write;

虽然被阻塞,但是 mysql.INNODB_LOCKS里面却查不到任何记录!  (why: lock tables 不是存储引擎级别的锁)

 

实例分析:

表结构:

sql语句1:

sql语句2:

如果简单认为innodb的锁都是行锁(尤其是select和insert操作的压根儿也不是一行),怎么可能发生死锁呢?

让我们模拟一下,分别在两个session中一行一行地交替执行两个事务,我们发现,确实在insert的时候死锁了

session 1:

 

session 2:

 

如果我们不着急去执行session 2中的insert的语句,而是查询一下存在哪些锁,发现如下:

什么是supremum pseudo-record?

supremum pseudo-record :相当于比索引中所有值都大,但却不存在索引中,相当于最后一行之后的间隙锁

解决办法:

 

参看: http://blog.itpub.net/26250550/viewspace-1070422/

 

问题: 两个select … for update ; 加的是共享锁?否则不能同时两个select语句都能成功执行。还如何查证?

不能简单地说 select … for update 加的是共享锁还是排他锁; 如果select有结果,则加行锁,此时为排他锁; 如果没有结果,则加间隙锁,此时为共享锁(和间隙大小没有关系); 如果没有在事务中,则select for update是不加锁的;

参看: Enabling InnoDB Monitors: https://dev.mysql.com/doc/refman/5.6/en/innodb-enabling-monitors.html

可以查看当前被设置的锁的信息:

部分信息如下:

 

下面是insert 语句等待时的锁情况:

 

学习资料:

http://hedengcheng.com/?p=771    非常不错

 Posted by at 下午 2:44
11月 232015
 

查看配置:

 

查看使用情况:

Qcache_free_blocks: 表示查询缓存中目前还有多少剩余的blocks,如果该值显示较大,则说明查询缓存中的内存碎片过多了,可能在一定的时间进行整理

Qcache_free_memory: 查询缓存的内存大小,通过这个参数可以很清晰的知道当前系统的查询内存是否够用,是多了,还是不够用,DBA可以根据实际情况做出调整

Qcache_hits: 命中多少

Qcache_inserts: 写入多少

Qcache_lowmem_prunes: 空间不够,挤出多少 (?)

Qcache_not_cached: 有多少查询因不符合条件而没有被写入cache (如:结果集太大、sql条件中使用了函数、select 1等等)

Qcache_queries_in_cache: 当前缓存了多少查询

Qcache_total_blocks:  当前缓存的block数量

 

 

参考:

http://www.111cn.net/database/mysql/44889.htm

http://www.jb51.net/article/58537.htm

 Posted by at 下午 1:21