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

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

---

下文所有命令默认为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;

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语句查询结果指标说明：

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

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