Mysql常用关键字指令和参数总结

欢迎关注微信公众号「野狐」

最近在学习极客时间上的mysql课程，对mysql数据库有了更多了解，本篇文章是想总结一些mysql的基础知识。目的是加深自己的记忆，也可以提升对mysql设计原理的了解。

关键字

关键字	作用	关键字	作用
avg	平均值	as	别名
add	加字段	analyze	分析表
alter	声明修改	asc	升序排序
after	插入在字段后	before	插入在字段前
between	查询范围	convert	类型转换
count	计数	create	创建表
change	修改字段	cast	类型转换
delete	删除数据	des	降序排序
distinct	查询结果去重	drop	删除表
declare	声明变量	delimiter	命令结束符设置
exist	是否存在	explain	执行计划
force	强制	from	查询来源
fetch	获取数据	grant	授权
group	分组	having	分组后筛选结果
in	范围判断	insert	插入数据
join	查询结果合并	like	模糊查询
limit	限制返回数据	order	排序
max	最大值	min	最小值
or	或者	offset	和limit配合使用
show	展示表和数据库	set	设置
select	选择查询	sum	取和
use	选择数据库	update	更新
union	合并结果	where	查询条件关键字

---

名词

transaction:事物

view：视图

consistent read view：一致性视图

redo log：重做日志，物理日志，记录数据页上做的什么修改

binlog：归档日志，逻辑日志，记录语句的原始逻辑，即sql语句

WAL：write ahead logging，更新数据时先写redo log后写磁盘

 

crash-safe：程序异常时不丢数据

B+树：innodb的索引存储方式，索引不仅存在内存，而且还存在磁盘

clustered index：聚簇索引，就是主键索引

secondary index：非主键索引，基于非主键索引查询需要多查一次主键索引树，即回表。

覆盖索引：比如以一个索引为where条件查ID，这个索引树上就有id，不需要查主键索引树回表。

索引最左前缀：联合索引最左面开始的n个字节优先索引

索引下推：联合索引ab上可以优先判断b是否满足然后再去回表，提高效率，降低回表次数

前缀索引：不能用到覆盖索引，即需要回表

页分裂：插入数据时，索引上的数据页满了，需要新申请数据页

Auto_Increment:自增主键，有序插入避免了页分裂，提高效率降低空间使用率。

ACID：atomicity原子性，consistency一致性，isolation隔离性，durability持久性

dirty read：脏读

non-repeatable read：不可重复读

phantom read：幻读

幻读：同一个事物，前后两次读同一个范围得到的结果不同，因为后一次有新插入导致。

read uncommited: 读未提交

read commited：读提交

repeatedable read：可重复读

seraileable：串行化

当前读：读到所有数据的已经提交的最新值。

读提交隔离级别的优势：语句执行过程中加的行锁，在语句执行完成后就把不满足条件的锁释放了，不用等到事务提交，比可重复度和串行化更快。

过期读：因为主从延迟导致读的数据是旧的

短链接风暴：短链接太多导致mysql不可用

Dead Lock：死锁

next-key lock：间隙所和行锁一起
Gap Lock: 间隙锁

全局锁：Flush tables with read lock命令实现，整个数据库只能读

两阶段锁：在提交事物后才释放行锁。因为锁在最后释放，所以如果锁多行或者多个表的行，要把锁冲突最大的放在最后。

 

排序算法：小数据量内存中快排，大数据外部排序归并，还有order by limit小数据用堆排序（优先队列）

io_thread：主备同步时备库用来连接主库的线程

sql_thread：执行日志中的命令线程

双M: 主备切换的结果

commit_id：一组事务里一起提交的事务有相同的commit_id

 

frm：mysql表结构文件

ibd：mysql表数据文件

 

脏页：内存数据和磁盘不一致的数据页

刷脏页：就是把内存中的数据刷到磁盘上

MVCC：数据库的多版本并发控制

mysqldump：mysql官方自带的逻辑备份工具

DML：增删改数据

DLL：修改表结构

MDL：meta data lock元数据锁，加MDL写锁时才能做修改字段

DDL NOWAIT/WAIT n：在执行alter修改表结构时支持等待时间，对热点表修改字段有帮助。

快照：数据库的当时状况

低水位：每个事物启动后和提交前活跃的事物列表里最小的事物id

高水位：每个事物启动后和提交前活跃的事物列表里最大的事物id

net_buffer：网络缓存区

socket send buffer：发送缓存区

