做网站商标分类,海淀网站建设公司,什么播放器能看无线新闻台直播,婚纱网sql 优化1. mysql 基础架构1.1 mysql 的组成2. mysql 存储引擎2.1MyISAM2.2 InnoDB2.3 MyISAM 和 InnoDB 的对比3. mysql 索引3.1 Hash 索引3.2 B-Tree 索引3.3 BTree 索引3.4 R-Tree 索引3.5 Full-Text 索引4. sql 优化4.1 避免 select *4.2 避免在where子句中使用or来连接条件…
sql 优化1. mysql 基础架构1.1 mysql 的组成2. mysql 存储引擎2.1MyISAM2.2 InnoDB2.3 MyISAM 和 InnoDB 的对比3. mysql 索引3.1 Hash 索引3.2 B-Tree 索引3.3 BTree 索引3.4 R-Tree 索引3.5 Full-Text 索引4. sql 优化4.1 避免 select *4.2 避免在where子句中使用or来连接条件4.3 使用varchar代替char4.4 查询尽量避免返回大量数据4.5 使用explain分析你SQL执行计划4.6 优化like语句使用Full-Text索引4.7 避免数据隐式格式转换4.8 索引不宜太多4.9 索引不适合建在有大量重复数据的字段上4.10 where限定查询的数据不扩大条件范围4.11 避免在索引列上使用内置函数4.12 避免在where中对字段进行表达式操作4.13 避免在where子句中使用!或操作符4.14 去重distinct过滤字段要少4.15 where中使用默认值代替null4.16 批量插入性能提升4.17 复合索引最左特性4.18 排序字段创建索引4.19 删除冗余和重复的索引4.20 不要有超过5个以上的表连接4.21 inner join 、left join、right join优先使用inner join4.22 in子查询的优化4.23 尽量使用union all替代union4.24 小表驱动大表4.25 高效的分页4.26 提升group by的效率5. 总结1. mysql 基础架构
MySQL是一种关系型数据库管理系统RDBMS它采用了SQL结构化查询语言作为查询语言是一个开源的免费软件。MySQL的特点是高效灵活易于使用和维护能够与很多编程语言如PHPC Python等进行集成。MySQL是世界上最流行的关系型数据库管理系统在许多网络应用程序如论坛博客电子商务网站等中广泛使用。
MySQL对于现代应用程序至关重要下面是具体说明
大数据存储MySQL可以存储大量的数据这对于现代应用程序来说是非常重要的因为它们通常需要处理大量的数据如用户数据产品数据等。高效查询MySQL支持高效的数据查询这使得开发人员能够快速访问数据库中的数据以满足现代应用程序的各种需求。可扩展性MySQL是一个高度可扩展的数据库管理系统可以通过扩展硬件资源来满足现代应用程序的数据存储需求。可靠性MySQL提供了强大的数据完整性和可靠性保证这对于现代应用程序来说是非常重要的因为它们通常需要对数据进行长期存储以便随时访问。灵活性MySQL可以在各种操作系统平台上运行并且可以与多种编程语言集成如PHPC Java等。这种灵活性使得MySQL可以适应现代应用程序的各种需求。
1.1 mysql 的组成
连接器 身份认证和权限相关(登录 MySQL 的时候)。
查询缓存 执行查询语句的时候会先查询缓存MySQL 8.0 版本后移除因为这个功能不太实用。
分析器 没有命中缓存的话SQL 语句就会经过分析器分析器说白了就是要先看你的 SQL 语句要干嘛再检查你的 SQL 语句语法是否正确。
优化器 按照 MySQL 认为最优的方案去执行。
执行器 执行语句然后从存储引擎返回数据。 MySQL中SQL语句的执行过程大致如下
语法解析当客户端发送SQL语句到MySQL服务器时MySQL首先对SQL语句进行语法检查以确保语句符合SQL语法标准。优化如果SQL语句语法正确MySQL服务器将使用优化器对SQL语句进行优化以提高执行效率。数据字典查询MySQL服务器将查询数据字典以确定SQL语句中涉及的表的结构并确定如何执行SQL语句。数据锁定在执行SQL语句前MySQL服务器将对涉及的表进行锁定以确保数据的一致性和完整性。数据读取MySQL服务器将从数据库中读取所需的数据并通过数据存储引擎访问数据。结果生成MySQL服务器将使用SQL语句和读取的数据生成执行结果并将结果返回给客户端。数据提交如果SQL语句是一个事务语句MySQL服务器将在执行结束后提交数据并释放锁定的表。状态更新最后MySQL服务器将更新数据字典以反映SQL语句对数据库的影响。
2. mysql 存储引擎
MySQL的存储引擎是数据存储的核心组件负责管理数据存储和访问。MySQL支持多种不同的存储引擎每种存储引擎都有自己的特点和优点用户可以根据自己的需要选择合适的存储引擎。下面是MySQL中一些常用的存储引擎
MyISAM是MySQL最早推出的存储引擎支持快速查询和排序。MyISAM常用于读多写少的场景例如数据报告和统计。InnoDB是MySQL的主流存储引擎支持事务处理具有良好的完整性和可靠性。InnoDB常用于读写并存的场景例如互联网应用程序。Memory支持快速存储和读取数据是内存数据库的替代品。Memory存储引擎常用于对性能要求极高的场景例如缓存系统。Archive支持对大量历史数据进行压缩存储常用于数据备份和归档。CSV支持将数据存储为CSV格式文件方便数据与其他应用程序集成。Federated支持将数据分布在多个不同的MySQL数据库上是分布式数据库的替代品。
2.1MyISAM
MyISAM是MySQL数据库系统中的一种存储引擎它在存储方面的特点是快速、灵活和简单因此MyISAM常常被用于读多写少的场景。
原理
MyISAM存储引擎是一种非事务性存储引擎数据以表的形式存储在磁盘上每个表分成三个文件.MYD数据文件、.MYI索引文件和.frm结构文件。MyISAM采用基于磁盘的存储技术数据被组织成页以提高读写效率。
优点
高效MyISAM的读取速度非常快因为它在读取数据时不需要加锁也不需要等待事务完成。灵活MyISAM支持非常多的字段类型例如全文索引、空间数据类型和全文搜索。简单MyISAM的管理和维护比较简单不需要维护事务日志或其他复杂的结构。
缺点
不支持事务MyISAM是非事务存储引擎不支持事务处理。这意味着不能回滚数据或保证数据的一致性在数据安全性和可靠性方面存在潜在的风险。不支持外键MyISAM不支持外键因此不能通过外键约束实现数据完整性。不支持并发控制MyISAM不支持并发控制因此多个用户同时对同一表进行写操作时会发生冲突。不支持表级锁MyISAM不支持表级锁因此不能保证数据的一致性。容易损坏由于MyISAM的结构是非常简单的因此数据容易被损坏特别是在系统故障、停电等情况下。
2.2 InnoDB
InnoDB是一种具有ACID事务处理、外键支持、行级锁、高可用性、冗余数据少、高效率、大数据量处理能力的存储引擎。
原理
将数据存储在B树结构的索引中每一行数据对应一个叶子节点而索引本身也可以被索引。InnoDB使用了MVCC多版本并发控制技术能够在多用户并发读写的情况下保证数据的一致性。
优点
ACID事务处理InnoDB支持ACID原子性、一致性、隔离性、持久性事务处理可以提供数据的完整性和安全性。外键支持InnoDB支持外键约束可以帮助用户维护数据的完整性。行级锁InnoDB使用行级锁使得并发处理的效率更高。高可用性InnoDB支持自动恢复、备份、热备份可以帮助用户解决数据丢失等问题。冗余数据少InnoDB采用压缩存储可以节省硬盘空间。高效率InnoDB采用B树索引可以保证高效率的查询。大数据量处理能力InnoDB支持分布式处理可以处理大量数据。
缺点
磁盘空间占用大InnoDB支持事务处理、外键约束等功能因此占用的磁盘空间比较大。复杂的配置InnoDB的配置要比MyISAM更加复杂需要对数据库有比较深入的了解。性能下降InnoDB支持事务处理和行级锁当数据量过大或并发度过高时性能可能会下降。访问速度稍慢InnoDB比MyISAM要稍微慢一些因为它进行了更多的数据检查和处理。 ACIDAtomicity, Consistency, Isolation, Durability是一种用于评价数据库事务处理的标准。ACID是一组事务特性用于保证数据库中的数据在事务处理过程中的完整性。 Atomicity原子性事务是一个不可分割的操作要么全部成功要么全部失败。Consistency一致性事务执行前和执行后数据库中的数据必须处于一致状态即不会发生数据损坏。Isolation隔离性多个事务同时进行时互相不会影响。Durability持久性当事务被提交后它所做的改变将永久保存在数据库中。 MVCC (Multi-Version Concurrency Control)是一种数据库事务处理机制用于保证在并发访问数据库时多个事务之间的隔离性和数据一致性。 MVCC通过在数据库中存储多个版本的数据来实现多个事务的并发访问。在事务开始之前数据库会读取当前的数据版本并在事务过程中对数据进行修改。在事务结束时数据库会创建一个新版本并将修改后的数据存储在该版本中。 这样每个事务都可以独立地对数据进行修改而不会对其他事务造成影响。MVCC还可以提高数据库的并发性能因为事务之间的冲突可以在数据库的数据版本级别解决。 2.3 MyISAM 和 InnoDB 的对比
MyISAM 和 InnoDB是MySQL数据库的两种常用存储引擎它们的区别在于存储数据、索引、事务处理、锁定、回滚、备份和恢复等方面。
MyISAM
适用于读多写少的场景因为它不支持事务处理和行级锁定对写操作效率较低。对数据进行存储时会先将数据放入表中再更新索引因此在写操作期间不支持回滚。由于不支持事务处理如果写操作失败数据可能会损坏因此MyISAM需要定期进行修复。MyISAM的备份和恢复相对简单可以直接将表数据文件复制到其他服务器进行恢复。MyISAM的查询速度快但对写操作的支持较弱。
InnoDB
支持事务处理和行级锁定适用于读写并存的场景。InnoDB使用MVCC多版本并发控制机制在事务中对数据进行修改时可以保证数据的一致性。InnoDB支持回滚如果写操作失败可以回滚到事务前的状态。InnoDB的备份和恢复相对复杂需要使用数据库管理工具进行备份和回复
3. mysql 索引
MySQL 的索引是一种数据结构用于加速数据的查询和排序。它可以快速地查询到满足特定条件的数据而不需要扫描整个数据表。
MySQL 支持多种索引类型包括 普通索引通过索引字段快速查询数据可以解决部分查询操作的性能问题。 唯一索引类似于普通索引但保证索引字段不重复。 主键索引主键索引是一种特殊的唯一索引用于唯一标识数据行。 全文索引用于查询数据表中的文本字段支持全文检索。 组合索引按多个字段的顺序排列允许快速查询多个字段的数据。
索引的优点
提高查询速度索引可以通过快速定位查询数据从而大大提高查询速度。减少查询时间索引的使用减少了需要搜索的数据量从而减少了查询时间。增加查询灵活性索引可以在多个列上创建并可以使用不同的排序方式从而提高查询的灵活性。提高数据完整性索引可以通过强制唯一约束来提高数据完整性从而防止数据重复。提高系统性能索引的使用可以减少系统对数据的访问从而提高系统性能。
索引的缺点
增加存储空间索引需要占用额外的存储空间这可能导致数据库存储空间不足。增加写入时间在插入、更新或删除数据时需要同时更新索引这会使写入操作变慢。增加维护代价索引需要不断维护以确保其正确性这可能导致额外的维护成本。降低灵活性索引只能根据创建索引时指定的列进行查询因此如果查询的条件不涵盖所有索引列索引就不能够发挥其作用。增加数据一致性难度如果在索引列上进行了修改操作需要确保数据的一致性这可能导致额外的维护成本。
MySQL支持多种不同类型的索引主要有以下几种实现方案 B-tree 索引是MySQL使用最广泛的索引可以使用于所有数据类型是MySQL默认使用的索引类型。 Hash 索引主要用于全文搜索或内存数据库表。 R-tree 索引主要用于空间数据的搜索。 Full-Text 索引主要用于文本搜索。 聚簇索引InnoDB引擎特有的索引它是通过数据记录的物理存储顺序来加速检索的。
3.1 Hash 索引
Hash索引是一种散列索引它是通过哈希函数将索引键映射到一个固定大小的散列表中。它把每一个索引值通过哈希函数运算映射成一个散列值然后在散列表中快速查找相应的索引记录。
Hash索引的原理非常简单并且具有非常高的查询效率。在查询操作时通过对索引值进行哈希运算可以快速定位到索引的位置从而查询到符合要求的数据。Hash索引适用于对等值查询效率要求较高的数据表。
Hash索引的主要优点
查询速度快Hash索引是通过计算Hash值来快速定位记录的因此查询的速度非常快。简单易用Hash索引是一种简单的索引类型它不需要任何其他的信息即可快速定位记录因此它比其他索引类型更简单易用。适用于离散数据Hash索引适用于离散数据即键值只有一些有限的取值。这种情况下Hash索引的性能非常优秀。空间使用效率高由于Hash索引直接映射到存储位置因此它不需要额外的空间用于存储索引结构因此空间使用效率非常高。
Hash索引有以下缺点
只适用于等值查询Hash索引只适用于等值查询不适用于范围查询不能执行大于、小于、between等操作。不支持排序因为Hash索引不支持排序因此不适用于order by等排序操作。数据量较大时内存不足Hash索引把数据的hash值存储在内存中当数据量较大时可能导致内存不足。Hash冲突由于Hash索引依赖于hash值因此可能会出现hash冲突的情况影响查询的效率。不支持自动的索引修复Hash索引不支持自动的索引修复当数据出现损坏时需要手动进行修复。
3.2 B-Tree 索引
B-Tree 索引是最常用的索引类型它在大多数数据库管理系统中都有实现。B-Tree 索引是一种树形结构其主要思想是将数据分块存储每个块都有一个索引关键字用于在大量数据中快速查询目标数据。
在 B-Tree 索引中每个节点都是一个块包含一定数量的索引关键字和指向叶节点的指针而叶节点则存储实际的数据。每次查询时系统从根节点开始遍历比较目标数据与每个节点的索引关键字的大小关系决定该往左子树还是右子树继续查询直到找到目标数据或者到达叶节点为止。这种分块存储数据的方式使得查询的复杂度从线性降为对数从而大大提高了查询效率。
B-Tree 索引的优点
支持范围查询B-Tree 索引可以高效地实现范围查询比如查询某个范围内的数据这个功能在关系型数据库的索引中非常重要。高效的插入、删除操作B-Tree 索引支持平衡树的思想在插入和删除操作时可以保证索引树的平衡因此查询效率不会因为插入、删除操作而降低。排序功能B-Tree 索引支持数据的排序这在某些排序查询中非常有用。多列索引B-Tree 索引可以实现多列索引即一个索引同时包含多个字段这在多字段联合索引时非常有用。
B-Tree 索引的缺点
磁盘空间开销B-Tree 索引需要一定的磁盘空间来存储索引数据因此会增加数据库的存储开销。插入和删除操作的复杂度当你在表中插入或删除数据时B-Tree 索引也要随之更新。这些更新操作的复杂度为 O(log n)这与数据的规模有关因此可能会对性能产生一定的影响。冗余数据B-Tree 索引存储的数据有些冗余因为它需要存储完整的索引节点以支持查询操作。这会增加索引文件的大小。不适用于高维数据B-Tree 索引通常用于存储一维数据因此在处理高维数据时可能不够有效。
3.3 BTree 索引
BTree 索引是一种常用的索引结构它在 B Tree 的基础上进行了优化。
在 BTree 索引中除了叶节点以外的所有节点都是索引每个节点存储了一些索引键和指向下一层的指针。叶节点存储了实际的数据每一个叶节点都连续存储了一定数量的数据记录。 MyISAM 引擎中BTree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候首先按照 BTree 搜索算法搜索索引如果指定的 Key 存在则取出其 data 域的值然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引非聚集索引”。 InnoDB 引擎中其数据文件本身就是索引文件。相比 MyISAM索引文件和数据文件是分离的其表数据文件本身就是按 BTree 组织的一个索引结构树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引聚集索引”而其余的索引都作为 辅助索引 辅助索引的 data 域存储相应记录主键的值而不是地址这也是和 MyISAM 不同的地方。在根据主索引搜索时直接找到 key 所在的节点即可取出数据在根据辅助索引查找时则需要先取出主键的值再走一遍主索引。 因此在设计表的时候不建议使用过长的字段作为主键也不建议使用非单调的字段作为主键这样会造成主索引频繁分裂。 –内容整理自《Java 工程师修炼之道》 BTree索引的优点
效率高BTree索引使用树形结构来存储数据在查询数据时具有很高的效率可以减少数据库对于数据表的查询时间。空间利用率高BTree索引使用链表来存储数据因此空间利用率更高同时能够存储更多的数据。支持范围查询BTree索引支持范围查询因此可以更快地查询满足条件的数据。支持高并发读写BTree索引使用叶子节点存储数据因此在并发读写时不会对整个索引造成影响从而提高数据库系统的可用性和并发性。
BTree索引的缺点
空间浪费BTree索引的数据结构比较复杂需要额外的空间存储如果索引字段数据量较大那么空间浪费将更严重。更新开销因为BTree索引的数据存储在磁盘上所以每次更新索引的数据时需要进行磁盘的读写操作比较耗时。增加磁盘I/O在使用BTree索引进行数据查询时需要多次访问磁盘可能会增加磁盘I/O。频繁插入导致重建BTree索引是通过频繁的插入操作不断平衡的如果频繁插入会导致重建那么索引的构建时间会显著增加降低效率。
3.4 R-Tree 索引
R-Tree索引是一种用于空间数据索引的数据结构主要用于支持空间查询如范围查询和最近邻查询等。
R-Tree索引通过建立树形结构来组织空间数据在每个节点中维护一个矩形框表示该节点下的所有数据的范围。矩形框由最小矩形框MBR确定该矩形框包含了该节点下所有数据的最小矩形。
查询时R-Tree索引通过从根节点开始向下遍历树选择与查询矩形最交的节点。这些节点继续递归遍历直到叶节点。这样就可以快速确定查询结果。
R-Tree索引通常应用于空间数据查询如地理信息系统GIS和地图数据库中。它可以提供高效的查询性能特别是在处理大量空间数据时。
R-Tree索引的优点
支持空间数据的快速搜索由于 R-Tree 索引使用的是多叉树数据结构它能够快速定位与特定空间区域相关的数据。支持多维数据R-Tree 索引不仅支持二维数据还支持多维数据。支持高效插入、删除操作R-Tree 索引使用的是平衡树结构因此插入和删除数据的时间复杂度很低。减少数据库的读取次数通过使用 R-Tree 索引可以减少数据库的读取次数从而提高数据库的性能。
R-Tree索引的缺点
增加空间复杂度R-Tree索引对数据存储空间有比较高的要求不适合数据量比较大的情况。更新复杂度高R-Tree索引的更新操作复杂度较高当数据变化频繁时会影响整体的性能。可读性差R-Tree索引的结构和查询方式对人类来说不太容易理解和使用所以可读性较差。
3.5 Full-Text 索引
Full-Text索引是一种特殊的索引它专门用于存储和搜索大量的文本数据如文章、邮件、产品描述等。它的原理基于文本分析使用特定的算法对文本进行分词将文本转化为词语即关键字并将这些词语作为索引。
当用户搜索时MySQL会将查询语句中的关键字也分词并在Full-Text索引中进行匹配。如果有匹配的关键字则将包含该关键字的文本返回给用户。
需要注意的是Full-Text索引仅适用于MyISAM存储引擎并且需要对需要搜索的列单独建立Full-Text索引。
Full-Text索引的优点
更快的搜索速度使用 Full-Text 索引可以更快地执行文本搜索操作提高搜索效率。支持多语言Full-Text 索引可以识别多种语言支持多语言搜索。支持多字段搜索可以在多个字段上创建 Full-Text 索引并且可以把多个字段的内容组合在一起进行搜索。关键词排序Full-Text 索引可以按关键词的重要性和频率来排序搜索结果以使得用户更方便地找到所需的信息。灵活的搜索Full-Text 索引支持多种文本搜索操作包括模糊搜索、短词搜索、词组搜索等。
Full-Text索引的缺点
效率问题比起其他类型的索引Full-Text索引的效率要更劣一些因为它需要额外处理文本并且索引数据比较大。创建与维护成本高Full-Text索引需要额外的空间和时间来创建和维护所以需要增加服务器的负担。对于长文本的支持不好Full-Text索引只能处理固定长度的文本数据如果文本数据很长可能会导致索引不准确。对于特殊字符的支持不好Full-Text索引不能很好的处理一些特殊字符如标点符号和空格所以索引的准确性也可能会受到影响。不支持前缀搜索Full-Text索引不支持前缀搜索如果需要前缀搜索的话需要使用其他的索引方案。
4. sql 优化
SQL的关键字的执行顺序通常是如下的
FROM 和 JOIN连接表并生成笛卡尔积行集。WHERE对行集中的每一行执行过滤。GROUP BY将行集分组。HAVING对分组结果进行过滤。SELECT对行集执行投影生成查询结果。DISTINCT从查询结果中删除重复行。ORDER BY对查询结果排序。LIMIT限制返回的行数。
4.1 避免 select *
避免使用 SELECT * 是 SQL 优化的一个重要步骤因为它可能导致效率低下负载增加。
当使用 SELECT * 时数据库需要获取所有列的所有数据这需要花费更多的时间和资源。同时如果查询返回的数据不需要所有列这会浪费带宽和资源导致性能问题。
正例下面的查询只需要返回名称和地址列
SELECT name, address FROM customers;反例下面的查询需要返回所有列
SELECT * FROM customers;4.2 避免在where子句中使用or来连接条件
避免在 where 子句中使用 or 连接条件的原因是 or 连接的条件不利于数据库的索引优化导致查询速度变慢。因为索引只能对单一条件优化而 or 连接的两个条件需要重新扫描表中的所有数据因此会使查询的效率降低。
正例
SELECT * FROM users WHERE user_id 1 AND username admin;反例
SELECT * FROM users WHERE user_id 1 OR username admin;在正例中数据库可以利用 user_id 和 username 两个字段的索引快速查找符合条件的记录。而在反例中由于使用了 or导致数据库需要重新扫描表中的所有数据因此效率较低。
4.3 使用varchar代替char
使用 varchar 数据类型比使用 char 数据类型更加高效和灵活是因为 varchar 可以根据需要分配所需的内存空间而 char 则必须预先分配一个固定大小的空间。因此当存储的字符串长度比预分配的空间更短时使用 char 会浪费空间。
正例
CREATE TABLE customers (customer_id INT PRIMARY KEY,customer_name VARCHAR(255)
);反例
CREATE TABLE customers (customer_id INT PRIMARY KEY,customer_name CHAR(255)
);因此通常建议使用 varchar特别是在需要存储具有不同长度的字符串的情况下使用 char 可能会带来性能问题。
尽量使用数值替代字符串类型 主键idprimary key优先使用数值类型inttinyint 性别sex0-代表女1-代表男数据库没有布尔类型mysql推荐使用tinyint 支付方式payment1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡 服务状态state1-开启、2-暂停、3-停止 商品状态state1-上架、2-下架、3-删除
4.4 查询尽量避免返回大量数据
返回大量数据将增加网络带宽的使用使用的内存和硬盘空间也将增加同时会使数据库的处理速度降低。
正例如果只需要查询前10条记录可以使用limit语句
SELECT * FROM users LIMIT 10;反例如果查询所有记录可能会使数据库卡住
SELECT * FROM users;因此在使用 SQL 查询时尽量避免返回大量数据。更好的方法是查询所需的最小数据量例如使用 limit 和 offset 子句。
4.5 使用explain分析你SQL执行计划
使用EXPLAIN分析SQL执行计划可以帮助您了解数据库如何执行查询并识别任何潜在的性能问题。此信息可以帮助您确定哪些索引、链接和排序操作是最有效的并可以确定查询是否正确使用了索引。
正例在执行一个 SELECT 语句之前我们可以使用 EXPLAIN 关键字来分析该语句的执行计划查看如何对数据进行扫描、排序和链接
EXPLAIN SELECT * FROM orders WHERE order_id 100;反例在不使用 EXPLAIN 关键字的情况下执行一个 SELECT 语句而不了解其执行计划可能导致性能问题因为我们无法识别该语句是否正确使用了索引和链接。
SELECT * FROM orders WHERE order_id 100;4.6 优化like语句使用Full-Text索引
在sql中使用like语句模糊查询数据时会非常耗时因为这种查询方式需要遍历整张表以确定哪些记录符合查询条件。使用Full-Text索引可以加速模糊查询因为它建立了全文索引使用了特殊的数据结构例如倒排索引来加快查询速度。
正例
例如你有一张表包含了文章的标题你需要在表中查询含有“社会”这个关键字的所有文章。
SELECT * FROM articles WHERE MATCH (title) AGAINST (社会);反例
如果你使用了like语句那么查询速度将会非常慢
SELECT * FROM articles WHERE title LIKE %社会%;这是因为like语句需要遍历整张表而Full-Text索引会使用特殊的数据结构加速查询。因此如果可能的话你应该尽量避免使用like语句并使用Full-Text索引代替它。
4.7 避免数据隐式格式转换
数据隐式格式转换是指在SQL语句执行过程中将一种数据类型隐式转换成另一种数据类型这样的转换会影响SQL语句的性能。
当数据隐式转换时数据库系统需要进行一些额外的处理消耗了额外的时间和系统资源。同时隐式转换还可能导致精度丢失和数据不准确的情况。
正例在进行查询前确保字段的数据类型与查询语句中需要的数据类型相同以便避免隐式数据转换。
SELECT * FROM users WHERE age 25;反例查询语句中字段的数据类型与查询语句中需要的数据类型不同导致隐式格式转换。
SELECT * FROM users WHERE age 25;在这种情况下数字 25 会被隐式转换为字符串 ‘25’可能导致查询变慢并且容易产生错误结果。
4.8 索引不宜太多
索引不宜太多是因为索引会影响数据的插入、修改和删除的效率并且会占用额外的空间。
正例假设有一张名为orders的表你想快速查询按照订单日期排序的数据那么你可以创建一个在订单日期上的索引。
反例假设有一张名为users的表你创建了在用户名、邮箱、手机号上的所有索引。这样会使得数据的插入、修改和删除的效率降低并且占用大量的空间。因此你应该评估每个索引的必要性并只在实际需要时创建索引。
4.9 索引不适合建在有大量重复数据的字段上
索引是在数据库中对表的一些数据字段建立索引的一种技术用来加速检索数据的速度。但如果该字段有大量重复数据那么索引并不适合在这个字段上建立。原因如下
索引数据冗余。如果该字段有大量重复数据那么索引所存储的数据就会大量冗余因为每一条重复的数据都需要占用一个索引空间。索引空间浪费。由于索引数据冗余索引空间的利用率也会相应的降低浪费空间资源。增加数据插入时间。如果在有大量重复数据的字段上建立索引则每次数据插入时需要更新索引这会使数据插入时间变慢。
正例建立索引适合建在数据分布较为均匀不存在大量重复数据的字段上如身份证号、电话号码等字段。
反例不适合建立索引的字段如性别、状态等因为这些字段有大量重复数据不利于提升检索效率。
4.10 where限定查询的数据不扩大条件范围
在 SQL 中WHERE 子句用于筛选满足特定条件的数据对于查询的优化来说尽量减少 WHERE 子句限定的数据范围是很有必要的。
为什么因为数据库管理系统需要扫描整个数据表以确定哪些行符合 WHERE 子句中的条件而不符合条件的行将被排除。因此限制 WHERE 子句的数据范围可以减少数据库管理系统扫描的数据量从而提高查询的效率。
正例
假设有一个名为 “orders” 的表其中有订单的详细信息包括客户姓名、订单编号、产品名称、价格和日期。如果要查询仅在2022年2月份内的订单则可以使用以下 SQL 语句
SELECT *
FROM orders
WHERE date BETWEEN 2022-02-01 AND 2022-02-28;反例
如果使用以下 SQL 语句查询所有订单则需要扫描整个 orders 表
SELECT *
FROM orders;与正例相比这种查询方式的效率要低得多因为它需要扫描整个表以检索所有订单而不是仅检索满足特定条件的订单。因此使用 WHERE 子句限制数据范围是提
4.11 避免在索引列上使用内置函数
避免在索引列上使用内置函数是因为使用内置函数将导致SQL引擎无法识别该列进行索引。由于索引是用来加速查询的如果索引列上有内置函数那么SQL引擎将无法使用该索引查询效率会降低。
正例如下查询中使用的是字段name而不是使用内置函数对字段进行运算。
SELECT * FROM users WHERE name John;反例如下查询中使用了内置函数LOWER对字段name进行了运算而不是使用字段本身。
SELECT * FROM users WHERE LOWER(name) john;因为在索引列上使用内置函数导致SQL引擎无法识别该列进行索引查询效率可能会降低。因此在索引列上使用内置函数是不推荐的应该避免这种情况。
4.12 避免在where中对字段进行表达式操作
避免在 WHERE 子句中对字段进行表达式操作是因为表达式操作会使得索引无效。数据库引擎在执行查询时会先进行索引扫描如果扫描的列发生了变换那么数据库就无法在索引中查询到相关数据从而导致索引失效。
正例
SELECT * FROM products WHERE price 100;反例
SELECT * FROM products WHERE price / 2 50;在这个反例中对 price 进行了除法操作使得索引失效数据库可能会进行全表扫描影响查询效率。
4.13 避免在where子句中使用!或操作符
在查询中使用不等于操作符!或时数据库引擎通常需要扫描整个表以寻找符合条件的记录因此效率很低。
正例使用其他运算符如等于操作符或大于操作符以缩小数据范围。
SELECT * FROM orders WHERE order_date ! 2022-01-01;反例使用不等于操作符!或来进行查询。
SELECT * FROM orders WHERE order_date ! 2022-01-01;对于上面的反例如果需要查询不等于某个值的数据可以考虑使用其他方法如将数据分为两部分分别查询。
SELECT * FROM orders WHERE order_date 2022-01-01 OR order_date 2022-01-01;4.14 去重distinct过滤字段要少
使用DISTINCT过滤数据时数据库会从所有的数据行中查找不同的行。这会对数据库的性能造成一定影响。如果选择的字段多则会增加数据库处理的复杂度进而降低查询效率。
正例
SELECT DISTINCT name
FROM users反例
SELECT DISTINCT name, age, gender
FROM users综上所述最好的方法是只在最少的字段上使用DISTINCT过滤。这样可以帮助数据库快速找到不同的行提高查询效率。
4.15 where中使用默认值代替null
在 SQL 中当对 NULL 值进行比较时它们不相等因此使用 NULL 值进行比较的 SQL 查询会比使用默认值进行比较的查询慢。
正例
SELECT * FROM table_name
WHERE column_name default_value;反例
SELECT * FROM table_name
WHERE column_name IS NULL;在上面的反例中查询会对表中的每一行进行比较以确定是否存在 NULL 值这可能导致查询速度较慢。与此相比在正例中查询只需要对等于默认值的行进行比较因此速度较快。
4.16 批量插入性能提升
批量插入性能提升的原因
减少网络开销一次性插入多条数据网络开销比单次插入数据小。减少数据库开销每次执行一条插入语句都会有一些数据库内部的操作批量插入能减少这些操作。减少缓存消失每次执行一条插入语句会消耗一部分缓存批量插入能减少缓存消耗。
正例
INSERT INTO table_name(column1, column2, column3)
VALUES (value1_1, value1_2, value1_3),(value2_1, value2_2, value2_3),(value3_1, value3_2, value3_3),...(valueN_1, valueN_2, valueN_3);反例
INSERT INTO table_name(column1, column2, column3) VALUES (value1_1, value1_2, value1_3);
INSERT INTO table_name(column1, column2, column3) VALUES (value2_1, value2_2, value2_3);
INSERT INTO table_name(column1, column2, column3) VALUES (value3_1, value3_2, value3_3);
...
INSERT INTO table_name(column1, column2, column3) VALUES (valueN_1, valueN_2, valueN_3);4.17 复合索引最左特性
SQL优化中的“复合索引最左特性”指的是复合索引的最左侧字段在查询时的作用是最大的。这是因为当一个复合索引的最左侧字段在查询中使用到时可以帮助提升查询性能因为数据库在索引上面已经组织了数据。
正例 假设有一个订单表其中存储了用户ID订单日期和订单金额。 如果我们要查询每个用户的订单金额并按订单日期排序我们可以创建以下复合索引
CREATE INDEX idx_orders_userid_date_amount
ON orders (user_id, order_date, order_amount)查询语句
SELECT user_id, SUM(order_amount)
FROM orders
GROUP BY user_id, order_date
ORDER BY user_id, order_date在这种情况下数据库将使用复合索引因为该查询涵盖了复合索引的所有字段。
反例 如果查询仅涵盖复合索引的一个字段则数据库不会使用复合索引。
查询语句
SELECT SUM(order_amount)
FROM orders
WHERE user_id 1在这种情况下数据库将扫描整张表而不是使用复合索引。因此如果表很大该查询将非常慢。
4.18 排序字段创建索引
当查询数据时如果需要对结果进行排序则需要消耗大量的计算资源。如果对排序字段创建索引数据库可以使用索引来快速查询数据排序时间将大大缩短。
正例 假设你有一张表Orders其中有几万条订单数据你需要按订单时间Order_Date从最近到最早排序。
你可以为字段Order_Date创建一个索引并在你的SQL查询中使用以下语句
SELECT *
FROM Orders
ORDER BY Order_Date DESC;在这种情况下创建索引将大大加快查询速度因为SQL引擎可以利用索引快速对数据进行排序。
反例 如果你在查询时只选择了一些字段并且不需要对数据进行排序那么为排序字段创建索引是没有意义的。
SELECT Customer_Name, Product_Name
FROM Orders在这种情况下创建索引将增加数据库维护索引的开销但不会带来任何性能提升。
4.19 删除冗余和重复的索引
过多的索引会影响数据库的性能因为数据库在更新、插入、删除操作时都需要维护索引的一致性。因此在索引过多的情况下数据库的性能会明显降低。另外如果存在冗余或重复的索引它们也会浪费数据库的存储空间对查询性能造成负面影响。
正例 假设有一个employees表包含以下字段idnamedepartmentagesalary。
在这种情况下如果在name、department和age上分别创建了索引则删除重复的索引可以提高数据库的性能。
反例 假设有一个sales表包含以下字段idproductpricequantityamount。
在这种情况下如果仅在product上创建了索引则删除索引可能会影响数据库的性能因为在查询某个产品的销售情况时product字段可能是查询的关键字段。
4.20 不要有超过5个以上的表连接
多个表连接会对数据库性能产生很大的影响因为每个表连接都需要进行大量的数据扫描和比较以确定哪些数据需要合并。
超过5个以上的表连接将对数据库性能造成极大的压力并且可能导致查询时间过长因此不建议使用。
正例
假设我们有三个表customersorders和order_items。我们可以使用以下语句来查询客户订单总金额
SELECT customers.name, SUM(order_items.price * order_items.quantity) AS total_amount
FROM customers
JOIN orders
ON customers.id orders.customer_id
JOIN order_items
ON orders.id order_items.order_id
GROUP BY customers.name;反例
假设我们有六个表customersordersorder_itemsproductscategories和suppliers。我们不应该使用以下语句来查询所有客户的订单信息
SELECT customers.name, orders.order_date, order_items.quantity, products.name, categories.name, suppliers.name
FROM customers
JOIN orders
ON customers.id orders.customer_id
JOIN order_items
ON orders.id order_items.order_id
JOIN products
ON order_items.product_id products.id
JOIN categories
ON products.category_id categories.id
JOIN suppliers
ON products.supplier_id suppliers.id;这将对数据库造成极大的压力并可能导致查询时间过长因此不建议使用。
4.21 inner join 、left join、right join优先使用inner join
SQL 连接操作可以连接多个表并生成一个新的数据集。有三种常见的连接类型内连接inner join、左连接left join和右连接right join。
内连接inner join只返回两个表中都存在的行。如果在任一表中没有匹配行则该行将不返回。因此内连接是最常用的连接方式并且比其他连接方式具有更高的效率因为它只返回两个表的交集。
左连接left join将左边的表第一个表的所有行与右边的表第二个表的匹配行进行连接。如果在右边的表中没有匹配行则在结果中返回空值。
右连接right join与左连接类似但是它将右边的表的所有行与左边的表的匹配行进行连接。
为了优化查询的效率建议使用内连接因为内连接只返回两个表的交集因此比其他连接操作更有效。
正例
SELECT *
FROM customers
INNER JOIN orders
ON customers.customer_id orders.customer_id;反例
SELECT *
FROM customers
LEFT JOIN orders
ON customers.customer_id orders.customer_id;4.22 in子查询的优化
使用IN子查询的SQL语句可能会导致性能问题因为它需要额外的内存空间和多余的操作。
正例 假设你有两个表一个是用户表user另一个是订单表order。
你想查询所有有订单的用户你可以使用以下语句
SELECT user.user_id, user.username
FROM user
INNER JOIN order ON user.user_id order.user_id;反例 假设你想查询所有没有订单的用户你可能会使用以下语句
SELECT user.user_id, user.username
FROM user
WHERE user.user_id NOT IN (SELECT order.user_id FROM order);这种方法的问题在于对于每个用户它需要额外的操作来检查该用户是否在订单表中存在。如果用户表很大这将导致性能问题。此外该语句需要额外的内存空间来存储订单表中的数据。
为了避免这些问题你可以使用以下语句
SELECT user.user_id, user.username
FROM user
LEFT JOIN order ON user.user_id order.user_id
WHERE order.user_id IS NULL;这样的语句可以有效地避免使用IN子查询并可以通过更高效的方式查询所有没有订单的用户。
4.23 尽量使用union all替代union
SQL 优化中使用 UNION ALL 替代 UNION 是一种常见的优化方式因为 UNION ALL 的性能更高主要原因如下
去重UNION 会去除重复的行而 UNION ALL 则不会。在数据量大的情况下去重是一个非常耗时的操作因此使用 UNION ALL 可以大大提高查询速度。排序UNION 会在所有数据拼接在一起之后再进行排序而 UNION ALL 不会。如果数据量较大排序会对查询速度产生较大影响。
正例
SELECT col1, col2, col3
FROM table1
UNION ALL
SELECT col1, col2, col3
FROM table2;反例
SELECT col1, col2, col3
FROM table1
UNION
SELECT col1, col2, col3
FROM table2;在实际使用中应根据业务需求来选择 UNION 或 UNION ALL如果需要去重的话则需要使用 UNION。
4.24 小表驱动大表
小表驱动大表是指在进行两个或多个表的关联操作时尽量选择记录数更少的表作为驱动表这样可以大大提高查询效率。
例子
假设有两张表A和BA表有100万条记录B表有10万条记录当我们使用A表作为驱动表时执行的查询语句将是
SELECT * FROM A LEFT JOIN B ON A.id B.id如果使用B表作为驱动表时执行的查询语句将是
SELECT * FROM B LEFT JOIN A ON B.id A.id在这种情况下选择B表作为驱动表可以大大提高查询的效率。
4.25 高效的分页
分页是一种常见的数据库优化技巧其目的是有效地从数据库中检索部分数据而不是将所有数据加载到内存中。
一种常见的分页方法是使用 LIMIT 子句例如
SELECT * FROM table_name LIMIT a, b;其中a 表示从第 a 条数据开始b 表示从该位置加载 b 条数据。然而使用 a 作为分页起始位置会对数据库性能产生负面影响因为数据库必须检查数据表中的所有数据以确定在第 a 条数据之前存在多少条数据。
为了提高效率推荐使用条件代替 LIMIT 中的 a。
正例
SELECT * FROM table_name WHERE id 10 LIMIT 10;反例
SELECT * FROM table_name LIMIT 10, 10;在正例中数据库只需要扫描 id 大于 10 的数据并选择最多 10 条数据。这将大大提高数据库的性能特别是在大型数据表中。
请注意在反例中数据库需要扫描整个数据表以确定第 10 条数据的位置然后才能加载数据。这会导致效率低下并影响数据库的性能。
4.26 提升group by的效率
GROUP BY 和 HAVING 两种查询方式在查询时都会对数据进行分组和筛选但是 HAVING 在数据分组之后再进行条件筛选效率较低。而 WHERE 条件则是在查询前对数据进行筛选效率较高。
正例
假设有一张 sales 表需要查询每个销售员的销售额。
原始的 HAVING 查询
SELECT salesman, SUM(amount) AS total_sales
FROM sales
GROUP BY salesman
HAVING SUM(amount) 1000;优化后的 WHERE 查询
SELECT salesman, SUM(amount) AS total_sales
FROM sales
WHERE amount 1000
GROUP BY salesman;反例
如果要统计每个销售员销售额中的最高金额那么此时不能使用 WHERE 条件进行优化因为最高金额是在数据分组之后才能计算出来的。
此时的查询只能使用 HAVING 条件
SELECT salesman, SUM(amount) AS total_sales
FROM sales
GROUP BY salesman
HAVING MAX(amount) 1000;5. 总结
SQL优化是提高数据库性能的关键步骤它可以帮助您缩短响应时间并减少对服务器的资源消耗。下面是一些常见的SQL优化步骤
明确目标 在优化 SQL 之前你需要明确你的目标是提升某个操作的速度还是减小数据库的资源消耗分析 SQL 语句 分析 SQL 语句是第一步可以帮助你确定哪些操作对性能产生了影响。你可以使用工具例如 EXPLAIN 命令来分析 SQL 语句。简化 SQL 语句 在分析 SQL 语句后你可以简化 SQL 语句例如删除多余的表连接和减少子查询的数量。正确的数据类型使用合适的数据类型可以节约磁盘空间和内存并缩短查询时间。合适的索引创建索引是提高查询性能的有效方法但要保证索引不冗余或重复并遵得复合索引的最左特性。批量插入将多条SQL语句合并为单个语句可以提高效率并减少与数据库的交互次数。避免过多的表连接避免在单个查询中使用超过5个表连接因为这会增加查询的复杂度和时间。使用合适的连接方式 当执行 SQL 查询时使用合适的连接方式例如 INNER JOIN 或 LEFT JOIN可以提升查询的速度。使用inner join在多表查询时应该优先使用inner join因为它只返回匹配的行。优化in子查询使用exists语句或join语句代替in子查询可以提高查询性能。使用union all替代union除非需要去重否则应该优先使用union all语句因为它不需要去重的代价。小表驱动大表将小表作为驱动表可以提高查询性能降低数据库计算的数据量。
实际上sql的优化没有确定的步骤和方式一定需要结合实际情况使用上面的一个或者多个点进行优化同时需要注意不要过度优化。
