$$$ 数据类型
索引列尽量是 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
当用bigint保存,则范围会变更大(目前不支持)
$$$ 索引
结构
通常都是B+Tree,但NDB Cluster内部使用T-Tree,特点:
数据存储有序
叶子节点包含指向下一个节点的联接
存储方式
MYISAM 前缀压缩,引用行的物理位置(当行位置变更时需要更新索引)
主键索引与辅助索引一样
INNODB 不压缩,引用主键值(只要主键不变,即使位置变了也不需要更新索引)
主键索引与辅助索引不一样.主键索引叶子节点保存(主键值,事务ID,MVCC回滚指针及其余列数据),而辅助索引保存(key值,主键值)
如果索引列中没有主键,主键仍会保存在索引中
如果索引中已包含主键,则不会重复保存,只保存索引列中未使用的主键
建议:定义索引时最后把主键带上,因为server层并不知道索引中含主键
哈希索引
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加速
需提前备份数据
不进行重建表
移除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数量;并且和性能之间做权衡
分享到:
相关推荐
中英文对比看高性能MySQL,理解掌握更多的细节知识。
mysql高级笔记 P1 day01-02. MySQL 高级 - Linux上安装MySQL P2 day01-03. MySQL 高级 - 启动及登录MySQL P3 day01-04. MySQL 高级 - 索引 - 概述 P4 day01-05. MySQL 高级 - 索引 - 优势和劣势 P5 day01-06. MySQL ...
《高性能MySQL(第3版)》是MySQL 领域的经典之作,拥有广泛的影响力。第3版更新了大量的内容,不但涵盖了最新MySQL5.5版本的新特性,也讲述了关于固态盘、高可扩展性设计和云计算环境下的数据库相关的新内容,原有的...
高性能MySQL(第2版)中文版.pdf,完整版
笔记目录: P1 day01-02. MySQL 高级 - Linux上安装MySQL P2 day01-03. MySQL 高级 - 启动及登录MySQL P3 day01-04. MySQL 高级 - 索引 - 概述 P4 day01-05. MySQL 高级 - 索引 - 优势和劣势 P5 day01-06. MySQL ...
高性能mysql第三版 高性能mysql第三版 高性能mysql第三版 高性能mysql第三版
高性能Mysql,高清PDF,带目录,mysql机构与历史,mysql基准测试
《高性能MySQL(第3版)》是MySQL 领域的经典之作,拥有广泛的影响力。第3版更新了大量的内容,不但涵盖了最新MySQL5.5版本的新特性,也讲述了关于固态盘、高可扩展性设计和云计算环境下的数据库相关的新内容,原有的...
高性能Mysql笔记,网易云课堂https://study.163.com/course/courseMain.htm?courseId=1003746011课程笔记
高性能MySQL高性能MySQL高性能MySQL高性能MySQL高性能MySQL高性能MySQL高性能MySQL高性能MySQL高性能MySQL高性能MySQL高性能MySQL高性能MySQL高性能MySQL高性能MySQL高性能MySQL高性能MySQL高性能MySQL高性能MySQL高...
高性能mysql第三版.part1.rar 高性能mysql第三版.part1.rar 高性能mysql第三版.part1.rar 高性能mysql第三版.part1.rar 高性能mysql第三版.part1.rar 高性能mysql第三版.part1.rar 高性能mysql第三版.part1.rar 高...
高性能MySQL(一)优化服务器设置 高性能MySQL(二)操作系统和硬件优化 高性能MySQL(三)复制 高性能MySQL(四)可扩展的MySQL 高性能MySQL(五)MySQL分支与变种 高性能MySQL(六)高可用性
高性能MySQL(第3版)中文完整版,清晰度不错。有需要的童鞋可以看看
第1章 mysql 架构与历史 1 第2章 mysql 基准测试 35 第3章 服务器性能剖析 67 第4章 schema 与数据类型优化 111 第5章 创建高性能的索引 141 第6章 查询性能优化 195 第7章 mysql 高级特性 259 第8章 优化服务器设置...
高性能Mysql(中文版)第二版,电子工业出版社。
high performance mysql 4th edition PDF download
高性能mysql PDF 中英文合集 高性能mysql PDF 中英文合集
高性能MYSQL第三版 高性能MYSQL第三版 高性能MYSQL第三版 高性能MYSQL第三版 KINDLE版,
[高性能MySQL(第2版)中文版].施瓦茨.扫描版.pdf