范式

第一范式要求表中的每一列都是不可分割的原子值

第二范式要求表中的每一列都与主键直接相关

第三范式要求每一列都应该只与主键相关，而不与其他非主键列相关。

乐观锁悲观锁

zhuanlan.zhihu.com/p/139007138

乐观锁 (cas or 版本号) 加自旋

1）CAS（定义见后）操作方式：即compare and swap 或者 compare and set，涉及到三个操作数，数据所在的内存值，预期值，新值。当需要更新时，判断当前内存值与之前取到的值是否相等，若相等，则用新值更新，若失败则重试，一般情况下是一个自旋操作，即不断的重试。

2）version方式：一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。

悲观锁 [SELECT ... FOR SHARE]

[SELECT ... FOR UPDATE]

悲观锁与乐观锁的区别两种锁各有优缺点，不可认为一种好于另一种，比如像乐观锁，适用于冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

Locking Reads

[SELECT ... FOR SHARE]

Sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.

[SELECT ... FOR UPDATE]

For index records the search encounters, locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... FOR SHARE, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying [undo logs] on an in-memory copy of the record.)

int（1）

blog.csdn.net/qq_35387940… nt （1）在mysql 里面是指 显示长度！！！

跟存值范围没半毛钱关系！ 跟存值范围没半毛钱关系！ 跟存值范围没半毛钱关系！

为什么经常很多同事动不动就 int（10） int（11）呢？

其实这10和11 来源也是有说法的，记得前文我说的，对于mysql 创建int 类型，默认就是有符号。也就是说范围在 -2147483648 ~ 2147483647 。

2147483647 长度 10

-2147483648 长度 11

如果就是想用的比较安心一点（强迫症），那如果是默认int （默认是有符号），那你就int（11）好了；如果是无符号 unsigned int ，那你就int （10）好了。

MySQL有哪些常见的存储引擎？

InnoDB is the most widely used storage engine with transaction support. It is an ACID compliant storage engine. It supportsz row-level locking, crash recovery and multi-version concurrency control. It is the only engine which provides foreign key referential integrity constraint. Oracle recommends using InnoDB for tables except for specialized use cases. 支持事务和外键

MyISAM is the original storage engine. It is a fast storage engine. It does not support transactions. MyISAM provides table-level locking. It is used mostly in Web and data warehousing. 不支持事务

存储引擎的选择

如何选择：

是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；
如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。
系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB；
MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的。如果你不知道用什么存储引擎，那就用InnoDB，至少不会差

外键

MySQL supports foreign keys, which permit cross-referencing related data across tables, and foreign key constraints, which help keep the related data consistent.

A foreign key relationship involves a parent table that holds the initial column values, and a child table with column values that reference the parent column values. A foreign key constraint is defined on the child table.

clustered index， Secondary Indexes

The InnoDB term for a primary key index. InnoDB table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. For best performance, choose the primary key columns carefully based on the most performance-critical queries. Because modifying the columns of the clustered index is an expensive operation, choose primary columns that are rarely or never updated.

Indexes other than the clustered index are known as secondary indexes

covering index

An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O. InnoDB can apply this optimization technique to more indexes than MyISAM can, because InnoDB secondary indexes also include the primary key columns. InnoDB cannot apply this technique for queries against tables modified by a transaction, until that transaction ends.

（If a secondary index record is marked for deletion or the secondary index page is updated by a newer transaction, the covering index technique is not used）

B+树

B+树是B-树的变体，也是一颗多路搜索树。一棵m阶的B+树主要有这些特点：

Each intermediary node can have ⌈m/2⌉ to m children.
相邻叶子节点是通过指针连起来的，并且是关键字大小排序的。

b tree vs b+ tree

Basis of Comparison	B tree	B+ tree
Pointers	All internal and leaf nodes have data pointers	Only leaf nodes have data pointers
Search	Since all keys are not available at leaf, search often takes more time.	All keys are at leaf nodes, hence search is faster and more accurate.
Redundant Keys	No duplicate of keys is maintained in the tree.	Duplicate of keys are maintained and all nodes are present at the leaf.
Leaf Nodes	Leaf nodes are not stored as structural linked list.	Leaf nodes are stored as structural linked list.

B+树插入要记住这几个步骤：

