MySQL 基础笔试题 mysql数据库笔试

• Mysql数据库
• Mysql数据库的常用操作
• 表的约束
• mysql连接方式
• 三大范式
• 什么是事务
• 事务隔离等级
• 索引的相关
• 什么时候不建立?&&索引什么时候会失效
• myisam和innodb的区别？
• myisam和innodb的谁更好？
• 为什么Innodb必须建立主键，并且要是整形自增？
• 关系型数据库和非关系型数据库
• B树和B+树
• 索引最左前缀
• 索引的优化（待解决）
• 幻读的解决（MVCC&&间隙锁）
• 分库&&分表

Mysql数据库的常用操作

进入：mysql -uroot -p
建库：（create database db_name）create database if not exists db_name;
删库：drop database db_name;
显示所有库数据： show databases;
选择要使用的库：use db_name
修改：update tb_name set 修改元素=修改值 where 谁的
描述表的属性：desc tablename;
描述表的内容：select* from tablename
查询平均工资大于60的职业（name和avg是要打印出来的）：
select name ,avg(ch) from stu group by name having(相当于where) avg>(60);
分组查询：以某一列为依据查询另外一列（一般用聚合函数对其操作）//从tb_name中查询列1，然后通过列2分组去重
select 列1from tb_name group by 列2；

建表：create table if not exists tb_name;（建一张表至少有一列（至少初始化一个元素））

sn学号—整形；ch成绩——浮点型（4位有效，最后两个小数点）；name姓名——字符型；ctime创建时间——now（）

字符串类型：

char和varchar的区别：都表示字符串，但char是定长，二varchar是可变长度的字符串。

表的约束

非空约束（NOT NULL）:约束字段不可为NULL；
唯一约束（UNIQUE）:约束表的字段必须唯一
主键约束（PRIMARY KEY）：字段必须非空且唯一（一个表只能有一个）
外键约束（FOREIGN KEY）：约束字段必须是另外一张表的值（学生表中插入班级字段，班级字段必须是另外一张班级表中的数据）
例：

FOREIGN KEY(作为外键的列) REFERENCES `表名`（`列名`） //使从表（黄色）中的列，依赖主表（加粗）的列

create table stu( 
id int, name varchar(32), 
class_id int,
foreign key(class_id) references class(id));//外键必须依赖的是唯一建或是主键

默认值约束（DEFAULT KEY）:默认值约束，如果不插入默认是这个值
例：（name varchar（32）NOT NULL DEFAULT KEY ‘张三’）

注意： 1、auto_increament：自动增长只对整形 2、primary
key（id，name）这是组合主键——以id，和name两个字段共同作为主键

Mysql的连接

mysql的连接方式分为内连接和外连接（外连接又分左连接和右连接）；
内连接：两表取交集，比如两个表id字段是12，和23；如果以id相等为条件进行内连接，则是取交集进行组合；结果就是id为2的两个部分。
左连接以左表为主表组合两个表，连接符合条件的结果集，左表中不符合的为NULL
右连接：以右表为主表组合两个表，连接符合条件的结果集，表中不符合的为NULL

select * from class1 inner join class2 on class1.id = class2.id;
select * from 表A left outer join 表B on 条件
select * from 表A right outer join 表B on 条件

三大范式

第一范式：每一列必须不可再分割； 第二范式：对于联合主键来说，不能只依赖联合主键的一部分 第三范式：每个列必须与主键直接依赖，不能出现A依赖B，B依赖C的情况；

什么是事务

事务就是将多条sql语句当作一个整体进行执行的功能，成功就一起成功，失败就一起失败；
事务有四大特性：原子性、一致性、持续性、隔离性、
如果再问具体：
原子性：事务是最小的单元不可分割
一致性：同一事务的所有sql语句都是一起成功或者一起失败
持续性：事务一旦提交不可返回
隔离性：事务之间存在隔离等级的
（下来回答隔离等级与问题）
补充：事务在mysql中默认开启。
关闭事务：set autocommit = 0；（=1是开启）
如果再关闭事务的情况下要手动提交事务——commit；
如果没提交之前可以回滚——roolback；

隔离等级

事务的隔离等级由低到高分别是：
第一种：读未提交的它的隔离等级最低，表示事务A可以读到事务B未提交的数据。（这就有可能造成脏读，比如AB交易，A修改了账户数据没提交，B可以看到修改的数据就发货了，此时A进行回滚）因此对于这种情况出现了隔离级别更高的第二种：读已提交的表示A只能读到B已经提交的数据。第三种：可重读就是事务A操作事务没有结束，事务B不能看到事务A提交的数据；这就会有可能出现幻读的情况，比如事务A插入了一个数据然后提交但B看不到，此时如果事务B插入相同数据就会发现表中数据重复不能插入；因此出现了第四种：序列化，它的隔离等级最高，表示AB事务不能同时操作同一张表；A使用的时候B阻塞；但其实序列化虽然可以解决幻读问题，但它的效率太低，一般MySQL数据库默认是在RR（可重读）等级采用MVCC+间隙锁来解决幻读问题。

