高性能Mysql

高性能Mysql知识点

  • 表设计

    1. 自增主键：顺序插入减少数据分片，数字的排序性能更好，聚簇索引减少回表查询

    2. 外键：特定应用保持数据一致性

    3. 为列选择合适的数据类型，减少内存占用

    4. 避免一个表中出现太多的列：行结构转换的代价依赖于列的数量

    5. 范式与反范式表的设计：范式查询时经常需要关联，反范式所有数据都在同一张表中因此查询不需要关联。实际中不会有完全遵循范式或者反范式的设计

    6. 询不需要关联。实际中不会有完全遵循范式或者反范式的设计

  • 索引设计

    1. 索引是在引擎层而非服务器层实现的

    2. 索引有：B-Tree，哈希索引，空间数据索引（R-Tree，地理位置），全文索引

    3. 索引优势：减少服务器需要扫描的数据量；帮助服务器避免排序和创建临时表；磁盘顺序I/O

    4. 查询条件中，列为表达式的一部分时，该列无法使用索引

    5. 索引列的顺序：将选择性高的列放在索引的最前列

    6. 覆盖索引：直接从索引中读取数据无需回表查询，性能高

    7. 索引最左匹配原则

    8. 全文索引：自然语言匹配，布尔匹配

  • 查询优化

    1. 避免不必要的排序，如group by时可以指定order by null避免排序

    2. limit 10，10会先查出100条符合条件的数据，然后丢弃前面的90条返回最后10条。数据量过大时，用下一页而不是跳转的方式会时查询性能更好（id > ${id} limit 10）

    3. 善于使用explain分析需要执行的语句，从分析结果优化查询

    4. 对于大查询采用分治的思想，将大查询切分为小查询，每个查询功能完全一样，多次执行（如删除旧数据）

    5. 分解关联查询：让缓存的效率更高；执行单个查询会降低对锁的竞争；小查询本身的效率很可能更高；在应用层做关联对数据库依赖降低，更容易做到高性能和可扩展；可以减少冗余记录的查询

  • 查询优化器做了什么

    1. 重新定义关联表的顺序

    2. 将外连接转化为内连接

    3. 使用等价变换规则

    4. 优化count(), min(), max()

    5. 预估并转换为常数表达式

    6. 覆盖索引扫描

    7. 子查询优化

    8. 提前终止查询：满足需求时立刻终止查询

    9. 等值传播

    10. 列表IN()的比较：将in列表中的数据先进行排序，然后通过二分法确定列表中的值是否满足条件

    11. … …

  • 使用缓存

    1. mysql服务缓存，只有完全相同的两条查询语句，后者才能命中前者的缓存。非查询语句不会缓存，即非查询语句永远不可能命中缓存

    2. 可以指定查询语句是否被缓存，或指定是否需要查询缓存

    3. 应用层缓存：本地缓存，redis缓存等

  • 数据同步

    1. 主服务的二进制日志（BinLog）被读取到从服务的中继日志中，然后有一个线程从中继日志中同步数据

    2. 基于行的复制：保存数据的二进制，直接复制；基于语句的复制：保存生成数据的命令，重新执行一遍命令

  • 分库分表

    1. 真正需要时才考虑这种操作，否则只会增加不必要的性能损耗和维护难度
发布于