`
zhanglu0223
  • 浏览: 22709 次
  • 性别: Icon_minigender_1
  • 来自: 合肥
社区版块
存档分类
最新评论

数据库性能优化1——正确建立索引以及最左前缀原则

 
阅读更多

1. 索引建立的原则

用于索引的最好的备选数据列是那些出现在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列。

仅仅出现在SELECT关键字后面的输出数据列列表中的数据列不是很好的备选列

SELECT
col_a <- 不是备选列
FROM
tbl1 LEFT JOIN tbl2
ON tbl1.col_b = tbl2.col_c <- 备选列
WHERE
col_d = expr; <- 备选列

当然,显示的数据列与WHERE子句中使用的数据列也可能相同。
我们的观点是输出列表中的数据列本质上不是用于索引的很好的备选列。


2. 复合索引的建立以及最左前缀原则

索引字符串值的前缀(prefixe)。如果你需要索引一个字符串数据列,那么最好在任何适当的情况下都应该指定前缀长度。
例如,如果有CHAR(200)数据列,如果前面10个或20个字符都不同,就不要索引整个数据列。
索引前面10个或20个字符会节省大量的空间
你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT数据列的前缀。


假设你在表的state、city和zip数据列上建立了复合索引。索引中的数据行按照state/city/zip次序排列,
因此它们也会自动地按照state/city和state次序排列。这意味着,即使你在查询中只指定了state值,
或者指定state和city值,MySQL也可以使用这个索引。因此,这个索引可以被用于搜索如下所示的数据列组合:
state, city, zip
state, city
state

MySQL不能利用这个索引来搜索没有包含在最左前缀的内容。例如,如果你按照city或zip来搜索,
就不会使用到这个索引。如果你搜索给定的state和具体的ZIP代码(索引的1和3列),
该索引也是不能用于这种组合值的,尽管MySQL可以利用索引来查找匹配的state从而缩小搜索的范围。

如果你考虑给已经索引过的表添加索引,那么就要考虑你将增加的索引是否是已有的多列索引的最左前缀。
如果是这样的,不用增加索引,因为已经有了(例如,如果你在state、city和zip上建立了索引,那么没有必要再增加state的索引)。

3. 实例分析

通过实例理解单列索引、多列索引以及最左前缀原则
实例:现在我们想查出满足以下条件的用户id:
mysql>SELECT `uid` FROM people WHERE lname`='Liu' AND `fname`='Zhiqun' AND `age`=26
因为我们不想扫描整表,故考虑用索引。

单列索引:
ALTER TABLE people ADD INDEX lname (lname);
将lname列建索引,这样就把范围限制在lname='Liu'的结果集1上,之后扫描结果集1,产生满足fname='Zhiqun'的结果集2,再扫描结果集2,找到 age=26的结果集3,即最终结果。

由于建立了lname列的索引,与执行表的完全扫描相比,效率提高了很多,但我们要求扫描的记录数量仍旧远远超过了实际所需 要的。虽然我们可以删除lname列上的索引,再创建fname或者age 列的索引,但是,不论在哪个列上创建索引搜索效率仍旧相似。

多列索引:
ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);
为了提高搜索效率,我们需要考虑运用多列索引,由于索引文件以B-Tree格式保存,所以我们不用扫描任何记录,即可得到最终结果。

注:在mysql中执行查询时,只能使用一个索引,如果我们在lname,fname,age上分别建索引,执行查询时,只能使用一个索引,mysql会选择一个最严格(获得结果集记录数最少)的索引。


3.最左前缀:顾名思义,就是最左优先,上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引,(lname,fname)组合索引以及(lname,fname,age)组合索引。


注:在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。
分享到:
评论

