高性能 MySQL 读书笔记
一、范式与反范式
完全范式化和完全反范式化的Schema都是不可取的
在实际的应用场景中,通常需要混用,可能使用部分范式化的 schema
、缓存表以及其他技巧。
二、缓存表和汇总表
计数器表
💡 当我们使用单个记录来保存计数器,则在更新计数器时可能碰到并发问题。
一个比较好的解决方式就是通过多个字段(slot
,cnt
)来保存计数器,然后查询的时候使用 sum
来求和。
如果需要一天生成一个计数器,可以通过添加data字段来对应每天的计数。
三、加快 ALTER TABLE 操作的速度
-
在一台不提供服务的机器上执行alter table操作,然后和提供服务的主库进行切换。(主要的使用方式)
-
通过“影子拷贝”。
只替换.frm文件的方法
使用前需要考虑清楚执行的操作的合理性,有一定的心智负担。
-
创建一个有相同数据结构的空表,并进行所需要的修改。
-
执行
FLUSH TABLES WITH READ LOCK
关闭所有正在使用的表,并且禁止任何表被打开。 -
交换 .frm 文件。
-
执行
UNLOCK TABLES
来释放第二条的读锁。
四、如何创建高性能的索引
B-Tree 索引
B-Tree 在底层的存储引擎层可能使用不同的存储结构。比如 NDB 集群存储引擎使用的就是T-Tree 结构存储,在 InnoDB 下,使用的是 B+Tree。
B-Tree 索引对什么类型的查询有效?(ex: key(last_name, first_name, dob)
)
-
全值匹配
-
匹配最左前缀
-
匹配列前缀(比如可以查找 last_name 首字母以J开头的人)
-
匹配范围值
-
精准匹配某一列,并范围匹配另外一列
-
只访问索引的查询
对什么类型的查询无效?
-
不是按照索引的最左列开始查找,则无法使用索引。
-
不能跳过索引的列。
-
索引中某个列有范围查询,则其右边的列无法使用索引优化。
哈希索引
InnoDB 中的“自适应哈希索引”也是对哈希索引的一种利用,主要是在B-Tree索引上针对查询特别频繁的索引的一种优化。
只有Memory存储引擎显式支持哈希索引。
哈希索引的限制
哈希索引只包含哈希值和行指针。
哈希索引不支持排序。
不支持部分索引列匹配查找。
只支持等值查询,不支持范围查询。
哈希冲突多的话,一些索引的维护代价高。
如何在存储引擎不支持哈希索引的情况下自定义哈希索引?
-
面对比较长的字符串,用CRC32方法进行运算,把运算的结果放入单独的一列做索引。
-
避免哈希冲突的查询方式
|
|
空间数据索引(R-Tree)
MyISAM 表支持空间索引,可以用作地理数据存储。不过好像支持不完善,大部分人不会使用这个特性。
全文索引
一般搜索引擎的主要索引。
五、高性能索引的策略
注意独立的列的查询方式
|
|
前缀索引和索引选择器
MySQL 不允许索引 BOLB TEXT VARCHAR 这种列的完整长度,所以要根据实际情况创建前缀索引。
前缀索引的创建方式
|
|
多列索引
当出现多个索引出现相交操作时(通常多个AND条件),这种情况意味着要创建多列索引,而不是单列索引。
多个联合操作时(通常多个OR条件),成本可能比较高,这时候查看执行计划,看看是否修改成union操作更合适。