MySQL速成

目录

• 推荐些参考资料和工具
• Linux下安装MySQL
• 常用命令
• 一些高级特性
  • 视图
  • 事务
  • 主键
  • 外键
  • 存储过程
  • 宽表的优缺点有哪些
  • 数据库表设计的三范式
  • 游标
  • 索引
    • • B树索引
      • 哈希索引
      • 空间数据索引 R-Tree
      • 全文索引
      • 其他索引
  • 存储引擎
    • • InnoDB
      • MyISAM
      • MySQL内建的其他存储引擎
      • 第三方存储引擎
  • MVCC 多版本并发控制
• MySQL面试题

推荐些参考资料和工具

Navicat Premium 支持连接数据库种类多，界面直观 破解教程
MySQL必知必会 一本小册子，新手入门
《MySQL必知必会》概括版 - yhm138 - 博客园
高性能MySQL
MySQL技术内幕——InnoDB存储引擎第2版 很原理性的东西，DBA建议观看
《MySQL技术内幕 InnoDB存储引擎》目录 - yhm138 - 博客园

Linux下安装MySQL

准备安装包  阿里云盘链接  https://www.aliyundrive.com/s/zf4SAd5MQ2F
MySQL-server-5.5.46-1.linux2.6.x86_64.rpm
MySQL-client-5.5.46-1.linux2.6.x86_64.rpm

移除mariadb 
yum -y remove maria*

以root权限安装
sudo rpm -ivh MySQL-server-5.5.46-1.linux2.6.x86_64.rpm
sudo rpm -ivh MySQL-client-5.5.46-1.linux2.6.x86_64.rpm

启动MySQL服务
service mysql start


如果读取文件时报错没有权限
赋予mysql用户及mysql组 权限，更换文件夹所有者
chown -R mysql:mysql /var/lib/mysql/


rpm安装的MySQL是不会安装/etc/my.cnf 文件的。
cp /usr/share/mysql/my-medium.cnf /etc/my.cnf


在 my.cnf 中的 [mysqld] 下,追加 lower_case_table_names = 1
# 1表示不区分大小写，0区分大小写
lower_case_table_names = 1


首次安装时，默认密码为空，可以使用如下命令修改 root 密码，
/usr/bin/mysqladmin -u root password aicp123


配置远程登录
处于安全考虑，Mysql 默认是不允许远程访问的，可以使用下面开启远程访问
# 赋予任何主机访问数据的权限
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'aicp123' WITH GRANT OPTION;
# 使修改生效
mysql>FLUSH PRIVILEGES;
# 退出mysql命令行
mysql>quit

如果再不行应考虑是否开启了防火墙

常用命令

mysql -u用户名 -p口令

登入

show databases;
use {数据库名};
show tables;
desc  {表名};
select * from {表名};

这个网上也很多：
https://devhints.io/mysql

一些高级特性

保持更新

视图

视图，5.0版本之后引入。视图本身是一个虚拟表，不存放任何数据。在使用SQL语句访问视图的时候，它返回的数据是MySQL从其他表中生成的。视图和表是在同一个namespace,MySQL在很多地方对于视图和表是同样对待的，不过视图不能创建trigger,也不能用DROP TABLE命令删除视图。

视图仅仅是用来查看存储在别处的数据的一种设施。

为什么使用视图？

• 重用SQL语句
• 简化复杂的SQL操作。用到创建好的视图而不需要这个视图是怎么来的
• 使用表的组成部分而不是整个表。
• 保护数据。用户只能有表的特定部分的访问权限而不是整个表的访问权限。
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

事务

事务transaction 指一组SQL语句。
回退rollback 指撤销指定SQL语句的过程。
提交commit 指将未存储的SQL语句结果写入数据库。
保留点 savapoint 指事务常处理中设置的临时占位符place-holder,你可以对它发布回退(与回退整个事务不同)。

事务。MyISAM存储引擎不支持事务，InnoDB存储引擎支持事务。

事务四特性 ACID
A 原子性。事务是最小的单位，不可分割。
C 一致性。 一个事务中的sql语句要么都成功，要么都不成功。
I 隔离性。 事务1和事务2之间是具有隔离性的
D 持久性。 它指的是一旦事务被提交(这里我理解是所有Sql都正确，且最后成功commit)，它对数据库的修改应该是永久性的，即使发生系统故障，如数据库崩溃或电源故障等。

事务开启方式
1、修改默认提交 set autocommit=0;
2、begin
3、start transaction

事务手动提交与回滚
手动提交 commit
手动回滚 rollback

MySQL事务的隔离级别有4个，由低到高依次为
Read uncommitted、
Read committed、 可以解决脏读；不能解决不可重复读、幻读
Repeatable read、 可以解决脏读、不可重复；不能解决幻读
Serializable， 可以解决脏读、不可重复读、幻读；
这后三个级别可以逐个解决：脏读、不可重复读、幻读这几类问题。越高的隔离级别，能解决的数据一致性问题越多，理论上性能损耗更大，可并发性越低

脏读（Dirty Read）：
当一个事务检索已被另一个尚未提交的事务更新的行时，就会发生脏读（aka uncommitted dependency）。

不可重复读（Non-repeatable Read）：
当一个事务两次检索某行并且该行被其间提交的另一个事务更新时，就会发生不可重复读。

幻读（Phantom Read）：
当一个事务两次检索一组行，并且中间提交的另一个事务将新行插入到该组中或从该组中删除新行时，就会发生幻读。

主键

一列(或一组列)，其值能够唯一区分表中每个行。
可以认为，两个不同行的主键一定是不同的？？

外键

