mysql基础知识

 三大范式

  • 1NF：原子性 字段不可再分,否则就不是关系数据库
  • 2NF：唯一性 一个表只说明一个事物
  • 3NF：每列都与主键有直接关系，不存在传递依赖

范式化设计优缺点:

优点:

  • 可以尽量得减少数据冗余，使得更新快，体积小
  • 缺点:对于查询需要多个表进行关联，减少写得效率增加读得效率，更难进行索引优化

反范式化:

  • 优点:可以减少表得关联，可以更好得进行索引优化
  • 缺点:数据冗余以及数据异常，数据得修改需要更多的成本

 

什么是事务？及其特性？

事务：是一系列的数据库操作，是数据库应用的基本逻辑单位。

事务特性：

  • 原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。
  • 一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确状态
  • 隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，
  • 持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

或者这样理解：

事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。

 

事务隔离级别

  • 脏读：指一个事务读取了另外一个事务未提交的数据。
  • 可重复读： 可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据**更新（UPDATE）**操作。
  • 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。
  • 幻读：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

隔离级别：

  • 读未提交（READ UNCOMMITTED）
  • 读提交 （READ COMMITTED）
  • 可重复读 （REPEATABLE READ）
  • 串行化（SERIALIZABLE）
  • 从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是MySQL 的默认级别。

 

 

 

 

 

什么叫视图？游标是什么？


答：视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

  游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

 

什么是存储过程？用什么来调用？


答：存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。

 

什么是锁？


  答：数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

基本锁类型：锁包括行级锁和表级锁

 

mysql 的行锁和表锁

 

 

 

锁的优化策略

    • 读写分离
    • 分段加锁
    • 减少锁持有的时间
    • 多个线程尽量以相同的顺序去获取资源

 

 

什么是索引？

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据 库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数 据结构上实现高级查找算法，这种数据结构就是索引,总结，索引就是一种高效的数据结构。

 

Mysql的索引？

  • BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
  • HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
  • R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常 使用较少，不做特别介绍。
  • Full-text（全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从 Mysql5.6版本开始支持全文索引。

 

BTREE 索引

  • 树中每个节点最多包含m个孩子（m个叶子节点）。
  • 除根节点与叶子节点外，每个节点至少有[向上取值(m/2)]个孩子节点（叶子节点）。
  • 若根节点不是叶子节点，则至少有两个孩子。
  • 所有的叶子节点都在同一层。 每个非叶子节点由n个key与n+1个指针域组成，其中[向上取值(m/2)-1] <= n <= m-1
  • 每一次超过N则向上分裂

B+Tree为BTree的变种，B+Tree与BTree的区别为：

  • n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。
  • B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。
  • 所有的非叶子节点都可以看作是key的索引部分。
  • MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指 针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

 

对于关系型数据库而言，索引是相当重要的概念，请回答有关索引的几个问题：

a)、索引的目的是什么？

  • 快速访问数据表中的特定信息，提高检索速度
  • 创建唯一性索引，保证数据库表中每一行数据的唯一性。
  • 加速表和表之间的连接
  • 使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

b)、索引对数据库系统的负面影响是什么？

  • 创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；
  • 索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；
  • 当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

c)、为数据表建立索引的原则有哪些？

  • 在最频繁使用的、用以缩小查询范围的字段上建立索引。
  • 在频繁使用的、需要排序的字段上建立索引

d)、 什么情况下不宜建立索引？

  • 对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。
  • 对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等

 

数据库引擎有哪些？

  • InnoDB：存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。 但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。
  • MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表 。
  • Memory存储引擎将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件，格式是.frm ，该文件中只 存储表的结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。MEMORY 类型的表访问非常地快，因为他的数据是存放在内存中的，并且默认使用HASH索引 ， 但是服务一旦关闭，表中的 数据就会丢失。
  • MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有存储数据，对 MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的

 

Mysql 的存储引擎,myisam和innodb的区别。

  • MyISAM 是非事务的存储引擎，适合用于频繁查询的应用。表锁，不会出现死锁，适合小数据，小并发。
  • innodb是支持事务的存储引擎，合于插入和更新操作比较多的应用，设计合理的话是行锁（最大区别就在锁的级别上），适合大数据，大并发。

 

