B-Tree 索引不同的存储引擎也可能使用不同的存储结构，i如，NDB集群存储引擎内部实现使用了T-Tree结构存储这种索引，即使其名字是BTREE；InnoDB使用的是B+Tree。B-Tree通常一位这所有的值都是按顺序存储的，并且每一个叶子页道根的距离相同。下图大致反应了InnoDB索引是如何工作的。为什么mysql索引要使用B+树，而不是B树，红黑树看完上面的文章就可以理解为何B-Tree索引能够快速访问数据了。因为存储引擎不再需要进行全表扫描获取需要的数据，叶子节点包含了所有元素信息，每一个叶子节点指针都指向下一个节点，所以很适合查找范围数据。索引对多个值进行排列的依据是CREATE TABLE 语句中定义索引时的顺序。那么，索引排序的规则就是按照 last_name ,first_name ,dob 的顺序来的。可以使用 B-Tree 索引的查询类型B-Tree索引适用于全键值、键值范围或键前缀查找。键前缀查找只是用于根据最左前缀查找。举个栗子：CREATE TABLE People (   last_name VARCHAR ( 50 ) NOT NULL,   first_name VARCHAR ( 50 ) NOT NULL,   dob date NOT NULL,   gender enum ( 'm', 'f' ) NOT NULL, KEY ( last_name, first_name, dob )  );这个表的索引如下：type结果type结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。possible_keys：sql所用到的索引key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL（1）全值匹配全值匹配指的是和索引中的所有列进行匹配。例如上面的People表的索引（last_name,first_name,dob）可以用于查找last_name='Cuba Allen',first_name='Chuang',dob='1996-01-01'的人。这就是使用了索引中的所有列进行匹配，即全值匹配。mysql> EXPLAIN select * from People where last_name = 'aaa' and first_name = 'bbb' and dob='2020-11-20' \G; *************************** 1. row ***************************      id: 1  select_type: SIMPLE     table: People  partitions: NULL     type: ref possible_keys: last_name      key: last_name  <-----可以看到这个key就是我们定义的索引    key_len: 307      ref: const,const,const     rows: 1   filtered: 100.00     Extra: NULL 1 row in set, 1 warning (0.00 sec) ERROR:  No query specified（2）匹配最左前缀可以只使用索引的第一个列进行匹配。例如可以用于查找last_name='aaa'的人，即用于查找姓为Zeng的人，这里只使用了索引的最左列进行匹配，即匹配最左前缀。mysql> EXPLAIN select * from People where last_name = 'aaa' \G; *************************** 1. row ***************************      id: 1  select_type: SIMPLE     table: People  partitions: NULL     type: ref possible_keys: last_name      key: last_name  <----使用了索引    key_len: 152      ref: const     rows: 3   filtered: 100.00     Extra: NULL 1 row in set, 1 warning (0.00 sec) ERROR:  No query specified（3）匹配列前缀可以只匹配某一列的值的开头部分。例如可以用于查找last_name LIKE ‘a%'的人，即用于查找所有以Z开头的姓的人，这里只使用了索引最左列的前缀进行匹配，即匹配列前缀。mysql> EXPLAIN select * from People where last_name = 'a%' \G; *************************** 1. row ***************************      id: 1  select_type: SIMPLE     table: People  partitions: NULL     type: ref possible_keys: last_name      key: last_name   <---使用了索引    key_len: 152      ref: const     rows: 1   filtered: 100.00     Extra: NULL 1 row in set, 1 warning (0.00 sec) ERROR:  No query specified（4）匹配范围值可以只适用索引的第一列查找符合某个范围内的数据。例如可以用于查找last_name BETWEEN ‘aaa' AND ‘aaabbbccc'的人，即用于查找姓在aaa和aaabbbccc之间的人，这里只使用了索引最左列的前缀进行范围匹配，即匹配范围值。mysql> EXPLAIN select * from People where last_name BETWEEN 'aaa' and 'aaabbbccc'\G; *************************** 1. row ***************************      id: 1  select_type: SIMPLE     table: People  partitions: NULL     type: range possible_keys: last_name      key: last_name  <---使用了索引    key_len: 152      ref: NULL     rows: 3   filtered: 100.00     Extra: Using index condition 1 row in set, 1 warning (0.00 sec) ERROR:  No query specified（5）精确匹配某一列并范围匹配另外一列可以使第一列全匹配，第二列范围匹配。例如可以用于查找last_name='aaa' AND first_name LIKE 'b%'的人，即用于查找姓是Zeng，名字以C开头的人，这里使用了索引的最左列精确匹配，第二列进行范围匹配。mysql> EXPLAIN select * from People where last_name = 'aaa' and first_name like 'b%'\G; *************************** 1. row ***************************      id: 1  select_type: SIMPLE     table: People  partitions: NULL     type: range possible_keys: last_name      key: last_name  <---使用了索引    key_len: 304      ref: NULL     rows: 1   filtered: 100.00     Extra: Using index condition 1 row in set, 1 warning (0.00 sec) ERROR:  No query specified（6）只访问索引的查询查询只需访问索引，而无须访问数据行。例如select last_name, first_name where last_name='aaa'; 这里只查询索引所包含的last_name和first_name列，则无须读取数据行。mysql> explain select last_name,first_name,dob from People where last_name = 'aaa' *************************** 1. row ***************************       id: 1  select_type: SIMPLE     table: People   partitions: NULL      type: ref possible_keys: last_name      key: last_name    key_len: 152      ref: const      rows: 1    filtered: 100.00     Extra: Using index 1 row in set, 1 warning (0.00 sec) ERROR:  No query specifiedB-Tree 的限制1）只能按照索引的最左列开始查找。例如People表中的索引无法用于查找first_name为'bbb'的人，也无法查找某个特定生日的人，因为这两个列都不是最左数据列。（2）只能按照索引最左列的最左前缀进行匹配。例如People表中的索引无法查找last_name LIKE ‘%b'的人，虽然last_name就是此索引的最左列，但MySQL索引无法查找以‘b'结尾的last_name的记录。（3）只能按照索引定义的顺序从左到右进行匹配，不能跳过索引中的列。例如People表中的索引无法用于查找last_name='a' AND bod='1996-01-01'的人，因为MySQL无法跳过索引中的某一列而使用索引中最左列和排在末尾的列进行组合。如果不指定索引中中间的列，则MySQL只能使用索引的最左列，即第一列。（4）如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如有这样一个查询：where last_name='a' AND first_name LIKE 'b%' AND dob='1996-01-01'; 这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件，则first_name后面的索引列都将失效。（优化点：尽量不要在索引列中使用LIKE等范围条件，改用多个等于条件来替代，保证后面的索引列能生效。）

