写作认知体系
  • 《技能记录》
  • db
  • mysql
samy
2021-01-30

mysql类型、储存引擎

# 三大范式

第一范式 每一列不可分，字段原子性，简称１NF 第二范式 不存在部分依赖，消除对主键的部分依赖，简称２NF 第三范式 不存在传递依赖，消除对主键的传递依赖，简称３NF

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。

第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。

第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。

上面的文字我们肯定是看不懂的，也不愿意看下去的。接下来我就总结一下：

首先要明确的是：满足着第三范式，那么就一定满足第二范式、满足着第二范式就一定满足第一范式

  • 第一范式：字段是最小的的单元不可再分；比如：学生信息组成学生信息表，有年龄、性别、学号等信息组成。这些字段都不可再分，所以它是满足第一范式的
  • 第二范式：满足第一范式,表中的字段必须完全依赖于全部主键而非部分主键；其他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依赖于主键，也就成了唯一的学号为1024的同学，姓名为Java3y，年龄是22岁。姓名和年龄字段都依赖着学号主键。
  • **第三范式：满足第二范式，**非主键外的所有字段必须互不依赖就是数据只在一个地方存储，不重复出现在多张表中，可以认为就是消除传递依赖
    • 比如，我们大学分了很多系（中文系、英语系、计算机系……），这个系别管理表信息有以下字段组成：系编号，系主任，系简介，系架构。那我们能不能在学生信息表添加系编号，系主任，系简介，系架构字段呢？不行的，因为这样就冗余了，非主键外的字段形成了依赖关系(依赖到学生信息表了)！正确的做法是：学生表就只能增加一个系编号字段。

# SQL约束

SQL 约束种类: 主键，外键，非空，唯一约束，检查约束；(5种)

  • PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
  • FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
  • NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
  • UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
  • CHECK: 用于控制字段的值范围。

# 存储过程

简介：就像我们编程语言中的函数一样，封装了我们的代码(PLSQL、T-SQL)

  • 为以后的使用保存的一条或多条MySQL语句的集合，因此也可以在存储过程中加入业务逻辑和流程。
  • 可以在存储过程中创建表，更新数据，删除数据等等。

# 优点

  • 能够将代码封装起来
  • 保存在数据库之中；让编程语言进行调用
  • 存储过程是一个预编译的代码块，执行效率比较高
  • 一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率
  • 可以通过把SQL语句封装在容易使用的单元中，简化复杂的操作
  • 可以保证数据的一致性；简化对变动的管理

# 缺点

  • 每个数据库的存储过程语法几乎都不一样，十分难以维护（不通用）
  • 业务逻辑放在数据库上，难以迭代

# 触发器

提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程。 触发器是指一段代码，当触发某个事件时，自动执行这些代码。

在MySQL数据库中有如下六种触发器：

  • 1、Before Insert
  • 2、After Insert
  • 3、Before Update
  • 4、After Update
  • 5、Before Delete
  • 6、After Delete

# 视图

视图提供数据内容的语句为 SELECT 语句,可以将视图理解为存储起来的 SELECT 语句

**视图就是基于查询的一种虚表，也就是说，视图可以将查询出来的数据进行封装。。。**那么我们在使用的时候就会变得非常方便...

值得注意的是：使用视图可以让我们专注与逻辑，但不提高查询效率

# 数据类型

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

varchar(50)中50的含义 最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)。

int(20)中20的含义 是指显示字符的长度，不影响内部存储，只是当定义了ZEROFILL时，前面补多少个 0

MySQL数据类型表达的意义（int(0)、char(16)、varchar(16)、datetime、text）

int(0)表示数据是INT类型，长度是0; char(16)表示固定长度字符串，长度为16; varchar(16)表示可变长度字符串，长度为16;

datetime表示时间类型; text表示字符串类型，能存储大字符串，最多存储65535字节数据）

# varchar和text的区别

  • varchar可指定字符数，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
  • text类型不能有默认值。
  • varchar可直接创建索引，text创建索引要指定前多少个字符。
  • varchar查询速度快于text,在都创建索引的情况下，text的索引几乎不起作用。
  • 查询text需要创建临时表。

临时表：

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间

默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。当然你也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。

CREATE TEMPORARY TABLE SalesSummary;  DROP TABLE SalesSummary;
1

