Mysql

架构

数据类型

字符串

  • char
  • varchar
  • text

数字

类型字节长度有符号无符号
tinyint1-127~1280~255
smallint2-32768~327670~65535
mediumint3-8388608~83886070~16777215
int4-2147483648~21474836470~4294967295
bigint8-$2^{63}$ ~ $2^{63}$-10~$2^{64}$-1

二进制

  • json

编码

SQL

一、权限登录类

  • 1.授权用户
-- 授权用户
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- 查看某个用户的权限
show grants for username;
  • 2.远程连接
mysql -h192.168.1.1 -P3306 -uroot -p
  • 3.导出数据库
mysqldump -h192.168.1.1-P3306 -uroot -p db_name > db_name.sql
  • 4.导入数据库
mysql -uroot -p db_name < db_name.sql
  • 5.分析查询
explain select * from user;
  • 6.查询所有的进程
show procelist;
show full procelist;

二、DDL

2.1 数据库

  • 1.建数据库语句
create database db_name default character set utf8mb4 collate utf8_general_ci;
  • 3.删除数据库
drop database db_name;

表操作

  • 2.创建数据表语句
CREATE TABLE `user_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` char(32) NOT NULL COMMENT 'id',
  `user_name` varchar(255) NOT NULL COMMENT '姓名',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `idx_user` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='用户信息表';
  • 3.查看数据表
-- 查看所有表
show tables;
-- 查看建表语句
show create table table_name;
-- 查看表字段及描述
desc table_name;
  • 4.查看字段
show columns from table_name;
  • 5.查看索引
show index from table_name;
  • 6.删除表
-- 删除表结构，表结构和数据全部删除
drop table table_name;
  • 清空表数据
truncate table table_name;
  • 7.新增字段
  • 8.修改字段
  • 9.删除字段
  • 10.添加索引
  • 11.删除索引

三、DML

3.1 查询

union

union进行链接两个查询，并返回结果集，但是会去除重复数据 union all会保留重复数据

2.新增

3.修改

4.删除

-- 全部删除数据，不删表结构。不做表优化
delete from table_name;

-- 按条件删除
delete from table_name where is_deleted = 1;

-- 按条件限制删除
delete from table_name where is_deleted = 1 limit 100;

-- 删除表数据，并优化表空间
truncate table table_name;

5.重建引擎

alter table table_name engine=InnoDB

四、参数状态

  • 1.查看参数
-- 查看所有参数
show variables;
-- 过滤参数
show variables like '%sql%';
  • 2.查看事件
show events;
  • 3.查看触发器
show triggers;
  • 4.查看所有状态
show status;
  • 5.查看打开表
show open tables;
  • 6.查看表状态
-- 在某个db下执行
-- 可以查看表的存储引擎，表空间，自增主键，创建和更新时间等
show table status;
  • 7.查看数据库状态
-- 查看主库
show master status;
-- 查看从库
show slave status;
  • 8.查看函数状态
show function status;
  • 9.查看插件
show plugins;
  • 10.查看错误
show errors;
  • 11.查看存储引擎
show engines;
  • 12.查询数据存储空间
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS'Total'FROM information_schema.TABLES WHERE table_schema = 'mainsite' order by Total desc;

五、函数

5.1 日期时间

  • 当前日期时间:NOW()
  • 当前日期:CURDATE(),CURRENT_DATE()
  • 当前时间:CURTIME(),CURRENT_TIME()
  • 当前时间戳：UNIX_TIMESTAMP(),CURRENT_TIMESTAMP()
  • 格式化:DATE_FORMAT(NOW(), "%Y-%m-%d %H:%m:%s")
  • 日期相加:DATE_ADD(CURDATE(), INTERVAL 1 DAY),ADDDATE(CURDATE(), INTERVAL 1 DAY),ADDDATE(CURDATE(), 1)
  • 日期相减:DATE_SUB(CURDATE(), INTERVAL 1 DAY),SUBDATE(CURDATE(), INTERVAL 1 DAY),SUBDATE(CURDATE(), 1)
  • 日期差值:DATADIFF(DAY, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 DAY))
  • 第几周:WEEKOFYEAR(CURDATE())
  • 第几月:MONTH(CURDATE())