下表中规划了每个浮点类型的存储大小和范围:类型大小范围（无符号）范围（有符号）用途float4 type(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)单精度 浮点数值double8 type (-1.797 693 134 862 315 7 E+308，-2.225073858507 2014E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)双精度 浮点数值decimal对decimal(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值那么MySQL中这三种都是浮点类型 它们彼此的区别又是什么呢 ？？float 浮点类型用于表示==单精度浮点==数值,double浮点类型用于表示==双精度浮点==数值一个bytes(字节) 占8位 float单精度 存储浮点类型的话 就是 ==4x8=32位的长度==  , 所以float单精度浮点数在内存中占 4 个字节，并且用 32 位二进制进行描述那么 double双精度 存储浮点类型就是 ==8x8 =64位的长度==,  所以double双精度浮点数在内存中占 8 个字节，并且用 64 位二进制进行描述  float单精度小数部分只能精确到后面6位，加上小数点前的一位，即有效数字为7位double双精度小数部分能精确到小数点后的15位，加上小数点前的一位 有效位数为16位。最后就区别出了小数点后边位数的长度，越长越精确!double 和 float 彼此的区别:在内存中占有的字节数不同, 单精度内存占4个字节,  双精度内存占8个字节有效数字位数不同(尾数)  单精度小数点后有效位数7位,  双精度小数点后有效位数16位数值取值范围不同  根据IEEE标准来计算!在程序中处理速度不同,一般来说，CPU处理单精度浮点数的速度比处理双精度浮点数快double 和 float 彼此的优缺点:float单精度优点: float单精度在一些处理器上比double双精度更快而且只占用double双精度一半的空间缺点: 但是当值很大或很小的时候，它将变得不精确。double双精度优点: double 跟 float比较, 必然是 double 精度高，尾数可以有 16 位,而  float 尾数精度只有 7 位缺点: double 双精度是消耗内存的,并且是 float 单精度的两倍! ,double 的运算速度比 float 慢得多, 因为double 尾数比float  的尾数多, 所以计算起来必然是有开销的!如何选择double 和 float 的使用场景!首先: 能用单精度时不要用双精度 以省内存，加快运算速度!float: 当然你需要小数部分并且对精度的要求不高时，选择float单精度浮点型比较好!double: 因为小数位精度高的缘故，所以双精度用来进行高速数学计算、科学计算、卫星定位计算等处理器上双精度型实际上比单精度的快, 所以: 当你需要保持多次反复迭代的计算精确性时，或在操作值很大的数字时，双精度型是最好的选择。double和float:float 表示的小数点位数少,double能表示的小数点位数多，更加精确！ 就这么简单double和float 后面的长度m,d代表的是什么?double(m,d) 和float(m,d) 这里的m,d代表的是什么呢 ？  很多小伙伴也是不清不楚的!  我还是来继续解释一下吧其实跟前面整数int(n)一样，这些类型也带有附加参数：一个显示宽度m和一个小数点后面带的个数d比如: 语句 float(7,3) 规定显示的值不会超过 7 位数字，小数点后面带有 3 位数字 、double也是同理在MySQL中,在定义表字段的时候,  unsigned和 zerofill 修饰符也可以被 float、double和 decimal数据类型使用, 并且效果与 int数据类型相同在MySQL 语句中, 实际定义表字段的时候,float(M,D) unsigned  中的M代表可以使用的数字位数，D则代表小数点后的小数位数, unsigned 代表不允许使用负数!double(M,D) unsigned 中的M代表可以使用的数字位数，D则代表小数点后的小数位数==注意:== M>=D!decimal类型1.介绍decimal在存储同样范围的值时，通常比decimal使用更少的空间，float使用4个字节存储，double使用8个字节  ,而 decimal依赖于M和D的值,所以decimal使用更少的空间在实际的企业级开发中，经常遇到需要存储金额（3888.00元）的字段，这时候就需要用到数据类型decimal。在MySQL数据库中，decimal的使用语法是：decimal(M,D)，其中，M 的范围是165，D 的范围是030，而且D不能大于M。2.最大值数据类型为decimal的字段，可以存储的最大值/范围是多少？例如：decimal(5,2)，则该字段可以存储-999.99~999.99，最大值为999.99。也就是说D表示的是小数部分长度，(M-D)表示的是整数部分长度。3.存储decimal类型的数据存储形式是，将每9位十进制数存储为4个字节（官方解释：Values for DECIMAL columns are stored using a binary format that packs nine decimal digits into 4 bytes）。那有可能设置的位数不是9的倍数，官方还给了如下表格对照：Leftover DigitsNumber of types001-213-425-637-841、字段decimal(18,9)，18-9=9，这样整数部分和小数部分都是9，那两边分别占用4个字节；2、字段decimal(20,6)，20-6=14，其中小数部分为6，就对应上表中的3个字节，而整数部分为14，14-9=5，就是4个字节再加上表中的3个字节案例：mysql> drop table temp2; Query OK, 0 rows affected (0.15 sec) mysql> create table temp2(id float(10,2),id2 double(10,2),id3 decimal(10,2)); Query OK, 0 rows affected (0.18 sec) mysql> insert into temp2 values(1234567.21, 1234567.21,1234567.21),(9876543.21,    -> 9876543.12, 9876543.12); Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from temp2; +------------+------------+------------+ | id     | id2    | id3    | +------------+------------+------------+ | 1234567.25 | 1234567.21 | 1234567.21 | | 9876543.00 | 9876543.12 | 9876543.12 | +------------+------------+------------+ 2 rows in set (0.01 sec) mysql> desc temp2; +-------+---------------+------+-----+---------+-------+ | Field | Type     | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+-------+ | id  | float(10,2)  | YES |   | NULL  |    | | id2  | double(10,2) | YES |   | NULL  |    | | id3  | decimal(10,2) | YES |   | NULL  |    | +-------+---------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)案例2：mysql> drop table temp2; Query OK, 0 rows affected (0.16 sec) mysql> create table temp2(id double,id2 double); Query OK, 0 rows affected (0.09 sec) mysql> insert into temp2 values(1.235,1,235); ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> insert into temp2 values(1.235,1.235); Query OK, 1 row affected (0.03 sec) mysql>  mysql> select * from temp2; +-------+-------+ | id  | id2  | +-------+-------+ | 1.235 | 1.235 | +-------+-------+ 1 row in set (0.00 sec) mysql> insert into temp2 values(3.3,4.4); Query OK, 1 row affected (0.09 sec) mysql> select * from temp2; +-------+-------+ | id  | id2  | +-------+-------+ | 1.235 | 1.235 | |  3.3 |  4.4 | +-------+-------+ 2 rows in set (0.00 sec) mysql> select id-id2 from temp2; +---------------------+ | id-id2       | +---------------------+ |          0 | | -1.1000000000000005 | +---------------------+ 2 rows in set (0.00 sec) mysql> alter table temp2 modify id decimal(10,5); Query OK, 2 rows affected (0.28 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> alter table temp2 modify id2 decimal(10,5); Query OK, 2 rows affected (0.15 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from temp2; +---------+---------+ | id   | id2   | +---------+---------+ | 1.23500 | 1.23500 | | 3.30000 | 4.40000 | +---------+---------+ 2 rows in set (0.00 sec) mysql> select id-id2 from temp2; +----------+ | id-id2  | +----------+ | 0.00000 | | -1.10000 | +----------+ 2 rows in set (0.00 sec)

replace into和insert into on duplicate key 区别replace的用法当不冲突时相当于insert，其余列默认值 当key冲突时，自增列更新，replace冲突列，其余列默认值 Com_replace会加1 Innodb_rows_updated会加1Insert into …on duplicate key的用法不冲突时相当于insert，其余列默认值 当与key冲突时，只update相应字段值。 Com_insert会加1 Innodb_rows_inserted会增加1实验展示表结构create table helei1( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL DEFAULT '', age tinyint(3) unsigned NOT NULL default 0, PRIMARY KEY(id), UNIQUE KEY uk_name (name) ) ENGINE=innodb AUTO_INCREMENT=1  DEFAULT CHARSET=utf8;表数据root@127.0.0.1 (helei)> select * from helei1; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 1 | 贺磊 | 26 | | 2 | 小明 | 28 | | 3 | 小红 | 26 | +----+-----------+-----+ 3 rows in set (0.00 sec)replace into用法root@127.0.0.1 (helei)> replace into helei1 (name) values('贺磊'); Query OK, 2 rows affected (0.00 sec) root@127.0.0.1 (helei)> select * from helei1; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 2 | 小明 | 28 | | 3 | 小红 | 26 | | 4 | 贺磊 | 0 | +----+-----------+-----+ 3 rows in set (0.00 sec) root@127.0.0.1 (helei)> replace into helei1 (name) values('爱璇'); Query OK, 1 row affected (0.00 sec) root@127.0.0.1 (helei)> select * from helei1; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 2 | 小明 | 28 | | 3 | 小红 | 26 | | 4 | 贺磊 | 0 | | 5 | 爱璇 | 0 | +----+-----------+-----+ 4 rows in set (0.00 sec)replace的用法当没有key冲突时，replace into 相当于insert，其余列默认值当key冲突时，自增列更新，replace冲突列，其余列默认值Insert into …on duplicate key:root@127.0.0.1 (helei)> select * from helei1; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 2 | 小明 | 28 | | 3 | 小红 | 26 | | 4 | 贺磊 | 0 | | 5 | 爱璇 | 0 | +----+-----------+-----+ 4 rows in set (0.00 sec) root@127.0.0.1 (helei)> insert into helei1 (name,age) values('贺磊',0) on duplicate key update age=100; Query OK, 2 rows affected (0.00 sec) root@127.0.0.1 (helei)> select * from helei1; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 2 | 小明 | 28 | | 3 | 小红 | 26 | | 4 | 贺磊 | 100 | | 5 | 爱璇 | 0 | +----+-----------+-----+ 4 rows in set (0.00 sec) root@127.0.0.1 (helei)> select * from helei1; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 2 | 小明 | 28 | | 3 | 小红 | 26 | | 4 | 贺磊 | 100 | | 5 | 爱璇 | 0 | +----+-----------+-----+ 4 rows in set (0.00 sec) root@127.0.0.1 (helei)> insert into helei1 (name) values('爱璇') on duplicate key update age=120; Query OK, 2 rows affected (0.01 sec) root@127.0.0.1 (helei)> select * from helei1; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 2 | 小明 | 28 | | 3 | 小红 | 26 | | 4 | 贺磊 | 100 | | 5 | 爱璇 | 120 | +----+-----------+-----+ 4 rows in set (0.00 sec) root@127.0.0.1 (helei)> insert into helei1 (name) values('不存在') on duplicate key update age=80; Query OK, 1 row affected (0.00 sec) root@127.0.0.1 (helei)> select * from helei1; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 2 | 小明 | 28 | | 3 | 小红 | 26 | | 4 | 贺磊 | 100 | | 5 | 爱璇 | 120 | | 8 | 不存在 | 0 | +----+-----------+-----+ 5 rows in set (0.00 sec)replace into这种用法，相当于如果发现冲突键，先做一个delete操作，再做一个insert 操作，未指定的列使用默认值，这种情况会导致自增主键产生变化，如果表中存在外键或者业务逻辑上依赖主键，那么会出现异常。因此建议使用Insert into …on duplicate key。

顾名思义，临时表就是临时用来存储数据的表，是建立在系统临时文件夹中的表，如果使用得当，完全可以像普通表一样进行各种操作。我们常使用临时表来存储中间结果集。如果需要执行一个很耗资源的查询或需要多次操作大表时，可以把中间结果或小的子集放到一个临时表里，再对这些表进行查询，以此来提高查询效率。临时表主要适用于需要临时保存数据的一些场景。一般情况下，临时表通常是在应用程序中动态创建或者由 MySQL 内部根据需要自己创建。临时表可以分为内部临时表和外部临时表。外部临时表外部临时表也可称为会话临时表，这种临时表只对当前用户可见，它的数据和表结构都存储在内存中。当前会话中断或结束后，数据表数据就会丢失，MySQL 会自动删除表并释放其所占空间。1）创建临时表创建临时表很容易，在 CREATE TABLE 语句上添加 TEMPORARY 关键字，如下所示：CREATE TEMPORARY TABLE <表名>...临时表的命名可以和非临时表同名，但是同名后非临时表将对当前会话不可见，直到临时表被删除。2）查询临时表创建了临时表之后，运行 SHOW TABLES 命令不会列出临时表，以及在 INFORMATION_SCHEMA 数据库中也不存在临时表的信息，这不是 Bug，而是设计就是如此。我们可以使用以下命令来查看临时表：SHOW CREATE TABLE <表名>; 3）删除临时表当然，我们也可以在当前会话中手动销毁临时表，SQL 语句如下：DROP TABLE <表名>;例 1下面我们创建 tmp_table 表，并对它进行操作。mysql> CREATE TEMPORARY TABLE tmp_table (     -> id INT NOT NULL,     -> name VARCHAR(10) NOT NULL); Query OK, 0 rows affected (0.03 sec) mysql> SHOW TABLES; +-------------------+ | Tables_in_test    | +-------------------+ | product           | | product_price     | | score             | | student           | | student_comment   | | tb_student        | | tb_student_course | | tb_students_info  | | tb_students_score | | tb_usertest       | +-------------------+ 10 rows in set (0.01 sec) mysql> SHOW CREATE TABLE tmp_table \G *************************** 1. row ***************************        Table: tmp_table Create Table: CREATE TEMPORARY TABLE `tmp_table` (   `id` int(11) NOT NULL,   `name` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 1 row in set (0.00 sec) mysql> DROP TABLE tmp_table; Query OK, 0 rows affected (0.01 sec) mysql> SHOW CREATE TABLE tmp_table \G ERROR 1146 (42S02): Table 'test.tmp_table' doesn't exist如果你在执行 DROP TABLE 命令之前退出当前 MySQL 会话，再使用 SHOW CREATE TABLE 命令来读取 tmp_table 表，会发现数据库中没有该表的存在，因为在退出会话时该临时表已经被销毁了。外部临时表也有一些限制，使用时需注意以下几点：所用数据库账号需要有建立和使用临时表的权限在同一条 SQL 语句中，不能关联2次相同的临时表不能用 RENAME 来重命名一个临时表，可以用 ALTER TABLE 来代替。内部临时表内部临时表是一种特殊轻量级的临时表，不同于手工创建的临时表，它是被 MySQL 自动创建的。在 SQL 的执行过程中可能会用到临时表来存储某些操作的中间结果，该过程由 MySQL 自动完成，用户无法手工干预，且这种内部表对用户来说是不可见的。我们可以通过 EXPLAIN 或者 SHOW STATUS 查看 MySQL 是否使用了内部临时表帮助完成某个操作。内部临时表在 SQL 语句的优化过程中扮演着非常重要的角色，MySQL 中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价，所以用户在写 SQL 语句的时候应该尽量的去避免使用临时表。以下是生成内部临时表的几种可能：使用 ORDER BY/GROUP BY 的列并非全来自于表连接的第一个表Distinct 和 ORDER BY 联合使用多表连接需要保存中间结果集内部临时表有两种类型，一种是 HEAP 临时表，这种临时表的所有数据都会存在内存中，对于这种表的操作不需要 IO 操作；另一种是 OnDisk 临时表，顾名思义，这种临时表会将数据存储在磁盘上。OnDisk 临时表用来处理中间结果比较大的操作。以下情况可能阻碍 MySQL 使用 HEAP 临时表，改用 OnDisk 临时表：数据表中含有 BLOB 类型或 TEXT 类型字段列；在 GROUP BY 或 DISTINCT 任何条件中含有超过 512 字节的列；如果使用了 UNION 或 UNION ALL，而且 SELECT 列中含有任何超过 512 字节的列；如果 HEAP 临时表的大小大于 MAX_HEAP_TABLE_SIZE 系统变量的值，HEAP 临时表将会被自动转换成 OnDisk 临时表。MySQL 5.7 版本中，OnDisk 临时表可以通过 INTERNAL_TMP_DISK_STORAGE_ENGINE 系统变量选择使用 MyISAM 引擎或者 InnoDB 引擎。Created_tmp_tables 和 Created_tmp_disk_tables 变量用来记录所用临时表的数目。

在忘记 MySQL 密码的情况下，可以通过 --skip-grant-tables 关闭服务器的认证，然后重置 root 的密码，具体操作步骤如下。步骤 1)：关闭正在运行的 MySQL 服务。打开 cmd 进入 MySQL 的 bin 目录。步骤 2)：输入mysqld --console --skip-grant-tables --shared-memory 命令。–skip-grant-tables 会让 MySQL 服务器跳过验证步骤，允许所有用户以匿名的方式，无需做密码验证就可以直接登录 MySQL 服务器，并且拥有所有的操作权限。步骤 3)：上一个 DOS 窗口不要关闭，打开一个新的 DOS 窗口，此时仅输入 mysql 命令，不需要用户名和密码，即可连接到 MySQL。步骤 4)：输入命令 update mysql.user set authentication_string=password('root') where user='root' and Host ='localhost'; 设置新密码。注意：MySQL 5.7 版本中的 user 表里已经去掉了 password 字段，改为了 authentication_string。步骤 5)：刷新权限（必须步骤），输入flush privileges;命令。步骤 6)：因为之前使用 --skip-grant-tables 启动，所以需要重启 MySQL 服务器去掉 --skip-grant-tables。输入无误后输入quit;命令退出 MySQL 服务。步骤 7)：重启 MySQL 服务，使用用户名 root 和刚才设置的新密码 root 登录就可以了。

