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

Question

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

Answer 1

根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少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 表中的列 、 无关联子查询的结果 、 完全从前面类型的子表达式组成的表达式

Answer 2

我也来谈谈，主要需要注意以下几点:
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各语句所占的百分比。