Mysql进阶【3】论述Mysql优化

古城微笑少年丶 2023-10-12 12:11 17阅读 0赞

## 1.通过explain查看sql的详细信息 ##

Mysql的sql优化企业里边主要是对慢sql进行优化，对语句进行优化，对索引进行优化

通过explain查看sql的详细信息，并且分析sql语句存在的问题，比如有没有使用到索引、使用了索引还是慢是不是索引设置的不合理、没有显示使用到索引是不是触碰了索引失效的规则。

使用explian的方法也就是在sql前边加上explian.

### ![c5272f933ee043da99924305bd8da614.png][] 字段解析： ###

1.  id:查询序列号，因为explain可能会存在多条，标记条数用的
2.  select\_type:查询语句类型：
    
    1.  **simple:普通查询**
    2.  **union、union all：联合查询**
        
        1.  derived:包含子查询
        2.  union result ：连接查询结果
    3.  **subquery： 子查询**
3.  table：查询的表名称
4.  **type：连接类型**
    
    1.  **system：只有一行，或者是空表**
    2.  **const：唯一索引或者主键**
    3.  **eq\_ref：唯一索引扫描**
    4.  **ref:非唯一索引扫描**
    5.  **range:索引范围扫描**
    6.  index:使用到了索引
    7.  all：全表扫描
5.  possible\_keys：此次查询中可能选用的索引
6.  key：查询真正使用到的索引
7.  key\_len：显示MySQL决定使用的索引size
8.  ref：哪个字段或常数与 key 一起被使用
9.  **rows：显示此查询一共扫描了多少行，这个是一个估计值，不是精确的值。**
10. filtered: 表示此查询条件所过滤的数据的百分比
11. **Extra：额外信息**
    
    1.  Using filesort：文件排序
    2.  Using index：where后使用到了索引，会出现Using index；Using Where
    3.  Using join buffer：连接缓存

## 2.MySQL 的 SQL 优化 ##

1.对索引进行优化

*  表行数很少的去掉索引
 *  表上的索引数量不能过多，索引过多最好去掉不常用的索引，索引的存在会占用磁盘空间，更新数据的时候，不仅仅数据需要维护，索引也需要进行维护
 *  频繁更新的字段不要作为索引，频繁更新性能消耗高
 *  区分度低的不要作为索引，区分度低，查询的数量多，回表的性能消耗增加
 *  无序的值不要作为索引，无序的数据存放排列散乱
 *  尽量使用组合索引，节省空间
 *  需要谨防索引失效规则，不要触碰此规则，避免全表扫描

2.查询语句加上limit优化sql,查询数据若只有一条，使用limit可以限制全表扫描

3.能不使用子查询就不使用，可以使用连接代替子查询的就是用连接查询，因为子查询会创建临时表，查询完毕之后会将临时表删除，创建跟删除的过程是不可避免的，势必会降低查询时间

4.如果需要关联查询，连接的字段最后建立索引

## 3.MySQL 的 数据库优化 ##

数据库优化：开启慢查询优化日志、配置连接数、数据库结构优化

### 开启慢查询优化日志 ###

开启慢查询优化日志，存在慢查询的时候开启日志，然后分配慢查询日志，平时的时候不建议开启，日志的查看需要在用户下边，而不是在mysql里边。

![55e1f0b8568446a8a1e09d3f6f620fc2.png][]

[root@localhost ~]# mysqldumpslow -s t /var/lib/mysql/localhost-slow.log 
    
    Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
    Count: 1  Time=77.12s (77s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[192.168.200.1]
      select tk.id,ts.* from  tb_seckill_goods ts LEFT JOIN tb_sku tk ON tk.id=ts.id where ts.id>N order by ts.price
    
    Count: 1  Time=2.00s (2s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[192.168.200.1]
      select sleep(N)

### 配置连接数 ###

配置这个连接数，就跟连接池一样使用的池化思想，但是也不能设置过高，要根据cpu核数，任务处理时间来合理配置

# 查看 max_connections
    show global variables like '%max_connections%'
    # 设置 max_connections（立即生效重启后失效）
    set global max_connections = 800;

## 数据库结构优化 ##

1.  字段多的表，字段使用频率低的表字段，拆分为多个表，通过主键关联
2.  增加中间表，需要频繁进行多表关联的多个表，可以建立中间表，建立只有不需要每次都关联，只需要查询中间表即可
3.  合理增加冗余字段，规范化表的设计。

##  ##

## 4.MySQL 的 硬件优化 ##

提升硬件设备，例如选择**尽量高频率的内存**（频率不能高于主板的支持）、提升**网络带宽**、使用SSD高速磁盘、提升**CPU性能**等。CPU的选择:cpu决定并发，决定处理能力

[c5272f933ee043da99924305bd8da614.png]: https://img-blog.csdnimg.cn/c5272f933ee043da99924305bd8da614.png
[55e1f0b8568446a8a1e09d3f6f620fc2.png]: https://img-blog.csdnimg.cn/55e1f0b8568446a8a1e09d3f6f620fc2.png