ref: High Performance MySQL
Group by原理:
原理:group by先根据要分组的字段进行分组(排序后),再根据主键id进行排序,最终才确定结果。
ref: http://www.qzblog.net/blog/561
默认情况下,id为主键,查询的时候会根据ID ASC进行查询排序,所以进行分组后,找到每个组里的第一条数据作为该组的目标数据。
数据类型
MySQL可以为整数类型指定宽度,例如INT(11), 对于大多数应用这是没有意义的,它不会限制值得合法范围,只是规定了MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说,INT(1)和INT(20)是相同的。
数据类型 | 说明 |
---|---|
VARCHAR | 在V5.0或者更高版本,MySQL在存储和检索时会保留末尾空格。但在V4.1或者更低版本,MySQL会删除末尾空格 |
CHAR | MySQL会删除所有末尾空格 |
整数
数据类型 | 字节数 | 范围 |
---|---|---|
TINYINT | 8 | -128127/0255 (TINYINT UNSIGNED) |
SMALLINT | 16 | |
MEDIUMINT | 24 | |
INT | 32 | |
BIGINT | 64 |
MySQL隔离级别
级别 | 脏读 | 不可重复的读 | 幻读 | 加锁读 | 备注 |
---|---|---|---|---|---|
READ UNCOMMITTED | Y | Y | Y | N | |
READ COMMITTED | N | Y | Y | N | |
REPEATABLE READ | N | N | Y | N | MySQL默认事务隔离级别 |
SERIALIZABLE | N | N | N | Y |
Ch4 Schema与数据类型优化
缓存和汇总表
计数器表
Ch 5 创建高性能索引
在MySQL中索引是在存储引擎层而不是服务器层实现的。
InnoDB使用的是B+Tree
关于B-Tree索引的限制:
- 如果不是按照索引的最左列开始查找,则无法使用索引;
- 不能跳过索引中的列;
- 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查询。
索引的优点
- 索引大大减少了服务器需要扫描的数据量;
- 索引可以帮助服务器避免排序和临时表;
- 索引可以将随机I/O变成顺序I/O;
评价索引的标准
- 索引将相关的记录放在一起;
- 索引中的数据顺序和查找中的排列顺序一致;
- 索引中的列包含了查询中需要的全部列;
5.3 高性能索引策略
独立的列
指索引类不能是表达式的一部分,也不能是函数的参数;
我们应该养成简化WHERE条件的习惯,始终将索引列单独放在比较符号的一侧前缀索引和索引的选择性
索引的选择性=不重复的索引值/数据表记录总数
对于BLOB, TEXT, 很长的VARCHAR类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度。多列索引
选择合适的索引列顺序
经典法则:将选择性最高的类放在索引的最前列。聚族索引
聚族索引不是一种单独的索引类型,而是一种数据存储方式。
InnoDB的聚族索引实际上在同一结构中保存了B-Tree索引和数据行覆盖索引
如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”。使用索引扫描来做排序
MySQL使用两种方式可以生产排序的结果:排序操作;
按照索引顺序排序;
只要索引的列和ORDER BY子句顺序完全一致,并且所有列的排序方向(倒序或正序)都一样时,MySQL才能够使用索引来对结果做排序。
- 压缩(前缀压缩)索引
- 冗余和重复索引
- 未使用的索引
建议删除永远不会使用的索引 - 索引和锁
索引可以让查询锁定更少的行。
InnoDB只有在访问行的时候才会对其加锁,而索引能够减少InnoDB访问的行数,从而减少锁的数量。
Ch6 查询性能优化
Ch7 MySQL高级特性
7.1 分区表
对用户来说,分区表是一个独立的逻辑表,但是底层是有多个物理子表组成。
MySQL实现分区表的方式——对底层表的封装——意味着索引也是按照分区的子表定义的, 而没有全局索引。这和Oracle不同,在Oracle中可以更加灵活的定义索引和表是否分区。
MySQL在创建表时使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候,优化器会根据分区定义过滤那些没有我们需要数据的分区,这样查询就无须扫描所有分区——只需要查询包含需要数据的分区就可以了。
分区的一个主要目的是将数据按照一个较粗的力度分在不同的表中。这样做可以将相关的数据放在一起。另外,如果想一次批量删除猪呢个分区的数据也会变得很方便。
实现视图方法
- 合并算法
- 临时表算法
7.3 外键约束
使用外键是有成本的。
7.9 字符集和校对
校对:指一组用于某个字符集的排序规则
7.9.3 字符集和校对规则如何影响查询
只有排序查询要求的字符集(包含校对规则,下同)与服务器数据的字符集相同的时候,才能使用索引排序,如果希望使用别的校对规则进行排序,那么MySQL就需要使用文件排序。
7.12 查询缓存
MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存,MySQL会立刻返回结果,跳过了解析,优化和执行阶段。
查询系统会跟踪查询中设计的每个表,如果这些表发生变化,那么和这个表相关的所有缓存数据都将失效。
Ch 10 复制
复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。
MySQL支持的复制方式:
- 基于行的复制(逻辑复制)
- 基于语句的复制
实现原理:两种方式都是通过在主库上记录二进制日志,在备库重放日志的方式来实现异步的数据复制。
这也意味着,在同一个时间点备库上的数据可能与主库存在不一致,并且无法保证主备之间的延迟。
复制的用途:
- 数据分布
- 负载均衡
- 备份
- 高可用性和故障切换
- MySQL升级测试
复制步骤:
- 在主库上吧数据更改记录到二进制日志(Binary Log)中(这些记录被称为二进制日志事件);
- 备库将主库上的日志复制到自己的中继日志(Relay Log)中;
配置复制
- 在每台服务器上创建复制账号;
- 配置主库和备库;
- 通知备库连接到主库并从主库复制数据。
复制拓扑
- 一主库多备库
- 主动-主动模式下的主-主复制
- 主动-被动模式下的主-主复制
- 拥有备库的主-主结构
- 环形复制
Ch 11 可扩展性的MySQL
可扩展性:
当增加资源以处理负载和增加容量时系统能够获得投资产出率(ROI)。