$$$ 数据类型
索引列尽量是 NOT NULL
实数
浮点类型(float(4字节)、double(8字节))和decimal类型(转为double计算)
cpu可以直接计算浮点类型，但不能计算decimal类型
每4个字节保存9个数字，小数点1个字节
varchar(N)存储长度： N+额外长度(1字节n<=255,2字节n>255)
更新时若原位置不适合新长度：myisam 拆分行，innodb分页保存
memory引擎char与varchar都是定长保存
N越小，排序效率越高，内存表(都是定长)空间越小
blob、text类型
排序仅适用max_sort_length规定的前N个字节进行排序
enum --不建议使用
变为整数进行保存，空间小
缺点：需要进行额外转换才能显示字符串，
添加/删除enum集时需要alter table
比较时转为字符串进行比较
日期
datetime：8字节、时区无关，1001-9999年
timestamp：4字节、保存1970.1.1以来的秒数，依赖于时区，1970-2038
[color=red]当用bigint保存，则范围会变更大(目前不支持)[/color]
$$$ 索引
结构
通常都是B+Tree，但NDB Cluster内部使用T-Tree，特点：
数据存储有序
叶子节点包含指向下一个节点的联接
存储方式
MYISAM 前缀压缩，引用行的物理位置(当行位置变更时需要更新索引)
主键索引与辅助索引一样
INNODB 不压缩，引用主键值(只要主键不变，即使位置变了也不需要更新索引)
主键索引与辅助索引不一样.主键索引叶子节点保存(主键值，事务ID，MVCC回滚指针及其余列数据)，而辅助索引保存(key值，主键值)
如果索引列中没有主键，主键仍会保存在索引中
如果索引中已包含主键，则不会重复保存，只保存索引列中未使用的主键
[color=red]建议：定义索引时最后把主键带上，因为server层并不知道索引中含主键[/color]
哈希索引
memory引擎默认hash索引(innodb是自适应，不能控制)，并且支持不唯一哈希索引，相同hash码的行指针(与myisam索引结构相同)以链表方式保存
特征：适用于等值查找
使用
适合全键值、键值范围、键前缀查找(大字符串索引)
索引选择性越高，过滤的行数越多。所以越高越好，聚集索引、唯一索引的选择性最高，只后命中1行。
索引覆盖查询：myisam压缩存储，效率更高，innodb索引中包含主键值
使用索引进行group、order
导致索引无法生效的错误用法：
> 条件(where / group)没有从索引(a,b)最左列开始，即跳过a列直接使用b列
> 不在索引(a)列上进行任何运算。例如：a+1<10,to_days(a)<10等
> 用联合索引(a,b,c)进行 group 、order时，
左侧列是等值查找，用到索引，例如
where a=10 group by b,c OR where a=10 and b=10 group by c
where a=10 order by b,c OR where a=10 and b=10 order by c
左侧列是范围查找，不会用到索引，例如
where a>10 group by b,c OR where a=10 and b>10 group by c
where a>10 order by b,c OR where a=10 and b>10 order by c
> 用联合索引(a,b,c)进行order by时
要保持采用同样的升/降序，用到索引，例如
ORDER BY a ASC,b ASC,c ASC 或者 ORDER BY a DESC,b DESC,c DESC
如果混用，不会用到索引，例如
ORDER BY a ASC,b DESCSC,c ASC 或者 ORDER BY a DESC,b DESC,c ASC
> 选择性低的索引可能比全表扫描效率更差。比如性别列，由于性别分布基本是1:1，即使有索引也要扫描一半的表数据。
analyze table
重新生成索引统计，使得mysql优化器评估时更准确
optimize table
整理表空间碎片，提高访问速度，但不影响查询计划
也可通过alter table tb_name engine=原引擎;
插入非递增主键缺点：
> 大量随机IO查找插入新行的目标页面(可能已被flush到磁盘并从缓存移走)
> 为新行进行分页，导致移动大量数据
> 因分页导致页面稀疏和不规则填充，碎片化。需要optimize table重新组织表
$$$ 锁
计数表设计
单行无主键：表锁，并发差
多行有主键：行锁，提高并发，查询时进行sum
$$$ DDL加速
[color=red]需提前备份数据[/color]
不进行重建表
移除anto_increment属性
更改enum、set常量
步骤
1、create table tb_new like tb_old;
2、在tb_new上进行属性修改
3、FLUSH WITH READ LOCK.关闭所有正在使用表并且防止任何表被打开
4、交换tb_old.frm,tb_new.frm文件
5、UNLOCK TABLES.释放锁
$$$ explain/profile
> explain
查询状态：
sleep：等待客户端新查询
query：a、正在执行查询，b、往客户端发送数据
lock：等待锁
分析统计：优化器正在分析执行计划
拷贝到磁盘：线程内存不足，要保存到磁盘上。
大查询(group，order)或alter 大表
排序结果(sorting):在内存中对结果排序
发送数据：a、在各个状态间传递数据，b、产生结果，c、把结果返给客户端
myisam缓存
缓存命中率：100-((key_reads * 100)/key_read_requests)
缓存使用率: 100-((key_blocks_unused * key_cache_block_size) *100 / key_buffer_size)
innodb缓冲池
buffer pool：延迟写入，但若过大当检查点或插入缓存合并时会变慢
线程缓存thread_cache_size:检测thread_connected值在min--max变化，则将thread_cache_size设为max-min.每个线程大小一般为256K
sync_binlog
一条数据都不能丢： sync_binlog = 1;
可以丢部分数据，假设可以接受丢10秒的数据： sync_binlog = 10*每秒DML数量;并且和性能之间做权衡
索引列尽量是 NOT NULL
实数
浮点类型(float(4字节)、double(8字节))和decimal类型(转为double计算)
cpu可以直接计算浮点类型，但不能计算decimal类型
每4个字节保存9个数字，小数点1个字节
varchar(N)存储长度： N+额外长度(1字节n<=255,2字节n>255)
更新时若原位置不适合新长度：myisam 拆分行，innodb分页保存
memory引擎char与varchar都是定长保存
N越小，排序效率越高，内存表(都是定长)空间越小
blob、text类型
排序仅适用max_sort_length规定的前N个字节进行排序
enum --不建议使用
变为整数进行保存，空间小
缺点：需要进行额外转换才能显示字符串，
添加/删除enum集时需要alter table
比较时转为字符串进行比较
日期
datetime：8字节、时区无关，1001-9999年
timestamp：4字节、保存1970.1.1以来的秒数，依赖于时区，1970-2038
[color=red]当用bigint保存，则范围会变更大(目前不支持)[/color]
$$$ 索引
结构
通常都是B+Tree，但NDB Cluster内部使用T-Tree，特点：
数据存储有序
叶子节点包含指向下一个节点的联接
存储方式
MYISAM 前缀压缩，引用行的物理位置(当行位置变更时需要更新索引)
主键索引与辅助索引一样
INNODB 不压缩，引用主键值(只要主键不变，即使位置变了也不需要更新索引)
主键索引与辅助索引不一样.主键索引叶子节点保存(主键值，事务ID，MVCC回滚指针及其余列数据)，而辅助索引保存(key值，主键值)
如果索引列中没有主键，主键仍会保存在索引中
如果索引中已包含主键，则不会重复保存，只保存索引列中未使用的主键
[color=red]建议：定义索引时最后把主键带上，因为server层并不知道索引中含主键[/color]
哈希索引
memory引擎默认hash索引(innodb是自适应，不能控制)，并且支持不唯一哈希索引，相同hash码的行指针(与myisam索引结构相同)以链表方式保存
特征：适用于等值查找
使用
适合全键值、键值范围、键前缀查找(大字符串索引)
索引选择性越高，过滤的行数越多。所以越高越好，聚集索引、唯一索引的选择性最高，只后命中1行。
索引覆盖查询：myisam压缩存储，效率更高，innodb索引中包含主键值
使用索引进行group、order
导致索引无法生效的错误用法：
> 条件(where / group)没有从索引(a,b)最左列开始，即跳过a列直接使用b列
> 不在索引(a)列上进行任何运算。例如：a+1<10,to_days(a)<10等
> 用联合索引(a,b,c)进行 group 、order时，
左侧列是等值查找，用到索引，例如
where a=10 group by b,c OR where a=10 and b=10 group by c
where a=10 order by b,c OR where a=10 and b=10 order by c
左侧列是范围查找，不会用到索引，例如
where a>10 group by b,c OR where a=10 and b>10 group by c
where a>10 order by b,c OR where a=10 and b>10 order by c
> 用联合索引(a,b,c)进行order by时
要保持采用同样的升/降序，用到索引，例如
ORDER BY a ASC,b ASC,c ASC 或者 ORDER BY a DESC,b DESC,c DESC
如果混用，不会用到索引，例如
ORDER BY a ASC,b DESCSC,c ASC 或者 ORDER BY a DESC,b DESC,c ASC
> 选择性低的索引可能比全表扫描效率更差。比如性别列，由于性别分布基本是1:1，即使有索引也要扫描一半的表数据。
analyze table
重新生成索引统计，使得mysql优化器评估时更准确
optimize table
整理表空间碎片，提高访问速度，但不影响查询计划
也可通过alter table tb_name engine=原引擎;
插入非递增主键缺点：
> 大量随机IO查找插入新行的目标页面(可能已被flush到磁盘并从缓存移走)
> 为新行进行分页，导致移动大量数据
> 因分页导致页面稀疏和不规则填充，碎片化。需要optimize table重新组织表
$$$ 锁
计数表设计
单行无主键：表锁，并发差
多行有主键：行锁，提高并发，查询时进行sum
$$$ DDL加速
[color=red]需提前备份数据[/color]
不进行重建表
移除anto_increment属性
更改enum、set常量
步骤
1、create table tb_new like tb_old;
2、在tb_new上进行属性修改
3、FLUSH WITH READ LOCK.关闭所有正在使用表并且防止任何表被打开
4、交换tb_old.frm,tb_new.frm文件
5、UNLOCK TABLES.释放锁
$$$ explain/profile
> explain
查询状态：
sleep：等待客户端新查询
query：a、正在执行查询，b、往客户端发送数据
lock：等待锁
分析统计：优化器正在分析执行计划
拷贝到磁盘：线程内存不足，要保存到磁盘上。
大查询(group，order)或alter 大表
排序结果(sorting):在内存中对结果排序
发送数据：a、在各个状态间传递数据，b、产生结果，c、把结果返给客户端
myisam缓存
缓存命中率：100-((key_reads * 100)/key_read_requests)
缓存使用率: 100-((key_blocks_unused * key_cache_block_size) *100 / key_buffer_size)
innodb缓冲池
buffer pool：延迟写入，但若过大当检查点或插入缓存合并时会变慢
线程缓存thread_cache_size:检测thread_connected值在min--max变化，则将thread_cache_size设为max-min.每个线程大小一般为256K
sync_binlog
一条数据都不能丢： sync_binlog = 1;
可以丢部分数据，假设可以接受丢10秒的数据： sync_binlog = 10*每秒DML数量;并且和性能之间做权衡
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
