MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43178 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

11 | 怎么给字符串字段加索引?

林晓斌 2018-12-07
现在,几乎所有的系统都支持邮箱登录,如何在邮箱这样的字段上建立合理的索引,是我们今天要讨论的问题。
假设,你现在维护一个支持邮箱登录的系统,用户表是这么定义的:
mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64),
...
)engine=innodb;
由于要使用邮箱登录,所以业务代码中一定会出现类似于这样的语句:
mysql> select f1, f2 from SUser where email='xxx';
从第 4 和第 5 篇讲解索引的文章中,我们可以知道,如果 email 这个字段上没有索引,那么这个语句就只能做全表扫描。
同时,MySQL 是支持前缀索引的,也就是说,你可以定义字符串的一部分作为索引。默认地,如果你创建索引的语句不指定前缀长度,那么索引就会包含整个字符串。
比如,这两个在 email 字段上创建索引的语句:
mysql> alter table SUser add index index1(email);
mysql> alter table SUser add index index2(email(6));
第一个语句创建的 index1 索引里面,包含了每个记录的整个字符串;而第二个语句创建的 index2 索引里面,对于每个记录都是只取前 6 个字节。
那么,这两种不同的定义在数据结构和存储上有什么区别呢?如图 2 和 3 所示,就是这两个索引的示意图。
图 1 email 索引结构
图 2 email(6) 索引结构
从图中你可以看到,由于 email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节(即:zhangs),所以占用的空间会更小,这就是使用前缀索引的优势。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(125)

  • 封建的风
    原谅我偷懒的想法,一个学校每年预估2万新生,50年才100万记录,能节省多少空间,直接全字段索引。省去了开发转换及局限性风险,碰到超大量迫不得已再用后两种办法

    作者回复: 从业务量预估优化和收益,这个意识很好呢👍🏿

    2018-12-08
    4
    156
  • 小文
    首先排除全部索引,占空间,其次排除前缀索引,区分度不高,再排除倒序索引,区分度还没前缀索引高。
    最后hash索引适合,而且只是登录检验,不需要范围查询。
    2018-12-07
    44
  • 老杨同志
    老师整篇都讲的是字符串索引,但是思考题的学号比较特殊,15位数字+固定后缀“@gmail.com”
    这种特殊的情况,可以把学号使用bigint存储,占4个字节,比前缀索引空间占用要小。跟hash索引比,
    也有区间查询的优势

    作者回复: Bigint 8 个字节哦,赞思路。

    嗯问题不是唯一答案,大家集思广益哈

    2018-12-07
    29
  • 进阶的码农
    老师按照你视频里的例子又做了一下还是不行
    mysql版本8.0.12
    +-----------+
    | version() |
    +-----------+
    | 8.0.12 |
    +-----------+
    是innodb 隔离级别是RR
    1.先打开一个客户端A 执行start transaction with consistent snapshot;没有commit
    2.然后在重新打开一个mysql客户端B 执行
    delete from t;call idata(); explain select * from t between 10000 and 20000;
    +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
    | 1 | SIMPLE | t | NULL | range | a | a | 5 | NULL | 10001 | 100.00 | Using index condition |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
    2018-12-07
    1
    24
  • 某、人
    老师针对上一期的答案有两个问题:
    1.为什么事务A未提交,之前插入的10W数据不能删除啊?不是应该都进undo和change buffer了嘛,
    根据mvcc查之前的版本就可以了啊。
    2.不明白为什么第二次调用插入的存储过程,id就变为100000-200000,id是固定插入的,又不是自增的

    作者回复: 1. 这里说的“不能删”,其实就是说的,undo log不能删,逻辑上还在

    2. 你说的对… 😓我最开始的例子是用自增主键,改完自己晕了,堪误了哈

    2018-12-07
    2
    23
  • lttzzlll
    只取 四位年份+五位编号 并转化为int类型作为唯一主键
    2018-12-07
    1
    13
  • 小潘
    可以考虑根据字符串字段业务特性做进制压缩,业务上一般会限制每个字符的范围(如字母数字下划线)。
    从信息论的角度看,每个字节并没有存8 bit的信息量。如果单个字符的取值只有n种可能性(把字符转成0到n-1的数字),可以考虑把n进制转为为更高进制存储(ascii可看做是128进制)。
    这样既可以减少索引长度,又可以很大程度上兼顾前缀匹配。

    作者回复: 这个有点高端了😄

    2018-12-07
    13
  • 某、人
    回答下今天老师的问题:
    1.在user建立索引,由于学号的最后7位才能确定到某个学生.不满足最左前缀,那么select from where '%1234567%'无法使用索引,是全表扫描。但是这种情况也有优化的办法,如果该表上的字段比较多,可以这样改写select password from t join (select id from where user like '%1234567%') as a on a.id=t.id
    通过全扫描二级索引得到唯一id值.再用id值与t表关联的时候,就能迅速的定位到某一行了,避免全表扫描
    不过在本次问题里,这种方式效果不好
      
    2.hash索引,建立(hashuser,user,password)索引,不用回表,覆盖索引,但是索引占用长度长。
    或者建立(hashuser)索引,因为hashuser基本上能确定到唯一值,虽然回表但是扫描的行数也就两行,效率也挺高。但是hash索引对于insert和update操作要多做一些额外的操作。要嘛通过程序计算出hash值,插入表里,要嘛就通过触发器来做。

    3.建立前缀索引,由于后面是固定email,可以考虑只存学号.由于学号后面7位就能确定到某一个学生,可以用倒序存储+前缀索引。不过由于前缀索引不能在int类型上建立,只能用varchar类型。虽然前缀索引无法用到覆盖索引,不过回表扫描的行数也就一行,效率也挺高。这种方式来说,对insert和update相对还好。还有前缀索引还有个影响是不能用于排序。
    2018-12-08
    9
  • Hunter
    是不是还是之前的思路,把邮件的地址里面@的前一段做倒排存储,然后就可以使用前缀索引了。
    或者也可以用年份和顺序号单独存一列,这样就有点类似哈希了,优点是还可以支持范围查询。
    2018-12-07
    8
  • Tony Du
    对于上一期的问题的回答,“索引 a 上的数据其实就有两份”,是不是这样理解,
    其中一份是已经被标记为deleted的数据,另一份是新插入的数据,对索引数据的预估把已经被标记为deleted的数据也算上去了?MySQL对索引数据的预估为什么不去check 被标记为deleted的数据?
    这种场景一旦发生,就会导致预估索引数据不准确,有什么好的方法去避免和解决?

    作者回复: 理解对的,

    方法就是避免长事务(session A 就是模拟一个长事务)

    2018-12-07
    7
  • 不似旧日
    因为mail的格式是学号@gmail.com ,所以@gmail.com可以不存储,使用倒叙前缀索引即可
    2019-03-27
    6
  • ccccc
    实际操作上直接全字段索引就行了,一个学校数据库的数据量和查询压力都不会大到哪儿去。
    如果单从优化数据表的角度:
    1. 后缀@gmail可以单独一个字段来存,或者用业务代码来保证,
    2. 城市编号和学校编号估计也不会变,也可以用业务代码来配置
    3. 然后直接存年份和顺序编号就行了,这个字段可以全字段索引
    2019-03-23
    6
  • WL
    把该讲内容总结为几个问题, 大家复习的时候可以先尝试回答这些问题检查自己的掌握程度:

    1.
    采用整个字符串作为索引. 在查询操作时, 执行顺序是怎样的?
    2.
    采用部分字段做索引, 在查询操作时, 执行顺序是怎样的?
    3.
    使用什么语句可以分析不同长度前缀的索引区分度是多少?
    4.
    前缀索引相比较整个字段做索引有什么优势和劣势?
    5.
    采用反序存储和hash字段作为索引有什么异同点?

    2018-12-09
    5
  • Livis
    课后题思考:可以使用学号拼接密码构建一个hash值,每次登录校验时重新计算hash值匹配;
    2018-12-07
    5
  • 叶剑峰
    我觉得建立索引和插入数据在实际生产过程中可能是相互迭代的。先建立索引--后插入数据--再优化索引,再插入数据,所以文中说的几种方法都要知道下,具体不同情况不同看。像人员表邮箱这个字段,会先建立全字符串索引,要是业务发展到人员表暴增,导致磁盘比较多,才会想到优化某种长度的字符串索引

    作者回复: 差不多是这样的
    一般在你说的这个迭代之前,会再多一个“业务量预估”😆

    2019-01-16
    4
  • 一撮韶光
    删的时候,由于有未提交事务开启的一致性视图read-view,所以导致了存在两个数据版本的数据,貌似优化器在"看"二级索引的时候,"看到"了多个历史版本的数据,错误以为有很多数据

    而主键索引数量由于确认机制不同,数量没有变,综合考虑,优化器选择了主键索引

    作者回复: 👍🏿

    2018-12-09
    3
  • alioo
    去掉@gmail.com后倒序存储,取7位创建前缀索引
    2019-08-26
    2
  • 剃刀吗啡
    通常我们都是在创建表的时候根据业务创建索引,这时候分析前缀没意义,因为没数。等表大了上千万行后再执行前缀分析也不合适啊。
    另外,不是不推荐在where中使用函数吗?使用reverse不影响性能?
    2018-12-12
    1
    2
  • 一步
    前缀索引和整个字段的索引在索引树上是没有区分的吗? 能不能通过某个属性判断一个索引是不是前缀索引?

    还有个问题,就是文中使用整个字段的索引的时候,取出邮箱的值后还要回表判断一下邮箱是否正确,但是下面讲解覆盖索引的时候又不需要回表判断邮箱的值是否正确, 这个如果是整个字段的索引就不需要回表判断是否正确了吧

    下面是文中的内容
    ****************************
    如果使用的是 index1(即 email 整个字符串的索引结构),执行顺序是这样的:从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录,取得 ID2 的值;到主键上查到主键值是 ID2 的行,判断 email 的值是正确的,将这行记录加入结果集;取 index1 索引树上刚刚查到的位置的下一条记录,发现已经不满足 email='zhangssxyz@xxx.com’的条件了,循环结束。
    ****************************
    2019-11-13
    1
  • 小鱼儿
    老师,请教您一个问题,上一章的问题,由于session A 的一致性非锁定读,session B delete之后,undo log肯定不能purge。那么在insert之前,物理磁盘上的 数据页 和 索引页 是真正的删除,还是打个已delete的标记呢,还是什么也不做?如果是打个delete标记,那么下一步如果进行rollback, 是把delete标记去掉吗?
    2019-08-05
    1
收起评论
99+
返回
顶部