MySQL常用查询总结

悠悠 2021-10-14 21:31 283阅读 0赞

## MySQL常用示例及慢查询总结 ##

在日常工作中，大部分项目采用的是MySQL作为数据库支撑，下面总结日常需要用到的一些比较实用的查询，后续会不断补充，感兴趣的小伙伴可以留言好用的查询语句，与君共勉。

### 常用示例 ###

#### 1. 查询数据库所有表明和对应的字段名称 ####

`select table_name, column_name from information_schema.columns where table_schema='db_name' and table_name in (select table_name from information_schema.tables where table_schema='db_name') ;`

#### 2.查询表名、表注释 ####

`SELECT table_name 表名, table_comment 表注释 FROM information_schema.TABLES WHERE table_schema = 'db_name'`

#### 3.查看当前表字段注释 ####

`select COLUMN_NAME 字段名, column_comment 字段说明, column_type 字段类型, column_key 约束 from information_schema.columns where table_schema = '数据库名' and table_name = 'table_name' ;`

`或者：`

`show full columns from 表名;`

#### 4.查看表生成的DDL ####

`show create table table_name;`

#### 5.创建表 ####

`create table t_user( ID INT(19) primary key auto_increment comment '主键', NAME VARCHAR(300) comment '姓名', CREATE_TIME date comment '创建时间' )comment = '用户信息表';`

#### 6.修改表字段 ####

`alter table t_user comment = '修改后的表注释信息(用户信息表)';`

#### 7. 关联查询数据只在左表存在数据的情况 ####

![在这里插入图片描述][20190819115503240.png]  
`select tcc.customer_id from TABLE_A tcc left join TABLE_B tcra on tcc.customer_id = tcra.customer_id and tcra.customer_id is null;`

#### 8. 关联查询数据不在内表存在数据的情况 ####

![在这里插入图片描述][20190822111955375.png]

`select tmc.id from TABLE_A ta where id not in (select customer_id from TABLE_B tb where tb.status = 0 group by tb.customer_id) and ta.status = 1 order by ta.create_time desc ;`

#### 9.从一个表更新字段值到另一个表字段 ####

`update tableA as a join tableB as b on a.no=b.no set a.name=b.name where a.status=1`

#### 10.查询表字段及对应注释 ####

`SELECT TABLE_NAME 表名, COLUMN_NAME 列名, COLUMN_TYPE 数据类型, COLUMN_KEY 主键, IF(IS_NULLABLE='NO','是','否') AS '必填', COLUMN_COMMENT 注释 FROM information_schema.`COLUMNS `WHERE TABLE_SCHEMA='db_name' AND table_name in ('table_name_1','table_name_2','table_name_3');`

### 11. 删除操作 ###

#### delete 语句 ####

`delete from table_name;`

#### 查询表空间大小 ####

select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') as table_size
     from information_schema.tables  where table_schema='db_name' AND table_name='table_name';

#### 空间优化语句 ####

`optimize table table_name;`

上述示例中：  
`1、DELETE属于数据库DML操作语言，只删除数据不删除表的结构，会走事务，执行时会触发trigger；`

`2、在 InnoDB 中，DELETE其实并不会真的把数据删除，mysql 实际上只是给删除的数据打了个标记为已删除，因此 delete 删除表中的数据时，表文件在磁盘上所占空间不会变小，存储空间不会被释放，只是把删除的数据行设置为不可见。 虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以重用这部分空间（重用 → 覆盖）。`

`3、 DELETE执行时，会先将所删除数据缓存到rollback segement中，事务commit之后生效;`

`4、 delete from table_name删除表的全部数据,对于MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;`

`5、对于delete from table_name where xxx 带条件的删除, 不管是InnoDB还是MyISAM都不会释放磁盘空间;`

`6、 delete操作以后使用 optimize table table_name 会立刻释放磁盘空间。不管是InnoDB还是MyISAM 。所以要想达到释放磁盘空间的目的，delete以后执行optimize table 操作。`

`7、delete 操作是一行一行执行删除的，并且同时将该行的的删除操作日志记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，生成的大量日志也会占用磁盘空间。`

#### truncate 语句 ####

`truncate table table_name;`

`1、truncate：属于数据库DDL定义语言，不走事务，原数据不放到 rollback segment 中，操作不触发 trigger。`

`执行后立即生效，无法找回 执行后立即生效，无法找回 执行后立即生效，无法找回`

`2、 truncate table table_name 立刻释放磁盘空间 ，不管是 InnoDB和MyISAM 。truncate table其实有点类似于drop table 然后creat,只不过这个create table 的过程做了优化，比如表结构文件之前已经有了等等。所以速度上应该是接近drop table的速度;`

`3、truncate能够快速清空一个表。并且重置auto_increment的值。`

`但对于不同的类型存储引擎需要注意的地方是：`

`对于MyISAM，truncate会重置auto_increment（自增序列）的值为1。而delete后表仍然保持auto_increment。 对于InnoDB，truncate会重置auto_increment的值为1。delete后表仍然保持auto_increment。但是在做delete整个表之后重启MySQL的话，则重启后的auto_increment会被置为1。 也就是说，InnoDB的表本身是无法持久保存auto_increment。delete表之后auto_increment仍然保存在内存，但是重启后就丢失了，只能从1开始。实质上重启后的auto_increment会从 SELECT 1+MAX(ai_col) FROM t 开始。`

`4、清库脚本`

