MySQL 索引与优化

这篇具有很好参考价值的文章主要介绍了MySQL 索引与优化。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

索引基础

索引用途

索引有很多用途,并不仅仅是优化查询性能,这些用途包括:

  • 保持数据完整性(主键和唯一索引)
  • 优化数据检索性能(使用索引进行条件匹配和模式匹配)
  • 改进表的连接操作(使用索引连接表)
  • 优化结果排序操作(ORDER BY)
  • 优化聚合数据操作(GROUP BY)

创建索引

创建表时指定索引

主键索引:

PRIMARY KEY index-name

非主键索引:

UNIQUE KEY|INDEX index-name

下面的示例创建了一个主键索引和两个非主键索引,创建非主键索引时KEY和INDEX可以互换。

CREATE TABLE db_school.t_student (
    field_id varchar(10) NOT NULL,
    field_name varchar(80) NOT NULL,
    field_gender enum('M','F') DEFAULT NULL,
    field_dob datetime NOT NULL,
    field_grade int DEFAULT 0,
    field_class int DEFAULT 0,
    PRIMARY KEY (field_id),
    INDEX idx_name (field_name),
    INDEX idx_gc(field_grade,field_class)
) ENGINE=InnoDB;

复制

在已有表上添加索引

主键索引:

ALTER TABLE table ADD PRIMARY KEY index-name

非主键索引:

ALTER TABLE table ADD UNIQUE KEY|INDEX index-name

查看索引

可以用SHOW INDEXES命令查看索引的信息,该命令输出包括索引的类型和当前报告的MySQL索引基数等信息。

mysql> SHOW INDEXES FROM t_student;
--+----------+--------------+-------------+-------------+------+------------+--
  | Key_name | Seq_in_index | Column_name | Cardinality | Null | Index_type |
--+----------+--------------+-------------+-------------+------+------------+--
  | PRIMARY  |            1 | field_id    |        2689 |      | BTREE      |
  | idx_name |            1 | field_name  |        2689 |      | BTREE      |
  | idx_gc   |            1 | field_grade |         224 | YES  | BTREE      |
  | idx_gc   |            2 | field_class |         224 | YES  | BTREE      |
--+----------+--------------+-------------+-------------+------+------------+--

复制

选定索引

索引有一些时间和空间上的缺点。首先,索引加快了检索速度,但却降低了许多写入操作的速度,包括插入、删除,以及包含索引列的更新。其次,索引要占用磁盘空间,索引越多,它所占用的磁盘空间也就越大。因此,索引并不是越多越好,我们应该有选择地使用索引。

筛选索引

通过阅读上节中的索引用途,我们不难得出,适合作为索引的是以下这些数据列:

  • WHERE子句中的列
  • ORDER BY子句中的列
  • GROUP BY子句中的列
  • 用于表连接的列

上面只是数据列作为表索引的入选条件,满足这些条件的列可能有很多,我们还需要结合考虑以下因素进一步筛选。

  • 考虑列在查询中覆盖的广度。
  • 考虑数据列的区分度(维度),选择区分度高的列作为索引。
  • 优先考虑短小的列作为索引。越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快,整型有余字符串。
  • 考虑为字符串前缀编制索引。
  • 考虑为关联度高的字段编制复合索引,并将它们按区分度的高低从左到右排序。

单列索引vs.复合索引

多个单列索引和一个包含这些列的符合索引,在时间和空间上孰优孰劣?下面是两者对比的一些结论,仅供参考。

  1. 复合索引中最左边的列可以当作单列索引高效地使用(单列索引对它的优势并不明显)。
  2. 复合索引中最左边之外的列单独作为索引时,相比单列索引有明显的劣势。
  1. 作为两个用OR连接的条件,单列索引有一定优势,因为复合索引将导致全表扫描。
  2. 作为两个用AND连接的条件:
  • 如果两个列之间的关联度较低,复合索引有一定的优势。
  • 如果两个列之间的关联度较高,复合索引有明显的优势。
  1. 单列索引占用的空间更多,对写入操作的性能影响更大。

避免冗余索引

