Skip to content

MySQL

查看表结构

desc 表名称;

查看建表sql语句

show create table 表名称;

MERGE存储引擎

官方文档:http://dev.mysql.com/doc/refman/5.7/en/merge-storage-engine.html

查看能用的引擎:show engines

创建一个MERGE表:

假设有a,b表,他们的结构完全相同,然后就可以建立一个c表,注意这个表的定义要与a和b表完全一致

drop table if exists data;
CREATE TABLE c (
   `id` int(11) NOT NULL,
  `data` longtext NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE= MRG_MYISAM ,UNION=(a,b);

特点:

这种表不会创建额外的索引,但查询起来比视图速度更快;

不能在这种表上建立全文索引


删除表的冗余

两行只有一列(这里假设为 gettime )不同,删除其中一行

delete t1 from t as t1, t as t2 where
    t1.id = t2.id and
    t1.其他列=t2.其他列 and
    t1.gettime>t2.gettime;

修改表 alter table

ALTER IGNORE TABLE `表名称`
MODIFY COLUMN `id`  int(11) NOT NULL FIRST,
MODIFY COLUMN `user` varchar(66) NOT NULL AFTER `id`,
MODIFY COLUMN `content` longtext NOT NULL AFTER `user`,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`),
DROP INDEX `a1`,
ADD INDEX `a1` (`user`);

将中文转为拼音 函数

代码在code/pinyin.sql


从路径URL获取文件名称

来源 http://stackoverflow.com/questions/17090237/extracting-filenames-from-a-path-mysql

使用SUBSTRING_INDEX函数,假设url此行的内容为”http://example.com/some/path/to/filename.zip”

select SUBSTRING_INDEX(url, '/', -1) as filename;

即可得到一列filename,此行数据为”filename.zip”


查询优化

explain发现出现了using filesort

参考 http://www.ccvita.com/169.html

如果使用了order by或者group by,需要建索引以优化这个查询

group by用了两个列,两列要合在一起创建索引

内存表索引的选择

参考 https://dev.mysql.com/doc/refman/5.5/en/optimizing-memory-tables.html

内存表的索引应该选择BTREE


内存表The table is full

修改MySQL的配置文件/etc/mysql/my.cnf,在[mysqld]下添加/修改两行(下面的值仅供参考,请根据实际情况酌情处理):

tmp_table_size = 256M // 临时表大小 
max_heap_table_size = 256M // 内存表大小 

replace函数替换文本

update `content` set value=replace(value,"original content","replaced content");

注意replace不要反引号


简单的split功能,文本转数字

表的设计违背了一列只存放一种数据的原则,搞出了这样一个Text类型的列(假设为info),格式为”用户名: 数值”

现在需要将数值从这一列中提取出来,并转为int类型

convert (
    substr(
        `info`,
        locate(':', `info`) + 2
    ),
    unsigned integer
)

Google关键词:mysql split string,mysql string to int

参考:

https://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql

https://stackoverflow.com/questions/5960620/convert-text-into-number-in-mysql-query


mysqld配置参数调优

参考:https://www.linode.com/docs/databases/mysql/how-to-optimize-mysql-performance-using-mysqltuner

使用MySQLTuner这个工具得到一些建议:

curl -L http://mysqltuner.pl/ | perl

对于其最后给出的参数建议照做即可。

key_buffer参数是最关键的参数,决定了mysql占用的内存大小


支持emoji,从utf8升级到utf8mb4

为了让mysql存储emoji表情,需要进行表的变更操作 以及 连接代码的修改

参考https://stackoverflow.com/questions/26532722/how-to-encode-utf8mb4-in-python

表的变更 表的每一个CHARVARCHARTEXT类型的列都要改为使用utf8mb4

举个例子,表名称{tablename},修改其user列和content列,以及表的默认字符集:

ALTER TABLE `{tablename}`
MODIFY COLUMN `user`  varchar(66) CHARACTER SET utf8mb4 NOT NULL AFTER `edittime`,
MODIFY COLUMN `content`  longtext CHARACTER SET utf8mb4 NOT NULL AFTER `user`,
DEFAULT CHARACTER SET=utf8mb4;

连接代码的改动

在执行 insert 的 sql语句之前,先执行这三条sql:

SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4; 
SET character_set_connection=utf8mb4;

解决 Too many open files 错误

参考:https://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit

https://stackoverflow.com/questions/22495124/cannot-set-limit-of-mysql-open-files-limit-from-1024-to-65535/35515570

系统环境: ubuntu16.04,用apt install mysql-server安装的mysql

这个问题的原因是mysql默认的open_files_limit是1024,在mysql打开1024个文件后就无法再打开新文件,需要修改这个limit,如改为1024000

修改/etc/mysql/mysql.conf.d/mysqld.cnf,在[mysqld_safe]和[mysqld]都加入一行:

open_files_limit = 1024000

/etc/security/limits.conf中加入:

* soft nofile 1024000
* hard nofile 1024000
* soft nproc 10240
* hard nproc 10240

上述还不够,由于mysql服务被systemd管理,还要修改/lib/systemd/system/mysql.service,在最后加入

LimitNOFILE = infinity
LimitMEMLOCK = infinity

在ubuntu16.04上,这些还不够,还要继续改systemd: (参考:https://serverfault.com/questions/791729/ubuntu-16-04-server-mysql-open-file-limit-wont-go-higher-than-65536)

mkdir /etc/systemd/system/mysql.service.d
tee /etc/systemd/system/mysql.service.d/override.conf <<-'EOF'
[Service]
LimitNOFILE=1024000
EOF

然后重启mysql:

systemctl daemon-reload
systemctl restart mysql.service

使用这两种方法都能看到修改是否生效:

ps aux|grep mysqld #找到mysqld的进程ID
cat /proc/上述找到的进程ID/limits | grep files

mysql -u root -p
show global variables like "%open_files_limit%";

解决ubuntu16.04上mysql被apt upgrade自动关闭的问题

问题现象: mysql服务自动退出,查日志jounralctl -xe|tac|less发现是apt upgrade引起的自动退出

发现这是一个ubuntu16.04的特性,每天凌晨6点会自动apt upgrade安全更新,但不明原因这个更新失败了,apt关掉了mysql服务后由于异常退出并没有重新把mysql服务启动

如果你想关掉自动更新(不建议):修改/etc/apt/apt.conf.d/20auto-upgrades,改为APT::Periodic::Unattended-Upgrade "0";

执行apt upgrade或者apt-get install -f看看,能重现问题(mysql被关掉了),也发现了报错信息:

mysql_upgrade: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) while connecting to the MySQL server
  Upgrade process encountered error and will not continue.

然后就查这个报错,发现了这个:https://github.com/chef-cookbooks/mysql/issues/466

人家的解决方案是:systemctl enable mysql,然而我执行又遇到了systemd的报错:Failed to execute operation: Invalid argument

最终解决方案就是先disable一下

systemctl disable mysql
systemctl enable mysql
apt-get install -f

NULL转0

用函数IFNULL,写法就是IFNULL(列名, 0)

举个例子

select sum(IFNULL(score, 0)) from runs;

给MySQL增加一个slave做主从复制

master启用binlog配置server-id;master用mysqldump导出数据库;slave导入,开始slave

master创建用户

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

master启用binlog

/etc/mysql/conf.d/master.cnf

[mysqld]
server-id=1
log-bin=mysql-bin.log
innodb_flush_log_at_trx_commit=1
sync_binlog=1

搬运数据库

由于binlog是在已经有数据之后才开启的,而mysql不会自己执行全量同步,就只能靠手工搬运sql文件咯

跨地域复制网速是限制因素,就用7z压缩了

mysqldump -h 127.0.0.1 -P 3306 -u root -p --opt --single-transaction --comments --hex-blob --dump-date --no-autocommit --all-databases --master-data | 7z a -si dbdump.sql.7z
7z x -so dbdump.sql.7z | mysql -h 127.0.0.1 -u root -p

你也可以使用ssh和管道操作,就不用创建文件了:

ssh master_server "mysqldump -h 127.0.0.1 -P 3306 -u root -ppassword --opt --single-transaction --comments --hex-blob --dump-date --no-autocommit --all-databases --master-data |gzip" | zcat | mysql -h 127.0.0.1 -u root -ppassword

其中--master-data很重要,导出的时候就自动带上了master的信息,无需再手工记录

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=15410;

配置slave

实际使用中很容易遇到duplicate key的问题,导致slave线程停住,配置直接忽略这个错误 参考

server-id               = 2
relay-log               = mysql-relay-bin.log
log_bin                 = mysql-bin.log
slave-skip-errors = 1062
CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password';
START SLAVE;
SHOW SLAVE STATUS\G

Docker镜像

https://github.com/bitnami/bitnami-docker-mysql

但这个镜像也不会帮你自动完成mysqldump,但配置一个slave还是很省心的

注意权限配置,如果原本数据文件夹不存在,Docker创建的是root用户才能读写,导致容器直接退出,坑的地方在于没有报错信息

mkdir -p /srv/mysql-slave/data
chown 1001 /srv/mysql-slave/data -R

docker run -it -v /srv/mysql-slave/my.cnf:/opt/bitnami/mysql/conf/my.cnf:ro -v /srv/mysql-slave/data:/bitnami/mysql/data \
     --name mysql-slave -e MYSQL_REPLICATION_MODE=slave -e MYSQL_REPLICATION_USER=slave_user -e MYSQL_REPLICATION_PASSWORD=password \
      -e MYSQL_MASTER_HOST=12.34.56.789 -e MYSQL_MASTER_PORT_NUMBER=3306 \
      -e MYSQL_MASTER_ROOT_USER=slave_user -e MYSQL_MASTER_ROOT_PASSWORD=password \
      bitnami/mysql:5.7

其中MYSQL_MASTER_ROOT_USER虽然人家说要root用户,实际上代码里只是用来select 1,所以用slave_user即可,参见我提的issue

容器启动后一般会由于master的binlog不完整执行sql失败(表不存在),需要继续操作:

修改my.cnf在[mysqld]增加skip-grant-tables 注意复数,来保证slave可以连上进行操作

然后执行sql语句STOP SLAVE; 再执行上述搬运数据库的操作,导入完成后START SLAVE

使用show slave status;查看slave状态

另外,配合autossh和ssh的端口转发可以避免将mysql端口暴露在外:

autossh -M 0 -o ServerAliveInterval=30 -o ServerAliveCountMax=3 -L 172.17.0.1:3306:127.0.0.1:3306 user@12.34.56.789

相应地,slave配置的master地址改为172.17.0.1


为什么MySQL表必须要有主键

Learned from: https://federico-razzoli.com/why-mysql-tables-need-a-primary-key

InnoDB中非主键的索引是额外存储的,使用索引查询先查到主键,再用主键查数据——没有主键的查询会慢一些

没有主键也没有隐式主键(创建表的时候指定的NOT NULL UNIQUE列)会自动使用递增的row id作为主键,但递增需要全局锁dict_sys->mutex——多个表同时插入时性能下降

在binlog中删除操作有主键只需要记录主键,没有主键需要记录所有内容——binlog更大

从机执行binlog中的delete操作,每次操作都需要扫描全表——性能显著下降

集群如Galera需要等待所有节点commit才会给客户端返回成功——用户感受到很高的延迟