MySQL注意知识点

INT(N)表示什么？

N是显示宽度，不表示存储数字的长度限制。使用zerofill表示长度小于N时，使用0填充高位，直到长度为N，长度大于N时，按照实际显示。

自动增长注意点

1. 自动增长需要设置在主键上

2. 主键插入为null或者0时，会自动增长

3. 主键插入负数时，会按照实际值插入

字符类型

char(n)和varchar(n) 其中n表示的是字符长度。

其它例如text(n)，longtext(n) n表示的是字节

数据库排列规则Collation

设置为utf8_general_ci时，会忽略字符的大小写。

而使用utf8mb4_bin时，_bin结尾的，不会忽略大小写

时间类型

DATETIME 没有时区的概念，而TIMESTAMP有时区的概念

显示MySQL的最大连接数：

show VARIABLES like '%max_connections%'

MySql缓存默认关闭，开启后要命中要求2次SQL要完全一样，包括SQL语句，连接的数据库，协议版本，字符集等。

默认关闭缓存：

show variables like '%query_cache_type%'

在 mysql 中 database 和 schema 是等价的

MySQL存储引擎

Innodb

MySQL5.5以后默认的存储引擎，完全支持事务和ACID特性，支持行级锁。

MyISAM

MySQL5.5之前默认的存储引擎，有myd(数据)和myi(索引)组成

支持表压缩，但是表压缩后，就不能再插入数据了。适用于非事务类型应用，只读应用，空间类应用。基本上大多数场景都不适合了。

Innodb与MyISAM区别

CSV

• 以 csv 格式进行数据存储

• 所有列都不能为 null 的

• 不支持索引（不适合大表，不适合在线处理）

• 可以对数据文件直接编辑（保存文本文件内容）

Archive

以 zlib 对表数据进行压缩，磁盘 I/O 更少,数据存储在 ARZ 为后缀的文件中。

只支持 insert 和 select 操作，只允许在自增 ID 列上加索引

Memory

• 文件系统存储特点，也称 HEAP 存储引擎，所以数据保存在内存中

• 支持 HASH 索引和 BTree 索引

• 所有字段都是固定长度 varchar(10) = char(10)

• 不支持 Blog 和 Text 等大字段

• Memory 存储引擎使用表级锁

• 最大大小由 max_heap_table_size 参数决定

使用场景

• hash 索引用于查找或者是映射表（邮编和地区的对应表）

• 用于保存数据分析中产生的中间表

• 用于缓存周期性聚合数据的结果表

Ferderated

• 提供了访问远程 MySQL 服务器上表的方法

• 本地不存储数据，数据全部放到远程服务器上

• 本地需要保存表结构和远程服务器的连接信息

使用场景

偶尔的统计分析及手工查询（某些游戏行业）

MySQL中的锁

1. 表级锁，适合以查询为主，只有少量按索引条件更新数据的应用。会把整个表锁住

2. 行级锁，适合按照索引条件并发更新少量不同数据，同时又有并发查询的应用。

3. 页面锁（gap锁，间隙锁）

表锁

1. 表共享读锁。（读锁）

2. 表独占写锁。（写锁）

读锁

语法：

lock table 表名 read # 加锁 UNLOCK TABLES # 解除锁

多个session可共享读，当前session写操作会报错，其它session写操作会阻塞。当前session查询时，不能设置表别名，否则报错。

写锁

lock table 表名 write # 加锁 UNLOCK TABLES # 解除锁

同一个session中，可以进行增删改操作，其它session的增删改操作会阻塞。所有的读操作都会被阻塞。

行锁

读锁（共享锁）

BEGIN SELECT * FROM 表名 WHERE 条件 LOCK IN SHARE MODE

COMMIT

一个session开启一个事务，设置一个行级读锁，另外一个session就不能对已经上锁的数据进行写操作。比如设置条件为id <=13,那么对id为小于等于13的数据，都不能进行写操作，会阻塞。同个session可以进行操作。

写锁（排它锁）

同一个session中可以进行读写操作，另外一个session就不能对已经上锁的数据进行写操作。比如设置条件为id <=13,那么对id为小于等于13的数据，都不能进行写操作，会阻塞。

注意点

1. 两个事务不能锁同一个索引。

2. insert ，delete ， update 在事务中都会自动默认加上排它锁。

3. 行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。**

事务

事务特性

ACID

1. 原子性atomicity，一个事务为一个不可分割的最小单元，事务中的操作，要么全部成功，要么全部失败。

