《mysql索引设计策略》由会员分享,可在线阅读,更多相关《mysql索引设计策略(10页珍藏版)》请在金锄头文库上搜索。
1、MySQL50中索引的设计和使用下面讨论下 MySQL5.0 中的索引的设计和使用。任何东西设计的好,那么使 用起来就顺手。不过很多时候给出设计什么规则这些都是相对的。做任何的事情, 最重要的是能否根据当时情况就合理的调整你的设计。如果你只会看着书本或者 权威来死套什么设计理念来进行实际的开发和设计的话,那么我请你还是少读书 为妙。索引是数据库中用来提高性能的常用工具。(注意如果要优化数据库的性能,这 是一个点)。所有MySQL列类型都可以被索引,对相关列使用索引是提高SELECT操作性能 的最佳途径。根据存储引擎可以定义每个表的最大索引数和最大索引长度,每种 存储引擎(MylSAM、Inno
2、DB、BDB、MEMORY等)对每个表至少支持16个索引,总 索引长度至少为256字节。大多数存储引擎有更高的限制。MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引o MySQL目前还不 支持函数索引,但是支持前缀索引,即对索引字段的前N个字符创建索引。前缀 索引的长度跟存储引擎相关,对于MyISAM存储引擎的表,索引的前缀长度可以 达到1000字节长,而对于InnODB存储引擎的表,索引的前缀长度最长是767 字节。请注意前缀的限制应以字节为单位进行测量,而CREATE TABLE语句中的 前缀长度解释为字符数。在为使用多字节字符集的列指定前缀长度时一定要加以 考虑。MyS
3、QL中还支持全文本索引(FULLTEXT),该索引可以用于全文搜索。但是在 Mysql5.0中只有MyISAM存储引擎支持全文本索引,并且仅仅局限于CHAR、 VARCHAR和TEXT列。索引总是对整个列进行的,不支持局部索引。也可以为空 间类型创建索引,但是只要MyISAM存储引擎支持空间类型索引,而且索引的字 段必须是非空。创建索引的语法如下:引用CREATE UNIQUE | FULLTEXT | SPATIAL IDEX index_nameUSING index_typeON tb1_name (index_col_name,)index_col_name:col_name (len
4、gth)ASC | DESC索引的创建可以在创建表的时候就创建,也可以随时增加新的索引。面给出一个例子:引用create index personname on person (person(10) 查询,可以发现索引 personname 被使用 引用explain select * from person where person=hi G 索引的删除语法为:引用DROP INDEX index_name ON tb1_name 如果想删除 person 表中的 personname 索引如下操作引用drop index personname on person上面简单的演示了索引的创建和
5、删除。接下来讨论下索引的设计原则: 索引的设计可以遵循一些已有的原则,创建索引的时候请尽量考虑符合这些 原则,便于提升索引的使用效率,更高效地使用索引。1. 搜索的索引列,不一定是所要选择的列。最合适索引的列是出现在WHERE子 句中的列,或连接子句中指定的列,而不是出现在SELECT关键字后的选择列表 中的列。2. 使用唯一索引。考虑某列中值的分布。索引的列的基数越大,索引的效果越 好。例如数据库中有好几年的资料,这些资料中有日期这个字段,而且查询中常 常要区分日期。那么针对日期设索引就很容易区分。3. 使用短索引。如果对字符串列进行索引,应该指定前缀长度,只要有可能就 应该这样做。例如:如
6、果有一个CHAR(200)的列,如果在前10个或20个字符内, 多数值是唯一的。那么就不要对整个列进行索引。对前10个或者20个字符进行 索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘 IO 较少,较短的值比较起来更快。更为重要的是,对于较短的键值,索引高速缓存 中的块能容纳更多的键值,因此,MySQL也可以在内存中容纳更多的值。这样就 增加了找到行而不用读取索引中较多块的可能性。4利用最左前缀。在创建一个n列的索引时,实际是创建了 MySQL可利用的n 个索引。多列索引起几个索引的作用,因为可利用索引中最左的列集来匹配行。 这样的列集称最左前缀。5. 不要过度索引。不要以
7、为索引“越多越好”,什么东西都用索引是错误的。 每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能。在修改表的内 容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越 长。如果有一个索引很少利用或者从不使用,那么会不必要地减缓表的修改速度。 此为 MySQL 在生成一个执行计划时,要考虑各个索引,这也要花费时间。创建多 余的索引给查询优化带来了更多的工作。索引太多,也可能会使MySQL选择不到 所要使用的最好索引。值保持所需的索引有利于查询优化。6.对于InnoDB存储引擎的表,记录默认会按照一个的顺序保存,如果有明确定 义的主键,则按照主键顺序保存。如果没有主键,但是
8、有唯一索引,那么就是按 照唯一索引的顺序保存。如果既没有主键有没有唯一索引,那么表中会自动生成 一个内部列,按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的, 所以 InnoDB 表尽量自己指定主键,当表中同时有几个列都是唯一的,都可以作 为主键的时候,要选择最常作为访问条件的列作为主键,提高查询的效率。另外 还需要注意, InnoDB 表的普通索引都会保存主键的键值,所以主键要尽可能选 择较短的数据类型,可以有效减少索引的磁盘占用,提高索引的缓存效果。BTREE索引与HASH索引MEMORY存储引擎的表可以选择使用BTREE或者HASH索引,两种不同类型的 索引各有其不同的适用范围
9、。 HASH 索引有些重要的特征需要在使用的时候特别 注意,如下所示:1. 只用于使用=或=操作符的等式比较。2. 优化器不能使用HASH索引来加速ORDER BY操作3. MySQL不能确定在两个值之间大约有多少行。如果将一个MylSAM表改为HASH 索引的MEMORY表,会影响一些查询的执行效率。4. 只能使用整个关键字来搜索一行。而对于 BTREE 索引,当使用 、 、= 、= 、BETWEEN 、!= 或者 ,或者LINKEpattern(其中pattern不以通配符开始)操作符时,都可以使用相关 列上的索引。索引的类型和使用场合索引分单列索引和组合索引单列索引:即一个索引只包含单个
10、列,一个表可以有多个单列索引,但这不 是组合索引。组合索引:即一个索包含多个列。介绍一下索引的类型1. 普通索引。这是最基本的索引,它没有任何限制。它有以下几种创建方式:(1) 创建索引:CREATE INDEX indexName ON tableName( tableColumns(leng th);如果是 CHAR,VARCHAR 类型,leng th 可以小 于字段实际长度;如果是BLOB和TEXT类型,必须指定length,下同。(2) 修改表结构:ALTER tableName ADD INDEX indexName ON (tableColumns(length)(3) 创建表的
11、时候直接指定: CREATE TABLE tableName ( ., INDEX indexName (tableColumns(length) ;2. 唯一索引。 它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允 许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:(1) 创建索引: CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length)(2) 修改表结构: ALTER tableName ADD UNIQUE indexName ON (tableColumns(length)(3) 创
12、建表的时候直接指定: CREATE TABLE tableName ( ., UNIQUE indexName (tableColumns(length);3. 主键索引 它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创 建主键索引: CREATE TABLE testIndex(i_testID INT NOT NULLAUTO_INCREMENT,vc_Name VARCHAR(16) NOT NULL,PRIMARY KEY(i_testID); 当 然也可以用 ALTER 命令。记住:一个表只能有一个主键。4. 全文索引MySQL 从3.23.23 版开始支持全文索引和全
13、文检索。这里不作讨论,呵 呵删除索引的语法: DROP INDEX index_name ON tableName单列索引和组合索引为了形象地对比两者,再建一个表:CREATE TABLE myIndex ( i_testID INT NOT NULL AUTO_INCREMENT, vc_Name VARCHAR(50) NOT NULL, vc_City VARCHAR(50) NOT NULL, i_Age INT NOT NULL, i_SchoolID INT NOT NULL, PRIMARY KEY (i_testID) );在这10000条记录里面7上8下地分布了 5条vc_N
14、ame=erquan的记录, 只不过 city,age,school 的组合各不相同。来看这条 T-SQL:SELECT i_testID FROM myIndex WHERE vc_Name=erquan AND vc_City= 郑州 AND i_Age=25;首先考虑建单列索引:在vc_Name列上建立了索引。执行T-SQL时,MYSQL很快将目标锁定在了vc_Name=erquan 的 5 条记录上,取出来放到一中间结果集。在这个结果集里, 先排除掉 vc_City 不等于郑州的记录,再排除 i_Age 不等于 25 的记录,最后 筛选出唯一的符合条件的记录。虽然在vc_Name上建立
15、了索引,查询时MYSQL不用扫描整张表,效率有所提 高,但离我们的要求还有一定的距离。同样的,在 vc_City 和 i_Age 分别建立的 单列索引的效率相似。为了进一步榨取MySQL的效率,就要考虑建立组合索引。就是将 vc_Name,vc_City,i_Age 建到一个索引里:ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name(10),vc_City,i_Age);-注意了,建表时, vc_Name 长度为 50,这里为 什么用 10呢?因为一般情况下名字的长度不会超过 10,这样会加速索引查询速 度,还会减少索引文件的大小,提高 INSERT 的更新速度。执行T-SQL时,MySQL无须扫描任何记录就到找到唯一的记录!肯定有人要问了,如果分别在 vc_Name,vc_City,i_Age 上建立单列索引,让 该表有 3 个单列索引,查询时和上述的组合索引效率一样吧?嘿嘿,大不一样, 远远低于我们的组合索引虽然此时有了三个索引,但MySQL只能用到其中的那 个它认为似乎是最有效率的单列索引。建立这样的组合索引,其实是相当于分别建立了 vc_Name,vc_City,i_Age vc_Name,vc_Cityvc_Name这样的三个组合索引!为什么没有vc_City,i_Age等这样的