索引.docx

索引.docx

《索引.docx》由会员分享，可在线阅读，更多相关《索引.docx（12页珍藏版）》请在冰豆网上搜索。

索引

索引

注意:

1、不要过度索引

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

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

索引类型

1、普通索引（index）：

仅仅是加快查询速度

2、唯一索引（uniqueindex）：

行上的值不能重复

3、主键索引（primarykey）：

主键不能索引

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

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

4、全文索引（fulltextindex）：

在mysql默认情况下对于中文作用不大。

查看一张表上的索引

showindexfrom表名（/G可以横着显示）

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

建立索引

altertable表名addindex/unique/fulltext[索引名]（列名）

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

altertable表名addprimarykey（列名）

创建索引示例：

为tel列创建普通索引

为email列添加唯一索引

为intro列添加全文索引

为id列添加主键

删除索引

altertabledropindex索引名

示例：

删除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

注意！

！

换服务器导入数据：

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

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

额外：

showcreatetable表名

可以查看创建表的语句

发现问题

使用全文索引时加上引擎