# float和double的区别

  • FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。
  • DOUBLE类型数据可以存储至多18位十进制数，并在内存中占8字节。
  • //TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1、2、3、4、8字节整数。
  • float和double求SUM的结果都是不精确的，只有decimal求SUM得到的是精准数值： 所以，decimal 类型是适合财务和货币计算的128位数据类型。

# delete、truncate与drop的使用场景

DELETE是一条一条记录的删除，而Truncate是将整个表删除，保留表结构，这样比DELETE快;

  • 想删除部分数据行时候，用delete，并且带上where子句
  • 保留表而删除所有数据的时候用truncate
  • 不再需要一张表的时候，用drop

# ALTER、CHANGE、MODIFY的区别

--ALTER COLUMN：设置或删除列的默认值（操作速度非常快） 例子：
alter table film alter column rental_duration set default 5;  
alter table film alter column rental_duration drop default;

--CHANGE COLUMN：列的重命名、列类型的变更以及列位置的移动 例子：
ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL FIRST;  
ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL AFTER baz; 

--MODIFY COLUMN：除了不能给列重命名之外，他干的活和CHANGE COLUMN是一样的 例子：
ALTER TABLE MyTable MODIFY COLUMN foo VARCHAR(32) NOT NULL AFTER baz;
--示例：
ALTER TABLE test MODIFY f FLOAT;
ALTER TABLE test MODIFY d DOUBLE;
INSERT INTO test(f,d,de) VALUES(1.234,1.234,1.234);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 设置序列的开始值

一般情况下序列的开始值为1，但如果你需要指定一个开始值100，那我们可以通过以下语句来实现：

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, 
    -> date DATE NOT NULL,
    -> origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;

--或者你也可以在表创建成功后，通过以下语句来实现：
mysql> ALTER TABLE t AUTO_INCREMENT = 100;
1
2
3
4
5
6
7
8
9
10
11
# 重置序列

如果你删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。 不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。操作如下所示：

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);
1
2
3
4

# 编码

# 查看修改数据库、表、字段编码

character_set_results：结果集的字符集。

character_set_server：数据库服务器的默认字符集。

show variables like 'character%';
show variables like ‘collation%’;

SELECT @@character_set_database, @@collation_database;
-- 数据库中的表中修改；
CREATE DATABASE blog CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 全局替换数据库格式；
ALTER TABLE etag_dev CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE etag_dev CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
1
2
3
4
5
6
7
8
9

# 查询语句

# 六种关联查询

  • 交叉连接（CROSS JOIN）
  • 内连接（INNER JOIN）
  • 外连接（LEFT JOIN/RIGHT JOIN）
  • 联合查询（UNION与UNION ALL）
  • 全连接（FULL JOIN）
  • 交叉连接（CROSS JOIN）

内连接分为三类

  • 等值连接：ON A.id=B.id
  • 不等值连接：ON A.id > B.id
  • 自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

外连接（LEFT JOIN/RIGHT JOIN）

  • 左外连接：LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN
  • 右外连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN

联合查询（UNION与UNION ALL）

SELECT * FROM A UNION SELECT * FROM B UNION ...
1
  • 就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并
  • 如果使用UNION ALL，不会合并重复的记录行
  • 效率 UNION 高于 UNION ALL

全连接（FULL JOIN）

  • MySQL不支持全连接
  • 可以使用LEFT JOIN 和UNION和RIGHT JOIN联合使用
SELECT * FROM A LEFT JOIN B ON A.id=B.id UNION SELECT * FROM A RIGHT JOIN B ON A.id=B.id
1

嵌套查询 用一条SQL语句得结果作为另外一条SQL语句得条件，效率不好把握； SELECT * FROM A WHERE id IN (SELECT id FROM B)

# mysql关联查询

关联查询(内连接，外连接，自连接)

JOIN 按照功能大致分为如下三类：

  • INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)
  • **LEFT JOIN（左[外]连接）：**获取左表所有记录，即使右表没有对应匹配的记录。
  • **RIGHT JOIN（右[**外]连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

关键字是left outer join，等效于left join，在关联查询中，做外连接查询就是左连接查询，两者是一个概念

sql示例：

--INNER JOIN
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
--以上 SQL 语句等价于：where语句；
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;

--LEFT JOIN
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

--RIGHT JOIN
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
1
2
3
4
5
6
7
8
9
10

图示： ![](~/16-21-11.jpg)

全外连接 顾名思义，把两张表的字段都查出来，没有对应的值就显示null，但是注意：mysql是没有全外连接的(mysql中没有full outer join关键字)，想要达到全外连接的效果，可以使用union关键字连接左外连接和右外连接。如果在oracle中，直接就使用full outer join关键字连接两表就行了,例如： ![](~/16-24-33.jpg)

