数据库的索引，mysql中的索引|||如何在mysql中对text字段加索引？|||mysql数据库中的字段在什么情况下加索引?|||高手请进：text字段不能做索引，有无其他方法？|||该建那个字段

来源：互联网发布：mac os x 10.11.4 编辑：程序博客网时间：2024/04/29 03:55

数据库的索引
1.
如果不建立索引，那么查询都需要全表扫描；如果建立了索引，则数据库会保存一个索引文件通常是特殊的结构比如b树，这样查询起来不需要全表扫描，一下子能够找到满足要求的记录。
2.
一般是对where之后的条件建立索引，数据库中的主键是已经建立了索引的。数据库中可以建立多个索引。
可以对不同类型的列建立索引。
对于text类型等，可以使用mysql的全文检索功能建立全文索引。它利用了自然语言的方法去在文本中检索关键词。
举个例子：如果使用=号的话可能需要使用like以及%等去匹配。而使用mysql的全文检索可以使用match函数即可检索出包含关键词的列。
详细情况参看mysql参考手册关于全文检索的部分。
更多的参考资料：

4.1使用索引
我们首先讨论索引，因为它是加快查询的最重要的工具。还有其他加快查询的技术，但是最有效的莫过于恰当地使用索引了。在mysql的邮件清单上，人们通常询问关于使查询更快的问题。在大量的案例中，都是因为表上没有索引，一般只要加上索引就可以立即解决问题。但这样也并非总是有效，因为优化并非总是那样简单。然而，如果不使用索引，在许多情形下，用其他手段改善性能只会是浪费时间。应该首先考虑使用索引取得最大的性能改善，然后再寻求其他可能有帮助的技术。
本节介绍索引是什么、它怎样改善查询性能、索引在什么情况下可能会降低性能，以及怎样为表选择索引。下一节，我们将讨论mysql的查询优化程序。除了知道怎样创建索引外，了解一些优化程序的知识也是有好处的，因为这样可以更好地利用所创建的索引。某些编写查询的方法实际上会妨碍索引的效果，应该避免这种情况出现。（虽然并非总会这样。有时也会希望忽略优化程序的作用。我们也将介绍这些情况。）
4.1.1索引的益处
让我们从一个无索引的表着手来考察索引是怎样起作用的。无索引的表就是一个无序的行集。例如，图4 - 1给出了我们在第1章“mysql与sql 介绍” 中首先看到的ad 表。这个表上没有索引，因此如果我们查找某个特定公司的行时，必须查看表中的每一行，看它是否与所需的值匹配。这是一个全表扫描，很慢，如果表中只有少数几个记录与搜索条件相匹配，则其效率是相当低的。
图4 - 2给出了相同的表，但在表的company_num 列上增加了一个索引。此索引包含表中每行的一项，但此索引是在company_num 上排序的。现在，不需要逐行搜索全表查找匹配的条款，而是可以利用索引进行查找。假如我们要查找公司13的所有行，那么可以扫描索引，结果得出3行。然后到达公司14的行，这是一个比我们正在查找的要大的号码。索引值是排序的，因此在读到包含14的记录时，我们知道不会再有匹配的记录，可以退出了。如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描（如二分查找法）。这样，可以快速定位到第一个匹配的值，以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，这些技术是什么并不重要，重要的是它们工作正常，索引技术是个好东西。
有人会问，为什么不只对数据文件进行排序，省掉索引文件？这样不也在搜索时产生相同的效果吗？问得好，如果只有单个索引时，
是这样的。不过有可能会用到第二个索引，但同时以两种不同的方法对同一个数据文件进行排序是不可能的。（如，想要一个顾客名的索
引，同时又要一个顾客id 号或电话号码的索引。）将索引文件作为一个与数据文件独立的实体就解决了这个问题，而且允许创建多个索
引。此外，索引中的行一般要比数据文件中的行短。在插入或删除值时，为保持排序顺序而移动较短的索引值与移动较长的数据行相比更
为容易。
这个例子与mysql索引表的方法相符。表的数据行保存在数据文件中，而索引值保存在索引文件中。一个表上可有不止一个索引；如果确实有不止一个索引，它们都保存在同一个索引文件中。索引文件中的每个索引由排过序的用来快速访问数据文件的键记录数组构成。
前面的讨论描述了单表查询中索引的好处，其中使用索引消除了全表扫描，极大地加快了搜索的速度。在执行涉及多个表的连接查询时，索引甚至会更有价值。在单个表的查询中，每列需要查看的值的数目就是表中行的数目。而在多个表的查询中，可能的组合数目极大，因为这个数目为各表中行数之积。
　假如有三个未索引的表t 1、t 2、t 3，分别只包含列c 1、c 2、c 3，每个表分别由含有数值1到1000 的1000 行组成。查找对应值相等的表行组合的查询如下所示：
select c1,c2,c3
from t1,t2,t3
where c1=c2 and c1=c3
此查询的结果应该为1000 行，每个组合包含3 个相等的值。如果我们在无索引的情况下处理此查询，则不可能知道哪些行包含那些值。因此，必须寻找出所有组合以便得出与where 子句相配的那些组合。可能的组合数目为10 0 0?0 0 0?0 0 0（十亿），比匹配数目多一百万倍。很多工作都浪费了，并且这个查询将会非常慢，即使在如像mysql这样快的数据库中执行也会很慢。而这还是每个表中只有1000 行的情形。如果每个表中有一百万行时，将会怎样？很显然，这样将会产生性能极为低下的结果。如果对每个表进行索引，就能极大地加速查询进程，因为利用索引的查询处理如下：
1) 如下从表t1中选择第一行，查看此行所包含的值。
2) 使用表t2 上的索引，直接跳到t2 中与来自t1的值匹配的行。类似，利用表t3 上的索引，直接跳到t3 中与来自t1的值匹配的行。
3) 进到表t1的下一行并重复前面的过程直到t1中所有的行已经查过。在此情形下，我们仍然对表t1执行了一个完全扫描，但能够在表t2 和t3 上进行索引查找直接取出这些表中的行。从道理上说，这时的查询比未用索引时要快一百万倍。如上所述，mysql利用索引加速了where 子句中与条件相配的行的搜索，或者说在执行连接时加快了与其他表中的行匹配的行的搜索。它也利用索引来改进其他操作的性能：
■ 在使用min( ) 和max( ) 函数时，能够快速找到索引列的最小或最大值。
■ mysql常常能够利用索引来完成order by 子句的排序操作。
■ 有时，mysql可避免对整个数据文件的读取。假如从一个索引数值列中选择值，而且不选择表中其他列。这时，通过对索引值的读取，就已经得到了读取数据文件所要得到的值。没有对相同的值进行两次读取的必要，因此，甚至无需涉及数据文件。
4.1.2 索引的弊端
一般情况下，如果mysql能够知道怎样用索引来更快地处理查询，它就会这样做。这表示，在大多数情况下，如果您不对表进行索引，则损害的是您自己的利益。可以看出，作者描绘了索引的诸多好处。但有不利之处吗？是的，有。实际上，这些缺点被优点所掩盖了，
但应该对它们有所了解。
首先，索引文件要占磁盘空间。如果有大量的索引，索引文件可能会比数据文件更快地达到最大的文件尺寸。其次，索引文件加快了检索，但增加了插入和删除，以及更新索引列中的值的时间（即，降低了大多数涉及写入的操作的时间），因为写操作不仅涉及数据行，而且还常常涉及索引。一个表拥有的索引越多，则写操作的平均性能下降就越大。在4 . 4节“有效地装载数据”中，我们将更为详细地介绍这些性能问题，并讨论怎样解决。
4.1.3 选择索引
创建索引的语法已经在3 . 4 . 3节“创建和删除索引”中进行了介绍。这里，我们假定您已经阅读过该节。但是知道语法并不能帮助确定表怎样进行索引。要决定表怎样进行索引需要考虑表的使用方式。本节介绍一些关于怎样确定和挑选索引列的准则：
■ 搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在where 子句中的列，或连接子句中指定的列，而不是出现在select 关键字后的选择列表中的列：
当然，所选择的列和用于where 子句的列也可能是相同的。关键是，列出现在选择列表中不是该列应该索引的标志。出现在连接子句中的列或出现在形如col1= col2 的表达式中的列是很适合索引的列。查询中的col_b 和col_c 就是这样的例子。如果mysql能利用连接列来优化一个查询，表示它通过消除全表扫描相当可观地减少了表行的组合。
■ 使用惟一索引。考虑某列中值的分布。对于惟一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。例如，存放年龄的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“ m”和“f”，则对此列进行索引没有多大用处（不管搜索哪个值，都会得出大约一半的行）。
■ 使用短索引。如果对串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，如果有一个char(200) 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前10 个或20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘i/o 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，mysql也可以在内存中容纳更多的值。这增加了找到行而不用读取索引中较多块的可能性。（当然，应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的，因为这个索引中不会有许多不同的值。）
■ 利用最左前缀。在创建一个n 列的索引时，实际是创建了mysql可利用的n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。（这与索引一个列的前缀不同，索引一个列的前缀是利用该的前n 个字符作为索引值。）
假如一个表在分别名为s t a t e、city 和zip 的三个列上有一个索引。索引中的行是按state/city/zip 的次序存放的，因此，索引中的行也会自动按state/city 的顺序和state 的顺序存放。这表示，即使在查询中只指定state 值或只指定state 和city 的值，mysql也可以利用索引。因此，此索引可用来搜索下列的列组合：
state,city,zip
state,city
sate
mysql不能使用不涉及左前缀的搜索。例如，如果按city 或zip 进行搜索，则不能使用该索引。如果要搜索某个州以及某个zip 代码（索引中的列1和列3），则此索引不能用于相应值的组合。但是，可利用索引来寻找与该州相符的行，以减少搜索范围。
■ 不要过度索引。不要以为索引“越多越好”，什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，这一点我们前面已经介绍过。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，mysql在生成一个执行计划时，要考虑各个索引，这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 mysql选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是，则就不要费力去增加这个索引了，因为已经有了。
■ 考虑在列上进行的比较类型。索引可用于“ <”、“ < = ”、“ = ”、“ > =”、“ > ”和between 运算。在模式具有一个直接量前缀时，索引也用于like 运算。如果只将某个列用于其他类型的运算时（如strcmp( )），对其进行索引没有价值。

