Mysql学习总结一：mysql问题总结大全

１、什么是DBMS？DBMS和数据库之间是什么关系？SQL的中英文全称是什么？

1. 数据库管理系统DBMS(Database Management System)：是一种操纵和管理数据库的大型软件,用于建立、使用和维护数据库,对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据。常见的如：Mysql,Oracle,PostgreSQL等。
2. 数据库DB(Database)：即存储数据的“仓库”,其本质是一个文件系统。它保存了一系列有组织的数据。一般会针对每一个应用创建一个数据库。
3. 结构化查询语言SQL(Structured Query Language)：专门用来与数据库通信的语言。

２、SQL语言分类有哪些？

1. DDL(Data Definition Language,数据定义语言)：定义不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。主要语句关键字包括CREATE,DROP,UPDATE等。
2. DML(Data Manipulation Language,数据操作语言)：用于添加、删除、修改和查询数据库记录。主要语句关键字包括INSERT,DELETE,UPDATE,SELECT等。其中SELECT是SQ语言的基础，最为重要。
3. DCL(Data Control Language,数据控制语言)：用于定义数据库、表、字段、用户的访问权限和安全级别。主要语句关键字包括GRANT,REVOKE,COMMIT,ROLLBACK,SAVEPOINT等。
4. 其他：因为查询语句使用的非常的频繁,所以很多人把查询语句单拎出来一类:DQL(数据查询语言)。还有单独将 COMMIT 、 ROLLBACK 取出来称为TCL (Transaction Control Language,事务控制语
   言)。

３、对涉及金额等精确度要求高的属性，应该用什么数据类型？可以用double么？

  对精确度要求高的属性，可以使用定点数：DECIMAL/NUMERIC。定点数在MySQL内部是以 字符串 的形式进行存储,这就决定了它一定是精准的
　　不能使用double，因为mysql是用二进制来存储doulbe类型的，当数字较大的情况下，会存在四舍五入不精准的情况发生。

4、建和不建外键(FOREIGN KEY)约束有什么区别?如果是分布式、高并发集群是否需要添加外键？

建外键约束的话,你的操作(创建表、删除表、添加、修改、删除)会受到限制,从语法层面受到限
制。例如:在员工表中不可能添加一个员工信息,它的部门的值在部门表中找不到。

不建外键约束,你的操作(创建表、删除表、添加、修改、删除)不受限制,要保证数据的 引用完整
性 ,只能依 靠程序员的自觉 ,或者是 在Java程序中进行限定 。例如:在员工表中,可以添加一个员工的信息,它的部门指定为一个完全不存在的部门。

在 MySQL 里,外键约束是有成本的,需要消耗系统资源。对于大并发的 SQL 操作,有可能会不适合。比如大型网站的中央数据库,可能会 因为外键约束的系统开销而变得非常慢 。所以, MySQL 允许你不使用系统自带的外键约束,在 应用层面 完成检查数据一致性的逻辑。也就是说,即使你不用外键约束,也要想办法通过应用层面的附加逻辑,来实现外键约束的功能,确保数据的一致性。

阿里开发规范：
【 强制 】不得使用外键与级联,一切外键概念必须在应用层解决。

说明:(概念解释)学生表中的 student_id 是主键,那么成绩表中的 student_id 则为外键。如果更新学
生表中的 student_id,同时触发成绩表中的 student_id 更新,即为级联更新。外键与级联更新适用于 单机低并发 ,不适合 分布式 、 高并发集群 ;级联更新是强阻塞,存在数据库 更新风暴 的风险;外键影响数据库的 插入速度 。

5、mysql中表的约束是存储在哪个库？mysql中存储过程和存储函数的信息存储在哪个库？如何查看某个表中的约束、视图、存储过程、存储函数？

１）mysql自带的information_schema.table_constraints表是专门存储各个表的约束。
查看某个表的约束：

SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名称';

２）MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。
查看某个表中的存储过程和存储函数可以用下面两种方法：
用information_schema.Routines查看

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

或者用：SHOW STATUS查看

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

