Hugh's Blog

MySQL 笔记(持续更新)

中文乱码

# 登录
mysql -u root -p
# 查看目前所有编码
mysql> show variables like 'char%';

会显示类似下面的信息:

+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| character_set_client     | gbk                           |
| character_set_connection | gbk                           |
| character_set_database   | latin1                        |
| character_set_filesystem | binary                        |
| character_set_results    | gbk                           |
| character_set_server     | latin1                        |
| character_set_system     | utf8                          |
+--------------------------+-------------------------------+

注意:

character_set_client -> 客户端字符集

character_set_connection -> 中间转换器字符集

character_set_results -> 查询结果字符集

一般来说,只要这三个字符集和客户端一致,就不会有乱码出现,快速设置的方法为:

-- gbk|utf8
set names utf8;

MyISAM 和 InnoDB

  1. InnoDB 支持事务,MyISAM 不支持事务。

  2. InnoDB 支持数据行锁定,MyISAM 不支持行锁定,只支持锁定整个表。

  3. InnoDB 支持外键,MyISAM 不支持。

  4. InnoDB 不支持全文索引,而 MyISAM 支持。全文索引是指对 char、varchar 和 text 中的每个词(停用词除外)建立倒排序索引,全文索引不支持中文分词,需使用者自行加入空格进行分词。

索引

-- 显示索引
show index from `table_name`;
-- 创建索引
-- ALTER TABLE
alter table `table_name` add index index_name(`column_1`, `column_2`);
alter table `table_name` add unique index_name(`column_name`);
-- CREATE INDEX
create index index_name on `table_name`(`column_name`);
create unique index index_name on `table_name`(`column_name`);
-- 删除索引
drop index index_name on `table_name`;
alter table `table_name` drop index index_name;
-- 重建索引
repair table `table_name` quick;

优化方案:

  • where 或者 order by 涉及的字段上建立索引。

  • 表与表的连接字段应加索引。

  • 查询条件有 null 值,索引失效,如:where status is null

  • where 包含比较操作符,索引失效,如:where id <> 3where id != 3

  • where 如果使用 or 来连接条件,所有字段都要有索引,否则索引失效。

  • where 如果使用 in 或者 not in,索引失效。

  • where 后面条件如果有函数操作,索引失效。

  • like 如果左边包含 % 号,索引失效,应该使用:where title like 'search%'

  • 组合索引查询应该符合最左匹配原则。

导出与导入

# 导出
mysql> mysqldump db_name > backup.sql
# 导入
mysql> use db_name;
mysql> source backup.sql;
# Windows 导入
mysql> source D:\backup.sql;
# 非登录导入
mysql -u {user} -p db_name < /path/to/backup.sql

导入备份文件最好用绝对路径,注意在 Windows 下的 sql 文件路径与名称最好是全英文,不要带下划线等,不然可能会报 SQL failed to open file error 22 错误。

查看状态

-- 查看正在运行的线程,除非有 super 权限,否则只能查看当前用户下的线程。
show processlist;
show full processlist;
-- 查看当前打开的表,一般发生在 drop 或者 rename 的操作会有影响。
show open tables;
show open tables from your_db;
-- 查看服务器状态。
show status like '%lock%';
show status like '%Table%';
show status like 'innodb_row_lock%';
-- 查看 InnoDB 引擎信息。
show engine innodb status\G;
-- 查看服务器配置信息。
show variables like '%timeout%';

共享锁与排他锁

共享锁又称读锁,简称S锁,读取数据时创建的锁,当一个事务给一个数据行加锁的时候,其他事务可以读,但不能写,用法:select ... lock in share mode;

排他锁又称写锁,简称X锁,读取数据时创建的锁,当一个事务加锁的时候,其他事务不能读取,也不能写,但对于一般的 select 语句无影响,用法:select ... for update;

注意:

对于 insert、update、delete 操作,InnoDB 会自动给数据行加排他锁,对于一般的 select 语句则不会加任何锁。

排他锁需要在语句明确主键的情况下,才会加行级锁,否则会加表级锁,同样的,索引也会影响锁的类型,跟主键差不多。

例如:

select * from goods where id = 1 for update; -- 主键明确,加行锁
select * from goods where id = 99999 for update; -- 无记录,不加锁
select * from goods where id >= 10 for update; -- 主键不明确,加表锁
select * from goods where name = 'goods_name' for update; -- 无主键,加表锁

