MYSQL2

mysql -h192.168.200.202 -uroot -p123456 beegotable -e "select * from user";

分库分表中间件Mycat

选项	操作
-u	用户名
-p	密码
-h	ip或域名
-P	端口
-e	执行语句并退出

笔记

宝塔放行root

# 登录
mysql -u root -p123456#语法mysql -u 用户名 -p密码

# MySQL库
use mysql

# “%”指的是所有地址
update user set Host='%' where User='root';

# 刷新权限。
flush privileges;

常用命令

查看性能 EXPLAIN

1	EXPLAIN SELECT * FROM `user`

查看执行频次('_'*7)

1	`show global status like 'Com_______'`

批量插入load命令

update

没有索引会把行锁变成表锁

count

MYISAM引擎把表总行数存在了磁盘上，执行count会直接返回
InnoDB需要一行一行读，累积计数

如果count函数的参数不是NULL，累计值就加1，否则不加，最后返回值 - count(*) 不取值，服务层按行直接累加 - count(主键) 遍历全表 - count(字段) 判断是否not null,有的话服务层判断，没有的话直接累加 - count(1) 遍历全表，有行就加1

limit

创建覆盖索引以提高性能，通过覆盖索引加子查询形式进行优化。

SELECT * FROM user limit 200000,10

SELECT * from user u, (SELECT id FROM user order by id limit 200000,10) a where u.id = a.id

order by

排序

using filesort

通过表索引或全表扫描，读取满足条件的数据行，然后排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都是fileSort排序

using index

通过有序索引顺序扫描直接返回有序数据，不需要额外排序，效率高

group by

分组

主键

尽量自增降低主键长度尽量不要使用uuid 尽量不要修改主键

页分裂

插入数据时，当前页和下一页写满时，把当前页拆出一半的数据和新的数据放去新的页，再把新页放在之前的两页中间

页合并

删除行数据后，如果页空间小于50%(默认)，尝试合并上一页或者下一页的数据

引擎

注意

Redis 代替 Memory MongoDB 代替 MYISAM

特点	InnoDB	MyISAM	Memory
存储限制	64TB	256TB	RAM
事务	支持	-	-
锁机制	行锁	表锁	表锁
B+tree索引	支持	支持	支持
Hash索引	-	-	支持
全文索引	支持	支持	-
空间使用	多	少	-
内存使用	多	少	中等
批量插入速度	慢	快	快
外键	支持	-	-

MYISAM

无事务更新、删除表锁访问速度快

文件形式

MYD MYI SDI

文件名	存放信息
表名.MYD	数据
表名.MYI	索引
表名_自增序号.sdi	表结构

INNODB

支持事务行锁支持外键

文件形式

IBD

文件名	存放信息
表名.ibd	全部信息

Memory

数据存放在内存中做临时表或缓存时使用数据非常快支持hash索引(默认)

文件形式

SDI

文件名	存放信息
表名_自增序号.sdi	表结构

索引

二叉树 -> Btree -> B+tree

B+tree索引

最常见的索引

Hash索引

性能很高哈希表实现，不支持范围查询，只能精确匹配查询无法利用索引排序

R+tree 空间索引

主要使用在MyISAM的特殊索引，用于地理空间数据类型，使用少

Full-text 全文索引

建立倒排索引，快速匹配文档的方式

事务

四个特性

ACID Atomicity,Consistency,Isolation,Durability 原子性、一致性、隔离性、持久性

CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。
SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。
NO ACTION: InnoDB拒绝删除或者更新父表。
RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。

RESTRICT 是在更新之前限制， NO ACTION 是在更新之后限制，

RESTRICT（限制外表中的外键改动） CASCADE（跟随外键改动） SET NULL（设空值） SET DEFAULT（设默认值） NO ACTION（无动作，默认的）

锁

全局锁

一致性数据备份

表级锁

表锁
元素据锁（meta data lock，MDL）
意向锁

表锁

分为两大类

表共享读锁（读锁）

大家都可以读，大家都不能写

用户	读	写
我	能	不能
其他	能	不能

表独占写锁（写锁）

只有自己能读写

用户	读	写
我	能	能
其他	不能	不能

元数据锁

MDL加锁过程系统自动控制，在访问时候会自动加上。 MDL锁主要作用维护表元素据的数据一致性，在表上有事务时，不可以对元数据写。

增删改查时加MDL读锁(共享)，修改表结构时加MDL写锁(排他)。

意向锁

避免DML执行时，行锁与表锁冲突事务更新加行锁时会同时加意向锁，其他用户想锁表时会检查是否存在意向锁。

行级锁

操作时锁住对应行对索引进行加锁

行锁

锁定单行，防止其他事务更新、删除。

当前类型\请求类型	共享锁	排他锁
共享锁	兼容	冲突
排他锁	冲突	冲突

共享锁（S）

允许多个共享锁读一行，阻止其他排他锁

排他锁（X）

允许自己更新事务，阻止其他共享锁和排他锁增删改自动加锁

间隙锁

所住前后间隙，防止插入。

临键锁

行锁+间隙锁的组合前面间隙和当前都锁住

视图

记录查询语句，生成虚拟表，方便查看

函数

封装复用

过程

函数

in 输入 out 输出，可以做返回值 inout 既可以输入也可以输出

MVCC

多版本并发控制

为了判断未提交的事务所带来的影响

当前读

读取当前最新记录，会对读的数据进行加锁

快照读

不加锁，可能是历史数据，非阻塞

undo log

回滚日志