精华内容
下载资源
问答
  • oracle sql:select a.*,max(val) over(partition by x1order by x2) from table a 测试用例: select t.*,max(score) over (partition by id order by calss) max ...mysql实现: -- 通过判断 clas...

    oracle sql:select a.*,max(val) over(partition by x1 order by x2) from table a 

    测试用例:

    select t.*,max(score) over (partition by id order by calss) max
    from T_SCORE t

    数据

     

    mysql实现:

    -- 通过判断 class 是否>=取到的class 实现
    select a.*,
    case when a.calss<b.calss then a.score else b.score end max
    from t_score a
    left join (
    -- 这一层是取到按id分组的最大值所在的最小的class 
    select a.id,min(a.calss) calss,b.score
    from t_score a
    left join (select id,max(score) score from t_score group by id) b 
    on a.id=b.id
    where a.score=b.score
    group by a.id,b.score
    ) b 
    on a.id=b.id
    order by a.id,a.calss
    

    比较麻烦,需要至少3次关联,欢迎补充

     

    展开全文
  • MySQL 面试题

    万次阅读 多人点赞 2019-09-02 16:03:33
    MySQL 面试题 MySQL 涉及的内容非常非常非常多,所以面试题也容易写的杂乱。当年,我们记着几个一定要掌握的重心: 重点的题目添加了【重点】前缀。 索引。 锁。 事务和隔离级别。 因为 MySQL 还会有部分内容和...

    MySQL 面试题

    MySQL 涉及的内容非常非常非常多,所以面试题也容易写的杂乱。当年,我们记着几个一定要掌握的重心:

    重点的题目添加了【重点】前缀。

    1. 索引。
    2. 锁。
    3. 事务和隔离级别。

    因为 MySQL 还会有部分内容和运维相关度比较高,所以本文我们分成两部分【开发】【运维】两部分。

    • 对于【开发】部分,我们需要掌握。
    • 对于【运维】部分,更多考验开发的知识储备情况,当然能回答出来是比较好的,特别是对于高级开发工程师、架构师等。

    开发

    为什么互联网公司一般选择 MySQL 而不是 Oracle?

    免费、流行、够用。

    ? 当然,这个回答要稍微润色下。不过一般,很少问这个问题了。

    数据库的三范式是什么?什么是反模式?

    艿艿:重点在于反模式的回答。实际开发中,不会严格遵守三范式。

    胖友直接看 《服务端指南 数据存储篇 | MySQL(07) 范式与反模式》

    MySQL 有哪些数据类型?

    MySQL 支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。具体可以看看 《MySQL 数据类型》 文档。

    • 正确的使用数据类型,对数据库的优化是非常重要的。

    ? MySQL 中 varchar 与 char 的区别?varchar(50) 中的 50 代表的涵义?

    • 1、varchar 与 char 的区别,char 是一种固定长度的类型,varchar 则是一种可变长度的类型。
    • 2、varchar(50) 中 50 的涵义最多存放 50 个字符。varchar(50) 和 (200) 存储 hello 所占空间一样,但后者在排序时会消耗更多内存,因为 ORDER BY col 采用 fixed_length 计算 col 长度(memory引擎也一样)。所以,实际场景下,选择合适的 varchar 长度还是有必要的。

    ? int(11) 中的 11 代表什么涵义?

    int(11) 中的 11 ,不影响字段存储的范围,只影响展示效果。具体可以看看 《MySQL 中 int 长度的意义》 文章。

    ? 金额(金钱)相关的数据,选择什么数据类型?

    • 方式一,使用 int 或者 bigint 类型。如果需要存储到分的维度,需要 *100 进行放大。
    • 方式二,使用 decimal 类型,避免精度丢失。如果使用 Java 语言时,需要使用 BigDecimal 进行对应。

    ? 一张表,里面有 ID 自增主键,当 insert 了 17 条记录之后,删除了第 15,16,17 条记录,再把 MySQL 重启,再 insert 一条记录,这条记录的 ID 是 18 还是 15?

    • 一般情况下,我们创建的表的类型是 InnoDB ,如果新增一条记录(不重启 MySQL 的情况下),这条记录的 ID 是18 ;但是如果重启 MySQL 的话,这条记录的 ID 是 15 。因为 InnoDB 表只把自增主键的最大 ID 记录到内存中,所以重启数据库或者对表 OPTIMIZE 操作,都会使最大 ID 丢失。
    • 但是,如果我们使用表的类型是 MyISAM ,那么这条记录的 ID 就是 18 。因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里面,重启 MYSQL 后,自增主键的最大 ID 也不会丢失。

    最后,还可以跟面试官装个 x ,生产数据,不建议进行物理删除记录。

    ? 表中有大字段 X(例如:text 类型),且字段 X 不会经常更新,以读为为主,请问您是选择拆成子表,还是继续放一起?写出您这样选择的理由

    • 拆带来的问题:连接消耗 + 存储拆分空间。

      如果能容忍拆分带来的空间问题,拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序 IO ,减少连接消耗,最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗。

    • 不拆可能带来的问题:查询性能。

      如果能容忍不拆分带来的查询性能损失的话,上面的方案在某个极致条件下肯定会出现问题,那么不拆就是最好的选择。

    实际场景下,例如说商品表数据量比较大的情况下,会将商品描述单独存储到一个表中。即,使用拆的方案。

    MySQL 有哪些存储引擎?

    MySQL 提供了多种的存储引擎:

    • InnoDB
    • MyISAM
    • MRG_MYISAM
    • MEMORY
    • CSV
    • ARCHIVE
    • BLACKHOLE
    • PERFORMANCE_SCHEMA
    • FEDERATED

    具体每种存储引擎的介绍,可以看看 《数据库存储引擎》

    ? 如何选择合适的存储引擎?

    提供几个选择标准,然后按照标准,选择对应的存储引擎即可,也可以根据 常用引擎对比 来选择你使用的存储引擎。使用哪种引擎需要根据需求灵活选择,一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎,将会提高整个数据库的性能。

    1. 是否需要支持事务。

    2. 对索引和缓存的支持。

    3. 是否需要使用热备。

    4. 崩溃恢复,能否接受崩溃。

    5. 存储的限制。

    6. 是否需要外键支持。

      艿艿:目前开发已经不考虑外键,主要原因是性能。具体可以看看 《从 MySQL 物理外键开始的思考》 文章。

    目前,MySQL 默认的存储引擎是 InnoDB ,并且也是最主流的选择。主要原因如下:

    • 【最重要】支持事务。
    • 支持行级锁和表级锁,能支持更多的并发量。
    • 查询不加锁,完全不影响查询。
    • 支持崩溃后恢复。

    在 MySQL5.1 以及之前的版本,默认的存储引擎是 MyISAM ,但是目前已经不再更新,且它有几个比较关键的缺点:

    • 不支持事务。
    • 使用表级锁,如果数据量大,一个插入操作锁定表后,其他请求都将阻塞。

    艿艿:也就是说,我们不需要花太多力气在 MyISAM 的学习上。

    ? 请说明 InnoDB 和 MyISAM 的区别

    InnoDBMyISAM
    事务支持不支持
    存储限制64TB
    锁粒度行锁表锁
    崩溃后的恢复支持不支持
    外键支持不支持
    全文检索5.7 版本后支持支持

    更完整的对比,可以看看 《数据库存储引擎》「常用引擎对比」 小节。

    ? 请说说 InnoDB 的 4 大特性?

    艿艿:貌似我面试没被问过…反正,我是没弄懂过~~

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

    ? 为什么 SELECT COUNT(*) FROM table 在 InnoDB 比 MyISAM 慢?

    对于 SELECT COUNT(*) FROM table 语句,在没有 WHERE 条件的情况下,InnoDB 比 MyISAM 可能会慢很多,尤其在大表的情况下。因为,InnoDB 是去实时统计结果,会全表扫描;而 MyISAM 内部维持了一个计数器,预存了结果,所以直接返回即可。

    详细的原因,胖友可以看看 《高性能 MySQL 之 Count 统计查询》 博客。

    ? 各种不同 MySQL 版本的 Innodb 的改进?

    艿艿:这是一个选择了解的问题。

    MySQL5.6 下 Innodb 引擎的主要改进:

    1. online DDL
    2. memcached NoSQL 接口
    3. transportable tablespace( alter table discard/import tablespace)
    4. MySQL 正常关闭时,可以 dump 出 buffer pool 的( space, page_no),重启时 reload,加快预热速度
    5. 索引和表的统计信息持久化到 mysql.innodb_table_stats 和 mysql.innodb_index_stats,可提供稳定的执行计划
    6. Compressed row format 支持压缩表

    MySQL5.7 下 Innodb 引擎的主要改进:

    • 1、修改 varchar 字段长度有时可以使用

      这里的“有时”,指的是也有些限制。可见 《MySQL 5.7 online ddl 的一些改进》

    • 2、Buffer pool 支持在线改变大小

    • 3、Buffer pool 支持导出部分比例

    • 4、支持新建 innodb tablespace,并可以在其中创建多张表

    • 5、磁盘临时表采用 innodb 存储,并且存储在 innodb temp tablespace 里面,以前是 MyISAM 存储

    • 6、透明表空间压缩功能

    重点】什么是索引?

    索引,类似于书籍的目录,想找到一本书的某个特定的主题,需要先找到书的目录,定位对应的页码。

    MySQL 中存储引擎使用类似的方式进行查询,先去索引中查找对应的值,然后根据匹配的索引找到对应的数据行。

    ? 索引有什么好处?

    1. 提高数据的检索速度,降低数据库IO成本:使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
    2. 降低数据排序的成本,降低CPU消耗:索引之所以查的快,是因为先将数据排好序,若该字段正好需要排序,则正好降低了排序的成本。

    ? 索引有什么坏处?

    1. 占用存储空间:索引实际上也是一张表,记录了主键与索引字段,一般以索引文件的形式存储在磁盘上。
    2. 降低更新表的速度:表的数据发生了变化,对应的索引也需要一起变更,从而减低的更新速度。否则索引指向的物理数据可能不对,这也是索引失效的原因之一。

    ? 索引的使用场景?

    • 1、对非常小的表,大部分情况下全表扫描效率更高。

    • 2、对中大型表,索引非常有效。

    • 3、特大型的表,建立和使用索引的代价随着增长,可以使用分区技术来解决。

      实际场景下,MySQL 分区表很少使用,原因可以看看 《互联网公司为啥不使用 MySQL 分区表?》 文章。

      对于特大型的表,更常用的是“分库分表”,目前解决方案有 Sharding Sphere、MyCAT 等等。

    ? 索引的类型?

    索引,都是实现在存储引擎层的。主要有六种类型:

    • 1、普通索引:最基本的索引,没有任何约束。

    • 2、唯一索引:与普通索引类似,但具有唯一性约束。

    • 3、主键索引:特殊的唯一索引,不允许有空值。

    • 4、复合索引:将多个列组合在一起创建索引,可以覆盖多个列。

    • 5、外键索引:只有InnoDB类型的表才可以使用外键索引,保证数据的一致性、完整性和实现级联操作。

    • 6、全文索引:MySQL 自带的全文索引只能用于 InnoDB、MyISAM ,并且只能对英文进行全文检索,一般使用全文索引引擎。

      常用的全文索引引擎的解决方案有 Elasticsearch、Solr 等等。最为常用的是 Elasticsearch 。

    具体的使用,可以看看 《服务端指南 数据存储篇 | MySQL(03) 如何设计索引》

    ? MySQL 索引的“创建”原则?

    注意,是“创建”噢。

    • 1、最适合索引的列是出现在 WHERE 子句中的列,或连接子句中的列,而不是出现在 SELECT 关键字后的列。

    • 2、索引列的基数越大,索引效果越好。

      具体为什么,可以看看如下两篇文章:

    • 3、根据情况创建复合索引,复合索引可以提高查询效率。

      因为复合索引的基数会更大。

    • 4、避免创建过多的索引,索引会额外占用磁盘空间,降低写操作效率。

    • 5、主键尽可能选择较短的数据类型,可以有效减少索引的磁盘占用提高查询效率。

    • 6、对字符串进行索引,应该定制一个前缀长度,可以节省大量的索引空间。

    ? MySQL 索引的“使用”注意事项?

    注意,是“使用”噢。

    • 1、应尽量避免在 WHERE 子句中使用 !=<> 操作符,否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

      注意,column IS NULL 也是不可以使用索引的。

    • 2、应尽量避免在 WHERE 子句中使用 OR 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:SELECT id FROM t WHERE num = 10 OR num = 20

    • 3、应尽量避免在 WHERE 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。

    • 4、应尽量避免在 WHERE 子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。

    • 5、不要在 WHERE 子句中的 = 左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

    • 6、复合索引遵循前缀原则。

    • 7、如果 MySQL 评估使用索引比全表扫描更慢,会放弃使用索引。如果此时想要索引,可以在语句中添加强制索引。

    • 8、列类型是字符串类型,查询时一定要给值加引号,否则索引失效。

    • 9、LIKE 查询,% 不能在前,因为无法使用索引。如果需要模糊匹配,可以使用全文索引。

    关于这块,可以看看 《服务端指南 数据存储篇 | MySQL(04) 索引使用的注意事项》 文章,写的更加细致。

    ? 以下三条 SQL 如何建索引,只建一条怎么建?

    WHERE a = 1 AND b = 1
    WHERE b = 1
    WHERE b = 1 ORDER BY time DESC
    
    
    • 以顺序 b , a, time 建立复合索引,CREATE INDEX table1_b_a_time ON index_test01(b, a, time)
    • 对于第一条 SQL ,因为最新 MySQL 版本会优化 WHERE 子句后面的列顺序,以匹配复合索引顺序。

    ? 想知道一个查询用到了哪个索引,如何查看?

    EXPLAIN 显示了 MYSQL 如何使用索引来处理 SELECT 语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。

    使用方法,在 SELECT 语句前加上 EXPLAIN 就可以了。 《MySQL explain 执行计划详细解释》

    【重点】MySQL 索引的原理?

    解释 MySQL 索引的原理,篇幅会比较长,并且网络上已经有靠谱的资料可以看,所以艿艿这里整理了几篇,胖友可以对照着看。

    下面,艿艿对关键知识做下整理,方便胖友回顾。

    几篇好一点的文章:

    《MySQL索引背后的数据结构及算法原理》

    《MySQL 索引原理》

    《深入理解 MySQL 索引原理和实现 —— 为什么索引可以加速查询?》

    MySQL 有哪些索引方法?

    在 MySQL 中,我们可以看到两种索引方式:

    什么是 B-Tree 索引?

    B-Tree 是为磁盘等外存储设备设计的一种平衡查找树。因此在讲 B-Tree 之前先了解下磁盘的相关知识。

    • 系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的,位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么。
    • InnoDB存储引擎中有页(Page)的概念,页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为 16 KB,可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K ,在 MySQL 中可通过如下命令查看页的大小:
    mysql> show variables like 'innodb_page_size';
    
    • 而系统一个磁盘块的存储空间往往没有这么大,因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB 。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位,在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置,这将会减少磁盘 I/O 次数,提高查询效率。

    B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree,首先定义一条记录为一个二元组 [key, data] ,key 为记录的键值,对应表中的主键值,data 为一行记录中除主键外的数据。对于不同的记录,key值互不相同。

    一棵 m 阶的 B-Tree 有如下特性:

    1. 每个节点最多有 m 个孩子。
      • 除了根节点和叶子节点外,其它每个节点至少有 Ceil(m/2) 个孩子。
      • 若根节点不是叶子节点,则至少有 2 个孩子。
    2. 所有叶子节点都在同一层,且不包含其它关键字信息。
    3. 每个非叶子节点包含 n 个关键字信息(P0,P1,…Pn, k1,…kn)
      • 关键字的个数 n 满足:ceil(m/2)-1 <= n <= m-1
      • ki(i=1,…n) 为关键字,且关键字升序排序。
      • Pi(i=0,…n) 为指向子树根节点的指针。P(i-1) 指向的子树的所有节点关键字均小于 ki ,但都大于 k(i-1) 。

    B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支,如下图所示为一个 3 阶的 B-Tree:

    B-Tree 的结构

    • 每个节点占用一个盘块的磁盘空间,一个节点上有两个升序排序的 key 和三个指向子树根节点的 point ,point 存储的是子节点所在磁盘块的地址。两个 key 划分成的三个范围域,对应三个 point 指向的子树的数据的范围域。
    • 以根节点为例,key 为 17 和 35 ,P1 指针指向的子树的数据范围为小于 17 ,P2 指针指向的子树的数据范围为 [17~35] ,P3 指针指向的子树的数据范围为大于 35 。

    模拟查找 key 为 29 的过程:

    • 1、根据根节点找到磁盘块 1 ,读入内存。【磁盘I/O操作第1次】
    • 2、比较 key 29 在区间(17,35),找到磁盘块 1 的指针 P2 。
    • 3、根据 P2 指针找到磁盘块 3 ,读入内存。【磁盘I/O操作第2次】
    • 4、比较 key 29 在区间(26,30),找到磁盘块3的指针P2。
    • 5、根据 P2 指针找到磁盘块 8 ,读入内存。【磁盘I/O操作第3次】
    • 6、在磁盘块 8 中的 key 列表中找到 eky 29 。

    分析上面过程,发现需要 3 次磁盘 I/O 操作,和 3 次内存查找操作。由于内存中的 key 是一个有序表结构,可以利用二分法查找提高效率。而 3 次磁盘 I/O 操作是影响整个 B-Tree 查找效率的决定因素。B-Tree 相对于 AVLTree 缩减了节点个数,使每次磁盘 I/O 取到内存的数据都发挥了作用,从而提高了查询效率。

    什么是 B+Tree 索引?

    B+Tree 是在 B-Tree 基础上的一种优化,使其更适合实现外存储索引结构,InnoDB存储引擎就是用 B+Tree 实现其索引结构。

    从上一节中的 B-Tree 结构图中可以看到,每个节点中不仅包含数据的 key 值,还有 data 值。而每一个页的存储空间是有限的,如果 data 数据较大时将会导致每个节点(即一个页)能存储的 key 的数量很小,当存储的数据量很大时同样会导致 B-Tree 的深度较大,增大查询时的磁盘 I/O 次数,进而影响查询效率。在 B+Tree 中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储 key 值信息,这样可以大大加大每个节点存储的 key 值数量,降低 B+Tree 的高度。

    B+Tree 相对于 B-Tree 有几点不同:

    • 非叶子节点只存储键值信息。
    • 所有叶子节点之间都有一个链指针。
    • 数据记录都存放在叶子节点中。

    将上一节中的 B-Tree 优化,由于 B+Tree 的非叶子节点只存储键值信息,假设每个磁盘块能存储 4 个键值及指针信息,则变成 B+Tree 后其结构如下图所示:

    B+Tree 的结构

    • 通常在 B+Tree 上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找。

    可能上面例子中只有 22 条数据记录,看不出 B+Tree 的优点,下面做一个推算:

    • InnoDB 存储引擎中页的大小为 16KB,一般表的主键类型为 INT(占用4个字节) 或 BIGINT(占用8个字节),指针类型也一般为 4 或 8 个字节,也就是说一个页(B+Tree 中的一个节点)中大概存储 16KB/(8B+8B)=1K 个键值(因为是估值,为方便计算,这里的 K 取值为〖10〗^3)。也就是说一个深度为 3 的 B+Tree 索引可以维护10^3 *10^3 *10^3 = 10亿 条记录。
    • 实际情况中每个节点可能不能填充满,因此在数据库中,B+Tree 的高度一般都在 2~4 层。MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要 1~3 次磁盘 I/O 操作。

    B+Tree 有哪些索引类型?

    在 B+Tree 中,根据叶子节点的内容,索引类型分为主键索引非主键索引

    • 主键索引的叶子节点存的数据是整行数据( 即具体数据 )。在 InnoDB 里,主键索引也被称为聚集索引(clustered index)。
    • 非主键索引的叶子节点存的数据是整行数据的主键,键值是索引。在 InnoDB 里,非主键索引也被称为辅助索引(secondary index)。

    辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据,而是存储相应行数据的聚集索引键,即主键。当通过辅助索引来查询数据时,需要进过两步:

    • 首先,InnoDB 存储引擎会遍历辅助索引找到主键。
    • 然后,再通过主键在聚集索引中找到完整的行记录数据。

    另外,InnoDB 通过主键聚簇数据,如果没有定义主键,会选择一个唯一的非空索引代替,如果没有这样的索引,会隐式定义个主键作为聚簇索引。

    再另外,可能有胖友有和艿艿的一样疑惑,在辅助索引如果相同的索引怎么存储?最终存储到 B+Tree 非子节点中时,它们对应的主键 ID 是不同的,所以妥妥的。如下图所示:

    相同的索引怎么存储

    聚簇索引的注意点有哪些?

    聚簇索引表最大限度地提高了 I/O 密集型应用的性能,但它也有以下几个限制:

    • 1、插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于 InnoDB 表,我们一般都会定义一个自增的 ID 列为主键。

      关于这一点,可能面试官会换一个问法。例如,为什么主键需要是自增 ID ,又或者为什么主键需要带有时间性关联。

    • 2、更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB 表,我们一般定义主键为不可更新。

      MySQL 默认情况下,主键是允许更新的。对于 MongoDB ,其 主键是不允许更新的。

    • 3、二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

      当然,有一种情况可以无需二次查找,基于非主键索引查询,但是查询字段只有主键 ID ,那么在二级索引中就可以查找到。

    • 4、主键 ID 建议使用整型。因为,每个主键索引的 B+Tree 节点的键值可以存储更多主键 ID ,每个非主键索引的 B+Tree 节点的数据可以存储更多主键 ID 。

    什么是索引的最左匹配特性?

    当 B+Tree 的数据项是复合的数据结构,比如索引 (name, age, sex) 的时候,B+Tree 是按照从左到右的顺序来建立搜索树的。

    • 比如当 (张三, 20, F) 这样的数据来检索的时候,B+Tree 会优先比较 name 来确定下一步的所搜方向,如果 name 相同再依次比较 age 和 sex ,最后得到检索的数据。
    • 但当 (20, F) 这样的没有 name 的数据来的时候,B+Tree 就不知道下一步该查哪个节点,因为建立搜索树的时候 name 就是第一个比较因子,必须要先根据 name 来搜索才能知道下一步去哪里查询。
    • 比如当 (张三, F) 这样的数据来检索时,B+Tree 可以用 name 来指定搜索方向,但下一个字段 age 的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是 F 的数据了。

    这个是非常重要的性质,即索引的最左匹配特性。

    MyISAM 索引实现?

    MyISAM 索引的实现,和 InnoDB 索引的实现是一样使用 B+Tree ,差别在于 MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址

    MyISAM 索引与 InnoDB 索引的区别?

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

    【重点】请说说 MySQL 的四种事务隔离级别?

    • 1、插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于 InnoDB 表,我们一般都会定义一个自增的 ID 列为主键。

      关于这一点,可能面试官会换一个问法。例如,为什么主键需要是自增 ID ,又或者为什么主键需要带有时间性关联。

    • 2、更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB 表,我们一般定义主键为不可更新。

      MySQL 默认情况下,主键是允许更新的。对于 MongoDB ,其 主键是不允许更新的。

    • 3、二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

      当然,有一种情况可以无需二次查找,基于非主键索引查询,但是查询字段只有主键 ID ,那么在二级索引中就可以查找到。

    • 4、主键 ID 建议使用整型。因为,每个主键索引的 B+Tree 节点的键值可以存储更多主键 ID ,每个非主键索引的 B+Tree 节点的数据可以存储更多主键 ID 。

    • 1、插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于 InnoDB 表,我们一般都会定义一个自增的 ID 列为主键。

      关于这一点,可能面试官会换一个问法。例如,为什么主键需要是自增 ID ,又或者为什么主键需要带有时间性关联。

    • 2、更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB 表,我们一般定义主键为不可更新。

      MySQL 默认情况下,主键是允许更新的。对于 MongoDB ,其 主键是不允许更新的。

    • 3、二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

      当然,有一种情况可以无需二次查找,基于非主键索引查询,但是查询字段只有主键 ID ,那么在二级索引中就可以查找到。

    • 4、主键 ID 建议使用整型。因为,每个主键索引的 B+Tree 节点的键值可以存储更多主键 ID ,每个非主键索引的 B+Tree 节点的数据可以存储更多主键 ID 。

    • 1、插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于 InnoDB 表,我们一般都会定义一个自增的 ID 列为主键。

      关于这一点,可能面试官会换一个问法。例如,为什么主键需要是自增 ID ,又或者为什么主键需要带有时间性关联。

    • 2、更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB 表,我们一般定义主键为不可更新。

      MySQL 默认情况下,主键是允许更新的。对于 MongoDB ,其 主键是不允许更新的。

    • 3、二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

      当然,有一种情况可以无需二次查找,基于非主键索引查询,但是查询字段只有主键 ID ,那么在二级索引中就可以查找到。

    • 4、主键 ID 建议使用整型。因为,每个主键索引的 B+Tree 节点的键值可以存储更多主键 ID ,每个非主键索引的 B+Tree 节点的数据可以存储更多主键 ID 。

    事务就是对一系列的数据库操作(比如插入多条数据)进行统一的提交或回滚操作,如果插入成功,那么一起成功,如果中间有一条出现异常,那么回滚之前的所有操作。

    这样可以防止出现脏数据,防止数据库数据出现问题。

    事务的特性指的是?

    指的是 ACID ,如下图所示:

    事务的特性

    1. 原子性 Atomicity :一个事务(transaction)中的所有操作,或者全部完成,或者全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被恢复(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。即,事务不可分割、不可约简。
    2. 一致性 Consistency :在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束触发器级联回滚等。
    3. 隔离性 Isolation :数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
    4. 持久性 Durability :事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

    事务的并发问题?

    实际场景下,事务并不是串行的,所以会带来如下三个问题:

    • 1、脏读:事务 A 读取了事务 B 更新的数据,然后 B 回滚操作,那么 A 读取到的数据是脏数据。
    • 2、不可重复读:事务 A 多次读取同一数据,事务 B 在事务 A 多次读取的过程中,对数据作了更新并提交,导致事务 A 多次读取同一数据时,结果不一致。
    • 3、幻读:系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级,但是系统管理员 B 就在这个时候插入了一条具体分数的记录,当系统管理员 A 改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。

    小结:不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表。

    MySQL 事务隔离级别会产生的并发问题?

    • READ UNCOMMITTED(未提交读):事务中的修改,即使没有提交,对其他事务也都是可见的。

      会导致脏读。

    • READ COMMITTED(提交读):事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。

      会导致不可重复读。

      这个隔离级别,也可以叫做“不可重复读”。

    • REPEATABLE READ(可重复读):一个事务按相同的查询条件读取以前检索过的数据,其他事务插入了满足其查询条件的新数据。产生幻行。

      会导致幻读。

    • SERIALIZABLE(可串行化):强制事务串行执行。

    事务隔离级别脏读不可重复读幻读
    读未提交(read-uncommitted)
    读已提交(read-committed)
    可重复读(repeatable-read)是(x)
    串行化(serializable)
    • MySQL 默认的事务隔离级别为可重复读(repeatable-read) 。
    • 上图的 <X> 处,MySQL 因为其间隙锁的特性,导致其在可重复读(repeatable-read)的隔离级别下,不存在幻读问题。也就是说,上图 <X> 处,需要改成“否”!!!!
    • ? 记住这个表的方式,我们会发现它是自左上向右下是一个对角线。当然,最好是去理解。
    • 具体的实验,胖友可以看看 《MySQL 的四种事务隔离级别》

    【重点】请说说 MySQL 的锁机制?

    表锁是日常开发中的常见问题,因此也是面试当中最常见的考察点,当多个查询同一时刻进行数据修改时,就会产生并发控制的问题。MySQL 的共享锁和排他锁,就是读锁和写锁。

    • 共享锁:不堵塞,多个用户可以同时读一个资源,互不干扰。
    • 排他锁:一个写锁会阻塞其他的读锁和写锁,这样可以只允许一个用户进行写入,防止其他用户读取正在写入的资源。

    ? 锁的粒度?

    • 表锁:系统开销最小,会锁定整张表,MyIsam 使用表锁。
    • 行锁:最大程度的支持并发处理,但是也带来了最大的锁开销,InnoDB 使用行锁。

    ? 什么是悲观锁?什么是乐观锁?

    1)悲观锁

    它指的是对数据被外界(包括本系统当前的其他事务,以及来自外部系统的事务处理)修改持保守态度,因此,在整个数据处理过程中,将数据处于锁定状态。悲观锁的实现,往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据)。

    在悲观锁的情况下,为了保证事务的隔离性,就需要一致性锁定读。读取数据时给加锁,其它事务无法修改这些数据。修改删除数据时也要加锁,其它事务无法读取这些数据。

    2)乐观锁

    相对悲观锁而言,乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现,以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销,特别是对长事务而言,这样的开销往往无法承受。

    而乐观锁机制在一定程度上解决了这个问题。乐观锁,大多是基于数据版本( Version )记录机制实现。何谓数据版本?即为数据增加一个版本标识,在基于数据库表的版本解决方案中,一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时,将此版本号一同读出,之后更新时,对此版本号加一。此时,将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对,如果提交的数据版本号大于数据库表当前版本号,则予以更新,否则认为是过期数据。

    什么是死锁?

    多数情况下,可以认为如果一个资源被锁定,它总会在以后某个时间被释放。而死锁发生在当多个进程访问同一数据库时,其中每个进程拥有的锁都是其他进程所需的,由此造成每个进程都无法继续下去。简单的说,进程 A 等待进程 B 释放他的资源,B 又等待 A 释放他的资源,这样就互相等待就形成死锁。

    虽然进程在运行过程中,可能发生死锁,但死锁的发生也必须具备一定的条件,死锁的发生必须具备以下四个必要条件:

    • 互斥条件:指进程对所分配到的资源进行排它性使用,即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源,则请求者只能等待,直至占有资源的进程用毕释放。
    • 请求和保持条件:指进程已经保持至少一个资源,但又提出了新的资源请求,而该资源已被其它进程占有,此时请求进程阻塞,但又对自己已获得的其它资源保持不放。
    • 不剥夺条件:指进程已获得的资源,在未使用完之前,不能被剥夺,只能在使用完时由自己释放。
    • 环路等待条件:指在发生死锁时,必然存在一个进程——资源的环形链,即进程集合 {P0,P1,P2,•••,Pn} 中的 P0 正在等待一个 P1 占用的资源;P1 正在等待 P2 占用的资源,……,Pn 正在等待已被 P0 占用的资源。

    下列方法有助于最大限度地降低死锁:

    • 设置获得锁的超时时间。

      通过超时,至少保证最差最差最差情况下,可以有退出的口子。

    • 按同一顺序访问对象。

      这个是最重要的方式。

    • 避免事务中的用户交互。

    • 保持事务简短并在一个批处理中。

    • 使用低隔离级别。

    • 使用绑定连接。

    ? MySQL 中 InnoDB 引擎的行锁是通过加在什么上完成(或称实现)的?为什么是这样子的??

    InnoDB 是基于索引来完成行锁。例如:SELECT * FROM tab_with_index WHERE id = 1 FOR UPDATE

    • FOR UPDATE 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,如果 id 不是索引键那么 InnoDB 将完成表锁,并发将无从谈起。

    【重要】MySQL 查询执行顺序?

    MySQL 查询执行的顺序是:

    (1)     SELECT
    (2)     DISTINCT <select_list>
    (3)     FROM <left_table>
    (4)     <join_type> JOIN <right_table>
    (5)     ON <join_condition>
    (6)     WHERE <where_condition>
    (7)     GROUP BY <group_by_list>
    (8)     HAVING <having_condition>
    (9)     ORDER BY <order_by_condition>
    (10)    LIMIT <limit_number>
    

    具体的,可以看看 《SQL 查询之执行顺序解析》 文章。

    【重要】聊聊 MySQL SQL 优化?

    可以看看如下几篇文章:

    另外,除了从 SQL 层面进行优化,也可以从服务器硬件层面,进一步优化 MySQL 。具体可以看看 《MySQL 数据库性能优化之硬件优化》

    编写 SQL 查询语句的考题合集

    MySQL 数据库 CPU 飙升到 500% 的话,怎么处理?

    当 CPU 飙升到 500% 时,先用操作系统命令 top 命令观察是不是 mysqld 占用导致的,如果不是,找出占用高的进程,并进行相关处理。

    如果此时是 IO 压力比较大,可以使用 iostat 命令,定位是哪个进程占用了磁盘 IO 。

    如果是 mysqld 造成的,使用 show processlist 命令,看看里面跑的 Session 情况,是不是有消耗资源的 SQL 在运行。找出消耗高的 SQL ,看看执行计划是否准确, index 是否缺失,或者实在是数据量太大造成。一般来说,肯定要 kill 掉这些线程(同时观察 CPU 使用率是否下降),等进行相应的调整(比如说加索引、改 SQL 、改内存参数)之后,再重新跑这些 SQL。

    也可以查看 MySQL 慢查询日志,看是否有慢 SQL 。

    也有可能是每个 SQL 消耗资源并不多,但是突然之间,有大量的 Session 连进来导致 CPU 飙升,这种情况就需要跟应用一起来分析为何连接数会激增,再做出相应的调整,比如说限制连接数等。

    ? 在 MySQL 服务器运行缓慢的情况下输入什么命令能缓解服务器压力?

    1)检查系统的状态

    通过操作系统的一些工具检查系统的状态,比如 CPU、内存、交换、磁盘的利用率,根据经验或与系统正常时的状态相比对,有时系统表面上看起来看空闲,这也可能不是一个正常的状态,因为 CPU 可能正等待IO的完成。除此之外,还应观注那些占用系统资源(CPU、内存)的进程。

    • 使用 sar 来检查操作系统是否存在 IO 问题。
    • 使用 vmstat 监控内存 CPU 资源。
    • 磁盘 IO 问题,处理方式:做 raid10 提高性能 。
    • 网络问题,telnet 一下 MySQL 对外开放的端口。如果不通的话,看看防火墙是否正确设置了。另外,看看 MySQ L是不是开启了 skip-networking 的选项,如果开启请关闭。

    2)检查 MySQL 参数

    • max_connect_errors
    • connect_timeout
    • skip-name-resolve
    • slave-net-timeout=seconds
    • master-connect-retry

    3)检查 MySQL 相关状态值

    • 关注连接数
    • 关注下系统锁情况
    • 关注慢查询(slow query)日志

    Innodb 的事务与日志的实现方式

    ? 有多少种日志?

    • redo 日志
    • undo 日志

    ? 日志的存放形式?

    • redo:在页修改的时候,先写到 redo log buffer 里面, 然后写到 redo log 的文件系统缓存里面(fwrite),然后再同步到磁盘文件(fsync)。
    • undo:在 MySQL5.5 之前,undo 只能存放在 ibdata* 文件里面, 5.6 之后,可以通过设置 innodb_undo_tablespaces 参数把 undo log 存放在 ibdata* 之外。

    ? 事务是如何通过日志来实现的,说得越深入越好

    艿艿:这个流程的理解还是比较简单的,实际思考实现感觉还是蛮复杂的。

    基本流程如下:

    • 因为事务在修改页时,要先记 undo ,在记 undo 之前要记 undo 的 redo, 然后修改数据页,再记数据页修改的 redo。 redo(里面包括 undo 的修改)一定要比数据页先持久化到磁盘。
    • 当事务需要回滚时,因为有 undo,可以把数据页回滚到前镜像的状态。
    • 崩溃恢复时,如果 redo log 中事务没有对应的 commit 记录,那么需要用 undo 把该事务的修改回滚到事务开始之前。如果有 commit 记录,就用 redo 前滚到该事务完成时并提交掉。

    MySQL binlog 的几种日志录入格式以及区别

    ? 各种日志格式的涵义

    binlog 有三种格式类型,分别如下:

    1)Statement

    每一条会修改数据的 SQL 都会记录在 binlog 中。

    • 优点:不需要记录每一行的变化,减少了 binlog 日志量,节约了 IO,提高性能。(相比 row 能节约多少性能与日志量,这个取决于应用的 SQL 情况,正常同一条记录修改或者插入 row 格式所产生的日志量还小于 Statement 产生的日志量,但是考虑到如果带条件的 update 操作,以及整表删除,alter 表等操作,ROW 格式会产生大量日志,因此在考虑是否使用 ROW 格式日志时应该跟据应用的实际情况,其所产生的日志量会增加多少,以及带来的 IO 性能问题。)

    • 缺点:由于记录的只是执行语句,为了这些语句能在 slave 上正确运行,因此还必须记录每条语句在执行的时候的一些相关信息,以保证所有语句能在 slave 得到和在 master 端执行时候相同 的结果。另外 MySQL 的复制,像一些特定函数功能,slave 可与 master 上要保持一致会有很多相关问题(如 sleep() 函数,last_insert_id(),以及 user-defined functions(udf) 会出现问题)。

    • 使用以下函数的语句也无法被复制:

      • LOAD_FILE()

      • UUID()

      • USER()

      • FOUND_ROWS()

      • SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)

        同时在 INSERT …SELECT 会产生比 RBR 更多的行级锁 。

    2)Row

    不记录 SQL 语句上下文相关信息,仅保存哪条记录被修改。

    • 优点:binlog 中可以不记录执行的 SQL 语句的上下文相关的信息,仅需要记录那一条记录被修改成什么了。所以 rowlevel 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程,或 function ,以及 trigger 的调用和触发无法被正确复制的问题。
    • 缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容,比如一条 Update 语句,修改多条记录,则 binlog 中每一条修改都会有记录,这样造成 binlog 日志量会很大,特别是当执行 alter table 之类的语句的时候,由于表结构修改,每条记录都发生改变,那么该表每一条记录都会记录到日志中。

    3)Mixedlevel

    是以上两种 level 的混合使用。

    • 一般的语句修改使用 Statement 格式保存 binlog 。
    • 如一些函数,statement 无法完成主从复制的操作,则采用 Row 格式保存 binlog 。

    MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式,也就是在 Statement 和 Row 之间选择 一种。

    新版本的 MySQL 中对 row level 模式也被做了优化,并不是所有的修改都会以 row level 来记录。

    • 像遇到表结构变更的时候就会以 Statement 模式来记录。
    • 至于 Update 或者 Delete 等修改数据的语句,还是会记录所有行的变更,即使用 Row 模式。

    ? 适用场景?

    在一条 SQL 操作了多行数据时, Statement 更节省空间,Row 更占用空间。但是, Row 模式更可靠。

    因为,互联网公司,使用 MySQL 的功能相对少,基本不使用存储过程、触发器、函数的功能,选择默认的语句模式,Statement Level(默认)即可。

    ? 结合第一个问题,每一种日志格式在复制中的优劣?

    • Statement 可能占用空间会相对小一些,传送到 slave 的时间可能也短,但是没有 Row 模式的可靠。
    • Row 模式在操作多行数据时更占用空间,但是可靠。

    所以,这是在占用空间和可靠之间的选择。

    如何在线正确清理 MySQL binlog?

    MySQL 中的 binlog 日志记录了数据中的数据变动,便于对数据的基于时间点和基于位置的恢复。但日志文件的大小会越来越大,占用大量的磁盘空间,因此需要定时清理一部分日志信息。

    # 首先查看主从库正在使用的binlog文件名称
    show master(slave) status
    
    # 删除之前一定要备份
    purge master logs before'2017-09-01 00:00:00'; # 删除指定时间前的日志
    purge master logs to'mysql-bin.000001'; # 删除指定的日志文件
    
    # 自动删除:通过设置binlog的过期时间让系统自动删除日志
    show variables like 'expire_logs_days'; # 查看过期时间
    set global expire_logs_days = 30; # 设置过期时间
    

    MySQL 主从复制的流程是怎么样的?

    MySQL 的主从复制是基于如下 3 个线程的交互(多线程复制里面应该是 4 类线程):

    • 1、Master 上面的 binlog dump 线程,该线程负责将 master 的 binlog event 传到 slave 。
    • 2、Slave 上面的 IO 线程,该线程负责接收 Master 传过来的 binlog,并写入 relay log 。
    • 3、Slave 上面的 SQL 线程,该线程负责读取 relay log 并执行。
    • 4、如果是多线程复制,无论是 5.6 库级别的假多线程还是 MariaDB 或者 5.7 的真正的多线程复制, SQL 线程只做 coordinator ,只负责把 relay log 中的 binlog 读出来然后交给 worker 线程, woker 线程负责具体 binlog event 的执行。

    ? MySQL 如何保证复制过程中数据一致性?

    • 1、在 MySQL5.5 以及之前, slave 的 SQL 线程执行的 relay log 的位置只能保存在文件( relay-log.info)里面,并且该文件默认每执行 10000 次事务做一次同步到磁盘, 这意味着 slave 意外 crash 重启时, SQL 线程执行到的位置和数据库的数据是不一致的,将导致复制报错,如果不重搭复制,则有可能会导致数据不一致。
      • MySQL 5.6 引入参数 relay_log_info_repository,将该参数设置为 TABLE 时, MySQL 将 SQL 线程执行到的位置存到 mysql.slave_relay_log_info 表,这样更新该表的位置和 SQL 线程执行的用户事务绑定成一个事务,这样 slave 意外宕机后,slave 通过 innodb 的崩溃恢复可以把 SQL 线程执行到的位置和用户事务恢复到一致性的状态。
    • 2、MySQL 5.6 引入 GTID 复制,每个 GTID 对应的事务在每个实例上面最多执行一次, 这极大地提高了复制的数据一致性。
    • 3、MySQL 5.5 引入半同步复制, 用户安装半同步复制插件并且开启参数后,设置超时时间,可保证在超时时间内如果 binlog 不传到 slave 上面,那么用户提交事务时不会返回,直到超时后切成异步复制,但是如果切成异步之前用户线程提交时在 master 上面等待的时候,事务已经提交,该事务对 master 上面的其他 session 是可见的,如果这时 master 宕机,那么到 slave 上面该事务又不可见了,该问题直到 5.7 才解决。
    • 4、MySQL 5.7 引入无损半同步复制,引入参 rpl_semi_sync_master_wait_point,该参数默认为 after_sync,指的是在切成半同步之前,事务不提交,而是接收到 slave 的 ACK 确认之后才提交该事务,从此,复制真正可以做到无损的了。
    • 5、可以再说一下 5.7 的无损复制情况下, master 意外宕机,重启后发现有 binlog 没传到 slave 上面,这部分 binlog 怎么办???分 2 种情况讨论, 1 宕机时已经切成异步了, 2 是宕机时还没切成异步??? 这个怎么判断宕机时有没有切成异步呢??? 分别怎么处理???

    ? MySQL 如何解决主从复制的延时性?

    5.5 是单线程复制,5.6 是多库复制(对于单库或者单表的并发操作是没用的),5.7 是真正意义的多线程复制,它的原理是基于 group commit, 只要 master 上面的事务是 group commit 的,那 slave 上面也可以通过多个 worker线程去并发执行。 和 MairaDB10.0.0.5 引入多线程复制的原理基本一样。

    ? 工作遇到的复制 bug 的解决方法?

    5.6 的多库复制有时候自己会停止,我们写了一个脚本重新 start slave 。

    ? 你是否做过主从一致性校验,如果有,怎么做的,如果没有,你打算怎么做?

    主从一致性校验有多种工具 例如 checksum、mysqldiff、pt-table-checksum 等。

    聊聊 MySQL 备份方式?备份策略是怎么样的?

    具体的,胖友可以看看 《MySQL 高级备份策略》 。主要有几个知识点:

    • 数据的备份类型

      • 【常用】完全备份

        这是大多数人常用的方式,它可以备份整个数据库,包含用户表、系统表、索引、视图和存储过程等所有数据库对象。但它需要花费更多的时间和空间,所以,一般推荐一周做一次完全备份。

      • 增量备份

        它是只备份数据库一部分的另一种方法,它不使用事务日志,相反,它使用整个数据库的一种新映象。它比最初的完全备份小,因为它只包含自上次完全备份以来所改变的数据库。它的优点是存储和恢复速度快。推荐每天做一次差异备份。

      • 【常用】事务日志备份

        事务日志是一个单独的文件,它记录数据库的改变,备份的时候只需要复制自上次备份以来对数据库所做的改变,所以只需要很少的时间。为了使数据库具有鲁棒性,推荐每小时甚至更频繁的备份事务日志。

      • 文件备份

        数据库可以由硬盘上的许多文件构成。如果这个数据库非常大,并且一个晚上也不能将它备份完,那么可以使用文件备份每晚备份数据库的一部分。由于一般情况下数据库不会大到必须使用多个文件存储,所以这种备份不是很常用。

    • 备份数据的类型

      • 热备份
      • 温备份
      • 冷备份
    • 备份工具

      • cp
      • mysqldump
      • xtrabackup
      • lvm2 快照

    MySQL 几种备份方式?

    MySQL 一般有 3 种备份方式。

    1)逻辑备份

    使用 MySQL 自带的 mysqldump 工具进行备份。备份成sql文件形式。

    • 优点:最大好处是能够与正在运行的 MySQL 自动协同工作,在运行期间可以确保备份是当时的点,它会自动将对应操作的表锁定,不允许其他用户修改(只能访问)。可能会阻止修改操作。SQL 文件通用方便移植。
    • 缺点:备份的速度比较慢。如果是数据量很多的时候,就很耗时间。如果数据库服务器处在提供给用户服务状态,在这段长时间操作过程中,意味着要锁定表(一般是读锁定,只能读不能写入数据),那么服务就会影响的。

    2)物理备份

    艿艿:因为现在主流是 InnoDB ,所以基本不再考虑这种方式。

    直接拷贝只适用于 MyISAM 类型的表。这种类型的表是与机器独立的。但实际情况是,你设计数据库的时候不可能全部使用 MyISAM 类型表。你也不可能因为 MyISAM 类型表与机器独立,方便移植,于是就选择这种表,这并不是选择它的理由。

    • 缺点:你不能去操作正在运行的 MySQL 服务器(在拷贝的过程中有用户通过应用程序访问更新数据,这样就无法备份当时的数据),可能无法移植到其他机器上去。

    3)双机热备份。

    当数据量太大的时候备份是一个很大的问题,MySQL 数据库提供了一种主从备份的机制,也就是双机热备。

    • 优点:适合数据量大的时候。现在明白了,大的互联网公司对于 MySQL 数据备份,都是采用热机备份。搭建多台数据库服务器,进行主从复制。

    数据库不能停机,请问如何备份? 如何进行全备份和增量备份?

    可以使用逻辑备份和双机热备份。

    • 完全备份:完整备份一般一段时间进行一次,且在网站访问量最小的时候,这样常借助批处理文件定时备份。主要是写一个批处理文件在里面写上处理程序的绝对路径然后把要处理的东西写在后面,即完全备份数据库。
    • 增量备份:对 ddl 和 dml 语句进行二进制备份。且 5.0 无法增量备份,5.1 后可以。如果要实现增量备份需要在 my.ini 文件中配置备份路径即可,重启 MySQL 服务器,增量备份就启动了。

    ? 你的备份工具的选择?备份计划是怎么样的?

    视库的大小来定,一般来说 100G 内的库,可以考虑使用 mysqldump 来做,因为 mysqldump 更加轻巧灵活,备份时间选在业务低峰期,可以每天进行都进行全量备份(mysqldump 备份出来的文件比较小,压缩之后更小)。

    100G 以上的库,可以考虑用 xtrabackup 来做,备份速度明显要比 mysqldump 要快。一般是选择一周一个全备,其余每天进行增量备份,备份时间为业务低峰期。

    备份恢复时间是多长?

    物理备份恢复快,逻辑备份恢复慢。

    这里跟机器,尤其是硬盘的速率有关系,以下列举几个仅供参考:

    • 20G 的 2 分钟(mysqldump)
    • 80G 的 30分钟(mysqldump)
    • 111G 的 30分钟(mysqldump)
    • 288G 的 3 小时(xtrabackup)
    • 3T 的 4 小时(xtrabackup)

    逻辑导入时间一般是备份时间的 5 倍以上。

    备份恢复失败如何处理?

    首先在恢复之前就应该做足准备工作,避免恢复的时候出错。比如说备份之后的有效性检查、权限检查、空间检查等。如果万一报错,再根据报错的提示来进行相应的调整。

    ? mysqldump 和 xtrabackup 实现原理?

    1)mysqldump

    mysqldump 是最简单的逻辑备份方式。

    • 在备份 MyISAM 表的时候,如果要得到一致的数据,就需要锁表,简单而粗暴。
    • 在备份 InnoDB 表的时候,加上 –master-data=1 –single-transaction 选项,在事务开始时刻,记录下 binlog pos 点,然后利用 MVCC 来获取一致的数据,由于是一个长事务,在写入和更新量很大的数据库上,将产生非常多的 undo ,显著影响性能,所以要慎用。
    • 优点:简单,可针对单表备份,在全量导出表结构的时候尤其有用。
    • 缺点:简单粗暴,单线程,备份慢而且恢复慢,跨 IDC 有可能遇到时区问题

    2)xtrabackup

    xtrabackup 实际上是物理备份+逻辑备份的组合。

    • 在备份 InnoDB 表的时候,它拷贝 ibd 文件,并一刻不停的监视 redo log 的变化,append 到自己的事务日志文件。在拷贝 ibd 文件过程中,ibd文件本身可能被写”花”,这都不是问题,因为在拷贝完成后的第一个 prepare 阶段,xtrabackup 采用类似于 Innodb 崩溃恢复的方法,把数据文件恢复到与日志文件一致的状态,并把未提交的事务回滚。
    • 如果同时需要备份 MyISAM 表以及 InnoDB 表结构等文件,那么就需要用 flush tables with lock 来获得全局锁,开始拷贝这些不再变化的文件,同时获得 binlog 位置,拷贝结束后释放锁,也停止对 redo log 的监视。

    如何从 mysqldump 产生的全库备份中只恢复某一个库、某一张表?

    具体可见 《MySQL 全库备份中恢复某个库和某张表以及 mysqldump 参数 –ignore-table 介绍》 文章。

    聊聊 MySQL 集群?

    艿艿:这块艿艿懂的少,主要找了一些网络上的资料。

    ? 对于简历中写有熟悉 MySQL 高可用方案?

    我一般先问他现在管理的数据库架构是什么,如果他只说出了主从,而没有说任何 HA 的方案,那么我就可以判断出他没有实际的 HA 经验。

    不过这时候也不能就是断定他不懂 MySQL 高可用,也许是没有实际机会去使用,那么我就要问 MMM 以及 MHA 以及 MM + keepalived 等的原理、实现方式以及它们之间的优势和不足了,一般这种情况下,能说出这个的基本没有。

    • MMM 那东西好像不靠谱,据说不稳定,但是有人在用的,和 mysql-router 比较像,都是指定可写的机器和只读机器。
    • MHA 的话一句话说不完,可以搜索下相关博客。

    聊聊 MySQL 安全?

    感兴趣的胖友,可以看看:

    MySQL 有哪些日志?

    • 错误日志:记录了当 mysqld 启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息。

    • 二进制文件:记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句,不包括数据查询语句。语句以“事件”的形式保存,它描述了数据的更改过程。(定期删除日志,默认关闭)。

      就是我们上面看到的 MySQL binlog 日志。

    • 查询日志:记录了客户端的所有语句,格式为纯文本格式,可以直接进行读取。(log 日志中记录了所有数据库的操作,对于访问频繁的系统,此日志对系统性能的影响较大,建议关闭,默认关闭)。

    • 慢查询日志:慢查询日志记录了包含所有执行时间超过参数long_query_time(单位:秒)所设置值的 SQL 语句的日志。(纯文本格式)

      重要,一定要开启。

    另外,错误日志和慢查询日志的详细解释,可以看看 《MySQL 日志文件之错误日志和慢查询日志详解》 文章。

    聊聊 MySQL 监控?

    你是如何监控你们的数据库的?

    监控的工具有很多,例如 Zabbix ,Lepus ,我这里用的是 Lepus

    对一个大表做在线 DDL ,怎么进行实施的才能尽可能降低影响?

    使用 pt-online-schema-change ,具体可以看看 《MySQL 大表在线 DML 神器–pt-online-schema-change》 文章。

    另外,还有一些其它的工具,胖友可以搜索下。

    展开全文
  • Mysql case when 实现行转列时为什么要用max()或者其他聚合函数 mysql中的case when语句查询结果问题 原始数据: 不加max进行case when 加max: 很明显两次结果不同,不加max结果不对,熟悉分组聚合的...

    Mysql case when 实现行转列时为什么要用max()或者其他聚合函数

    mysql中的case when语句查询结果问题
    原始数据:
    原始数据
    不加max进行case when
    这里写图片描述
    加max:
    这里写图片描述
    很明显两次结果不同,不加max结果不对,熟悉分组聚合的同学应该一下就明白了这里还是进行下说明为什么要加max,这里直接采用这位网友的答案:

    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    这里写图片描述
    另外的解释:
    这里写图片描述

    这里相信大家都知道了为什么要加聚合函数max(),min()等等,是因为分组函数导致的,跟case when没有很大关系,分组函数一定和聚合函数一同存在,要不然你想,比如上述数据,按照名字分组后,每个组内都有三个数据,而展示的时候就只展示一条,所以必须从中选择一条展示所以才出现了上述数据不完全正确状况,所以以后大家在使用分组函数时一定要使用聚合函数SQL分组和聚合
    这里写图片描述
    这里相信大家会有些不理解,特别是新手,对于 “出现在SELECT子句中的单独的列,必须出现在GROUP BY子句中作为分组列”特别是这一行要求是大部分新手难以理解的,为什么一定要求出现在SELECT子句中的单独的列,必须出现在GROUP BY子句中作为分组列呢?大家结合刚刚解决问题的思路去向,如果是出现在GROUP BY子句中的字段,当我们去select时列值一定是唯一的。例如这里写图片描述
    这个是按照姓名分组的,我们去在select姓名,数据库会选择按照姓名分组后的一条记录显示,因为我们就是按照姓名分组的所以选择任何一条数据的姓名列值都是相同的,例如图中的‘张三’,但是为啥不出现在GROUP BY子句中的字段不能选择呢,比如‘语文’‘数学’字段,因为他们的记录值可能是不一样的,但是显示的时候只能取一条,所以取到的值可能正确也可能不正确,可能是0也可能是83,相信大家结合例子去理解这个要求已经明白了为什么要这样,以后再写的时候也能少踩坑,希望对大家有帮助。

    展开全文
  • MySQL学习总结

    千次阅读 2019-05-21 14:39:29
    MySQL学习总结 前言 春节期间看了一本mysql书《MySQL数据库应用从入门到精通》 觉得这本书相对简单、基础、实用、全面,我们大多数人喜欢搞一些高深的东西,而忽视一些简单基础的东西,在工作当中我们犯错的...

    MySQL学习总结

     

    前言

    春节期间看了一本mysql书《MySQL数据库应用从入门到精通》

    觉得这本书相对简单、基础、实用、全面,我们大多数人喜欢搞一些高深的东西,而忽视一些简单基础的东西,在工作当中我们犯错的地方往往是那些简单基础的地方,有的时候一些基础的DDL、DML并不一定是信手拈来,有些概念和用法也并不一定掌握的很准确,还需要百度的帮助。

    下面简单的总结些容易犯错或者是容易模糊的概念和用法分享给大家,抽时间整理了下 以便后续review,目前完成了两篇《基础篇》《操作应用篇》,还有一篇《数据库管理篇》主要是安全、日志、性能、维护的知识。后续整理完补发给大家,希望大家能从中受益一点点,也希望大家多分享,共同切磋、共同进步。

    第1篇 MySQL数据库基础篇

    1. 概念

    1. 数据库(DataBase,DB:是指长期保存在计算机的存储设备上,按照一定规则组织起来,可以被各种用户或应用共享的数据集合。

    2. 数据库管理系统(DataBase Management System,DBMS:是一种操作和管理数据库的大型软件,用于建立、使用和维护数据库,对数据库进行统一管理和控制,以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。当前比较流行和常用的数据库管理系统有Oracle、MySQL、SQL Server和DB2等。

    3. 数据库系统(DataBase System,DBS:是指在计算机系统中引入数据库后的系统,通常由计算机硬件、软件、数据库管理系统和数据管理员组成。

     

    在通常情况下,经常会用数据库来表示它们使用的数据库软件。这经常会引起混淆,确切地说,数据库软件应该为数据库管理系统,数据库是通过数据库管理系统创建和操作的容器。

     

    2. 数据库管理系统提供的功能

    数据定义语义(Data Definition Language,DDL:数据库管理系统提供了数据定义语言定义数据库涉及各种对象,定义数据的完整性约束,保密限制等约束。

    数据操作语言(Data Manipulation Language,DML:数据库管理系统提供了数据操作语言实现对数据的操作。基本的数据操作有两类:检索(查询)和更新(插入、删除和更新)。

    数据控制语言(Data Control Language,DCL:数据库管理系统提供了数据控制语言实现对数据库的控制,包含数据完整性控制、数据安全性控制和数据库的恢复等。

    什么是 SQL其发音为字母S-Q-L或sequel [ˈsikwəl],是Structure QueryLanguage(结构化查询语言)的缩写,是目前广泛使用的关系数据库标准语言。

     

    3. 安装和配置

    MySQL基于客户端---服务器(C\S)的数据库管理系统,即服务器软件和客户端软件。

     

    服务器软件是负责所有数据访问和处理的一个软件,而关于数据添加、删除等所有请求都来自于客户端软件。

    ü  服务器端软件为MySQL数据库管理系统,可以在本地计算机上或者具有访问权限的远程服务器上安装该软件。

    ü  客户端软件为可以操作MySQL服务器的软件。

     

    5.MySQL的版本

    目前MySQL数据库按照用户群分为社区版(Community Server)和企业版(Enterprise)

     

    从MySQL版本5开始,开始支持触发器、师徒、存储过程等数据库对象。

    常见软件版本:

    ü  GA(General Availablity):官方推崇广泛使用的版本。

    ü  RC(Release Candidate):候选版本的意思,该版本深思最接近正式版的版本。

    ü  Alpha和Bean都属于测试中版本,其中Alpha是指内侧版本,Bean是指公测版本。

     

    注意:如果MySQL安装在服务器上,一定要选择“Add Firewall exception for this port”复选框,这样就可以在同一网络内的用户可以访问该端口;

     

    如果MySQL安装在服务器上,需要选择“Enable root access from remove machines”复选框来设置可以让远程计算机通过用户root来登陆MySQL。

     

    6.MySQL目录说明

    l  Bin文件夹:存放可执行文件。

    l  Include文件夹:存放头文件。

    l  Lib文件夹:存放库文件。

    l  Share文件夹:存放字符集、语言等信息。

     

    各个.ini文件的含义如下:

    my.ini文件:MySQL软件正在使用的配置文件。

    l  my-huge.ini文件:当MySQL软件为超大型数据库时时用的配置文件。

    l  my-innodb-heavy-4G.ini:当MySQL软件的存储引擎为InnoDB,而且内存不小于4GB时使用的配置文件。

    l  my-large.ini:当MySQL软件为大型数据库时使用的配置文件。

    l  my-medium.ini:当MySQL软件为中型数据库时使用的配置文件。

    l  my-small.ini:当MySQL软件为小型数据库时使用的配置文件。

    l  my-template.ini:配置文件模板。

     

    在DOS窗口查看window是系统已经启动的服务命令:

    net start

    net start MySQL  启动

    net stop MySQL  停止

     

    DOS窗口连接MySQL

    mysql –h 127.0.0.1 –u root –p

     

    执行上面命令时,如果出现 mysql 不是内部或外部命令,也不是可运行的程序或批处理文件。

    说明在安装时没有勾选“Include Bin Directory in windows PATH”复选框。

    可以在通过设置环境变量path来完成,变量值为MySQL安装的目录到\bin目录;

     

    提示:如果是免安装版的也可以设置成为windows服务,自己查找下资料吧,也是很简单。

    My.ini中添加[WindowsMySQLServer]

    Server=” C:\\mysql\\bin\\mysqld.exe”

    运行中:C:\\mysql\\bin\\mysqld.exe–install

     

     

    第2篇 MySQL数据库操作和应用篇

    1.数据库和数据库对象

    数据库是一种可以通过某种方式存储数据库对象的容器。

     

    各个系统数据库作用:

    l  information_schema:主要存储了系统中的一些数据库对象信息,例如用户表信息、列信息、权限信息、字符集信息和分区信息等。

    l  performance_schema:主要存储数据库服务器性能参数。

    l  mysql:主要存储了系统的用户权限信息。

    l  test:该数据库为MySQL数据库管理系统自动创建的测试数据库,任何用户都可以使用。

     

    所谓数据库对象是存储、管理和使用数据的不同结构形式,主要包含表、视图、存储过程、函数、触发器和事件等。

     

    2.存储引擎

    在MySQL中查看数据库的存储引擎:

    ü  show engines;   或者 showengines \G  或者  show engines \g

    ü  show variables like ‘have%’;  查看所支持的存储引擎。

    ü  show variables like ‘storage_engine%’;  查询默认存储引擎。

     

    注:在具体执行SQL语句中,可以用“;”、“\g”和“\G”符号表示语句结束。

    以“;”、“\g”结束符作用一样,而“\G”符号除了表示语句结束外,还可以使得结果显示更美观,相当于格式化的作用。

     

    MySQL 5.5支持9种存储引擎,分别为FEDERATED、MRG_MYISAM、MyISAM、BLACKHOLE、CSV、MEMORY、ARCHIVE、InnoDB和PERFORMANCE_SCHEMA。

     

    主要介绍MyISAM、InnoDb和MEMORY三种存储引擎特性的对比:

    l  MyISAM:不支持事物、也不支持外键,所以访问速度比较快。因此对事物完整性没有要求并以访问为主的应用适合使用该存储引擎。

    l  InnoDB:支持具有提交、回滚和崩溃恢复能力的事物,所以比MyISAM存储引擎占用更多的磁盘空间。因此当需要进行频繁的更新、删除操作,同时还对事物的完整性要求比较高,需要实现并发控制,此时适合使用该存储引擎。

    l  MEMORY:使用内存来存储数据,因此访问速度快,但没有安全保障。如果应用中涉及数据比较小,需要进行快速的访问,则适合使用该存储引擎。

     

    3.数据类型

    浮点数类型

    l  FLOAT(4字节)

    l  DOUBLE(8字节)

    当存储小数数据,两者皆可,但需要精确到小数点后10位以上,就需要选择DOUBLE类型。

    定点数类型

    l  DEC(M,D)    

    l  DECIMAL(M,D)

    字节M+2  最大和最小值取值范围与DOUBLE一样。但是有效取值范围由MD来决定。

    如果存储小数数据,除了可以选择FLOATDOUBLE类型外,还可选择DECDECIMAL类型,当要求小数数据精确度非常高时,则可选择DECDECIMAL类型,它们的精度比DOUBLE类型还要高。

     

    位类型

    l  BIT(M)字节是M,M的取值范围为1---8,该类型的存储空间是根据精度决定的。

     

    日期和时间类型

    l  表示年月日,一般使用DATE类型。(4字节)

    l  表示年月日时分秒,DATETIME类型。(8字节)

    l  需要经常插入或者更新日期为当前系统时间,TIMESTAMP类型。(4字节)

    l  时分秒,TIME类型。(3字节)

    l  年份,YEAR类型。因为该类型比DATE类型占用更少的空间。(1字节)

    注:要根据实际应用来选择满足需求的最小存储的日期类型。

    ü  如果只需要存储“年份”,则可以选择存储字节为1的YEAR类型。

    ü  如果要存储年月日时分秒,并且年份的取值可能比较久远,最好使用DATETIME类型而不是TIMESTAMP类型,因为前者比后者所表示的日期范围要长一些。

    ü  如果存储的日期需要让不同时区的用户使用,则可以使用TIMESTAMP类型,因为只有该类型日期能够跟实际时区相对应。

    字符串类型

    l  CHAR(M)

    l  VARCHAR(M)

    VARCHAR类型的长度是可变得,范围0---65535。

    如果需要存储少量字符串,则可以选择CHAR和VARCHAR类型,至于选择哪个?需要判断所存储字符串长度是否经常变换,如果经常变化则可以选择VARCHAR类型,否则选择CHAR类型。

     

    4.表的操作

    表是一种很重要的数据库对象,是组成数据库的基本元素,由若干个字段组成,主要用来实现存储数据记录。

    表中的数据库对象包含

    l  列(Column) 也叫属性列,创建表时,必须指定列的名字和数据类型。

    l  索引(Index) 指根据指定的数据库表列建立起来的顺序,提供快速访问数据的途径且可监督表的数据,使其索引所指向的列中的数据不重复。

    l  触发器(Triger)是指用户定义的事物命令的集合。

    查看表结构语句

    l  DESCRIBLE/DESC table_name;  查看表定义

    l  show create table table_name \G 查看表详细定义

    增加字段

    1.        在表的最后一个位置增加字段

    alter table table_name add 属性名 属性类型;

    2.        在表的第一个位置增加字段

    Alter table table_name add 属性名 属性类型 first

    3.        在表指定字段之后增加字段

    Alter table table_name add 属性名 属性类型 after 属性名;

    删除字段

    4.        Alter table table_name drop 属性名;

    修改字段

    5.        Alter table table_name modify 属性名数据类型;

    6.        修改字段的名字:alter table table_name change 旧属性名 新属性名 旧数据类型;

     

    7.        同时修改字段的名字和属性:alter table table_name change 旧属性名 新属性名 新数据类型;

    8.        修改字段的顺序:alter table table_name modify 属性名1 数据类型 first|after 属性名2;

    5.索引操作

    根据索引的存储类型,分为B型树索引(BTREE)和哈希索引(HASH)。

    注:InnoDB和MyISAM存储引擎支持BTREE类型索引,MEMORY存储引擎支持HASH类型索引,默认为前者索引。

    查看帮助文档,MySQL支持6种索引,普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引。

    一般下面情况适合创建索引:

    l  经常被查询的字段,即在where子句中出现的字段。

    l  在分组的字段,即在groupby子句中出现的字段。

    l  存在依赖关系的子表和父表之间的联合查询,即主键或外键字段。

    l  设置唯一完整性约束的字段。

    一般下面情况不适合创建索引:

    l  在查询中很少被使用的字段。

    l  拥有许多重复值的字段。

     

    创建索引

    1.      创建表时创建普通索引:

    Create table table_name(

    属性名  数据类型,

    …………

    普通索引:Index|key[索引名] (属性名1 (长度)   ASC|DESC)

    唯一索引:uniqueindex|key [索引名] (属性名1 (长度)   ASC|DESC)

    全文索引:fulltext index|key [索引名] (属性名1 (长度)   ASC|DESC) ;  engine=MyISAM

    );

    注:只能在存储引擎MyISAM的数据库表上创建全文索引,在默认情况下,全文索引的搜索执行方式为不区分大小写,如果全文索引所关联的字段为二进制数据类型,则以区分大小写的搜索方式执行。

     

    2.       在已存在的表上创建普通索引:

    Create index 索引名 on 表名属性名 (长度)  ASC|DESC

    3.      通过SQL语句ALTER TABLE创建普通索引:

    Alter table table_name add index|key 索引名 属性名(长度)  ASC|DESC

    注:在创建索引时,可以指定索引的长度。这是因为不同存储引擎定义了表的最大索引数和最大索引长度。MySQL所支持的存储引擎对每个表至少支持16个索引,总索引长度至少为256字节。

     

    查看索引

    l  查看表中的索引是否创建成功:Show create table table_name \G;

    l  查看表中索引是否被启用:explainselect * from table_name where 属性名=1;

     

     

    6.视图操作

    视图:本质上是一种虚拟表,其内容与真实表相似,包含一系列带有名称的列和行数据。但是,视图并不是在数据库中以存储的数据值形式存在。行和列数据来自定义视图的查询所引用基本表,并且在具体引用视图时动态生成。

     

    视图的特点:

    l  视图的列可以来自不同的表,是表的抽象和在逻辑意义上建立的新关系。

    l  视图是由基本表(实表)产生的表(虚表)。

    l  视图的建立和删除不影响基本表。

    l  对视图内容的更新(添加、删除和修改)直接影响基本表。

    l  当视图来自多个基本表时,不允许添加和删除数据。

    注:MySQL数据库管理系统从5.0.1版本开始提供视图新特性。

     

    创建视图

    Create viewview_name  as  查询语句;

     

    查看视图

    l  进入数据库view,查看该数据库里所有表名和视图名。

    Use view ;

    Show tables;

    l  查看视图详细信息

    Show table status [from db_name] [like ‘pattern’]

    Show table status from view \G    返回表示表和视图各种信息的各种字段。

    Show table status FROM view LIKE “view_name” \G  查看指定视图的详细信息。

    l  查看视图定义信息

    Show create view view_name

    l  查看视图设计信息

    DESCRIBE | DESC view_name

    l  通过系统表查看视图信息

    系统数据库information_schema 中存在一个包含视图信息的表格views,可以通过查看表格views来查看所有视图的相关信息。

    Use information_schema;

    Select * from views where table_name=’view_name’ \G

     

    修改视图

    l  Use view ;

    Create or replace view view_name as 查询语句; 此法先删除原视图在重新创建

    l  Alter 语句修改视图

    Alter view view_name as 查询语句

    7.触发器操作

    l  创建有一条执行语句的触发器

    Create triggertrigger_name

                BEFORE|AFTER trigger_EVENT

                   ON TABLE_NAME FOR EACHROW  trigger_STMT

    Trigger_EVENT 包括 insert、update、delete;

    l  创建包含多条执行语句的触发器

    Create  trigger  trigger_name

    BEFORE|AFTER   trigger_EVENT

         ON TABLE_NAME   FOR EACH ROW

              BEGIN

              Trigger_STMT

              END

    在关键字BEGIN和END之间为所要执行的多个执行语句的内容,语句之间用分号隔开。

     

    注:在MySQL软件中,一般情况下用“;”符号作为语句的结束符号,可是在创建触发器的时候,需要用到“;”符号作为执行语句的结束符号。为了解决该问题,可以使用关键字DELIMITER语句,例如“DELIMITER$$”,可以用来实现将结束符合设置成“$$”。

     

    DELIMITER $$

    CREATE TRIGGER  tri_diarytime2

        AFTER  INSERT

             ON  t_dept  FOR EACH ROW

                   BEGIN

                       INSER INTO t_diaryVALUES(null, ‘t_dept’,now());

                                INSERINTO t_diary VALUES(null, ‘t_dept’,now());

           END

           $$

    DELIMITER ;

     

    上述语句中首先通过“DELIMITER $$”语句设置结束符号为“$$”,然后在关键字BEGIN和END之间编写了执行语句列表,最后通过“DELIMITER;”语句将结束符号还原成默认结束符号“;”。

     

    查看触发器

    l  通过 SHOWTRIGGERS语句查看触发器

    Show triggers \G

    l  通过查看系统表triggers实现查看触发器

    于系统数据库information_schema中存在一个存储所有触发器信息的系统表triggers。

    USE information_schema;

    Select * from triggers \G  查询系统表triggers中的所有记录

    SELECT * FORM triggers WHERE  TRIGGER_NAME=’tri_diarytime2’  \G  查询具体触发器对象。

     

    9.     表查询数据记录

    注:在具体使用关键字IN时,查询的集合中如果存在NULL,则不会影响查询;如果使用关键字NOT IN,查询的集合中如果存在NULL,则不会有任何的查询结果。

     

    SELECT ename FROM  t_employee  WHERE empno  IN (1,2,3,4,NULL);  查询有结果集

     

    SELECT ename FROM  t_employee  WHERE empno  NOT  IN (1,2,3,4,NULL);没有查询结果集。

     

    LIKE 关键字支持的通配符如下:

    l  “_”通配符,该通配符值能匹配但个字符。

    l  “%”通配符,该通配符值可以匹配任意长度的字符串,即可以是0个字符,1个字符,也可以很多个字符。

    LIKEA%  和 LIKE a%   查询结果是一样的,MySQL软件不仅对于关键字不区分大小写,对于字段数据记录也不区分大小写。

    对于LIKE关键字,如果匹配“%%”则表示查询所有数据记录。

     

    排序数据记录查询

    MySQL软件中关键字ORDER BY 默认的排序顺序为升序。

    注:在MySQL软件中,如果字段的值为空值(NULL),则该值为最小值,因此在降序排序中将最后显示(即最后一行);在升序排序中则将最先显示(即第一行)。

     

    多字段排序

    具体运行过程中,首先按照第一个字段进行排序,如果遇到值相同的字段则会按照第二个字段进行排序,依次进行类推。

     

    限制数据记录查询数量

    SELECT field1  field2 fieldn   FROM table_name WHERE  CONDITION  LIMIT OFFSET_START, ROW_COUNT

    关键字LIMIT来限制数据查询结果数量,其中参数OFFSET_START表示数据记录的起始偏移量,参数ROW_COUNT表示显示的行数。

    对于MySQL软件提供的关键字LIMIT,如果不指定初始位置,默认值为0,表示从第一条记录开始显示。

     

     

    统计函数和分组数据记录查询

    l  COUNT(*)使用方式:对表中记录统计,不管表字段中包含的是NULL值还是非NULL值。

    l  COUNT(field)使用方式:指定字段的记录进行统计,统计时忽略NULL值,但不忽略值为0的数据记录。

    l  AVG(field)使用方式:平均值计算,忽略NULL值,但是没有忽略0数据记录。

    l  SUM(field)使用方式:计算指定字段值之和,忽略NULL值,不忽略值为0的记录。

    l  MAX(field)、MIN(field):忽略NULL值,不忽略值为0的记录。

    注:MySQL中统计函数,如果所操作的表中没有任何数据记录,则COUNT()函数会返回数据0,而AVG()、SUM()、MAX()、MIN()函数则会返回NULL。

    在具体进行分组查询时,分组所依据的字段上的值一定要具有重复值,否则将没有任何实际意义。

     

    分组数据查询----实现统计功能分组查询

    MySQL软件如果只实现简单的分组查询,是没有任何实际意义的。因为关键字GROUP BY单独使用时,默认查询出每个分组中随机一条记录,具有很大的不确定性。。分组关键字建议与统计函数一起使用。

    想显示分组中的字段,可以通过函数GROUP_CONCAT()来实现。

    SELECT GROUP_CONCAT(field)

     FROM  table_name 

          WHERE  CONDITION

          GROUP  BY field

     

    分组数据查询------实现HAVING字句限定分组查询

     

    SELECT function(field)

     FROM  table_name 

          WHERE  CONDITION

          GROUP  BY field1,field2,……..fieldn

          HAVING  CONDITION;  ----- (AVG(sal)>2000)

     

    多表数据记录查询

    l  UNION :查询结果集直接合并,并去掉重复数据记录。

    l  UNION ALL :查询结果集直接合并,没有去掉重复数据记录。

    为什么使用子查询

    例如:SELECT * FROM t_dept t, t_emp e WHERE t.deptno = e.deptno ;

    首先会对两个表进行笛卡儿积操作,然后在选取符合匹配条件的数据记录。进行笛卡儿积操作时,会生成连个数据表数据记录数的乘积条数据记录,如果这两张表的数据记录比较大,则在进行笛卡儿积操作时会造成死机。

    对于有经验的用户,首先会通过统计函数查看所操作表笛卡儿积后的数据记录数,然后才会进行多表查询。

     

     

    因此多表查询一般会经过如下步骤:

    ü  通过统计函数(COUNT())查询所关联表笛卡儿积后的数据记录数,具体SQL语句如下:

    SELECT COUNT(*) FROM t_dept , t_emp  ;

    ü  如果查询到的数据记录数MySQL软件可以接受,然后才进行多表连接查询,否则就应该考虑通过其他方式来实现。

    为了解决查询到笛卡儿积后的数据记录数远远大于MySQL软件可接受的范围,MySQL提供了子查询来实现多表查询。

    所谓子查询,就是在一个查询中嵌套了其他的若干查询,即在一个SELECT查询语句的WHERE或FROM子句中包含另一个SELECT查询语句。理论上子查询可以出现在查询语句的任意位置,但实际开发中,子查询经常出现在WHERE和FROM子句中。

    l  WHERE子句中的子查询:该位置处的子查询一般返回单行单列、多行单列、单行多列数据记录。

    l  FROM子句中的子查询:该位置处的子查询一般返回多行多列数据记录,可以当做一张临时表。

    注:此处关于笛卡儿积、多表操作等不多介绍了,自己找找相关资料吧。

     

    10.             MySQL运算符

    l  算术运算符;

    加(+)、减(-)、乘(*)、除(/ (DIV))、除(% (MOD));

    注:所有的算术运算符都可以同时运算多个操作数,但是除运算符(/和DIV)和求模运算符(%和MOD)的操作数最好是两个。在MySQL中对于除和模操作,如果除数为0将是非法运算,返回结果为NULL。·

    l  比较运算符;

    1.       等于:=(< = >):判断数值、字符串和表达式等是否相等。如果相等则返回1;否则返回0。依据字符的ASCII码来进行判断。= 不能操作NULL值,< = > 可以操作NULL。  SELECT  NULL < = > NULL , NULL = NULL ; 结果返回 1 、NULL。

    2.       不等于:!=(<>):判断不相等,如果不相等则返回1,否则返回0。这两个比较运算符不能操作NULL(空值)。

    3.       “>”、“>=”、“<”、“<=”比较运算符主要判断数值、字符串和表达式等的相关比较,如果表达式成立则返回1,否则将返回0。不能操作NULL。

    4.       实现特殊功能比较运算符:MySQL支持的模式字符

    ^ :匹配字符串的开始部分。

    $ :匹配字符串的结束部分。

    . :匹配字符串中的任意一个字符。

    * :匹配字符,包含0个和1个。

    + :匹配字符,包含1个。

    [字符集合] :匹配字符集合中的任意一个字符。

    [^字符集合] :匹配字符集合外的任意一个字符。

    字符串{N} :字符串出现N次。

    字符串{M,N}:字符串出现至少M次,最多N次。

    l  逻辑运算符;

    AND(&&):与;

    OR(||):或;

    NOT(!):非;

    XOR:亦或;

    l  位运算符;

    &:按位与;

    |:按位或;

    ~:按位取反;

    ^:按位亦或;

    <<:按位左移;

    >>:按位右移;

    11.             MySQL常用函数

    l  字符串函数:处理字符串;

    l  数值函数:处理数字;

    l  日期函数:处理日期和时间;

    EXTRACT();获取指定值的函数:EXTRACT(type  FROM date)

    SELECT NOW() 当前日期和时间,

    EXTRACT(YEAR  FROM NOW()) 年,

    EXTRACT(MONTH  FROM NOW()) 月,

    EXTRACT(DAY  FROM NOW()) 日,

    EXTRACT(HOUR  FORM NOW())小时 ,

    EXTRACT(MINUTE  FROM NOW()) 分,

    EXTRACT(SECOND  FROM NOW())秒 ;

    l  系统信息函数:获取MySQL软件的系统信息;

    SELECT  VERSION() 版本号,  DATABASE() 数据库名,  USER () 用户名;

     

    注:此章节函数很多,就不一一列举说明了,也不难自己遇到了查下资料就好了。

     

    12.             存储过程和函数的操作

    存储过程和函数可以简单的理解为一条或多条SQL语句的集合,查看帮助文档可以发现,存储过程和函数就是事件经过编译并存储在数据库中的一段SQL语句集合。

    存储过程和函数的执行不是由程序调用,也不是由手工启动,而是由事件来触发、激活从而实现执行。而存储过程和函数的执行,则需要手工调用存储过程和函数的名字并需要制定相应的参数。

    存储过程和函数的区别:函数必须有返回值,而存储过程则没有。存储过程的参数类型远远多于函数参数类型。

     

     

    存储过程和函数优点:

    l  允许标准组件式编程,提高了SQL语句的重用性、共享性和可移植性。

    l  能够实现较快的执行速度,能够减少网络流量。

    l  可以被作为一种安全机制来利用。

    缺陷:

    l  编写比单句SQL语句复杂,需要用户具有更高的技能和更丰富的经验。

    l  需要创建这些数据库对象的权限。

     

    创建存储过程和函数

    l  存储过程:CREATE  PROCEDURE procedure_name ([procedure_paramter[,…..]])

    [characteristic…]  routine_body

    l  函数:CREATE  FUNCTION function_name ([function_paramter[,…..]])

    [characteristic…]  routine_body

     

    Characteristic 存储过程或函数的特性,routine_body参数表示存储过程或函数的SQL语句代码,可以用BEGIN…END来标志SQL语句的开始和结束。

     

    创建存储过程示例:

    DELIMITER $$

    CREATE PROCEDURE   proce_employee_sal()

    COMMENT ‘查询所有雇员的工资’

    BEGIN

    SELECT  sal

    FROM  t_employee;

    END $$

    DELIMITER ;

     

    创建函数示例:

    DELIMITER $$

    CREATE  FUNCTION   func_employee_sal(empnoINT(11))

    RETURNS DOUBLE(10,2)

    COMMENT ‘查询所有雇员的工资’

    BEGIN

             RETURN (SELECT  sal

    FROM  t_employee

    WHEREt_employee.empno=empno);

    END $$

    DELIMITER ;

     

    注:关于存储过程和函数的表达式在这里就不介绍了。

     

    使用光标(游标)

     MySQL软件的查询语句可以返回多条记录结果,那么在表达式中如何遍历这些记录结果呢?MySQL软件提供了光标(游标)来实现。

    l  声明光标

    DECLARE  cursor_nameCURSOR FOR select_statement  ;

    l  打开光标

    OPEN cursor_name 

    l  使用光标

    FETCH cursor_name   INTO  var_name  [,var_name] …

    l  关闭光标

    CLOSE   cursor_name

     

    示例:

    1.      声明:DECLARE  cursor_employee  CURSOR  FOR  SELECT sal  FROM  t_employee;

    2.      打开:OPEN  cursor_employee ;

    3.      执行:FETCH  cursor_employee  INTO  employee_sal  ;

    4.      关闭:CLOSE  cursor_employee ;

     

    查询存储过程和函数(三种方式)

    l  存储过程状态信息:SHOW  PROCEDURE STATUS  [ LIKE ‘proce_employee_sal]  \G

    函数状态信息:SHOW  FUNCTION  STATUS [ LIKE ‘proce_employee_sal]  \G

    l  查看系统表information_schema.routines  详细信息

    USE information_schema ;

    SELECT  * FROM  routines \G

    SELECT  * FROM ROUTINES  WHERE  SPECIFIC_NAME = ‘proce_employee_sal ’ \G

    l  SHOW  CREATE  PROCEDURE 查看定义信息

    存储过程:SHOW  CREATE  PROCEDURE proce_name \G

    函数:SHOW  CREATE  FUNCTION func_name \G

    修改

    ALTER关键字

    删除

      DROP关键字

    来自自己的qq空间,转到CSDN

    展开全文
  • 有一个表user,字段分别有id、nick_name、password、email、phone。 一、单字段(nick_name) 查出所有有重复记录的所有记录 ...select * from user where id in (select max(id) from user group by nick_name having
  • Canal+Kafka实现mysql与Redis数据同步

    千次阅读 2020-12-13 13:27:12
    Canal+Kaka实现mysql与Redis数据同步 一、Canal简介 canal主要用途是基于 MySQL 数据库增量日志解析,提供增量数据订阅和消费,早期阿里巴巴因为杭州和美国双机房部署,存在跨机房同步的业务需求,实现方式主要是...
  • mysql max(if) 将MAX IF与多个条件一起使用 (Using MAX IF With Multiple Criteria) Excel doesn’t have a MAXIF function, but we’re able to create our own version, by combining the MAX and IF functions....
  • 总表 统计函数 NULL是否影响SUM AVG的计算? MAX MIN 用在日期时间上会怎么样 这些函数用在字符串上会怎么样 结合上节文章案例 统计一波
  • Mysql数据库读写分离的实现

    千次阅读 2020-05-16 16:45:08
    Mysql中可以实现读写分离的插件有mysql-proxy / Mycat / Amoeba ,mysql-proxy是系统自带的一个插件,此次实验主要用它来实现读写分离 mysql-proxy是实现"读写分离(Read/Write Splitting)"的一个软件(MySQL官方提供...
  • mysqlmax_connection的设置及优化

    千次阅读 2017-11-27 17:30:00
    要通过修改 win2000的c:/winnt/my.ini 或者 win2003的 c:/windows/my.ini 来实现首先,我们打开这个文件,先将最底部的密码项改成其它的,这个对数据库本身没影响,主要是防止一些有心人获取这个密码,对我们不利#...
  • MySQL事务底层实现原理

    千次阅读 2019-10-23 16:37:36
    事务特性 事务特性分为: ...也是在事务并发时实现一致性的一个前提,可以设置4种隔离级别。级别越高一致性越强,但并发性越低; 1.读未提交 会读到其他事务未提交的数据,产生脏读 2.读已提交 解...
  • 自己电脑上装的WAMP,在导入数据库比较大(大于2M)时遇到错误,不能导入。... 您可能感兴趣的文章:如何修改Mysql中group_concat的长度限制MySQL连接数超过限制的解决方法MySQL 数据库对服务器端光标的限制在MySQL中修
  • Java实现Mysql数据同步

    千次阅读 2018-11-09 17:01:27
    本篇博客介绍的是Java程序实现Mysql数据同步,要对抽象类有深刻的理解,不然会对代码逻辑很懵懂,不懂得同学可以看我这篇博客回忆一下Java基础知识: Java抽象类 编写同步数据逻辑抽象类代码: 根据主键id...
  • MySQL中表无唯一递增字段,也无唯一递增时间字段,该怎么使用logstash实现MySQL实时增量导数据到es中? logstash和kafka_connector都仅支持基于自增id或者时间戳更新的方式增量同步数据。 回到问题本身:如果库...
  • MySQL 如何实现递归查询?

    万次阅读 多人点赞 2020-09-09 11:38:00
    文章主要知识点: Oracle 递归查询, start with connect by prior 用法 find_in_set 函数 concat,concat_ws,group_concat 函数 MySQL 自定义函数 手动实现 MySQL 递归查询 Oracle 递归查询 在 Oracle 中是通过 ...
  • mysql实现sequence功能 1.建立sequence记录表 CREATE TABLE `sys_sequence` ( `seq_name` varchar(50) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `min_value` int(11) NOT NULL, `max_value` int(11)...
  • MySQL优化技巧

    万次阅读 多人点赞 2017-09-10 14:14:00
    MySQL优化三大方向① 优化MySQL所在服务器内核(此优化一般由运维人员完成)...MySQL参数优化1:MySQL 默认的最大连接数为 100,可以在 mysql 客户端使用以下命令查看mysql> show variables like 'max_connections';2:查看
  • test password: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 200 slave_ds_0: url: jdbc:mysql://***.***.***.***:****/db_seq?...
  • 实现MySQL主从复制,nginx读写分离

    千次阅读 2019-04-05 11:26:16
    说明:我的nginx装在Windows上,实现反向代理,读写分离。在两台centos7主机上安装MySQL5.7,部署相同的项目。使用MySQL自带的主从复制。 测试环境:Windows上配置nginx做nginx服务器 一台centos7做主从复制的主...
  • mysql全量备份、增量备份。开启mysql的logbin日志功能。在/etc/my.cnf文件中加入以下代码: [mysqld] log-bin = /home/mysql/logbin.log binlog-format = ROW log-bin-index = /home/mysql/logindex binlog_cache_...
  • C 语言实现MySQL连接池

    千次阅读 热门讨论 2017-12-11 16:29:24
    该图为一个简单的连接池实现模型,我们将以该图来实现一个最基础的连接池,该数据库采用MySQL 一、数据库连接池的抽象结构体(圆圈1) # define IP_LEN 15 # define DBNAME_LEN 64 # define DBUSER_...
  • canal实现mysql实时数据binlog同步

    万次阅读 2019-01-09 17:50:11
    canal模拟mysql slave的交互协议,伪装自己为mysql slave,向mysql master发送dump协议 mysql master收到dump请求,开始推送binary log给slave(也就是canal) canal解析binary log对象(原始为byte流) 基本说明 ...
  • MySQL max_execution_time参数原理解析

    万次阅读 2019-02-27 20:17:29
    MySQL max_execution_time参数解析 一、参数解释说明 参数max_execution_time用来控制select语句的最大执行时间,单位是毫秒,可以动态修改,分为session和global两种级别,如果设置为0的话,则证明不设限制。 二、...
  • Key TakeAways InnoDB 引擎中 有三种 AutoIncrement 锁模式: innodb_autoinc_lock_mode=0(traditional lock mode):获取表锁,语句...innodb_autoinc_lock_mode=1(consecutive lock mode,MySQL 8.0 之前默认.
  • 使用MHA实现MySQL主从复制高可用

    万次阅读 多人点赞 2018-07-31 16:37:10
    innobackupex --user root --password 123456 --defaults-file=/home/mysql/mysql-5.6.14/my.cnf --no-lock --socket=/home/mysql/mysql-5.6.14/mysql.sock --port 3306 --stream=tar ./ | ssh mysql@172.16.1.126 \...
  • canal_mysql_elasticsearch_sync 支持请star:sparkles: 基于 canal 的 Mysql 与 Elasticsearch 实时同步的 javaweb 服务。 canal是阿里巴巴mysql数据库binlog的增量订阅&消费组件。 工作原理 全量 暴露Http接口...
  • MySQL 5.1.30 数据库连接池:druid Maven 3.5.9 项目pom文件: &lt;?xml version="1.0" encoding="UTF-8"?&gt; &lt;project xmlns="http://maven.apache.or...
  • MySQL实现split功能

    千次阅读 2018-08-09 14:22:46
    在特定的情况下我们可能也需要在MySQL查询中实现这样的功能, 而MySQL本身是不支持的,但我们可以通过下面的方法间接完成 MySQL实现split功能 假如我们有一张员工表 id name hobby 1 ...
  • 一.部署前需要准备的东西 jdk:1.8.0_181 confluent:5.4.1 debezium:1.1.0.Final 修改数据库配置文件my.cnf 或 my.inf ... log-bin=mysql-bin #添加这一行就ok binlog-format=ROW #选择row模式 binlog_r...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 139,811
精华内容 55,924
热门标签
关键字:

max实现mysql

mysql 订阅