学习笔记-数据库优化

腾讯课堂,燕十八Mysql高性能优化https://ke.qq.com/course/171224

建表原则

1. 变长字段和定长字段分离
2. 常用字段和不常用字段分离
3. 在1对多,需要关联统计的字段上,添加冗余字段以减少查询的压力,写入的时候麻烦一点

列类型选择

1. 字段类型优先级 整型>date,time>enum,char>varcahr>blob,text
2. 性别:utf8为例
3. char(1)三个字节
4. enum('男','女')内部转成数字来存,多了一个转换过程
5. tinyint(),0,1,2 定长1字节
6. 够用就行,不要慷慨
7. 年龄:用 tinyint unsigned not null足够,可以存0-255
8. varchar(10),varcahr(300)存储内容相同,但是在联查时,后者占用更多内存
9. 尽量避免使用null()

索引类型

1. btree索引
2. 将节点用树进行索引

F18AEF581E92509B292295258BE85331.png

3. 找到索引,再找到对应记录的位置获取详情
4. 提高排序/分组/查询速度
5. 误区:
6. where id>3 and price>100,那么在id和price都建立索引.这是错误的,因为索引是独立的,这条语句只能用一个索引
7. 联合索引:左前缀索引,按索引顺序(语句可替换) 使用索引
8. hash索引
9. 将索引hash,放入对应位置
10. 范围查找,不好使
11. 排序无法优化
12. 优点,复杂度理论为O(1)
13. 非聚簇索引
14. myisam:索引和数据是分开的
15. 回行拿数据
    4.聚簇索引
16. innoDB:索引和数据是放在一起的
17. 主键索引即存储索引,又存储数据
18. 非主键索引的数据指向主键键索引:即先找到name,name节点存储了id,再到id索引找到详细数据

修复表

1. 索引碎片与维护
2. 在长期的数据更改过程中,索引文件和数据文件,都将产生空洞,形成碎片
3. 可以通过Nop(不产生对数据实质影响的操作)来修复表:
4. alter table xxx engine innodb;本来就是innodb引擎
5. optimize table name 来修复表
6. 把数据文件对齐,比较耗资源,不能频繁操作,如果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;