索引
注意:
1、
2、
3、
索引类型
1、普通索引(index):仅仅是加快查询速度
2、唯一索引(unique index):行上的值不能重复
3、主键索引(primary key):主键不能索引
主键索引必定是唯一的,唯一索引不一定是主键,
一张表上只能一个主键,可以有一个或者多个唯一索引。
4、
查看 一张表上的索引
show index from 表名(/G可以横着显示)
索引不知道名称默认以索引的列名作索引
建立索引
alter table 表名add index/unique/fulltext [索引名](列名)
[索引名]可以不写 ,不写默认与列名相同。
alter table 表名add primary key (列名)
创建索引示例:
为 tel列 创建普通索引
为email列 添加唯一索引
为intro列添加全文索引
为id列添加主键
删除索引
alter
示例:
删除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引擎,不支持,会报错