1.B+树插入都是在叶子结点进行的，就是插入前，需要先找到要插入的叶子结点。
2.如果被插入关键字的叶子节点，当前含有的关键字数量是小于阶数m，则直接插入。
3.如果插入关键字后，叶子节点当前含有的关键字数目等于阶数m，则插，该节点开始「分裂」为两个新的节点，一个节点包含⌊m/2⌋ 个关键字，另外一个关键字包含⌈m/2⌉个关键值。(⌊m/2⌋表示向下取整，⌈m/2⌉表示向上取整，如⌈3/2⌉=2)。
4.分裂后，需要将第⌈m/2⌉的关键字上移到父结点。如果这时候父结点中包含的关键字个数小于m，则插入操作完成。
5.分裂后，需要将⌈m/2⌉的关键字上移到父结点。如果父结点中包含的关键字个数等于m，则继续分裂父结点。

InnoDB-棵B+树可以存放多少行数据

磁盘扇区、文件系统、InnoDB存储引擎都有各自的最小存储单元。

在[MySQL]中我们的InnoDB页的大小默认是16k，当然也可以通过参数设置

这里我们先假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数*单个叶子节点记录行数 。

上文我们已经说明单个叶子节点（页）中的记录数=16K/1K=16。（这里假设一行记录的数据大小为1k，实际上现在很多互联网业务数据记录大小通常就是1K左右）。

那么现在我们需要计算出非叶子节点能存放多少指针？

其实这也很好算，我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170 。那么可以算出一棵高度为2的B+树，能存放1170*16=18720 条这样的数据记录。

根据同样的原理我们可以算出一个高度为3的B+树可以存放 ：1170117016=21902400 条这样的记录。

所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

buffer pool

The memory area that holds cached InnoDB data for both tables and indexes. For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache, using a variation of the LRU algorithm. On systems with large memory, you can improve concurrency by dividing the buffer pool into multiple buffer pool instances.

crash recovery

Tablespace discovery
- Tablespace discovery is the process that InnoDB uses to identify tablespaces that require redo log application.
Redo log application
- Redo log application is performed during initialization, before accepting any connections. If all changes are flushed from the [buffer pool]to the tablespaces at the time of the shutdown or crash, redo log application is skipped. InnoDB also skips redo log application if redo log files are missing at startup.
[Roll back] of incomplete [transactions]
[Change buffer] merge
[Purge]

redoLog，undoLog，binLog 的作用

binlog

binlog ,mysql操作记录归档的日志，用于归档、恢复、同步数据。

记录了除了查询语句(select、show)之外的所有的 DDL 和 DML 语句,也就意味着我们基本上所有对数据库的操作变更都会记录到binlog里面。binlog以事件形式记录，不仅记录了操作的语句，同时还记录了语句所执行的消耗的时间。 binlog 有三种记录格式，分别是ROW、STATEMENT、MIXED。

1、ROW： 基于变更的数据行进行记录，如果一个update语句修改一百行数据，那么这种模式下就会记录100行对应的记录日志。

2、STATEMENT： 基于SQL语句级别的记录日志，相对于ROW模式，STATEMENT模式下只会记录这个update 的语句。所以此模式下会非常节省日志空间，也避免着大量的IO操作。

3、MIXED： 混合模式，此模式是ROW模式和STATEMENT模式的混合体，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog。

这三种模式需要注意的是：使用 row 格式的 binlog 时，在进行数据同步或恢复的时候更能保持一致。而使用 mixed 或者 statement 格式的 binlog 时，很多事务操作都是基于SQL逻辑记录，我们都知道一个SQL在不同的时间点执行它们产生的数据变化和影响是不一样的，所以这种情况下，数据同步或恢复的时候就容易出现不一致的情况。

binlog 写入策略

在进行事务的过程中，首先会把binlog 写入到binlog cache中（因为写入到cache中会比较快，一个事务通常会有多个操作，避免每个操作都直接写磁盘导致性能降低），事务最终提交的时候再吧binlog 写入到磁盘中。当然事务在最终commit的时候binlog是否马上写入到磁盘中是由参数 sync_binlog 配置来决定的。