5.2 聚合函数

  • 平均值:AVG()
  • 求和:SUM()
  • 最大值:MAX()
  • 最小值:MIN()
  • 数量:COUNT()
  • 求余:MOD()

5.3 字符串

  • 长度:LENGTH(str)
  • 截取:SUBSTR(str, start, length)
  • 替换:REPLACE(str, old, new)
  • 大写:UPPER(str)
  • 小写:LOWER(str)

5.4 数字

  • 四舍五入:ROUND(num, length)
  • 向下取整:CEIL(num)
  • 向上取整:FLOOR(num)
  • 绝对值:ABS()

5.4 类型转换

  • 字符串转数字:CAST(str AS SIGNED)

空值

  • 判断空值:ISNULL(str)
  • 如果为空:IFNULL(str, default)
  • 如果不为空:IF(str, true, false)
  • 如果返回空:NULLIF

位运算

  • & AND
  • | OR
  • ^ XOR

引擎

主从同步

  • 时间：2019-03-10 18:25:53

0x00、同步步骤

  • 1.主库和从库创建同步账户
mysql> grant replication slave, replication client on *.* to repl@'192.168.1.%' identified by '123456';
  • 2.主库配置/etc/my.cnf
server-id=130	#保证唯一值
log-bin=mysql-bin
binlog_format=mixed
binlog-do-db=test2
binlog-ignore-db=mysql
log-error=/var/lib/mysql/mysql.err
  • 3.重启主库
  • 4.查看主库状态
mysql>show master status;
+-------------------------+-----------------+--------------------+-------------------------+
| File            |	 Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------------+----------------+--------------------+--------------------------+
| mysql-bin.000002 |      120 |   test2      |   mysql        |
+-------------------------+----------------+---------------------+------------------------+
1 row in set (0.00 sec)
  • 5.从库配置 /etc/my.cnf
server-id=131 #保证唯一值
log-bin=mysql-bin
binlog_format=mixed
replicate-do-db=test2
replicate-ignore-db=mysql
relay_log=/var/lib/mysql/mysql-relay-bin
log_slave_updates=1
read_only=1
  • 6.重启从库
  • 7.指向主库操作
mysql> change master to master_host='192.168.1.201',
master_user='repl',
master_password='123456',
master_log_file='mysql-bin.000002',
master_log_pos=120;
  • 8.开始同步
mysql>start slave;
  • 9.查看状态
mysql>show slave status;
  • 10.双向主从的话，把主库按照从库在配置一遍即可

0x01、备注

  • 1.两个数据库版本尽量一致，如果不一致，高版本做从库，此时不可双向主从
  • 2.主库和从库的数据库名必须相同；
  • 3.主库和从库的复制可以精确到表，但是在需要更改主库或从库的数据结构时需要立刻重启slave；
  • 4.不能在mysql配置文件里直接写入master的配置信息，需要用change master命令来完成；
  • 5.指定replicate_do_db必须在my.ini里配置，不能用change master命令来完成；
  • 6.如果不及时清理，日积月累二进制日志文件可能会把磁盘空间占满，可以在配置文件里加上expire_logs_days=7，只保留最近7天的日志，建议当slave不再使用时，通过reset slave来取消relaylog；