乐观锁与悲观锁

乐观锁是假设不会发生并发冲突,每次拿数据时认为别人不会去修改,所以不会加锁,只会在更新的时候去判断数据是否有更新,做法可以使用版本号来控制。

悲观锁是假定会发生并发冲突,每次拿数据时认为别人会修改数据,所以拿数据时会加锁,其他拿数据的事务只能等待,例如行级锁,表级锁等。

慢查询日志

通过命令行开启,重启后会失效。

-- 开启
set global slow-query-log=on;
-- 日志文件
set global slow_query_log_file='/var/log/mysql/slow-query.log';
-- 记录没有使用索引的查询
set global log_queries_not_using_indexes=on;
-- 记录 2 秒以上的查询
set global long_query_time=2;

通过配置文件 my.cnf 开启,永久性,需要重启。

[mysqld]
slow-query-log=on
slow_query_log_file="/var/log/mysql/slow-query.log"
log_queries_not_using_indexes=on
long_query_time=2

查看当前慢查询状态:

show variables like '%slow%';
show variables like '%long_query%';

使用 mysqldumpslow 来分析慢日志,使用说明:mysqldumpslow --help

-- 列出最慢的前 3 个查询
mysqldumpslow -t 3 slow-query.log
-- 列出记录次数最多的前 3 个语句
mysqldumpslow -s c -t 3 slow-query.log
-- 按照查询时间返回前 3 个并含有左连接的语句
mysqldumpslow -s t -t 10 -g "left join" slow-query.log

获取库下的所有表

select table_name from information_schema.tables where table_schema = 'test';

获取库表占用大小

# 每个表的大小
select table_name, (data_length + index_length) / 1024 / 1024 as 'used(mb)', (data_free) / 1024 / 1024 as 'free(mb)' from information_schema.tables where table_schema = 'test';

# 每个数据库的大小
select table_schema, sum(data_length + index_length) / 1024 / 1024 as 'used(mb)', sum(data_free) / 1024 / 1024 as 'free(mb)' from information_schema.tables group by table_schema;

获取删除表语句

# 删除日志表
select group_concat(concat("drop table `", table_schema, "`.`", table_name, "`;") separator " ") as drop_sql from information_schema.tables where table_schema ="test_db" and table_name like "log_%" group by table_schema;

问题记录

2017-06-30

今天在导入 WordPress 数据时报错:Unknown COLLATE: ‘utf8mb4_unicode_520_ci’,看了下,应该是低版本排序规则不支持 utf8mb4_unicode_520_ci 导致的,目标 MySQL 版本为 5.5.55,源备份版本为 5.6.35。

解决方法很简单,把 sql 文件内容里的 utf8mb4_unicode_520_ci 全局替换成 utf8mb4_unicode_ci 就行了。

2019-03-13

今天在 Ubuntu 18.04 下安装 MySQL 5.7 版本,过程中并没有要求输入 root 密码,安装完成之后无法登录,除非加 sudo 命令。

更新:通过命令 cat /var/log/mysqld.log | grep password 查看密码。

# 登录
sudo mysql -u root
# 查看用户信息,可以看到 root 用户使用 auth_socket 来验证密码
mysql> select user, plugin from mysql.user;
+------------------+-----------------------+
| user             | plugin                |
+------------------+-----------------------+
| root             | auth_socket           |
| mysql.session    | mysql_native_password |
| mysql.sys        | mysql_native_password |
+------------------+-----------------------+
# 修改 root 用户密码及插件类型
mysql> update mysql.user set authentication_string=PASSWORD('root'), plugin='mysql_native_password' where user='root';
mysql> flush privileges;
mysql> exit;

# 重启
sudo service mysql restart
# 登录
mysql -u root -p

因为环境是在虚拟机中,为了让宿主机访问,配置远程登录。

# 注释 bind-address = 127.0.0.1
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

# 登录
mysql -u root -p
# 添加用户 root,允许远程登录
mysql> grant all on *.* to root@'%' identified by 'root' with grant option;
mysql> flush privileges;
mysql> exit;

# 重启
sudo service mysql restart

参考

Mysql易犯错误

MyISAM和InnoDB的区别

How to import an SQL file using the command line in MySQL?

MySQL锁定状态查看命令

MySQL SQL优化

Ubuntu18.04安装mysql5.7