2020-11-22：mysql中，什么是filesort？#福大大架构师每日一题#

一、变量的定义mysql中变量定义用declare来定义一局部变量，该变量的使用范围只能在begin...end 块中使用,变量必须定义在复合语句的开头，并且是在其它语句之前，也可以同时申明多个变量，如果需要，可以使用default赋默认值。定义一个变量语法如下:declare var_name[,...] type[default value]看一个变量定义实例declare last date;二、mysql存储过程变量赋值变量的赋值可直接赋值与查询赋值来操作，直接赋值可以用set来操作，可以是常量或表达式如果下  set var_name= [,var_name expr]...给上面的last变量赋值方法如下  set last = date_sub( current_date(),interval 1 month);下面看通过查询给变量赋值，要求查询返回的结果必须为一行，具体操作如下  select col into var_name[,...] table_expr我们来通过查询给v_pay赋值。  create function get _cost(p_custid int,p_eff datetime)  return decimal(5,2)  deterministic  reads sql data  begin  declare v_pay decimail(5,2);  select ifnull( sum(pay.amount),0) into vpay from payment where pay.payd<=p_eff and pay.custid=pid  reutrn v_rent + v_over - v_pay;  end $$补充部分：在MySQL的存储过程中，可以使用变量，它用于保存处理过程中的值。定义变量使用DECLARE语句，语法格式如下：DECLARE var_name[,...] type [DEFAULT value]其中，var_name为变量名称，type为MySQL支持的任何数据类型，可选项[DEFAULT value]为变量指定默认值。一次可以定义多个同类型的变量，各变量名称之间以逗号“,”隔开。定义与使用变量时需要注意以下几点：◆ DECLARE语句必须用在DEGIN…END语句块中，并且必须出现在DEGIN…END语句块的最前面，即出现在其他语句之前。◆ DECLARE定义的变量的作用范围仅限于DECLARE语句所在的DEGIN…END块内及嵌套在该块内的其他DEGIN…END块。◆ 存储过程中的变量名不区分大小写。定义后的变量采用SET语句进行赋值，语法格式如下：SET var_name = expr [,var_name = expr] ...其中，var_name为变量名，expr为值或者返回值的表达式，可以使任何MySQL支持的返回值的表达式。一次可以为多个变量赋值，多个“变量名=值”对之间以逗号“,”隔开。  begin  declare no varchar(20);  declare title varchar(30);  set no='101010',title='存储过程中定义变量与赋值';  end

