mysql存储引擎

myisam

三种索引 B-Tree R-Tree Full-text
分为静态表动态表可压缩表有varchar字段表自动变为动态表
表类型可以强制转换，但是字段类型会变

Innodb

支持事务，即使数据库挂掉重启后也能恢复

mysql安全

外围网络，将Mysql放在局域网环境内
主机，拦截试图入侵主机的行为
数据库本身权限

Mysql权限

分为五级优先级更高的授权可以覆盖低级授权
全局->库->表->列->routing level
特殊的权限控制 Grant all on . to xxx 将某个level的权限给某个用户
%为全省意为全部
Grant index on test.t1 To 'abc'@%

Mysql访问控制原理

客户端请求->用户管理模块->客户端QUERY->解析QUERY->权限匹配->执行

用户管理模块

授权用户放在mysql.user表验证主机用户名密码
在mysql启动时，加载进内存。
如果对权限作了修改，使用flush privileges命令，重新将权限加载进内存。
对已经连接上的用户，如果权限发生了变化，视情况而定。

Mysql备份恢复

逻辑备份

生成insert语句方法通过mysqldump语句加上--single-transaction参数仅对于支持事务的引擎 innodb等或者使用--lock--table参数锁定表，只允许查询不允许插入
纯文本文件方法 select xxx to outfile from xxx

逻辑恢复

insert语句备份方法恢复 mysql<mysqlback.sql 如果已经连接上了mysql 使用source /path/back.sql
纯文本文件方法恢复 load data infile或者使用工具

1.表锁：最基本的锁策略，开销最小。没有写锁时，才能获得读锁。写锁可以插入到读锁前。读锁不能插入到写锁前。
2.行锁：支持最大并发，开销大。
3.事务四种隔离级别：未提交读提交读可重复读(mysql默认) 可串行
4.死锁：多个事务同时锁定资源，产生死锁。
5.事务中混合使用了事务型的表和非事务型的表，回滚时会出问题。非事务性的表数据无法回滚。
6.事务中的多版本并发控制，和隔离级别相关。
7.Innodb 事务型表。首选。存储格式是平台独立的。崩溃后可恢复
8.myisam 默认表。
9.转换表引擎：1.alter table 2.导出后导入，修改引擎 3.创建新表，insert table xxx select * from xx
第三种方法最好。数据量大的话可以分批
10.show status show processlist
11.选择数据类型：
一、越小越好。二、简单就好。三、避免NULL
12.整形 int 有符号和无符号使用相同的存储空间和使用性能。int(1)和int(11)效果一样，只是规定客户端用来显示字符的个数。
13.DECIMAL(18，9) 小数点两边各存9个数字 4个字节存9个数字小数点占一个字节共9个字节
14.DECIMAL消耗比float大
15.varchar需要额外的一个或两个字节记录长度。varchar(10)需要11个字节。varchar(1000)需要1002个字节，大于255个字节需要两个字节记录长度。
16.char存储效率大于varchar
17.char(1) 一个字节 varchar(1)两个字节一个字节记录长度
18.varchar(5) 和 varchar(100) 存储的空间开销是一样的，但是更长的列会消耗更多的内存。
19.枚举ENUM实际存储的是数字而不是字符。避免枚举数字。枚举是按照内部存储的整数排序，而不是字符排序。
20.枚举可以让表缩小。
21.不同类型的字段最好不要相关联。
22.mysql能存储的最小时间粒度是秒。
23.datetime 保存较大时间范围，精度秒。8个字节
24.timestamp 4个字节。和时区有影响。通常情况下使用timestamp 空间使用效率高
25.存储微妙级的时间。bigint类型存时间戳。double类型存秒后面的小数部分。
26.不要太多列。不要太多关联。不要太多枚举。不要NULL。注意可变长字段，在临时表和排序时会按照最大长度分配内存。使用整形定义标识列。
27.快速alter表。
ALTER table xx MODIFY COLUMN XX TINYINT(3) NOT NULL DEFAULT 3;
ALTER table xx ALTER COLUMN XX SET DEFAULT 3;
第二种比较快，直接修改.frm文件不涉及表数据。第一种相当于重建表。
28.迁移数据到myisam表的时候，可以先禁用key，然后再启用。这个方法对唯一索引无效。
ALTER TABLE XX DISABLE KEY
ALTER TABLE XX ENABLE KEY
29.B-Tree MyISAM 索引通过数据的物理物质引用被索引的行。InnoDB根据主键引用被索引的行。MyIsam索引更小，InnoDB按照原数据格式存储索引，较大。
30.索引列不能是表达式或者函数的参数，否则索引会失效。

mysql基础笔记