精华内容
下载资源
问答
  • MySQL 面试题

    万次阅读 多人点赞 2019-09-02 16:03:33
    实际场景下,例如说商品表数据量比较的情况下,会将商品描述单独存储到一个表中。即,使用拆的方案。 MySQL 有哪些存储引擎? MySQL 提供了多种的存储引擎: InnoDB MyISAM MRG_MYISAM MEMORY CSV...

    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》 文章。

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

    展开全文
  • 大数据量下高并发同步的解决方案

    万次阅读 2016-07-04 11:52:32
     对于我们开发的网站,如果网站的访问量非常的话,那么我们就需要考虑相关的并发访问问题了。而并发问题是绝部分的程序员头疼的问题, 但话又说回来了,既然逃避不掉,那我们就坦然面对吧~今天就让我们一起来...

    大数据量下高并发同步的讲解(不看,保证你后悔)

     

       对于我们开发的网站,如果网站的访问量非常大的话,那么我们就需要考虑相关的并发访问问题了。而并发问题是绝大部分的程序员头疼的问题,

    但话又说回来了,既然逃避不掉,那我们就坦然面对吧~今天就让我们一起来研究一下常见的并发和同步吧。

       为了更好的理解并发和同步,我们需要先明白两个重要的概念:同步和异步

       1、同步和异步的区别和联系

             所谓同步,可以理解为在执行完一个函数或方法之后,一直等待系统返回值或消息,这时程序是出于阻塞的,只有接收到

            返回的值或消息后才往下执行其它的命令。

            异步,执行完函数或方法后,不必阻塞性地等待返回值或消息,只需要向系统委托一个异步过程,那么当系统接收到返回

            值或消息时,系统会自动触发委托的异步过程,从而完成一个完整的流程。

             同步在一定程度上可以看做是单线程,这个线程请求一个方法后就待这个方法给他回复,否则他不往下执行(死心眼)。

            异步在一定程度上可以看做是多线程的(废话,一个线程怎么叫异步),请求一个方法后,就不管了,继续执行其他的方法。

            

            同步就是一件事,一件事情一件事的做。
            异步就是,做一件事情,不引响做其他事情。

            例如:吃饭和说话,只能一件事一件事的来,因为只有一张嘴。
                    但吃饭和听音乐是异步的,因为,听音乐并不引响我们吃饭。

     

            对于Java程序员而言,我们会经常听到同步关键字synchronized,假如这个同步的监视对象是类的话,那么如果当一个对象

            访问类里面的同步方法的话,那么其它的对象如果想要继续访问类里面的这个同步方法的话,就会进入阻塞,只有等前一个对象

            执行完该同步方法后当前对象才能够继续执行该方法。这就是同步。相反,如果方法前没有同步关键字修饰的话,那么不同的对象

            可以在同一时间访问同一个方法,这就是异步。

          

            在补充一下(脏数据和不可重复读的相关概念):

           脏数据

       脏读就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这
    个数据。因为这个数据是还没有提交的数据,那么另外一个事务读到的这个数据是脏数据(Dirty Data),依据脏数据所做的操作可能是不正确的。

         不可重复读

       不可重复读是指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的,因此称为是不可重复读

     

             

     

       2、如何处理并发和同步

            今天讲的如何处理并发和同同步问题主要是通过锁机制。

           我们需要明白,锁机制有两个层面。

           一种是代码层次上的,如java中的同步锁,典型的就是同步关键字synchronized,这里我不在做过多的讲解,

           感兴趣的可以参考:http://www.cnblogs.com/xiohao/p/4151408.html

           另外一种是数据库层次上的,比较典型的就是悲观锁和乐观锁。这里我们重点讲解的就是悲观锁(传统的物理锁)和乐观锁。

           悲观锁(Pessimistic Locking):       

           悲观锁,正如其名,它指的是对数据被外界(包括本系统当前的其他事务,以及来自 外部系统的事务处理)修改持保守态度,因此,

           在整个数据处理过程中,将数据处于锁定状态。

           悲观锁的实现,往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能 真正保证数据访问的排他性,否则,即使在本系统

           中实现了加锁机制,也无法保证外部系 统不会修改数据)。 

           一个典型的倚赖数据库的悲观锁调用: 

           select * from account where name=”Erica” for update

           这条 sql 语句锁定了 account 表中所有符合检索条件( name=”Erica” )的记录。

           本次事务提交之前(事务提交时会释放事务过程中的锁),外界无法修改这些记录。 
           Hibernate 的悲观锁,也是基于数据库的锁机制实现。 
           下面的代码实现了对查询记录的加锁:

           String hqlStr ="from TUser as user where user.name='Erica'";

            Query query = session.createQuery(hqlStr);

            query.setLockMode("user",LockMode.UPGRADE); // 加锁

           List userList = query.list();// 执行查询,获取数据

           query.setLockMode 对查询语句中,特定别名所对应的记录进行加锁(我们为 TUser 类指定了一个别名 “user” ),这里也就是对

          返回的所有 user 记录进行加锁。 

          观察运行期 Hibernate 生成的 SQL 语句: 
          select tuser0_.id as id, tuser0_.name as name, tuser0_.group_id
          as group_id, tuser0_.user_type as user_type, tuser0_.sex as sex
          from t_user tuser0_ where (tuser0_.name='Erica' ) for update
         这里 Hibernate 通过使用数据库的 for update 子句实现了悲观锁机制。 
          Hibernate 的加锁模式有: 
          Ø LockMode.NONE : 无锁机制。 
          Ø LockMode.WRITE : Hibernate 在 Insert 和 Update 记录的时候会自动获取
          Ø LockMode.READ : Hibernate 在读取记录的时候会自动获取。 
          以上这三种锁机制一般由 Hibernate 内部使用,如 Hibernate 为了保证 Update
          过程中对象不会被外界修改,会在 save 方法实现中自动为目标对象加上 WRITE 锁。 
          Ø LockMode.UPGRADE :利用数据库的 for update 子句加锁。 
          Ø LockMode. UPGRADE_NOWAIT : Oracle 的特定实现,利用 Oracle 的 for
          update nowait 子句实现加锁。 
          上面这两种锁机制是我们在应用层较为常用的,加锁一般通过以下方法实现: 
          Criteria.setLockMode
          Query.setLockMode
          Session.lock
          注意,只有在查询开始之前(也就是 Hiberate 生成 SQL 之前)设定加锁,才会 
          真正通过数据库的锁机制进行加锁处理,否则,数据已经通过不包含 for update
          子句的 Select SQL 加载进来,所谓数据库加锁也就无从谈起。

          为了更好的理解select... for update的锁表的过程,本人将要以mysql为例,进行相应的讲解

          1、要测试锁定的状况,可以利用MySQL的Command Mode ,开二个视窗来做测试。

              表的基本结构如下:

              

     

               表中内容如下:

               

     

              开启两个测试窗口,在其中一个窗口执行select * from ta for update0

              然后在另外一个窗口执行update操作如下图:

              

              等到一个窗口commit后的图片如下:

              

               到这里,悲观锁机制你应该了解一些了吧~

           

               需要注意的是for update要放到mysql的事务中,即begin和commit中,否者不起作用。

               至于是锁住整个表还是锁住选中的行,请参考:

               http://www.cnblogs.com/xiohao/p/4385768.html

                至于hibernate中的悲观锁使用起来比较简单,这里就不写demo了~感兴趣的自己查一下就ok了~

               

              乐观锁(Optimistic Locking):        
             相对悲观锁而言,乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依 靠数据库的锁机制实现,以保证操作最大程度的独占性。但随之

    而来的就是数据库 性能的大量开销,特别是对长事务而言,这样的开销往往无法承受。 如一个金融系统,当某个操作员读取用户的数据,并在读出的用户数

    据的基础上进 行修改时(如更改用户帐户余额),如果采用悲观锁机制,也就意味着整个操作过 程中(从操作员读出数据、开始修改直至提交修改结果的全

    过程,甚至还包括操作 员中途去煮咖啡的时间),数据库记录始终处于加锁状态,可以想见,如果面对几 百上千个并发,这样的情况将导致怎样的后果。 

    观锁机制在一定程度上解决了这个问题。

             乐观锁,大多是基于数据版本   Version )记录机制实现。何谓数据版本?即为数据增加一个版本标识,在基于据库表的版本解决方案中,一般是通

    过为数据库表增加一个 “version” 字段来 实现。 读取出数据时,将此版本号一同读出,之后更新时,对此版本号加一。此时,将提 交数据的版本数据与数据

    库表对应记录的当前版本信息进行比对,如果提交的数据 版本号大于数据库表当前版本号,则予以更新,否则认为是过期数据。 对于上面修改用户帐户信息

    的例子而言,假设数据库中帐户信息表中有一个 version 字段,当前值为 1 ;而当前帐户余额字段( balance )为 $100 。 操作员 A 此时将其读出

    ( version=1 ),并从其帐户余额中扣除 $50( $100-$50 )。 2 在操作员 A 操作的过程中,操作员 B 也读入此用户信息( version=1 ),并 从其帐

    户余额中扣除 $20 ( $100-$20 )。 3 操作员 A 完成了修改工作,将数据版本号加一( version=2 ),连同帐户扣 除后余额( balance=$50 ),提交

    至数据库更新,此时由于提交数据版本大 于数据库记录当前版本,数据被更新,数据库记录 version 更新为 2 。 4 操作员 B 完成了操作,也将版本号加一

    ( version=2 )试图向数据库提交数 据( balance=$80 ),但此时比对数据库记录版本时发现,操作员 B 提交的 数据版本号为 2 ,数据库记录当前版

    本也为 2 ,不满足 “ 提交版本必须大于记 录当前版本才能执行更新 “ 的乐观锁策略,因此,操作员 B 的提交被驳回。 这样,就避免了操作员 B 用基于

    version=1 的旧数据修改的结果覆盖操作 员 A 的操作结果的可能。 从上面的例子可以看出,乐观锁机制避免了长事务中的数据库加锁开销(操作员 A


    和操作员 B 操作过程中,都没有对数据库数据加锁),大大提升了大并发量下的系 统整体性能表现。 需要注意的是,乐观锁机制往往基于系统中的数据存储

    逻辑,因此也具备一定的局 限性,如在上例中,由于乐观锁机制是在我们的系统中实现,来自外部系统的用户 余额更新操作不受我们系统的控制,因此可能

    会造成脏数据被更新到数据库中。在 系统设计阶段,我们应该充分考虑到这些情况出现的可能性,并进行相应调整(如 将乐观锁策略在数据库存储过程中实

    现,对外只开放基于此存储过程的数据更新途 径,而不是将数据库表直接对外公开)。 Hibernate 在其数据访问引擎中内置了乐观锁实现。如果不用考虑外

    部系统对数 据库的更新操作,利用 Hibernate 提供的透明化乐观锁实现,将大大提升我们的 生产力。


    Hibernate 中可以通过 class 描述符的 optimistic-lock 属性结合 version描述符指定。

    现在,我们为之前示例中的 User 加上乐观锁机制。

     

    1 . 首先为 User 的POJO class

           

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    package  com.xiaohao.test;
     
    public  class  User {
        private  Integer id;
        private  String userName;
        private  String password;
        private  int  version;
        
    public  int  getVersion() {
         return  version;
    }
     
    public  void  setVersion( int  version) {
         this .version = version;
    }
     
    public  Integer getId() {
         return  id;
    }
     
    public  void  setId(Integer id) {
         this .id = id;
    }
     
    public  String getUserName() {
         return  userName;
    }
     
    public  void  setUserName(String userName) {
         this .userName = userName;
    }
     
    public  String getPassword() {
         return  password;
    }
     
    public  void  setPassword(String password) {
         this .password = password;
    }
     
     
     
    public  User() {}
     
    public  User(String userName, String password) {
         super ();
         this .userName = userName;
         this .password = password;
    }
     
     
        
        
    }

      


            然后是User.hbm.xml

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    <?xml version= "1.0" ?>
    <!DOCTYPE hibernate-mapping PUBLIC
             "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
     
    <hibernate-mapping  package = "com.xiaohao.test" >
     
         < class  name= "User"   table= "user"  optimistic-lock= "version"  >
                   <id name= "id" >
                 <generator  class = "native"  />
             </id>
             <!--version标签必须跟在id标签后面-->
             <version column= "version"  name= "version"   />
             <property name= "userName" />
             <property name= "password" />
                     
         </ class >
         
     
    </hibernate-mapping>

    注意 version 节点必须出现在 ID 节点之后。 
    这里我们声明了一个 version 属性,用于存放用户的版本信息,保存在 User 表的version中 

    optimistic-lock 属性有如下可选取值: 
    Ø none
    无乐观锁 
    Ø version
    通过版本机制实现乐观锁 
    Ø dirty
    通过检查发生变动过的属性实现乐观锁 
    Ø all
    通过检查所有属性实现乐观锁 
    其中通过 version 实现的乐观锁机制是 Hibernate 官方推荐的乐观锁实现,同时也 
    是 Hibernate 中,目前唯一在数据对象脱离 Session 发生修改的情况下依然有效的锁机 
    制。因此,一般情况下,我们都选择 version 方式作为 Hibernate 乐观锁实现机制。


    2 . 配置文件hibernate.cfg.xml和UserTest测试类

       hibernate.cfg.xml

      

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    <!DOCTYPE hibernate-configuration PUBLIC
             "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
     
    <hibernate-configuration>
    <session-factory>
     
         <!-- 指定数据库方言 如果使用jbpm的话,数据库方言只能是InnoDB-->
         <property name= "dialect" >org.hibernate.dialect.MySQL5InnoDBDialect</property>
         <!-- 根据需要自动创建数据表 -->
         <property name= "hbm2ddl.auto" >update</property>
         <!-- 显示Hibernate持久化操作所生成的SQL -->
         <property name= "show_sql" > true </property>
         <!-- 将SQL脚本进行格式化后再输出 -->
         <property name= "format_sql" > false </property>
         <property name= "current_session_context_class" >thread</property>
     
     
         <!-- 导入映射配置 -->
         <property name= "connection.url" >jdbc:mysql: ///user</property>
         <property name= "connection.username" >root</property>
         <property name= "connection.password" > 123456 </property>
         <property name= "connection.driver_class" >com.mysql.jdbc.Driver</property>
         <mapping resource= "com/xiaohao/test/User.hbm.xml"  />
     
     
     
    </session-factory>
    </hibernate-configuration>

      UserTest.java

        

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    package  com.xiaohao.test;
     
    import  org.hibernate.Session;
    import  org.hibernate.SessionFactory;
    import  org.hibernate.Transaction;
    import  org.hibernate.cfg.Configuration;
     
    public  class  UserTest {
         public  static  void  main(String[] args) {
             Configuration conf= new  Configuration().configure();
             SessionFactory sf=conf.buildSessionFactory();
             Session session=sf.getCurrentSession();
             Transaction tx=session.beginTransaction();
    //      User user=new User("小浩","英雄");
    //      session.save(user);
    //       session.createSQLQuery("insert into user(userName,password) value('张英雄16','123')")
    //                  .executeUpdate();
             User user=(User) session.get(User. class 1 );
             user.setUserName( "221" );
    //      session.save(user);
         
             System.out.println( "恭喜您,用户的数据插入成功了哦~~" );
             tx.commit();
         }
     
    }

      每次对 TUser 进行更新的时候,我们可以发现,数据库中的 version 都在递增。



    下面我们将要通过乐观锁来实现一下并发和同步的测试用例:

    这里需要使用两个测试类,分别运行在不同的虚拟机上面,以此来模拟多个用户同时操作一张表,同时其中一个测试类需要模拟长事务

    UserTest.java

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    package  com.xiaohao.test;
     
    import  org.hibernate.Session;
    import  org.hibernate.SessionFactory;
    import  org.hibernate.Transaction;
    import  org.hibernate.cfg.Configuration;
     
    public  class  UserTest {
         public  static  void  main(String[] args) {
             Configuration conf= new  Configuration().configure();
             SessionFactory sf=conf.buildSessionFactory();
             Session session=sf.openSession();
    //      Session session2=sf.openSession();
             User user=(User) session.createQuery( " from User user where user=5" ).uniqueResult();
    //      User user2=(User) session.createQuery(" from User user where user=5").uniqueResult();
             System.out.println(user.getVersion());
    //      System.out.println(user2.getVersion());
             Transaction tx=session.beginTransaction();
             user.setUserName( "101" );
             tx.commit();
             
             System.out.println(user.getVersion());
    //      System.out.println(user2.getVersion());
    //      System.out.println(user.getVersion()==user2.getVersion());
    //      Transaction tx2=session2.beginTransaction();
    //      user2.setUserName("4468");
    //      tx2.commit();
         
         }
     
    }

      UserTest2.java

     

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    package  com.xiaohao.test;
     
    import  org.hibernate.Session;
    import  org.hibernate.SessionFactory;
    import  org.hibernate.Transaction;
    import  org.hibernate.cfg.Configuration;
     
    public  class  UserTest2 {
         public  static  void  main(String[] args)  throws  InterruptedException {
             Configuration conf= new  Configuration().configure();
             SessionFactory sf=conf.buildSessionFactory();
             Session session=sf.openSession();
    //      Session session2=sf.openSession();
             User user=(User) session.createQuery( " from User user where user=5" ).uniqueResult();
             Thread.sleep( 10000 );
    //      User user2=(User) session.createQuery(" from User user where user=5").uniqueResult();
             System.out.println(user.getVersion());
    //      System.out.println(user2.getVersion());
             Transaction tx=session.beginTransaction();
             user.setUserName( "100" );
             tx.commit();
             
             System.out.println(user.getVersion());
    //      System.out.println(user2.getVersion());
    //      System.out.println(user.getVersion()==user2.getVersion());
    //      Transaction tx2=session2.beginTransaction();
    //      user2.setUserName("4468");
    //      tx2.commit();
         
         }
     
    }

     

    操作流程及简单讲解: 首先启动UserTest2.java测试类,在执行到Thread.sleep(10000);这条语句的时候,当前线程会进入睡眠状态。在10秒钟之内

                                启动UserTest这个类,在到达10秒的时候,我们将会在UserTest.java中抛出下面的异常:

     

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    Exception in thread  "main"  org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [com.xiaohao.test.User# 5 ]
         at org.hibernate.persister.entity.AbstractEntityPersister.check(AbstractEntityPersister.java: 1932 )
         at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java: 2576 )
         at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java: 2476 )
         at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java: 2803 )
         at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java: 113 )
         at org.hibernate.engine.ActionQueue.execute(ActionQueue.java: 273 )
         at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java: 265 )
         at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java: 185 )
         at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java: 321 )
         at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java: 51 )
         at org.hibernate.impl.SessionImpl.flush(SessionImpl.java: 1216 )
         at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java: 383 )
         at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java: 133 )
         at com.xiaohao.test.UserTest2.main(UserTest2.java: 21 )

     

     UserTest2代码将在 tx.commit() 处抛出 StaleObjectStateException 异 常,并指出版本检查失败,当前事务正在试图提交一个过期数据。通过捕捉这个异常,我 们就可以在乐观锁校验失败时进行相应处理

     

     

     

       3、常见并发同步案例分析

        案例一:订票系统案例,某航班只有一张机票,假定有1w个人打开你的网站来订票,问你如何解决并发问题(可扩展到任何高并发网站要考虑

                   的并发读写问题)

        问题,1w个人来访问,票没出去前要保证大家都能看到有票,不可能一个人在看到票的时候别人就不能看了。到底谁能抢到,那得看这个人的“运气”(网

                 络快慢等)

    其次考虑的问题,并发,1w个人同时点击购买,到底谁能成交?总共只有一张票。

    首先我们容易想到和并发相关的几个方案 :

    锁同步同步更多指的是应用程序的层面,多个线程进来,只能一个一个的访问,java中指的是syncrinized关键字。锁也有2个层面,一个是java中谈到的对

    象锁,用于线程同步;另外一个层面是数据库的锁;如果是分布式的系统,显然只能利用数据库端的锁来实现。

    假定我们采用了同步机制或者数据库物理锁机制,如何保证1w个人还能同时看到有票,显然会牺牲性能,在高并发网站中是不可取的。使用hibernate后我们

    提出了另外一个概念:乐观锁悲观锁(即传统的物理锁);

    采用乐观锁即可解决此问题。乐观锁意思是不锁定表的情况下,利用业务的控制来解决并发问题,这样即保证数据的并发可读性又保证保存数据的排他性,保

    证性能的同时解决了并发带来的脏数据问题。

    hibernate中如何实现乐观锁:

    前提:在现有表当中增加一个冗余字段,version版本号, long类型

    原理:

    1)只有当前版本号》=数据库表版本号,才能提交

    2)提交成功后,版本号version ++

    实现很简单:在ormapping增加一属性optimistic-lock="version"即可,以下是样例片段

    <hibernate-mapping>

    <class name="com.insigma.stock.ABC" optimistic-lock="version" table="T_Stock" schema="STOCK">

    案例二、股票交易系统、银行系统,大数据量你是如何考虑的

    首先,股票交易系统的行情表,每几秒钟就有一个行情记录产生,一天下来就有(假定行情3秒一个) 股票数量×20×60*6 条记录,一月下来这个表记录数

    量多大? oracle中一张表的记录数超过100w后 查询性能就很差了,如何保证系统性能?

    再比如,中国移动有上亿的用户量,表如何设计?把所有用于存在于一个表么?

    所以,大数量的系统,必须考虑表拆分-(表名字不一样,但是结构完全一样),通用的几种方式:(视情况而定)

    1)按业务分,比如 手机号的表,我们可以考虑 130开头的作为一个表,131开头的另外一张表 以此类推

    2)利用oracle的表拆分机制做分表

    3)如果是交易系统,我们可以考虑按时间轴拆分,当日数据一个表,历史数据弄到其它表。这里历史数据的报表和查询不会影响当日交易。

    当然,表拆分后我们的应用得做相应的适配。单纯的or-mapping也许就得改动了。比如部分业务得通过存储过程等

    此外,我们还得考虑缓存

    这里的缓存,指的不仅仅是hibernate,hibernate本身提供了一级二级缓存。这里的缓存独立于应用,依然是内存的读取,假如我们能减少数据库频繁的访

    问,那对系统肯定大大有利的。比如一个电子商务系统的商品搜索,如果某个关键字的商品经常被搜,那就可以考虑这部分商品列表存放到缓存(内存中

    去),这样不用每次访问数据库,性能大大增加。

    简单的缓存大家可以理解为自己做一个hashmap,把常访问的数据做一个key,value是第一次从数据库搜索出来的值,下次访问就可以从map里读取,而不

    读数据库;专业些的目前有独立的缓存框架比如memcached 等,可独立部署成一个缓存服务器。

     

    4、常见的提高高并发下访问的效率的手段

          首先要了解高并发的的瓶颈在哪里?

         1、可能是服务器网络带宽不够

         2.可能web线程连接数不够

         3.可能数据库连接查询上不去。

         根据不同的情况,解决思路也不同。

    1. 像第一种情况可以增加网络带宽,DNS域名解析分发多台服务器。

    2. 负载均衡,前置代理服务器nginx、apache等等

    3. 数据库查询优化,读写分离,分表等等

       最后复制一些在高并发下面需要常常需要处理的内容:

    • 尽量使用缓存,包括用户缓存,信息缓存等,多花点内存来做缓存,可以大量减少与数据库的交互,提高性能。

    • 用jprofiler等工具找出性能瓶颈,减少额外的开销。

    • 优化数据库查询语句,减少直接使用hibernate等工具的直接生成语句(仅耗时较长的查询做优化)。

    • 优化数据库结构,多做索引,提高查询效率。

    • 统计的功能尽量做缓存,或按每天一统计或定时统计相关报表,避免需要时进行统计的功能。

    • 能使用静态页面的地方尽量使用,减少容器的解析(尽量将动态内容生成静态html来显示)。

    • 解决以上问题后,使用服务器集群来解决单台的瓶颈问题。

     

    展开全文
  • java项目——大数据量的处理

    万次阅读 2016-01-31 23:40:36
    比如求TOP10,我们首先取前 10个元素调整成最小堆,如果发现,然后扫描后面的数据,并与堆顶元素比较,如果比堆顶元素,那么用该元素替换堆顶,然后再调整为最小堆。最后堆中的元 素就是TOP10。 s 求出每...

    1. 给定a、b两个文件,各存放50亿个url,每个url各占64字节,内存限制是4G,让你找出a、b文件共同的url?
    方案1:可以估计每个文件安的大小为50G×64=320G,远远大于内存限制的4G。所以不可能将其完全加载到内存中处理。考虑采取分而治之的方法。
    s 遍历文件a,对每个url求取 ,然后根据所取得的值将url分别存储到1000个小文件(记为 )中。这样每个小文件的大约为300M。
    s 遍历文件b,采取和a相同的方式将url分别存储到1000各小文件(记为 )。这样处理后,所有可能相同的url都在对应的小文件( )中,不对应的小文件不可能有相同的url。然后我们只要求出1000对小文件中相同的url即可。
    s 求每对小文件中相同的url时,可以把其中一个小文件的url存储到hash_set中。然后遍历另一个小文件的每个url,看其是否在刚才构建的hash_set中,如果是,那么就是共同的url,存到文件里面就可以了。
    方案2:如果允许有一定的错误率,可以使用Bloom filter,4G内存大概可以表示340亿bit。将其中一个文件中的url使用Bloom filter映射为这340亿bit,然后挨个读取另外一个文件的url,检查是否与Bloom filter,如果是,那么该url应该是共同的url(注意会有一定的错误率)。

     

    2. 有10个文件,每个文件1G,每个文件的每一行存放的都是用户的query,每个文件的query都可能重复。要求你按照query的频度排序。
    方案1:
    s 顺序读取10个文件,按照hash(query)%10的结果将query写入到另外10个文件(记为 )中。这样新生成的文件每个的大小大约也1G(假设hash函数是随机的)。
    s 找一台内存在2G左右的机器,依次对 用hash_map(query, query_count)来统计每个query出现的次数。利用快速/堆/归并排序按照出现次数进行排序。将排序好的query和对应的query_cout输出到文件中。这样得到了10个排好序的文件(记为 )。
    s 对 这10个文件进行归并排序(内排序与外排序相结合)。
    方案2:
    一般query的总量是有限的,只是重复的次数比较多而已,可能对于所有的query,一次性就可以加入到内存了。这样,我们就可以采用trie树/hash_map等直接来统计每个query出现的次数,然后按出现次数做快速/堆/归并排序就可以了。
    方案3:
    与方案1类似,但在做完hash,分成多个文件后,可以交给多个文件来处理,采用分布式的架构来处理(比如MapReduce),最后再进行合并。

     

    3. 有一个1G大小的一个文件,里面每一行是一个词,词的大小不超过16字节,内存限制大小是1M。返回频数最高的100个词。
    方案1:顺序读文件中,对于每个词x,取 ,然后按照该值存到5000个小文件(记为 ) 中。这样每个文件大概是200k左右。如果其中的有的文件超过了1M大小,还可以按照类似的方法继续往下分,知道分解得到的小文件的大小都不超过1M。对 每个小文件,统计每个文件中出现的词以及相应的频率(可以采用trie树/hash_map等),并取出出现频率最大的100个词(可以用含100个结点 的最小堆),并把100词及相应的频率存入文件,这样又得到了5000个文件。下一步就是把这5000个文件进行归并(类似与归并排序)的过程了。

     

    4. 海量日志数据,提取出某日访问百度次数最多的那个IP。
    方案1:首先是这一天,并且是访问百度的日志中的IP取出来,逐个写入到一个大文件中。注意到IP是32位的,最多有 个 IP。同样可以采用映射的方法,比如模1000,把整个大文件映射为1000个小文件,再找出每个小文中出现频率最大的IP(可以采用hash_map进 行频率统计,然后再找出频率最大的几个)及相应的频率。然后再在这1000个最大的IP中,找出那个频率最大的IP,即为所求。

     

    5. 在2.5亿个整数中找出不重复的整数,内存不足以容纳这2.5亿个整数。
    方案1:采用2-Bitmap(每个数分配2bit,00表示不存在,01表示出现一次,10表示多次,11无意义)进行,共需内存 内存,还可以接受。然后扫描这2.5亿个整数,查看Bitmap中相对应位,如果是00变01,01变10,10保持不变。所描完事后,查看bitmap,把对应位是01的整数输出即可。
    方案2:也可采用上题类似的方法,进行划分小文件的方法。然后在小文件中找出不重复的整数,并排序。然后再进行归并,注意去除重复的元素。

     

    6. 海量数据分布在100台电脑中,想个办法高校统计出这批数据的TOP10。
    方案1:
    s 在每台电脑上求出TOP10,可以采用包含10个元素的堆完成(TOP10小,用最大堆,TOP10大,用最小堆)。比如求TOP10大,我们首先取前 10个元素调整成最小堆,如果发现,然后扫描后面的数据,并与堆顶元素比较,如果比堆顶元素大,那么用该元素替换堆顶,然后再调整为最小堆。最后堆中的元 素就是TOP10大。
    s 求出每台电脑上的TOP10后,然后把这100台电脑上的TOP10组合起来,共1000个数据,再利用上面类似的方法求出TOP10就可以了。

     

    7. 怎么在海量数据中找出重复次数最多的一个?
    方案1:先做hash,然后求模映射为小文件,求出每个小文件中重复次数最多的一个,并记录重复次数。然后找出上一步求出的数据中重复次数最多的一个就是所求(具体参考前面的题)。

     

    8. 上千万或上亿数据(有重复),统计其中出现次数最多的钱N个数据。
    方案1:上千万或上亿的数据,现在的机器的内存应该能存下。所以考虑采用hash_map/搜索二叉树/红黑树等来进行统计次数。然后就是取出前N个出现次数最多的数据了,可以用第6题提到的堆机制完成。

     

    9. 1000万字符串,其中有些是重复的,需要把重复的全部去掉,保留没有重复的字符串。请怎么设计和实现?
    方案1:这题用trie树比较合适,hash_map也应该能行。

     

    10. 一个文本文件,大约有一万行,每行一个词,要求统计出其中最频繁出现的前10个词,请给出思想,给出时间复杂度分析。
    方案1:这题是考虑时间效率。用trie树统计每个词出现的次数,时间复杂度是O(n*le)(le表示单词的平准长度)。然后是找出出现最频繁的 前10个词,可以用堆来实现,前面的题中已经讲到了,时间复杂度是O(n*lg10)。所以总的时间复杂度,是O(n*le)与O(n*lg10)中较大 的哪一个。

     

    11. 一个文本文件,找出前10个经常出现的词,但这次文件比较长,说是上亿行或十亿行,总之无法一次读入内存,问最优解。
    方案1:首先根据用hash并求模,将文件分解为多个小文件,对于单个文件利用上题的方法求出每个文件件中10个最常出现的词。然后再进行归并处理,找出最终的10个最常出现的词。

     

    12. 100w个数中找出最大的100个数。
    方案1:在前面的题中,我们已经提到了,用一个含100个元素的最小堆完成。复杂度为O(100w*lg100)。
    方案2:采用快速排序的思想,每次分割之后只考虑比轴大的一部分,知道比轴大的一部分在比100多的时候,采用传统排序算法排序,取前100个。复杂度为O(100w*100)。
    方案3:采用局部淘汰法。选取前100个元素,并排序,记为序列L。然后一次扫描剩余的元素x,与排好序的100个元素中最小的元素比,如果比这个 最小的要大,那么把这个最小的元素删除,并把x利用插入排序的思想,插入到序列L中。依次循环,知道扫描了所有的元素。复杂度为O(100w*100)。

     

    13. 寻找热门查询:
    搜索引擎会通过日志文件把用户每次检索使用的所有检索串都记录下来,每个查询串的长度为1-255字节。假设目前有一千万个记录,这些查询串的重复 读比较高,虽然总数是1千万,但是如果去除重复和,不超过3百万个。一个查询串的重复度越高,说明查询它的用户越多,也就越热门。请你统计最热门的10个 查询串,要求使用的内存不能超过1G。
    (1) 请描述你解决这个问题的思路;
    (2) 请给出主要的处理流程,算法,以及算法的复杂度。
    方案1:采用trie树,关键字域存该查询串出现的次数,没有出现为0。最后用10个元素的最小推来对出现频率进行排序。

     

    14. 一共有N个机器,每个机器上有N个数。每个机器最多存O(N)个数并对它们操作。如何找到 个数中的中数?
    方案1:先大体估计一下这些数的范围,比如这里假设这些数都是32位无符号整数(共有 个)。我们把0到 的整数划分为N个范围段,每个段包含 个整数。比如,第一个段位0到 ,第二段为 到 ,…,第N个段为 到 。 然后,扫描每个机器上的N个数,把属于第一个区段的数放到第一个机器上,属于第二个区段的数放到第二个机器上,…,属于第N个区段的数放到第N个机器上。 注意这个过程每个机器上存储的数应该是O(N)的。下面我们依次统计每个机器上数的个数,一次累加,直到找到第k个机器,在该机器上累加的数大于或等于 ,而在第k-1个机器上的累加数小于 ,并把这个数记为x。那么我们要找的中位数在第k个机器中,排在第 位。然后我们对第k个机器的数排序,并找出第 个数,即为所求的中位数。复杂度是 的。
    方案2:先对每台机器上的数进行排序。排好序后,我们采用归并排序的思想,将这N个机器上的数归并起来得到最终的排序。找到第n个便是所求。复杂度是n(i)的。

     

    15. 最大间隙问题
    给定n个实数 ,求着n个实数在实轴上向量2个数之间的最大差值,要求线性的时间算法。
    方案1:最先想到的方法就是先对这n个数据进行排序,然后一遍扫描即可确定相邻的最大间隙。但该方法不能满足线性时间的要求。故采取如下方法:
    s 找到n个数据中最大和最小数据max和min。
    s 用n-2个点等分区间[min, max],即将[min, max]等分为n-1个区间(前闭后开区间),将这些区间看作桶,编号为 ,且桶 的上界和桶i+1的下届相同,即每个桶的大小相同。每个桶的大小为: 。实际上,这些桶的边界构成了一个等差数列(首项为min,公差为 ),且认为将min放入第一个桶,将max放入第n-1个桶。
    s 将n个数放入n-1个桶中:将每个元素 分配到某个桶(编号为index),其中 ,并求出分到每个桶的最大最小数据。
    s 最大间隙:除最大最小数据max和min以外的n-2个数据放入n-1个桶中,由抽屉原理可知至少有一个桶是空的,又因为每个桶的大小相同,所以最大间隙 不会在同一桶中出现,一定是某个桶的上界和气候某个桶的下界之间隙,且该量筒之间的桶(即便好在该连个便好之间的桶)一定是空桶。也就是说,最大间隙在桶 i的上界和桶j的下界之间产生 。一遍扫描即可完成。

     

    16. 将多个集合合并成没有交集的集合:给定一个字符串的集合,格式如: 。要求将其中交集不为空的集合合并,要求合并完成的集合之间无交集,例如上例应输出 。
    (1) 请描述你解决这个问题的思路;
    (2) 给出主要的处理流程,算法,以及算法的复杂度;
    (3) 请描述可能的改进。
    方案1:采用并查集。首先所有的字符串都在单独的并查集中。然后依扫描每个集合,顺序合并将两个相邻元素合并。例如,对于 , 首先查看aaa和bbb是否在同一个并查集中,如果不在,那么把它们所在的并查集合并,然后再看bbb和ccc是否在同一个并查集中,如果不在,那么也把 它们所在的并查集合并。接下来再扫描其他的集合,当所有的集合都扫描完了,并查集代表的集合便是所求。复杂度应该是O(NlgN)的。改进的话,首先可以 记录每个节点的根结点,改进查询。合并的时候,可以把大的和小的进行合,这样也减少复杂度。

     

    17. 最大子序列与最大子矩阵问题
    数组的最大子序列问题:给定一个数组,其中元素有正,也有负,找出其中一个连续子序列,使和最大。
    方案1:这个问题可以动态规划的思想解决。设 表示以第i个元素 结尾的最大子序列,那么显然 。基于这一点可以很快用代码实现。
    最大子矩阵问题:给定一个矩阵(二维数组),其中数据有大有小,请找一个子矩阵,使得子矩阵的和最大,并输出这个和。
    方案1:可以采用与最大子序列类似的思想来解决。如果我们确定了选择第i列和第j列之间的元素,那么在这个范围内,其实就是一个最大子序列问题。如何确定第i列和第j列可以词用暴搜的方法进行。

     

    转自:http://sunjia-704471770-qq-com.iteye.com/blog/1628359

    展开全文
  • Python入门学习(第三天)--爬取网站数据

    万次阅读 热门讨论 2019-09-01 13:57:59
    在我们学习Python的第一天和第二天的时候我们已经把Python安装好了,环境配置也处理完毕了,今天我们就来爬数据,用Python总得做点啥是不是,今天我们先爬取网站数据,提高一下我们明天开始学基础的积极性。...

    在我们学习Python的第一天和第二天的时候我们已经把Python安装好了,环境配置也处理完毕了,今天我们就来爬数据,用Python总得做点啥是不是,今天我们先爬取网站数据,提高一下我们明天开始学基础的积极性。

    废话不多逼逼了,开始上步骤,请确保每一步与笔者一致并执行成功!!!
    废话不多逼逼了,开始上步骤,请确保每一步与笔者一致并执行成功!!!
    废话不多逼逼了,开始上步骤,请确保每一步与笔者一致并执行成功!!!


    准备开始

    每一次打算用 Python 搞点什么的时候,你问的第一个问题应该是:“我需要用到什么库”?


    第一步,连接并获取一个要爬取的网站内容

    今天网页爬取要用到的库(选任意一个就可以):

    要爬取的网站:
    https://www.fasttrack.co.uk/league-tables/tech-track-100/league-table/
    在这里插入图片描述
    爬取数据之前要先分析你将要爬取数据的数据结构,通过鼠标右键你想要查看的内容,选择“检查”,就能看到他的dom结构。
    在这里插入图片描述
    这个例子里,所有的100个结果都包含在同一个页面中,还被 标签分隔成行。但实际抓取过程中,许多数据往往分布在多个不同的页面上,你需要调整每页显示的结果总数,或者遍历所有的页面,才能抓取到完整的数据。

    在表格页面上,你可以看到一个包含了所有100条数据的表格,右键点击它,选择“检查”,你就能很容易地看到这个 HTML 表格的结构。包含内容的表格本体是在这样的标签里:

    在这里插入图片描述
    每一行都是在一个 标签里,也就是我们不需要太复杂的代码,只需要一个循环,就能读取到所有的表格数据,并保存到文件里。

    附注:你还可以通过检查当前页面是否发送了 HTTP GET 请求,并获取这个请求的返回值,来获取显示在页面上的信息。因为 HTTP GET 请求经常能返回已经结构化的数据,比如 JSON 或者 XML 格式的数据,方便后续处理。你可以在开发者工具里点击 Network 分类(有必要的话可以仅查看其中的 XHR 标签的内容)。这时你可以刷新一下页面,于是所有在页面上载入的请求和返回的内容都会在 Network 中列出。此外,你还可以用某种 REST 客户端(比如 Insomnia)来发起请求,并输出返回值。


    将你的编辑器和电脑上安装BeautifulSoup4

    windows键加r键开启命令行。

    $ pip install BeautifulSoup4
    

    然后再在你的编辑器上进行安装
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    安装完成后在界面下方会提示“Package‘bs4’ install successfully”

    在这里插入图片描述
    现在开始在你第一天print出Hello World的那个文件把那个Print删了开始改成我们今天接下来的代码:

    首先要做的是导入代码中需要用到的各种模块。上面我们已经提到过 BeautifulSoup,这个模块可以帮我们处理 HTML 结构。接下来要导入的模块还有 urllib,它负责连接到目标地址,并获取网页内容。最后,我们需要能把数据写入 CSV 文件,保存在本地硬盘上的功能,所以我们要导入 csv 库。当然这不是唯一的选择,如果你想要把数据保存成 json 文件,那相应的就需要导入 json 库。

    # 导入需要的模块
    from bs4 import BeautifulSoup
    import urllib.request
    import csv
    
    # 把网站url存在变量里
    urlpage = 'https://www.fasttrack.co.uk/league-tables/tech-track-100/league-table/'
    
    # 获取网页内容,把html数据保存在“page变量里”
    page = urllib.request.urlopen(urlpage)
    # 用beautiful soup 解析 html 数据
    soup = BeautifulSoup(page, 'html.parser')
    

    这时候,你可以试着把 soup 变量打印出来,看看里面已经处理过的 html 数据长什么样:

    print(soup)
    

    如果变量内容是空的,或者返回了什么错误信息,则说明可能没有正确获取到网页数据。你也许需要用一些错误捕获代码,配合 urllib.error 模块,来发现可能存在的问题。


    第二步,用BeautifulSoup处理获得的html数据

    查找 HTML 元素

    既然所有的内容都在表格里(<table>标签),我们可以在 soup 对象里搜索需要的表格,然后再用 find_all 方法,遍历表格中的每一行数据。

    如果你试着打印出所有的行,那应该会有 101 行——100行内容,加上一行表头。

    # 在表格中查找数据
    table = soup.find('table', attrs={'class': 'tableSorter'})
    results = table.find_all('tr')
    print('Number of results', len(results))
    

    看看打印出来的内容,如果没问题的话,我们就可以用一个循环来获取所有数据啦。

    如果你打印出 soup 对象的前 2 行,你可以看到,每一行的结构是这样的:
    在这里插入图片描述
    可以看到,表格中总共有 8 列,分别是 Rank(排名)、Company(公司)、Location(地址)、Year End(财年结束)、Annual Sales Rise(年度销售增长)、Latest Sales(本年度销售额)、Staff(员工数)和 Comments(备注)。这些都是我们所需要的数据。

    这样的结构在整个网页中都保持一致(不过在其他网站上可能就没这么简单了!),所以我们可以再次使用 find_all 方法,通过搜索 元素,逐行提取出数据,存储在变量中,方便之后写入 csv 或 json 文件。

    循环遍历所有的元素并存储在变量中

    在 Python 里,如果要处理大量数据,还需要写入文件,那列表对象是很有用的。我们可以先声明一个空列表,填入最初的表头(方便以后CSV文件使用),而之后的数据只需要调用列表对象的 append 方法即可。

    rows = []
    rows.append(['Rank', 'Company Name',
                'Webpage', 'Description',
                'Location', 'Year end',
                'Annual sales rise over 3 years', 'Sales f000s',
                'Staff', 'Comments'
                ])
    print(rows)
    

    这样就将打印出我们刚刚加到列表对象 rows 中的第一行表头。

    你可能会注意到,我输入的表头中比网页上的表格多写了几个列名,比如 Webpage(网页)和 Description(描述),请仔细看看上面打印出的 soup 变量数据——第二行第二列的数据里,可不只有公司名字,还有公司的网址和简单描述。所以我们需要这些额外的列来存储这些数据。


    第三步,在soup对象里循环搜索需要的html元素

    下一步,我们遍历所有100行数据,提取内容,并保存到列表中。

    循环读取数据的方法:

    # 遍历所有数据
    for result in results:
    	# 找到每一个 td 单元格的内容
    	data = result.find_all('td')
    	# 如果该单元格无数据,则跳过
    	if len(data) == 0:
    		continue
    

    因为数据的第一行是 html 表格的表头,所以我们可以跳过不用读取它。因为表头用的是 标签,没有用 标签,所以我们只要简单地查询 标签内的数据,并且抛弃空值即可。

    接着,我们将 data 的内容读取出来,赋值到变量中:

    # 将单元格内容保存到变量中
    rank = data[0].getText()
    company = data[1].getText()
    location = data[2].getText()
    yearend = data[3].getText()
    salesrise = data[4].getText()
    sales = data[5].getText()
    staff = data[6].getText()
    comments = data[7].getText()
    

    如上面的代码所示,我们按顺序将 8 个列里的内容,存储到 8 个变量中。当然,有些数据的内容还需有额外的清理,去除多余的字符,导出所需的数据。


    第四步,进行数据处理(将不要的符号去掉等等操作)

    数据清理

    如果我们打印出 company 变量的内容,就能发现,它不但包含了公司名称,还包括和描述。如果我们打印出 sales 变量的内容,就能发现它还包括一些备注符号等需要清除的字符。

    print('Company is', company)
    print('Sales', sales)
    

    我们希望把 company 变量的内容分割成公司名称和描述两部分。这用几行代码就能搞定。再看看对应的 html 代码,你会发现这个单元格里还有一个 元素,这个元素里只有公司名称。另外,还有一个 <a> 链接元素,包含一个指向该公司详情页面的链接。我们一会也会用到它!
    在这里插入图片描述
    为了区分公司名称和描述两个字段,我们再用 find 方法把 元素里的内容读取出来,然后删掉或替换 company 变量中的对应内容,这样变量里就只会留下描述了。

    要删除 sales 变量中的多余字符,我们用一次 strip 方法即可。

    # 提取公司名字
    companyname = data[1].find('span', attrs={'class': 'company-name'}).getText()
    description = company.replace(companyname, '')
    # 移除多余的字符
    sales = sales.strip('*').strip('+').replace(',', '')
    

    最后我们要保存的是公司网站的链接。就像上面说的,第二列中有一个指向该公司详情页面的链接。每一个公司的详情页都有一个表格,大部分情况下,表格里都有一个公司网站的链接。

    在这里插入图片描述
    检查公司详情页里,表格中的链接

    为了抓取每个表格中的网址,并保存到变量里,我们需要执行以下几个步骤:

    • 在最初的 fast track 网页上,找到需要访问的公司详情页的链接。
    • 发起一个对公司详情页链接的请求
    • 用 Beautifulsoup 处理一下获得的 html 数据
    • 找到需要的链接元素

    正如上面的截图那样,看过几个公司详情页之后,你就会发现,公司的网址基本上就在表格的最后一行。所以我们可以在表格的最后一行里找 <a> 元素。

    # 获取链接,并发出访问请求
    url = data[1].find('a').get('href')
    page = urllib.request.urlopen(url)
    # 处理HTML数据
    soup = BeautifulSoup(page, 'html.parser')
    # 找到表格中的最后一行,尝试获取<a>元素的内容
    try:
    	tableRow = soup.find('table').find_all('tr')[-1]
    	webpage = tableRow.find('a').get('href')
    except:
    	webpage = None
    

    同样,有可能出现最后一行没有链接的情况。所以我们增加了 try… except 语句,如果没有发现网址,则将变量设置成 None。当我们把所有需要的数据都存在变量中的以后(还在循环体内部),我们可以把所有变量整合成一个列表,再把这个列表 append 到上面我们初始化的 rows 对象的末尾。

    # 把变量添加到rows对象里
     rows.append([rank, company,
                  webpage, desciption,
                  location, yearend,
                  salesrise, sales,
                  staff, comments
                  ])
    # 注意这里退出了循环体
    print(rows)
    

    上面代码的最后,我们在结束循环体之后打印了一下 rows 的内容,这样你可以在把数据写入文件前,再检查一下。


    第五步,把数据写入csv文件中

    最后,我们把上面获取的数据写入外部文件,方便之后的分析处理。在 Python 里,我们只需要简单的几行代码,就可以把列表对象保存成文件。

    # 创建一个 csv 文件,并将 rows 对象写入这个文件中
    with open('techtrack100.scv', 'w', newline='') as f_output:
    	csv_output = csv.writer(f_output)
    	csv_output.writerows(rows)
    

    最后我们来运行一下这个 python 代码,如果一切顺利,你就会发现一个包含了 100 行数据的 csv 文件出现在了目录中,你就可以看到如下效果了:
    在这里插入图片描述
    如果你运行时报错:UnicodeEncodeError: ‘gbk’ codec can’t encode character ‘\xa0’
    请将最后的代码改成:

    with open('techtrack100.csv', 'w', newline='') as f_output:
        csv_output = csv.writer(f_output.replace(u'\xa0', u''))
        csv_output.writerows(rows)
    

    OK了,如果朋友们还有其他什么问题就在下面留言吧!明天开始学python基础!一起加油!

    展开全文
  • 《数据库原理》— 数据库系统概论第五版习题解析

    万次阅读 多人点赞 2017-05-29 14:57:48
    (l)数据(Data):描述事物的符号记录称为数据数据的种类有数字、文字、图形、图像、声音、正文等。数据与其语义是不可分的。解析在现代计算机系统中数据的概念是广义的。早期的计算机系统主要用于科学计算,处理...
  • 入门学习Linux常用必会60个命令实例详解doc/txt

    千次下载 热门讨论 2011-06-09 00:08:45
    这是因为Linux和许多版本的Unix一样,提供了虚拟控制台的访问方式,允许用户在同一时间从控制台(系统的控制台是与系统直接相连的监视器和键盘)进行多次登录。每个虚拟控制台可以看作是一个独立的工作站,工作台...
  • 问题描述:一个日志文件里逐行存储着 时间 ip 这种形式的日志,现在需要让你使用linux命令查找出日志文件中访问量最大的10个ip 怎么处理? 1.首先创建模拟数据 这里博主用python写了个创建模拟数据的脚本 文件内容格式...
  • 将已经集成好的软件系统,作为整个计算机系统的一个元素,与计算机硬件、外设、某些支持软件、数据和人员等其它系统元素结合在一起,在实际运行(使用)环境下, - 对计算机系统进行一系列测试活动。 根本任务 ...
  • SQL Server DBA调优日记第一篇,大数据量查询记录数优化及原理探讨。现象、解决、模拟、原理探讨。
  • 进行本地加载,可以节约请求回显时间,但是这种做法并没有使问题获得改善,这表示接口返回应该是很快的,请求或者本地并不是主要影响,那么主要影响可能是在真机上数据导致列表渲染耗时太长。 2. 采用本地、分段...
  • 场景还原:前一个月给朋友写了个简单的登录功能,简单的查询数据库登录逻辑,使用mysbatis-plus进行的dao层代码生成(吐槽一下这个工具,真是方便一时爽,后面维护难,比较喜欢自己能够组装和优化sql,大数据量插入...
  • 2. 存储何种数据? 3. 数据特点是什么? 4. 存在哪些技术挑战? 5. 解决方案有哪些? 6. md5散列桶的方法需要注意哪些问题? 7. 测试结果是什么? 解决方案: 1 需求背景 该应用场景为 DMP(Data...
  • 问题描述 百度面试题: 搜索引擎会通过日志文件把用户每次检索使用的所有检索串都记录下来,每个查询串的长度为1-255字节。 假设目前有一千万个记录(这些查询串的重复度比较高,虽然总数是1千万,但如果除去...
  • 首页 资讯 精华 论坛 问答 博客 专栏 群组 更多 ▼ 您还未登录 ! 登录 注册 ...大数据量下高并发同步的讲解(不看,保证你后悔) javahibernateoraclemysql  对于我们开发的网站
  • 数据仓库基本知识

    万次阅读 多人点赞 2017-10-31 17:35:04
    根据统计,每个企业的数据量每2~3年时间就会成倍增长,这些数据蕴含着巨大的商业价值,而企业所关注的通常只占在总数据量的2%~4%左右。 因此,企业仍然没有最大化地利用已存在的数据资源,以至于浪费了更多的...
  •  HDFS(Hadoop Distributed File System)是Hadoop项目的核心子项目,是分布式计算中数据存储管理的基础,是基于流数据模式访问和处理超大文件的需求而开发的,可以运行于廉价的商用服务器上。它所具有的高容错、高...
  • 数据治理系列2:元数据管理—企业数据治理的基础

    万次阅读 多人点赞 2019-05-13 20:11:04
    导读:元数据管理是对企业涉及的业务元数据、技术元数据、管理元数据进行盘点、集成和管理,按照科学、有效的机制...元数据(Metadata),元数据是关于数据的组织、数据域及其关系的信息,简言之,元数据就是描述数据...
  • 摘要从自然图像生成自动描述是一个具有挑战性的问题,近来受到计算机视觉和自然语言处理... 此外,我们概述了评估机器生成图像描述质量的基准图像数据集和评估措施。 最后,我们推断自动图像描述生成领域的未来方向。
  • 教你如何迅速秒杀掉:99%的海量数据...十道海量数据处理面试题与十个方法总结 https://blog.csdn.net/twlkyao/article/details/12037073 题目描述: 给定a、b两个文件,各存放50亿个url,每个url各占64B,...
  • 在Modbus主站与从站之间进行大数据量通信的方法 什么​是​Modbus​协议? Modbus​是​使用​主从关系​实现​的​请求 - 响应​协议。 在​主从关系​中,​通信​总是​成​对​发生 —— 一个​设备​必须​...
  • 当用户规模比较小的时候,使用简单的:一台应用服务器+一台数据库服务器+一台文件服务器,这样的话完全可以解决一部分问题,也可以通过堆硬件的方式来提高网站应用的访问性能,当然,也要考虑成本的问题。...
  • 架构大数据分析应用

    万次阅读 2016-05-18 21:19:50
    数据的获取或者摄取开始于不同的数据源,可能是的日志文件,流数据, ETL处理过的输出,在线的非结构化数据,或者离线的结构化数据。 Apache Flume 当查看生成的摄取日志的时候,强烈推荐使用Apache Flume...
  • 数据分析之数据预处理、分析建模、可视化

    万次阅读 多人点赞 2020-08-08 15:03:21
    概述:简介、思路、发展...数据分析方法:描述统计、假设检验、信度分析、相关分析、方差分析、回归分析、聚类分析、判别分析、主成分分析、因子分析、时间序列分析等; 数据可视化:Excel、PowerBI、Tableau、Python;
  • 数据中“淘金”,从大量数据(文本)中挖掘出隐含的、未知的、对决策有潜在的关系、模型和趋势,并用这些知识和规则建立用于决策支持的模型,提供预测性决策支持的方法、工具和过程,这就是数据挖掘。 它是利用...
  • 数据仓库(六)之数据质量篇

    万次阅读 多人点赞 2018-09-26 23:04:51
    数据质量的高低代表了该数据满足数据消费者期望的程度,这种程度基于他们对数据的使用预期。数据质量必须是可测量的,把测量的结果转化为可以理解的和可重复的数字,使我们能够在不同对象之间和跨越不同时间进行比较...
  • 数据处理是对纷繁复杂的海量数据价值的提炼,而其中最有价值的地方在于预测性分析,即可以通过数据可视化、统计模式识别、数据描述数据挖掘形式帮助数据科学家更好的理解数据,根据数据挖掘的结果得出预测性决策。...
  • 海量数据的存储和访问解决方案

    万次阅读 2016-05-24 16:34:28
    随着互联网应用的广泛普及,海量数据的存储和访问成为了系统设计的瓶颈问题。对于一个大型的互联网应用,每天几十亿的PV无疑对数据库造成了相当高的负载。对于系统的稳定性和扩展性造成了极的问题。通过数据切分来...
  • 下面的代码无法复制粘贴直接使用,主要记录...2.当Excel内有大批数据时,如何优化校验这个步骤。 我的处理方法是: * Excel性能优化方法 * 将Excel数据进行划分处理 * 1.根据某一列的属性,把Excel数据划分成...
  • Impala高性能探秘之HDFS数据访问

    千次阅读 2017-07-11 20:08:19
    Impala是一个高性能的OLAP引擎,Impala本身只是一个OLAP-SQL引擎,它访问数据存储在第三方引擎中,第三方引擎包括HDFS、Hbase、kudu。对于HDFS上的数据,Impala支持多种文件格式,目前可以访问Parquet、TEXT、avro...
  • 一、什么是数据挖掘 数据挖掘(Data Mining),也叫数据开采、数据采掘等,是从大量的、不完全的、有噪声的、模糊的、随机的实际应用数据中,自动提取隐含在其中的、人们事先不知道的,但又是潜在有用的信息的过程...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 603,200
精华内容 241,280
关键字:

数据访问量大的描述