读书笔记-mysql王者
- 系统默认表
- mysql远程连接方法:
mysql -u root -p 123456 -P 3306 -h 192.168.20.6 - 用户权限:分为root和其他用户,root用户要避免web连接
- query cache:生产中建议关闭,因为只缓存静态数据,如果数据经常读写作用不大
SHOW VARIABLES LIKE '%query_cache_size%'
SHOW VARIABLES LIKE '%query_cache_type%' - Innodb存储结构
InnoDb主要分为表空间,段,区和页
SHOW VARIABLES LIKE '%innodb_autoextend_increment%' 初始自动化扩展值为64m
SHOW VARIABLES LIKE '%innodb_data%'
默认为12M
段:每个段由N个区和32个零散页组成
区:由连续的页组成,是物理上连续的一段空间,每个区固定大小是1MB
页:
- 内存结构: 分为SGA(系统全局区)和PGA(程序缓存区)
- 各大线程及其作用
- 内存刷新机制
- 数据库文件
表
1.禁止用字符型做主键
2.禁止无主键或唯一索引的表出现
3.主要数据类型:整型,浮点型,字符类型和日期类型
- 整型:
int和tinyint用的最多
主键要选择不经常修改的,尽量与业务无关的,没有具体含义的字段
因为Innodb是索引组织表,需要保证索引结构不经常翻转,避免造成性能损耗
如果要显示0003这样,显示宽度的值,需要加上zerofill,不然的话,int(1)和int(10)都是一样的 - 浮点型:生产环境不建议使用float和double,因为是属于不精确的类型,一般使用decimal类型
- 时间类型
TIMESTAMP
4个字节储存;值以UTC格式保存;.时区转化 ，存储时对当前的时区进行转换，检索时再转换回当前的时区。
DATETIME
8个字节储存;实际格式储存;与时区无关;datetime 以'YYYY- MM-DD HH:MM:SS'格式检索和显示DATETIME值。支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'TIMESTAMP值不能早于1970或晚于2037
INT
存时间戳。占用资源少，查询速度快。条件范围搜索使用between没什么问题。查询条件自由拼接。
datetime和timestamp相对于int来说也有一个小小的好处，就是对于时间类型来说，可以有一系列的时间函数可以用.
-
字符串类型
推荐使用int保存ip地址
-
字符集: mysql5.7推荐使用utf8mb4
-
表delete导致的碎片:
-
索引种类:
主键索引和唯一索引:
前缀索引:
eg: alter table x_test add index(x_name(4));
联合索引: 使用过程需要满足最左前缀原则,一般把选择性高的列放前面
eg:
用不到索引:
哈希索引:
- Innodb锁类型:
读锁:一个事务获取了数据行的读锁,另外一个事务能获取读锁,但是不能获得写锁
写锁: 一个事务获取了写锁,其他事务就不能获取该行的其他锁,写锁优先级最高
MDL锁:
意向锁:
- Innodb行锁种类
- Innodb的行锁是加载索引上的,如果索引删除了,会变成对所有行上锁
- 间隙锁: SELECT * FROM x_test WHERE id <4145026 LOCK IN SHARE MODE; 在小于这个范围的值都会被锁定
- 记录锁和间隙锁组合: SELECT * FROM x_test WHERE id <4145026 for update 在这条记录和小于这条记录的范围都会被锁定
mysql备份
- 根据运行状态,可以分为热备和冷备,热备分为逻辑备份和裸文件备份,按照备份后内容分为全量备份和增量备份
冷备: 直接将数据文件复制到新的地方
热备:
1.myslqdump:
单表备份:mysqldump --single-transaction -uroot -p123456 demo test > test.sql
单表恢复:mysql -uroot -p123456 demo < test.sql
- select ... into outfile
导出: 必须要指定目录(show global variables like '%secure_file_priv%';)select * from test into outfile '/var/lib/mysql-files/a.sql';
导入:load data infile '/var/lib/mysql-files/a.sql' into table test;
load data的插入速度是insert的12倍
3.mydumper
4.裸文件备份:XtraBackup 直接复制低等数据文件备份,备份时不会锁表
主从复制
- 一般企业用到的mysql集群