相关推荐

    mysql进阶学习一之知识点总结(csdn)————程序.pdf

    - 索引可能因不合规的SQL写法而失效,如未遵循最左前缀原则、使用`!=`或`IS NOT NULL`判断、`LIKE`模糊查询时百分号在前、对索引列使用函数或不正确地处理字符串类型字段。 7. **关联查询优化**: - 在JOIN操作中...

    SQLServer2008查询性能优化 1/2

    内容涵盖了数据库应用系统中各种性能瓶颈的表现形式及其发生的根源和解决方法,从硬件瓶颈到查询、索引设计以及数据库管理等,贯穿了数据库系统知识的各个方面。最后以一个实际的工作负载将所有技巧联系起来,并且...

    SQLServer2008查询性能优化 2/2

    内容涵盖了数据库应用系统中各种性能瓶颈的表现形式及其发生的根源和解决方法,从硬件瓶颈到查询、索引设计以及数据库管理等,贯穿了数据库系统知识的各个方面。最后以一个实际的工作负载将所有技巧联系起来,并且...

    mysql全国手机归属数据库

    10. **查询优化**:为了快速响应查询,数据库设计应考虑索引的建立,尤其是在高频查询的字段上,如手机号码前缀,以提高查询效率。 综上所述,MySQL全国手机归属数据库提供了丰富的信息,对于商业决策、客户关系...

    一次MySQL两千万数据大表的优化过程,三种解决方案.docx

    3. **避免稀疏字段的索引**:如“性别”这样的字段,值分布非常集中,不适合建立索引。 4. **字符字段索引**:仅对字符字段建立前缀索引。 5. **主键选择**:尽量不使用字符字段作为主键。 6. **避免使用外键**:由...

    Python-手机号码归属地SQLite数据库

    6. 考虑性能优化,如建立索引以提高查询速度,或使用缓存机制减少重复查询。 在实际应用中,这样的数据库可能需要定期更新,以保持手机号码归属地信息的准确性。这可能涉及到数据的导入、更新和同步流程,以及错误...

    论文研究-一种新的快速报文分类算法——RC-FST*.pdf

    RC-FST 算法利用IP 地址高8 比特前缀建立Hash 压缩索引表, 将分类规则集分成多个子集, 并针对每个子集建立快速搜索树, 而这些规模相对小的本地搜索树更利于实现快速建立、查找和优化。为提高搜索树性能, 在规则分割...

    技术支持——综合笔试题检测版(福州实施项目).doc

    13. 创建唯一索引:为了提高查询速度,SC表(选课表)的唯一索引应建立在(S#,C#)组合上,因为每名学生对每门课的成绩是唯一的,选项A正确。 【数字推理部分】 数字推理题目的规律通常需要根据数列的递增或递减...

    页面传入多个条件——sql语句的拼接

    - **索引优化**:对于频繁查询的字段,应考虑建立索引以提高查询效率。 - **避免使用`LIKE`操作符**:如果可能的话,尽量避免在查询条件中使用`LIKE`操作符,尤其是在其前缀使用通配符时,因为这可能导致无法使用...

    SQL.Programming.Style

    本书旨在传授读者如何通过优化SQL语句结构、命名规范以及查询逻辑来改善数据库性能和程序质量。 2. **命名规范**:在SQL编程中,命名规范至关重要。Joe Celko在本书中强调了以下几点: - **名称长度**:控制列名、...

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

    PostgreSQL 号称“世界上最先进的开源数据库“,可以运行在多种平台下,是tb级数据库,而且性能也很好 中大型企业 oracle 甲骨文 获得最高认证级别的ISO标准安全认证,性能最高, 保持开放平台下的TPC-D和TPC-C的...

    SQL使用规范

    - **避免频繁更新**:频繁更新的字段不适合建立索引。 - **复合索引**:合理构建复合索引以满足多种查询需求。 - **其他优化技巧**: - **减少表扫描**:通过合理的索引设计减少全表扫描操作。 - **使用...

    青岛大学--复试--专业课问题

    这些基本数据类型的字节数可以帮助程序员更好地管理内存,尤其是在处理大量数据或优化程序性能时尤为重要。 #### 三、关系型数据库设计规范——第一、二、三范式 数据库的设计遵循一定的规范,以提高数据的一致性...

    阿里巴巴JAVA开发手册_1.3.0.pdf

    例如,建表规约中建议对表名采用复数形式,索引规约中提倡在业务主键上建立唯一索引,并对索引的命名、大小写敏感性进行了规定。对于ORM映射,手册建议使用懒加载策略,避免全量加载,以提升系统的性能和响应速度。 ...

    非常全的oracal讲稿

    ### Oracle数据库基础知识详解 #### 一、理解关系数据库系统(RDBMS) ##### 1.1 关系模型 关系模型是关系数据库的核心概念之一,它由三部分组成:数据结构、数据操作和完整性约束。 - **数据结构**:主要包括表...

    2021-2022计算机二级等级考试试题及答案No.19275.docx

    - **扩展**:搜索引擎的工作原理包括爬虫抓取网页、索引建立、检索算法等多个方面。 ### 8. Java语言基础 - **知识点**:Java语言中数据类型的内存占用情况。 - **解释**:在Java中,双精度型变量(double)占用8个...

Global site tag (gtag.js) - Google Analytics