Mysql笔记

Mysql

索引

CREATE INDEX idx_column_name ON table_name (column_name);
根据EXPLAIN来确定是否走索引

• 主键索引（Primary Key Index）：主键索引是唯一索引的一种，表中每一行都有一个唯一的标识符。主键索引不允许有NULL值。
• 唯一索引（Unique Index）：唯一索引保证索引列的所有值都是唯一的，但允许NULL值。
• 普通索引（Index）：普通索引是最基本的索引类型，可以加速查询，但不保证唯一性。
• 全文索引（Fulltext Index）：主要用于对文本字段进行全文搜索，适用于大文本字段的搜索。
• 组合索引（Composite Index）：由多个列组成的索引，可以加速对多个列的查询。
  复合索引的顺序很重要，通常将选择性高的列放在前面

索引失效

• 索引列使用函数
• 索引列使用运算符
• 索引列使用OR 当OR操作符前后连接的条件中，有一个或多个条件涉及的列没有被索引覆盖时，索引可能会失效
• 索引列使用LIKE ‘%xxx’
• 索引列使用!=或<>
• 索引列使用IS NULL或IS NOT NULL
• 索引列使用IN或NOT IN
• 索引列使用BETWEEN
• 隐式类型转换

主从

• 主从同步：主库负责写入数据，从库负责读取数据。
• 主库和从库之间通过binlog进行数据同步，当主库写入数据时，会生成binlog日志并发送给从库，从库接收后执行相应的SQL语句来更新自己的数据。

分库分表

• jdbc client模式，如shardingsphere，需要客户端配置分片策略
• proxy模式，客户端无感，如mycat

事务级别

• READ UNCOMMITTED（读取未提交数据）
  可以读取其他事务未提交的数据。这种级别下存在脏读（dirty read）的风险，即一个事务读取到了另一个事务未提交的数据。
• READ COMMITTED（读取已提交数据）
  只能读取到已经提交的数据。这种级别下避免了脏读，但仍然存在不可重复读（non-repeatable read）和幻读（phantom read）的风险。
• REPEATABLE READ（可重复读）
  事务在执行期间可以多次读取相同的数据，并且保证读取到的数据是一致的。这种级别下避免了不可重复读，但仍然存在幻读的风险
• SERIALIZABLE（串行化）
  事务的执行顺序是串行化的，即每个事务都会等待前一个事务执行完成后才能执行。这种级别下可以避免幻读，但可能会导致性能下降。

锁

MySQL也会根据执行的SQL语句和事务的隔离级别自动应用锁。
在某些情况下，如果多个事务尝试锁定表中的大量行，MySQL可能会决定升级锁定级别，从行锁升级到表锁，以提高性能。这种情况通常发生在InnoDB存储引擎中，尽管它主要支持行级锁定。

全局锁

• 全库备份，导出时，加全局读锁，备份期间其他语句会被阻塞
• 一般为隐式使用，也可以显式使用，flush tables with read lock;

表级锁

• MyISAM存储引擎的操作：MyISAM存储引擎不支持行级锁，只支持表级锁。因此，在对MyISAM表进行读写操作时，会自动使用表级锁。
• 某些特定的DDL操作：例如，在对表进行结构变更（如添加、删除列）时，通常需要使用表级锁来确保操作的一致性。
• 简化事务管理：在一些场景下，为了简化事务的管理和降低复杂性，可能会选择使用表级锁而不是更细粒度的行级锁。
• LOCK TABLES语句可以显式地请求表级锁。例如，LOCK TABLES table_name WRITE;会对指定的表应用排他锁，而LOCK TABLES table_name READ
• UNLOCK TABLES语句可以释放所有表级锁。

行级锁

• nnoDB存储引擎的默认锁定方式：InnoDB存储引擎支持行级锁作为默认的锁定机制。当在InnoDB表上进行数据修改操作时（如UPDATE、DELETE），会自动使用行级锁来锁定被修改的行。
• 高并发读写操作：在需要处理大量并发读写操作的场景中，行级锁可以显著提高数据库的性能和并发度。
• 行级锁可以通过在SQL查询中使用FOR UPDATE或LOCK IN SHARE MODE子句来显式请求

窗口函数

OVER 开窗
PARTITION BY 分类
ORDER BY 排序

• ROW_NUMBER() 为窗口中的每一行分配一个唯一的序号
• RANK() 和 DENSE_RANK()：为窗口中的每一行分配一个排名。
  • RANK() 如果有两行或多行具有相同的排序值，则它们会获得相同的排名,下一排行会留下间隙；
  • DENSE_RANK() 如果有两行或多行具有相同的排序值，它们会获得相同的排名，但下一行的排名将紧随其后，不会跳过任何数字。
• LAG() 和 LEAD()：访问窗口中当前行的前一行或后一行的数据。
• 聚合函数如 SUM()、AVG() 等也可以在窗口中使用。