2. 一致性consistency，事务将数据库从一种一致性转换到另外一种一致性状态，数据库中的数据完整性没有被破坏。

3. 隔离性isolation，一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

4. 持久性durability，事务一旦提交，其所做的修改就会永久保存到数据库中。

事务并发问题

• 未提交读（READ UNCOMMITED）脏读

• 已提交读 （READ COMMITED）不可重复读

• 可重复读（REPEATABLE READ）

• 可串行化（SERIALIZABLE）

MySQL的默认隔离级别为REPEATABLE-READ

show variables like '%isolation%';

间隙锁(gap锁)

MySQL中，可重复读就已经解决了幻读的问题，借助的就是间隙锁。

事务语法

• 开启事务：

1. begin

2. start transaction（推荐）

3. begin work

• 事务回滚：rollback

• 事务提交：commit

• 还原点：savepoint point名称a。 rollback to savepoint a 回滚到还原点a

业务设计

逻辑设计

范式设计

第一大范式：数据库表中的所有字段都只具有单一属性，单一属性的列是由基本数据类型所构成的。

第二大范式：表中只有一个业务主键。

第三大范式：每一个非主属性既不部分依赖于也不传递依赖于业务主键。

优点：

1. 可以尽量得减少数据冗余

2. 范式化的更新操作比反范式化更快

3. 范式化的表通常比反范式化的表更小

缺点：

1. 对于查询需要对多个表进行关联

2. 更难进行索引优化

反范式设计

为了性能和读取效率考虑，适当违反数据库设计范式要求，允许存在少量冗余。使用空间来换取时间

优点：

1. 可以减少表的关联

2. 可以更好的进行索引优化

缺点：

1. 存在数据冗余及数据维护异常

2. 对数据的修改需要更多的成本

物理设计

命名规范

数据库、表、字段的命名要遵守可读性原则，使用下划线或者大小写来格式化命名以获得良好的可读性

数据库、表、字段的命名要遵守表意性原则，对象的名字应该能够描述它所表示的对象

数据库、表、字段的命名要遵守长名原则 ，尽可能少使用或者不使用缩写

数据类型选择

当一个列可以选择多种数据类型时

1. 优先考虑数字类型

2. 其次是日期、时间类型

3. 最后是字符类型

4. 对于相同级别的数据类型，应该优先选择占用空间小的数据类型

慢查询

分析工具： Mysqldumpslow，pt_query_digest

索引

索引分类

1. 普通索引：一个索引只包含单个列，一个表可以有多个单列索引

2. 唯一索引：索引的值必须唯一，允许有空值

3. 复合索引：一个索引包含多个列

4. 聚簇索引（聚集索引）：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB 的聚簇索引其实就是在同一个结构中保存了 B-Tree 索引(技术上来说是 B+Tree)和数据行。

5. 非聚簇索引：不是聚簇索引，就是非聚簇索引

基础语法

查看索引：SHOW INDEX FROM 表名

创建索引

1. CREATE [UNIQUE ] INDEX indexName ON 表名(columnName(length));

2. ALTER TABLE 表名 ADD [UNIQUE ] INDEX [indexName] ON (columnName(length))

删除索引

DROP INDEX [indexName] ON 表名;

执行计划

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。分析查询语句或者表结构性能瓶颈

作用

可以查询到以下信息：

• 表的读取顺序

• 数据读取操作的操作类型

• 哪些索引可以使用

• 哪些索引被实际使用

• 表之间的引用

• 每张表有多少行被优化器查询

详解

通过explain关键字分析的结果由以下列组成。

explain执行后的列

id列

描述 select 查询的序列号,包含一组数字，表示查询中执行 select 子句或操作表的顺序

根据 ID 的数值结果可以分成一下三种情况

• id 相同：执行顺序由上至下

• id 不同：如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

• id 相同和不同：同时存在时，相同id执行顺序由下至上，不同的大的优先执行

select_type列

查询的类型，用于区分普通查询、联合查询、子查询等复杂查询

查询类型

table列

标识这行数据来源于哪张表

type列

显示的是访问类型，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery >

index_subquery > range > index > ALL

关注的结果值：

system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

type列各个值含义

1. system：表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也

可以忽略不计

2. const：表示通过索引一次就找到了

const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快如将主键置于 where条件中，MySQL 就能将该查询转换为一个常量

3. eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引

扫描

4. ref: 非唯一性索引扫描，返回匹配某个单独值的所有行.

