查看“︁MySQL:索引”︁的源代码

=SQL语法=
==创建索引==
<syntaxhighlight 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)
) ENGINE=MyISAM;
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);

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');
</syntaxhighlight>

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

</syntaxhighlight>

= 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]]