如何在mysql中对text字段加索引？

1 楼wildlily980（小李）回复于 2005-06-06 11:13:45 得分25

全文索引对中文支持不好。top

2 楼mathematician（数学家）回复于 2005-06-06 13:37:31 得分35

全文搜索和普通的索引用途有区别，它是在text字段中执行一个自然语言搜索给定的字符串。如果你仅仅为了提高查询速度，不需要使用全文搜索top

3 楼kinglz（五月的雪）回复于 2005-06-07 17:06:46 得分 0

我的text字段都是存放的英文字母和数字，没有中文。但是这种情况下我如何提高速度呢？把这个字段分成两部分存储？top

4 楼seakingx（亚龙湾）回复于 2005-06-07 17:28:14 得分35

如果长度及格式固定的，做索引会快，
top

5 楼kinglz（五月的雪）回复于 2005-06-08 15:12:02 得分 0

长度和格式固定的，做全文索引会快么？top

6 楼wildlily980（小李）回复于 2005-06-08 15:22:37 得分5

mysql根本就没有办法解决这个问题。

mysql数据库中的字段在什么情况下加索引?

是不是每个字段都加上索引速度才快?

1 楼wildlily980（小李）回复于 2005-06-01 20:35:11 得分10

不是的，你经常用来判断的就需要加上索引。
如select * from table_name where id=****;
id这一列就需要加上索引。top

