bt365体育投注.主頁欢迎您!!

    <acronym id="zvmrr"></acronym>
    <td id="zvmrr"></td>
  • <tr id="zvmrr"><label id="zvmrr"></label></tr>
  • <acronym id="zvmrr"></acronym>
  • 白菜1031

    白菜1031 查看完整档案

    北京编辑河北工程大学  |  通信工程 编辑北京酷炫网络  |  php开发 编辑 github.com/wangsai1031 编辑
    编辑

    I hear and I forget.
    I see and I remember.
    I do and I understand.
    闻之不若见之,见之不若知之,知之不若行之。

    个人动态

    白菜1031 发布了文章 · 11月4日

    MySQL Explain命令详解:type列详解及案例分析

    Explain 命令中的 type 列,显示MySQL查询所使用的 关联类型(Join Types) 或者 访问类型,它表明 MySQL决定如何查找表中符合条件的行
    常见访问类型性能由最差到最优依次为:ALL < index < range < index_subquery < unique_subquery < index_merge < ref_or_null < fulltext < ref < eq_ref < const < system

    0、测试环境简述

    本文 MySQL 实例版本为 5.7,表存储引擎为 InnoDB

    数据库 t 中有两张表 useruser_captcha,每张表中有2W+条数据,下面是两张表的建表语句(表结构只为满足实验要求,没有实际业务逻辑参考价值):

    user 表

    • id 字段是主键
    • email 字段建立了唯一索引
    • phonecountry_code 字段组成联合唯一索引
    • birth_yeargender 字段组成联合普通索引
    • nickname 字段前10个字符建立了普通索引
    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `nickname` varchar(255) DEFAULT NULL,
      `country_code` smallint(6) unsigned NOT NULL DEFAULT '0',
      `phone` varchar(12) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
      `email` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
      `gender` tinyint(4) DEFAULT NULL,
      `birth_year` smallint(11) unsigned DEFAULT NULL,
      `created_at` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `unq_phone_country_code` (`phone`,`country_code`) USING BTREE,
      UNIQUE KEY `unq_email` (`email`),
      KEY `idx_birth_year_gender` (`birth_year`,`gender`) USING BTREE,
      KEY `idx_nickname` (`nickname`(10))
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

    user_captcha 表

    • id 字段是主键
    • user_id 字段建立了唯一索引,可以为空
    • receiver 字段建立了唯一索引
    CREATE TABLE `user_captcha` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_id` int(11) unsigned DEFAULT NULL,
      `code` char(6) COLLATE utf8_unicode_ci NOT NULL COMMENT '验证码',
      `retry_times` int(11) NOT NULL COMMENT '重试次数',
      `last_request_at` int(11) unsigned DEFAULT NULL COMMENT '最后请求时间',
      `receiver` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL COMMENT '接收者(手机号或邮箱)',
      `created_at` int(11) NOT NULL,
      `expired_at` int(11) NOT NULL COMMENT '过期时间',
      PRIMARY KEY (`id`),
      UNIQUE KEY `unq_receiver` (`receiver`) USING BTREE,
      UNIQUE KEY `unique_user` (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

    1. ALL

    全表扫描,通常意味着MySQL必须从头到尾扫描整张表,去查找匹配的行的行,性能极差。
    但是,如果在查询里使用了 LIMIT n,虽然 type 依然是 ALL,但是MySQL只需要扫描到符合条件的前 n 行数据,就会停止继续扫描

    • 查询昵称中带 字的用户数据,因为使用了前缀模糊匹配,不能命中索引,会导致全表扫描
    mysql> EXPLAIN SELECT * FROM `user` WHERE `nickname` LIKE '%雪%' LIMIT 1 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 22748
         filtered: 11.11
            Extra: Using where
    • 查询根据用户id可以被10整除的用户数据。因为在 = 前的索引列上进行了表达式运算,不能命中索引,会全表扫描。
    mysql> EXPLAIN SELECT * FROM `user` WHERE id%10=0 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 22293
         filtered: 100.00
            Extra: Using where
    1 row in set, 1 warning (0.01 sec)
    • 查询手机号是 18888888888 的用户数据,由于数据表中 phone 字段是字符串类型,而查询时使用了数字类型,会触发隐式类型转换,不会命中索引,因此会全表扫描。
    mysql> EXPLAIN SELECT * FROM `user` WHERE phone=18888888888 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: ALL
    possible_keys: unq_phone_country_code
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 22293
         filtered: 10.00
            Extra: Using where

    2. index

    indexALL 一样,也会进行全表扫描,只是MySQL会按索引次序进行全表扫描,而不是直接扫描行数据。它的主要优点是避免了排序;最大的缺点是要承担按索引次序读取整个表的开销。若是按随机次序访问行,开销将会非常大。

    • 根据出生年分组去重,查询用户数据。
    mysql> EXPLAIN SELECT * FROM `user` GROUP BY `birth_year` \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: index
    possible_keys: idx_birth_year_gender
              key: idx_birth_year_gender
          key_len: 5
              ref: NULL
             rows: 22748
         filtered: 100.00
            Extra: NULL

    如果在 Extra 列中看到 Using index,说明MySQL正在使用覆盖索引,索引的数据中包含了查询所需的所有字段,因此只需要扫描索引树就能够完成查询任务。它比按索引次序全表扫描的开销要少很多,因为索引树的大小通常要远小于全表数据。

    • 根据出生年分组,查询不同年份出生的用户个数,这里用到了覆盖索引。
    mysql> EXPLAIN SELECT `birth_year`,COUNT(*) FROM `user` GROUP BY `birth_year`\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: index
    possible_keys: idx_birth_year_gender
              key: idx_birth_year_gender
          key_len: 5
              ref: NULL
             rows: 22748
         filtered: 100.00
            Extra: Using index
    • 查询用户的id、性别、出生年数据,由于 idx_birth_year_gender 索引中包含 birth_yeargender字段,而 InnoDB的所有索引都包含id字段,不需要回表查询其他数据,因此也能用到覆盖索引。
    mysql> EXPLAIN SELECT `id`,`birth_year`,`gender` FROM `user` LIMIT 10 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: index
    possible_keys: NULL
              key: idx_birth_year_gender
          key_len: 5
              ref: NULL
             rows: 22748
         filtered: 100.00
            Extra: Using index
    • 查询表数据总条数,查询数据条数时,InnoDB存储引擎会自动选择最短的索引,通过遍历该索引,就可以计算出数据总条数,不需要回表查询其他数据,因此也能用到覆盖索引。
    mysql> EXPLAIN SELECT COUNT(*) FROM user \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: index
    possible_keys: NULL
              key: idx_birth_year_gender
          key_len: 5
              ref: NULL
             rows: 22748
         filtered: 100.00
            Extra: Using index

    3. range

    范围扫描,就是一个有范围限制的索引扫描,它开始于索引里的某一点,返回匹配这个范围值的行。range 比全索引扫描更高效,因为它用不着遍历全部索引。

    范围扫描分为以下两种情况:

    1. 范围条件查询:在 WHERE 子句里带有 BETWEEN><>=<= 的查询。
    2. 多个等值条件查询:使用 IN()OR ,以及使用 like 进行前缀匹配模糊查询。
    • 查询 id >= 1000id < 2000 的用户数据。
    mysql> EXPLAIN SELECT * FROM `user` WHERE `id`>=1000 AND `id`<2000 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: range
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 4
              ref: NULL
             rows: 8
         filtered: 100.00
            Extra: Using where
    • 查询 90后 的用户数据。
    mysql> EXPLAIN SELECT * FROM `user` WHERE `birth_year` BETWEEN 1990 AND 1999 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: range
    possible_keys: idx_birth_year_gender
              key: idx_birth_year_gender
          key_len: 3
              ref: NULL
             rows: 150
         filtered: 100.00
            Extra: Using index condition
    • 查询昵称以 字开头的用户数据。
    mysql> EXPLAIN SELECT * FROM `user` WHERE `nickname` LIKE '雪%' \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: range
    possible_keys: idx_nickname
              key: idx_nickname
          key_len: 43
              ref: NULL
             rows: 30
         filtered: 100.00
            Extra: Using where
    • 分别使用 IN()OR 两种方式查询出生年份在 1990,2000,2010 的用户数据。
    mysql> EXPLAIN SELECT * FROM `user` WHERE `birth_year` IN (1990,2000,2010) \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: range
    possible_keys: idx_birth_year_gender
              key: idx_birth_year_gender
          key_len: 3
              ref: NULL
             rows: 41
         filtered: 100.00
            Extra: Using index condition
    
    mysql> EXPLAIN SELECT * FROM `user` WHERE `birth_year`=1990 OR `birth_year`=2000 OR `birth_year`=2010 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: range
    possible_keys: idx_birth_year_gender
              key: idx_birth_year_gender
          key_len: 3
              ref: NULL
             rows: 41
         filtered: 100.00
            Extra: Using index condition

    4. index_subquery

    index_subquery 替换了以下形式的子查询中的 eq_ref 访问类型,其中 key_column 是非唯一索引。

    value IN (SELECT key_column FROM single_table WHERE some_expr)

    index_subquery 只是一个索引查找函数,它可以完全替换子查询,提高查询效率。

    大多数情况下,使用SELECT子查询时,MySQL查询优化器会自动将子查询优化为联表查询,因此 type 不会显示为 index_subquery

    • 在MySQL查询优化器判定可以对 SELECT 子查询进行优化的情况下,使用子查询联表查询的执行计划是相同的。
    mysql> EXPLAIN SELECT code FROM user_captcha LEFT JOIN user ON user.phone=user_captcha.receiver WHERE  phone like '1888%' \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: range
    possible_keys: unq_phone_country_code
              key: unq_phone_country_code
          key_len: 14
              ref: NULL
             rows: 44
         filtered: 100.00
            Extra: Using where; Using index
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: user_captcha
       partitions: NULL
             type: eq_ref
    possible_keys: unq_receiver
              key: unq_receiver
          key_len: 257
              ref: t.user.phone
             rows: 1
         filtered: 100.00
            Extra: Using index condition
    
    mysql> EXPLAIN SELECT code FROM user_captcha WHERE receiver IN (SELECT phone FROM `user` WHERE phone like '1888%') \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: range
    possible_keys: unq_phone_country_code
              key: unq_phone_country_code
          key_len: 14
              ref: NULL
             rows: 44
         filtered: 100.00
            Extra: Using where; Using index; LooseScan
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: user_captcha
       partitions: NULL
             type: eq_ref
    possible_keys: unq_receiver
              key: unq_receiver
          key_len: 257
              ref: t.user.phone
             rows: 1
         filtered: 100.00
            Extra: Using index condition
    • 我们可以通过在 UPDATE 语句的执行计划中看到 index_subquery
    mysql> EXPLAIN UPDATE user_captcha SET retry_times=1 WHERE receiver IN (SELECT phone FROM `user` WHERE phone like '1888%') \G
    *************************** 1. row ***************************
               id: 1
      select_type: UPDATE
            table: user_captcha
       partitions: NULL
             type: index
    possible_keys: NULL
              key: PRIMARY
          key_len: 4
              ref: NULL
             rows: 22433
         filtered: 100.00
            Extra: Using where
    *************************** 2. row ***************************
               id: 2
      select_type: DEPENDENT SUBQUERY
            table: user
       partitions: NULL
             type: index_subquery
    possible_keys: unq_phone_country_code
              key: unq_phone_country_code
          key_len: 14
              ref: func
             rows: 1
         filtered: 100.00
            Extra: Using where; Using index

    5. unique_subquery

    unique_subqueryindex_subquery 类似,它替换了以下形式的子查询中的 eq_ref 访问类型,其中 primary_key 可以是主键索引或唯一索引。

    value IN (SELECT primary_key FROM single_table WHERE some_expr)

    unique_subquery 只是一个索引查找函数,它可以完全替换子查询,提高查询效率。

    • 由于MySQL查询优化器会对 SELECT 子查询进行优化,我们可以在 UPDATE 语句的执行计划中看到 unique_subquery
    mysql> EXPLAIN UPDATE user_captcha SET retry_times=1 WHERE user_id IN (SELECT id FROM `user` WHERE phone like '%1888%') \G
    *************************** 1. row ***************************
               id: 1
      select_type: UPDATE
            table: user_captcha
       partitions: NULL
             type: index
    possible_keys: NULL
              key: PRIMARY
          key_len: 4
              ref: NULL
             rows: 22433
         filtered: 100.00
            Extra: Using where
    *************************** 2. row ***************************
               id: 2
      select_type: DEPENDENT SUBQUERY
            table: user
       partitions: NULL
             type: unique_subquery
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 4
              ref: func
             rows: 1
         filtered: 11.11
            Extra: Using where

    6. index_merge

    表示出现了索引合并优化,通常是将多个索引字段的范围扫描合并为一个。包括单表中多个索引的交集,并集以及交集之间的并集,但不包括跨多张表和全文索引。
    这种优化并非必然发生的,当查询优化器判断优化后查询效率更优时才会进行优化。详情可查看官方文档

    • 查询出生年在 1990,2000,2010 年,或 id<1000 的用户数据
    mysql> EXPLAIN SELECT * FROM `user` WHERE `birth_year` IN (1990,2000,2010) OR `id`<1000 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: index_merge
    possible_keys: PRIMARY,idx_birth_year_gender
              key: idx_birth_year_gender,PRIMARY
          key_len: 3,4
              ref: NULL
             rows: 46
         filtered: 100.00
            Extra: Using sort_union(idx_birth_year_gender,PRIMARY); Using where
    • 查询手机号以 183 开头或 出生年 大于1990 年的用户数据
    mysql> EXPLAIN SELECT * FROM `user` WHERE `phone` like '183%' OR `birth_year`>1990 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: index_merge
    possible_keys: unq_phone_country_code,idx_birth_year_gender
              key: unq_phone_country_code,idx_birth_year_gender
          key_len: 14,3
              ref: NULL
             rows: 1105
         filtered: 100.00
            Extra: Using sort_union(unq_phone_country_code,idx_birth_year_gender); Using where
    • 查询出生年在 1990 年或 id=1000 的用户数据
    mysql> EXPLAIN SELECT * FROM `user` WHERE `birth_year`=1990 OR `id`=1000 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: index_merge
    possible_keys: PRIMARY,idx_birth_year_gender
              key: idx_birth_year_gender,PRIMARY
          key_len: 3,4
              ref: NULL
             rows: 11
         filtered: 100.00
            Extra: Using sort_union(idx_birth_year_gender,PRIMARY); Using where
    1 row in set, 1 warning (0.01 sec)

    7. ref_or_null

    ref_or_nullref 类似,但是MySQL必须对包含 NULL 值的行就行额外搜索。

    • 查找昵称是 空字符串 ''NULL 的用户数据
    mysql> EXPLAIN SELECT * FROM `user` WHERE `nickname`='' OR `nickname` IS NULL \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: ref_or_null
    possible_keys: idx_nickname
              key: idx_nickname
          key_len: 43
              ref: const
             rows: 2
         filtered: 100.00
            Extra: Using where

    8. fulltext

    命中全文索引时 typefulltext

    9. ref

    索引访问(有时也叫做索引查找),它返回所有匹配某个单个值的行。然而,它可能会找到多个符合条件的行,因此,它是查找和扫描的混合体。此类索引访问只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。把它叫做 ref 是因为索引要跟某个参考值相比较。这个参考值或者是一个常数,或者是来自多表查询前一个表里的结果值。

    • 查找出生年在2000年的用户数据。
    mysql> EXPLAIN SELECT * FROM `user` WHERE `birth_year`=2000 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: ref
    possible_keys: idx_birth_year_gender
              key: idx_birth_year_gender
          key_len: 3
              ref: const
             rows: 30
         filtered: 100.00
            Extra: NULL
    • 查找电话号码是 18888888888 的用户数据,phonecountry_count 联合组成唯一索引 unq_phone_country_codephone 是唯一索引 unq_phone_country_code 的非唯一性前缀。
    mysql> EXPLAIN SELECT * FROM `user` WHERE `phone`='18888888888'\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: ref
    possible_keys: unq_phone_country_code
              key: unq_phone_country_code
          key_len: 14
              ref: const
             rows: 1
         filtered: 100.00
            Extra: NULL

    10. eq_ref

    当进行等值联表查询时,联结字段命中主键索引或唯一的非空索引时,将使用 eq_ref
    (《高性能MySQL(第3版)》一书中说"使用主键或唯一索引查询时会用 eq_ref",经过反复测试,并查阅MySQL5.65.7版本的官方文档,实际上使用主键或唯一索引进行等值条件查询时 type 会显示 const,《高性能MySQL(第3版)》这里应该是只适用于5.5之前的版本。)

    • user_captcha 表作为主表,LEFT JOINuser 表查询用户数据,因为user表中id字段是主键,所以第二行的 user 表的 typeeq_ref
    mysql> EXPLAIN SELECT * FROM `user_captcha` LEFT JOIN `user` ON `user`.`id`=`user_captcha`.`user_id` \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user_captcha
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 22433
         filtered: 100.00
            Extra: NULL
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: eq_ref
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 4
              ref: t.user_captcha.user_id
             rows: 1
         filtered: 100.00
            Extra: Using where
    2 rows in set, 1 warning (0.01 sec)
    • 当使用 user 表作为主表,LEFT JOINuser_captcha 表时,因为 user_captcha 表中 user_id 字段与 device_id 组成联合唯一索引,user_id 并非独立的唯一索引,所以第二行的 user_captcha 表的 typeref,而并非 eq_ref
    mysql> EXPLAIN SELECT * FROM `user` LEFT JOIN `user_captcha` ON `user`.`id`=`user_captcha`.`user_id` \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 22999
         filtered: 100.00
            Extra: NULL
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: user_captcha
       partitions: NULL
             type: ref
    possible_keys: unique_user
              key: unique_user
          key_len: 5
              ref: t.user.id
             rows: 1
         filtered: 100.00
            Extra: Using where

    11. const

    MySQL 知道查询最多只能匹配到一条符合条件的记录。因为只有一行,所以优化器可以将这一行中的列中的值视为常量。const 表查询非常快,因为它们只读取一次数据行。
    通常使用主键或唯一索引进行等值条件查询时会用 const。

    • 使用主键查询用户数据
    mysql> EXPLAIN SELECT * FROM `user` WHERE `id`=120 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: const
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 4
              ref: const
             rows: 1
         filtered: 100.00
            Extra: NULL
    • 使用唯一索引查询用户数据
    mysql> EXPLAIN SELECT * FROM `user` WHERE `email`='54222806@qq.com' \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: const
    possible_keys: unq_email
              key: unq_email
          key_len: 258
              ref: const
             rows: 1
         filtered: 100.00
            Extra: NULL
    • 使用联合唯一索引查询用户数据
    mysql> EXPLAIN SELECT * FROM `user` WHERE `country_code`=86 AND `phone`='18888888888' \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: user
       partitions: NULL
             type: const
    possible_keys: unq_phone_country_code
              key: unq_phone_country_code
          key_len: 16
              ref: const,const
             rows: 1
         filtered: 100.00
            Extra: NULL

    12. system

    官方文档原文是:The table has only one row (= system table). This is a special case of the const join type.
    该表只有一行(=系统表)。这是 const 关联类型的特例。

    • 从系统库mysql的系统表 proxies_priv 里查询数据,这里的数据在Mysql服务启动时候已经加载在内存中,不需要进行磁盘IO。
    mysql> EXPLAIN SELECT * FROM `mysql`.`proxies_priv` \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: proxies_priv
       partitions: NULL
             type: system
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 1
         filtered: 100.00
            Extra: NULL
    参考:
    《高性能MySQL(第3版)》
    MySQL 5.7官方文档:explain-join-types
    查看原文

    赞 7 收藏 6 评论 0

    白菜1031 赞了问题 · 11月1日

    发布文章被隐藏,可否告知具体原因方便修改

    查看可能被隐藏的原因,仔细查阅但是不清楚不符合的地方。
    image.png

    实现一个Vue自定义指令懒加载该文章第一次发布没被隐藏,之后无论怎么修改都会显示隐藏。希望可以有一个友好的提示和修改建议。

    关注 1 回答 0

    白菜1031 发布了文章 · 11月1日

    MySQL索引都有哪些分类?

    〇、MySQL索引都有哪些分类?

    按数据结构分类可分为:B+tree索引、Hash索引、Full-text索引
    按物理存储分类可分为:聚簇索引、二级索引(辅助索引)
    按字段特性分类可分为:主键索引、普通索引、前缀索引
    按字段个数分类可分为:单列索引、联合索引(复合索引、组合索引)

    一、按数据结构分类

    MySQL索引按数据结构分类可分为:B+tree索引、Hash索引、Full-text索引

    -InnoDBMyISAMMemory
    B+tree索引
    Hash索引××
    Full-text索引√(MySQL5.6+)×
    注:InnoDB实际上也支持Hash索引,但是InnoDB中Hash索引的创建由存储引擎引擎自动优化创建,不能人为干预是否为表创建Hash索引

    B+tree 是MySQL中被存储引擎采用最多的索引类型。B+tree 中的 B 代表平衡(balance),而不是二叉(binary),因为 B+tree 是从最早的平衡二叉树演化而来的。下面展示B+tree数据结构与其他数据结构的对比。

    1. B+tree与B-tree的对比

    B-tree 中的每个节点根据实际情况可以包含多条数据信息和子节点,如下图所示为一个3阶的B-tree:

    B-tree结构(图片来源于网络)
    (图片来源于网络)

    相对于B-tree,B+tree有以下两点不同:

    • B+tree 非叶子节点只存储键值信息, 数据记录都存放在叶子节点中。而B-tree的非叶子节点也存储数据。所以B+tree单个节点的数据量更小,在相同的磁盘I/O次数下,能查询更多的节点。
    • B+tree 所有叶子节点之间都采用单链表连接。适合MySQL中常见的基于范围的顺序检索场景,而B-tree无法做到这一点。

    B+tree结构(图片来源于网络)

    2. B+tree与红黑树的对比

    红黑树结构(图片来源于网络)
    (图片来源于网络)

    红黑树是一种弱平衡二叉查找树。通过对任何一条从根到叶子的路径上各个节点着色的方式的限制,红黑树确保没有一条路径会比其他路径长出两倍

    对于有N个叶子结点的 B+tree,其搜索复杂度为 O(logdN) ,其中 d(degree) 为 B+tree 的度,表示节点允许的最大子节点个数为d个,在实际应用当中,d值一般是大于100的,即使数据量达到千万级别时B+tree的高度依然维持在3-4左右,保证了3-4次磁盘I/O操作就能查询到目标数据。

    红黑树是二叉树,节点子节点个数为两个,意味着其搜索复杂度为 O(logN),树的高度也会比 B+tree 高出不少,因此红黑树检索到目标数据所需经历的磁盘I/O次数更多。

    3. B+tree与Hash的对比

    Hash 索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位,不像B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 B-Tree 索引。虽然 Hash 索引效率高,但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端,主要有以下这些。

    Hash 索引仅仅能满足 = , IN<=>(表示NULL安全的等价) 查询,不能使用范围查询。

    由于 Hash 索引比较的是进行 Hash 运算之后的 Hash值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的 Hash算法处理之后的 Hash 值的大小关系,并不能保证和Hash运算前完全一样。

    Hash 索引无法适用数据的排序操作。

    由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash值,而且Hash值的大小关系并不一定和 Hash运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算;

    Hash 索引不能利用部分索引键查询。

    对于组合索引,Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值,而不是单独计算 Hash值,所以通过组合索引的前面一个或几个索引键进行查询的时候,Hash 索引也无法被利用。

    Hash 索引依然需要回表扫描。

    Hash 索引是将索引键通过 Hash 运算之后,将 Hash运算结果的 Hash值和所对应的行指针信息存放于一个 Hash 表中,由于不同索引键可能存在相同 Hash 值,所以即使取满足某个 Hash 键值的数据的记录条数,也无法从 Hash索引中直接完成查询,还是要通过访问表中的实际数据进行相应的比较,并得到相应的结果。

    Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

    选择性比较低的索引键,如果创建 Hash 索引,那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦,会浪费多次表数据的访问,而造成整体性能低下

    由于范围查询是MySQL数据库查询中常见的场景,Hash表不适合做范围查询,它更适合做等值查询。另外Hash表还存在Hash函数选择和Hash值冲突等问题。因此,B+tree索引要比Hash表索引有更广的适用场景。

    二、按物理存储分类

    MySQL索引按叶子节点存储的是否为完整表数据分为:聚簇索引、二级索引(辅助索引)。全表数据存储在聚簇索引中,聚簇索引以外的其他索引叫做二级索引,也叫辅助索引。

    1. 聚簇索引

    聚簇索引的每个叶子节点存储了一行完整的表数据,叶子节点间按id列递增连接,可以方便地进行顺序检索。

    聚簇索引B+tree示意图(图片来源于网络)
    (图片来源于网络)

    InnoDB表要求必须有聚簇索引,默认在主键字段上建立聚簇索引,在没有主键字段的情况下,表的第一个非空的唯一索引将被建立为聚簇索引,在前两者都没有的情况下,InnoDB将自动生成一个隐式的自增id列,并在此列上建立聚簇索引。

    以MyISAM为存储引擎的表不存在聚簇索引。

    MyISAM表中的主键索引和非主键索引的结构是一样的,索引的叶子节点不存储表数据,存放的是表数据的地址。所以,MyISAM表可以没有主键。

    MyISAM索引B+tree示意图(图片来源于网络)
    (图片来源于网络)

    MyISAM表的数据和索引是分开存储的。MyISAM表的主键索引和非主键索引的区别仅在于主键索引的B+tree上的key必须符合主键的限制,非主键索引B+tree上的key只要符合相应字段的特性就可以了。

    2. 二级索引

    二级索引的叶子节点并不存储一行完整的表数据,而是存储了聚簇索引所在列的值。

    二级索引B+tree示意图(图片来源于网络)
    (图片来源于网络)

    回表查询

    由于二级索引的叶子节点不存储完整的表数据,索引当通过二级索引查询到聚簇索引列值后,还需要回到聚簇索引也就是表数据本身进一步获取数据。

    回表查询示意图(图片来源于网络)
    (图片来源于网络)

    回表查询 需要额外的 B+tree 搜索过程,必然增大查询耗时。

    需要注意的是,通过二级索引查询时,回表不是必须的过程,当SELECT的所有字段在单个二级索引中都能够找到时,就不需要回表,MySQL称此时的二级索引为覆盖索引或触发了索引覆盖
    可以用Explain命令查看SQL语句的执行计划,执行计划的Extra字段中若出现Using index,表示查询触发了索引覆盖

    三、按字段特性分类

    MySQL索引按字段特性分类可分为:主键索引、普通索引、前缀索引

    1. 主键索引

    建立在主键上的索引被称为主键索引,一张数据表只能有一个主键索引,索引列值不允许有空值,通常在创建表时一起创建。

    2. 唯一索引

    建立在UNIQUE字段上的索引被称为唯一索引,一张表可以有多个唯一索引,索引列值允许为空,列值中出现多个空值不会发生重复冲突。

    3. 普通索引

    建立在普通字段上的索引被称为普通索引

    4. 前缀索引

    前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引,而不是在整个字段上建索引。前缀索引可以建立在类型为char、varchar、binary、varbinary的列上,可以大大减少索引占用的存储空间,也能提升索引的查询效率。

    四、按索引字段个数分类

    MySQL索引按字段个数分类可分为:单列索引、联合索引(复合索引、组合索引)

    1. 单列索引

    建立在单个列上的索引被称为单列索引。

    2. 联合索引(复合索引、组合索引)

    建立在多个列上的索引被称为联合索引,又叫复合索引、组合索引。

    查看原文

    赞 10 收藏 6 评论 1

    白菜1031 发布了文章 · 10月21日

    MySQL的多版本并发控制(MVCC)

    一、什么是多版本并发控制

    多版本并发控制技术的英文全称是 Multiversion Concurrency Control,简称 MVCC

    多版本并发控制(MVCC) 是通过保存数据在某个时间点的快照来实现并发控制的。也就是说,不管事务执行多长时间,事务内部看到的数据是不受其它事务影响的,根据事务开始的时间不同,每个事务对同一张表,同一时刻看到的数据可能是不一样的。

    简单来说,多版本并发控制 的思想就是保存数据的历史版本,通过对数据行的多个版本管理来实现数据库的并发控制。这样我们就可以通过比较版本号决定数据是否显示出来,读取数据的时候不需要加锁也可以保证事务的隔离效果。

    可以认为 多版本并发控制(MVCC) 是行级锁的一个变种,但是它在很多情况下避免了加锁操作,因此开销更低。虽然实现机制有所不同,但大都实现了非阻塞的读操作,写操作也只锁定必要的行。

    MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑,它们一般都同时实现了多版本并发控制(MVCC)。不仅是MySQL,包括Oracle、PostgreSQL等其他数据库系统也都实现了MVCC,但各自的实现机制不尽相同,因为MVCC没有一个统一的实现标准,典型的有乐观(optimistic)并发控制悲观(pessimistic)并发控制

    二、多版本并发控制解决了哪些问题

    1. 读写之间阻塞的问题

    通过 MVCC 可以让读写互相不阻塞,即读不阻塞写,写不阻塞读,这样就可以提升事务并发处理能力。

    提高并发的演进思路:

    • 普通锁,只能串行执行;
    • 读写锁,可以实现读读并发;
    • 数据多版本并发控制,可以实现读写并发。

    2. 降低了死锁的概率

    因为 InnoDB 的 MVCC 采用了乐观锁的方式,读取数据时并不需要加锁,对于写操作,也只锁定必要的行。

    3. 解决一致性读的问题

    一致性读也被称为快照读,当我们查询数据库在某个时间点的快照时,只能看到这个时间点之前事务提交更新的结果,而不能看到这个时间点之后事务提交的更新结果。

    三、快照读与当前读

    快照读(SnapShot Read) 是一种一致性不加锁的读,是InnoDB并发如此之高的核心原因之一

    这里的一致性是指,事务读取到的数据,要么是事务开始前就已经存在的数据,要么是事务自身插入或者修改过的数据

    不加锁的简单的 SELECT 都属于快照读,例如:

    `SELECT * FROM t WHERE id=1`
    

    快照读 相对应的则是 当前读当前读就是读取最新数据,而不是历史版本的数据。加锁的 SELECT 就属于当前读,例如:

    SELECT * FROM t WHERE id=1 LOCK IN SHARE MODE;
    
    SELECT * FROM t WHERE id=1 FOR UPDATE;
    

    四、InnoDB 的 MVCC 是如何工作的

    1. InnoDB 是如何存储记录的多个版本的

    事务版本号

    每开启一个事务,我们都会从数据库中获得一个事务 ID(也就是事务版本号),这个事务 ID 是自增长的,通过 ID 大小,我们就可以判断事务的时间顺序。

    行记录的隐藏列

    InnoDB 的叶子段存储了数据页,数据页中保存了行记录,而在行记录中有一些重要的隐藏字段:

    • DB_ROW_ID:6-byte,隐藏的行 ID,用来生成默认聚簇索引。如果我们创建数据表的时候没有指定聚簇索引,这时 InnoDB 就会用这个隐藏 ID 来创建聚集索引。采用聚簇索引的方式可以提升数据的查找效率。
    • DB_TRX_ID:6-byte,操作这个数据的事务 ID,也就是最后一个对该数据进行插入或更新的事务 ID。
    • DB_ROLL_PTR:7-byte,回滚指针,也就是指向这个记录的 Undo Log 信息。

    InnoDB数据记录隐藏列

    Undo Log

    InnoDB 将行记录快照保存在了 Undo Log 里,我们可以在回滚段中找到它们,如下图所示:

    Undo Log回滚历史记录

    从图中能看到回滚指针将数据行的所有快照记录都通过链表的结构串联了起来,每个快照的记录都保存了当时的 db_trx_id,也是那个时间点操作这个数据的事务 ID。这样如果我们想要找历史快照,就可以通过遍历回滚指针的方式进行查找。

    2. 在 可重复读(REPEATABLE READ) 隔离级别下, InnoDB 的 MVCC 是如何工作的

    查询(SELECT)

    InnoDB 会根据以下两个条件检查每行记录:

    1. InnoDB只查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号),这样可以确保事务读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或者修改过的
    2. 行的删除版本要么未定义,要么大于当前事务版本号。这可以确保事务读取到的行,在事务开始之前未被删除

    只有符合上述两个条件的记录,才能返回作为查询结果。

    插入(INSERT)

    InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

    删除(DELETE)

    InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
    删除在内部被视为更新,行中的一个特殊位会被设置为已删除。

    更新(UPDATE)

    InnoDB为插入一行新记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识。

    五、总结

    多版本并发控制(MVCC) 在一定程度上实现了读写并发,它只在 可重复读(REPEATABLE READ)提交读(READ COMMITTED) 两个隔离级别下工作。其他两个隔离级别都和 MVCC 不兼容,因为 未提交读(READ UNCOMMITTED),总是读取最新的数据行,而不是符合当前事务版本的数据行。而 可串行化(SERIALIZABLE) 则会对所有读取的行都加锁。

    行锁,并发,事务回滚等多种特性都和MVCC相关。

    参考:
    MySQL5.7文档:innodb-multi-versioning
    《高性能MySQL》
    查看原文

    赞 14 收藏 9 评论 2

    白菜1031 赞了问题 · 10月19日

    解决crontab 如果日期和星期冲突时,会如何执行?

    比如 指定

    * * 22 8 3 

    时,每周三执行,但是8月22日不是星期三,会如何执行?

    关注 3 回答 2

    白菜1031 赞了回答 · 7月8日

    解决Laravel where time="0000-00-00" order by id 查不出结果

    相比关掉严格模式,我更加建议你使用 Carbon 把时间处理一下,或者 Laravel 中的 Date 类。

    Model::where(Carbon::parse('0000-00-00'));
    // 或者
    Model::where(Carbon::parse('0000-00-00')->toDateTimeString());

    关注 2 回答 2

    白菜1031 发布了文章 · 7月5日

    MySQL逻辑架构 - SQL语句的执行都经历了哪些步骤

    MySQL逻辑架构 - SQL语句的执行都经历了哪些步骤

    从 MySQL 架构来理解,我们可以把 MySQL 拆解成几个零件,如下图所示

    MySQL 逻辑架构模型

    大体来说,MySQL 可以分为 Server层存储引擎层两部分。

    Server 层包括连接器、查询缓存、分析器、优化器、执行器,涵盖MySQL的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能也在这一层实现,包括 存储过程、触发器、视图等。

    存储引擎层负责数据的存储和提取。包括 MySQL 常见的存储引擎,包括 MyISAM、InnoDB 和 Memory 等,最常用的是 InnoDB,也是现在 MySQL 的默认存储引擎。
    存储引擎也可以在创建表的时候手动指定,使用如下语句:

    CREATE TABLE t (i INT) ENGINE = <Storage Engine>;

    不同存储引擎的表数据存取方式不同,支持的功能也不同。
    从图中可以看出,不同的存储引擎共用一个Server层,也就是从连接器到执行器的部分。

    连接器

    首先需要在 MySQL 客户端登陆才能使用,所以需要一个连接器来连接用户和 MySQL 数据库,我们一般是使用

    mysql -h<host> -P<port> -u<用户名> -p[<密码>]

    来进行 MySQL 登陆,和服务端建立连接。

    虽然密码可以直接跟在 -p 后面写在命令行中,但这样可能会导致你的密码泄露。强烈建议不输入密码,直接运行命令,然后再再交互对话框中输入密码。

    在完成 TCP 握手 后,连接器会根据你输入的用户名和密码验证你的登录身份。如果用户名或者密码错误,MySQL 就会提示 Access denied for user,然后客户端程序结束执行。如果用户名密码认证通过,连接器会到权限表里面查出你拥有的权限。之后,这个连接里面的权限判断逻辑,都将依赖于此时读到的权限。

    这就意味着,一个用户成功建立连接后,即使你用管理员账号对这个用户的权限做了修改,也不会影响已经存在连接的权限。修改完成后,只有再新建的连接才会使用新的权限设置。

    连接完成后,如果没有后续的动作,这个连接就处于空闲状态,可以使用 show processlist 命令中看到它。

    mysql> show processlist;
    +--------+-------------+---------------------+--------+---------+------+----------+------------------+
    | Id     | User        | Host                | db     | Command | Time | State    | Info             |
    +--------+-------------+---------------------+--------+---------+------+----------+------------------+
    | 214416 | master      | 124.126.130.4:29734 | db_name | Sleep   |   13 |          | NULL             |
    | 214417 | master      | 124.126.130.4:29754 | db_name | Query   |    0 | starting | show processlist |
    +--------+-------------+---------------------+--------+---------+------+----------+------------------+
    2 rows in set (0.07 sec)

    其中的Command列显示为 Sleep 的这一行,就表示现在系统里面有一个空闲连接。客户端如果太长时间没动静,连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的,默认值是8小时。

    如果在连接被断开之后,客户端再次发送请求的话,就会收到一个错误提醒: Lost connection to MySQL server during query。这时候如果你要继续,就需要重连,然后再执行请求了。

    数据库里面,长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个。

    建立连接的过程通常是比较复杂的,所以建议在使用中要尽量减少建立连接的动作,也就是尽量使用长连接。

    但是全部使用长连接后,你可能会发现,有些时候MySQL占用内存涨得特别快,这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来,可能导致内存占用太大,被系统强行杀掉(OOM),从现象看就是MySQL异常重启了。

    怎么解决这个问题呢?你可以考虑以下两种方案。

    1. 定期断开长连接。使用一段时间,或者程序里面判断执行过一个占用内存的大查询后,断开连接,之后要查询再重连。
    2. 如果你用的是 MySQL 5.7 以上的版本,可以在每次执行一个比较大的操作后,通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证,但是会将连接恢复到刚刚创建完时的状态。

    查询缓存

    连接完成后,你就可以执行 SQL 语句了,这行逻辑就会来到第二步:查询缓存

    MySQL 在得到一个执行请求后,会首先去 查询缓存 中查找,是否执行过这条 SQL 语句,之前执行过的语句以及结果会以 key-value 对的形式,被直接放在内存中。key 是查询语句,value 是查询的结果。如果通过 key 能够查找到这条 SQL 语句,就直接返回 SQL 的执行结果。

    如果语句不在查询缓存中,就会继续后面的执行阶段。执行完成后,执行结果就会被放入查询缓存中。可以看到,如果查询命中缓存,MySQL 不需要执行后面的复杂操作,就可以直接返回结果,效率会很高。

    MySQL 查询缓存流程图

    但是大多数情况下,不建议使用查询缓存
    因为查询缓存的失效非常频繁,只要在 MySQL 中对某一张表执行了更新操作,那么这张表上的所有的查询缓存就会失效,对于更新频繁的数据表来说,查询缓存的命中率会非常低。除非你的业务就是有一张静态表,很长时间才会更新一次。比如,一个系统配置表,那这张表上的查询才适合使用查询缓存。

    好在MySQL也提供了这种“按需使用”的方式。你可以将参数 query_cache_type 设置成 DEMAND,这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句,可以用 SQL_CACHE 显式指定:

    select SQL_CACHE * from T where ID=10;

    需要注意的是,MySQL 8.0版本直接将查询缓存的整块功能删掉了。

    分析器

    如果没有命中查询缓存,就要开始真正执行 SQL 语句了。
    首先,MySQL 会根据你写的 SQL 语句进行解析,分析器会先做 词法分析,你输入的是由多个字符串和空格组成的一条 SQL 语句,MySQL 需要识别出里面的字符串是什么,代表什么。

    然后进行 语法分析,根据词法分析的结果, 语法分析器会根据语法规则,判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果 SQL 语句不正确,就会提示 You have an error in your SQL syntax

    优化器

    经过分析器的词法分析和语法分析后,这条 SQL 就合法了,MySQL 就知道你要做什么了。但是在执行前,还需要经过优化器的处理。

    优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序。比如你执行下面这样的语句,这个语句是执行两个表的join:

    select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;
    • 既可以先从表t1里面取出c=10的记录的ID值,再根据ID值关联到表t2,再判断t2里面d的值是否等于20。
    • 也可以先从表t2里面取出d=20的记录的ID值,再根据ID值关联到t1,再判断t1里面c的值是否等于10。

    这两种执行方法的逻辑结果是一样的,但是执行的效率会有不同,而优化器的作用就是决定选择使用哪一个方案。

    优化器会判断你使用了哪种索引,使用了何种连接,确定效率最高的执行方案。

    执行器

    MySQL 通过分析器知道了你的 SQL 语句是否合法,你想要做什么操作,通过优化器知道了该怎么做效率最高,然后就进入了执行阶段,开始执行这条 SQL 语句。

    开始执行的时候,MySQL 首先会判断你对要操作的表有没有执行这条语句的权限。

    • 如果没有权限,就会返回没有权限的错误(ERROR 1142 (42000): SELECT command denied to user 'foo'@'localhost' for table 'bar')。
    • 如果有权限,就打开表继续执行。

    打开表的时候,执行器就会根据表的引擎定义,去使用这个引擎提供的接口查询数据返回给客户端。

    至此,MySQL 对于一条语句的执行过程也就完成了。

    参考《MySQL实战45讲》丁奇
    查看原文

    赞 8 收藏 6 评论 0

    白菜1031 提出了问题 · 6月19日

    如何避免大数据量聚合查询拖垮Elasticsearch?

    今天一个小白的ES聚合查询少加了条件,原本只需要在当月索引内聚合,他对所有日期的索引进行了聚合查询,直接导致ES崩掉了。

    请问Es有没有设置可以避免这种情况发生,当查询所需要的资源大于某个值时,直接拒绝查询?

    关注 1 回答 0

    白菜1031 发布了文章 · 5月24日

    MySQL基础知识点整理 - 事务

    MySQL基础知识点整理 - 事务

    一、简介

    1. 什么是事务

    事务就是一组原子性的SQL查询,或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句,那么就执行该组查询。如果其中有任何一条语句因为崩溃或其他原因无法执行,那么所有的语句都不会执行。也就是说,事务内的语句,要么全部执行成功,要么全部执行失败。在整个过程中,无论事务是否成功完成,总能确保数据的完整性。

    2. 如何使用事务

    银行应用是解释事务必要性的一个经典例子。
    假设一个银行的数据库有两张表:支票(checking)表和储蓄(savings)表。现在要从用户的支票账户转移 200 元到他的储蓄账户,那么需要至少三个步骤:

    • 1.检查支票账户的余额高于 200 元。
    • 2.从支票账户余额中减去 200 元。
    • 3.在储蓄账户余额中增加 200 元。

    上述三个步骤的操作必须打包在一个事务中,任何一个步骤失败,则必须回滚所有的步骤。

    可以用 START TRANSACTION 语句开始一个事务,然后要么使用 COMMIT 提交事务将修改的数据持久保留,要么使用 ROLLBACK 撤销所有的修改。
    事务SQL的样本如下:

    /* 开始事务 */
    START TRANSACTION;
    /* 检查支票账户的余额高于 200 元 */
    SELECT balance FROM checking WHERE customer_id=10233276;
    /* 从支票账户余额中减去 200 元 */
    UPDATE checking SET balance=balance-200.00 WHERE customer_id=10233276;
    /* 在储蓄账户余额中增加 200 元 */
    UPDATE savings SET balance=balance+200.00 WHERE customer_id=10233276;
    /* 提交事务 */
    COMMIT;

    二、事务的 ACID 特性

    ACID表示原子性 (atomicity)、一致性(consistency)、隔离性(isolation)和持久性(durability)。一个运行良好的事务处理系统,必须具备这些标准特征。

    1. 原子性 (atomicity)

    一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚。对于一个事务来说,不可能只执行其中的一部分操作,这就是事务的原子性。

    2. 一致性(consistency)

    数据库总是从一个一致性的状态转换到另外一个一致性的状态。在前面的例子中,一致性确保了,即使在执行第三、四条语句之间时系统崩溃,支票账户中也不会损失200元,因为事务最终没有提交,所以事务中所做的修改也不会保存到数据库中。

    3. 隔离性(isolation)

    通常来说,一个事务所做的修改在最终提交以前,对其他事务是不可见的。在前面的例子中,当执行完第三条语句、第四条语句还未开始时,此时有另外一个账户汇总程序开始运行,则其看到的支票账户的余额并没有被减去200元。

    4. 持久性(durability)

    一旦事务提交,则其所做的修改就会永久保存到数据库中。此时即使系统崩溃,修改的数据也不会丢失。

    三、事务的隔离级别

    1. READ UNCOMMITTED(未提交读)

    READ UNCOMMITTED 级别,事务中的修改,即使没有提交,对其他事务也都是可见的。事务可以读取未提交的数据,这也被称为脏读(Dirty Read) 。这个级别会导致很多问题,从性能上来说,READ UNCOMMITTED 不会比其他的级别好太多,但却缺乏其他级别的很多好处,除非真的有非常必要的理由,在实际应用中一般很少使用。

    2. READ COMMITTED(提交读)

    大多数数据库系统的默认隔离级别都是 READ COMMITTED(但MysQL不是)。READ COMMITTED 满足前面提到的隔离性的简单定义:一个事务开始时,只能“看见”已经提交的事务所做的修改。换句话说,一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读(nonrepeatable read),因为两次执行同样的查询,可能会得到不一样的结果。

    3. REPEATABLE READ(可重复读)

    REPEATABLE READ 解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上,可重复读隔离级别还是无法解决另外一个幻读(Phantom Read)的问题。所谓幻读,指的是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行(Phantom Row)。InnoDBXtraDB 存储引擎通过多版本并发控制(MVCC, Multiversion Concurrency Control)解决了幻读的问题。
    可重复读是MySQL的默认事务隔离级别。

    4. SERIALIZABLE(可串行化)

    SERIALIZABLE 是最高的隔离级别。它通过强制事务串行执行,避免了前面说的幻读的问题。简单来说,SERIALIZABLE 会在读取的每一行数据上都加锁,所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别,只有在非常需要确保数据的一致性而且可以接受没有并发的情况下,才考虑采用该级别。

    四种隔离级别对比

    隔离级别脏读可能性不可重复读可能性幻读可能性加锁读
    READ UNCOMMITTED×
    READ COMMITTED××
    REPEATABLE READ×××
    SERIALIZABLE×××

    四、死锁

    1. 什么是死锁

    死锁是指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时,就可能会产生死锁。多个事务同时锁定同一个资源时,也会产生死锁。

    2. 死锁实例

    设想下面两个事务同时处理 StockPrice 表:

    • 事务1:
    START TRANSACTION;
    UPDATE StockPrice SET close=45.50 WHERE stock_id=4 and date='2002-05-01';
    UPDATE StockPrice SET close=19.80 WHERE stock_id=3 and date='2002-05-02';
    COMMIT;
    • 事务2
    START TRANSACTION;
    UPDATE StockPrice SET high=20.12 WHERE stock_id=3 and date='2002-05-02';
    UPDATE StockPrice SET high=47.20 WHERE stock_id=4 and date='2002-05-01';
    COMMIT;

    如果凑巧,两个事务都执行了第一条 UPDATE 语句,更新了一行数据,同时也锁定了该行数据,接着每个事务都尝试去执行第二条 UPDATE 语句,却发现该行已经被对方锁定,然后两个事务都等待对方释放锁,同时又持有对方需要的锁,则陷入死循环。除非有外部因素介入才可能解除死锁。

    3. 如何解决死锁问题

    为了解决死锁问题,数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统,比如 InnoDB 存储引擎,越能检测到死锁的循环依赖,并立即返回一个错误。这种解决方式很有效,否则死锁会导致出现非常慢的查询。还有一种解决方式,就是当查询的时间达到锁等待超时的设定后放弃锁请求,这种方式通常来说不太好。InnoDB 目前处理死锁的方法是,将持有最少行级排他锁的事务进行回滚(这是相对比较简单的死锁回滚算法)。
    锁的行为和顺序是和存储引擎相关的。以同样的顺序执行语句,有些存储引擎会产生死锁,有些则不会。死锁的产生有双重原因:有些是因为真正的数据冲突,这种情况通常很难避免,但有些则完全是由于存储引擎的实现方式导致的。死锁发生以后,只有部分或者完全回滚其中一个事务,才能打破死锁。对于事务型的系统,这是无法避免的,所以应用程序在设计时必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。

    五、MySQL 事务的特点

    1. 自动提交(AUTOCOMMIT)

    MySQL默认采用自动提交(AUTOCOMMIT)模式。也就是说,如果不是显式地开始一个事务,则每个查询都被当作一个事务执行提交操作。在当前连接中,可以通过设置 AUTOCOMMIT 变量来启用或者禁用自动提交模式:

    mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | autocommit    | ON    |
    +---------------+-------+
    1 row in set (0.00 sec)
    
    mysql> SET AUTOCOMMIT=0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | autocommit    | OFF   |
    +---------------+-------+
    1 row in set (0.00 sec)
    
    mysql]> SET AUTOCOMMIT=1;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | autocommit    | ON    |
    +---------------+-------+
    1 row in set (0.00 sec)
    

    1 或者 ON 表示启用,0 或者 OFF表示禁用。当 AUTOCOMMIT=0 时,所有的查询都是在一个事务中,直到显式地执行 COMMIT 提交或者 ROLLBACK 回滚,该事务结束,同时又开始了另一个新事务。修改 AUTOCOMMIT 对非事务型的表,比如 MyISAM 或者内存表,不会有任何影响。对这类表来说,没有 COMMIT 或者 ROLLBACK 的概念,也可以说是相当于一直处于 AUTOCOMMIT 启用的模式。

    另外还有一些命令,在执行之前会强制执行 COMMIT 提交当前的活动事务。典型的例子,在数据定义语言(DDL)中,如果是会导致大量数据改变的操作,比如 ALTER TABLE,就是如此。另外还有 LOCK TABLES 等其他语句也会导致同样的结果。如果有需要,请检查对应版本的官方文档来确认所有可能导致自动提交的语句列表。MySQL可以通过执行 SET TRANSACTION ISOLATION LEVEL 命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。可以在配置文件中设置整个数据库的隔离级别,也可以只改变当前会话的隔离级别:

    mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

    2. 隐式和显式锁定

    InnoDB采用的是两阶段锁定协议(two-phase locking protocol)。在事务执行过程中,随时都可以执行锁定,锁只有在执行 COMMIT 或者 ROLLBACK 的时候才会释放,并且所有的锁是在同一时刻被释放。前面描述的锁定都是隐式锁定,InnoDB 会根据隔离级别在需要的时候自动加锁。

    另外,InnoDB 也支持通过特定的语句进行显式锁定,这些语句不属于SQL规范(这些锁定提示经常被滥用,实际上应当尽量避免使用)

    • SELECT ... LOCK IN SHARE MODE
    • SELECT ... FOR UPDATE

    MySQL 也支持 LOCK TABLESUNLOCK TABLES 语句,这是在服务器层实现的,和存储引擎无关。它们有自己的用途,但并不能替代事务处理。如果应用需要用到事务,还是应该选择事务型存储引擎。

    经常可以发现,应用已经将表从 MyISAM 转换到 InnoDB,但还是显式地使用 LOCK TABLES 语句。这不但没有必要,还会严重影响性能,实际上 InnoDB 的行级锁工作得更好。

    LOCK TABLES 和事务之间相互影响的话,情况会变得非常复杂,在某些 MySQL 版本中甚至会产生无法预料的结果。因此,建议除了事务中禁用了 AUTOCOMMIT,可以使用 LOCK TABLES 之外,其他任何时候都不要显式地执行 LOCK TABLES,不管使用的是什么存储引擎。

    参考《高性能MySQL》
    查看原文

    赞 10 收藏 9 评论 0

    白菜1031 发布了文章 · 5月23日

    Elasticsearch7.X Mapping 常见字段类型整理

    一、核心数据类型

    • 字符串:text, keyword
    • 数值型:long, integer, short, byte, double, float, half_float, scaled_float
    • 布尔型:boolean
    • 日期型:date, date_nanos
    • 二进制:binary
    • 范围型:integer_range, float_range, long_range, double_range, date_range

    1. 字符串

    text

    text 类型的字段数据会被分词,在生成倒排索引以前,字符串会被分词器分成一个一个词项。
    text 类型的字段不用于排序,很少用于聚合(termsAggregation除外)。
    如果一个字段需要被全文搜索或模糊匹配,比如文章内容、产品描述、新闻内容等,应该使用text类型。

    keyword

    keyword 类型的字段内容不会被分词。
    keyword 类型的字段只能通过精确值搜索到,用于过滤、排序、聚合。
    适用于索引结构化的字段,比如IP地址、性别和地区等。

    2. 数值型

    整数

    类型最小值最大值说明
    byte-1281278 位有符号整数(1个字节),相当于MySQL中有符号的 tinyint
    short-327683276716 位有符号整数(2个字节),相当于MySQL中有符号的 smallint
    integer-2147483648
    (-2^31^)
    2147483647
    (2^31^-1)
    32 位有符号整数(4个字节),相当于MySQL中有符号的 int
    long-9223372036854775808)
    (-2^63^)
    9223372036854775807
    (2^63^-1)
    64 位有符号整数(8个字节),相当于MySQL中有符号的 bigint

    对于整数类型的字段,在满足需求的情况下,要尽可能选择范围小的数据类型。比如某个字段的取值最大值不会超过100,那么选择byte类型即可。迄今为止,吉尼斯世界记录的人类的年龄的最大值为134岁,对于年龄字段,short足矣。字段的长度越短,索引和搜索的效率越高。

    小数

    类型最小值最大值说明
    half_float2^-24^6550416位半精度浮点数
    float2^-149^(2-2^-23^)·2^127^32位单精度浮点数
    double2^-1074^(2-2^-52^)·2^1023^64位双精度浮点数
    scaled_float缩放类型浮点数

    处理浮点数时,优先考虑使用scaled float类型。scaled float 是通过缩放因子把浮点数变成long类型,比如价格只需要精确到分,price字段的取值为57.34,设置放大因子为100,存储起来就是5734,所有的API都会把price的取值当作浮点数,事实上Elasticsearch底层存储的是整数类型,因为压缩整数比压缩浮点数更加节省存储空间。

    3. 布尔型

    如果一个字段是布尔类型,可接受的值为 true, false
    Elasticsearch 5.4版本以前,可以接受可被解释为 truefalse 的字符串和数字。
    5.4版本以后只接受 true, false, "true", "false"

    4. 日期型

    date

    JSON 没有日期型数据类型,所以在Elasticsearch中,日期可以是:

    • 包含格式化日期的字符串,例如"2015-01-01"或者"2015/01/01 12:10:30"
    • 代表时间毫秒数的长整型数字。
    • 代表时间秒数的整数。

    Elasticsearch内部会把日期转换为 UTC (世界标准时间),并将其存储为代表时间毫秒数的长整数。
    日期格式可以自定义,如果没有指定格式,则使用默认值:

    
    "strict_date_optional_time||epoch_millis"
    

    这种情况下可以解析下面三种日期格式:

    "2020-05-01"
    "2020-05-01T12:10:30Z"
    1591234567890
    

    date_nanos

    此数据类型是对日期数据类型的补充。现有的 date 类型可以存储毫秒级时间。而 date_nanos 可以存储纳秒级时间。

    5. 二进制

    binary

    二进制数据类型接受Base64编码字符串的二进制值。字段不以默认方式存储而且不能搜索。
    Base64编码二进制值不能嵌入换行符\n

    6. 范围型

    类型说明
    integer_range32 位有符号整数的范围值,-2^31^ ~ 2^31^-1
    long_range62 位有符号整数的范围值,-2^63^ ~ 2^63^-1
    float_range32位单精度浮点数范围值
    double_range64位单精度浮点数范围值
    date_range以64位无符号整数形式表示的日期值范围
    ip_rangeIPv4IPv6 的范围值

    二、复合数据类型

    1. 对象类型

    object

    用于存储单个JSON对象。
    JSON本质上具有层级关系,文档包含内部对象,内部对象本身还可以包含内部对象。

    2. 嵌套类型

    nested

    用于存储多个JSON对象组成的数组。
    nested 类型是 object 类型中的一个特例,可以让对象数组独立索引和查询。Lucene没有内部对象的概念,所以Elasticsearch将对象层次扁平化,转化成字段名字和值构成的简单列表。

    三、地理位置类型

    1. 地理坐标类型

    geo_point

    用于存储经纬度坐标对,可用来
    查找一定范围内的地理点,这个范围可以是相对于一个中心点的固定距离,也可以是多边形或者地理散列单元。
    通过地理位置或者相对于中心点的距离聚合文档。
    整合距离到文档的相关性评分中。

    用于存储地理位置信息的经纬度坐标对,可用于以下几种场景:

    • 查找一定范围内的地理位置。
    • 通过地理位置或者相对中心点的距离来聚合文档。
    • 把距离因素整合到文档的评分中。
    • 通过距离对文档排序。

    2. 地理形状类型

    geo_shape

    地理形状数据类型有利于索引和搜索任意地理形状,例如矩形、三角形或者其他多边形。无论是数据被索引还是在查询执行的过程中,都可以使用地理形状数据类型在地理点的基础上包含地理形状。
    Elasticsearch 使用 GeoJSON 格式来表示地理形状。
    GeoJSON 是一种对各种地理数据结构进行编码的格式,对象可以表示几何、特征或者特征集合,支持点、线、面、多点、多线、多面等几何类型。
    GeoJSON 里的特征包含一个几何对象和其他属性,特征集合表示一系列特征。
    想了解更多关于 GeoJSON 的资料可参考《GeoJSON格式规范说明》

    四、特殊类型

    IP

    IP地址类型,存储 IPv4 和 IPv6 地址

    Completion datatype

    completion 提供自动补全建议

    Token count

    token_count 用于统计字符串分词后的词项个数,本质上是一个整数型字段。
    例如:映射中指定 name 为 text 类型,增加 name_length 字段用于统计分词后词项的长度,类型为 token_count,分词器为标准分词器。

    mapper-murmur3

    murmur3 在索引时计算值的哈希值并将它们存储在索引中

    mapper-annotated-text

    annotated-text 索引包含特殊标记的文本(通常用于标识命名实体)

    Percolator

    接受来自 query-dsl 的查询

    Join

    为同一索引中的文档定义父/子关系

    Rank feature

    Rank features

    排名功能,记录数字特性以提高查询时的命中率

    Dense vector

    密集向量,记录浮点值的密集向量

    Sparse vector

    稀疏向量,记录浮点值的稀疏向量

    Search-as-you-type

    按类型搜索,类似文本的字段,为查询进行优化,以实现按类型完成

    Alias

    别名,定义现有字段的别名

    Flattened

    允许将整个JSON对象作为单个字段编入索引。

    Shape

    shape for arbitrary cartesian geometries.

    Histogram

    histogram for pre-aggregated numerical values for percentiles aggregations.

    五、数组类型

    数组类型不需要专门指定数组元素的类型,任何字段类型都可以包含在数组内,但是数组中的所有值必须具有相同的数据类型。

    • 字符型数组: ["one", "two"]
    • 整型数组:[1, 2]
    • 数组型数组:[1, [2, 3]] 等同于 [1, 2, 3]
    • 对象数组:[{"name": "Mary", "age": 12}, {"name": "John", "age": 10}]
    参考文献:

    官方文档v7.6:字段数据类型
    《从Lucene到Elasticsearch全文检索实战》
    《Elasticsearch技术解析与实战》

    查看原文

    赞 9 收藏 5 评论 0

    认证与成就

    • 获得 1182 次点赞
    • 获得 78 枚徽章 获得 2 枚金徽章, 获得 23 枚银徽章, 获得 53 枚铜徽章

    擅长技能
    编辑

    开源项目 & 著作
    编辑

    • 记忆管家 APP

      项目职责: 【APP服务端API】 登录:QQ、微信第三方登录,短信验证码登录。 支付及提现转账:支付宝、微信支付、ApplePay。 使用Redis记录用户搜索关键字排行等。 【用户网页端】 网址:https://my.jiyiguanjia.com 【WEB管理控制台】 【服务器运维】 Ubuntu14.04 + Nginx + PHP5.9 + MySQL + Redis + Elasticsearch。 项目采用阿里云服务器,MySQL数据库。 使用七牛云存储音频、图片、应用安装包等静态文件。

    • 唯一码发票查重助手 微信小程序

      项目描述: 私人项目,主要是为在某公司做财务的媳妇儿开发的微信小程序,用于电子发票查重,防止重复报销。 小程序可以通过扫一扫功能扫描二维码、条形码,获取包含的字符串信息,来达到发票、书籍、商品等查重的目的。 小程序的主要功能有扫码查重,查看、删除扫码记录,添加、修改删除备注。 项目职责: 负责微信小程序UI设计、UE设计、小程序开发、服务端API开发和运维。 服务端使用CentOS7.5 + PHP7.2 + MySQL + Redis。 使用从腾讯云购买的服务器、MySQL数据库和Redis。

    注册于 2016-05-06
    个人主页被 5.9k 人浏览

    bt365体育投注