问再说：可序列化用锁实现的（悲观锁）

下面都是问到再说

Innodb使用MVCC和间隙锁来解决幻读，在快照读的情况下通过mvcc来避免幻读，而在当前读的情况下通过间隙锁来防止幻读；
MVCC（多版本并发控制）：通过维护一个数据的多个版本，保证在读一个事务的时候不会被阻塞；（比如事务A现在处理的是6.0版本，但别人只能读6.0之前的版本，所以不会影响）；
间隙锁：是解决当前读问题的，它会给要查询的内容上锁，使得其他并发的事务不能访问；（比如有10个数组，要查询大于3的，他就会给3以上的数据上锁，其他事务只能访问没上锁的数据）
mvcc实现：innodb会为每一行添加两个字段，一个是创建版本，一个是回滚指针，存的是之前的版本，如果事务A对最新版本进行操作，事务B访问也只能通过回滚之前访问之前的版本；
缺点：要保存之前的版本，比较消耗资源
当前读它读取的是 记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进⾏加锁。
快照读：像不加锁的select 操作就是快照读，即不加锁的⾮阻塞读;

补充：
性能排序1>2>3>4;
mysql默认是可重复读

如果数据库不支持事务的时候有可能出现的四种问题：
脏读：事务A修改了数据但没提交，脏读导致事务B查询到没有提交的数据，此时事务A回滚；
不可重复读：事务A先读取数据，事务B修改完数据，提交了事务，这时候事务A再读取数据就会导致两次读取的数据不同；
幻读：由于A不能看到B提交的数据，所以如果B插入一个数据，A也插入相同的数据，A明明看表中没有，但就是插入不进去；

索引的相关

1、什么是索引（在mysql中索引的机制是什么）
索引就是一种为了更加方便进行数据查找而采用的一种数据结构排布（Mysql底层采用B+树）

索引有哪些类型
普通索引、唯一索引、主键索引、联合索引
区别：普通索引就是以某一列为索引，唯一索引在普通索引的基础上使其不能重复（但可以为NULL），而主键索引，既不能重复，也不能为空；联合索引是将数据表中的多个字段共同组成索引；

索引的建立过程
我之前还了解过索引的建立过程，就拿mysql中的Innodb来说，它都是通过innodb页来存储数据；在每个innodb页中都会有页目录对数据进行组织，提高数据的查找效率，这个页目录其实就是这一页数据的索引。当数据足够大的时候，多个innodb页链接在一起就会退化成链式结构，效率比较低。因此再把页目录中的索引拿出来在上层构建一个B+树。
注意事项
虽然索引可以提高数据的查找效率，但索引也不是越多越好，我们应该对与频繁作为查询条件的字段可以建立索引，但对于那些频繁更新的字段则不能建立索引，因为每次的插入删除，索引的结构都是要改变的。

索引的建立
1、创建表的时候就创建索引

create table tb_name(
	id int,
	name varchar(32),
	sex char,
	//给name字段建立唯一索引
	UNIQUE INDEX(name),
	//给name建立普通索引
	INDEX（name）,
	//给id和name创建联合索引
	INDEX(id，name)
	)

2、建表后创建索引

ALTER TABLE tb_name ADD INDEX(name);
ALTER TABLE tb_name ADD INDEX(id,name);
ALTER TABLE tb_name UNIQUE INDEX(name);

4、什么是Innodb页
innodb页式mysql数据库下的一种存储引擎；
注意：
一个innodb页存放多个数据，而在取数据的时候要遵循局部性原理，一次取整个Innodb页的数据；
局部性原理：在取数据的时候，我们为了降低磁盘IO的次数，认为取该位置数据极有可能用的到周围数据，所以一次取一页；

什么时候建立索引

什么时候应该建立：对于频繁作为查询条件的字段应该建立索引
什么时候不能使用：
1、频繁进行字段更新的时候，因为字段更新的时候还要更新索引；
2、频繁的进行增删Innodb表；

补充：所以索引不是越多越好；其创建和维护都需要花费一定时间，所以我们仅仅对频繁作为查询条件的字段建立索引就可以了；

索引什么时候会失效？
1、对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引；
2、如果mysql认为全表扫面要比使用索引快，则不使用索引。(如：表里只有一条数据)
3、like查询是以%开头（对于使用 like 查询， 查询如果是 ‘%aaa’ 不会使用索引，而 ‘aaa%’ 会使用到索引。）

myisam和innodb的区别？

myisam和innodb都是mysql提供的存储引擎，但他们有很大的区别：
第一、Innodb支持事务和外键，但myisam不支持（最大区别）
第二、InnoDB是聚簇索引，它使用B+树作为索引结构，数据文件是和索引绑定在一起的，可以通过索引直接找到数据。myisam是非聚簇索引，虽然使用B+树作为索引结构，但它的数据和索引是分离的，它把索引存在MYI文件中，把数据存在MYD文件中；通过MYI中叶子节点包含的所在行地址，然后用地址在MYD文件中找到数据。
第三：innoDB必须有唯一索引（如主键），而myisam可以没有
第四、innodb强调安全，而myisam强调性能；因此在查询上myisam更快点。
在使用选择方面问再说
1.、如果要支持事务选择innodb，如果不需要可以考虑MyISAM；
2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。

