MySQL:索引 - 版本历史

Riguz：/* Secondary Indexes */

2021-05-03T10:07:27Z

Secondary Indexes

←上一版本		2021年5月3日 (一) 10:07的版本
第58行：		第58行：

	===Secondary Indexes===		===Secondary Indexes===
			Indexes other than the clustered index are known as <span class="article-label">secondary indexes</span>. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

	[[Category:Database]]		[[Category:Database]]

Riguz：/* Clustered Indexes */

2021-05-03T10:06:19Z

Clustered Indexes

←上一版本		2021年5月3日 (一) 10:06的版本
第54行：		第54行：
	#If you do not define a <span class="article-label">PRIMARY KEY</span> for a table, InnoDB uses the first <span class="article-label">UNIQUE</span> index with all key columns defined as <span class="article-label">NOT NULL</span> as the clustered index.		#If you do not define a <span class="article-label">PRIMARY KEY</span> for a table, InnoDB uses the first <span class="article-label">UNIQUE</span> index with all key columns defined as <span class="article-label">NOT NULL</span> as the clustered index.
	#If a table has no <span class="article-label">PRIMARY KEY</span> or suitable <span class="article-label">UNIQUE</span> index, InnoDB generates a hidden clustered index named <span class="article-label">GEN_CLUST_INDEX</span> on a synthetic column that contains <span class="article-label">row ID</span> values. The rows are ordered by the row ID that InnoDB assigns. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in order of insertion.		#If a table has no <span class="article-label">PRIMARY KEY</span> or suitable <span class="article-label">UNIQUE</span> index, InnoDB generates a hidden clustered index named <span class="article-label">GEN_CLUST_INDEX</span> on a synthetic column that contains <span class="article-label">row ID</span> values. The rows are ordered by the row ID that InnoDB assigns. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in order of insertion.

			Accessing a row through the clustered index is fast because the index search leads directly to the page that contains the row data. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record.

	===Secondary Indexes===		===Secondary Indexes===

	[[Category:Database]]		[[Category:Database]]

Riguz：/* InnoDB 索引实现 */

2021-05-03T10:05:30Z

InnoDB 索引实现

←上一版本		2021年5月3日 (一) 10:05的版本
第47行：		第47行：

	= InnoDB 索引实现=		= InnoDB 索引实现=
			== 索引类型 ==
			===Clustered Indexes===
			Each InnoDB table has a special index called the clustered index that stores row data. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, it is important to understand how InnoDB uses the clustered index to optimize the common lookup and DML operations.

			#When you define a <span class="article-label">PRIMARY KEY</span> on a table, InnoDB uses it as the clustered index.
			#If you do not define a <span class="article-label">PRIMARY KEY</span> for a table, InnoDB uses the first <span class="article-label">UNIQUE</span> index with all key columns defined as <span class="article-label">NOT NULL</span> as the clustered index.
			#If a table has no <span class="article-label">PRIMARY KEY</span> or suitable <span class="article-label">UNIQUE</span> index, InnoDB generates a hidden clustered index named <span class="article-label">GEN_CLUST_INDEX</span> on a synthetic column that contains <span class="article-label">row ID</span> values. The rows are ordered by the row ID that InnoDB assigns. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in order of insertion.
			===Secondary Indexes===

	[[Category:Database]]		[[Category:Database]]

2021年5月3日 (一) 10:00 Riguz

2021-05-03T10:00:42Z