数据库优化有哪些手段？

  • 使用 explain 命令查询 SQL语句执行计划。
  • 查看sql频率，通过show [session|global] status 命令可以提供服务器状态信息
  • show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否 锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。
  • 开启慢日志记录
  • show profile分析SQL
  • trace分析优化器执行计划

 

主键、外键和索引的区别？

主键、外键和索引的区别

定义：

  •  主键–唯一标识一条记录，不能有重复的，不允许为空
  •  外键–表的外键是另一表的主键, 外键可以有重复的, 可以是空值
  •  索引–该字段没有重复值，但可以有一个空值

作用：

  •  主键–用来保证数据完整性
  •  外键–用来和其他表建立联系用的
  •  索引–是提高查询排序的速度

个数：

  •  主键–主键只能有一个
  •  外键–一个表可以有多个外键
  •  索引–一个表可以有多个唯一索引

 

数据库中的事务是什么?

事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。ACID 四大特性,原子性、隔离性、一致性、持久性。

 

数据表类型有哪些

       答：MyISAM、InnoDB、HEAP、BOB,ARCHIVE,CSV等。
       MyISAM：成熟、稳定、易于管理，快速读取。一些功能不支持（事务等），表级锁。
       InnoDB：支持事务、外键等特性、数据行锁定。空间占用大，不支持全文索引等。

 

MySQL数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？

  • 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
  • 选择合适的表字段数据类型和存储引擎，适当的添加索引。
  •  mysql库主从读写分离。
  • 找规律分表，减少单表中的数据量提高查询速度。
  • 添加缓存机制，比如memcached，apc等。
  • 不经常改动的页面，生成静态页面。
  • 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.

 

对于大流量的网站,您采用什么样的方法来解决各页面访问量统计问题？

  • 确认服务器是否能支撑当前访问量。
  • 优化数据库访问。
  • 禁止外部访问链接（盗链）, 比如图片盗链。
  • 控制文件下载。
  • 使用不同主机分流。
  • 使用浏览统计软件，了解访问量，有针对性的进行优化。

 

如何进行SQL优化？

（1）选择正确的存储引擎

以 MySQL为例，包括有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。
MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。但是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

 

（2）优化字段的数据类型

记住一个原则，越小的列会越快。如果一个表只会有几列罢了（比如说字典表，配置表），那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多。当然，你也需要留够足够的扩展空间。

 

（3）为搜索字段添加索引

索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么最好是为其建立索引，除非你要搜索的字段是大的文本字段，那应该建立全文索引。

 

(4)避免使用Select *从数据库里读出越多的数据，那么查询就会变得越慢。

并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。即使你要查询数据表的所有字段，也尽量不要用*通配符，善用内置提供的字段排除定义也许能给带来更多的便利。

 

(5)使用 ENUM 而不是 VARCHAR

ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。例如，性别、民族、部门和状态之类的这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

 

(6)尽可能的使用 NOT NULL

除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。 NULL其实需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。 当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。

 

(7)固定长度的表会更快

如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。

 

固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。

 

并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。

 

如何设计一个高并发的系统

  • 数据库的优化，包括合理的事务隔离级别、SQL语句优化、索引的优化
  • 使用缓存，尽量减少数据库 IO
  • 分布式数据库、分布式缓存
  • 服务器的负载均衡

 

 

Myql中的事务回滚机制概述

事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位，事务回滚是指将该事务已经完成的对数据库的更新操作撤销。

要同时修改数据库中两个不同表时，如果它们不是一个事务的话，当第一个表修改完，可能第二个表修改过程中出现了异常而没能修改，此时就只有第二个表依旧是未修改之前的状态，而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候，当第一个表修改完，第二表修改出现异常而没能修改，第一个表和第二个表都要回到未修改的状态，这就是所谓的事务回滚

 



posted @ 2021-06-05 14:31  flyacome  阅读(83)  评论(0)    收藏  举报