InnoDB支持外键，内置存储引擎中貌似只有InnoDB支持外键。
PBXT也支持外键。

InnoDB强制外键使用索引，但还是无法消除这种约束检查的开销。

外键约束使得查询需要额外访问一些别的表，这也意味着需要额外的锁。
如果向子表中写入一条记录，外键约束会让InnoDB检查对应的父表的记录，也就需要对父表的对应记录进行加锁操作。(当然！)
这会导致额外的锁等待，甚至会导致一些死锁。因为没有直接访问这些父表，所以这类死锁问题往往很难排查。

对于相关数据的同时更新 当然外键更加合适。
如果外键只是用来做数值约束。那还不如用触发器或者设计表的时候显式指定ENUM类型。

存储过程

存储过程是一组为了完成特定功能的SQL语句集，它们被编译并存储在数据库中，可以通过指定存储过程的名字并给定参数（如果该存储过程需要参数的话）来进行调用。

特点

• 封装性：存储过程将一系列操作封装在一起，用户只需调用存储过程即可执行这些操作，无需知道具体的SQL执行细节。
• 性能提升：由于存储过程是预编译的，因此在执行时比起多条单独的SQL语句来说，可以减少编译的次数和优化查询。
• 减少网络交互：存储过程的执行是在数据库服务器端完成的，只需要传输少量的信息，如存储过程名和参数等，可以减少网络流量和提高效率。
• 安全性：通过控制用户对存储过程的访问权限，可以有效地保护数据，防止直接的非法SQL操作。

宽表的优缺点有哪些

宽表的优点：因为少了麻烦的聚合操作，所以优点有：一是为了提高查询性能。二是为了降低业务难度。

宽表的缺点：数据冗余容量大，数据错误。由于宽表不符合三范式要求，数据存储时可能出现一致性错误（脏写）。灵活性差。可用性问题。

数据库表设计的三范式

第一范式(通俗理解就是，表中字段的数据，不可以再拆分。)
第二范式(通俗理解就是，一个表只能描述一件事情。)
第二范式(通俗理解就是：比如有2个字段：主键班主任id，非主键班级号。在非主键字段中，我们也可以通过班级推导出该班级的班主任，这就不符合第三范式)

游标

游标是一段私有的SQL工作区,也就是一段内存区域,用于暂时存放受SQL语句影响到的数据。
通俗理解就是将受影响的数据暂时放到了一个内存区域的虚表中，而这个虚表就是游标。
MySQL在服务器端提供 ro(readonly)、单向的游标，而且只能在存储过程或者更底层的客户端API中使用。

MySQL游标中指向的对象都是存储在临时表中而不是实际查询到的数据

MySQL中的游标总是只读的。它可以逐行指向查询结果，然后让程序做进一步的处理。在一个存储过程中，可以有多个游标，也可以在循环中“嵌套”地使用游标。

索引

以下内容主要来自高性能mysql

索引有如下三个优点：
1、索引大大减少了服务器需要扫描的数据量
2、索引可以帮助服务器避免排序和临时表
3、索引可以把随机I/O变为顺序I/O

B树索引

当人们谈论索引的时候，如果没有特别指明类型，那多半说的是B-Tree索引
B树索引 大多数MySQL引擎都支持这种索引。

不过，底层的存储引擎也可能使用不同的存储结构，比如。NDB集群存储引擎内部实际上使用了T树结构存储这种索引，即使其名字是BTREE；InnoDB则使用的是B+Tree

存储引擎以不同的方式使用B树索引，性能也各有不同，各有优劣。
MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。
MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

全值匹配 查找姓名为Cuba Allen、出生于1960-01-01的人
匹配最左前缀 查找所有姓为Allen的人
匹配列前缀 查找所有以J开头的姓的人
匹配范围值 查找姓在Allen和Barry之间的人。
精确匹配某一列并范围匹配另一列 查找所有姓为Allen，且名字是字母K开头的人
只访问索引的查询 即查询只需要访问索引，而无须访问数据行。这个东西好像也叫"覆盖索引"

B树索引的限制：
如果不是按照索引的最左列开始查找，则无法使用索引。不能查找姓氏以某个字母结尾的人；生日不是最左的话，无法查找某个特定生日的人
不能跳过索引中的列。
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

哈希索引

hash index

只有精确匹配索引所有列的查询才有效。
MySQL中，只有Memory引擎支持哈希索引。

空间数据索引 R-Tree

MyISAM支持空间索引，可以用作地理数据存储。

全文索引

MyISAM引擎 支持 全文索引
InnoDB引擎 不支持 全文索引

其他索引

TokuDB引擎使用的分形树索引 fractal tree index
InnoDB引擎的聚簇索引
InnoDB引擎的覆盖索引
ScalesDB引擎的Patricia tries(前缀树)

存储引擎

InnoDB

InnoDB存储引擎

• 备注：8.0版本默认的数据库存储引擎。共享表空间和独立表空间
• 适用场景：经常更新的表，多并发的表 大数据量 支持事务 容灾恢复 外键约束

MyISAM

想起来字节跳动一面被面试官问MySQL的默认存储引擎是什么？MyISAM是5.1之前的版本所用默认引擎。
MyISAM不支持事务和行级锁。

MySQL内建的其他存储引擎

Archive引擎
Blackhole引擎
CSV引擎
Federated引擎
Memory引擎
Merge引擎
NDB集群引擎

第三方存储引擎

MySQL 从2007年开始提供了插件式的存储引擎API
OLTP类引擎
面向列的存储引擎
社区存储引擎

MVCC 多版本并发控制

可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。
MVCC分为乐观并发控制和悲观并发控制。

MySQL面试题