与其它数据库不同，MySQL 服务器可以在不同的 SQL 模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于 sql_mode 系统变量的值。 SQL 模式定义了 MySQL 数据库所支持的 SQL 语法和数据校验（数据验证检查），这样可以更容易的在不同环境下使用 MySQL。 在 MySQL 中，SQL 模式常用来解决下面几类问题：通过设置 SQL Mode，可以完成不同严格程度的数据校验，有效地保障了数据的准确性。通过设置 SQL Mode 为 ANSI 模式，可以保证大多数 SQL 符合标准的 SQL 语法，使不同数据库之间进行迁移时，不需要进行较大的修改。在不同数据库之间进行数据迁移之前，设置 SQL Mode 可以使 MySQL 中的数据更方便地迁移到目标数据库中。sql_mode 系统变量的常用值下面列出了几种 SQL 模式常用的值。1) TRICT_ ALL_TABLES 和 STRICT_ TRANS_TABLES如果将 sql_mode 的值设置为 TRICT_ALL_TABLES 和 STRICT_TRANS_TABLES，那么 MySQL将启用“严格”模式。在严格模式下，MySQL 服务器会更加严格地对待接收到的不合格数据，它不会把这些不合格的数据转换为最为接近的有效值，而是会拒绝接收它们。简单来说 MySQL 的严格模式就是 MySQL 自身对数据进行的严格校验，例如格式、长度和类型等。2) TRADITIONAL类似于严格模式，但是对于插入的不合格值会给出错误而不是警告。可以应用在事务表和非事务表，用于事务表时，只要出现错误就会立即回滚。如果你使用的是非事务存储引擎，建议不要把 SQL Mode 值设置为 TRADITIONAL，因为出现错误前进行的操作不会回滚，这样会导致操作只进行了一部分。3) ANSI_QUOTESMySQL 服务器会把双引号识别为一个标识符引用字符，而不是字符串的引号字符。所以在启用 ANSI_QUOTES 时，不能用双引号来引用字符串。4) PIPES_ AS_ CONCAT会让 MySQL 服务器把||当成一个标准的 SQL 字符串连接运算符，而不会把它当成是 OR 运算符的同义词。在 Oracle 等数据库中，||被视为字符串的连接操作符，所以在其它数据库中含有||操作符的 SQL 在 MySQL 中将无法执行，为了解决这个问题，MySQL 提供了这个值。5) ANSI会同时启用 ANSI_QUOTES、PIPES_ AS_CONCAT 和其它的几个模式值，使 MySQL 服务器的行为比它的默认运行状态更接近于标准 SQL。如何设置 sql_mode在设置 SQL 模式时，需要指定一个由单个模式值或多个模式值（多个模式值用逗号分隔）构成的值，或者指定一个空字符串，用以清除该值。模式值不区分大小写。 如果想在启动服务器时设置 SQL 模式，那么可以在 mysqld 命令行，或者在某个选项文件里设置系统变量 sql_mode。可以使用下面语句：sql_mode= "TRADITIONAL "sql_mode= "ANSI_ QUOTES, PIPES_ AS_ CONCAT"如果只是想在运行时更改 SQL 模式，那么可以使用 SET 语句来设置 sql_mode 系统变量。SET sql_mode = ' TRADITIONAL' ; 如果想设置全局性的 SQL 模式，则需要加上 GLOBAL 关键字：SET GLOBAL sql_mode = ' TRADITIONAL';设置全局变量需要具备 SUPER 管理权限。新设置的全局变量值将成为此后连入客户端的默认 SQL 模式。 如果想获取当前会话或全局的 SQL 模式值，则可以使用如下语句:SELECT @@SESSION.sql_mode;SELECT @@GLOBAL. sql_mode;其返回值由当前启用的所有模式构成，两个模式之间以逗号隔开。如果当前没有启用任何模式，则返回一个空值。