mysqldump

  • --all-databases , -A
    • 导出全部数据库。
    •   mysqldump  -uroot -p --all-databases
  • --all-tablespaces , -Y
    • 导出全部表空间。
    •   mysqldump  -uroot -p --all-databases --all-tablespaces
  • --no-tablespaces , -y
    • 不导出任何表空间信息。
    •   mysqldump  -uroot -p --all-databases --no-tablespaces
  • --add-drop-database
    • 每个数据库创建之前添加drop数据库语句。
    •   mysqldump  -uroot -p --all-databases --add-drop-database
  • --add-drop-table
    • 每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用--skip-add-drop-table取消选项)
    •   mysqldump  -uroot -p --all-databases  (默认添加drop语句)
  mysqldump  -uroot -p --all-databases –skip-add-drop-table  (取消drop语句)
  • --add-locks
    • 在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态，使用--skip-add-locks取消选项)
    •   mysqldump  -uroot -p --all-databases  (默认添加LOCK语句)
  mysqldump  -uroot -p --all-databases –skip-add-locks   (取消LOCK语句)
  • --allow-keywords
    • 允许创建是关键词的列名字。这由表名前缀于每个列名做到。
    •   mysqldump  -uroot -p --all-databases --allow-keywords
  • --apply-slave-statements
    • 在'CHANGE MASTER'前添加'STOP SLAVE'，并且在导出的最后添加'START SLAVE'。
    • mysqldump -uroot -p --all-databases --apply-slave-statements
  • --character-sets-dir
    • 字符集文件的目录
    • mysqldump -uroot -p --all-databases --character-sets-dir=/usr/local/mysql/share/mysql/charsets
  • --comments
    • 附加注释信息。默认为打开，可以用--skip-comments取消
    • mysqldump -uroot -p --all-databases (默认记录注释)
    • mysqldump -uroot -p --all-databases --skip-comments (取消注释)
  • --compatible
    • 导出的数据将和其它数据库或旧版本的MySQL 相兼容。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等，要使用几个值，用逗号将它们隔开。它并不保证能完全兼容，而是尽量兼容。
    • mysqldump -uroot -p --all-databases --compatible=ansi
  • --compact
    • 导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项：--skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys
    • mysqldump -uroot -p --all-databases --compact
  • --complete-insert, -c
    • 使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。
    • mysqldump -uroot -p --all-databases --complete-insert
  • --compress, -C
    • 在客户端和服务器之间启用压缩传递所有信息
    • mysqldump -uroot -p --all-databases --compress
  • --create-options, -a
    • 在CREATE TABLE语句中包括所有MySQL特性选项。(默认为打开状态)
    • mysqldump -uroot -p --all-databases
  • --databases, -B
    • 导出几个数据库。参数后面所有名字参量都被看作数据库名。
    • mysqldump -uroot -p --databases test mysql
    • --debug
    • 输出debug信息，用于调试。默认值为：d:t:o,/tmp/mysqldump.trace
    • mysqldump -uroot -p --all-databases --debug
    • mysqldump -uroot -p --all-databases --debug="d:t:o,/tmp/debug.trace"
  • --debug-check
    • 检查内存和打开文件使用说明并退出。
    • mysqldump -uroot -p --all-databases --debug-check
  • --debug-info
    • 输出调试信息并退出
    • mysqldump -uroot -p --all-databases --debug-info
  • --default-character-set
    • 设置默认字符集，默认值为utf8
    • mysqldump -uroot -p --all-databases --default-character-set=latin1
  • --delayed-insert
    • 采用延时插入方式（INSERT DELAYED）导出数据
    • mysqldump -uroot -p --all-databases --delayed-insert
  • --delete-master-logs
    • master备份后删除日志. 这个参数将自动激活--master-data。
    • mysqldump -uroot -p --all-databases --delete-master-logs
  • --disable-keys
    • 对于每个表，用/!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/!40000 ALTER TABLE tbl_name ENABLE KEYS */;语句引用INSERT语句。这样可以更快地导入dump出来的文件，因为它是在插入所有行后创建索引的。该选项只适合MyISAM表，默认为打开状态。
    • mysqldump -uroot -p --all-databases
  • --dump-slave
    • 该选项将导致主的binlog位置和文件名追加到导出数据的文件中。设置为1时，将会以CHANGE MASTER命令输出到数据文件；设置为2时，在命令前增加说明信息。该选项将会打开--lock-all-tables，除非--single-transaction被指定。该选项会自动关闭--lock-tables选项。默认值为0。
    • mysqldump -uroot -p --all-databases --dump-slave=1
    • mysqldump -uroot -p --all-databases --dump-slave=2
  • --events, -E
    • 导出事件。
    • mysqldump -uroot -p --all-databases --events
  • --extended-insert, -e
    • 使用具有多个VALUES列的INSERT语法。这样使导出文件更小，并加速导入时的速度。默认为打开状态，使用--skip-extended-insert取消选项。
    • mysqldump -uroot -p --all-databases
    • mysqldump -uroot -p --all-databases--skip-extended-insert (取消选项)
  • --fields-terminated-by
    • 导出文件中忽略给定字段。与--tab选项一起使用，不能用于--databases和--all-databases选项
    • mysqldump -uroot -p test test --tab=”/home/mysql” --fields-terminated-by="#"
  • --fields-enclosed-by
    • 输出文件中的各个字段用给定字符包裹。与--tab选项一起使用，不能用于--databases和--all-databases选项
    • mysqldump -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by="#"
  • --fields-optionally-enclosed-by
    • 输出文件中的各个字段用给定字符选择性包裹。与--tab选项一起使用，不能用于--databases和--all-databases选项
    • mysqldump -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by=”#” --fields-optionally-enclosed-by =”#”
  • --fields-escaped-by
    • 输出文件中的各个字段忽略给定字符。与--tab选项一起使用，不能用于--databases和--all-databases选项
    • mysqldump -uroot -p mysql user --tab=”/home/mysql” --fields-escaped-by=”#”
  • --flush-logs
    • 开始导出之前刷新日志。
    • 请注意：假如一次导出多个数据库(使用选项--databases或者--all-databases)，将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs。
    • mysqldump -uroot -p --all-databases --flush-logs
  • --flush-privileges
    • 在导出mysql数据库之后，发出一条FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。
    • mysqldump -uroot -p --all-databases --flush-privileges
  • --force
    • 在导出过程中忽略出现的SQL错误。
    • mysqldump -uroot -p --all-databases --force
  • --help
    • 显示帮助信息并退出。
    • mysqldump --help
  • --hex-blob
    • 使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字段类型有BINARY、VARBINARY、BLOB。
    • mysqldump -uroot -p --all-databases --hex-blob
  • --host, -h
    • 需要导出的主机信息
    • mysqldump -uroot -p --host=localhost --all-databases
  • --ignore-table
    • 不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 ……
    • mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user
  • --include-master-host-port
    • 在--dump-slave产生的'CHANGE MASTER TO..'语句中增加'MASTER_HOST=，MASTER_PORT='
    • mysqldump -uroot -p --host=localhost --all-databases --include-master-host-port
  • --insert-ignore
    • 在插入行时使用INSERT IGNORE语句.
    • mysqldump -uroot -p --host=localhost --all-databases --insert-ignore
  • --lines-terminated-by
    • 输出文件的每行用给定字符串划分。与--tab选项一起使用，不能用于--databases和--all-databases选项。
    • mysqldump -uroot -p --host=localhost test test --tab=”/tmp/mysql” --lines-terminated-by=”##”
  • --lock-all-tables, -x
    • 提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables 选项。
    • mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables
  • --lock-tables, -l
    • 开始导出前，锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。
    • 请注意当导出多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
    • mysqldump -uroot -p --host=localhost --all-databases --lock-tables
  • --log-error
    • 附加警告和错误信息到给定文件
    • mysqldump -uroot -p --host=localhost --all-databases --log-error=/tmp/mysqldump_error_log.err
  • --master-data
    • 该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的--single-transaction选项）。该选项自动关闭--lock-tables选项。
    • mysqldump -uroot -p --host=localhost --all-databases --master-data=1;
    • mysqldump -uroot -p --host=localhost --all-databases --master-data=2;
  • --max_allowed_packet
    • 服务器发送和接受的最大包长度。
    • mysqldump -uroot -p --host=localhost --all-databases --max_allowed_packet=10240
  • --net_buffer_length
    • TCP/IP和socket连接的缓存大小。
    • mysqldump -uroot -p --host=localhost --all-databases --net_buffer_length=1024
  • --no-autocommit
    • 使用autocommit/commit 语句包裹表。
    • mysqldump -uroot -p --host=localhost --all-databases --no-autocommit
  • --no-create-db, -n
    • 只导出数据，而不添加CREATE DATABASE 语句。
    • mysqldump -uroot -p --host=localhost --all-databases --no-create-db
  • --no-create-info, -t
    • 只导出数据，而不添加CREATE TABLE 语句。
    • mysqldump -uroot -p --host=localhost --all-databases --no-create-info
  • --no-data, -d
    • 不导出任何数据，只导出数据库表结构。
    • mysqldump -uroot -p --host=localhost --all-databases --no-data
  • --no-set-names, -N
    • 等同于--skip-set-charset
    • mysqldump -uroot -p --host=localhost --all-databases --no-set-names
  • --opt
    • 等同于--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 该选项默认开启, 可以用--skip-opt禁用.
    • mysqldump -uroot -p --host=localhost --all-databases --opt
  • --order-by-primary
    • 如果存在主键，或者第一个唯一键，对每个表的记录进行排序。在导出MyISAM表到InnoDB表时有效，但会使得导出工作花费很长时间。
    • mysqldump -uroot -p --host=localhost --all-databases --order-by-primary
  • --password, -p
    • 连接数据库密码
  • --pipe(windows系统可用)
    • 使用命名管道连接mysql
    • mysqldump -uroot -p --host=localhost --all-databases --pipe
  • --port, -P
    • 连接数据库端口号
  • --protocol
    • 使用的连接协议，包括：tcp, socket, pipe, memory.
    • mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp
  • --quick, -q
    • 不缓冲查询，直接导出到标准输出。默认为打开状态，使用--skip-quick取消该选项。
    • mysqldump -uroot -p --host=localhost --all-databases
    • mysqldump -uroot -p --host=localhost --all-databases --skip-quick
  • --quote-names,-Q
    • 使用（'）引起表和列名。默认为打开状态，使用--skip-quote-names取消该选项。
    • mysqldump -uroot -p --host=localhost --all-databases
    • mysqldump -uroot -p --host=localhost --all-databases --skip-quote-names
  • --replace
    • 使用REPLACE INTO 取代INSERT INTO.
    • mysqldump -uroot -p --host=localhost --all-databases --replace
  • --result-file, -r
    • 直接输出到指定文件中。该选项应该用在使用回车换行对（\r\n）换行的系统上（例如：DOS，Windows）。该选项确保只有一行被使用。
    • mysqldump -uroot -p --host=localhost --all-databases --result-file=/tmp/mysqldump_result_file.txt
  • --routines, -R
    • 导出存储过程以及自定义函数。
    • mysqldump -uroot -p --host=localhost --all-databases --routines
  • --set-charset
    • 添加'SET NAMES default_character_set'到输出文件。默认为打开状态，使用--skip-set-charset关闭选项。
    • mysqldump -uroot -p --host=localhost --all-databases
    • mysqldump -uroot -p --host=localhost --all-databases --skip-set-charset
  • --single-transaction
    • 该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和--lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick 选项。
    • mysqldump -uroot -p --host=localhost --all-databases --single-transaction
  • --dump-date
    • 将导出时间添加到输出文件中。默认为打开状态，使用--skip-dump-date关闭选项。
    • mysqldump -uroot -p --host=localhost --all-databases
    • mysqldump -uroot -p --host=localhost --all-databases --skip-dump-date
  • --skip-opt
    • 禁用–opt选项.
    • mysqldump -uroot -p --host=localhost --all-databases --skip-opt
  • --socket,-S
    • 指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock
    • mysqldump -uroot -p --host=localhost --all-databases --socket=/tmp/mysqld.sock
  • --tab,-T
    • 为每个表在给定路径创建tab分割的文本文件。注意：仅仅用于mysqldump和mysqld服务器运行在相同机器上。
    • mysqldump -uroot -p --host=localhost test test --tab="/home/mysql"
  • --tables
    • 覆盖--databases (-B)参数，指定需要导出的表名。
    • mysqldump -uroot -p --host=localhost --databases test --tables test
  • --triggers
    • 导出触发器。该选项默认启用，用--skip-triggers禁用它。
    • mysqldump -uroot -p --host=localhost --all-databases --triggers
  • --tz-utc
    • 在导出顶部设置时区TIME_ZONE='+00:00' ，以保证在不同时区导出的TIMESTAMP 数据或者数据被移动其他时区时的正确性。
    • mysqldump -uroot -p --host=localhost --all-databases --tz-utc
  • --user, -u
    • 指定连接的用户名。
  • --verbose, --v
    • 输出多种平台信息。
  • --version, -V
    • 输出mysqldump版本信息并退出
  • --where, -w
    • 只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。
    • mysqldump -uroot -p --host=localhost --all-databases --where=” user=’root’”
  • --xml, -X
    • 导出XML格式.
    • mysqldump -uroot -p --host=localhost --all-databases --xml
  • --plugin_dir
    • 客户端插件的目录，用于兼容不同的插件版本。
    • mysqldump -uroot -p --host=localhost --all-databases --plugin_dir=”/usr/local/lib/plugin”
  • --default_auth
    • 客户端插件默认使用权限。
    • mysqldump -uroot -p --host=localhost --all-databases --default-auth=”/usr/local/lib/plugin/”

