mysql 优化笔记

最新推荐文章于 2024-09-26 22:37:35 发布

码出花

最新推荐文章于 2024-09-26 22:37:35 发布

阅读量121

点赞数

分类专栏： Mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_27696413/article/details/104933700

版权

Mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

启动问题，配置文件等
1. 实现开机自启动：chkconfig mysql on
2. 检查开机自启动：ntsysy
3. 关闭开机自启动：chkconfig mysql off
4. 配置文件
  - /usr/share/mysql配置文件
    - my-huge.cnf 高端服务器 1-2G内存
    - my-large.cnf 中等规模
    - my-medium.cnf
    - my-small.cnf
    - 以上配置默认不能识别，默认识别/etc/my.cnf，需要使用以上配置需要复制到/etc/my.cnf(mysql-default.cnf)
  - /etc/init.d/mysql
5. mysql字符编码
  1. 查看 show variables like '%char%'
  2. 设置编码 vi /etc/my.cnf
    - 文件中mysql/client option中设置
      - default-character-set=utf8
    - mysqld option中设置
      - character_set_server=utf8
      - character_set_client=utf8
      - collation_server=utf8_general_ci
    - 设置编码完之后只对之后创建的数据库有效
逻辑分层
1. 连接层：提供与客户端连接服务
2. 服务层：
  - 提供各种用户使用接口
  - 提供sql优化器（是否可关闭）
3. 引擎层（show engines）
  - 提供各种存储数据方式（引擎）
    - innodb：事务优先（适合高并发操作：行锁）
    - myisam：性能优先（表锁）
4. 存储层：存储数据
SQL优化
1. 性能低，执行时间长，索引失效，服务器参数设置不合理（缓冲、线程池）
2. SQL：
  - 编写过程：select ... from join ... where ... group up.... order by ... desc
  - 解析过程：from ... on... join... group up ... having... order by....
