MYSQL 日常记录

乌云下的风
47 阅读9分钟
  1. Server 层包括连接器、查询缓存、分析器、优化器、执行器
  2. InnoDB、MyISAM
  3. 连接器mysql -h$ip -P$port -u$user -p
  4. 查看连接show processlist
  5. 执行结果会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。
  6. 参数query_cache_type设置成 DEMAND，用 SQL_CACHE 显式指定。MySQL 8.0删掉了
select SQL_CACHE * from T where ID=10；
  1. 引擎扫描行数跟 rows_examined 并不是完全相同的
  2. redo log（重做日志）和 binlog（归档日志）
  3. WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。InnoDB 的 redo log 是固定大小的，能力称为 crash-safe。
  4. redo log binlog区别
    • redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
    • redo log 是物理日志，binlog 是逻辑日志。
    • redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。
  5. update 语句时的内部流程，3和5 prepare 和 commit就是"两阶段提交"
1. 查询。执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2. 执行。执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

3.告知执行器完成。引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

4.写入。执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

5.完成。执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。
  1. ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）
  2. 脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）、读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。
  3. information_schema 库的 innodb_trx 这个表中查询长事务select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
  4. 索引的常见模型 哈希表、有序数组和搜索树
    • 哈希表这种结构适用于只有等值查询O(1)
    • 有序数组等值和范围查询，有序数组索引只适用于静态存储引擎O(N)、(log(N))、O(n2)
    • 搜索树O(log(N))
  5. InnoDB索引类型分为主键索引和非主键索引。非主键索引也被称为二级索引（secondary index）。非主键索引的查询需要多扫描一棵索引树。称为回表。
  6. B+ 树为了维护索引有序性会出现页分裂 页合并
  7. 覆盖索引可以减少树的搜索次数，显著提升查询性能是一个常用的性能优化手段。 覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。
--两条结果
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
select * from T where k between 3 and 5 -- 查询5次节点
select ID from T where k between 3 and 5 -- 查询2次节点
  1. 联合索引 最左前缀原则 需要调整索引内的字段顺序
  2. 索引下推 性能体现在回表几次
联合索引（name, age）为例
select * from tuser where name like '张%' and age=10 and ismale=1;
  1. 全局锁、表级锁和行锁。表级的锁是 MDL（metadata lock)（加个字段，索引 DDL）(DML 操作数据）
  2. 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
  3. 死锁检测 innodb_lock_wait_timeout默认50s innodb_deadlock_detect=on

24.行锁

mysql>lect k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;
  1. 事务视图
版本未提交，不可见；
版本已提交，但是是在视图创建后提交的，不可见；
版本已提交，而且是在视图创建前提交的，可见。
对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
对于读提交，查询只承认在语句启动前就已经提交完成的数据；
  1. 优化器误判用 force index 来强行指定索引，也可以修改语句来引导优化器，还可以增加或者删除索引。
  2. 使用前缀索引，定义好长度。crc32()、倒序存储、 hash 字段，reverse()和crc32()两个函数的计算复杂度来看，reverse 函数额外消耗的 CPU 资源会更小些。
  3. 使用前缀索引，定义好长度。crc32()
  4. SQL 语句为什么变“慢”,redo log（重做日志）,innodb_io_capacity的默认值为200,关注脏页比例，不要让它经常接近 75%。
mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;
  1. 表中的数据被删除了，但是表空间却没有被回收。innodb_file_per_table 设置为 ON,不止是删除数据会造成空洞，插入数据也会。optimize table、analyze table 和 alter table,analyze table t 其实不是重建表，只是对表的索引信息做重新统计,optimize table t 等于 recreate+analyze 30.count(字段)<count(主键 id)<count(1)≈count(*)
  2. MySQL 怎么知道 binlog 是完整的
  • statement 格式的 binlog，最后会有 COMMIT；
  • row 格式的 binlog，最后会有一个 XID event。
  1. redo log 和 binlog 是怎么关联起来的?它们有一个共同的数据字段，叫 XID
  2. redo log 一般设置多大？redo log 设置为 4 个文件、每个文件 1GB 吧。
  3. ACID中undo log实现原子性+隔离性，redo log 持久性，binlog 一致性。
  4. 全字段排序sort_buffer_size，如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。
  5. rowid 排序，rowid 排序多访问了一次表 t 的主键索引，如果内存够，就要多利用内存，尽量减少磁盘访问。
  6. MySQL 的表是用什么方法来定位“一行数据”的rowid
  7. order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法，执行弱于Using temporary 和 Using filesort。
  8. 内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表 40.磁盘临时表，随机算法 2跟 order by rand() 比，执行代价小
优先队列排序算法
select city,name,age from t where city='杭州' order by name limit 3  ;
归并排序算法
select city,name,age from t where city='杭州' order by name limit 1000  ;
随机排序方法
1.取1、2、4、5 中的一个
select * from t where id >= @X limit 1;
2.取1、2、40000、40001中的一个
select * from t limit , @Y, 1
  1. 对索引字段做函数操作和隐式类型转换，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。“将字符串转成数字”，“将数字转成字符串” “字符集不同”，关联查询中要求在被驱动表的索引字段上加函数操作，优化器优先转成索引类型。
  2. MySQL 启动时需要设置 performance_schema=on
  3. 一条SQL查询慢 等 MDL 锁、等 flush、等行锁、索引，一致性读坏查询不一定是慢查询
  4. 10 万行数据的 b 的值是 1234567890，给引擎执行时字符截断，跟索引做匹配，10 万次回表，结果是空，查询慢
select * from table_a where b='1234567890abcd';
  1. 跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作
  2. 慢查询性能 索引没有设计好 SQL 语句没写好 MySQL 选错了索引。
  1. 上线前，在测试环境，把慢查询日志（slow log）打开，并且把 long_query_time 设置成 0，确保每个语句都会被记录入慢查询日志；
  1. MySQL是怎么保证数据不丢的，binlog 的写入机制，write 和 fsync 的时机，redo log 的写入机制，redo log 可能存在的三种状态。 48.主备延迟，一主多从、通过 binlog 输出到外部系统 大表 DDL，可用性优先策略 ：主备反转再反转
  2. 并发连接和并发查询。

  3. 数据库是不是出问题了？添加数据、内部统计

mysql> CREATE TABLE `health_check` (
  `id` int(11) NOT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

/* 检测命令 */
insert into mysql.health_check(id, t_modified) values (@@server_id, now()) on duplicate key update t_modified=now();

  1. delete 语句误删了数据行，可以用 Flashback 工具通过闪回把数据恢复回来。

  2. 关联查询没有索引Simple Nested-Loop Join``Block Nested-Loop Join 有索引Index Nested-Loop Join看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样。

  3. 小表：在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

  4. 临时表 DROP TABLE t_normal /* generated by server */

  5. union，group by 执行使用临时表，union all可能会使用Index

  6. 执行 insert … select 的时候，对目标表也不是锁全表，而是只锁住需要访问的资源。

  7. grant 命令之后，没有必要跟着执行 flush privileges 命令。当数据表中的权限数据跟内存中的权限数据不一致的时候，flush privileges 语句可以用来重建内存数据，达到一致状态。

  8. 分区：hash分区-平均分配、Range范围分区-范围要连续不能重叠、列表分区、键值分区

avatar
文章
阅读
粉丝