学习笔记-数据库优化

作者: 罗曼蒂克 | 来源:发表于2017-05-05 16:08 被阅读90次

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

    相关文章

      网友评论

        本文标题:学习笔记-数据库优化

        本文链接:https://www.haomeiwen.com/subject/onhstxtx.html