mysql优化

错误浮现，排查，改bug，重写提交，测试，发布

SQL性能下降原因：
1. 查询语句写的烂
2. 索引失效
3. 关联查询太多join（设计缺陷或不得已的需求，七八张表关联一块）
4. 服务器调优及各个参数设置不合理（缓存，线程数等）
...

索引：(Index) 是帮助mysql高效获取数据的 数据结构

1. 索引目的在于提高查询效率，可以类比字典

2. 可以简单理解为"排好序的快速查询数据结构"

3. 索引会影响到where 后面的查询，以及order by 后面的排序

1. 单值:
select * from user where name = ''
create index idx_user_name on user(name)

2. 复合：
select * from user where name = '' and email = ''
create index idx_user_name_email on user(name, email)
SQL执行顺序：

1. 手写顺序

SELECT DISTINCT
<select_list>
FROM
<left_table> <join_type> JOIN <right_table> ON <join_condition>
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

2. 机读顺序

FROM
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

image

SQL1七种join

1、mysql服务器配置文件优化

1. MySQL允许最大的进程连接数，
2. 每个主机的连接请求异常中断的最大次数，
3. 设置表高速缓存的数目，
4. 指定单个查询能够使用的缓冲区大小，缺省为1M
.....
2、根据不同的业务和需求选择核心的存储引擎（mysql可插拔引擎机制MYISAM INNODB）
MyISAM：
1. 不支持主外键、不支持事务
2. 表锁：即使操作一条记录一会锁住整个表，不适合高并发
3. 只缓存索引，不缓存真实数据
4. 表空间小
5. 关注性能（高）
InnoDB：
1. 支持主外键、支持事务
2. 行锁：操作一条记录只会锁住一行，不影响其他行，适合高并发
3. 不仅缓存索引，还缓存真实数据，对内存要求高，内存性能直接影响数据库性能
4. 表空间占用大
5. 关注事务

image

Explain + SQL语句

多表：
索引创建：left 连接 在右表创建索引 right 连接在左表建立索引

3、sql语句优化：

多表：
索引创建：left 连接 在右表创建索引 right 连接在左表建立索引

条件查询时，如果创建的有复合索引，最后按照复合索引的顺序编写where条件

索引优化口诀
全值匹配我最爱，最左前缀要遵守;
带头大哥不能死，中间兄弟不能断;
索引列上少计算，范围之后全失效;
LIKE百分写最右，覆盖索引不写星;
不等空值不用or，索引失效要少用。

慢SQL分析步骤：

1. 观察，至少跑一天，看看生产的慢SQL情况
2. 开启慢查询日志，设置阔值，比如超过5s的就是慢SQL，并将其抓取出来
3. 使用explain+慢sql分析
4. show profile 查询SQL在Mysql服务器里面的执行细节和生命周期情况
5. 运维经理 DBA，进行数据库服务器的参数调优。

查询优化：
1. 永远小表驱动大表，即小的数据集驱动大的数据集

select * from A where id in (select id from B)
等价于
for select id from B
for select * from A where A.id = B.id

当B表的数据集小于A表的数据集时，用in优于exists

select * from A whre exists (select 1 from B where B.id = A.id)
等价于
for select * from A
for select * from B where B.id = A.id

2. 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的 索引。所以无论那个表大，用not exists都比not in要快。

image

group by 优化

慢查询日志：

image