Mysql进阶【3】论述Mysql优化

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

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

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

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

 字段解析：

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里边。

[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决定并发，决定处理能力