面试问题

待处理问题

  • 唯一索引和普通索引的区别，索引类别（B+树索引、全文索引、哈希索引），索引的区别
  • 为什么要用 B+tree 作为 MySql 索引的数据结构
  • 聚集索引与非聚集索引的区别
  • DDL、DML、DCL 分别指什么
  • explain 命令
  • 数据库的几大范式
  • 说说分库与分表设计，分库与分表带来的分布式困境与对应之策
  • 什么是自适应哈希索引（AHI）
  • limit 20000 加载很慢怎么解决
  • 常见的几种分布式 ID 的设计方案 基础部分
      1. 事务四大特性（ACID）
      1. 数据库隔离级别，每个级别会引发什么问题，mysql默认是哪个级别
      1. MYSQL的两种存储引擎区别（事务、锁级别等等），各自的适用场景
      1. 数据库的优化（从sql语句优化和索引两个部分回答）
      1. 索引有B+索引和hash索引，各自的区别
      1. B+索引数据结构，和B树的区别
      1. 索引的分类（主键索引、唯一索引），最左前缀原则，哪些情况索引会失效
      1. 聚集索引和非聚集索引区别。
      1. 有哪些锁（乐观锁悲观锁），select时怎么加排它锁
      1. 关系型数据库和非关系型数据库区别
      1. 了解nosql
      1. 数据库三范式，根据某个场景设计数据表（可以通过手绘ER图）
      1. 数据库的主从复制
      1. 使用explain优化sql和索引
      1. long_query怎么解决
      1. 内连接、外连接、交叉连接、笛卡儿积等 深入
      1. MVCC机制
      1. 根据具体场景，说明版本控制机制
      1. 死锁怎么解决
      1. varchar和char的使用场景。
      1. mysql并发情况下怎么解决（通过事务、隔离级别、锁）