聚集索引和非聚集索引的区别）
聚集索引：叶节点包含完整的数据（innodb）
非聚集索引:叶节点包含的是数据所在行的地址（myisam）

为什么Innodb必须建立主键（必须有唯一索引），并且要是整形自增？

因为如果innodb不自己建立主键，系统会自动生成一个ROWID，以这个rowid作为主键建立索引，而这个rowid针对用户是隐藏的，所以即花费了空间和时间去建立索引，也失去了索引的意义，还不如自己建立索引；

myisam和innodb的谁更好？

不能说谁比谁好，只能说使用场景不同；
1.、如果要支持事务选择innodb，如果不需要可以考虑MyISAM；
2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。

关系型数据库和非关系型数据库

关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织；例如mysql
非关系型数据库应是一种数据结构化存储方法的集合，可以是文档或者键值对，例如redis。
关系型数据库的优点是使用方便易于维护、可直接用sql语句进行查询；缺点：读写能力差；因此适合于复杂查询（用Mysql来说都要把磁盘数据取出加载到内存上（局部原理一次取一页），这就是硬盘IO）
非关系型数据库：的格式灵活、速度快；缺点：不支持事务，不支持sql

B树和B+树

B树和B+树都属于多路平衡查找树；
B树的特点是：1、每个节点都可以存多个值 2、每个节点中的索引都是从左到右递增的 3、B树的索引和数据是相连的；而B+树在B树的基础上做了改进，B+树的每个节点中只存索引，而数据都放在叶节点，通过一个双向链表进行链接；

B+作为mysql的底层优势：
1、是B+树支持范围查找；因为B+树的数据通过双向链表链接，找大于A的数据只要先找等于A的数据，后面的都是大于A的；2、是由于B+树的节点只存索引，所以可以存储更多的数据，一个IO可以找的数据也更多；
（相比于红黑树，B+树一个节点可以存多个索引，降低了磁盘IO（把磁盘数据取出加载到内存） 的次数提高了效率；相比于B树支持范围查找，同时因为B树每个节点还要存数据，肯定没B+树存的多）

索引最左前缀问题

最左前缀问题是针对联合索引，当把多个字段作为联合索引，只有含最左列的才会启动索引；
比如联合索引有（a/b/c三列），联合索引有a、ab、abc；但b或bc就不行

索引的优化

我知道的索引的优化体现在两个方面：
1、对于联合索引来说，innodb会自动找寻最合适的字段作为索引
2、生成一个rowid

幻读的解决&&mvcc&&间隙锁

Innodb使用MVCC和间隙锁来解决幻读，在快照读的情况下通过mvcc来避免幻读，而在当前读的情况下通过间隙锁来防止幻读；
MVCC（多版本并发控制）：通过维护一个数据的多个版本，保证在读一个事务的时候不会被阻塞；（比如事务A现在处理的是6.0版本，但别人只能读6.0之前的版本，所以不会影响）；
实现：innodb会为每一行添加两个字段，一个是创建版本，一个是删除版本；
缺点：要保存之前的版本，比较消耗资源

间隙锁：是解决当前读问题的，它会给要查询的内容上锁，使得其他并发的事务不能访问；（比如有10个数组，要查询大于3的，他就会给3以上的数据上锁，其他事务只能访问没上锁的数据）

当前读它读取的是 记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进⾏加锁。
快照读：像不加锁的select 操作就是快照读，即不加锁的⾮阻塞读;

分库分表

1、增加索引
2、提高硬件水平 比如增加存储容量 、CPU等，但这种方案成本很高
3、就是分库和分表；把数据分散在不同的数据库中，使得单一数据库的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的
垂直分表定义：将一个表按照字段分成多表，每个表存储其中一部分字段。比如淘宝的商品有它的描述信息和详细信息，用户在浏览商品列表时，只有对某商品感兴趣时才会查看该商品的详细描述。因此，商品信息中商品描述字段访问频次较低，且该字段存储占用空间较大，访问单个数据IO时间较长；而商品信息中商品名称、商品图片、商品价格等其他字段数据访问频次较高。
由于这两种数据的特性不一样，因此他考虑将商品信息表拆分：
将访问频次低的商品描述信息单独存放在一张表中，访问频次较高的商品基本信息单独放在一张表中。（充分发挥热门数据的操作效率，商品信息的操作的高效率不会被商品描述的低效率所拖累）
缺点：垂直分表只解决了单一表数据量过大的问题，但没有将表分布到不同的服务器上，因此每个表还是竞争同一个物理机的CPU、内存、网络IO、磁盘。

垂直分库：垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用。
它带来的提升是：
解决业务层面的耦合，业务清晰
能对不同业务的数据进行分级管理、维护、监控、扩展等