【摘要】 INSERT INTOINSERT INTO用于向表中插入数据，遇到主键重复时会自动忽略当前写入数据，不做更新，作用等同于INSERT IGNORE INTO。语法   INSERT [IGNORE] INTO table_name [( column_name [, …] )] [VALUES] [(value_list[, …])] [query];参数IGNORE：可选参数，若系统中已...详见博客：https://bbs.huaweicloud.com/blogs/221542

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。简而言之，存储引擎就是指表的类型。数据库的存储引擎决定了表在计算机中的存储方式。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。现在许多数据库管理系统都支持多种不同的存储引擎。MySQL 的核心就是存储引擎。MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。MySQL 5.7 支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、CSV、BLACKHOLE 等。可以使用SHOW ENGINES;语句查看系统所支持的引擎类型，结果如图所示。Support 列的值表示某种引擎是否能使用，YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认的存储引擎。下面简要描写几种存储引擎，后面会对其中的几种（主要是 InnoDB 和 MyISAM ）进行详细讲解。像 NDB 这样的需要更多扩展性的讨论，这超出了本教程的介绍范畴，所以在教程后面对它们不会介绍太多。表 1 MySQL 的存储引擎存储引擎描述ARCHIVE用于数据存档的引擎，数据被插入后就不能在修改了，且不支持索引。CSV在存储数据时，会以逗号作为数据项之间的分隔符。BLACKHOLE会丢弃写操作，该操作会返回空内容。FEDERATED将数据存储在远程数据库中，用来访问远程表的存储引擎。InnoDB具备外键支持功能的事务处理引擎MEMORY置于内存的表MERGE用来管理由多个 MyISAM 表构成的表集合MyISAM主要的非事务处理存储引擎NDBMySQL 集群专用存储引擎有几种存储引擎的名字还有同义词，例如，MRG_MyISAM 和 NDBCLUSTER 分别是 MERGE 和 NDB 的同义词。存储引擎 MEMORY 和 InnoDB 在早期分别称为 HEAP 和 Innobase。虽然后面两个名字仍能被识别，但是已经被废弃了。

查看mysql 事务隔离级别mysql> show variables like '%isolation%';+---------------+----------------+| Variable_name | Value     |+---------------+----------------+| tx_isolation | READ-COMMITTED |+---------------+----------------+1 row in set (0.00 sec)可以看到当前的事务隔离级别为 READ-COMMITTED 读提交下面看看当前隔离级别下的事务隔离详情，开启两个查询终端D、B。下面有一个order表，初始数据如下mysql> select * from `order`;+----+--------+| id | number |+----+--------+| 13 |   1 |+----+--------+1 row in set (0.00 sec)第一步，在D,B中都开启事务mysql> start transaction;Query OK, 0 rows affected (0.00 sec)第二步查询两个终端中的number值D mysql> select * from `order`;+----+--------+| id | number |+----+--------+| 13 |   1 |+----+--------+1 row in set (0.00 sec)B mysql> select * from `order`;+----+--------+| id | number |+----+--------+| 13 |   1 |+----+--------+1 row in set (0.00 sec)第三步将B中的number修改为2,但不提交事务mysql> update `order` set number=2;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0第四步查询A中的值mysql> select * from `order`;+----+--------+| id | number |+----+--------+| 13 |   1 |+----+--------+1 row in set (0.00 sec)发现A中的值并没有修改。第五步，提交事务B,再次查询A中的值Bmysql> commit;Query OK, 0 rows affected (0.01 sec)Dmysql> select * from `order`;+----+--------+| id | number |+----+--------+| 13 |   2 |+----+--------+1 row in set (0.00 sec)发现A中的值已经更改第六步，提交A中的事务，再次查询D，B的值。Dmysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from `order`;+----+--------+| id | number |+----+--------+| 13 |   2 |+----+--------+1 row in set (0.00 sec)Bmysql> select * from `order`;+----+--------+| id | number |+----+--------+| 13 |   2 |+----+--------+1 row in set (0.00 sec)完成示意图我们可以看到，在事务隔离级别为读已提交 的情况下，当B中事务提交了之后，即使D未提交也可以读到B事务提交的结果。这样解决了脏读的问题。

[Warning] Could not increase number of max_open_files to more than 1024 (request: 4907)这个错误常见，起码遇到两次了。解决方法很简单：vi /usr/lib/systemd/system/mariadb.service #在[service]下面加 [Service] LimitNOFILE=infinity然后重启进程

主要用途：pt-kill是用来kill MySQL连接的一个工具，在MySQL中因为空闲连接较多导致超过最大连接数，或某个有问题的sql导致mysql负载很高时，需要将其KILL掉来保证服务器正常运行。 从show processlist 中获取满足条件的连接或者从包含show processlist的文件中读取满足条件的连接并打印或者杀掉或者执行其他操作。这个工具在工作中实用性很高，当服务器连接出现异常后第一想到的就是pt-kill，我们这里主要用来防止某些select操作时间过长，从而影响其他线上SQL。 范例：pt-kill --log-dsn D=test1,t=pk_log --create-log-table --host=host2 --user=root --password=123--port=3306 --busy-time=10 --print --kill-query --match-info "SELECT|select" --victims all 该使用范例的作用：如果不存在test1.pk_log表，则创建该表，然后将所有pt-kill的操作记录到该表中。对所有查询时间超过10秒的SELECT语句进行print显示出来，同时会kill该query。pt-kill 默认检查间隔为5秒。