本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合

条件的行，所以他应该属于查找和扫描的混合体

5. range: 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现了 between、<、>、in 等的查询

6. index : 当查询的结果全为索引列的时候，虽然也是全部扫描，但是只查询的索引库，而没有去查询

数据。

7. all : 将遍历全表以找到匹配的行

possible_keys 与 key

possible_keys:可能使用的 key

Key:实际使用的索引。如果为 NULL，则没有使用索引

查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠

key_len

表示索引中使用的字节数，可以通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好

key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的

• key_len 表示索引使用的字节数，

• 根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到。

• char 和 varchar 跟字符编码也有密切的联系,

• latin1占用 1 个字节，gbk 占用 2 个字节，utf8 占用3 个字节。（不同字符编码占用的存储空间不同）

  1. 字符类型-索引字段为char类型+不可为Null时

     如果索引定义为char(20) ，则key_len= 20 * 3（utf-8b编码）

  2. 字符类型-索引字段为char类型+可为Null时

     如果索引定义为char(20) ，则key_len= 20 * 3（utf-8b编码）+ 1 = 31

     可以为Null，占用一个字节长度，需要加1

  3. 索引字段为varchar类型+不可为Null时

     Keylen=varchar(n)变长字段+不允许 Null=n*(utf8=3,gbk=2,latin1=1)+2

  4. 索引字段为varchar类型+可为Null时

     Keylen=varchar(n)变长字段+不允许 Null=n*(utf8=3,gbk=2,latin1=1)+2+1

  总结

  字符类型:

  变长字段需要额外的 2 个字节（VARCHAR 值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过 255，则使用两个字节)，所以 VARCAHR 索引长度计算时候要加 2），固定长度字段不需要额外的字节。

  NULL 需要 1 个字节的额外空间,所以索引字段最好不要为 NULL，因为 NULL 让统计更加复杂并且需要额外的存储空间。

  复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之 和，这也可以用来判定复合索引是否部分使用，还是全部使用。

  整数/浮点数/时间类型的索引长度

  1. NOT NULL=字段本身的字段长度

  2. NULL=字段本身的字段长度+1(因为需要有是否为空的标记，这个标记需要占用 1

  个字节)

  3. datetime 类型在 5.6 中字段长度是 5 个字节，datetime 类型在 5.5 中字段长度是 8 个字节

  ref

  显示索引的哪一列被用到了

  rows

  根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数。不准确。

  extra

  包含不适合在其它列中展示但十分重要的额外信息。

  extra的值说明

  using filesort

  当发现using filesort时，就是有优化的地方了。

  比如联合索引是a,b,c三个字段，查询时却是只按照c排序，就会导致索引失效。

  using Index

  表名使用了覆盖索引，避免了访问表的数据行，效率高。

  覆盖索引

  select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL 可以利用索引返回 select 列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

  如果要使用覆盖索引，一定要注意 select 列表中只取出需要的列，不可 select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

  SQL优化

  优化策略

  1. 尽量全值匹配

  2. 最佳左前缀法则（复合索引）。查询从索引的最左前列开始，并且不跳过索引中的列。

  3. 不在索引上做任何操作。包括计算、函数、类型转换。会导致索引失效而全表扫描

  4. 范围条件放最后。中间有范围查询会导致后面的索引列全部失效。

  5. 覆盖索引尽量用。

  6. 不等于要慎用。使用不等于会导致索引失效而全表扫描。需要使用不等于时，使用覆盖索引。

  7. Null/not null有影响。 查询is not null 时，会导致索引失效。查询is null时，如果索引是not null的，就会导致索引失效，如果不是，索引有效。解决方案：覆盖索引

  8. like查询要当心。like 以通配符开头('%abc...')索引失效会变成全表扫描的操作。

  9. 字符类型加引号。字符串不加单引号会导致索引失效。

  10. or改成union效率高。

  导入导出数据

  load data infile

  select * into OUTFILE 'D:\product.txt' from product_info

  load data INFILE 'D:\product.txt' into table product_info

  load data INFILE '/soft/product3.txt' into table product_info

  show VARIABLES like 'secure_file_priv'

  • secure_file_priv 为 NULL 时，表示限制 mysqld 不允许导入或导出。

  • secure_file_priv 为 /tmp 时，表示限制 mysqld 只能在/tmp 目录中执行导入导出，其他目录不能执行。

  • secure_file_priv 没有值时，表示不限制 mysqld 在任意目录的导入导出。