如何写出优质mysql语句

最新推荐文章于 2022-07-26 00:00:25 发布

sofency

最新推荐文章于 2022-07-26 00:00:25 发布

阅读量234

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_43079376/article/details/105426734

版权

数据库专栏收录该内容

19 篇文章 2 订阅

订阅专栏

SQL性能下降的原因

查询语句写的烂
索引失效单值和复合
关联查询太多join
服务器调优及各个参数设置

特征：执行时间长，等待时间长

sql的执行加载顺序

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>

七种join理论

在这里插入图片描述
第一种join的sql语句

select <select_list> from A left join B on A.key = B.key

第二种join的sql语句

select <select_list> from A right join B on A.key = B.key

第三种join的sql语句

select <select_list> from A left join B on A.key=B.key where B.key=null

第四种join的sql语句

select <select_list> from A inner join B on A.key=B.key

第五种join的sql语句

select <select_list> from A right join B on A.key=B.key where A.key=null

第六种join的sql语句全连接

select <select_list> from A full outer join B on A.key=B.key

第七种join的sql语句

select <select_list> from A full outer  join B on A.key=B.key where B.key=null or A.key = null

索引

mysql 官方对于索引的定义: 索引是帮助数据库高效获取数据的一种数据结构
索引的优势：采用B+数的数据结构存储了主键与索引的关系，可以提高查询的速度。

索引的劣势：就拿传统的二叉树来说,我们每插入，删除更新数据的时候，都要相应的更新树的结构，同理mysql索引采用B+的数据结构存储主键与字段的关系也会有这样的情况，如果创建的索引过多，会大大降低数据的更新插入和删除操作，因为大部分的时间都被用来更新索引信息。

索引的分类：
单值索引，一个索引只包含一个列
唯一索引 索引列的值必须唯一，但允许为空值。
复合索引 即一个索引包含多个列。
基本的语法
创建索引 create index indexName on table(column(length));
删除索引 drop index [ indexName] from table;
查看索引 show index from table;
使用alter 命令

alter table tableName add primary key (columnName);
alter table tablename add unique indexName(column_list);//创建唯一索引
alter table tablename add index indexName (column_list);
alter table tablename add fulltext indexName(column_list);

什么情况下适合创建索引

主键会自动唯一索引
频繁作为查询条件的字段适合创建索引
对于频繁更新的字段不合适创建索引，因为每此进行修改都要更新索引的信息
查询中与其它表关联的字段，外键关系建立索引
where条件里用不到的字段不创建索引
查询中排序的字段，需要排序的字段如果使用索引访问将大大提高排序速度。

什么情况下不适合创建索引

数据太少
经常增删改的表
对于字段重复性较高且分布平均的字段不适合创建字段。
比较的基准 = 该列中不重复的字段个数/该列的所有个数
如果基准越接近1 ，那么查询的效率就越高。

mysql的常见瓶颈

CPU cpu在饱和的时候一般发生在数据装入内存或者从磁盘读取数据时候
IO 装入数据远远大于内存的容量
服务器硬件瓶颈，top ,free iostat和vmstat 来查看系统的性能状态。

如何评价一个sql语句写的好，就好比医生诊断病人一样，要有证据说明人家sql写的烂。不能诬陷人家哈。
关键字explain
使用方式 explain + sql语句
explain 展示的表结构列解释

id: select查询的序列号，表示查询中执行select子句或操作表的顺序
三种情况
id相同，执行顺序由上至下
id不同，如果是子查询（SUBQUERY）id的序号会递增，id值越大优先级越高，越先被执行。
id相同不同同时存在，同级则顺序执行，不同级 id越高越先被执行

select_type:
常见的值有
SIMPLE ：查询中不包含子查询或者联合查询如下
在这里插入图片描述
PRIMARY :查询中如果包含任何复杂的子部分，最外层查询被标记为PRIMARY
SUBQUERY
select或者where列表中包含了子查询。

DERIVED from关键字后面包含的子查询被标记为Derived(衍生) 就是临时表

table 表示查询的是哪个表
type:访问类型排列，当前的查询使用和何种类型，从最好到最差依次是
System>const>eq_ref>ref>range>index>ALL
const 表示通过索引一次就找到数据。
如下面的sql语句我设置了engineer_tel为索引值
在这里插入图片描述
eq_ref 唯一性索引扫描对于每一个索引键，表中只有一条记录与之对应，常见于主键或者唯一索引，我们直接对唯一索引进行查询的时候会直接晋升为const类型，常见于多表查询中。

ref 非全局性唯一索引，当我们使用复合索引时，对单个索引字段搜索的时候就是ref类型。
在这里插入图片描述
range 是在一定范围内查找数据

index的话就是查询主键
ALL 是全表扫描，如果数据量比较大的话，性能很差。

possible_key 和key
possible_key 表示语句理论上可能使用的索引
key 表示语句实际上使用了什么索引，以此来判断索引是否失效。
下面的特殊情况理论上没有使用索引，但是实际上用了索引。在这里插入图片描述
key_len：表示索引中使用的字节数，可通过该列计算查询使用的索引的长度。在不损失精准的前提下，长度越短越好。
key_len显示的值为索引字段的最大可能长度，并非实际使用长度。
ref：显示索引的哪一列被使用了。
rows:根据表统计信息及索引的选用情况，大致估算出所需记录所需要读取的行数
extra 包含不适合在其他列显示但十分重要的信息。

实验：添加复合索引时 alter table stu add index inde_demo(class_id,grade_id);

using filesort 说明mysql会对数据使用一个外部的索引排序。
这种情况出现在order by的时候，如下图
using temporary 在使用group by时如果使用了索引那么group by的内容就要和索引的顺序和内容一样
explain select count(grade_id) from stu GROUP BY class_id,grade_id;
没有使用temporary