高性能mysql学习笔记 数据类型和部分索引策略

一、数据类型

char和varchar

char和varchar是两种主要的字符串类型，值在磁盘和内存中的存储跟存储引擎的实现有关。
以MYISAM和Innodb为例进行介绍。

• VARCHAR

varchar存储的是变长字符串。
优点：比定长类型更节省存储空间，它仅适用必要的空间。特例：如果MYSQL表使用ROW_FORMAT=FIXED创建的话，每一行会定长存储造成空间浪费。
varchar需要使用1-2个额外字节记录字符串长度，列长度<=255,使用一个字节，超过则使用两个字节。假设使用latin1字符集，一个varchar(10)列需要11个字节的存储空间;varchar(1000)列需要1002个字节的存储空间。
在5.0以以上版本，MYSQL存储和检索会保留末尾空格，4.1或更老版本，会剔除空格。

• CHAR

CHAR类型是定长的，MYSQL会根据定义的字符串长度分配足够的内存。当存储CHAR值时，MYSQL会剔除所欲的末尾空格。char值会根据需求采用空格进行填充以方便比较。
CHAR值更适合存储很短的字符串，或者值都接近一个长度。

BLOB和TEXT类型

不同之处
BLOB存储的是二进制数据，没有排序规则或字符集。而TEXT类型有排序规则和字符集。
MYSQL不能将BLOB和TEXT列全部长度的值作为索引，也不能使用这些索引消除排序。

ENUM

有时Enum可以代替字符串，枚举可以把一些不重复的字符串存储在一个预定义的集合中。MYSQL在存储枚举类型时非常紧凑，会根据列值的数量压缩到1-2哥字节中。MYSQL将每个值在列表中的位置保存为整数，并且在表的“.frm”文件中保存“数字-字符串”映射关系的查找表。

二、索引相关

1.MYSQL索引顺序问题

如何选择索引列顺序有个经验法则：将选择性最高的列放到索引的最前列。（只对某些场景有效。通常不如避免随机io和排序重要）
当不考虑排序和分组时，讲选择性最高的列放在前面通常是很好的。这时候索引的作用只是优化where条件的查找。
这样设计有助于更快的过滤除需要的行，对于where子句只使用了索引部分前缀列的查询来说选择性也更高。
例：

select * from payment where staff_id = 2 and customer_id = 584;

对于创建一个（staff_id,customer_id）索引还是颠倒顺序创建，可以跑一些查询看值分布情况，来确定哪个列的选择性更高。

select sum(staff_id = 2),sum(customer_id = 584) from payment \G
// sum(staff_id) : 7992 sum(customer_id) ：30

以上对应条件值的customer_id的数量更小。
但是查询的结果依赖于选定的具体值。虽然当前选定值的对应条件值更小 但是对于其他条件则不一定，甚至导致服务器性能降低。所以最好还是按照经验法则来做，考虑全局基数和选择性。

2 聚簇索引（聚集索引）

• 优点：

1. 可以把相关数据保存在一起。如根据用户id查询用户电子邮件，如果采用聚集索引，就可以从少数的磁盘页就能获取用户的全部邮件，但是如果没有用聚簇索引，每封邮件都可能会进行一次磁盘的io.
2. 数据访问更快。索引和数据保存在同一个B-tree中
3. 使用覆盖索引扫描的查询可以直接使用页节点的主键值。

• 缺点：

1. 虽然最大限度的提高了I/O密集型应用性能，但如果数据全部放在内存中，访问顺序没那么重要，聚簇索引也就没那么重要了。
2. 插入速度严重依赖插入顺序。按照主键的顺序插入时加载数据到innodb表中
3. 速度最快的方式。但如果不按主键顺序加载数据，那么在加载完成后最后使用optimize table 命令重新组织一下表。
4. 更新聚簇索引的列的代价很高，因此会强制innodb将每个更新的行移动到新的位置。
5. 基于聚簇索引的表插入新行，或者主键被更新导致行移动 可能造成”页分裂“问题。页分裂会导致表占用更多的存储空间。
6. 可能导致全表扫描变慢 尤其是行比较稀疏或者由于页分裂导致数据存储不连续的时候。
7. 二级索引（非聚簇索引）可能比想象中更大，因为二级索引叶子节点包含了引用行的主键列。
8. 二级索引访问需要两次索引查找。因为二级索引叶子节点保存的不是指向行的物理地址，而是行的主键值。这就意味着通过二级索引查找行，首先找到二级索引的叶子节点获取对应主键值，再通过这个值去聚簇索引中查找对应行。（Innodb中，自适应索引可以减少这样的重复工作。）虽然会占用更多的空间，但是在innodb行移动时不需要再对二级索引的这个“指针”进行维护。

主键使用UUID的缺点

对于第二条为什么一次插入会修改三个页
假如当前页是page1，会把要分裂的页记录进行复制到page2 ，然后对page2再进行分裂 （如果这个分裂点是在page2页尾也就会对页进行再分裂）,对page2进行分裂会对page2记录进行复制到page3 ，根据分裂点 把page2分裂点之后的记录删除，把page3分裂点之前的记录删除，然后再确定要插入数据的位置进行插入。

该条参考链接https://blog.csdn.net/weixin_31600859/article/details/113275656

为什么页分裂导致页变的稀疏，最终数据会有碎片。
比方有三页数据 10 11 12 ，现在有一个数据本来要插入第10也 但是第10页满了 然后本来会看11页有没有位置放 结构11页也是满的 就会在10和11之间创建个新页 ，然后指针指向发生变化 10-》新页 -》11页 =》12页，但是新页的的数据现在就一条。后期该页也可能不会填充满数据，所以会说页变得“稀疏”,页内会有空白空间，也就相当于“碎片”（实例有出入但有这么个意思）

该条参考链接https://zhuanlan.zhihu.com/p/98818611

顺序主键的问题。

对于上图提到在高并发工作负载中，InnoDB中主键顺序插入可能会存在并发插入间隙锁竞争的问题。
特此记录下间隙锁相关知识。
什么是间隙锁？
在索引记录之间的间隙的锁，为了解决幻读问题。
产生间隙锁的条件？

• 使用普通索引锁定；
• 使用多列唯一索引；
• 使用唯一索引锁定多行记录。
  代码如下（示例）
  注：对于使用唯一索引来搜索并给某一行记录加锁的语句，不会产生间隙锁。（这不包括搜索条件仅包括多列唯一索引的一些列 的情况；在这种情况下，会产生间隙锁。）例如，如果id列具有唯一索引，则下面的语句仅对具有id值100的行使用记录锁，并不会产生间隙锁：

SELECT * FROM child WHERE id = 100 FOR UPDATE;

唯一索引的间隙锁
对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁，如：

WHERE `id` = 5 FOR UPDATE;

对于查找某一范围内的查询语句，会产生间隙锁，如：

WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;

普通索引的间隙锁
在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样；
在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通索引排序，再根据唯一索引排序。

关于间隙锁并发下的重点考虑

三、覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称为覆盖索引。
优点

• 索引条目通常远小于数据行的大小，如果只需要读取索引，mysql能极大的减少数据访问量。
• 因为索引按顺序存储，所以多I/O密集型的范围查找比随机从磁盘读取每一行数据的I/O少的多。
• 对于一些存储引擎如MYISAM在内存中只缓存索引，数据则依赖操作系统缓存，避免了访问数据进行的数据调用。
• 由于Innodb聚簇索引，二级索引在叶子节点保存行的主键值，如果二级主键能够覆盖查询，不需要对主键索引再进行二次查询。

总结

后续再对笔记进行补充