《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等这样的
