11：怎么给字符串字段加索引？

业务场景：基于字符串字段做查询。例如邮箱登录等等。

前缀索引的优势：相对于整个字段索引，前缀索引的占用空间更小。
前缀索引带来的问题：区分度过低的时候会额外扫描次数。

使用前缀索引需要定义好长度，就可以节省空间又不用额外增加太多查询成本。

区分度语句：

select
count(distinct left(field,4)）as L4,
count(distinct left(field,5)）as L5,
count(distinct left(field,6)）as L6,
count(distinct left(field,7)）as L7,
from table;

使用前缀索引 = 去掉索引覆盖，因为无法确定前缀索引是否覆盖全部内容。

其他问题：例如身份证字段前6位区分不够好，加到12个字段又会导致过大，页存放数据变少，查询效率变低。

• 倒序存储：将身份证号倒过来存，身份证码后六位提供足够的区分度。
• hash字段：插入身份证号时通过crc32函数得到一个校验码，为这个字段设置索引，同时在判断的时候比较身份证号，这样索引长度就变为4个字节。

alter table t add id_card_crc int unsigned, add index(id_card_crc);

相同点：不支持范围查询，

小总结：

• 直接创建完整索引，这样可能比较占用空间；
• 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
• 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
• 创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

思考题

维护一个学校的学生信息数据库，学生登录名的统一格式是”学号@gmail.com", 而学号的规则是：十五位的数字，其中前三位是所在城市编号、第四到第六位是学校编号、第七位到第十位是入学年份、最后五位是顺序编号。

系统登录的时候都需要学生输入登录名和密码，验证正确后才能继续使用系统。就只考虑登录验证这个行为的话，你会怎么设计这个登录名的索引呢？

我的回答：学号只有后五位是区分度，根据所学，使用第三种倒序存储。
标准答案：而其实在此基础上，可以用数字类型来存这9位数字。比如201100001，这样只需要占4个字节。其实这个就是一种hash，只是它用了最简单的转换规则：字符串转数字的规则，而刚好我们设定的这个背景，可以保证这个转换后结果的唯一性

12：为什么我的MySQL会“抖”一下？

场景：一条SQL语句，正常执行的时候特别快，但是有时也不知道怎么回事，它就会变得特别慢，并且这样的场景很难复现，它不只随机，而且持续时间还很短。

脏页

13：为什么表数据删掉一半，表文件大小不变？

参数 innodb_file_per_table

innodb_file_per_table
show VARIABLES where Variable_name = 'innodb_file_per_table'

我建议你不论使用MySQL的哪个版本，都将这个值设置为ON。

14：count(*)这么慢，我该怎么办？

show table status

15： 答疑文章（一）：日志和索引相关问题

16：“order by”是怎么工作的？

MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。

show VARIABLES where Variable_name = 'sort_buffer_size'

17： 如何正确地显示随机消息？

对于对 InnoDB I 表来说 表 ，执行全字段排序会减少磁盘访问，因此会被优先选择。

18：为什么这些SQL语句逻辑相同，性能却差异巨大？

19：为什么我只查一行的语句，也执行这么慢？

20：幻读是什么，幻读有什么问题？

建表语句

CREATE TABLE `t_2001` (`id` INT ( 11 ) NOT NULL,`c` INT ( 11 ) DEFAULT NULL,`d` INT ( 11 ) DEFAULT NULL,PRIMARY KEY ( `id` ),KEY `c` ( `c` ) 
) ENGINE = INNODB;

数据

insert into t_2001 values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

BEGIN;SELECT* FROMt_2001 WHEREd = 5 FOR UPDATE;
COMMIT;

这个语句会命中d=5的这一行，对应的主键id=5，因此在select 语句执行完成后，id=5这一行会加一个写锁，而且由于两阶段锁协议，这个写锁会在执行commit语句的时候释放。

由于字段d上没有索引，因此这条查询语句会做全表扫描。那么，其他被扫描到的，但是不满足条件的5行记录上，会不会被加锁呢？

幻读是什么？

一个假设的场景，实际运行B和C会阻塞。

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

• 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。（for update）

• 上面session B的修改结果，被session A之后的select语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

幻读有什么问题？

语义：我要将 d=5的所有行锁住，实际上没有锁住。

select * from t_2001 where d= 5 for update 

一致性：

也就是说，即使把所有的记录都加上锁，还是阻止不了新插入的记录

如何解决幻读？

为了解决幻读问题，InnoDB只好引入新的锁，也就是间隙锁(Gap Lock)，顾名思义，间隙锁，锁的就是两个值之间的空隙。比如文章开头的表t，初始化插入了6个记录，这就产生了7个间隙。

当你执行 select *fromt where d=5 for update的时候，就不止是给数据库中已有的6个记录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新的记录。

间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间。

小结