读书笔记-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
  

2. 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集群