InnoDB存储引擎原理概览

目标

主要是了解innodb引擎的原理，有助于更加合理的书写sql，另外，有助于扩展对于数据存储的比较，进而更容易理解诸如lucene、redis、mysql等的数据存储和处理能力。

索引与算法

平衡二叉树、B树、B+树、B*树

Mysql源码中的一些变量

在阅读Mysql源码的过程中，发现有很多简写的变量名称。了解相关变量的含义及作用对理解源码有很大的帮助，本篇文章会在源码阅读过程中陆续更新。

THD
全称（thread/connection descriptor）线程描述符。

QEP
全称（Query Execution Plan）查询执行计划。

QEP_TAB
全称（Query Execution Plan Table) 查询执行计划表

LOCK_REC_NOT_GAP, xx_REC_xx
（REC 一般是Record的简写）此处是一个无间隙锁

sel_node_create, sel_xx
（sel 代表select, 描述查询select struct)

trx
（transaction 事务的简写）

btr_pcur_get_rec
（btree persistent cursor get record)

ha_rnd_next
（handler random next 读取下一条数据）

rr_quick
（rr 代表read record)

mysql 原理研究sql全集

1.登录mysql
./mysql -uroot -p
enter

2.show databases;

3.create database debug_db;//最精简数据库。

4.use debug_db;

5.create table

6.show engine innodb status\G

7.show variables;

8.select @@session.read_buffer_size\G;

9.show variables like ‘long_query_time’\G

10.show variables like ‘log_queries_not_using_indexes’\G

11.show table status like ‘Persons’\G;

12.select City,length(City),char_length(City) from Persons\G;

13.select * from information_schema.tables where table_type=‘base table’ and table_schema=database()\G;

14.select * from information_schema.views where table_schema=database()\G;

分区：
15.create table test_partition( id int, name varchar(20) )engine=innodb partition by range(id)( partition p0 values less than(10), partition p1 values less than(20), partition p2 values less than MAXVALUE);

16.select * from information_schema.partitions where table_schema=database() and table_name=‘test_partition’\G;

17.alter table test_partition drop partition p1;

18. create table test_partition2(id int,age date)engine=innodb partition by range(year(age))(partition p0 values less than(1990),partition p1 values less than(2000),partition p2 values less than maxvalue);

19.analyze table tb_name;show table status;show index from tb_name

20.select * from order force index(orderId) where orderId>1000 and orderId<102000;

21.select * from order use index(orderId) where orderId>1000 and orderId<102000;

锁：
22.show engine innodb mutex\G;

23.select * from information_schema.innodb_locks;

24.select * from information_schema.innodb_lock_waits;

事务
25.begin/set autocommit = 0;/ start transaction

26. show global status like ‘com_commit’;

27.show global status like ‘handler_commit’;

28.show global status like ‘handler_rollback’;

30.show variables like ‘innodb_undo%’;

31.show global status like ‘innodb%read%’\G;

mysql备份

mysqldump备份-slave

主从复制

binlog命令
mysql主从复制

mysql优化

1.cpu
olap是cpu密集型操作，oltp是io密集型操作。

SQL中的where条件，在数据库中提取与应用浅析

where条件算法浅析

mysql内部执行流程

mysql内部执行流程

mysql优化器原理

1.指定索引
FORCE INDEX
IGNORE INDEX

2.指定驱动表
STRAIGHT_JOIN

3.指定优先级
HIGH_PRIORITY
LOW_PRIORITY
DELAYED
4.指定缓存
query_cache_type

优化器原理

explain type解析

/*
  The structs which holds the join connections and join states
*/
enum join_type { /*
                   Initial state. Access type has not yet been decided
                   for the table
                 */
                 JT_UNKNOWN,
                 /* Table has exactly one row */
                 JT_SYSTEM,
                 /*
                   Table has at most one matching row. Values read
                   from this row can be treated as constants. Example:
                   "WHERE table.pk = 3"
                  */
                 JT_CONST,
                 /*
                   '=' operator is used on unique index. At most one
                   row is read for each combination of rows from
                   preceding tables
                 */
                 JT_EQ_REF,
                 /*
                   '=' operator is used on non-unique index
                 */
                 JT_REF,
                 /*
                   Full table scan.
                 */
                 JT_ALL,
                 /*
                   Range scan.
                 */
                 JT_RANGE,
                 /*
                   Like table scan, but scans index leaves instead of
                   the table
                 */
                 JT_INDEX_SCAN,
                 /* Fulltext index is used */
                 JT_FT,
                 /*
                   Like ref, but with extra search for NULL values.
                   E.g. used for "WHERE col = ... OR col IS NULL"
                  */
                 JT_REF_OR_NULL,
                 /*
                   Like eq_ref for subqueries: Replaces subquery with
                   index lookup in unique index
                  */
                 JT_UNIQUE_SUBQUERY,
                 /*
                   Like unique_subquery but for non-unique index
                 */
                 JT_INDEX_SUBQUERY,
                 /*
                   Do multiple range scans over one table and combine
                   the results into one. The merge can be used to
                   produce unions and intersections
                 */
                 JT_INDEX_MERGE};