３）查看某个表的视图，可以用３种方法：
第一种是用SHOW TABLES，查看数据库的表对象、视图对象

SHOW TABLES;

或者用：SHOW STATUS查看

SHOW TABLE STATUS LIKE '视图名称'\G

也可以查看视图的详细定义信息：

SHOW CREATE VIEW 视图名称;

6、使用mysql时，用户登录，以及用户查看、增加，删除表等操作时，mysql是怎么判断该用户是否有权限？

Mysql服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库中。

Mysql数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的是user表、db表、tables_priv表、columns_priv表和proc_priv表等。

其中：
user表的主键是User+Host；
db表的主键是User+Host+Db；
tables_priv表的主键是：User+Host+Db+Table_name；
columns_priv表的主键是：User+Host+Db+Table_name+Column_name；

在启动时，服务器将这些数据库表中的权限信息的内容读入内存。然后在执行所有命令之前，都会先判断下mysql数据库中的这些表里是否记录了对应的权限。如果有的话就会放行，否则就会返回无权限。

7、Mysql的执行流程大致有哪些？

第１步：查询缓存
Server如果在查询缓存中发现了这条sql语句，就会直接将结果返回给客户端；如果没有，就进入解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在Mysql 8.0 之后就抛弃了这个功能。

第２步：解析器解析
解析器中对sql语句进行词法分析和语义分析，从而判断输入的这条sql语句是否满足mysql语法。

第３步：优化器优化
经过了解析器，Mysql就知道了你要做什么了。而一条查询可以有很多种执行方式，虽然最后都返回相同的结果，而优化器的作用就是找到这里面最好的执行计划。

第４步：执行器执行
在执行之前需要判断该用户是否具备权限。如果没有，就会返回权限错误。如果具备权限，就执行sql查询并返回结果。在mysql 8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

８、如果有一个sql的执行时间特别长，想要分析该条sql语句在sql执行中每一个过程的耗时，应该用什么命令来排查？

可以使用show profiles 查看历史的sql命令，然后通过show profile for query id 命令得到该sql语句在每一步骤中的时间。**因此如果某一个sql语句运行时间特别长，我们可以通过该条命令来排查究竟在哪一个步骤中的耗时最长。
详情可以查看博客：Mysql高级篇学习总结4：Mysql逻辑架构剖析、sql执行流程、数据库缓冲池设置

９、数据库缓冲池和查询缓存是一个东西么？数据库缓冲池有什么作用？

首先要明确的是：缓冲池和查询缓存不是一个东西。

作用１：将数据预先放到内存中，提升访问效率。
InnoDB存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中，也就是说即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。

将整个页加载到内存中后就可以进行读写访问了，在进行完读写访问之后，并不着急把该页对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页面时，就可以省去磁盘IO的开销了。

作用２：当遇到高并发量访问时，可以增加Buffer pool实例个数，从而提升访问效率。

详情可以查看博客：Mysql高级篇学习总结4：Mysql逻辑架构剖析、sql执行流程、数据库缓冲池设置

10、Mysql的InnoDB引擎相对MyISAM引擎的优点？除了InnoDB，什么场景下可以选择其他存储引擎？

InnoDB相对MyISAM的优点：
１）MyISAM不支持外键，而InnoDB支持外键。
２）MyISAM不支持事务，而InnoDB支持事务。因此当除了查询和插入操作之外，还有比较多的更新和删除操作，优先考虑InnoDB。
３）MyISAM仅支持表锁，即使操作一条记录，也会锁住整个表。而InnoDB支持行锁，操作时只锁某一行，不对其他行有影响，因此InnoDB适合高并发的操作。

什么时候选择其他存储引擎？：
１）如果是小型应用，系统以插入操作和查询操作为主，只有很少的更新、删除操作，并且对事务的要求没有那么高，那么可以选择MyISAM存储引擎。MyISAM存储引擎的优势在于占用空间小，处理速度快；缺点是不支持事务的完整性和并发性。
２）如果是存储历史记录之类的不再修改的数据，可以使用Archive。Archive表仅支持插入和查询两个操作，拥有很好的压缩机制。适合日志和数据采集（档案）类的应用，拥有很高的插入速度，但是对查询的支持较差。
３）如果数据文件格式是CSV格式的，可以优先使用CSV引擎。

