mysql进阶之sql性能优化

1. 视图

创建

2. MYSQL优化

2.1 确定sql执行效率(查询看看是哪种操作比较慢)

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

# 查看全局数据
show Global status like'Com_____';
# 查看当前对话数据
show session status like 'Com_____'; 
# 查询innodb专属
show status like'innodb_rows_%';

Com_delete: 删除次数
Com_insert: 插入次数
Com_select: 查询次数
Com_update: 更新次数

通过这样我们就可以知道该数据库是查多还是增删改多, 方便确认后期的优化方向

2.2 查询慢日志（来看看是谁操作的 干了什么）\

2.2.1 打开慢日志查询记录

# 查询慢日志打开状态
show variable like 'slow_query_log'

打开慢日志需要修改配置文件,文件位置为（/etc/my.cnf）

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

这样一旦有超时的sql操作,就会记录进log日志中,日志保存的位置为
(/var/lib/mysql/localhost-slow.log)

2.2.2 查询当前线程执行效率

show processlist

show profiles

-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

展现所有的语句的耗时

show profile for query 6

查看指定sql语句在内部各步骤执行情况

2.2.3 explain查看执行计划

使用： explain sql语句

1. id执行优先级

行数代表执行次数，id代表执行顺序
id相同从上向下执行 id越大执行优先级越高

2. select_type表连接类型

确定表如何连接和连接顺序

-a35e-4a11da9c1ea&title=&width=898.5)
其中primary 代表该查询会依赖其它子查询的结果
拓展小知识： union 关键字（解决or关键字造成的索引失效）
必须两个查询出的结果字段名和字段数量完全一致才会合并

UNION

查询出结果会去重 并排序

UNION ALL

查询出结果直接合并

第三步就是整合查询出的结果（去重 排序）

3. type访问类型(观察索引)

一般来说当出现index和all的时候就需要注意了 说明这个sql效率很低
我们需要保证查询至少达到 range 级别， 最好达到ref 。

4. key

possible_keys : 显示可能应用在这张表的索引， 一个或多个。
key ： 实际使用的索引， 如果为NULL， 则没有使用索引。
key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前
提下， 长度越短越好 。

5. extra(额外信息)

前两个说明执行不正常 后一个正常

2.3 索引优化

2.3.1 最左前缀原则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)
较为简单,就不解释了
不过有一点需要注意,所谓索引最左开始,和sql查询条件顺序无关, 比如联合索引为(A,B,C) ,那么后面条件为 where B=xxx and C=xxx and A=xxx 索引此时也是有效的

2.3.2 范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

explain select * from tb_user
 where profession = '软件工程' and age > 30 
 and status= '0';

此时age右侧的status即使有索引也会失效.

当范围查询使用>= 或 <= 时，会走联合索引
所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <

2.4 索引失效

1. 索引列参与计算
2. 字符串不加引号
3. 模糊查询
4. or连接条件
5. 数据分布影响(sql优化机制)