大四学生对MySQL知识点的小总结（持续更新）

mundo_wang
96 阅读8分钟

MySQL大杂烩

基础概念

  • DQL：select
  • DML：insert、delete、update
  • DDL：create、drop、alter、truncate
  • TCL：commit、rollback

函数

单行处理函数：对具体某一字段的某一个数据进行操作
多行处理函数：count() sum() avg() max() min()
多行处理函数又叫分组函数，必须先进行分组才能用
如果没有分组，整张表默认为一组

数据类型

varchar(255) char(255) int(11) bigint float double date datetime clob blob

date和datetime区别：date是短日期，只有年月日；datetime是长日期，有年月日时分秒。MySQL用now()函数获取当前时间，是datetime类型的。

varchar和char的区别：二者最大长度都是255，varchar可变长度，可以节省空间，但是动态分配空间速度可能很慢，创建时必须手动定义长度。
char是定长字符串，可能造成空间的浪费。

关键字

distinct:做查询结果去重，只能出现在所有字段最前方

笛卡尔积：两张表连接查询，匹配次数为两张表条数的乘积。多表联查时，union的查询效率要更高，因为可以减少匹配的次数。

join是内连接，也可以写成inner join；left join 和 right join是外连接

truncate 截断 删除表中全部数据 truncate 和 delete区别：delete把表中数据删除了，但是数据在硬盘上真实存储空间不会被释放。缺点：效率较低；优点：如果后悔了，可以回滚数据。
truncate把表一次截断，效率很高，但是不支持回滚。

SQL关键字执行顺序：from...join...on...where...group by...having…select...order by...limit...

字段约束

约束：not null unique 主键 外键
主键：每张表必须有一个主键，非空唯一，建议使用int类型，使用自然主键
外键 解决了什么问题？表冗余的问题。
引入外键时，会有一个父表和子表的概念
有外键字段的表为子表（或者可以叫主表和从表）

添加外键：alter table 从表 add constraint 外键名 foreign key(外键字段) references 主表(主键字段)
删除外键：alter table 表名 drop foreign key 外键名

索引

索引类别：单一索引、复合索引、主键索引
索引优势：提高检索效率，降低数据排序成本，降低CPU消耗。
索引劣势：索引也是一张表，会占用空间。索引降低了更新表的速率，因为每次更新表都要更新一下索引文件。
平常所说的索引，一般都是指B+树索引。
B树和B+树区别：B树非叶子结点和叶子结点都保存key（索引）；B+树只有叶子结点保存key，非叶子结点都是索引部分 B+树的查询效率更加稳定

MySQL对B+树进行了优化，在其基础上增加了一个指向相邻叶子节点的链表指针，提高区间访问的性能。叶子结点从左到右是有序的。

  • 创建索引语法：create index 索引名 on 表名(字段名);
  • 查看索引： show index from 表名;
  • 删除索引：alter index 索引名 on 表名;

聚簇索引：数据和索引放到了一块，找到索引就找到了数据。
非聚簇索引：叶子结点不存储数据，存储的是数据行地址。根据索引查找到的数据行地址去磁盘查找数据。

索引失效

什么时候索引会失效？

  1. 不满足“最左前缀法则”：在使用复合索引的时候，查询要从索引的最左前列开始，并且不跳过索引中的列。如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效。
  2. 如果出现了范围查询，范围查询右边的列索引失效。
  3. 在索引列上进行运算操作，索引失效。
  4. 字符串不加单引号，索引失效。
  5. 尽量使用覆盖查询，避免使用select * 查询（会造成回调查询）
  6. 用or分割开的条件， 如果or前的列有索引，而后面的列中没有索引，那么索引失效。
  7. 以%开头的Like模糊查询，索引失效。（科技% 不会失效；%科技 会失效）解决方案：通过覆盖（select）索引来解决。（主键自带索引）
  8. 在创建索引时，如果MySQL评估使用索引比全表更慢，则索引不创建。再查询时不走索引。
  9. is NULL ， is NOT NULL 有时索引失效（MySQL自动判断，使用索引更慢时全表扫描）
  10. in走索引，not in索引失效。

尽量使用复合索引，而不是单列索引！

创建复合索引

  • create index idx_name_sta_address on tb_seller(name, status, address);

就相当于创建了三个索引 ：

  • name
  • name + status
  • name + status + address