数据库中的事务是什么?

  • 事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。

优化MYSQL数据库的方法?

  • 选取最适用的字段属性,尽可能减少定义字段长度,尽量把字段设置NOT NULL,例如'省份,性别',最好设置为ENUM
  • 使用连接（JOIN）来代替子查询
  • 使用联合(UNION)来代替手动创建的临时表
  • 建立索引

varchar快速很多，但是浪费存储空间，所以对存储不大，但在速度上有要求的可以使用char类型，反之可以用varchar类型来实例。

优化数据库的方法。说说 SQL 优化之道

  • 主机性能
  • 内存使用性能
  • 网络传输性能
  • SQL语句执行性能

ACID规则

事务在英文中是transaction，和现实世界中的交易很类似，它有如下四个特性：

  • 1、A (Atomicity) 原子性
    • 原子性很容易理解，也就是说事务里的所有操作要么全部做完，要么都不做，事务成功的条件是事务里的所有操作都成功，只要有一个操作失败，整个事务就失败，需要回滚。
    • 比如银行转账，从A账户转100元至B账户，分为两个步骤：1）从A账户取100元；2）存入100元至B账户。这两步要么一起完成，要么一起不完成，如果只完成第一步，第二步失败，钱会莫名其妙少了100元。
  • 2、C (Consistency) 一致性
    • 一致性也比较容易理解，也就是说数据库要一直处于一致的状态，事务的运行不会改变数据库原本的一致性约束。 例如现有完整性约束a+b=10，如果一个事务改变了a，那么必须得改变b，使得事务结束后依然满足a+b=10，否则事务失败。
  • 3、I (Isolation) 独立性
    • 所谓的独立性是指并发的事务之间不会互相影响，如果一个事务要访问的数据正在被另外一个事务修改，只要另外一个事务未提交，它所访问的数据就不受未提交事务的影响。
    • 比如现在有个交易是从A账户转100元至B账户，在这个交易还未完成的情况下，如果此时B查询自己的账户，是看不到新增加的100元的。
  • 4、D (Durability) 持久性
    • 持久性是指一旦事务提交后，它所做的修改将会永久的保存在数据库上，即使出现宕机也不会丢失。