id：id是一组数字，表示查询中执行select子句或操作表的顺序，如果id相同，则执行顺序从上至下，如果是子查询，id的序号会递增，id越大则优先级越高，越先会被执行。id列为null的就表是这是一个结果集，不需要使用它来进行查询。 select_type：simple：表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个。primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个。subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquerydependent subquery：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响。derived：from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select。union：union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union。dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响union result：包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null。 table：显示的查询表名，如果查询使用了别名，那么这里显示的是别名。如果不涉及对数据表的操作，那么这显示为null。如果显示为尖括号括起来的<derived N>就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>，与<derived N>类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。 type：依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL。除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。

主要用途：pt-kill是用来kill MySQL连接的一个工具，在MySQL中因为空闲连接较多导致超过最大连接数，或某个有问题的sql导致mysql负载很高时，需要将其KILL掉来保证服务器正常运行。 从show processlist 中获取满足条件的连接或者从包含show processlist的文件中读取满足条件的连接并打印或者杀掉或者执行其他操作。这个工具在工作中实用性很高，当服务器连接出现异常后第一想到的就是pt-kill，我们这里主要用来防止某些select操作时间过长，从而影响其他线上SQL。 范例：pt-kill --log-dsn D=test1,t=pk_log --create-log-table --host=host2 --user=root --password=123--port=3306 --busy-time=10 --print --kill-query --match-info "SELECT|select" --victims all 该使用范例的作用：如果不存在test1.pk_log表，则创建该表，然后将所有pt-kill的操作记录到该表中。对所有查询时间超过10秒的SELECT语句进行print显示出来，同时会kill该query。pt-kill 默认检查间隔为5秒。

在 MySQL 数据库，变量分为系统变量和用户自定义变量。系统变量以 @@ 开头，用户自定义变量以 @ 开头。服务器维护着两种系统变量，即全局变量（GLOBAL VARIABLES）和会话变量（SESSION VARIABLES）。全局变量影响 MySQL 服务的整体运行方式，会话变量影响具体客户端连接的操作。每一个客户端成功连接服务器后，都会产生与之对应的会话。会话期间，MySQL 服务实例会在服务器内存中生成与该会话对应的会话变量，这些会话变量的初始值是全局变量值的拷贝。查看系统变量可以使用以下命令查看 MySQL 中所有的全局变量信息。SHOW GLOBAL VARIABLES; 可以使用以下命令查看与当前会话相关的所有会话变量以及全局变量。SHOW SESSION VARIABLES;其中，SESSION 关键字可以省略。MySQL 中的系统变量以两个“@”开头。@@global 仅仅用于标记全局变量；@@session 仅仅用于标记会话变量；@@ 首先标记会话变量，如果会话变量不存在，则标记全局变量。MySQL 中有一些系统变量仅仅是全局变量，例如 innodb_data_file_path，可以使用以下 3 种方法查看：SHOW GLOBAL VARIABLES LIKE 'innodb_data_file_path';SHOW SESSION VARIABLES LIKE 'innodb_data_file_path';SHOW VARIABLES LIKE 'innodb_data_file_path';MySQL 中有一些系统变量仅仅是会话变量，例如 MySQL 连接 ID 会话变量 pseudo_thread_id，可以使用以下 2 种方法查看。SHOW SESSION VARIABLES LIKE 'pseudo_thread_id';SHOW VARIABLES LIKE 'pseudo_thread_id';MySQL 中有一些系统变量既是全局变量，又是会话变量，例如系统变量 character_set_client 既是全局变量，又是会话变量。SHOW SESSION VARIABLES LIKE 'character_set_client';SHOW VARIABLES LIKE 'character_set_client';此时查看全局变量的方法如下：SHOW GLOBAL VARIABLES LIKE 'character_set_client';设置系统变量可以通过以下方法设置系统变量：修改 MySQL 源代码，然后对 MySQL 源代码重新编译（该方法适用于 MySQL 高级用户，这里不做阐述）。在 MySQL 配置文件（mysql.ini 或 mysql.cnf）中修改 MySQL 系统变量的值（需要重启 MySQL 服务才会生效）。在 MySQL 服务运行期间，使用 SET 命令重新设置系统变量的值。服务器启动时，会将所有的全局变量赋予默认值。这些默认值可以在选项文件中或在命令行中对执行的选项进行更改。更改全局变量，必须具有 SUPER 权限。设置全局变量的值的方法如下：SET @@global.innodb_file_per_table=default;SET @@global.innodb_file_per_table=ON;SET global innodb_file_per_table=ON;需要注意的是，更改全局变量只影响更改后连接客户端的相应会话变量，而不会影响目前已经连接的客户端的会话变量（即使客户端执行 SET GLOBAL 语句也不影响）。也就是说，对于修改全局变量之前连接的客户端只有在客户端重新连接后，才会影响到客户端。客户端连接时，当前全局变量的值会对客户端的会话变量进行相应初始化。设置会话变量不需要特殊权限，但客户端只能更改自己的会话变量，而不能更改其它客户端的会话变量。设置会话变量的值的方法如下：SET @@session.pseudo_thread_id=5;SET session pseudo_thread_id=5;SET @@pseudo_thread_id=5;SET pseudo_thread_id = 5;如果没有指定修改全局变量还是会话变量，服务器会当作会话变量来处理。比如：SET @@sort_buffer_size = 50000;上面语句没有指定是 GLOBAL 还是 SESSION，服务器会当做 SESSION 处理。使用 SET 设置全局变量或会话变量成功后，如果 MySQL 服务重启，数据库的配置就又会重新初始化。一切按照配置文件进行初始化，全局变量和会话变量的配置都会失效。MySQL 中还有一些特殊的全局变量，如 log_bin、tmpdir、version、datadir，在 MySQL 服务实例运行期间它们的值不能动态修改，也就是不能使用 SET 命令进行重新设置，这种变量称为静态变量。数据库管理员可以使用前面提到的修改源代码或更改配置文件来重新设置静态变量的值。