11、Mysql中B+树的一般多少层就可以了？为什么？

一般情况下，我们用到的B+树都不会超过4层！

假设一条数据记录大小是160B，那么一个磁盘页（16K）最多可以存放100条数据。而目录页由于只需要存放数据记录的最小主键值和数据记录页的地址，因此一个磁盘页存放的目录项数据肯定比数据项个数多，假设能存放1000条。

1. 如果B+树只有1层：一个磁盘页（16K）最多可以存放100条数据。
2. 如果B+树有2层：最多能存放1000 × 100 = 10,0000（10万条数据）
3. 如果B+树有3层：最多能存放1000 × 1000 × 100 = 1,0000,0000（1亿条数据）
4. 如果B+树有４层：最多能存放1000 × 1000 × 1000 × 100 = 1000,0000,0000（1000亿条数据）

因此1000亿条数据，通过主键值去查找最多只需要加载４次磁盘页（３次目录项页、１次用户数据记录页）就可以找到数据，并且每一个页面内还有Page Directory(页目录)，也就是可以通过二分法快速定位，不用通过链表一个一个地查询。

12、什么是回表操作？

使用非聚簇索引先查找到主键值，由于非聚簇索引中不包含用户记录。所以如果想查找到完整用户记录的话，还是需要到聚簇索引中再查一遍，这个过程称为回表。

因为这种按照非主键列建立的B+树需要一次回表操作才可以定位到完整的用户记录，所以这种B+树也被称为二级索引（secondary index），或者辅助索引。

非聚簇索引的存在不影响数据子啊聚簇索引中的组织，所以一张表可以有多个非聚簇索引。

13、MyISAM与InnoDB的不同有哪些？在innoDB中，能否使用过长的字段作为主键？

MyISAM的索引方式都是非聚簇的。而InnoDB除了有非聚簇之外，还包含一个聚簇索引。

1. InnoDB的数据文件本身就是索引文件（.idb）。而MyISAM的索引文件（.MYI）和数据文件（.MYD）是分离的，索引文件仅保存数据记录的地址。
2. InnoDB如果根据主键值对聚簇索引进行查找，只需要一次就能找到用户数据记录。但是MyISAM的索引文件由于存储的是用户数据记录的地址，所以一定会有一次回表操作。
3. InnoDB的非聚簇索引存储的是数据记录的主键值，然后需要通过回表操作，通过主键值再来找到数据记录。而MyISAM索引记录的就是用户记录的地址，所以MyISAM的回表操作肯定比InnoDB要快。
4. InnoDB要求表必须要有主键，如果没有显示指定，则会自动选择一个可以非空且唯一标识数据记录的列作为主键，如果没找到的话，会自动生成一个隐含字段作为主键，这个字段长度为６个字节，类型为长整型。而MyISAM可以没有。

小结：
了解不同存储引擎的索引实现方式，对于正确使用和优化索引都非常有帮助。

举例１：知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键。因为所有二级索引都引用主键索引，过长的主键会令二级索引变得过大。

举例２：在InnoDB中，用非单调的字段作为主键不是一个好主意。非单调的主键会造成在插入新纪录时，数据文件为了维持B+树的特性而频繁地分裂调整，十分低效，使用自增字段作为主键则是一个很好的选择。

14、可以对varchar字段建立索引么？如果对varchar字段建立索引，有什么要注意的么？可以直接对varchar整个字段建立索引么？

在Alibaba的《Java开发手册》中有强制提醒：
【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

假设字符串很长，需要为这个字符串列建立索引的话，那就意味着对应的B+树有这么２个问题：

• B+树索引中**的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，**在索引中占用的存储空间越大。
• 如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

可以通过以下语句来查询该索引长度的选择度，如果越接近于１，说明散列度越高：

select count(distinct left(列名, 索引长度)) / count(*) from 表名;