Mysql存储引擎有哪些？有什么区别？

  • innodb、myisam
  • MyISAM：成熟、稳定、易于管理，快速读取。一些功能不支持（事务等），表级锁。
  • InnoDB：支持事务、外键等特性、数据行锁定。空间占用大，不支持全文索引等。

varchar和char有什么区别

  • char 长度是固定的，不管你存储的数据是多少他都会都固定的长度。而varchar则处可变长度但他要在总长度上加1字符，这个用来存储位置。
  • char 固定长度，所以在处理速度上要比

1.数据库中的事务是什么?事务的隔离级别?脏读、幻读、不可重复读是什么意思？

  • 数据库事务
    • 将一组相关操作组合为一个要么全部成功要么全部失败的单元
    • 具有ACID（原子性、一致性、隔离性和持久性）属性
      • 原子性(Atomicity)：原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚
      • 一致性(Consistent)：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须于一致性状态
      • 隔离性(Isolation)：隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所扰，多个并发事务之间要相互隔离。
      • 持久性(Duration)：持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
  • 事物隔离级别
    • Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
    • Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
    • Read committed (读已提交)：可避免脏读的发生。
    • Read uncommitted (读未提交)：最低级别，任何情况都无法保证。
  • 脏读、幻读、不可重复读
    • 脏读：脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据
    • 不可重复读：不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。
    • 幻读：是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
  • 备注：
    • 不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。
    • 幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）
    • 当前事务的隔离级别：select @@tx_isolation;

2.优化数据库的方法。说说 SQL 优化之道

  • 主机性能
  • 内存使用性能
  • 网络传输性能
  • SQL语句执行性能

3.唯一索引和普通索引的区别，索引类别（B+树索引、全文索引、哈希索引），索引的区别

为什么要用 B+tree 作为 MySql 索引的数据结构

聚集索引与非聚集索引的区别

Mysql存储引擎有哪些？有什么区别？存储引擎的 InnoDB 与 MyISAM区别、优缺点、使用场景？

varchar和char有什么区别

DDL、DML、DCL 分别指什么

explain 命令

数据库的几大范式

说说分库与分表设计，分库与分表带来的分布式困境与对应之策

什么是自适应哈希索引（AHI）

limit 20000 加载很慢怎么解决

常见的几种分布式 ID 的设计方案

Redis

数据结构