1、区别于oracle中的表与索引是不同的segment，在mysql的innodb中，数据段和索引段是不做区分的。按照姜总说的，数据即索引、索引即数据。表上创建索引的顺序如下：
create table z ( a int not null, b int null, c int not null, d int not null, unique key (b) , --b允许空值 unique key (d) , --d列优先于c存在唯一索引 unique key (c)); insert into z select 1,2,3,4; insert into z select 5,6,7,8; insert into z select 9,10,11,12; select a,b,c,d_rowid from z; mysql> select a,b,c,d,_rowid from z; --在d列创建了索引 +---+------+----+----+--------+ | a | b | c | d | _rowid | +---+------+----+----+--------+ | 1 | 2 | 3 | 4 | 4 | | 5 | 6 | 7 | 8 | 8 | | 9 | 10 | 11 | 12 | 12 | +---+------+----+----+--------+ 3 rows in set (0.01 sec)
2、在mysql中，extent的分配是定大小的，即都为1M，起初创建的表大小为96K(6个page)，当使用完32个page，大小为512K之后，一次分配的extent就为64个page（即1M）。而在oracle中，extent的分配是随着数据插入数据量的大小而调整的。
3、在mysql中查看当前page大小
mysql> show variables like '%innodb_page_size%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_page_size | 16384 | +------------------+-------+ 1 row in set (0.00 sec) mysql>
4、在page中，行存储的方式
每个page中存放的行数是有上限的，16384/2-200=7992行。 Row_format记录的是行格式，在这里是Dynamic mysql> show table status like '%z%'; +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | z | InnoDB | 10 | Dynamic | 3 | 5461 | 16384 | 0 | 32768 | 0 | NULL | 2019-05-28 09:27:53 | 2019-05-28 09:29:01 | NULL | latin1_swedish_ci | NULL | | | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ 1 row in set (0.02 sec)
行记录方式如下：
5、查看page内容（内容比较深，相关资料说明较少）
***********************
将数据文件转储到文本中
***********************
create table mytest ( t1 varchar(10), t2 varchar(10), t3 char(10), t4 varchar(10)) engine=innodb charset=latin1 row_format=compact; insert into mytest values('a','bb','bb','ccc'); insert into mytest values('d','ee','ee','fff'); insert into mytest values('d',null,null,'fff'); hexdump -C -v mytest.ibd > mytest.txt 0000c050 00 02 00 f2 00 00 00 34 00 00 00 02 00 32 01 00 |.......4.....2..| 0000c060 02 00 1e 69 6e 66 69 6d 75 6d 00 04 00 0b 00 00 |...infimum......| 0000c070 73 75 70 72 65 6d 75 6d 03 02 01 00 00 00 10 00 |supremum........| 0000c080 2c 00 00 00 00 03 1b 00 00 00 00 25 c0 a9 00 00 |,..........%....| 0000c090 01 1d 01 10 61 62 62 62 62 20 20 20 20 20 20 20 |....abbbb | 0000c0a0 20 63 63 63 03 02 01 00 00 00 18 00 2b 00 00 00 | ccc........+...| 0000c0b0 00 03 1c 00 00 00 00 25 c1 aa 00 00 01 1e 01 10 |.......%........| 0000c0c0 64 65 65 65 65 20 20 20 20 20 20 20 20 66 66 66 |deeee fff| 0000c0d0 03 01 06 00 00 20 ff 98 00 00 00 00 03 1d 00 00 |..... ..........| 0000c0e0 00 00 25 c6 ad 00 00 01 21 01 10 64 66 66 66 00 |..%.....!..dfff.| 0000c0f0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 0000c100 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 0000c110 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| ***********************
采用存储过程模拟插入数据，以及rand()函数的使用
*********************** create table t1 (a int unsigned not null auto_increment, b char(10), primary key(a)) engine=innodb charset=UTF8; delimiter $$ create procedure load_t1 (count int unsigned) begin set @c = 0; while @c < count do insert into t1 select null,repeat(char(97+rand()*26),10); set @c=@c+1; end while; end; $$; insert into t1 select null,repeat(char(97+rand()*26),10); mysql> select * from t1; +----+------------+ | a | b | +----+------------+ | 1 | uuuuuuuuuu | | 2 | ssssssssss | | 3 | eeeeeeeeee | | 4 | ssssssssss | | 5 | zzzzzzzzzz | | 6 | vvvvvvvvvv | | 7 | eeeeeeeeee | | 8 | iiiiiiiiii | | 9 | cccccccccc | | 10 | llllllllll | +----+------------+ 10 rows in set (0.00 sec) mysql> select char(97+rand()*26); +--------------------+ | char(97+rand()*26) | +--------------------+ | y | +--------------------+ 1 row in set (0.00 sec) mysql> select char(97); +----------+ | char(97) | +----------+ | a | +----------+ 1 row in set (0.00 sec) mysql> select rand(); +--------------------+ | rand() | +--------------------+ | 0.4113105371699767 | +--------------------+ 1 row in set (0.00 sec) mysql> select rand(); +---------------------+ | rand() | +---------------------+ | 0.23754936571936064 | +---------------------+ 1 row in set (0.00 sec) mysql> select rand(); +--------------------+ | rand() | +--------------------+ | 0.9538152478205182 | +--------------------+ 1 row in set (0.00 sec) ***************************
查询创建表的page分布
*************************** [root@mysql1 py_innodb_page_info-master]# ./py_innodb_page_info.py -v /var/lib/mysql/zxjt/t1.ibd page offset 00000000, page type <File Space Header> page offset 00000001, page type <Insert Buffer Bitmap> page offset 00000002, page type <File Segment inode> page offset 00000003, page type <B-tree Node>, page level <0000> page offset 00000000, page type <Freshly Allocated Page> page offset 00000000, page type <Freshly Allocated Page> Total number of page: 6: Freshly Allocated Page: 2 Insert Buffer Bitmap: 1 File Space Header: 1 B-tree Node: 1 File Segment inode: 1 **********************
查询innodb_file_format当前的格式
********************** mysql> select @@version; +------------+ | @@version | +------------+ | 5.7.26-log | +------------+ 1 row in set (0.02 sec) mysql> show variables like '%innodb_version%'; +----------------+--------+ | Variable_name | Value | +----------------+--------+ | innodb_version | 5.7.26 | +----------------+--------+ 1 row in set (0.04 sec) mysql> show variables like '%innodb_file_format%'; +--------------------------+-----------+ | Variable_name | Value | +--------------------------+-----------+ | innodb_file_format | Barracuda | | innodb_file_format_check | ON | | innodb_file_format_max | Barracuda | +--------------------------+-----------+ 3 rows in set (0.00 sec) mysql>
6、mysql中的约束
********************
mysql支持的约束
******************** primary constraint unique constraint foreign constraint defalut not null 不支持check约束 ********************
如何创建约束
******************** 可以通过create table时指定或者alter table来修改
********************
如何查询约束
******************** mysql> select * from information_schema.table_constraints; +--------------------+-------------------+-----------------+--------------+---------------------------+-----------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | +--------------------+-------------------+-----------------+--------------+---------------------------+-----------------+ | def | mysql | PRIMARY | mysql | columns_priv | PRIMARY KEY | | def | mysql | PRIMARY | mysql | db | PRIMARY KEY | | def | mysql | PRIMARY | mysql | engine_cost | PRIMARY KEY | | def | mysql | PRIMARY | mysql | event | PRIMARY KEY | | def | mysql | PRIMARY | mysql | func | PRIMARY KEY | | def | mysql | PRIMARY | mysql | gtid_executed | PRIMARY KEY | | def | mysql | PRIMARY | mysql | help_category | PRIMARY KEY | | def | mysql | name | mysql | help_category | UNIQUE | | def | mysql | PRIMARY | mysql | help_keyword | PRIMARY KEY | | def | mysql | name | mysql | help_keyword | UNIQUE | | def | mysql | PRIMARY | mysql | help_relation | PRIMARY KEY | | def | mysql | PRIMARY | mysql | help_topic | PRIMARY KEY | | def | mysql | name | mysql | help_topic | UNIQUE | | def | mysql | PRIMARY | mysql | innodb_index_stats | PRIMARY KEY | | def | mysql | PRIMARY | mysql | innodb_table_stats | PRIMARY KEY | | def | mysql | PRIMARY | mysql | ndb_binlog_index | PRIMARY KEY | | def | mysql | PRIMARY | mysql | plugin | PRIMARY KEY | | def | mysql | PRIMARY | mysql | proc | PRIMARY KEY | | def | mysql | PRIMARY | mysql | procs_priv | PRIMARY KEY | | def | mysql | PRIMARY | mysql | proxies_priv | PRIMARY KEY | | def | mysql | PRIMARY | mysql | server_cost | PRIMARY KEY | | def | mysql | PRIMARY | mysql | servers | PRIMARY KEY | | def | mysql | PRIMARY | mysql | slave_master_info | PRIMARY KEY | | def | mysql | PRIMARY | mysql | slave_relay_log_info | PRIMARY KEY | | def | mysql | PRIMARY | mysql | slave_worker_info | PRIMARY KEY | | def | mysql | PRIMARY | mysql | tables_priv | PRIMARY KEY | | def | mysql | PRIMARY | mysql | time_zone | PRIMARY KEY | | def | mysql | PRIMARY | mysql | time_zone_leap_second | PRIMARY KEY | | def | mysql | PRIMARY | mysql | time_zone_name | PRIMARY KEY | | def | mysql | PRIMARY | mysql | time_zone_transition | PRIMARY KEY | | def | mysql | PRIMARY | mysql | time_zone_transition_type | PRIMARY KEY | | def | mysql | PRIMARY | mysql | user | PRIMARY KEY | | def | sys | PRIMARY | sys | sys_config | PRIMARY KEY | | def | zxjt | PRIMARY | zxjt | insect | PRIMARY KEY | | def | zxjt | PRIMARY | zxjt | t | PRIMARY KEY | | def | zxjt | PRIMARY | zxjt | t1 | PRIMARY KEY | | def | zxjt | d | zxjt | z | UNIQUE | | def | zxjt | c | zxjt | z | UNIQUE | | def | zxjt | b | zxjt | z | UNIQUE | +--------------------+-------------------+-----------------+--------------+---------------------------+-----------------+ 39 rows in set (0.15 sec) mysql>
7、mysql中的分区表
********************
全局分区索引和局部分区索引
********************
全局分区索引：在oracle中，全局分区索引为一个独立的segment。各分区为独立的segment。 在mysql中不支持此种类型的索引。 局部分区索引：在oracle中，各分区为独立的segment，分区索引也为独立的segment。 在mysql中分区和索引放于一个segment中。 ***********************
查看是否支持分区
*********************** mysql> show plugins; +----------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+---------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | +----------------------------+----------+--------------------+---------+---------+ 44 rows in set (0.02 sec) ****************
当前mysql支持的分区方式
**************** range、 list、 hash还有linear hash key columns ****************
mysql也支持子分区技术
**************** 子分区技术：在range、list的基础上再进行hash、或者key的分区。在分区中对于null值，range分区会将其置于最小的分区中。在list分区中必须显示的说明null存放的分区，否则会报错。 ******************
如何查看有关子分区的相关信息
****************** mysql> select * from information_schema.partitions; +---------------+--------------------+------------------------------------------------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+--------------------+--------------+-----------+---------------------+---------------------+------------+----------+-------------------+-----------+-----------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME | UPDATE_TIME | CHECK_TIME | CHECKSUM | PARTITION_COMMENT | NODEGROUP | TABLESPACE_NAME | +---------------+--------------------+------------------------------------------------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+--------------------+--------------+-----------+---------------------+---------------------+------------+----------+-------------------+-----------+-----------------+ | def | information_schema | CHARACTER_SETS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 384 | 0 | 16434816 | 0 | 0 | 2019-05-28 16:27:45 | NULL | NULL | NULL | | | NULL |
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