socket receive buffer：客户端接收缓存

mysql_store_result：客户端把查询结果缓存在本地

buffer pool: 输入输出缓存，能提高读写效率，命中率即内存命中率，越高越好

NLJ：Index Nested-Loop Join算法，效果最好

SNL：Simple Nested-Loop Join算法

BNL：Block Nested-Loop Join 算法，扫描过度尽量不用

join_buffer: join 的内存缓存

MRR：multi-range read，尽量顺序读盘

read_rnd_buffer：通过二级索引读到id放到此缓存区

Index Organizied Table：索引组织表，innodb是把数据都放到B+树即主键索引上

Heap Organizied Table：堆组织表，memory引擎数据单独存储，索引上保存数据的地址

---

参数

transaction-isolation：事物的隔离级别配置

innodb_lock_wait_timeout：死锁等待超时时间，默认值50s，太长了。。。

innodb_deadlock_detect：死锁检测

transaction id：事物id

row trx_id：数据版本的事物id

undo log：回滚日志

readonly: 只读模式，从库或者备库

wait_timeout：mysql连接空闲超过一段时间后，mysql会断开这个连接

max_connections：mysql的最大可接受连接数

sys.innodb_lock_waits： 显示系统中的锁状态

binlog-checksum: 用来校验binlog是否完整

sort_buffer: 排序需要的空间

number_of_tmp_files： 为排序准备的临时文件数

OPTIMIZER_TRACE：mysql执行过程的跟踪

Using temporary：explain执行接话结果，需要使用临时表

Using filesort：explain执行接话结果，需要使用排序

binlog_format：mixed，row，statement

start-position：解析日志开始的位置

seconds_behind_master：备库延迟时间

slave_parallel_workers：主备同步的worker线程数

slave_skip_errors：主从同步时跳过错误

GTID : Global Transaction Identifie全局事务id

innodb_thread_concurrency：限制并发线程数，建议64到128

performance_schema：mysql一些统计表，包括磁盘利用率等

sql_safe_updates：mysql安全更新参数，delete时不加where条件或者没有索引会报错

query_cache_type:  查询缓存类型

innodb_old_blocks_time：mysql的lru算法分old和young区，此参数用来控制时间阈值

long_query_time: 慢查询日志参数

 

cardinality：索引上不同值的个数基数，也就是区分度

 

innodb_io_capacity：磁盘的能力参数

innodb_flush_neighbors：刷脏页是否刷邻居页参数

innodb_file_per_table：控制表数据放在共享表空间还是文件中，默认放在.ibd文件

change buffer: 数据库更新的缓存，在内存中。唯一索引更新用不上

innodb_change_buffer_max_size：change buffer的大小，用buffer pool里的内存

innodb_flush_log_at_trx_commit：每次事物的redolog是否直接持久化到磁盘

sync_binlog：每次事物的binglog是否持久化到磁盘

wait_timeout：客户端如果太长时间没动静，连接器就会自动将它断开，默认值是 8 小时。

rows_examined ：你会在数据库的慢查询日志中看到一个 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

net_buffer_length：网络缓存区长度

join_buffer_size: join缓存区大小

read_rnd_buffer_size: read_rnd_buffer 大小

auto_increment_offset：自增id开始数字

auto_increment_increment：自增id步长

innodb_autoinc_lock_mode：表示语句结束才释放锁

---

命令

prepare: sql语句预处理

show slave status：返回备库状态

start slave：从库开始同步主库

force index(a)：强制使用索引a

show index from t : 查看表的索引

 

analyze table t : 重新统计索引信息

show variables：查看配置

show processlist ：查看当前数据库连接情况

SQL_CACHE:查询缓存

kill connection：杀掉执行线程

join：如果被驱动表能用上索引，使用join比不使用要好，且要用小表做驱动

show create table：显示建表语句

---

查询优化

explain结果

1. 有join时尽量在extra有Using MRR

2. 有join时尽量在extra中没有BNL

3. Using index 用到覆盖索引

4. Using temporary用到临时表，可能是内存也可能是磁盘，尽量不用

5. Using filesort需要排序，尽量不用

6. group by 时如果不需要结果排序，加上order by null，可以避免排序

7.  如果对字段用了函数就不能用索引

8. 不按照字段类型查询，将会用全索引扫描

9. 表的字段字符集尽量统一，否则可能关联时无法使用索引

10. 尽量不用查询不需要的字段，无端增加开销