2 楼hleren（我是一只菜菜鸟）回复于 2005-06-02 00:05:13 得分 0

thanks!top

3 楼jfresh_man（十一月的萧邦－夜曲）回复于 2005-06-02 07:55:34 得分10

索引字段最好使用数字型的,速度上才会快一点
字符型的好象提高不大top

4 楼hleren（我是一只菜菜鸟）回复于 2005-06-02 10:47:27 得分 0

谢谢

高手请进：text字段不能做索引，有无其他方法？

表：mytable
字段1：id ----- int 类型主键
字段2：content--ntext 类型
记录总量：20万条
需求：查找content中包含某字符串strsearch的记录集合。
问题：由于sqlserver不支持ntext做索引或者主键，那么有没有办法使该查找操作加快？谢谢为盼。

不要设置ntext.改为大一点的nvarchar.top

2 楼crazyfor（冬眠的鼹鼠）回复于 2002-11-26 09:03:02 得分40

也可以把关键字另外存到一个字段，不过这样就麻烦点，需要多维护一下关键字，不过这是比较常用的做法。top

3 楼xl_xl（我想知道）回复于 2002-11-26 09:05:36 得分 0

由于content字段中有存在上万字的记录，顾难以设置较大的nvarchar，感谢两位，希望高手继续给以解答。top

4 楼crazyfor（冬眠的鼹鼠）回复于 2002-11-26 09:16:05 得分20

如果content字段内容很多，即使加了索引，速度也快不到哪儿的。top

5 楼xl_xl（我想知道）回复于 2002-11-26 10:21:47 得分 0

crazyfor(fan) ：我也试验了一下您提出的按照分页来做以加快速度，虽然有所提高，但速度还是没有明显改善，对于这种需求，有什么好的建议吗？我想控制在10秒以内，但目前一般都在20－30秒，有点不爽。top

6 楼seth99（seth）回复于 2002-11-30 02:35:16 得分 0

text and ntext当然用full-text indexing了top

7 楼seth99（seth）回复于 2002-11-30 02:41:37 得分 0

然后用下面的语句查询
============================================
select id,content
from mytable
where contains(content, @strsearch)
============================================

该建那个字段的索引呢？

有这些字段
table1里
id 自增
bid int
user 用户 varchar
content 内容 varchar
ntime 时间 datetime

table2里
id 自增
cid 和table1的id一样
content 内容 varchar
ltime 时间 datetime

读取的时候读取table1的bid＝一个数的所有内容
比如select * from table1 where bid=123，列出所有bid＝123所有字段的东西
如果有需要，点击一个连接执行select * from table2 where cid=456
cid是table1中某一条记录的id
整个表查询多余插入

想要建立一个索引，该怎么建呢？

你可以在
table1.bid，table2.cid
建立索引，索引可以根据需要进行建立，不止一个，主键才是唯一的。top

2 楼sailor001（醉雨梧桐）回复于 2005-04-24 21:58:03 得分 0

是不是用到什么字段检索就要建哪个字段的索引呢？