mysql 索引

mysql 索引(index)

(2012-09-09 12:45:13)

转载▼

标签： it

索引

注意:

1、  不要过度索引

2、  索引条件列（where后面最频繁的条件列）

3、  尽量索引散列值，过于集中的值不要索引。

索引类型

1、普通索引（index）：仅仅是加快查询速度

2、唯一索引(unique index)：行上的值不能重复

3、主键索引(primary key)：主键不能索引

主键索引必定是唯一的，唯一索引不一定是主键，

一张表上只能一个主键，可以有一个或者多个唯一索引。

4、  全文索引(fulltext index)：在mysql 默认情况下对于中文作用不大。

查看一张表上的索引

show index from表名(/G可以横着显示)

索引不知道名称默认以索引的列名作索引

建立索引

alter table表名add index/unique/fulltext[索引名](列名)

[索引名]可以不写 ，不写默认与列名相同。

alter table表名add primary key （列名）

创建索引示例：

为tel列 创建普通索引

 

为email列 添加唯一索引

 

为intro列添加全文索引

为id列添加主键

 

删除索引

alter  table drop index索引名

 

示例：

删除email（唯一）索引

删除主键索引

全文索引使用

 

 

查看匹配度

select列名, match (索引) against (‘ 索引词’) from表名;

新发现as  支持汉字

 

全文停止字：

全文索引，不针对非常频繁的词，做索引。

针对汉语意义不大，因为因为英文单词可以依赖空格等标点来判断单词界限，进而对单词进行索引，而中文mysql无法识别单词界限。

以下表列出了默认的全文停止字 ：

a's	able	about	above	according
accordingly	across	actually	after	afterwards
again	against	ain't	all	allow
allows	almost	alone	along	already
also	although	always	am	among
amongst	an	and	another	any
anybody	anyhow	anyone	anything	anyway
anyways	anywhere	apart	appear	appreciate
appropriate	are	aren't	around	as
aside	ask	asking	associated	at
available	away	awfully	be	became
because	become	becomes	becoming	been
before	beforehand	behind	being	believe
below	beside	besides	best	better
between	beyond	both	brief	but
by	c'mon	c's	came	can
can't	cannot	cant	cause	causes
certain	certainly	changes	clearly	co
com	come	comes	concerning	consequently
consider	considering	contain	containing	contains
corresponding	could	couldn't	course	currently
definitely	described	despite	did	didn't
different	do	does	doesn't	doing
don't	done	down	downwards	during
each	edu	eg	eight	either
else	elsewhere	enough	entirely	especially
et	etc	even	ever	every
everybody	everyone	everything	everywhere	ex
exactly	example	except	far	few
fifth	first	five	followed	following
follows	for	former	formerly	forth
four	from	further	furthermore	get
gets	getting	given	gives	go
goes	going	gone	got	gotten
greetings	had	hadn't	happens	hardly
has	hasn't	have	haven't	having
he	he's	hello	help	hence
her	here	here's	hereafter	hereby
herein	hereupon	hers	herself	hi
him	himself	his	hither	hopefully
how	howbeit	however	i'd	i'll
i'm	i've	ie	if	ignored
immediate	in	inasmuch	inc	indeed
indicate	indicated	indicates	inner	insofar
instead	into	inward	is	isn't
it	it'd	it'll	it's	its
itself	just	keep	keeps	kept
know	knows	known	last	lately
later	latter	latterly	least	less
lest	let	let's	like	liked
likely	little	look	looking	looks
ltd	mainly	many	may	maybe
me	mean	meanwhile	merely	might
more	moreover	most	mostly	much
must	my	myself	name	namely
nd	near	nearly	necessary	need
needs	neither	never	nevertheless	new
next	nine	no	nobody	non
none	noone	nor	normally	not
nothing	novel	now	nowhere	obviously
of	off	often	oh	ok
okay	old	on	once	one
ones	only	onto	or	other
others	otherwise	ought	our	ours
ourselves	out	outside	over	overall
own	particular	particularly	per	perhaps
placed	please	plus	possible	presumably
probably	provides	que	quite	qv
rather	rd	re	really	reasonably
regarding	regardless	regards	relatively	respectively
right	said	same	saw	say
saying	says	second	secondly	see
seeing	seem	seemed	seeming	seems
seen	self	selves	sensible	sent
serious	seriously	seven	several	shall
she	should	shouldn't	since	six
so	some	somebody	somehow	someone
something	sometime	sometimes	somewhat	somewhere
soon	sorry	specified	specify	specifying
still	sub	such	sup	sure
t's	take	taken	tell	tends
th	than	thank	thanks	thanx
that	that's	thats	the	their
theirs	them	themselves	then	thence
there	there's	thereafter	thereby	therefore
therein	theres	thereupon	these	they
they'd	they'll	they're	they've	think
third	this	thorough	thoroughly	those
though	three	through	throughout	thru
thus	to	together	too	took
toward	towards	tried	tries	truly
try	trying	twice	two	un
under	unfortunately	unless	unlikely	until
unto	up	upon	us	use
used	useful	uses	using	usually
value	various	very	via	viz
vs	want	wants	was	wasn't
way	we	we'd	we'll	we're
we've	welcome	well	went	were
weren't	what	what's	whatever	when
whence	whenever	where	where's	whereafter
whereas	whereby	wherein	whereupon	wherever
whether	which	while	whither	who
who's	whoever	whole	whom	whose
why	will	willing	wish	with
within	without	won't	wonder	would
would	wouldn't	yes	yet	you
you'd	you'll	you're	you've	your
yours	yourself	yourselves	zero

 

注意！！换服务器导入数据：

大数据量时，先把索引去掉，导入后，统一加上索引。

索引加快查询速度，降低增删改的速度。

额外：

show create table 表名

 可以查看  创建表的语句

发现问题

使用全文索引时加上引擎，默认 InnoDB引擎，不支持，会报错