select CONCAT('truncate TABLE ',table_schema,'.',TABLE_NAME, ';') from INFORMATION_SCHEMA.TABLES where  table_schema in ('数据库1','数据库2');`

#### drop 语句 ####

`drop table table_name`;  
`1、drop：属于数据库DDL定义语言，同truncate；`

`执行后立即生效，无法找回 执行后立即生效，无法找回 执行后立即生效，无法找回`

`2、 drop table table_name 立刻释放磁盘空间 ，不管是 InnoDB 和 MyISAM; drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index); 依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。`

#### deleted、truncate、drop总结 ####

1.delete不会直接删除数据，但在optimize执行之后会释放delete的空间；
    2.truncate会清空表内容；
    3.drop清空表内容并且删除表；

### 慢查询优化 ###

#### 1.后台系统统计数目 ####

SLOW_SQL【报警】
         当前时间: 2020-07-24 09:38:01
         数据库地址: xxx.xx.xxx.xx
         数据库端口: 3306
         数据库名: db_name
         业务方向: xxxxxxxx
         执行时长: 大于 10 秒
         执行用户:db_name_rw
         执行地址: xx.xx.xx.xx
         总计慢sql: 1
         执行SQL:
        SELECT count(1) FROM t_table_record t
    WHERE t.effect= 0
    AND t.date >= 1585670400
    AND 1595520000 >= t.date
    AND EXISTS(
    SELECT 1 FROM t_table_cust c WHERE c.effect= 0 AND t.cust_no = c.cust_no AND c.sys_id = 1
    )

###### 执行`explain`分析结果如下： ######

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYW5nX3hz_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][20200724102449435.png]

###### 分析`explain`执行结果： ######

###### 优化前扫描行数 ######

![在这里插入图片描述][20200724111016931.png]

###### 分析报告： ######

<table> 
 <thead> 
  <tr> 
   <th>建议使用 AS 关键字显示声明一个别名</th> 
   <th align="right">在列或表别名(如"tbl AS alias")中, 明确使用 AS 关键字比隐含别名(如"tbl alias")更易懂。</th> 
   <th>select name from tbl t1 where id &lt; 1000</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>MySQL 对子查询的优化效果不佳</td> 
   <td align="right">MySQL 将外部查询中的每一行作为依赖子查询执行子查询。 这是导致严重性能问题的常见原因。这可能会在 MySQL 5.6 版本中得到改善, 但对于5.1及更早版本, 建议将该类查询分别重写为 JOIN 或 LEFT OUTER JOIN。</td> 
   <td>select col1,col2,col3 from table1 where col2 in(select col from table2)</td> 
  </tr> 
  <tr> 
   <td>为db_name库的t_table_cust表添加索引</td> 
   <td align="right">为列effect添加索引;为列system_id添加索引</td> 
   <td>ALTER TABLE <code>db_name</code>.<code>t_table_cust</code> add index <code>idx_effect_system_id</code> (<code>effect</code>,<code>system_id</code>) ;</td> 
  </tr> 
  <tr> 
   <td>为db_name库的t_table_record表添加索引</td> 
   <td align="right">为列effect添加索引;为列date添加索引</td> 
   <td>ALTER TABLE <code>fi_dfm</code>.<code>t_table_record</code> add index <code>idx_effect_date</code> (<code>effect</code>,<code>date</code>) ;</td> 
  </tr> 
  <tr> 
   <td>指定了 WHERE 条件或非 MyISAM 引擎时使用 COUNT(*) 操作性能不佳</td> 
   <td align="right">COUNT(<em>) 的作用是统计表行数，COUNT(COL) 的作用是统计指定列非 NULL 的行数。MyISAM 表对于 COUNT(</em>) 统计全表行数进行了特殊的优化，通常情况下非常快。但对于非 MyISAM 表或指定了某些 WHERE 条件，COUNT(*) 操作需要扫描大量的行才能获取精确的结果，性能也因此不佳。有时候某些业务场景并不需要完全精确的 COUNT 值，此时可以用近似值来代替。EXPLAIN 出来的优化器估算的行数就是一个不错的近似值，执行 EXPLAIN 并不需要真正去执行查询，所以成本很低。</td> 
   <td>SELECT c3, COUNT(*) AS accounts FROM tab where c2 &lt; 10000 GROUP BY c3 ORDER BY num</td> 
  </tr> 
  <tr> 
   <td>不建议使用 COUNT(col) 或 COUNT(常量)</td> 
   <td align="right">不要使用 COUNT(col) 或 COUNT(常量) 来替代 COUNT(<em>), COUNT(</em>) 是 SQL92 定义的标准统计行数的方法，跟数据无关，跟 NULL 和非 NULL 也无关。</td> 
   <td>SELECT COUNT(1) FROM tbl;</td> 
  </tr> 
 </tbody> 
</table>

###### 分析逻辑完成，添加索引： ######

ALTER TABLE t_table_record add INDEX  i_cust_no (`cust_no `) USING BTREE;

###### 优化完成，执行`explain`分析结果如下： ######

![在这里插入图片描述][20200724102557877.png]

###### 优化后扫描行数 ######

![在这里插入图片描述][20200724110839860.png]

[20190819115503240.png]: /images/20211015/d767aceae73d4f46a2ef734287033e0e.png
[20190822111955375.png]: /images/20211015/0a6145c26cdd4168a58b9de5ccbc6ef6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYW5nX3hz_size_16_color_FFFFFF_t_70]: /images/20211015/42e0acf4e5ad4e9e83a818590ac29be5.png
[20200724102449435.png]: /images/20211015/54b796b64d7f41ca9abf52dfac112286.png
[20200724111016931.png]: /images/20211015/d6fbf6b44ed74b97aaf15aaff455ae63.png
[20200724102557877.png]: /images/20211015/9bf303419afc4d6aa14f5840f83ee47f.png
[20200724110839860.png]: /images/20211015/d062e49bb25045c38469ece2fb540de6.png