• 数据库知识点(性能优化)


    数据库
    1. 引擎 
    - innodb
    - 表/行锁
    - 事务
    -- mysiam
    - 表锁
    - 无事务
    -2. 数据库如何加锁?
    终端1:
    begin;
    select * from tb for update;
    commit;
    
    终端2:
    begin;
    select * from tb for update;
    commit;
    
    3. 什么时候需要加锁?
    - 计数
    - 应用场景:
    - 商品数量
    
    4. 了解:
    - 视图
    - 触发器
    - 函数 
    - 存储过程
    
    5. 索引
    
    作用:加速查找,对数据进行约束
    索引种类
    
    主键:不能为空
    唯一:可以为空
    普通
    联合:
    联合唯一
    
    最左前缀原则
    
    
    名词
    覆盖索引:无序再从物理表中进行查询
    
    索引合并:多个单列进行查询时
    
    6. 无法命中?
    
    7. 慢日志?(记录sql执行效率低的语句)
    - 查询时间久的数据
    - 未命中索引的数据
    
    修改配置:
    slow_query_log = ON 开启 是否开启慢日志记录
    long_query_time = 2 超过两秒记录下来 时间限制,超过此时间,则记录
    slow_query_log_file = /usr/slow.log 记录日志的文件
    log_queries_not_using_indexes = ON 为使用索引的搜索是否记录
    
    8. 执行计划(帮助自己测试sql语句执行的性能怎样)
    explain select * from tb; 
    
    id
    查询顺序标识
    如:mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;
    +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
    | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | NULL |
    | 2 | DERIVED | tb1 | range | PRIMARY | PRIMARY | 8 | NULL | 9 | Using where |
    +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
    特别的:如果使用union连接气值可能为null
    
    
    select_type
    查询类型
    SIMPLE 简单查询
    PRIMARY 最外层查询
    SUBQUERY 映射为子查询
    DERIVED 子查询
    UNION 联合
    UNION RESULT 使用联合的结果
    ...
    table
    正在访问的表名
    
    
    type(执行效率由低到高)
    查询时的访问方式,性能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
    ALL 全表扫描,对于数据表从头到尾找一遍(执行效率最低DBA有打死开发当事者的想法select * from tb1;
    特别的:如果有limit限制,则找到之后就不在继续向下扫描
    select * from tb1 where email = 'seven@live.com'
    select * from tb1 where email = 'seven@live.com' limit 1;
    虽然上述两个语句都会进行全表扫描,第二句使用了limit,则找到一个后就不再继续扫描。
    
    INDEX 全索引扫描,对索引从头到尾找一遍
    select nid from tb1;
    
    RANGE 对索引列进行范围查找
    select * from tb1 where name < 'alex';
    PS:
    between and
    in
    > >= < <= 操作
    注意:!=> 符号
    
    
    INDEX_MERGE 合并索引,使用多个单列索引搜索
    select * from tb1 where name = 'alex' or nid in (11,22,33);
    
    REF 根据索引查找一个或多个值
    select * from tb1 where name = 'seven';
    
    EQ_REF 连接时使用primary key 或 unique类型
    select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
    
     
    
    CONST 常量
    表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
    select nid from tb1 where nid = 2 ;
    
    SYSTEM 系统
    表仅有一行(=系统表)。这是const联接类型的一个特例。
    select * from (select nid from tb1 where nid = 1) as A;
    possible_keys
    可能使用的索引
    
    key
    真实使用的
    
    key_len
    MySQL中使用索引字节长度
    
    rows
    mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值
    
    extra
    该列包含MySQL解决查询的详细信息
    “Using index”
    此值表示mysql将使用覆盖索引,以避免访问表。不要把覆盖索引和index访问类型弄混了。
    “Using where”
    这意味着mysql服务器将在存储引擎检索行后再进行过滤,许多where条件里涉及索引中的列,当(并且如果)它读取索引时,就能被存储引擎检验,因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示:查询可受益于不同的索引。
    “Using temporary”
    这意味着mysql在对查询结果排序时会使用一个临时表。
    “Using filesort”
    这意味着mysql会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。mysql有两种文件排序算法,这两种排序方式都可以在内存或者磁盘上完成,explain不会告诉你mysql将使用哪一种文件排序,也不会告诉你排序会在内存里还是磁盘上完成。
    “Range checked for each record(index map: N)”
    这个意味着没有好用的索引,新的索引将在联接的每一行上重新估算,N是显示在possible_keys列中索引的位图,并且是冗余的。
    
    9. 优化方案:
    - 不使用select * 
    - 短索引 
    - 使用链表代替子查询
    - 固定长度在前面
    - 分库分表
      分库:一个库中数据太多了,分到其他库中
      分表:
        水平分表:把一个表拆到另一个表中,相当于一个one2one
        垂直分表:把经常不看的记录放到另一张表中
      
    - 组合索引 >大于索引合并 - 内存代替链表   - choices   - limit - 读写分离(主从) - 分页
      1(记录当前页面的最大id与最小id)
      2:指定只给用户看XX也
    - 缓存(redis):不能放非常多的数据,可以做高可用 select id,1 from tb;
  • 相关阅读:
    [java,2019-01-28] 枪手博弈,谁才是最后赢家
    [java,2019-01-25] 图片和二进制互转
    [java,2019-01-15] word转pdf
    [python,2018-06-29] 37%法则及其拓展解决恋爱问题
    [java,2018-06-26] 扑克牌抽牌求和问题
    [python,2018-06-25] 高德纳箭号表示法
    [java,2017-06-12] myEclipse双击无法打开文件
    OpenGL核心技术之法线贴图
    游戏中水的渲染技术系列一
    Unity 3D实现帧同步技术
  • 原文地址:https://www.cnblogs.com/wanghuaqiang/p/9432176.html
Copyright © 2020-2023  润新知