一、简介从 5.0 版本才开始支持，是一组为了完成特定功能的SQL语句集合（封装），比传统SQL速度更快、执行效率更高。存储过程的优点1、执行一次后，会将生成的二进制代码驻留缓冲区（便于下次执行），提高执行效率2、SQL语句加上控制语句的集合，灵活性高3、在服务器端存储，客户端调用时，降低网络负载4、可多次重复被调用，可随时修改，不影响客户端调用5、 可完成所有的数据库操作，也可控制数据库的信息访问权限为什么要用存储过程？1.减轻网络负载；2.增加安全性二、创建存储过程2.1 创建基本过程使用create procedure语句创建存储过程存储过程的主体部分，被称为过程体；以begin开始，以end$$结束#声明语句结束符，可以自定义: delimiter $$ #声明存储过程 create procedure 存储过程名（in 参数名 参数类型） begin #定义变量 declare 变量名 变量类型 #变量赋值 set 变量名 = 值  sql 语句1；  sql 语句2；  ... end$$ #恢复为原来的语句结束符 delimiter ；（有空格）调用完存储过程后，发现in参数不会对全局变量的值引起变化，而out和inout参数调用完存储过程后，会对全局变量的值产生变化，会将存储过程引用后的值赋值给全局变量。in参数赋值类型可以是变量还有定值，而out和inout参数赋值类型必须为变量。 

大家对 MySQL 的整体架构已经有了一定的了解，本节我们主要介绍数据库的具体工作流程。下面是一张简单的数据库执行流程图：下面从数据库架构的角度介绍数据库的工作流程：1. 连接层1）连接处理：客户端同数据库服务层通过连接管理模块建立 TCP 连接，并请求一个连接线程。如果连接池中有空闲的连接线程，则分配给这个连接，如果没有，在没有超过最大连接数的情况下，创建新的连接线程负责这个客户端。连接管理模块负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，接收客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的缓存等。2）授权认证：在真正的操作之前，还需要调用用户模块进行授权检查，来验证用户是否有权限。通过后，连接线程开始接收并处理来自客户端的请求。用户模块所实现的功能，主要包括用户的登录连接权限控制和用户的授权管理。它就像 MySQL 的大门守卫一样，决定是否给来访者“开门”。在 MySQL 中，将客户端请求分为了两种类型：一种是 query（SQL语句），需要调用 Parser（查询解析器）才能够执行的请求；一种是 command（命令），不需要调用 Parser 就可以直接执行的请求。2. SQL层连接线程接收到 SQL 语句之后，将语句交给 Parser 进行语法分析和语义分析。之后根据类型的不同，有些会直接处理，有些会分发给其他模块来处理。如果是一个 query 类型的请求，会将控制权交给 Query 解析器。Query 解析器首先分析是不是一个 Select 类型的 query。是则调用查询缓存模块，让它检查该 query 在 Query Cache（查询缓存）中是否已经存在，如果有结果可以直接返回给客户端。没有结果则将控制权交给 Optimizer（查询优化器），进行查询的优化。如果是表变更语句，则分别交给 Insert 处理器、Delete 处理器、Update 处理器、Create 处理器，以及 Alter 处理器这些小模块来负责。3. 存储引擎层在各个模块收到 Query 解析或其它模块分发过来的请求后，首先会通过访问控制模块检查连接用户是否有访问目标表以及目标字段的权限，如果有，就会调用表管理模块请求相应的表，并获取对应的锁。表变更管理模块主要是负责完成一些 DML 和 DDL 的 query，如：update，delete，insert，create table，alter table 等语句的处理。当表变更管理模块“获取”打开的表之后，就会根据该表的相关信息，判断表的存储引擎类型和其他相关信息。根据表的存储引擎类型，提交请求给存储引擎接口模块，调用对应的存储引擎实现模块，进行相应处理。 不过，对于表变更管理模块来说，可见的仅是存储引擎接口模块所提供的一系列“标准”接口，底层存储引擎实现模块的具体实现，对于表变更管理模块来说是透明的。他只需要调用对应的接口，并指明表类型，之后接口模块会根据表类型调用正确的存储引擎来进行相应的处理。 当一条 query 或者一个 command 处理完成（成功或者失败）之后，控制权都会交还给连接线程模块。如果处理成功，则将处理结果（可能是一个 ResultSet，也可能是成功或者失败的标识）通过连接线程反馈给客户端。如果处理过程中发生错误，也会将相应的错误信息发送给客户端，然后连接线程模块会进行相应的清理工作，并继续等待后面的请求。之后重复上面的过程，或者与客户端断开连接，最后关闭连接，释放连接线程。