←上一版本		2021年5月3日 (一) 10:00的版本
第1行：		第1行：
	=创建索引=		=SQL语法=
	<~~source~~ lang="sql">		==创建索引==
			<syntaxhighlight lang="sql">
	drop table if exists lck_tst;		drop table if exists lck_tst;
	create table lck_tst(		create table lck_tst(
第37行：		第38行：
	(9, '马克思', '9845', 'M', 'E', '她的热切的脸，如夜雨似的，搅扰着我的梦魂', '018989yhahhhjashd'),		(9, '马克思', '9845', 'M', 'E', '她的热切的脸，如夜雨似的，搅扰着我的梦魂', '018989yhahhhjashd'),
	(10, '买买提铁木耳', '9863', 'S', 'E', '忧思在我的心里平静下去，正如暮色降临在寂静的山林中。　　', '123sser');		(10, '买买提铁木耳', '9863', 'S', 'E', '忧思在我的心里平静下去，正如暮色降临在寂静的山林中。　　', '123sser');
	</~~source~~>		</syntaxhighlight>

	=全文索引=		==全文索引==
	<~~source~~ lang="sql">		<syntaxhighlight lang="sql">
	SELECT * FROM lck_tst WHERE MATCH(content) AGAINST('tears earth birds');		SELECT * FROM lck_tst WHERE MATCH(content) AGAINST('tears earth birds');

	</~~source~~>		</syntaxhighlight>

			= InnoDB 索引实现=
	[[Category:Database]]		[[Category:Database]]

imported>Soleverlee：/* 全文索引 */

2016-12-18T15:44:55Z

全文索引

←上一版本		2016年12月18日 (日) 15:44的版本
第40行：		第40行：

	=全文索引=		=全文索引=
			<source lang="sql">
			SELECT * FROM lck_tst WHERE MATCH(content) AGAINST('tears earth birds');

			</source>
	[[Category:Database]]		[[Category:Database]]

2016年12月18日 (日) 15:11 imported>Soleverlee

2016-12-18T15:11:04Z

←上一版本		2016年12月18日 (日) 15:11的版本
第19行：		第19行：
	insert into lck_tst(id, name, number, type1, type2, content, remark)		insert into lck_tst(id, name, number, type1, type2, content, remark)
	values		values
	(1, 'lee', '0001', 'M', 'S', 'Stray birds of summer come to my window to sing and fly away.And yellow leaves of autumn, which have no songs, flutter and fall there with a sign.', '140Kg, 25Y/O, like music'),		(1, 'lee', '0001', 'M', 'S', 'Stray birds of summer come to my window to sing and fly away.And yellow leaves
	(2, 'zhang', '0002', 'F', 'Q', 'O Troupe of little vagrants of the world, leave your footprints in my words.', '150Kg, 26Y/O, like sport'),		of autumn, which have no songs, flutter and fall there with a sign.', '140Kg, 25Y/O, like music'),
	(3, '刘德华', '0003', 'M', 'S', 'The world puts off its mask of vastness to its lover.', '180Kg, 25Y/O, like music'),		(2, 'zhang', '0002', 'F', 'Q', 'O Troupe of little vagrants of the world, leave your footprints in my
	(4, '侯宝林', '0004', 'M', 'Q', 'It is the tears of the earth that keep here smiles in bloom.', '14Kg, 26Y/O, like sport'),		words.', '150Kg, 26Y/O, like sport'),
	(5, '梅葆玖', '0005', 'M', 'W', 'The mighty desert is burning for the love of a bladeof grass who shakes her head and laughs and flies away.', 'wahaha a wahaha'),		(3, '刘德华', '0003', 'M', 'S', 'The world puts off its mask of vastness to its lover.', '180Kg, 25Y/O, like
	(6, 'Riguz', 'Ab01', 'F', 'R', 'If you shed tears when you miss the sun, you also miss the stars.', 'kasjdslkhah asjdhjksa xhjawhejqw xjhjask'),		music'),
	(7, 'Linus', 'BB-2', 'F', 'R', 'The sands in your way beg for your song and your movement, dancing water. Will you carry the burden of their lameness?', '180Kg'),		(4, '侯宝林', '0004', 'M', 'Q', 'It is the tears of the earth that keep here smiles in bloom.', '14Kg,
	(8, '列夫托尔斯泰', '30x9', 'E', 'S', '~~跳舞着的流水呀，在你途中的泥沙，要求你的歌声，你的流动呢。你肯挟瘸足的泥沙而俱下么？~~', 'shdiuowa weqwehoxz xckahwejk asjdhkjas'),		26Y/O, like sport'),
			(5, '梅葆玖', '0005', 'M', 'W', 'The mighty desert is burning for the love of a bladeof grass who shakes her
			head and laughs and flies away.', 'wahaha a wahaha'),
			(6, 'Riguz', 'Ab01', 'F', 'R', 'If you shed tears when you miss the sun, you also miss the stars.',
			'kasjdslkhah asjdhjksa xhjawhejqw xjhjask'),
			(7, 'Linus', 'BB-2', 'F', 'R', 'The sands in your way beg for your song and your movement, dancing water.
			Will you carry the burden of their lameness?', '180Kg'),
			(8, '列夫托尔斯泰', '30x9', 'E', 'S', '跳舞着的流水呀，在你途中的泥沙，要求你的歌声，你的流动呢。你肯挟瘸足
			的泥沙而俱下么？', 'shdiuowa weqwehoxz xckahwejk asjdhkjas'),
	(9, '马克思', '9845', 'M', 'E', '她的热切的脸，如夜雨似的，搅扰着我的梦魂', '018989yhahhhjashd'),		(9, '马克思', '9845', 'M', 'E', '她的热切的脸，如夜雨似的，搅扰着我的梦魂', '018989yhahhhjashd'),
	(10, '买买提铁木耳', '9863', 'S', 'E', '忧思在我的心里平静下去，正如暮色降临在寂静的山林中。　　', '123sser');		(10, '买买提铁木耳', '9863', 'S', 'E', '忧思在我的心里平静下去，正如暮色降临在寂静的山林中。　　', '123sser');

imported>Soleverlee：/* 创建索引 */

2016-12-18T15:09:22Z

创建索引

←上一版本		2016年12月18日 (日) 15:09的版本
第10行：		第10行：
	content varchar(200),		content varchar(200),
	remark varchar(200)		remark varchar(200)
	);		) ENGINE=MyISAM;
	alter table lck_tst add primary key (id);		alter table lck_tst add primary key (id);
	alter table lck_tst add unique idx_number(number);		alter table lck_tst add unique idx_number(number);
第16行：		第16行：
	alter table lck_tst add index idx_type(type1, type2);		alter table lck_tst add index idx_type(type1, type2);
	alter table lck_tst add fulltext(content);		alter table lck_tst add fulltext(content);

			insert into lck_tst(id, name, number, type1, type2, content, remark)
			values
			(1, 'lee', '0001', 'M', 'S', 'Stray birds of summer come to my window to sing and fly away.And yellow leaves of autumn, which have no songs, flutter and fall there with a sign.', '140Kg, 25Y/O, like music'),
			(2, 'zhang', '0002', 'F', 'Q', 'O Troupe of little vagrants of the world, leave your footprints in my words.', '150Kg, 26Y/O, like sport'),
			(3, '刘德华', '0003', 'M', 'S', 'The world puts off its mask of vastness to its lover.', '180Kg, 25Y/O, like music'),
			(4, '侯宝林', '0004', 'M', 'Q', 'It is the tears of the earth that keep here smiles in bloom.', '14Kg, 26Y/O, like sport'),
			(5, '梅葆玖', '0005', 'M', 'W', 'The mighty desert is burning for the love of a bladeof grass who shakes her head and laughs and flies away.', 'wahaha a wahaha'),
			(6, 'Riguz', 'Ab01', 'F', 'R', 'If you shed tears when you miss the sun, you also miss the stars.', 'kasjdslkhah asjdhjksa xhjawhejqw xjhjask'),
			(7, 'Linus', 'BB-2', 'F', 'R', 'The sands in your way beg for your song and your movement, dancing water. Will you carry the burden of their lameness?', '180Kg'),
			(8, '列夫托尔斯泰', '30x9', 'E', 'S', '跳舞着的流水呀，在你途中的泥沙，要求你的歌声，你的流动呢。你肯挟瘸足的泥沙而俱下么？', 'shdiuowa weqwehoxz xckahwejk asjdhkjas'),
			(9, '马克思', '9845', 'M', 'E', '她的热切的脸，如夜雨似的，搅扰着我的梦魂', '018989yhahhhjashd'),
			(10, '买买提铁木耳', '9863', 'S', 'E', '忧思在我的心里平静下去，正如暮色降临在寂静的山林中。　　', '123sser');
	</source>		</source>

	=全文索引=		=全文索引=

	[[Category:Database]]		[[Category:Database]]

imported>Soleverlee：以“=创建索引= drop table if exists lck_tst; create table lck_tst( id int not null, name varchar(200) not null, number varchar(20) not null, typ...”为内容创建页面

2016-12-18T14:53:10Z

以“=创建索引= <source lang="sql"> drop table if exists lck_tst; create table lck_tst( id int not null, name varchar(200) not null, number varchar(20) not null, typ...”为内容创建页面

新页面

=创建索引=
<source lang="sql">
drop table if exists lck_tst;
create table lck_tst(
id int not null,
name varchar(200) not null,
number varchar(20) not null,
type1 char(1),
type2 char(2),
content varchar(200),
remark varchar(200)
);
alter table lck_tst add primary key (id);
alter table lck_tst add unique idx_number(number);
alter table lck_tst add index idx_name(name);
alter table lck_tst add index idx_type(type1, type2);
alter table lck_tst add fulltext(content);
</source>
=全文索引=

[[Category:Database]]

MySQL:索引 - 版本历史

Riguz：​/* Secondary Indexes */

Riguz：​/* Clustered Indexes */

Riguz：​/* InnoDB 索引实现 */