Mysql – PHPor 的Blog

关于Mysql 索引

6,370

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

参考：

原文链接：https://phpor.net/blog/post/9034

关于mysql的max_allowed_packet

Mysql No Responses »

2,358

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.

解释一下：

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

其实，将max_allowed_packet 设置为比 net_buffer_length 小的意义不大，所以，这里同样会出现一个warning，只是不会强制将max_allowed_packet修改为大于等于net_buffer_length的
比较专业的设置该值的方法为：

set global max_allowed_packet=100*1024*1024;

1

set global max_allowed_packet=100*1024*1024;
session的max_allowed_packet是不允许修改的，修改了全局配置对当前session也不会生效的，只有重新连接才能看到变化

原文链接：https://phpor.net/blog/post/4499

记录一次sql优化

Mysql No Responses »

2,025

1月 112017

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

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

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

分析：

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

原文链接：https://phpor.net/blog/post/4487

非主流抓包工具

Linux & Unix, Mysql No Responses »

2,189

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.

效果：

安装：

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

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

原文链接：https://phpor.net/blog/post/3829

mysql 数据导入导出

Mysql No Responses »

2,433

7月 042016

场景：

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

1. 导出表结构（不包含数据）

mysqldump -d dbname tablename >tablename-schema.sql

1	mysqldump -d dbname tablename >tablename-schema.sql

2. 编辑表结构（去掉索引）

3. 创建表

cat tablename-schema-no-index.sql|mysqldump dbname

1	cat tablename-schema-no-index.sql\|mysqldump dbname

4. 导出表数据（不包含表结构）

mysqldump -t dbname tablename >tablename-data.sql

1	mysqldump -t dbname tablename >tablename-data.sql

5. 导入数据

cat tablename-data.sql|mysqldump dbname

1	cat tablename-data.sql\|mysqldump dbname

6. 添加索引

附：

解压大文件直接到mysql：

tar -zxf bigfile.sql.tgz -O |mysql

1	tar -zxf bigfile.sql.tgz -O \|mysql

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

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

cat /proc/pid_of_gzip/fdinfo/0

1	cat /proc/pid_of_gzip/fdinfo/0

原文链接：https://phpor.net/blog/post/3701

mysql 之 sql_mode

Mysql, 默认分类 No Responses »

2,121

6月 102016

sql_mode 之影响：

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

总结：

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

mysql> set @@sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set @@sql_mode='TRADITIONAL';

Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;

+------------------------------------------------------------------------------------------------------------------------------------------------------+

| @@sql_mode |

+------------------------------------------------------------------------------------------------------------------------------------------------------+

| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

TRADITIONAL 等价于下面一系列标准的集合

注意：与innodb_strict_mode的区别

实际应用：

可以直接在 /etc/my.cnf 中修改sql_mode，如下：（需要重启mysql）

[mysqld] sql_mode=traditional

1
2

[mysqld]
sql_mode=traditional
如果不方便重启mysql，则可以在执行sql语句前进行回话级别的设置，如下：

set session sql_mode='TRADITIONAL';

1

set session sql_mode='TRADITIONAL';

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

set global sql_mode='TRADITIONAL';

1

set global sql_mode='TRADITIONAL';

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

参考资料：

原文链接：https://phpor.net/blog/post/3519

设置mysql默认字符集和校对规则

Mysql No Responses »

2,845

6月 102016

在 /etc/my.cnf 中添加：

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_bin

[mysqld]

character-set-server=utf8mb4

collation-server=utf8mb4_bin

当然：

上面修改需要重启
对已有的表不生效，对于已有的数据库上创建新表是有效的

原文链接：https://phpor.net/blog/post/3517

mysql监控管理工具–innotop

Mysql No Responses »

2,292

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

原文链接：https://phpor.net/blog/post/3244

关于mysql锁的学习

Mysql No Responses »

4,769

12月 252015

锁的类型：表锁、页锁、行锁、间隙锁【读锁（共享锁）、写锁（排他锁）】、乐观锁、悲观锁（晕，其实没这么多东西）

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

但是，A表正在事务中被更新时，如果：

lock tables A write;

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

实例分析：

表结构：

CREATE TABLE `credit_core` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL,
  `app_id` int(10) unsigned NOT NULL,
  `limit` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_uid_appid` (`uid`,`app_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE `credit_core` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`uid` int(10) unsigned NOT NULL,

`app_id` int(10) unsigned NOT NULL,

`limit` int(10) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

