`
annan211
  • 浏览: 460366 次
  • 性别: Icon_minigender_1
  • 来自: 广州
社区版块
存档分类
最新评论

mysql 建立复合索引

 
阅读更多
   
   [size=medium]
      为什么使用数据索引能提高效率
   [/size]
      数据索引的存储是有序的
    在有序的情况下,通过索引查询一个数据是无需遍历索引记录的
    极端情况下,数据索引的查询效率为二分法查询效率,趋近于 log2(N)

      对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效。下面用几个例子对比查询条件的不同对性能影响.

    
create table test(
a int,
b int,
c int,
KEY a(a,b,c)
);

优: select * from test where a=10 and b>50
差: select * from test where a50

优: select * from test where order by a
差: select * from test where order by b
差: select * from test where order by c

优: select * from test where a=10 order by a
优: select * from test where a=10 order by b
差: select * from test where a=10 order by c

优: select * from test where a>10 order by a
差: select * from test where a>10 order by b
差: select * from test where a>10 order by c

优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b=10 order by b
优: select * from test where a=10 and b=10 order by c

优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b>10 order by b
差: select * from test where a=10 and b>10 order by c

索引原则

1.索引越少越好
原因:主要在修改数据时,第个索引都要进行更新,降低写速度。
2.最窄的字段放在键的左边
3.避免file sort排序,临时表和表扫描.


 
     复合索引的建立原则:

如果您很可能仅对一个列多次执行搜索,则该列应该是复合索引中的第一列。如果您很可能对一个两列索引中的两个列执行单独的搜索,则应该创建另一个仅包含第二列的索引。
如上图所示,如果查询中需要对年龄和性别做查询,则应当再新建一个包含年龄和性别的复合索引。
包含多个列的主键始终会自动以复合索引的形式创建索引,其列的顺序是它们在表定义中出现的顺序,而不是在主键定义中指定的顺序。在考虑将来通过主键执行的搜索,确定哪一列应该排在最前面。
请注意,创建复合索引应当包含少数几个列,并且这些列经常在select查询里使用。在复合索引里包含太多的列不仅不会给带来太多好处。而且由于使用相当多的内存来存储复合索引的列的值,其后果是内存溢出和性能降低。

        
复合索引对排序的优化:

复合索引只对和索引中排序相同或相反的order by 语句优化。
在创建复合索引时,每一列都定义了升序或者是降序。如定义一个复合索引:
 

  CREATE INDEX idx_example   
ON table1 (col1 ASC, col2 DESC, col3 ASC)  




  索引的缺点:
1.       占用磁盘空间。
2.       增加了插入和删除的操作时间。一个表拥有的索引越多,插入和删除的速度越慢。如 要求快速录入的系统不宜建过多索引。



其他情况 参见 博客 http://webnoties.blog.163.com/blog/static/183525141201310182313851/
分享到:
评论

相关推荐

    mysql存储与索引技术

    在索引优化方面,最左前缀原则是一个关键概念,这意味着复合索引只能按照索引创建时列的顺序部分使用,例如,INDEX(A, B, C)可以用于 WHERE A = x 或 WHERE A = x AND B = y 的查询,但不能单独用于 WHERE B = y 或 ...

    MySQL创建索引,查看以及删除

    5. 复合索引(Composite Index):由多个列组成的索引,按列的顺序进行排序。 创建索引的基本语法如下: ```sql CREATE INDEX index_name ON table_name (column_name); ``` 对于复合索引: ```sql CREATE ...

    Mysql数据库索引创建、索引删除、索引失效场景详解

    创建复合索引的方法与创建普通索引类似,只是涉及多个列。 5. 全文索引:用于解决大数据量的模糊查询问题。它通过分词技术建立索引,提高搜索效率。然而,全文索引的使用受到MySQL版本、存储引擎和字段类型的限制。...

    尚硅谷mysql高级:索引、优化

    5. 复合索引:当需要基于多列进行排序或过滤时,可以创建复合索引,索引顺序很重要,应根据查询条件的频率和选择性进行调整。 6. 空间索引:用于地理空间数据,如坐标位置。 二、MySQL优化 1. 查询优化:通过编写...

    MySql练习4:创建学生表和成绩表索引并查看索引.zip

    - **复合索引(Composite Index)**:由多个字段组成的索引,只有当指定的字段组合出现时才会被利用。 4. **查看索引**: 要查看已经创建的索引,可以使用`SHOW INDEX FROM`或`EXPLAIN`语句。例如,查看`students...

    MySQL索引 使用笔记

    - 通常应根据查询条件的频率和数据分布来选择复合索引的列。 6. 索引的选择性: - 选择性越高,索引效率越好。一个列的唯一值越多,选择性越高。 - 对于区分度低的列(如性别或地区),创建索引可能效果不佳。 ...

    04-VIP-Mysql索引优化实战一.pdf

    首先,创建了一个名为`employees`的员工记录表,其中包含`id`(主键)、`name`、`age`、`position`和`hire_time`等字段,并创建了一个名为`idx_name_age_position`的复合索引,由`name`、`age`和`position`三列组成...

    MySQL 索引最佳实践

    - **使用复合索引**:通过创建包含多个列的索引,可以同时满足多个查询条件,从而减少索引的数量。 - **定期分析和优化索引**:随着数据的变化,索引的效率也会变化,定期检查和调整索引结构是必要的。 - **利用覆盖...

    给MySQL建立索引1

    这时,我们需要创建一个复合索引,即包含多个字段的索引,如`CREATE INDEX mytable_categoryid_userid ON mytable (category_id, user_id);`。这样,MySQL可以在两个字段同时匹配时更有效地执行查询。 要验证数据库...

    mysql查询优化之索引优化

    - **复合索引**:如果一个查询涉及多个列,可以创建复合索引,其中列的顺序至关重要,应将最常用于筛选的列放在前面。 - **NULL值与索引**:对于包含NULL值的列,B-Tree索引可能会降低性能,因为NULL在索引中被视为...

    mysql 索引与执行计划

    3. **复合索引**:包含多个列的索引,可用于同时基于多个字段进行查询。 4. **聚簇索引(聚集索引)**:这是一种特殊的数据存储方式,通常与主键相关联。InnoDB引擎中,聚簇索引将数据和索引存储在同一结构中,因此...

    MySQL索引分析和优化.pdf

    ### MySQL索引分析和优化 ...- 对于复合查询,考虑创建多列索引以提高查询效率。 - 定期评估并优化索引,删除不再需要的索引。 通过合理设计和使用索引,可以显著提升MySQL数据库系统的性能和响应速度。

    mysql数据库索引类型

    4. 复合索引( Composite Index):复合索引是指在多个列上创建的索引,用于加速多列查询的速度。 二、MySQL索引的创建与删除 1. 创建索引:可以使用CREATE INDEX语句或在CREATE TABLE语句中指定INDEX关键字来创建...

    mysql之Linux安装,重点是索引的优化笔记

    - **复合索引**:基于多个字段组合创建的索引。 - **索引失效案例**: - 使用`OR`操作符连接条件时。 - 使用函数或表达式计算字段值时。 - 条件中包含`LIKE`操作且模式开头包含通配符时。 **5. 常见索引选择**...

    Mysql索引优化解决方案.doc

    索引可以分为多种类型,包括主键索引、唯一索引、单值索引、复合索引等。主键索引是表中的列设定为主键后,数据库会自动建立主键索引。唯一索引是表中的列创建了唯一约束时,数据库会自动建立唯一索引。单值索引是一...

    MySQL索引背后的数据结构及算法原理

    因此,在创建复合索引时应考虑查询中最常使用的字段放在最左边。 - **索引选择性**:选择性高的索引可以更快地过滤出结果集,因此在创建索引时应优先考虑选择性较高的字段。 - **前缀索引**:对于较长的字符串字段,...

    mysql索引笔记1

    3. 复合索引:索引包含多个列,适用于多列查询条件的情况。 索引的结构主要包括: 1. BTREE索引:最常见的索引类型,适合范围查询和排序。 2. Hash索引:适用于等值查询,不支持范围查询和排序。 3. Full-Text索引...

    MySQL 进阶-索引使用规则

    - 多个列可以组成一个复合索引,按照查询条件的顺序排列,例如:`CREATE INDEX idx_composite ON table_name (col1, col2);` 6. **覆盖索引** - 如果查询只需要索引中的列而无需访问原始数据行,覆盖索引可以提高...

    Mysql索引数据结构.pptx

    在实际应用中,选择合适的索引类型(如主键索引、唯一索引、普通索引、全文索引等)、索引字段(选择区分度高的字段)、以及索引策略(如复合索引、覆盖索引等)都是优化查询性能的重要手段。同时,过度使用索引也...

Global site tag (gtag.js) - Google Analytics