一、传统binlog主从复制，跳过报错方法mysql> stop slave;mysql> set global sql_slave_skip_counter = 1;mysql> start slave;mysql> show slave status \G二、GTID主从复制，跳过报错方法mysql> stop slave； #先关闭slave复制；mysql> change master to ...省略... #配置主从复制；mysql> show slave status\G #查看主从状态；发现报错：mysql> show slave status\G*************************** 1. row ***************************        Slave_IO_State: Waiting for master to send event         Master_Host: 172.19.195.212         Master_User: master-slave         Master_Port: 3306        Connect_Retry: 60       Master_Log_File: mysql-bin.000021     Read_Master_Log_Pos: 194        Relay_Log_File: nginx-003-relay-bin.000048        Relay_Log_Pos: 454    Relay_Master_Log_File: mysql-bin.000016       Slave_IO_Running: Yes      Slave_SQL_Running: No       Replicate_Do_DB:     Replicate_Ignore_DB:      Replicate_Do_Table:    Replicate_Ignore_Table:   Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table:          Last_Errno: 1007          Last_Error: Error 'Can't create database 'code'; database exists' on query. Default database: 'code'. Query: 'create database code'         Skip_Counter: 0     Exec_Master_Log_Pos: 8769118       Relay_Log_Space: 3500       Until_Condition: None        Until_Log_File:        Until_Log_Pos: 0      Master_SSL_Allowed: No      Master_SSL_CA_File:      Master_SSL_CA_Path:       Master_SSL_Cert:      Master_SSL_Cipher:        Master_SSL_Key:    Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No        Last_IO_Errno: 0        Last_IO_Error:        Last_SQL_Errno: 1007        Last_SQL_Error: Error 'Can't create database 'code'; database exists' on query. Default database: 'code'. Query: 'create database code' Replicate_Ignore_Server_Ids:       Master_Server_Id: 100         Master_UUID: fea89052-11ef-11eb-b241-00163e00a190       Master_Info_File: /usr/local/mysql/data/master.info          SQL_Delay: 0     SQL_Remaining_Delay: NULL   Slave_SQL_Running_State:      Master_Retry_Count: 86400         Master_Bind:   Last_IO_Error_Timestamp:   Last_SQL_Error_Timestamp: 201022 09:31:29        Master_SSL_Crl:      Master_SSL_Crlpath:      Retrieved_Gtid_Set: fea89052-11ef-11eb-b241-00163e00a190:8-5617      Executed_Gtid_Set: a56c9b04-11f1-11eb-a855-00163e128853:1-11224,fea89052-11ef-11eb-b241-00163e00a190:1-5614        Auto_Position: 1     Replicate_Rewrite_DB:         Channel_Name:      Master_TLS_Version: 1 row in set (0.01 sec)可以看到 Slave_SQL_Running 为 NO，表示运行取回的二进制日志出了问题；在 Last_Error 中也可以看到大概的报错；（因为我之前的操作，大概可以判断出 是因为主库的二进制日志中有创建code库的sql，而从库上我已经创建了这个库，应该是产生了冲突；）解决方法：1、如果清楚自己之前的操作，可以将从库中产生冲突的库删除；2、或者通过跳过GTID报错的事务的方法--- 通过 Last_SQL_Errno 报错编号查询具体的报错事务mysql> select * from performance_schema.replication_applier_status_by_worker where LAST_ERROR_NUMBER=1007\G*************************** 1. row ***************************     CHANNEL_NAME:      WORKER_ID: 0      THREAD_ID: NULL    SERVICE_STATE: OFFLAST_SEEN_TRANSACTION: fea89052-11ef-11eb-b241-00163e00a190:5615  LAST_ERROR_NUMBER: 1007  LAST_ERROR_MESSAGE: Error 'Can't create database 'code'; database exists' on query. Default database: 'code'. Query: 'create database code' LAST_ERROR_TIMESTAMP: 2020-10-22 09:31:291 row in set (0.00 sec)mysql> stop slave;Query OK, 0 rows affected (0.00 sec)--- 跳过查找到报错的事务（LAST_SEEN_TRANSACTION 的值）mysql> set @@session.gtid_next='fea89052-11ef-11eb-b241-00163e00a190:5615';Query OK, 0 rows affected (0.00 sec)mysql> begin;Query OK, 0 rows affected (0.00 sec)--- 提交一个空的事务，因为设置gtid_next后，gtid的生命周期开始了，必须通过显性的提交一个事务来结束；mysql> commit;Query OK, 0 rows affected (0.00 sec)--- 设置回自动模式；mysql> set @@session.gtid_next=automatic;Query OK, 0 rows affected (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)