前提：对于一个给定的 MySQL 连接，我们如何才能知道它来自于哪个客户端的哪个进程呢？HandshakeResponseMySQL-Client 在连接 MySQL-Server 的时候，不只会把用户名密码发送到服务端，还会把当前进程id，操作系统名，主机名等等信息也发到服务端。这个数据包就叫 HandshakeResponse 官方有对其格式进行详细的说明。我自己改了一个连接驱动，用这个驱动可以看到连接时发送了哪些信息。2020-05-19 15:31:04,976 - mysql-connector-python.mysql.connector.protocol.MySQLProtocol.make_auth - MainThread - INFO - conn-attrs  {'_pid': '58471', '_platform': 'x86_64', '_source_host': 'NEEKYJIANG-MB1', '_client_name': 'mysql-connector-python', '_client_license': 'GPL-2.0', '_client_version': '8.0.20', '_os': 'macOS-10.15.3'}HandshakeResponse 包的字节格式如下，要传输的数据就在包的最后部分。4       capability flags, CLIENT_PROTOCOL_41 always set 4       max-packet size 1       character set string[23]   reserved (all [0]) string[NUL]  username  if capabilities & CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA { lenenc-int   length of auth-response string[n]   auth-response  } else if capabilities & CLIENT_SECURE_CONNECTION { 1       length of auth-response string[n]   auth-response  } else { string[NUL]  auth-response  }  if capabilities & CLIENT_CONNECT_WITH_DB { string[NUL]  database  }  if capabilities & CLIENT_PLUGIN_AUTH { string[NUL]  auth plugin name  }  if capabilities & CLIENT_CONNECT_ATTRS { lenenc-int   length of all key-values lenenc-str   key lenenc-str   value   if-more data in 'length of all key-values', more keys and value pairs  }解决方案从前面的内容我们可以知道 MySQL-Client 确实向 MySQL-Server 发送了当前的进程 id ，这为解决问题提供了最基本的可能性。当服务端收到这些信息后双把它们保存到了 performance_schema.session_connect_attrs。第一步通过 information_schema.processlist 查询关心的连接，它来自于哪个 IP，和它的 processlist_id 。mysql> select * from information_schema.processlist; +----+---------+--------------------+--------------------+---------+------+-----------+----------------------------------------------+ | ID | USER  | HOST        | DB         | COMMAND | TIME | STATE   | INFO                     | +----+---------+--------------------+--------------------+---------+------+-----------+----------------------------------------------+ | 8 | root  | 127.0.0.1:57760  | performance_schema | Query  |  0 | executing | select * from information_schema.processlist | | 7 | appuser | 172.16.192.1:50198 | NULL        | Sleep  | 2682 |      | NULL                     | +----+---------+--------------------+--------------------+---------+------+-----------+----------------------------------------------+ 2 rows in set (0.01 sec)第二步通过 performance_schema.session_connect_attrs 查询连接的进程 IDmysql> select * from session_connect_attrs where processlist_id = 7;                +----------------+-----------------+------------------------+------------------+ | PROCESSLIST_ID | ATTR_NAME    | ATTR_VALUE       | ORDINAL_POSITION | +----------------+-----------------+------------------------+------------------+ |       7 | _pid      | 58471         |        0 | |       7 | _platform    | x86_64         |        1 | |       7 | _source_host  | NEEKYJIANG-MB1     |        2 | |       7 | _client_name  | mysql-connector-python |        3 | |       7 | _client_license | GPL-2.0        |        4 | |       7 | _client_version | 8.0.20         |        5 | |       7 | _os       | macOS-10.15.3     |        6 | +----------------+-----------------+------------------------+------------------+ 7 rows in set (0.00 sec)可以看到 processlist_id = 7 的这个连接是由 172.16.192.1 的 58471 号进程发起的。检查我刚才是用的 ipython 连接的数据库，ps 看到的结果也正是 58471 与查询出来的结果一致。 ps -ef | grep 58471  501 58471 57741  0 3:24下午 ttys001  0:03.67 /Library/Frameworks/Python.framework/Versions/3.8/Resources/Python.app/Contents/MacOS/Python /Library/Frameworks/Python.framework/Versions/3.8/bin/ipython