1、sync_binlog=0 的时候，表示每次提交事务binlog不会马上写入到磁盘，而是先写到page cache,相对于磁盘写入来说写page cache要快得多,不过在Mysql 崩溃的时候会有丢失日志的风险。

2、sync_binlog=1 的时候，表示每次提交事务都会执行 fsync 写入到磁盘；

3、sync_binlog的值大于1 的时候，表示每次提交事务都先写到page cach，只有等到积累了N个事务之后才fsync 写入到磁盘，同样在此设置下Mysql 崩溃的时候会有丢失N个事务日志的风险。

很显然三种模式下，sync_binlog=1 是强一致的选择，选择0或者N的情况下在极端情况下就会有丢失日志的风险，具体选择什么模式还是得看系统对于一致性的要求。

redo log

The redo log is a write ahead log of changes applied to contents of data pages.

A mini-transaction is an atomic set of page reads or writes, with write-ahead redo log.

The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions.

Modifications that did not finish updating data files before an unexpected shutdown are replayed automatically during initialization and before connections are accepted

Redo log data is appended as data modifications occur, and the oldest data is truncated as the checkpoint progresses.

Redo log application is performed during initialization, before accepting any connections. If all changes are flushed from the [buffer pool] to the [tablespaces] (ibdata* and *.ibd files) at the time of the shutdown or crash, redo log application is skipped. InnoDB also skips redo log application if redo log files are missing at startup.

mysql8 redo log architecture

Prior to MySQL 8.0.30, InnoDB creates two redo log files in the data directory by default, named ib_logfile0 and ib_logfile1, and writes to these files in a circular fashion.

The new redo log size is defined by one single variable: innodb_redo_log_capacity (in bytes). The default is 100MB.

InnoDB creates 32 redo log files in MySQL’s datadir inside a new dedicated folder: #innodb_redo.

checkpoint_lsn (Innodb_redo_log_checkpoint_lsn) : an LSN point up to which all changes to the pages are guaranteed to have already been written and fsynced back to tablespace files – basically, the still needed portion of redo log starts here.
current_lsn (Innodb_redo_log_current_lsn) : the last written position in the redo log. That write could still be buffered inside MySQL processes buffer.
flushed_to_disk_lsn (Innodb_redo_log_flushed_to_disk_lsn) : the last position in the redo log that InnoDB has been flushed to disk.

Checkpointing

Each time data is changed in InnoDB, the page(s) containing the data is modified in memory (in the InnoDB Buffer Pool). The page(s) is (are) noted as dirty. In case of a sudden crash, the data in memory is gone ! This is why diff data of the pages are also written (and by default flushed to disk) on the redo logs. The data in those logs will be read only in case of InnoDB Recovery. During that process the modified pages will be reconstructed with the modified data.

InnoDB flushes those dirty pages from the Buffer Pool (memory) to the table spaces (disk) in small batches, step by step. This operation is called Fuzzy Checkpointing.

Once the pages are written to the data files on disk (InnoDB tablespaces), the corresponding entries in the Redo Log are not required anymore. The position up to which InnoDB has written the data to the disk is the value of Innodb_redo_log_checkpoint_lsn.

刷新到磁盘

时机：

1.MySQL正常关闭的时候;
2.MySOL的后台线程每隔一段时间定时的将redo log缓冲区刷入到磁盘，默认是每隔1s刷一次;
3.当重做日志缓冲区中的日志写入量超过重做日志缓冲区内存的一半时，会触发重做日志缓冲区的刷盘;
4.当事务提交时，根据配置的参数 innodb_flush_log_at_trx_commit 来决定是否刷盘,
- The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
- With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
- With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

undo log

An undo log record contains information about how to undo the latest change by a transaction to a [clustered index] record.

A transaction writes undo log before modifying indexes.

用于回滚事务，还用于实现MVCC

trasaction and logs

mariadb.org/wp-content/… A mini-transaction is an atomic set of page reads or writes, with write-ahead redo log.

A transaction writes undo log before modifying indexes.

The read view of a transaction may access the undo logs of newer transactions to retrieve old versions.

Purge may remove old undo logs and delete-marked records once no read view needs them.

mini-transaction

A mini-transaction is an atomic set of page reads or writes, with write-ahead redo log.

A mini-transaction is not a user transaction. It is a short operation comprising:

A list of index, tablespace or page locks that have been acquired. - A list of modifications to pages.

There is no rollback for mini-transactions.

Commit will:

Append the log (if anything was modified) to the redo log buffer
- Release all locks

A mini-transaction can only modify one index at a time.

update语句的执行过程

UPDATE talk SET attendees = 25 WHERE conference="M|18" AND name="Deep Dive";

sql layer:

Constructs a parse tree.
Checks for permissions.
Acquires metadata lock on the table name (prevent DDL) and opens the table.
Retrieves index cardinality statistics from the storage engine(s).
Constructs a query execution plan.
总结：解析sql,生成查询执行计划

2a. Read via the Storage Engine Interface

Find the matching record(s) via the chosen index
On the very first read, InnoDB will lazily start a transaction:
- Assign a new DB_TRX_ID (incrementing number global in InnoDB)

2b. Filtering the Rows

2c. Locking the rows

InnoDB will write-lock each index leaf read
All records that may be changed are locked exclusively

3a. Undo log records

InnoDB writes each undo log in advance, before updating each index affected by the row operation.

3b. Updating the Matching Rows

The primary key (clustered index) record will be write-locked
An undo log record will be written in its own mini-transaction.
The primary key index will be updated in its own mini-transaction. - For each secondary index on updated columns, use 2 mini-transactions:
- (1) search, lock, delete-mark old record, (2) insert new record.

4a. commit

Because autocommit was enabled and there was no BEGIN, the SQL layer will automatically commit at the end of each statement.
In InnoDB, commit will assign an “end id” of the transaction and update the undo log information in a mini-transaction ？？

With the binary log enabled, MySQL/MariaDB uses XA/2-phase commit between the binary log and the storage engine to ensure the needed durability of all transactions. In XA, committing a transaction is a three-step process:

First, a prepare step, in which the transaction is made durable in the engine(s). After this step, the transaction can still be rolled back; also, in case of a crash after the prepare phase, the transaction can be recovered.
If the prepare step succeeds, the transaction is made durable in the binary log.
Finally, the commit step is run in the engine(s) to make the transaction actually committed (after this step the transaction can no longer be rolled back).

The idea is that when the system comes back up after a crash, crash recovery will go through the binary log. Any prepared (but not committed) transactions that are found in the binary log will be committed in the storage engine(s). Other prepared transactions will be rolled back. The result is guaranteed consistency between the engines and the binary log.

4b. Cleaning up

After User COMMIT is done, transactional row locks can be released.
InnoDB will also wake up any other transactions that waited for the locks.
Finally, the SQL layer will release metadata locks on the table name - the table can now be ALTERed again

mvcc

简单讲实现

InnoDB给每行增加三个字段 DB_TRX_ID，DB_ROLL_PTR，DB_ROW_ID
REPEATABLE READ 中，所有读读的是事务中第一次读的时间的快照

InnoDB is a multi-version storage engine. It keeps information about old versions of changed rows to support transactional features such as concurrency and rollback. This information is stored in undo tablespaces in a data structure called a rollback segment. . InnoDB uses the information in the rollback segment to perform the undo operations needed in a transaction rollback. It also uses the information to build earlier versions of a row for a consistent read.

Internally, InnoDB adds three fields to each row stored in the database:

A 6-byte DB_TRX_ID field indicates the transaction identifier for the last transaction that inserted or updated the row.
A 7-byte DB_ROLL_PTR field called the roll pointer. The roll pointer points to an undo log record written to the rollback segment.
A 6-byte DB_ROW_ID field contains a row ID that increases monotonically as new rows are inserted.If InnoDB generates a clustered index automatically, the index contains row ID values.

Undo logs in the rollback segment are divided into insert and update undo logs. Insert undo logs are needed only in transaction rollback and can be discarded as soon as the transaction commits. Update undo logs are used also in consistent reads, but they can be discarded only after there is no transaction present for which InnoDB has assigned a snapshot that in a consistent read could require the information in the update undo log to build an earlier version of a database row.

In the InnoDB multi-versioning scheme, a row is not physically removed from the database immediately when you delete it with an SQL statement. InnoDB only physically removes the corresponding row and its index records when it discards the update undo log record written for the deletion. This removal operation is called a purge, and it is quite fast, usually taking the same order of time as the SQL statement that did the deletion.