MySQL导致的CPU高负载问题   今天下午发现了一个MySQL导致的向上服务器负载高的问题，事情的背景如下：   在某个新服务器上，新建了一个MySQL的实例，该服务器上面只有MySQL这一个进程，但是CPU的负载却居高不下，使用top命令查询的结果如下：[dba_mysql@dba-mysql ~]$ top top - 17:12:44 up 104 days, 20 min, 2 users, load average: 1.06, 1.02, 1.00Tasks: 218 total,  1 running, 217 sleeping,  0 stopped,  0 zombieCpu0 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stCpu1 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stCpu2 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stCpu3 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stCpu4 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stCpu5 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stCpu6 :100.0%us, 0.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stCpu7 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stMem: 16318504k total, 7863412k used, 8455092k free,  322048k buffersSwap: 5242876k total,    0k used, 5242876k free, 6226588k cached  PID USER   PR NI VIRT RES SHR S %CPU %MEM  TIME+ COMMAND                                     75373 mysql   20  0 845m 699m 29m S 100.0 4.4 112256:10 mysqld                                     43285 root   20  0 174m 40m 19m S 0.7 0.3 750:40.75 consul                                      116553 root   20  0 518m 13m 4200 S 0.3 0.1  0:05.78 falcon-agent                                   116596 nobody  20  0 143m 6216 2784 S 0.3 0.0  0:00.81 python                                      124304 dba_mysq 20  0 15144 1420 1000 R 0.3 0.0  0:02.09 top                                         1 root   20  0 21452 1560 1248 S 0.0 0.0  0:02.43 init 从上面的结果中，可以看到，8核的cpu只有一个核上面的负载是100%，其他的都是0%，而按照CPU使用率排序的结果也是mysqld的进程占用CPU比较多。   之前从来没有遇到过这个问题，当时第一反应是在想是不是有些业务层面的问题，比如说一些慢查询一直在占用CPU的资源，于是登陆到MySQL上使用show processlist查看了当前的进程，发现除了有少许update操作之外，没有其他的SQL语句在执行。于是我又查看了一眼慢日志，发现慢日志中的SQL语句执行时间都很短，大多数都是由于未使用索引导致的，但是扫描的记录数都很少，只有几百行，这样看起来业务层面的问题是不存在的。  排除了业务层面的问题，现在看看数据库层面的问题，查看了一眼buffer pool，可以看到这个值是：mysql--dba_admin@127.0.0.1:(none) 17:20:35>>show variables like '%pool%';+-------------------------------------+----------------+| Variable_name            | Value     |+-------------------------------------+----------------+| innodb_buffer_pool_chunk_size    | 5242880    || innodb_buffer_pool_dump_at_shutdown | ON       || innodb_buffer_pool_dump_now     | OFF      || innodb_buffer_pool_dump_pct     | 25       || innodb_buffer_pool_filename     | ib_buffer_pool || innodb_buffer_pool_instances    | 1       || innodb_buffer_pool_load_abort    | OFF      || innodb_buffer_pool_load_at_startup | ON       || innodb_buffer_pool_load_now     | OFF      || innodb_buffer_pool_size       | 5242880    || thread_pool_high_prio_mode     | transactions  || thread_pool_high_prio_tickets    | 4294967295   || thread_pool_idle_timeout      | 60       || thread_pool_max_threads       | 100000     || thread_pool_oversubscribe      | 3       || thread_pool_size          | 8       || thread_pool_stall_limit       | 500      |+-------------------------------------+----------------+17 rows in set (0.01 sec)从这个结果来看，buffer pool的大小只有5M大小，肯定是有问题的，一般情况下，线上环境的buffer pool都是1G往上，于是我查看了my.cnf配置文件，在配置文件中发现这个实例在启动的时候，innodb_buffer_pool_size的设置是0M，是的，没有看错，是0M。这里不得不提另外一个参数，我们可以看到innodb_buffer_pool_size的大小和innodb_buffer_pool_chunk_size的大小一样，这个chunk的概念是内存块，也就是说每次申请buffer pool的时候，是以"内存块"为单位申请的，一个buffer pool当中包含多个内存块，所以buffer pool size的大小需要是chunk size的整数倍。    由于innodb_buffer_pool_chunk_size本身的值为5M，当我们设置它为0M时，它会自动的将其大小设置为5M的倍数，所以我们的innodb_buffer_pool_size值是5M。    既然buffer pool的值比较小，那么我将它改成1G的大小，看看这个问题还会不会发生：mysql--dba_admin@127.0.0.1:(none) 17:20:41>>set global innodb_buffer_pool_size=1073741824;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql--dba_admin@127.0.0.1:(none) 17:23:34>>show variables like '%pool%';         +-------------------------------------+----------------+| Variable_name            | Value     |+-------------------------------------+----------------+| innodb_buffer_pool_chunk_size    | 5242880    || innodb_buffer_pool_dump_at_shutdown | ON       || innodb_buffer_pool_dump_now     | OFF      || innodb_buffer_pool_dump_pct     | 25       || innodb_buffer_pool_filename     | ib_buffer_pool || innodb_buffer_pool_instances    | 1       || innodb_buffer_pool_load_abort    | OFF      || innodb_buffer_pool_load_at_startup | ON       || innodb_buffer_pool_load_now     | OFF      || innodb_buffer_pool_size       | 1074790400   || thread_pool_high_prio_mode     | transactions  || thread_pool_high_prio_tickets    | 4294967295   || thread_pool_idle_timeout      | 60       || thread_pool_max_threads       | 100000     || thread_pool_oversubscribe      | 3       || thread_pool_size          | 8       || thread_pool_stall_limit       | 500      |+-------------------------------------+----------------+17 rows in set (0.00 sec)操作如上，这样我们修改buffer pool的值为1G，我们设置的值是1073741824，而实际的值变成了1074790400，这个原因在上面已经说过了，就是chunk size的值影响的。此时使用top命令观察CPU使用情况：[dba_mysql@dba-mysql ~]$ toptop - 22:19:09 up 104 days, 5:26, 2 users, load average: 0.45, 0.84, 0.86Tasks: 218 total,  1 running, 217 sleeping,  0 stopped,  0 zombieCpu0 : 0.3%us, 0.3%sy, 0.0%ni, 99.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stCpu1 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stCpu2 : 1.0%us, 0.0%sy, 0.0%ni, 99.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stCpu3 : 1.0%us, 0.0%sy, 0.0%ni, 99.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stCpu4 : 0.3%us, 0.3%sy, 0.0%ni, 99.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stCpu5 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stCpu6 : 0.0%us, 0.3%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stCpu7 : 0.7%us, 0.0%sy, 0.0%ni, 99.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stMem: 16318504k total, 8008140k used, 8310364k free,  322048k buffersSwap: 5242876k total,    0k used, 5242876k free, 6230600k cached  PID USER   PR NI VIRT RES SHR S %CPU %MEM  TIME+ COMMAND                                     43285 root   20  0 174m 40m 19m S 1.0 0.3 753:07.38 consul                                      116842 root   20  0 202m 17m 5160 S 1.0 0.1  0:21.30 python                                       75373 mysql   20  0 1966m 834m 29m S 0.7 5.2 112313:36 mysqld                                      116553 root   20  0 670m 14m 4244 S 0.7 0.1  0:44.31 falcon-agent                                   116584 root   20  0 331m 11m 3544 S 0.7 0.1  0:37.92 python2.6                                       1 root   20  0 21452 1560 1248 S 0.0 0.0  0:02.43 init 可以发现，CPU的使用率已经下去了，为了防止偶然现象，我又重新把buffer pool的大小改成了最初的5M的值，发现之前的问题又复现了，也就是说，设置大的buffer pool确实是一种解决方法。    到这里，问题是解决了，但是这个问题背后引发的一些东西却值得思考，小的buffer pool为什么会导致其中一个CPU的使用率是100%？   这里，我能想到的一个原因是5M的buffer pool太小了，会导致业务SQL在读取数据的时候和磁盘频繁的交互，而磁盘的速度比较慢，所以会提高IO负载，导致CPU的负载过高，至于为什么只有一个CPU的负载比较高，其他的近乎为0。