MySQL表中的连接方式其实非常简单，这里就简单的罗列出他们的特点。表的连接（JOIN）可以分为内连接（JOIN/INNER JOIN）和外连接（LEFT JOIN/RIGHT JOIN）。首先我们看一下我们本次演示的两个表：mysql> SELECT * FROM student; +------+----------+------+------+ | s_id | s_name  | age | c_id | +------+----------+------+------+ |  1 | xiaoming |  13 |  1 | |  2 | xiaohong |  41 |  4 | |  3 | xiaoxia |  22 |  3 | |  4 | xiaogang |  32 |  1 | |  5 | xiaoli  |  41 |  2 | |  6 | wangwu  |  13 |  2 | |  7 | lisi   |  22 |  3 | |  8 | zhangsan |  11 |  9 | +------+----------+------+------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM class; +------+---------+-------+ | c_id | c_name | count | +------+---------+-------+ |  1 | MATH  |  65 | |  2 | CHINESE |  70 | |  3 | ENGLISH |  50 | |  4 | HISTORY |  30 | |  5 | BIOLOGY |  40 | +------+---------+-------+ 5 rows in set (0.00 sec)首先，表要能连接的前提就是两个表中有相同的可以比较的列。1.内连接 mysql> SELECT * FROM student INNER JOIN class ON student.c_id = class.c_id;+------+----------+------+------+------+---------+-------+| s_id | s_name  | age | c_id | c_id | c_name | count |+------+----------+------+------+------+---------+-------+|  1 | xiaoming |  13 |  1 |  1 | MATH  |  65 ||  2 | xiaohong |  41 |  4 |  4 | HISTORY |  30 ||  3 | xiaoxia |  22 |  3 |  3 | ENGLISH |  50 ||  4 | xiaogang |  32 |  1 |  1 | MATH  |  65 ||  5 | xiaoli  |  41 |  2 |  2 | CHINESE |  70 ||  6 | wangwu  |  13 |  2 |  2 | CHINESE |  70 ||  7 | lisi   |  22 |  3 |  3 | ENGLISH |  50 |+------+----------+------+------+------+---------+-------+7 rows in set (0.00 sec)简单的讲，内连接就是把两个表中符合条件的行的所有数据一起展示出来，即如果不符合条件，即在表A中找得到但是在B中没有（或者相反）的数据不予以显示。2.外连接mysql> SELECT * FROM student LEFT JOIN class ON student.c_id = class.c_id; +------+----------+------+------+------+---------+-------+ | s_id | s_name  | age | c_id | c_id | c_name | count | +------+----------+------+------+------+---------+-------+ |  1 | xiaoming |  13 |  1 |  1 | MATH  |  65 | |  2 | xiaohong |  41 |  4 |  4 | HISTORY |  30 | |  3 | xiaoxia |  22 |  3 |  3 | ENGLISH |  50 | |  4 | xiaogang |  32 |  1 |  1 | MATH  |  65 | |  5 | xiaoli  |  41 |  2 |  2 | CHINESE |  70 | |  6 | wangwu  |  13 |  2 |  2 | CHINESE |  70 | |  7 | lisi   |  22 |  3 |  3 | ENGLISH |  50 | |  8 | zhangsan |  11 |  9 | NULL | NULL  | NULL | +------+----------+------+------+------+---------+-------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM student RIGHT JOIN class ON student.c_id = class.c_id; +------+----------+------+------+------+---------+-------+ | s_id | s_name  | age | c_id | c_id | c_name | count | +------+----------+------+------+------+---------+-------+ |  1 | xiaoming |  13 |  1 |  1 | MATH  |  65 | |  4 | xiaogang |  32 |  1 |  1 | MATH  |  65 | |  5 | xiaoli  |  41 |  2 |  2 | CHINESE |  70 | |  6 | wangwu  |  13 |  2 |  2 | CHINESE |  70 | |  3 | xiaoxia |  22 |  3 |  3 | ENGLISH |  50 | |  7 | lisi   |  22 |  3 |  3 | ENGLISH |  50 | |  2 | xiaohong |  41 |  4 |  4 | HISTORY |  30 | | NULL | NULL   | NULL | NULL |  5 | BIOLOGY |  40 | +------+----------+------+------+------+---------+-------+ 8 rows in set (0.00 sec)上面分别展示了外连接的两种情况：左连接和右连接。这两种几乎是一样的，唯一的区别就是左连接的主表是左边的表，右连接的主表是右边的表。而外连接与内连接不同的地方就是它会将主表的所有行都予以显示，而在主表中有，其他表中没有的数据用NULL代替。

三大范式为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中，这种规则就是范式。范式是符合某一种级别的关系模式的集合。关系型数据库中的关系必须满足一定的要求，即满足不同的范式。目前关系型数据库有六种范式，分别为：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、第四范式（4NF）、第五范式（5NF）和第六范式（6NF）。要求最低的范式是第一范式。第二范式在第一范式的基础上又进一步的添加了要求，其余范式依次类推。一般说来，数据库只需满足第三范式就行了，而通常我们用的最多的就是第一范式、第二范式、第三范式，也就是接下来要讲的“三大范式”。1）第一范式第一范式（1NF）用来确保每列的原子性，要求每列（或者每个属性值）都是不可再分的最小数据单元（也称为最小的原子单元）。例如，客人住宿信息表 （姓名, 客人编号, 地址, 客房号, 客房描述, 客房类型, 客房状态, 床位数, 入住人数, 价格）。其中，“地址”列还可以细分为国家、省、市、区等，甚至有的程序还把“姓名”列也拆分为“姓”和“名”等。如果业务需求中不需要拆分“地址”和“姓名”列，则该数据表符合第一范式，如果需要将“地址”列拆分，则下列写法符合第一范式：客人住宿信息表（姓名, 客人编号, 国家, 省, 市, 区, 门牌号, 客房号, 客房描述, 客房类型, 客房状态, 床位数, 入住人数, 价格）。2）第二范式第二范式（2NF）在第一范式的基础上更进一层，要求表中的每列都和主键相关，即要求实体的唯一性。如果一个表满足第一范式，并且除了主键以外的其他列全部都依赖于该主键，那么该表满足第二范式。客人住宿信息表中的数据主要用来描述客人住宿信息，所以该表主键为（客人编号，客房号）:“姓名”列、“地址”列➡“客人编号”列。“客房描述”列、 “客房类型”列、“客房状态”列、“床位数”列、“入住人数”列、“价格”列➡“客房号”列。其中，“➡”符号代表依赖。以上各列没有全部依赖于主键（客人编号，客房号），只是部分依赖于主键，不符合第二范式。使用第二范式后，客人住宿信息表可以分解成以下两个表：客人信息表（客人编号，姓名，地址，客房号，入住时间，结账日期，押金，总金额），主键为“客人编号”列，其他列都全部依赖于主键列。客房信息表（客房号，客房描述，客房类型，客房状态，床位数，入住人数，价格），主键为“客房号”列，其他列都全部依赖于主键列。3）第三范式第三范式（3NF）在第二范式的基础上更进一层，第三范式是确保每列都和主键列直接相关，而不是间接相关，即限制列的冗余性。如果一个关系满足第二范式，并且除了主键以外的其他列都依赖于主键列，列和列之间不存在相互依赖关系，则满足第三范式。为了更好的理解第三范式，这里我们需要了解传递依赖。假设A、B 和 C 是关系 R 的三个属性，如果 A➡B 且 B➡C，则从这些函数依赖中，可以得出 A➡C。如上所述，依赖 A➡C 称之为传递依赖。以第二范式中的客房信息表为例，初看该表时没有问题，满足第三范式，每列都和主键列“客房号”相关，再细看会发现："床位数” 列、“价格”列➡“客房类型”列。“客房类型”列➡“客房号”列。“床位数”列、“价格”列➡“客房号”列为了满足第三范式，应该去掉“床位数”列，“价格”列和“客房类型”列，将客房信息表分解为如下两个表。客房表（客房号，客房描述，客房类型编号，客房状态，入住人数）客房类型表（客房类型编号，客房类型名称，床位数，价格）主键与外键在多表中的重复出现不属于数据冗余，非键字段的重复出现才是数据冗余。在客房表中客房状态存在冗余，需要进行规范化，规范化以后的表如下：客房表（客房号，客房描述，客房类型编号，客房状态编号，入住人数）。客房状态表（客房状态编号，客房状态名称）最后，满足三大范式的 E-R 图如下所示：4）反范式化不满足范式的数据库设计，就是反范式化。我们需要知道对于项目的最终用户来说，用户关心的是方便，清晰的数据结果。所以在设计数据库时，设计人员和客户在数据库的设计规范化和性能之间会有一定的矛盾。上面我们通过三大范式将客房表分解出两个表，为了满足客户的需求，最终可能需要通过三个或四个表之间的连接查询，来得到客户需要的数据结果，插入数据同样如此，对于客户输入的数据，我们需要分开插入到三个或四个不同的表中。由此可以看出，为了满足三大范式，我们的数据操作性能会受到相应的影响。所以，在实际的数据库设计中，既要考虑三大范式，避免数据的冗余和各种数据操作异常，又要考虑数据访问性能。为了减少表连接，提高数据库的访问性能，也可以允许适当的数据冗余列，这也许就是最合适的数据库设计方案。比如，有一张存放商品的基本表，数据表中包括“单价”、“数量”“金额”等字段。“金额”这个字段就说明该表的设计不满足第三范式，因为“金额”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。与第三范式中介绍的冗余相比，前面介绍的冗余属于低级冗余，我们反对低级冗余，但这里的冗余为高级冗余，目的是提高数据的处理速度，增加“金额”列后，可以提高查询统计的速度，这是以空间换取时间的做法。注意：不要轻易违反数据库设计的规范化原则，如果处理不好，可能会适得其反，使应用程序运行速度更慢。优缺点最后我们来总结一下范式化和反范式化的优缺点。1）范式化优点如下：减少数据冗余范式化后的表中只有很少的重复数据，更新时只需要更新较少的数据，所以范式化的更新操作比反范式化更快范式化的表通常比反范式化更小缺点如下：范式化的表在查询时经常需要很多的关联，这回导致性能降低增加了索引优化的难度2）反范式化优点如下：可以减少表的关联可以更好的进行索引优化缺点如下：数据表存在数据冗余及数据维护异常对数据的修改需要更多的成本