Multi-Versioning and Secondary Indexes

InnoDB multiversion concurrency control (MVCC) treats secondary indexes differently than clustered indexes. Records in a clustered index are updated in-place, and their hidden system columns point undo log entries from which earlier versions of records can be reconstructed. Unlike clustered index records, secondary index records do not contain hidden system columns nor are they updated in-place.

When a secondary index column is updated, old secondary index records are delete-marked, new records are inserted, and delete-marked records are eventually purged. When a secondary index record is delete-marked or the secondary index page is updated by a newer transaction, InnoDB looks up the database record in the clustered index. In the clustered index, the record's DB_TRX_ID is checked, and the correct version of the record is retrieved from the undo log if the record was modified after the reading transaction was initiated.

If a secondary index record is marked for deletion or the secondary index page is updated by a newer transaction, the [covering index] technique is not used.

consistent read

A [consistent read] means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time.

If the transaction [isolation level] is [REPEATABLE READ] (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction.

With [READ COMMITTED] isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.

Suppose that you are running in the default [REPEATABLE READ] isolation level. When you issue a consistent read (that is, an ordinary SELECT statement), InnoDB gives your transaction a timepoint according to which your query sees the database. If another transaction deletes a row and commits after your timepoint was assigned, you do not see the row as having been deleted. Inserts and updates are treated similarly.

If you want to see the “freshest” state of the database, use either the [READ COMMITTED]isolation level or a [locking read]:

SELECT * FROM t FOR SHARE;

With FOR SHARE, a locking read occurs instead: A SELECT blocks until the transaction containing the freshest rows ends .

note

REPEATABLE READ 级别select 看不到的数据，dml 能看到 The snapshot of the database state applies to [SELECT] statements within a transaction, not necessarily to [DML] statements. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction. For example, you might encounter a situation like the following:

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- Returns 0: no rows match.
DELETE FROM t1 WHERE c1 = 'xyz';
-- Deletes several rows recently committed by other transaction.

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- Returns 0: no rows match.
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- Returns 10: this txn can now see the rows it just updated.

explain

主要看 type, key

type

system,当表仅有一行记录时,
const,单表操作的时候，查询使用了主键或者唯一索引,
eq_ref,多表关联查询的时候，主键和唯一索引作为关联条件
ref, 查找条件列使用了索引而且不为主键和唯一索引
ref_or_null,类似 ref，会额外搜索包含NULL值的行。
index_merge,查询使用了两个以上的索引(The Index Merge intersection algorithm performs simultaneous scans on all used indexes and produces the intersection of row sequences that it receives from the merged index scans.
range, 有范围的索引扫描
index The index join type is the same as [ALL], except that the index tree is scanned. This occurs two ways:
- If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than [ALL] because the size of the index usually is smaller than the table data.
- A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.
MySQL can use this join type when the query uses only columns that are part of a single index.
all

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked [const], and usually very bad in all other cases. Normally, you can avoid [ALL] by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.

ref

The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.

extra

zhuanlan.zhihu.com/p/396114960

Using index condition

Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is necessary.

索引

索引的缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件

建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快

组合索引实现

An index may consist of up to 16 columns.

A multiple-column index can be considered a sorted array, the rows of which contain values that are created by concatenating the values of the indexed columns.

SELECT * FROM tbl_name
  WHERE col1=val1 AND col2=val2;

If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly.
If separate single-column indexes exist on col1 and col2, the optimizer attempts to use the Index Merge optimization (retrieves rows with multiple [range] scans and merges their results into one), or attempts to find the most restrictive index

子查询

子查询是将一个查询的结果用作另一个查询的输入。可以将子查询的结果用作使用 IN( ) 函数、EXISTS 运算符或 FROM 子句的语句。

执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表

如何优化[慢SQL]

是否用到了索引
是否join多张表，对于需要经常联合查询的表，可以冗余字段或者建立中间表
表数据量是不是太大，分库分表
避免 SELECT *、避免大事务操作，使用合理的分页方式提高分页的效率
将一些常用的数据结构放在缓存中（部门名字，组织架构之类的），就不需要查数据库了

什么情况不建议使用索引

在where条件中（包括group by以及order by）里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。
数据量小的表最好不要使用索引，少于1000个
字段中如果有大量重复数据（性别），也不用创建索引
避免对经常更新的表创建过多的索引。
不建议使用无序的值作为索引。

如何优化SQL查询

表结构设计优化

合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容

范式反范式（适当冗余）
- 关联操作涉及的表不要太多，否则执行会很慢.（可以增加中间表,对于需要经常联合查询的表，可以建立中间表以提高查询效率）
- mysql限制了每个关联操作最多只能有61张表，在实际应用中，经验法则得出关联操作的表数量最好控制在3个以内
表设计不能有太多的列，数千的列会影响性能
优化数据类型
- 更小的通常更好
- 尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些
- 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销
- 尽量避免null,尤其是添加索引的列
分库分表等

索引优化

选择那些经常用于查询条件、连接操作或排序的列作为索引列
唯一索引、组合索引、覆盖索引（覆盖索引是一种特殊的索引，它包含了查询所需的所有列）的使用
- 注意索引顺序，最左匹配原则，尽量保持查询条件中的列顺序与索引顺序一致
避免创建过多或过少的索引

查询优化

避免 SELECT *，只查询需要的字段
尽量避免大事务操作，提高系统并发能力
使用合理的分页方式以提高分页的效率。（优化的方法可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。 select id,name from product where id> 866612 limit 20）
小表驱动大表，即小的数据集驱动大的数据集
in 条件很多值，会慢
尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理
查询语句尽可能简单，大语句拆小语句，减少锁时间
避免频繁创建和删除临时表，以减少系统表资源的消耗
- 尽量使用连接代替子查询，因为使用 join 时，MySQL 不会在内存中创建临时表

避免索引失效的情况

数据类型隐式转换，如果列类型是字符串，那在查询条件中需要将数据用引号引用起来，否则不走索引*
索引字段参与计算, 使用 != 、 <> 操作符，使用函数
联合索引，没有使用第一列索引（违背最左匹配原则）
like查询以%开头
优化器选错索引
or前后没有同时使用索引

or

2.必须所有的or条件都必须是独立索引（innodb还要求是在同一个索引列下面的or） 3.用UNION替换OR。(适用于索引列)通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果.对索引列使用OR将造成全表扫描. 注意,以上规则只针对多个索引列有效.如果有column没有被索引,查询效率可能会因为你没有选择OR而降低.

4.用in来替换or

in或or在字段有添加索引的情况下，查询很快，两者查询速度没有什么区别；
in或or在字段没有添加索引的情况下,所连接的字段越多(1or2or3or4or......)，or比in的查询效率低很多，or的效率为O(n)，而in的效率为O(logn) 为何推荐在没有索引的情况下使用in呢？
OR，就是从a1匹配，匹配失败，去匹配a2，直到匹配成功或者一个都匹配不上，时间复杂度O(n)
IN，先将a1, a2, ..., an 排序再二分查找？？，时间复杂度O(log n)

in

If no type conversion is needed for the values in the IN() list, they are all non-JSON constants of the same type, and expr can be compared to each of them as a value of the same type (possibly after type conversion), an optimization takes place. The values the list are sorted and the search for expr is done using a binary search, which makes the IN() operation very quick.

定期分析和优化索引

使用 EXPLAIN 命令查看查询的执行计划，了解索引的使用情况。
使用 ANALYZE 命令分析表的数据分布，了解索引的选择性。

union

mysql union 用于把来自多个select 语句的结果组合到一个结果集合中。语法为：

select column,......from table1

union [all]

select column,...... from table2

union 与 union all 的区别
- 当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ，mysql 会把所有的记录返回，且效率高于union 。

主从同步

dev.mysql.com/doc/refman/…

binary log dump thread (on source server)

Here, thread 2 is a Binlog Dump thread that services a connected replica.

replication I/O (receiver) thread
replication SQL (applier) thread

The State information indicates that thread 10 is the replication I/O (receiver) thread that is communicating with the source server, and thread 11 is the replication SQL (applier) thread that is processing the updates stored in the relay logs.

Binary Log File Position Based Replication

Each replica keeps a record of the binary log coordinates: the file name and position within the file that it has read and processed from the source.

另外还有个 GTID-based replication

同步策略

MySQL replication by default is asynchronous.

「同步策略」：Master会等待所有的Slave都回应后才会提交，这个主从的同步的性能会严重的影响。
「半同步策略」：Master至少会等待一个Slave回应后提交。
「异步策略」：Master不用等待Slave回应就可以提交。
「延迟策略」：Slave要落后于Master指定的时间。

对于不同的业务需求，有不同的策略方案，但是一般都会采用最终一致性，不会要求强一致性，毕竟强一致性会严重影响性能。

Switching Sources During Failover

You can tell a replica to change to a new source using the [CHANGE REPLICATION SOURCE TO] statement (prior to MySQL 8.0.23: [CHANGE MASTER TO].

锁

共享锁与排他锁

意向锁（表锁）

意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）

-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。
SELECT column FROM table ... LOCK IN SHARE MODE;

意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
SELECT column FROM table ... FOR UPDATE;

意义：另一个进程想要加表锁的时候不需要逐行去判断是否有行锁，而只需检查表上的锁和意向锁

记录锁（Record Locks）

记录锁是最简单的行锁，仅仅锁住一行。如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE
记录锁永远都是加在索引上的，即使一个表没有索引，InnoDB也会隐式的创建一个索引，并使用这个索引实施记录锁。

间隙锁（Gap Locks）

间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。
使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。
间隙锁只阻止其他事务插入到间隙中，他们不阻止其他事务在同一个间隙上获得间隙锁，所以 gap x lock 和 gap s lock 有相同的作用。

Next-Key Locks

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

插入意向锁（Insert Intention）

插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号，亦即多个事务在相同的索引间隙插入时如果不是插入间隙中相同的位置就不需要互相等待。
假设有索引值4、7，几个不同的事务准备插入5、6，每个锁都在获得插入行的独占锁之前用插入意向锁各自锁住了4、7之间的间隙，但是不阻塞对方因为插入行不冲突。

如何读懂死锁日志？

show engine innodb status

consistent read

A read operation that uses snapshot information to present query results based on a point in time, regardless of changes performed by other transactions running at the same time. If queried data has been changed by another transaction, the original data is reconstructed based on the contents of the undo log. This technique avoids some of the locking issues that can reduce concurrency by forcing transactions to wait for other transactions to finish.

With REPEATABLE READ isolation level, the snapshot is based on the time when the first read operation is performed. With READ COMMITTED isolation level, the snapshot is reset to the time of each consistent read operation.

隔离级别

`REPEATABLE READ`

default isolation level for InnoDB. [Consistent reads] within the same transaction read the [snapshot] established by the first read.

For [locking reads] ([SELECT]with FOR UPDATE or FOR SHARE), [UPDATE], and [DELETE] statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition.

For a unique index with a unique search condition, InnoDB locks only the index record found, not the [gap] before it.
For other search conditions, InnoDB locks the index range scanned, using [gap locks] or [next-key locks] to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks. （REPEATABLE READ 的 locking reads 没有幻读）

`READ COMMITTED`

Each consistent read, even within the same transaction, sets and reads its own fresh snapshot.

serializable

This level is like [REPEATABLE READ], but InnoDB implicitly converts all plain [SELECT] statements to [SELECT ... FOR SHARE] if [autocommit] is disabled. If [autocommit] is enabled, the [SELECT] is its own transaction.

遇到死锁问题，我们应该怎么分析呢

模拟死锁场景
show engine innodb status;查看死锁日志
找出死锁SQL
SQL加锁分析，这个可以去官网看哈
分析死锁日志（持有什么锁，等待什么锁）
熟悉锁模式兼容矩阵，InnoDB存储引擎中锁的兼容性矩阵

mysql partition

每个patition表都有单独的索引数，但公司没实际用过，有维护问题

双写方案：

基于 Binlog 实时同步

通过canal 同步es

index hint

SELECT * FROM table1 USE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3;

join

工作原理：

从左表读出一条，选出所有与on匹配的右表纪录(n条)进行连接，形成n条纪录(包括重复的行，如：结果1和结果3)，如果右边没有与on条件匹配的表，那连接的字段都是null.然后继续读下一条。

示例

Suppose that the tables t1(a, b) and t2(a, c) have the following contents:

t1    t2
----  ----
1 x   2 z
2 y   3 w

Then, for this join, column a contains the values of t1.a:

mysql> SELECT * FROM t1 NATURAL LEFT JOIN t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | x    | NULL |
|    2 | y    | z    |
+------+------+------+

left join 以左表为基准

using

The USING(join_column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables:
```
a LEFT JOIN b USING (c1, c2, c3)
```

Tips

on a.c1 = b.c1 等同于 using(c1)
2.通过指定 USE INDEX (key_list)，你可以告诉 MySQL 使用可能的索引中最合适的一个索引在表中查找记录行。 (如果 EXPLAIN 显示 MySQL 使用了错误的索引，这个特性将是很有用的)

mysqld — The MySQL Server

[mysqld], also known as MySQL Server, is a single multithreaded program that does most of the work in a MySQL installation. It does not spawn additional processes. MySQL Server manages access to the MySQL data directory that contains databases and tables.

[mysqld]

The SQL daemon (that is, the MySQL server). To use client programs, [mysqld] must be running, because clients gain access to databases by connecting to the server.
[mysqld_safe]

A server startup script. [mysqld_safe] attempts to start [mysqld].

It is the recommended way to start a [mysqld] server on Unix. [mysqld_safe] adds some safety features such as restarting the server when an error occurs and logging runtime information to an error log.

持久化连接

$dbh = new PDO('mysql:host=localhost;dbname=test',$ user, $pass, array(
PDO::ATTR_PERSISTENT => true
));

很多 web 应用程序通过与数据库建立持久连接获得好处。持久连接不会在脚本结束时关闭，而是会缓存，且当另一个脚本使用相同凭证请求连接时重用。持久连接缓存可以避免每次脚本需要与数据库通信时建立新连接的开销，从而让 web 应用程序更快。

复杂sql

查询每个分组里面排名前3的数据

blog.csdn.net/junzi528/ar…

select * from student_grade a where ( select count(1) from student_grade b where b.subId=a.subId and b.grade>a.grade )<=2 order by a.subid,a.grade desc

核心思路：要算出某人成绩在第几名，可以转换成：算出他一共比多少人成绩高。比如，第一名的人，就没其它人成绩比他更好。第三名的人，就有两个人成绩比他好。

where语句可以理解为，把表中的每一行记录，都去与给定的where条件作对比，满足的再查出来。也就是有个遍历的过程。

mysql 的xx表有字段a_id和b_id，写sql查询哪些 a_id like %b_id%

SELECT a_id FROM xx WHERE a_id LIKE CONCAT("%",b_id,"%");

mysql

范式

乐观锁 悲观锁

Locking Reads

int（1）

MySQL有哪些常见的存储引擎？

存储引擎的选择

外键

clustered index， Secondary Indexes

covering index

B+树

b tree vs b+ tree

InnoDB-棵B+树可以存放多少行数据

buffer pool

crash recovery

redoLog，undoLog，binLog 的作用

binlog

binlog 写入策略

redo log

mysql8 redo log architecture

Checkpointing

刷新到磁盘

undo log

trasaction and logs

mini-transaction

update语句的执行过程

mvcc

Multi-Versioning and Secondary Indexes

consistent read

note

explain

type

ref

extra

索引

组合索引实现

子查询

如何优化[慢SQL]

什么情况不建议使用索引

如何优化SQL查询

表结构设计优化

索引优化

查询优化

避免索引失效的情况

or

in

定期分析和优化索引

union

主从同步

Binary Log File Position Based Replication

同步策略

Switching Sources During Failover

锁

共享锁与排他锁

意向锁（表锁）

记录锁（Record Locks）

间隙锁（Gap Locks）

Next-Key Locks

插入意向锁（Insert Intention）

如何读懂死锁日志？

consistent read

隔离级别

REPEATABLE READ

READ COMMITTED

serializable

遇到死锁问题，我们应该怎么分析呢

mysql partition

双写方案：

基于 Binlog 实时同步

index hint

join

示例

using

Tips

mysqld — The MySQL Server

持久化连接

复杂sql

查询每个分组里面排名前3的数据

mysql 的xx表有字段a_id和b_id，写sql查询哪些 a_id like %b_id%

乐观锁悲观锁

`REPEATABLE READ`

`READ COMMITTED`