命令记录|SQL&mysql核心知识总结

旧城等待， 2024-03-22 16:09 34阅读 0赞

> 本文主要记录工作中遇到的常用和重要的sql语句

#### 文章目录 ####

*  一、基本命令
 *   *  1.连接服务器、定位表、查询表结构等
     *  2.修改表结构
     *  3.文件操作
     *  4.性能监控场景
 *  二、explain语句查询结果指标说明：
 *   *   *  id
         *  type
         *  possible\_keys
         *  key
         *  ken\_len
         *  Extra
 *  三、索引以及索引失效的情况总结
 *   *  1.左模糊匹配，like '%xxx' like '%xxx%'
     *  2.函数，where length(name)=6
     *  3.进行表达式计算，where id - 1 = 9
     *  4.类型转换 select "10" > 9
     *  5.联合索引缺少最左条件
     *  6.where子句中的or，一边非索引字段
 *  总结

--------------------

`下文所有命令默认为mysql环境，如果在其他数据库如ti-db等环境运行，特别注意预先评估是否兼容`

--------------------

## 一、基本命令 ##

### 1.连接服务器、定位表、查询表结构等 ###

# 非容器环境用客户端连接服务端
    	mysql -h127.0.0.1 -P3306 -uxxx -pxxx
    	# 容器环境下连接服务端（ti-db端口默认4000）
    	mysql -hbasic-tidb -P4000 -uxxx -pxxx # 如果配置了服务名的dns，-h可以使用对应的service-name
    	kubectl get svc -A | grep tidb	# 获取clusterIp地址，如下图此处为：10.100.65.37
    	mysql -h10.100.65.37 -P4000 -uxxx -pxxx
    	
    	show databases;		# 查看所有库信息
    	use database_xxx;	# 进入指定的数据库
    	show tables like '%xxx%';	# 根据表的关键字模糊匹配表
    	desc table_xxx;		# 描述指定表的所有字段信息
    	# 将11位时间戳格式化显示：from_unixtime(time_millisecond/1000)
    	SELECT time_millisecond,from_unixtime(time_millisecond/1000) from 表名 ORDER BY time_millisecond desc limit 1;

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

### 2.修改表结构 ###

# 更新数据
    	update table_xxx set column1 = value1, column2 = value2 where ……;
    	# 增加字段
    	ALTER TABLE table_name ADD column_name datatype;
    	# 删除字段
    	ALTER TABLE table_name DROP column_name datatype;	
    	# 修改字段类型
    	ALTER TABLE table_name MODIFY COLUMN column_name datatype;
    	# 删除索引
    	DROP INDEX 索引名 ON 表名;
    	# 修改索引
    	ALTER TABLE 表名 ADD INDEX 索引名 [索引类型] (列名,…);

### 3.文件操作 ###

# 使用mysql工具批量执行sql文件
    	mysql -h127.0.0.1 -P3306 -uxxx -pxxx < xxx.sql
    	# 导出ddl和dml（常用于批量备份数据）
    	mysqldump -h127.0.0.1 -P3306 -uxxx -pxxx database_xxx table_xxx > xxx.sql

### 4.性能监控场景 ###

select version();	# 查看数据库版本号
    	select "10" > 9;	# 测试数据库的数据类型转换规则
    	# 查看指定库中超过阈值的表
    	select table_name,table_rows from information_schema.tables where table_schema in ('database_1','database_2') and table_rows>100000 order by table_rows desc;
    	# explain语句
    	explain sql_str;

## 二、explain语句查询结果指标说明： ##

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

#### id ####

表示查询中执行select子句或操作表的顺序，id相同顺序执行，id越大越先执行

#### type ####

常见的执行效率从低到高的顺序为：  
All（全表扫描）；  
index（全索引扫描）对索引表进行全扫描，这样做的好处是不再需要对数据进行排序；  
`range`（索引范围扫描）一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找；  
`ref`（非唯一索引扫描）或唯一索引的非唯一性前缀，返回数据返回可能是多条，一般会在一个非常小的范围内扫描；  
eq\_ref（唯一索引扫描）常使用在`多表联查`中；  
`const`（结果只有一条的主键或唯一索引扫描）。

#### possible\_keys ####

字段表示可能用到的索引

#### key ####

用到的索引的索引名

#### ken\_len ####

用到的索引的字段的长度，与表的字符编码、索引字段数量、字段长度、是否允许NULL（加2）有关

#### Extra ####

Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候， 可能会通过文件排序。  
Using temporary：使了用临时表保存中间结果，对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。  
`Using index`：即使用了覆盖索引，避免了回表操作。  
`Using index condition`：对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

`索引覆盖`：查询语句中的所有返回字段，在用到的二级索引的叶子节点都可以找到，避免再次回表操作  
`索引下推`：（index condition pushdown）在联合索引的情况下，类似"where a > 1 and b = 2"的条件。在5.6版本之前需要从存储引擎中依次将a > 1的数据读取到server层，然后再根据b = 2条件看是否满足，如果满足就返回给客户端不满足就继续下一条，一直循环。在5.6版本之后，b = 2的条件判断就直接在存储引擎层判断了，不需要再返回sever层进行判断。我的理解是：之前再server层进行判断，现在在下层存储引擎层进行判断，所以叫做`下推`。

## 三、索引以及索引失效的情况总结 ##

### 1.左模糊匹配，like ‘%xxx’ like ‘%xxx%’ ###

### 2.函数，where length(name)=6 ###

### 3.进行表达式计算，where id - 1 = 9 ###

### 4.类型转换 select “10” > 9 ###

### 5.联合索引缺少最左条件 ###

### 6.where子句中的or，一边非索引字段 ###

--------------------

## 总结 ##

参考[小林coding][coding]

<table> 
 <thead> 
  <tr> 
   <th>B+Tree----B Tree</th> 
   <th>相同数据量下，B+Tree比B Tree树低，磁盘IO次数更少；B+Tree叶子节点通过双向链表连接，更适合范围查询</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>B+Tree----二叉树</td> 
   <td>二叉树的层高会越来越高，而B+Tree在千万级别数据情况下，树高依然维持在3~4层，意味着最多几次oi就可以查询到数据</td> 
  </tr> 
  <tr> 
   <td>B+Tree----Hash</td> 
   <td>hash结构等值查询性能高，但是却无法做到范围查询</td> 
  </tr> 
  <tr> 
   <td>什么时候适合创建索引？</td> 
   <td>1.字段唯一 2.经常作为where条件 3.经常用于order by或group by</td> 
  </tr> 
  <tr> 
   <td>什么时候不适合创建索引？</td> 
   <td>1.不作为where、order by或group by条件查询 2.索引区分度很小（存在大量重复值的情况） 3.表数量很少 4.经常会发生修改的字段不适合创建索引</td> 
  </tr> 
  <tr> 
   <td>常用索引优化</td> 
   <td>1. 前缀索引 2. 索引覆盖，减少回表 3. 主键索引最好自增 4.防止使用索引失效的语句进行查询</td> 
  </tr> 
 </tbody> 
</table>

[46cc3224cacc423b92172a563552e02e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/80152cd0bbb44948a641ae4624bbecb3.png
[ea398af617d7482987bbf596b14fbc71.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/5af53fcf632140afae57eec218c0e186.png
[coding]: https://www.xiaolincoding.com/mysql/index