一. 对于MyISAM引擎表常见的优化方法如下：1. 禁用索引。对于非空表插入记录时，MySQL会根据表的索引对插入记录建立索引。如果插入大量数据，建立索引会降低插入记录的速度。为了解决这种情况可以在插入记录之前禁用索引，数据插入完毕后在开启索引。禁用索引的语句为： ALTER TABLE tb_name DISABLE KEYS;  重新开启索引的语句为： ALTER TABLE table_name ENABLE KEYS; 对于空表批量导入数据，则不需要进行此操作，因为MyISAM引擎的表是在导入数据之后才建立索引的。    2. 禁用唯一性检查：数据插入时，MySQL会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕之后再开启。禁用唯一性检查的语句为： SET UNIQUE_CHECKS=0; 开启唯一性检查的语句为： SET UNIQUE_CHECKS=1;    3. 使用批量插入。使用一条INSERT语句插入多条记录。如 INSERT INTO table_name VALUES(....),(....),(....)    4. 使用LOAD DATA INFILE批量导入当需要批量导入数据时，使用LOAD DATA INFILE语句导入数据的速度比INSERT语句快。二. 对于InnoDB引擎的表，常见的优化方法如下：1. 禁用唯一性检查。同MyISAM引擎相同，通过 SET UNIQUE_CHECKS=0;  导入数据之后将该值置1。    2. 禁用外键检查。插入数据之前执行禁止对外键的查询，数据插入完成之后再恢复对外键的检查。禁用外键检查语句为： SET FOREIGN_KEY_CHECKS=0;  恢复对外键的检查语句为： SET FOREIGN_KEY_CHECKS=1; 3. 禁止自动提交。插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。禁止自动提交语句为： SET AUTOCOMMIT=0;  恢复自动提交只需将该值置

在 MySQL 中，向数据表插入数据时，索引、唯一性检查、数据大小是影响插入速度的主要因素。本节将介绍优化插入数据速度的几种方法。根据不同情况，可以分别进行优化。对于 MyISAM 引擎的表，常见的优化方法如下：1. 禁用索引对非空表插入数据时，MySQL 会根据表的索引对插入的记录进行排序。插入大量数据时，这些排序会降低插入数据的速度。为了解决这种情况，可以在插入数据之前先禁用索引，等到数据都插入完毕后在开启索引。禁用索引的语句为：ALTER TABLE table_name DISABLE KEYS;重新开启索引的语句为：ALTER TABLE table_name ENABLE KEYS;对于新创建的表，可以先不创建索引，等到数据都导入以后再创建索引，这样可以提高导入数据的速度。2. 禁用唯一性检查插入数据时，MySQL 会对插入的数据进行唯一性检查。这种唯一性检验会降低插入数据的速度。为了降低这种情况对查询速度的影响，可以在插入数据前禁用唯一性检查，等到插入数据完毕后在开启。禁用唯一性检查的语句为：SET UNIQUE_CHECKS=0;开启唯一性检查的语句为：SET UNIQUE_CHECKS=1;3. 使用批量插入在 MySQL 中，插入多条数据有 2 种方式。第一种是使用一个 INSERT 语句插入多条数据。INSERT 语句的情形如下：INSERT INTO items(name,city,price,number,picture) VALUES ('耐克运动鞋','广州',500,1000,'001.jpg'),('耐克运动鞋2','广州2',500,1000,'002.jpg');第二种是一个 INSERT 语句只插入一条数据，执行多个 INSERT 语句来插入多条数据。INSERT 语句的情形如下：INSERT INTO items(name,city,price,number,picture)  VALUES('耐克运动鞋','广州',500,1000,'001.jpg');INSERT INTO items(name,city,price,number,picture)  VALUES('耐克运动鞋2','广州',500,1000,'002.jpg');一次性插入多条数据和多次插入数据所耗费的时间是不一样的。第一种方式减少了与数据库之间的连接等操作，其速度比第二种方式要快一些。所以插入大量数据时，建议使用第一种方法。注意：如果能用 LOAD DATA INFILE 语句，就尽量用 LOAD DATA INFILE 语句。因为 LOAD DATA INFILE 语句导入数据的速度比 INSERT 语句的速度快。对于 InnoDB 引擎的表，常见的优化方法如下：1. 禁用唯一性检查同 MyISAM 引擎相同，插入数据之前先禁用索引，等到数据都插入完毕后在开启索引。2. 禁用外键检查使用外键时，在子表中插入一条数据，首先会检查主表中是否有相应的主键值，然后锁定主表的记录，在插入值。相比较，使用外键多了2步操作，速度会慢一些。所以我们可以在插入数据之前禁止对外键的检查，数据插入完成之后再恢复对外键的检查。不多对于数据完整性要求较高的系统不建议使用。禁用外键检查语句为：SET FOREIGN_KEY_CHECKS=0; 恢复对外键的检查语句为：SET FOREIGN_KEY_CHECKS=1;3. 禁止自动提交在《MySQL设置事务自动提交》一节我们提到 MySQL 的事务自动提交模式默认是开启的，其对 MySQL 的性能也有一定得影响。也就是说如果你插入了 1000 条数据，MySQL 就会提交 1000 次，这大大影响了插入数据的速度。而如果我们把自动提交关掉，通过程序来控制，只要一次提交就可以了。所以插入数据之前可以先禁止事务的自动提交，待数据导入完成之后，再恢复自动提交操作。禁止自动提交语句为：SET AUTOCOMMIT=0; 恢复自动提交语句为：SET AUTOCOMMIT=1;