创建单列索引

  • create index idx_seller_name on tb_seller(name);
  • create index idx_seller_status on tb_seller(status);
  • create index idx_seller_address on tb_seller(address); 数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引 。

存储引擎

使用 show engines查询当前数据库支持的存储引擎
MySQL5.5以后的默认存储引擎是InnoDB

三种常用存储引擎

  • InnoDB引擎：mysql默认使用，支持事务，安全性最强。使用聚簇索引、使用行锁（适合高并发）、支持外键
  • MyISAM引擎：可以转换为压缩、只读表节省空间。使用非聚簇索引，使用表锁，访问速度快，适合以读和插入操作为主，并对事务和并发要求不高的应用
  • Memory引擎：存储在内存中，读取速度快，查询效率最高

MySQL锁

基于锁的属性分类：共享锁（读锁）、排它锁（写锁）
基于锁的粒度分类：行级锁、表级锁

表锁：对一张表执行select语句，会自动给涉及的表加读锁。
对一张表执行update、delete、insert语句，会自动给涉及的表加写锁。
如果对某一张表加了读锁，不会阻塞其它线程的读操作，但是会阻塞其它线程的写操作。
如果对某一张表加了写锁，不仅会阻塞其它线程的读操作，也会阻塞其它线程的写操作。
unlock tables可以释放一张表的锁。

行锁：对一张表执行update、delete、insert语句，会自动给涉及的数据集加排它锁（写锁）。
对于普通的select语句，innodb不会加任何锁。想加读锁需要LOCK IN SHARE MODE
在模拟行锁前，需要关闭自动提交功能。通过commit提交事务释放锁。

事务

事务：一个完整的业务逻辑，最小的工作单元
只有DML语句才涉及到事务，所以事务也可以理解为多条DML语句同时成功或失败

事务的四个特性（ACID）

  • 原子性：一个事务中所有操作要么同时成功要么同时失败
  • 一致性：事务从开始到结束，数据库的完整性没有被破坏
  • 隔离性：读未提交、读已提交、可重复读、序列化
  • 持久性：事务结束后数据保存在硬盘上，系统故障数据不丢失

MySQL默认的事务隔离级别是可重复读

数据库三大范式

  1. 每张表必须有主键，每一个字段原子性不能再分
  2. 非主键字段完全依赖主键，不能产生部分依赖
  3. 非主键字段直接依赖主键，不能产生传递依赖

视图

视图的特点：通过对视图的操作，会影响到原表数据。

sql优化

explain分析sql的执行计划

在大批量插入数据时，对于InnoDB的表，有以下几种提高导入效率方式

  1. 主键顺序插入（主键有序的话，插入效率高）
  2. 关闭唯一性校验
  3. 手动提交事务
insert语句的优化

对一张表插入多行数据时，可以使用多个值表的insert语句；这种方式可以缩减客户端与数据库之间的连接、关闭等消耗

  • insert into tb_test values(1,'Tom');
  • insert into tb_test values(2,'Cat');
  • insert into tb_test values(3,'Jerry'); 优化后
  • insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');
order by语句优化

filesort排序：先返回数据再排序
index排序：直接返回有序数据（需要select后面跟索引字段）
尽量添加索引，让MySQL使用index排序
而且order by的字段要么都是升序，要么都是降序
否则会造成filesort 排序

group by语句优化

group by后最好跟上有索引的字段
如果没有索引，那么加上order by null
可以避免效率低的filesort
因为group by也会进行排序操作

or查询优化

因为使用or查询时，or两边字段必须都有索引，才能利用索引，容易造成索引失效；所以我们用union关键字代替or，union可以连接两个select语句，代替or的作用。

优化嵌套查询

用两张表连接查询代替嵌套查询
因为嵌套查询需要在内存中创建临时表，步骤更加繁琐

优化分页查询limit
  1. 思路一：分页之前得排序，可以选择在索引上完成分页操作，最后根据主键关联回原表查询的其他内容（先把对应页的主键表查出来，再根据主键查出整表）
  2. 思路二：可以把limit查询转换成某个位置的查询：select * from 表名 where id > 1000000 limit 10;

该方法适用于主键自增的表，且主键不能断层
例如：日志信息表，只有插入没有修改删除。

avatar
文章
阅读
粉丝