3. sql优化：优化索引
4. 索引：降低增删改查的效率
SQL性能问题
1. 分析SQL执行计划：查询执行计划：explain 执行计划中显示 id值大的表先执行
  1. - id
    - select_type（查询类型）:primary（主查询），subquery（子查询），simple（简单查询，不包含子查询、union），derived（衍生查询，用到了临时表，a.在from子查询中只有一张表，b.在from子查询中只有，如果table1 union table2，则table1就是derived）
    - type（索引类型，以下顺序为效率排序，system，const只是理想情况，实际能达到ref>range）：
      - system：只有一条数据的系统表，或衍生表只有一条数据的主查询
      - const：仅仅能查到一条数据的SQL，用于Primary key或unique索引
      - eq_ref：主键或唯一性索引，对于每个索引键的查询，返回匹配唯一行数据（有且只有一个，不能多，不能0）
      - ref：非唯一性索引，对每个索引键的查询
      - range：索引指定范围内的行，where后面是>、< 、in(有时候失效，转为无索引)，<>=
      - index：查询全部索引中的数据
      - all：查询全部表中的数据
    - possible_keys:可能用到的索引，是一种预测，不一定准
    - key：实际上用的到索引
    - key_len：索引的长度，作用：用于判断符合索引是否完全使用，如果索引字段可以为null，则会使用1个字节用于标识，使用两个字节标识可变长度varchar
    - ref：注意与type中的ref区分，作用：指明当前表所参照的字段，例如 select ... where a.c = b.x (其中b.x可以是常量，为const)
    - rows：被索引优化查询的数据个数
    - extra：
      - using filesort：性能消耗大；需要“二外”的一次查询（排序）一般出现在order by：
        
        对于单索引，如果排序和查找是同一个字段就不会出现
        
        对于复合索引：不能跨列；避免：where和order by 按照顺序和不跨列
      - using temporary：性能损耗较大，用到临时表，一般出现group up
      - using index：性能提升；索引覆盖。不读取源文件，只查询索引文件，只要使用的列都在索引中，不需要回表查询
      - using where：需要回表查询
      - using join buffer：mysql底层自动加了缓存
  2. 表的执行顺序因表数量的个数改变而改变：原因：笛卡尔积（中间值，先执行数据表小的查询）
  3. 嵌套表子查询中，先查询内层，再执行外层
  4. 复合索引中：
    - 如果使用的（abcd）索引使用的顺序全部一致则使用的using index，（存在order by时，会看where 和order by结合的索引顺序）
    - 虽然查询顺序和索引顺序不一致，但是有sql优化器优化成顺序执行（全部索引字段使用情况下）
    - 如果使用时出现跨列，会出现回表using where，使用order by时出现跨列会出现using filesort
    - 优化案例（最佳左前缀，保持定义索引顺序，in放后面）
      1. 加索引
      2. 根据实际解析顺序，调整索引顺序正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消转存失败重新上传取消
      3. 多表查询优化（一般左外连接给左表加索引）
        
        小表驱动大表
        
        （示例两表查询）查询时相当于两层循环，小循环在外层，大循环在内层
      4. 避免索引失效的一些原则
        
        复合索引：
        
        不要跨列或无需使用（最佳左前缀）
        
        尽量使用全索引匹配
        
        对于复合索引，如果左边索引失效，右侧的全部失效
        
        不能使用不等于（>,<, <>=）或者is null，否则索引全失效，概率出现，由于正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消正在上传…重新上传取消转存失败重新上传取消
    - 不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效
    - 尽量使用索引覆盖
    - like尽量使用常量开头，不要使用“%”开头
    - 尽量不要使用类型转换，否则索引失效
    - 尽量不要使用or，否则索引失效
    - 一些优化的其他方法
    - exist和in
      - 如果主查询的数据集大，则使用In
      - 如果子查询的数据集大，则使用exist
    - order by优化
      - using filesort 有两种算法：双路排序、单路排序（根据IO次数）
        
        双路排序：mysql4.1之前默认使用：扫描两次磁盘（1.从磁盘独处排序字段，对排序字段进行排序，2.扫描其他字段）
        
        单路排序：mysql4.1之后默认使用：只读取一次磁盘全部数据，在buffer中进行排序，（不一定是单路，如果数据量太大，可能分片查询，多次读取）
        
        单路排序比双路排序会占用更多的buffer
        
        单路排序使用时，如果数据量大，可考虑调节buffer的容量大小：set max_leng_for_sort_data,如果该值太小，mysql会自动从单路切换到双路
      - 选择使用单路、双路，调整buffer的容量大小
      - 避免使用select *
      - 复合索引不要跨列使用，避免using filesort
      - 保证全部的排序字段排序的一致性（都是升序或降序）
    - SQL排查
      - 慢查询日志：Mysql提供一种日志记录，用于记录mysql响应时间超过阈值的SQL语句（开启慢查询日志再使用存储过程会有冲突）（long_query_time默认10秒)
        
        开启日志（临时开启）默认是关闭
        
        set global slow_query_log=1
        
        永久开启
        
        在/etc/my.cnf中[mysqld]添加
        
        slow_query_log=1
        
        slow_query_log_file=日志路径
      - 慢查询阈值
        
        临时设置
        
        show variables like "%long_query_time%"
        
        设置：set show_query_time,设置完毕后重新登录，不需要重启服务
        
        永久设置
        
        /etc/my.cnf中[mysqld]中添加
        
        long_query_time=5
      - show global status like "%slow_query%"
      - 查看日志文件：/var/lib/mysql/mysql/localhost-slow.log
      - mysqldumpslow工具查看慢sql（以下为常见参数）
        
        s 排序方式
        
        r 逆序
        
        l 锁定时间
        
        g 正则匹配模式
    - 分析海量数据：
      1. profiling
        
        show pofiles；
        
        show variables like "%%"
        
        set profiles：会记录所有profiling打开之后的全部SQL查询语句所花费的时间
        
        缺点：不够精确,cpu,io的总的时间
        
        精确分析
        
        show profile all for query 上一步查询的query_id
      2. 全局查询日志：记录开启之后的全部查询语句,会被记录到mysql.general_log表中
        
        show variables like "%general_log%"
        
        set global general_log=1;
        
        set globle log_output='table' 将全部sql查询输出到表中
        
        set globle log_output='file';set globle general_log=on;set general_log_file = 文件