MySQL-mysql建立索引有哪些需要注意的

MySQL-mysql建立索引有哪些需要注意的

浮生未歇 发布于 2017-01-03 字数 108 浏览 997 回复 2

mysql建立索引有哪些需要注意的。各种索引有什么区别和优势?比如主键,唯一索引,联合索引,全文索引等等。

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

晚风撩人 2017-11-04 2 楼

根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引,总索引长度至少为 256 字节。大多数存储引擎有更高的限制。
在 MySQL 5.1 中,对于 MyISAM 和 InnoDB 表,前缀可以达到 1000 字节长。请注意前
缀的限制应以字节为单位进行测量,而 CREATE TABLE 语句中的前缀长度解释为字符数。当
为使用多字节字符集的列指定前缀长度时一定要加以考虑。
还可以创建 FULLTEXT 索引。该索引可以用于全文搜索。只有 MyISAM 存储引擎支持
FULLTEXT 索引,并且只为 CHAR 、 VARCHAR 和 TEXT 列。索引总是对整个列进行,不支持局 部(前缀) 索引。也可以为空间列类型创建索引。只有 MyISAM 存储引擎支持空间类型。空
引使用 R- 树。默认情况 MEMORY(HEAP) 存储引擎使用 hash 索引,但也支持 B- 树索引。
设计索引的原则
1. 搜索的索引列,不一定是所要选择的列。换句话说,最适合索引的列是出现在 WHERE 子
句中的列,或连接子句中指定的列,而不是出现在 SELECT 关键字后的选择列表中的 列 。
2. 使用惟一索引。考虑某列中值的分布。对于惟一值的列,索引的效果最好,而具有多个
重复值的列,其索引效果最差。例如,存放年龄的列具有不同值,很容易区分各行。而用来记录性别的列,只含有 “ M ” 和 “ F ” ,则对此列进行索引没有多大用处(不管搜索哪个值,都会得出大约一半的行)
3. 使用短索引。如果对串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做 。
例如,如果有一个 CHAR(200) 列,如果在前 10 个或 20 个字符内,多数值是惟一的,
那么就不要对整个列进行索引。对前 10 个或 20 个字符进行索引能够节省大量索引空
间,也可能会使查询更快。较小的索引涉及的磁盘 I/O 较少,较短的值比较起来更快 。
更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此, MyS QL
也可以在内存中容纳更多的值。这增加 了找到行而不用读取索引中较多块的可能性。
(当然,应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的 ,
因为这个索引中不会有许多不 同的值。)
4. 利 用最左前缀。在创建 一个 n 列的索引时,实际是创建了 MySQL 可利用的 n 个索引。多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行。这样的列 集
称为最左前缀。(这与索引一个列的前缀不同,索引一个列的前缀是利用该的前 n 个 字
符作为索引值。)
5. 不要过度索引。不要以为 索引 “ 越多越好 ” ,什么东西都用索引是错的。每个额外的 索引都要占用额外的磁盘空间,并降低写操作的性能,这一点我们前面已经介绍 过。在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的 时间越长。如果有一个索引很少利用或从不使用,那么会不必要地减缓表 的修改速度。此外, MySQL 在生成一个执行计划时,要考虑各个索引,这也要费时间。创建多余的 索引给查询优化带来了更多的工作。索引太多,也可能会使 MySQL 选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引,应该考虑所要增加的索引是否是现有多列索引的最左 索引。如果是,则就不要费力去增加这个索引了,因为已经有了。
6. 考虑在列上进行 的比较类型。索引可用于 “ < ” 、 “ < = ” 、 “ = ” 、 “ > = ” 、 “ >” 和 BETWEEN 运算。在模式具有一个直接量前缀时,索引也用于 LIKE 运算。如果只将某个列用于其他类型的运算时(如 STRCMP( ) ),对其进行索引没有价值。

注:
对于 BTREE 和 HASH 索引,当使用 = 、 <=> 、 IN 、 IS NULL 或者 IS NOT NULL 操作符 时 ,关键元素与常量值的比较关系对应一个范围条件。 Hash 索引还有一些其它特征:它们只用于使用 = 或 <=> 操作符的等式比较 ( 但 很快 ) 。优化器不能使用 hash 索引来加速 ORDER BY 操 作 。( 该类索引不能用来按顺序搜索下一个条目)。 MySQL 不能确定在两个值之间大约有多少行( 这被范围优化器用来确定使用哪个索引 ) 。如果你将一个 MyISAM 表改为 hash- 索引的 MEMO RY表,会影响一些查询。只能使用整个关键字来搜索一行。 ( 用 B- 树索引,任何关键字的最 左面的前缀可用来找到行 ) 。
对于 BTREE 索引,当使用 > 、 < 、 >= 、 <= 、 BETWEEN 、 != 或者 <> ,或者 LIKE ' pattern '( 其中 ' pattern ' 不以通配符开始 ) 操作符时,关键元素与常量值的比较关系对应一个范围条件 。“ 常量值 ” 系指:查询字符串中的常量 、 同一联接中的 const 或 system 表中的列 、 无关联子查询的结果 、 完全从前面类型的子表达式组成的表达式

浮生未歇 2017-01-12 1 楼

我也来谈谈,主要需要注意以下几点:
1.建立索引的时机:若表中的某字段出现在select、过滤、排序条件中,为该字段建立索引是值得的。
2.对于like '%xxx'的模糊查询,普通的索引是无法满足的,需要建立全文索引
3.对于有多个条件的,比如: "...where a=xxx and b=yyy","...where a=xxx order by b","...where a=xxx group by b"。需要使用组合索引。但是组合索引只能在SQL语句中满足"最左前缀"的条件下使用。且组合索引有一些副作用,如索引尺寸可能比数据本身大,因为组合索引的组合条目多。所以在实际应用中,要量身定做,使用慢查询分析工具分析。
4.开启索引缓存,直接在内存中查找索引,不用再磁盘中。
5.建立索引是有代价的当update、delete语句执行时,会使得索引更新,将耗掉更多的时间。可以使用mysqlreport报告,了解select、update、delete、insert、replace各语句所占的百分比。