MySQL没有限制索引的数量,用户甚至可以在一个表上创建完全相同的多个索引。如上所述,添加索引会影响写操作的性能,我们应该尽量控制索引的数量,避免创建重复的索引。

本文第一节,我们为t_student创建了一个名字为idx_gc的索引:

INDEX idx_gc(field_grade,field_class)

如果我们再为t_student增加一个名字为inx_grade的索引:

INDEX idx_grade(field_grade)

新增的idx_grade实际上是一个重复的索引,因为idx_gc已经为field_grade创建了索引。

正确使用索引

定制了正确的索引还不够,我们还要正确使用它。

规则1:不能将索引放在表达式中,必须是独立的列,否则无法启用索引带来的高效。

例如,下面这个查询无法使用field_id列的索引。

SELECT field_name FROM t_student WHERE field_grade+1=3;

复制

凭肉眼很容易看出WHERE中的表达式其实等价于 field_grade=2,但是MySQL无法自动解析这个方程式。我们应该养成习惯,将索引列单独放在比较操作符的一侧。

同样,也不能将索引放在函数的参数中,本文不再赘述。

规则2:避免隐式类型转换。

这个规则不仅适用于索引字段,也适用于其它所有字段。有些隐式类型转换会导致索引失效,例如:

SELECT field_name FROM t_student WHERE field_id=123456;

复制

注意,field_id是varchar类型的,而上述语句中的查询条件是 field_id=123456。MySQL将数值类型隐式转换成字符串类型来匹配表。

我们来看一下这条SQL语句的执行计划(QEP):

mysql> EXPLAIN SELECT * FROM t_student WHERE field_id=123456\G  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_student
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 3 warnings (0.00 sec)

复制

看以看到,查询并没有使用索引,我们再看一下告警信息:

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1739
Message: Cannot use ref access on index 'PRIMARY' due to type or collation conversion on field 'field_id'
*************************** 2. row ***************************
  Level: Warning
   Code: 1739
Message: Cannot use range access on index 'PRIMARY' due to type or collation conversion on field 'field_id'
*************************** 3. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `db_school`.`t_student`.`field_id` AS `field_id`,`db_school`.`t_student`.`field_name` AS `field_name`,`db_school`.`t_student`.`field_gender` AS `field_gender`,`db_school`.`t_student`.`field_dob` AS `field_dob`,`db_school`.`t_student`.`field_grade` AS `field_grade`,`db_school`.`t_student`.`field_class` AS `field_class` from `db_school`.`t_student` where (`db_school`.`t_student`.`field_id` = 123456)
3 rows in set (0.00 sec)

复制

隐式类型转换使field_name上的索引失效,这将导致全表扫描。我们应该养成习惯,让索引的类型与你打算进行比较操作的(值)类型保持匹配。

规则3:验证索引的有效性。

要确定一条SQL语句能否按照我们的预期使用特定索引高效地执行,EXPLAIN命令是必不可少的工具。

EXPLAIN命令用于查看SQL语句的执行计划(QEP)。我们可以借助这条命令深入了解MySQL基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节(possible_keys,被评估的索引),以及当运行SQL语句时哪种策略会被优化器采用。

理想情况下,我们应该对系统中的每条SQL语句都执行EXPLAIN命令。所有SELECT语句前都可以直接加上EXPLAIN关键字。而对于UPDATE和DELETE语句,需要把查询改写成SELECT语句,以确保有效地使用索引。

总结

索引是对查询性能优化最有效的手段之一。正确地使用索引能够轻易地将查询性能提高几个数量级。编写查询语句时,应尽可能地选择合适的索引,以避免全表遍历。如果一个查询无法从现有的索引中获益,则应看看是否可以创建一个更合适的索引来提升性能。如果不行,也可以看看是否可以重写查询语句,将其转化成一个能够利用现有索引或者新创建索引的查询。文章来源地址https://www.toymoban.com/news/detail-483778.html

