腾讯课堂,燕十八Mysql高性能优化https://ke.qq.com/course/171224
建表原则
- 变长字段和定长字段分离
- 常用字段和不常用字段分离
- 在1对多,需要关联统计的字段上,添加冗余字段以减少查询的压力,写入的时候麻烦一点
列类型选择
- 字段类型优先级 整型>date,time>enum,char>varcahr>blob,text
- 性别:utf8为例
- char(1)三个字节
- enum('男','女')内部转成数字来存,多了一个转换过程
- tinyint(),0,1,2 定长1字节
- 够用就行,不要慷慨
- 年龄:用 tinyint unsigned not null足够,可以存0-255
- varchar(10),varcahr(300)存储内容相同,但是在联查时,后者占用更多内存
- 尽量避免使用null()
索引类型
- btree索引
- 将节点用树进行索引
F18AEF581E92509B292295258BE85331.png
- 找到索引,再找到对应记录的位置获取详情
- 提高排序/分组/查询速度
- 误区:
- where id>3 and price>100,那么在id和price都建立索引.这是错误的,因为索引是独立的,这条语句只能用一个索引
- 联合索引:左前缀索引,按索引顺序(语句可替换) 使用索引
- hash索引
- 将索引hash,放入对应位置
- 范围查找,不好使
- 排序无法优化
- 优点,复杂度理论为O(1)
- 非聚簇索引
- myisam:索引和数据是分开的
- 回行拿数据
4.聚簇索引 - innoDB:索引和数据是放在一起的
- 主键索引即存储索引,又存储数据
- 非主键索引的数据指向主键键索引:即先找到name,name节点存储了id,再到id索引找到详细数据
修复表
- 索引碎片与维护
- 在长期的数据更改过程中,索引文件和数据文件,都将产生空洞,形成碎片
- 可以通过Nop(不产生对数据实质影响的操作)来修复表:
- alter table xxx engine innodb;本来就是innodb引擎
- optimize table name 来修复表
- 把数据文件对齐,比较耗资源,不能频繁操作,如果update 操作频繁周/月 来修复,不频繁则更长周期来修复
explain
| 字段 | 解释 |
|---|---|
| id: | 该语句中查询语句的id(因为可能是嵌套查询) |
| select_type: | 见下图 |
| table : | 查询的表\别名 |
| possible_keys: | 可能用到的key |
| key: | 用到的key |
| type: | 查询的方式,非常重要,是分析"查数据过程"all/index/range/ref/const/system/null效果越来越好 |
| ref: | 连接查询是,表字段的引用关系 |
| rows: | 估计要扫描的行数 |
| extra: | 1. index:用到了索引覆盖,效率非常高;2. usering where指光靠索引不能定位,还要where判断一下;3. usering temporary用了临时表,group by 与order by 不同列时,或group by order by别的表的列;4. usering filesort:文件排序 |
0350565611272DB6AB52F0DA38415432.png
in型子查询陷阱
mysql的查询优化器,针对in型查询做了优化,被改成了exists子茶行的执行效果
改进:用链接查询代替子查询
count小技巧
表a有44555550条数据
想知道id大于1000的有多少条数据
直接select count() from a where id>1000;耗时可能有4/5s
优化策略:
select count() from a 得到总记录数 total;
select count(*) from a<=1000得到记录数 lt1000;
total-lt1000=总数-一小块=剩下一大块
union小技巧
union会排序,去重
union all 不会排序去重
limit翻页优化
limit随着翻页offset的增长,越来越慢
limit 10000,5 :拿出10005行,丢掉前边10000行,返回5行数据
改进方法
用where进行优化
select * from a where id>10000 limit 5;
这个要求数据id不能断
思路是:将分页只在索引叶子上进行,得到要获取的id,然后根据id区查询详情:
select * from a inner join (select id from a limit 1000000,5) as b on a.id=b.id;