UNIQUE KEY `uniq_uid_appid` (`uid`,`app_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

sql语句1：

begin;
select * from credit_core where uid=153 and app_id = 1 for update;
insert into credit_core(uid, appid, `limit`) values(153, 1, 1000);
commit;

begin;

select * from credit_core where uid=153 and app_id = 1 for update;

insert into credit_core(uid, appid, `limit`) values(153, 1, 1000);

commit;

sql语句2：

begin;
select * from credit_core where uid=154 and app_id = 1 for update;
insert into credit_core(uid, appid, `limit`) values(154, 1, 1000);
commit;

begin;

select * from credit_core where uid=154 and app_id = 1 for update;

insert into credit_core(uid, appid, `limit`) values(154, 1, 1000);

commit;

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

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

session 1:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from credit_core where uid=153 and app_id = 1 for update;
Empty set (0.00 sec)

mysql> insert into credit_core(uid, app_id, `limit`) values(153, 1, 1000);
#这里开始等待锁，第二个session执行到insert时，会发现死锁，然后取消事务并退出，然后，本sql得到锁并成功执行了
Query OK, 1 row affected (15.98 sec)

mysql>

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from credit_core where uid=153 and app_id = 1 for update;

Empty set (0.00 sec)

mysql> insert into credit_core(uid, app_id, `limit`) values(153, 1, 1000);

#这里开始等待锁，第二个session执行到insert时，会发现死锁，然后取消事务并退出，然后，本sql得到锁并成功执行了

Query OK, 1 row affected (15.98 sec)

mysql>

session 2:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from credit_core where uid=154 and app_id = 1 for update;
Empty set (0.00 sec)

mysql> insert into credit_core(uid, app_id, `limit`) values(154, 1, 1000);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql>

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from credit_core where uid=154 and app_id = 1 for update;

Empty set (0.00 sec)

mysql> insert into credit_core(uid, app_id, `limit`) values(154, 1, 1000);

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

mysql>

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

什么是supremum pseudo-record？

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

解决办法：

START TRANSACTION;
insert into ... on duplicate key update `val`='XX';

commit;

START TRANSACTION;

insert into ... on duplicate key update `val`='XX';

commit;

参看： 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

set GLOBAL innodb_status_output_locks=ON;

1	set GLOBAL innodb_status_output_locks=ON;

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

SHOW ENGINE INNODB STATUS\G

1	SHOW ENGINE INNODB STATUS\G

部分信息如下：

---TRANSACTION 39527, ACTIVE 3 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 37, OS thread handle 0x7f5fb0733700, query id 458 localhost root cleaning up
TABLE LOCK table `test`.`credit_core` trx id 39527 lock mode IX
RECORD LOCKS space id 132 page no 4 n bits 72 index `uniq_uid_appid` of table `test`.`credit_core` trx id 39527 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

---TRANSACTION 39527, ACTIVE 3 sec

2 lock struct(s), heap size 360, 1 row lock(s)

MySQL thread id 37, OS thread handle 0x7f5fb0733700, query id 458 localhost root cleaning up

TABLE LOCK table `test`.`credit_core` trx id 39527 lock mode IX

RECORD LOCKS space id 132 page no 4 n bits 72 index `uniq_uid_appid` of table `test`.`credit_core` trx id 39527 lock_mode X

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum;;

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

mysql> select * from INNODB_LOCKS\G
*************************** 1. row ***************************
    lock_id: 39515:132:4:1
lock_trx_id: 39515
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`credit_core`
 lock_index: uniq_uid_appid
 lock_space: 132
  lock_page: 4
   lock_rec: 1
  lock_data: supremum pseudo-record
*************************** 2. row ***************************
    lock_id: 39514:132:4:1
lock_trx_id: 39514
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`credit_core`
 lock_index: uniq_uid_appid
 lock_space: 132
  lock_page: 4
   lock_rec: 1
  lock_data: supremum pseudo-record
2 rows in set (0.00 sec)

mysql> select * from INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 39515
                 trx_state: LOCK WAIT
               trx_started: 2015-12-25 15:15:50
     trx_requested_lock_id: 39515:132:4:1
          trx_wait_started: 2015-12-25 15:25:24
                trx_weight: 4
       trx_mysql_thread_id: 35
                 trx_query: insert into credit_core(uid, app_id, `limit`) values(153, 1, 1000)
       trx_operation_state: inserting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 3
     trx_lock_memory_bytes: 360
           trx_rows_locked: 2
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 39514
                 trx_state: RUNNING
               trx_started: 2015-12-25 15:15:40
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 37
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 2
     trx_lock_memory_bytes: 360
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

mysql> select * from INNODB_LOCKS\G

*************************** 1. row ***************************

lock_id: 39515:132:4:1

lock_trx_id: 39515

lock_mode: X

lock_type: RECORD

lock_table: `test`.`credit_core`

lock_index: uniq_uid_appid

lock_space: 132

lock_page: 4

lock_rec: 1

lock_data: supremum pseudo-record

*************************** 2. row ***************************

lock_id: 39514:132:4:1

lock_trx_id: 39514

lock_mode: X

lock_type: RECORD

lock_table: `test`.`credit_core`

lock_index: uniq_uid_appid

lock_space: 132

lock_page: 4

lock_rec: 1

lock_data: supremum pseudo-record

2 rows in set (0.00 sec)

mysql> select * from INNODB_TRX\G

*************************** 1. row ***************************

trx_id: 39515

trx_state: LOCK WAIT

trx_started: 2015-12-25 15:15:50

trx_requested_lock_id: 39515:132:4:1

trx_wait_started: 2015-12-25 15:25:24

trx_weight: 4

trx_mysql_thread_id: 35

trx_query: insert into credit_core(uid, app_id, `limit`) values(153, 1, 1000)

trx_operation_state: inserting

trx_tables_in_use: 1

trx_tables_locked: 1

trx_lock_structs: 3

trx_lock_memory_bytes: 360

trx_rows_locked: 2

trx_rows_modified: 1

trx_concurrency_tickets: 0

trx_isolation_level: REPEATABLE READ

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 10000

trx_is_read_only: 0

trx_autocommit_non_locking: 0

*************************** 2. row ***************************

trx_id: 39514

trx_state: RUNNING

trx_started: 2015-12-25 15:15:40

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 2

trx_mysql_thread_id: 37

trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 0

trx_lock_structs: 2

trx_lock_memory_bytes: 360

trx_rows_locked: 1

trx_rows_modified: 0

trx_concurrency_tickets: 0

trx_isolation_level: REPEATABLE READ

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 10000

trx_is_read_only: 0

trx_autocommit_non_locking: 0

2 rows in set (0.00 sec)

学习资料：

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

原文链接：https://phpor.net/blog/post/3186

mysql query cache

Mysql No Responses »

2,139

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

原文链接：https://phpor.net/blog/post/3125

Older Entries