到了这里,关于MySQL 索引与优化的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL 索引与优化

    索引用途 索引有很多用途,并不仅仅是优化查询性能,这些用途包括: 保持数据完整性(主键和唯一索引) 优化数据检索性能(使用索引进行条件匹配和模式匹配) 改进表的连接操作(使用索引连接表) 优化结果排序操作(ORDER BY) 优化聚合数据操作(GROUP BY) 创建索引

    2024年02月09日
    浏览(45)
  • mysql 查询优化 、索引失效

    查询优化 物理查询优化 通过索引和表连接方式等技术来进行优化,这里重点需要掌握索引的使用 逻辑查询优化 通过SQL 等价变换 提升查询效率,直白一点就是说,换一种查询写法执行效率可能更高 索引失效 计算、函数、类型转换(自动或手动)导致索引失效 select sql_no_c

    2024年02月10日
    浏览(46)
  • MySQL性能优化(二)索引

    表的索引越全越好么? 为什么不要在性别子弹常见索引? 为什么不建议使用身份证做主键? 模糊匹配like xx%,like %xx% , like %xx都不用到索引么? 为什么不建议使用select * ? 数据库索引:数据库管理系统中一个排序的数据结构,加快查询效率。 索引按列分类:单列索引、联合

    2023年04月09日
    浏览(87)
  • MySQL 索引优化实践(单表)

          索引是为了高效查询排好序的数据结构,当表数据量到达一个量级没有对应索引帮助查询耗时会很长,MySQL资源开销也会非常大,当然索引也不能随意创建,要做到 尽量少的索引解决尽量多的问题 ,这里会对一些业务场景做索引优化演示,这篇文中只介绍单表索引优

    2024年02月07日
    浏览(43)
  • 【MySQL高级篇笔记-索引优化与查询优化(中) 】

    此笔记为尚硅谷MySQL高级篇部分内容 目录 一、索引失效案例 二、关联查询优化 1、采用左外连接 2、采用内连接 3、join语句原理 1.驱动表和被驱动表 2.Simple Nested-Loop Join(简单嵌套循环连接) 3.Index Nested-Loop Join(索引嵌套循环连接) 4.Block Nested-Loop Join(块嵌套循环连接) 5.Join小结

    2024年02月08日
    浏览(45)
  • MySQL---使用索引优化、大批量插入数据优化

    1. 使用索引优化 索引是数据库优化最常用也是最重要的手段之一 , 通过索引通常可以帮助用户解决大多数的 MySQL 的性能优化问题: 1.1 避免索引失效应用-全值匹配 该情况下,索引生效,执行效率高。 1.2 避免索引失效应用-最左前缀法则 1.3 避免索引失效应用-其他匹配原则

    2024年02月07日
    浏览(51)
  • MySQL 索引的分类和优化

    ​ 优质博文:IT-BLOG-CN 索引是什么 : MySQL 官方对索引的定义:索引(Index)是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。索引的目的在于提高查询效率。可以简单理解为,排好序的快速查找数据结构。在数据之外,数据系统还维护着满足特

    2024年03月22日
    浏览(40)
  • Mysql中索引优化和失效

    要了解索引优化和索引失效的场景就要先了解什么是索引 索引是一种有序的存储结构,按照单个或者多个列的值进行排序,以提升搜索效率。 索引的类型 UNIQUE唯一索引 不可以出现相同的值,可以有NULL值。 INDEX普通索引 允许出现相同的索引内容。 PRIMARY KEY主键索引 不允许出

    2024年02月20日
    浏览(44)
  • MySQL(二)索引原理以及优化

    MySQL(一)基本架构、SQL语句操作、试图 MySQL(二)索引原理以及优化 MySQL(三)SQL优化、Buffer pool、Change buffer MySQL(四)事务原理及分析 MySQL(五)缓存策略 MySQL(六)主从复制 数据库三范式 MySQL数据库是用来保存海量数据的,但是海量数据涉及到一个快速查找问题,怎么

    2024年02月16日
    浏览(47)
  • MySQL查询优化方案汇总(索引相关)

    类型隐式转换 大坑 大数据深度分页,用主键 避免使用MySQL函数 避免类型的隐式转换 避免使用函数或表达式,尽量只让数据库做纯粹的增删改查。 避免使用不等值做排除法 避免使用null值 减少大字段查询,避免使用*,不说磁盘io的损耗,连网络带宽都跟着损耗。 如果只sel

    2024年03月09日
    浏览(47)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包