Skip to content

索引

普通索引：最基本的索引，没有任何约束限制

唯一索引：与普通类似，但是具有唯一性

主键索引：特殊的唯一索引，不允许空值

  • 主键索引与唯一索引的区别
    1. 一个表只能有一个主键索引，可以有多个唯一索引
    2. 主键索引一定是唯一索引，唯一索引不是主键索引
    3. 主键可以与外键构成参照完整性约束，防止数据不一致

复合索引：将多个列组合在一起创建索引，可以覆盖多个列

  • 解释：A村有8组 给A村创建索引 但是需要查询的是A村1组 会找到A村索引后轮训8组 耗费时间 所以建议给8组每组建立索引

外键索引：只有Innodb类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作

全文索引：MySQL自带的全文索引只能用于MyISAM，并且只能对英文进行全文检索

关于索引的使用小技巧，避免对于整个索引的轮训，可以指定单个索引供以查询使用

  1. 强制索引： Force Index 【FORCE INDEX】

    • 示例：sel * from table force index (field1)
    • 注解：只是用在field1上的索引 不使用其他字段上的索引

  2. 忽略索引：Ignore Index 【IGNORE INDEX】

    • 示例：sel * from table IGNORE INDEX (field1, field2, ...) 注解：不使用field1 与 fields 上的索引

  3. 关闭/打开查询缓冲 SQL_ON_CACHE / SQL_CACHE

    • Tips:

      1. 查询缓冲是SQL文本和查询结果的映射。如果第二次查询的SQL和第一次
      2. 查询的SQL完全相同（注意必须是完全相同，即使多一个空格或者大小写不同都认为不同）
      3. 且开启了查询缓存，那么第二次查询就直接从查询缓存中取结果。

    • 示例：

      1. sel SQL_NO_CACHE field1, field2, from table;
      2. SQL_CACHE field1 field2, from table;

MySQL索引的创建原则

  • 原则
    1. 最适合索引的列是出现在WHERE子句中的列，或连接自句中的列而不是出现在SELECT关键字后的列
    2. 索引列的基数越大，索引的效果越好
    3. 对字符串进行索引，应该制定一个前缀长度，可以节省大量的索引空间
    4. 根据情况创建复合索引，复合索引可以提高查询效率
    5. 避免创建过多的索引，索引会额外占用磁盘空间，降低写操作的效率
    6. 主键尽可能的选择较短的数据类型，可以有效的减少索引的磁盘占用提高查询效率

MySQL索引的注意事项

  • 注意
    1. 复合索引遵循前缀原则

MySQL查询缓冲

  • 查询缓存可以看做是SQL文本和查询结果的映射。如果第二次查询的SQL和第一次查询的SQL完全相同（注意必须是完全相同，即使多一个空格或者大小写不同都认为不同）且开启了查询缓存，那么第二次查询就直接从查询缓存中取结果。

  • 缓存数据失效时机 ：在表的结构或数据发生改变时，查询缓存中的数据不再有效。

    • 有这些INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE会导致缓存数据失效。
    • 所以查询缓存适合有大量相同查询的应用，不适合有大量数据更新的应用。