先看一下最简单的例子，在test中，添加一个字段，字段名为birth，类型为date类型。mysql> alter table test add column birth date;Query OK, 0 rows affected (0.36 sec)Records: 0  Duplicates: 0  Warnings: 0查询一下数据，看看结果：mysql> select * from test;+------+--------+----------------------------------+------------+-------+| t_id | t_name | t_password                       | t_birth    | birth |+------+--------+----------------------------------+------------+-------+|    1 | name1  | 12345678901234567890123456789012 | NULL       | NULL  ||    2 | name2  | 12345678901234567890123456789012 | 2013-01-01 | NULL  |+------+--------+----------------------------------+------------+-------+2 rows in set (0.00 sec)从上面结果可以看出，插入的birth字段，默认值为空。我们再来试一下，添加一个birth1字段，设置它不允许为空。mysql> alter table test add column birth1 date not null;Query OK, 0 rows affected (0.16 sec)Records: 0  Duplicates: 0  Warnings: 0居然执行成功了！？意外了！我原来以为，这个语句不会成功的，因为我没有给他指定一个默认值。我们来看看数据：mysql> select * from test;+------+--------+----------------------------------+------------+-------+------------+| t_id | t_name | t_password                       | t_birth    | birth | birth1     |+------+--------+----------------------------------+------------+-------+------------+|    1 | name1  | 12345678901234567890123456789012 | NULL       | NULL  | 0000-00-00 ||    2 | name2  | 12345678901234567890123456789012 | 2013-01-01 | NULL  | 0000-00-00 |+------+--------+----------------------------------+------------+-------+------------+2 rows in set (0.00 sec)哦，明白了，系统自动将date类型的值，设置了一个默认值：0000-00-00。下面我来直接指定一个默认值看看：mysql> alter table test add column birth2 date default '2013-1-1';Query OK, 0 rows affected (0.28 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from test;+------+--------+----------------------------------+------------+-------+------------+------------+| t_id | t_name | t_password                       | t_birth    | birth | birth1     | birth2     |+------+--------+----------------------------------+------------+-------+------------+------------+|    1 | name1  | 12345678901234567890123456789012 | NULL       | NULL  | 0000-00-00 | 2013-01-01 ||    2 | name2  | 12345678901234567890123456789012 | 2013-01-01 | NULL  | 0000-00-00 | 2013-01-01 |+------+--------+----------------------------------+------------+-------+------------+------------+2 rows in set (0.00 sec)看到没，将增加的birth2字段，就有一个默认值了，而且这个默认值是我们手工指定的。

在 MySQL 中，除了常见的字符之外，我们还会遇到一些特殊的字符，如换行符、回车符等。这些符号无法用字符来表示，因此需要使用某些特殊的字符来表示特殊的含义，这些字符就是转义字符。转义字符一般以反斜杠符号\开头，用来说明后面的字符不是字符本身的含义，而是表示其它的含义。MySQL 中常见的转义字符如下表所示。转义字符转义后的字符\"双引号（"）\'单引号（'）\\反斜线（\）\n换行符\r回车符\t制表符\0ASCII 0（NUL）\b退格符转义字符区分大小写，例如：'\b' 解释为退格，但 '\B' 解释为 'B'。有以下几点需要注意：字符串的内容包含单引号'时，可以用单引号'或反斜杠\来转义。字符串的内容包含双引号"时，可以用双引号"或反斜杠\来转义。一个字符串用双引号"引用时，该字符串中的单引号 '不需要特殊对待，且不必被重复转义。同理，一个字符串用单引号'引用时，该字符串中的双引号"不需要特殊对待，且不必被重复转义。例 1下面通过 SELECT 语句演示单引号' 双引号" 和反斜杠\的使用：mysql> SELECT '华为云数据库', '"华为云数据库"','""华为云数据库""','华为云''数据库',  '\'华为云数据库';+-------------+---------------+-----------------+--------------+--------------+| 华为云数据库 | "华为云数据库" | ""华为云数据库"" | 华为云'数据库 | '华为云数据库 |+-------------+---------------+-----------------+--------------+--------------+1 row in set (0.07 sec)mysql> SELECT "华为云数据库 ", "'华为云数据库'", "''华为云数据库''", "华为云""数据库", "\"华为云数据库";+--------------+---------------+-----------------+--------------+--------------+| 华为云数据库  | '华为云数据库' | ''华为云数据库'' | 华为云数据库 | "华为云数据库 |+--------------+---------------+-----------------+--------------+--------------+1 row in set (0.00 sec)mysql> SELECT "This\nIs\n华为云\n数据库+----------------------+| ThisIs华为云数据库 |+----------------------+1 row in set (0.00 sec)如果你想要把二进制数据插入到一个 BLOB 列，下列字符必须使用反斜杠\转义：   NUL：ASCII  0。可以使用“\0“表示。   \：ASCII  92，反斜线。用“\\”表示。 ' ：ASCII  39，单引号。用“\'”表示。   " ：ASCII  34，双引号。用“\"”表示。

MySQL 提供了大量的数据类型，为了优化存储和提高数据库性能，在任何情况下都应该使用最精确的数据类型。 前面主要对 MySQL 中的数据类型及其基本特性进行了描述，包括它们能够存放的值的类型和占用空间等。本节主要讨论创建数据库表时如何选择数据类型。 可以说字符串类型是通用的数据类型，任何内容都可以保存在字符串中，数字和日期都可以表示成字符串形式。 但是也不能把所有的列都定义为字符串类型。对于数值类型，如果把它们设置为字符串类型的，会使用很多的空间。并且在这种情况下使用数值类型列来存储数字，比使用字符串类型更有效率。 另外需要注意的是，由于对数字和字符串的处理方式不同，查询结果也会存在差异。例如，对数字的排序与对字符串的排序是不一样的。 例如，数字 2 小于数字 11，但字符串 '2' 却比字符串 '11' 大。此问题可以通过把列放到数字上下文中来解决，如下面 SQL 语句：SELECT course+ 0 as num ... ORDER BY num;让 course 列加上 0，可以强制列按数字的方式来排序，但这么做很明显是不合理的。 如果让 MySQL 把一个字符串列当作一个数字列来对待，会引发很严重的问题。这样做会迫使让列里的每一个值都执行从字符串到数字的转换，操作效率低。而且在计算过程中使用这样的列，会导致 MySQL 不会使用这些列上的任何索引，从而进一步降低查询的速度。 所以我们在选择数据类型时要考虑存储、查询和整体性能等方面的问题。 在选择数据类型时，首先要考虑这个列存放的值是什么类型的。一般来说，用数值类型列存储数字、用字符类型列存储字符串、用时态类型列存储日期和时间。数值类型对于数值类型列，如果要存储的数字是整数（没有小数部分），则使用整数类型；如果要存储的数字是小数（带有小数部分），则可以选用 DECIMAL 或浮点类型，但是一般选择 FLOAT 类型（浮点类型的一种）。例如，如果列的取值范围是 1~99999 之间的整数，则 MEDIUMINT UNSIGNED 类型是最好的选择。MEDIUMINT 是整数类型，UNSIGNED 用来将数字类型无符号化。比如 INT 类型的取值范围是 -2 147 483 648 ～ 2 147 483 647，那么 INT UNSIGNED 类型的取值范围就是 0 ～ 4 294 967 295。如果需要存储某些整数值，则值的范围决定了可选用的数据类型。如果取值范围是 0~1000，那么可以选择 SMALLINT~BIGINT 之间的任何一种类型。如果取值范围超过了 200 万，则不能使用 SMALLINT，可以选择的类型变为从 MEDIUMINT 到 BIGINT 之间的某一种。 当然，完全可以为要存储的值选择一种最“大”的数据类型。但是，如果正确选择数据类型，不仅可以使表的存储空间变小，也会提高性能。因为与较长的列相比，较短的列的处理速度更快。当读取较短的值时，所需的磁盘读写操作会更少，并且可以把更多的键值放入内存索引缓冲区里。 如果无法获知各种可能值的范围，则只能靠猜测，或者使用 BIGINT 以满足最坏情况的需要。如果猜测的类型偏小，那么也不是就无药可救。将来，还可以使用 ALTER TABLE 让该列变得更大些。 如果数值类型需要存储的数据为货币，如人民币。在计算时，使用到的值常带有元和分两个部分。它们看起来像是浮点值，但 FLOAT 和 DOUBLE 类型都存在四舍五入的误差问题，因此不太适合。因为人们对自己的金钱都很敏感，所以需要一个可以提供完美精度的数据类型。 可以把货币表示成 DECIMAL(M,2) 类型，其中 M 为所需取值范围的最大宽度。这种类型的数值可以精确到小数点后 2 位。DECIMAL 的优点在于不存在舍入误差，计算是精确的。 对于电话号码、信用卡号和社会保险号都会使用非数字字符。因为空格和短划线不能直接存储到数字类型列里，除非去掉其中的非数字字符。但即使去掉了其中的非数字字符，也不能把它们存储成数值类型，以避免丢失开头的“零”。日期和时间类型MySQL 对于不同种类的日期和时间都提供了数据类型，比如 YEAR 和 TIME。如果只需要记录年份，则使用 YEAR 类型即可；如果只记录时间，可以使用 TIME 类型。 如果同时需要记录日期和时间，则可以使用 TIMESTAMP 或者 DATETIME 类型。由于TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，因此存储较大的日期最好使用 DATETIME。 TIMESTAMP 也有一个 DATETIME 不具备的属性。默认情况下，当插入一条记录但并没有指定 TIMESTAMP 这个列值时，MySQL 会把 TIMESTAMP 列设为当前的时间。因此当需要插入记录和当前时间时，使用 TIMESTAMP 是方便的，另外 TIMESTAMP 在空间上比 DATETIME 更有效。 MySQL 没有提供时间部分为可选的日期类型。DATE 没有时间部分，DATETIME 必须有时间部分。如果时间部分是可选的，那么可以使用 DATE 列来记录日期，再用一个单独的 TIME 列来记录时间。然后，设置 TIME 列可以为 NULL。SQL 语句如下：CREATE TABLE mytb1 (    date DATE NOT NULL,  #日期是必需的    time TIME NULL  #时间可选(可能为NULL));字符串类型字符串类型没有像数字类型列那样的“取值范围"，但它们都有长度的概念。如果需要存储的字符串短于 256 个字符，那么可以使用 CHAR、VARCHAR 或 TINYTEXT。如果需要存储更长一点的字符串，则可以选用 VARCHAR 或某种更长的 TEXT 类型。 如果某个字符串列用于表示某种固定集合的值，那么可以考虑使用数据类型 ENUM 或 SET。CHAR 和 VARCHAR 之间的特点和选择CHAR 和 VARCHAR 的区别如下:CHAR 是固定长度字符，VARCHAR 是可变长度字符。CHAR 会自动删除插入数据的尾部空格，VARCHAR 不会删除尾部空格。 CHAR 是固定长度，所以它的处理速度比 VARCHAR 的速度要快，但是它的缺点就是浪费存储空间。所以对存储不大，但在速度上有要求的可以使用 CHAR 类型，反之可以使用 VARCHAR类型来实现。 存储引擎对于选择 CHAR 和 VARCHAR 的影响:对于 MyISAM 存储引擎，最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。对于InnoDB存储引擎，最好使用可变长度的数据列，因为 InnoDB 数据表的存储格式不分固定长度和可变长度，因此使用 CHAR 不一定比使用 VARCHAR 更好，但由于 VARCHAR 是按照实际的长度存储，比较节省空间，所以对磁盘 I/O 和数据存储总量比较好。ENUM 和 SETENUM 只能取单值，它的数据列表是一个枚举集合。它的合法取值列表最多允许有 65 535个成员。因此，在需要从多个值中选取一个时，可以使用 ENUM。比如，性别字段适合定义，为 ENUM 类型，每次只能从‘男’或‘女’中取一个值。 SET 可取多值。它的合法取值列表最多允许有 64 个成员。空字符串也是一个合法的 SET值。在需要取多个值的时候，适合使用 SET 类型，比如，要存储一个人兴趣爱好，最好使用SET类型。 ENUM 和 SET 的值是以字符串形式出现的，但在内部，MySQL 以数值的形式存储它们。二进制类型BLOB 是二进制字符串，TEXT 是非二进制字符串，两者均可存放大容量的信息。BLOB 主要存储图片、音频信息等，而 TEXT 只能存储纯文本文件。

导出数据SHOW VARIABLES LIKE "secure_file_priv";查看默认导出目录mysql> SELECT * FROM student INTO OUTFILE "G:\ProgramData\MySQL\MySQL Server 8.0\Uploads\student.txt";ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement解决方法SELECT * FROM student INTO OUTFILE "G:/ProgramData/MySQL/MySQL Server 8.0/Uploads/student.txt";Query OK, 2 rows affected (0.02 sec)数据展示导入数据报错mysql> load data local infile 'G:/ProgramData/MySQL/MySQL Server 8.0/Uploads/student.txt' -> into table student(a,b,c);ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides解决方法mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';+---------------+-------+| Variable_name | Value |+---------------+-------+| local_infile | OFF |+---------------+-------+1 row in set, 1 warning (0.01 sec)mysql> SET GLOBAL local_infile = true;Query OK, 0 rows affected (0.00 sec)mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';+---------------+-------+| Variable_name | Value |+---------------+-------+| local_infile | ON |+---------------+-------+1 row in set, 1 warning (0.01 sec)报错mysql> load data local infile 'G:\ProgramData\MySQL\MySQL Server 8.0\Uploads\student.txt' -> into table student(id,name,score);ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.解决方法C:\Users>mysql -uroot -p --local-infile使用这种方法登录报错mysql> load data local infile 'G:\ProgramData\MySQL\MySQL Server 8.0\Uploads\student.txt' -> into table student(id,name,score);ERROR 2 (HY000): File 'G:ProgramDataMySQLMySQL Server 8.0Uploadsstudent.txt' not found (OS errno 2 - No such file or directory)解决方法mysql> load data local infile 'G://ProgramData/MySQL/MySQL Server 8.0/Uploads/student.txt' -> into table student(id,name,score);Query OK, 8 rows affected, 2 warnings (0.01 sec)Records: 10 Deleted: 0 Skipped: 2 Warnings: 2结果展示mysql> select *from student;+------+------+-------+| id | name | score |+------+------+-------+| 1 | zs | 100.0 || 2 | zlh | 100.0 || 3 | cyx | 99.1 || 4 | xjj | 90.0 || 5 | aa | 100.0 || 6 | alk | 20.1 || 7 | zml | 11.1 || 8 | djh | 98.0 || 9 | cc | 100.0 || 10 | pp | 20.0 |+------+------+-------+10 rows in set (0.00 sec)

  1.默认值相关操作我们可以用 DEFAULT 关键字来定义默认值，默认值通常用在非空列，这样能够防止数据表在录入数据时出现错误。创建表时，我们可以给某个列设置默认值，具体语法格式如下：# 格式模板<字段名> <数据类型> DEFAULT <默认值># 示例mysql> CREATE TABLE `test_tb` (    ->   `id` int NOT NULL AUTO_INCREMENT,    ->   `col1` varchar(50) not null DEFAULT 'a',    ->   `col2` int not null DEFAULT 1,    ->   PRIMARY KEY (`id`)    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.06 sec)mysql> desc test_tb;+-------+-------------+------+-----+---------+----------------+| Field | Type        | Null | Key | Default | Extra          |+-------+-------------+------+-----+---------+----------------+| id    | int(11)     | NO   | PRI | NULL    | auto_increment || col1  | varchar(50) | NO   |     | a       |                || col2  | int(11)     | NO   |     | 1       |                |+-------+-------------+------+-----+---------+----------------+3 rows in set (0.00 sec)mysql> insert into test_tb (col1) values ('fdg');Query OK, 1 row affected (0.01 sec)mysql> insert into test_tb (col2) values (2);Query OK, 1 row affected (0.03 sec)mysql> select * from test_tb;+----+------+------+| id | col1 | col2 |+----+------+------+|  1 | fdg  |    1 ||  2 | a    |    2 |+----+------+------+2 rows in set (0.00 sec)通过以上实验可以看出，当该字段设置默认值后，插入数据时，若不指定该字段的值，则以默认值处理。关于默认值，还有其他操作，例如修改默认值，增加默认值，删除默认值等。一起来看下这些应该如何操作。# 添加新字段 并设置默认值alter table `test_tb` add column `col3` varchar(20) not null DEFAULT 'abc';# 修改原有默认值alter table `test_tb` alter column `col3` set default '3a';alter table `test_tb` change column `col3` `col3` varchar(20) not null DEFAULT '3b';alter table `test_tb` MODIFY column `col3` varchar(20) not null DEFAULT '3c';# 删除原有默认值alter table `test_tb` alter column `col3` drop default;# 增加默认值(和修改类似)alter table `test_tb` alter column `col3` set default '3aa';  2.几点使用建议其实不止非空字段可以设置默认值，普通字段也可以设置默认值，不过一般推荐字段设为非空。mysql> alter table `test_tb` add column `col4` varchar(20) DEFAULT '4a';Query OK, 0 rows affected (0.12 sec)Records: 0  Duplicates: 0  Warnings: 0mysql>  desc test_tb;+-------+-------------+------+-----+---------+----------------+| Field | Type        | Null | Key | Default | Extra          |+-------+-------------+------+-----+---------+----------------+| id    | int(11)     | NO   | PRI | NULL    | auto_increment || col1  | varchar(50) | NO   |     | a       |                || col2  | int(11)     | NO   |     | 1       |                || col3  | varchar(20) | NO   |     | 3aa     |                || col4  | varchar(20) | YES  |     | 4a      |                |+-------+-------------+------+-----+---------+----------------+5 rows in set (0.00 sec)在项目开发中，有些默认值字段还是经常使用的，比如默认为当前时间、默认未删除、某状态值默认为 1 等等。简单通过下表展示下常用的一些默认值字段。CREATE TABLE `default_tb` (  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',  ...  `country` varchar(50) not null DEFAULT '中国',  `col_status` tinyint not null DEFAULT 1 COMMENT '1:代表啥 2:代表啥...',  `col_time` datetime NOT NULL DEFAULT '2020-10-01 00:00:00' COMMENT '什么时间',  `is_deleted` tinyint not null DEFAULT 0 COMMENT '0:未删除 1:删除',  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',  PRIMARY KEY (`id`)) ENGINE=InnoDB  DEFAULT CHARSET=utf8;这里也要提醒下，默认值一定要和字段类型匹配，比如说某个字段表示状态值，可能取值 1、2、3... 那这个字段推荐使用 tinyint 类型，而不应该使用 char 或 varchar 类型。笔者结合个人经验，总结下关于默认值使用的几点建议：非空字段设置默认值可以预防插入报错。默认值同样可设置在可为 null 字段。一些状态值字段最好给出备注，标明某个数值代表什么状态。默认值要和字段类型匹配。