自连接查询 自连接查询就是当前表与自身的连接查询，关键点在于虚拟化出一张表给一个别名 例如：查询员工以及他的上司的名称，由于上司也是员工，所以这里虚拟化出一张上司表；

后面的四个字段就是虚拟化出的b表的所有记录，但看这四个字段其实就是记录所有是上司的员工的信息 所以，自连接查询一般用作表中的某个字段的值是引用另一个字段的值，比如权限表中，父权限也属于权限。 ![](~/16-25-00.jpg) ![](~/16-25-34.jpg)

# UNION 操作符

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。 UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据） UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据） 使用形式如下： SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称； SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称； ![](~/16-26-45.jpg)

**注释：**UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！

# UNION与UNION ALL的区别

  • 如果使用UNION ALL，不会合并重复的记录行
  • 效率 UNION 高于 UNION ALL

# 存储引擎

# 分类

常用的存储引擎有以下：

  • Innodb引擎，Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
  • MyIASM引擎(原本Mysql的默认引擎),不提供事务的支持，也不支持行级锁和外键。
  • MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

其他引擎：show engines;可以查看得到;

引擎类型 描述
InnoDB Supports transactions, row-level locking, and foreign keys
MyISAM MyISAM storage engine
MEMORY Hash based, stored in memory, useful for temporary tables
------ -------
MRG_MYISAM /dev/null storage engine (anything you write to it disappears)
CSV CSV storage engine
ARCHIVE Archive storage engine
PERFORMANCE_SCHEMA Performance Schema
FEDERATED Federated MySQL storage engine

# 命令查看

查看出 MySQL 当前默认的存储引擎是InnoDB, 并且在5.7版本所有的存储引擎中默认是 InnoDB 是事务性存储引擎，也就是说 InnoDB 支持事务。

show engines; //查看MySQL提供的所有存储引擎
show variables like '%storage_engine%';//查看MySQL当前默认的存储引擎
show table status like "users" \G;//查看表的存储引擎
1
2
3

![](~/22-11-58.jpg)

# MyISAM和InnoDB的区别

MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。

大多数时候我们使用的都是 InnoDB 存储引擎，但是在某些情况下使用 MyISAM 也是合适的比如读密集的情况下。（如果你不介意 MyISAM 崩溃回复问题的话）。

同一个数据库也可以使用多种存储引擎的表。

  • 如果一个表修改要求比较高的事务处理，可以选择InnoDB。
  • 这个数据库中可以将查询要求比较高的表选择MyISAM存储。
  • 如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

两者的对比：

  1. 是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
  2. 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
  3. 是否支持外键： MyISAM不支持，而InnoDB支持。
  4. 是否支持MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效; MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作; MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现; 各数据库中MVCC实现并不统一。

# 总括：

  • InnoDB支持事务，MyISAM不支持；
  • InnoDB数据存储在共享表空间，MyISAM数据存储在文件中；
  • InnoDB支持行级锁，MyISAM只支持表锁；
  • InnoDB支持崩溃后的恢复，MyISAM不支持；
  • InnoDB支持外键，MyISAM不支持；
  • InnoDB不支持全文索引，MyISAM支持全文索引；

# 详细比较：

1、InnoDB存储引擎，

  • 默认事务型引擎，最重要最广泛的存储引擎，性能非常优秀。
  • 数据存储在共享表空间，可以通过配置分开。也就是多个表和索引都存储在一个表空间中，可以通过配置文件改变此配置。
  • 对主键查询的性能高于其他类型的存储引擎。
  • 内部做了很多优化，从磁盘读取数据时会自动构建hash索引，插入数据时自动构建插入缓冲区。
  • 通过一些机制和工具支持真正的热备份。
  • 支持崩溃后的安全恢复。
  • 支持行级锁。
  • 支持外键。

2、MyISAM存储引擎，

  • 拥有全文索引、压缩、空间函数。
  • 不支持事务和行级锁、不支持崩溃后的安全恢复。
  • 表存储在两个文件，MYD和MYI。
  • 设计简单，某些场景下性能很好，例如获取整个表有多少条数据，性能很高。
  • 全文索引不是很常用，不如使用外部的ElasticSearch或Lucene。

innodb引擎的特性

  • 插入缓冲（insert buffer)
  • 二次写(double write)
  • 自适应哈希索引(ahi)
  • 预读(read ahead)

# 索引

# 简介

索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据库表中数据。

SQL的主流索引结构有B+树以及Hash结构，聚集索引以及非聚集索引用的是B+树索引。

SQL Sever索引类型有：唯一索引，主键索引，聚集索引，非聚集索引。 MySQL 索引类型有：唯一索引，主键（聚集）索引，非聚集索引，全文索引。

# 索引的优缺点

（1）优点：

  • 大大加快数据的检索速度，这也是创建索引的最主要的原因；
  • 加速表和表之间的连接；
  • 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；
  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

（2）缺点：

  • 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度；
  • 空间方面：索引需要占物理空间。

# 索引的分类

一个表只能有一个主键索引，但是可以有多个唯一索引。 主键索引一定是唯一索引，唯一索引不是主键索引。 主键可以与外键构成参照完整性约束，防止数据不一致。

  • 主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空;一个表只能有一个主键索引，但是可以有多个唯一索引。不允许有空值。
  • 唯一索引(unique)：唯一索引不允许两行具有相同的索引值;和普通索引类似，但是具有唯一性约束。
  • 普通索引(normal)：最基本的索引，没有任何约束限制。
  • 联合索引：将多个列组合在一起创建索引，可以覆盖多个列。（也叫复合索引，组合索引）
  • 外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性、和实现级联操作（基本不用）。
  • 全文索引(full text)：MySQL自带的全文索引只能用于MyISAM，并且只能对英文进行全文检索 （基本不用）

聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个 非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

示例：

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) --1.添加PRIMARY KEY（主键索引）
ALTER TABLE `table_name` ADD UNIQUE ( `column` ) --2.添加UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) --3.添加INDEX(普通索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`) --4.添加FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )--5.添加多列索引
1
2
3
4
5

# SQL 约束有哪几种

5种：主键，外键，非空，唯一约束，检查约束；

  • PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。

  • FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

  • NOT NULL: 用于控制字段的内容一定不能为空（NULL）。

  • UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。

  • CHECK: 用于控制字段的值范围。

主键ID，可以一列或多列，主键既是约束也是索引且是唯一索引，同时也用于对象缓存的键值。

# 索引的数据结构

MyIASM和Innodb两种引擎所使用的索引的数据结构都是B+树!

使用B+树的原因： 查找速度快、效率高，在查找的过程中，每次都能抛弃掉一部分节点，减少遍历个数。

MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。

Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

# 聚集与非聚集索引的比较

对于普通的堆组织(非聚集索引)表来说，表数据和索引是分别存储的，主键索引和二级索引存储上没有任何区别。 而对于聚簇索引表来说，表数据是和主键一起存储的，主键索引的叶结点存储行数据，二级索引的叶结点存储行的主键值。 聚簇索引表最大限度地提高了I/O密集型应用的性能，但它也有以下几个限制：

  • 1）插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
  • 2）更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
  • 3）二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

二级索引的叶节点存储的是主键值，而不是行指针，这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。

# MyISAM索引与InnoDB索引的区别

  • InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
  • InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
  • MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
  • InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

# 使用场景

  • 对于非常小的表，大部分情况下全表扫描效率更高。
  • 中到大型表，索引非常有效。//要点
  • 特大型的表，建立和使用索引的代价会随之增大，可以使用分区技术来解决。

# 【要】创建索引

  • （1）表经常进行 SELECT 操作

  • （2）表很大(记录超多)，记录内容分布范围很广

  • （3）列名经常在 WHERE 子句或连接条件中出现

  • # 适合创建索引的字段

    • 经常作查询选择的字段
    • 经常作表连接的字段
    • 经常出现在order by, group by, distinct 后面的字段

# 【不要】创建索引

  • （1）表经常进行 INSERT/UPDATE/DELETE 操作
  • （2）表很小(记录超少)
  • （3）列名不经常作为连接条件或出现在 WHERE 子句中

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引,有7大原则：

1．选择唯一性索引 2．为经常需要排序、分组和联合操作的字段建立索引 3．为常作为查询条件的字段建立索引 4．限制索引的数目 5．尽量使用数据量少的索引 6．尽量使用前缀来索引 7．删除不再使用或者很少使用的索引

# 索引的注意事项

  • 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值； 在MySQL里NULL值的列也是走索引的。当然，如果计划对列进行索引，就要尽量避免把它设置为可空，MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂。

  • 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；

  • 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

  • 注意避免冗余索引: 冗余索引指的是索引的功能相同，能够命中就肯定能命中 ，那么 就是冗余索引如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

    MySQLS.7 版本后，可以通过查询 sys 库的 schema_redundant_indexes 表来查看冗余索引

  • 创建MySQL联合索引应该注意, 需遵循前缀原则

  • 1: LIKE查询，%不能在前

    2: 如果关键词or前面的条件中的列有索引，后面的没有，所有列的索引都不会被用到。

    3: 列类型是字符串，查询时一定要给值加引号，否则索引失效，例如： 列name varchar(16)，存储了字符串"100" WHERE name = 100;以上SQL语句能搜到，但无法用到索引。

# 问题

# 以下语句是否会应用索引：SELECT* FROM users WHERE YEAR(adddate) < 200

不会，因为只要列涉及到运算，MySQL就不会使用索引。

# 以下三条sql 如何建索引，只建一条怎么建？ 覆盖索引的示例

WHERE a=1 AND b=1
WHERE b=1
WHERE b=1 ORDER BY time DESC
1
2
3

以顺序b,a,time建立联合索引，CREATE INDEX table1_b_a_time ON index_test01(b,a,time)。因为最新MySQL版本会优化WHERE子句后面的列顺序，以匹配联合索引顺序。

# 数据表损坏的修复方式有哪些？

使用 myisamchk 来修复; 使用repair table 或者 OPTIMIZE table命令来修复

# 有A(id,sex,par,c1,c2),B(id,age,c1,c2)两张表，其中A.id与B.id关联，现在要求写出一条SQL语句，将B中age>50的记录的c1,c2更新到A表中同一记录中的c1,c2字段中

UPDATE A,B SET A.c1 = B.c1, A.c2 = B.c2 WHERE A.id = B.id
UPDATE A INNER JOIN B ON A.id=B.id SET A.c1 = B.c1,A.c2=B.c2
--再加上B中age>50的条件：要点；
UPDATE A,B set A.c1 = B.c1, A.c2 = B.c2 WHERE A.id = B.id and B.age > 50;
UPDATE A INNER JOIN B ON A.id = B.id set A.c1 = B.c1,A.c2 = B.c2 WHERE B.age  > 50
1
2
3
4
5

# 为了记录足球比赛的结果，设计表如下：team：参赛队伍表; match：赛程表; 其中，match赛程表中的hostTeamID与guestTeamID都和team表中的teamID关联，查询2006-6-1到2006-7-1之间举行的所有比赛，并且用以下形式列出：拜仁 2:0 不莱梅 2006-6-21

首先列出需要查询的列：

  • 表team
  • teamID teamName
  • 表match
  • match ID
  • hostTeamID
  • guestTeamID
  • matchTime matchResult

其次列出结果列：

  • 主队 结果 客对 时间

初步写一个基础的SQL：

SELECT hostTeamID,matchResult,matchTime guestTeamID from match where matchTime between "2006-6-1" and "2006-7-1";
1

通过外键联表，完成最终SQL：

select t1.teamName,m.matchResult,t2.teamName,m.matchTime from match as m left join team as t1 on m.hostTeamID = t1.teamID, left join team t2 on m.guestTeamID=t2.teamID where m.matchTime between "2006-6-1" and "2006-7-1"
1

# 一个6亿的表a，一个3亿的表b，通过外键tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

  • 1、如果A表TID是自增长,并且是连续的,B表的ID为索引
select * from a,b where a.tid = b.id and a.tid>50000 limit 200;
1
  • 2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。
select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;
1

# 拷贝表( 拷贝数据, 源表名：a 目标表名：b)

insert into b(a, b, c) select d,e,f from a; // 嵌套查询操作；
1

# Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表 查询没学过“samy”老师课的同学的学号、姓名

select Student.S#,Student.Snamefrom Studentwhere S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’samy’);
1

# 随机取出10条数据 控制id在（max-min）*Rand() + min

SELECT * FROM users WHERE id >= ((SELECT MAX(id) FROM users)-(SELECT MIN(id) FROM users)) * RAND() + (SELECT MIN(id) FROM users) LIMIT 10
1

此方法效率比直接用SELECT * FROM users order by rand() LIMIT 10高很多

上次更新: 2022/04/15, 05:41:31
mysql语法相关
mysql事务

← mysql事务→

Copyright © 2015-2022 友一升 | yessz
×