无锡网站建站公司惠阳网站开发

当前位置： 首页 > news >正文

无锡网站建站公司,惠阳网站开发,wordpress qnap nas外网,韩国企业网站设计未完待续… 1. 分库、分表结构优化 1.1 数据库设计 不规范的数据库设计存在数据冗余以及插入、更新、删除异常问题。 规范化#xff08;Normalization#xff09;是数据库设计的一系列原理和技术#xff0c;主要用于减少表中数据的冗余#xff0c;增加完整性和一致性… 未完待续… 1. 分库、分表结构优化 1.1 数据库设计 不规范的数据库设计存在数据冗余以及插入、更新、删除异常问题。 规范化Normalization是数据库设计的一系列原理和技术主要用于减少表中数据的冗余增加完整性和一致性同时使得数据库易于维护和扩展。 对于大多数的数据库系统而言到达第三范式就已经足够了。也就是说表需要定义主键表中的字段都是不可再分的单一属性。非主键字段必须完全依赖于主键不能只依赖于主键的一部分。属性不依赖于其它的非主属性。 对于前三个范式而言只需要将不同的实体/对象单独存储到一张表中并且通过外键建立它们之间的联系即可满足。 规范化可能导致连接查询JOIN过多从而降低数据库的性能。因此有时候为了提高某些查询或者应用的性能而故意降低规范反的程度也就是反规范化。 常用的反规范化方法包括 增加冗余字段增加计算列  将小表合成大表等。 例如想要知道每个部门的员工数量需要同时连接部门表和员工表可以在部门表中增加一个字段emp_numbers查询时就不需要再连接员工表但是每次增加或者删除员工时需要更新该字段。 反规范化可能带来数据完整性的问题因此通常我们应该先进行规范化设计再根据实际情况考虑是否需要反规范化。一般来说数据仓库Data Warehouse和在线分析系统OLAP会使用到反规范化的技术因为它们以复杂查询和报表分析为主。 推荐图书《数据库系统概念第七版》 1.2 选择数据类型 我们在选择字段的数据类型时首先应该满足存储业务数据的要求其次还需要考虑性能和使用的便捷性。 一般来说我们可以先确定基本的类型 文本数据使用字符串类型进行存储。  数值数据尤其是需要进行算术运算的数据使用数字类型。  日期和时间信息最好使用原生的日期时间类型。  文档、图片、音频和视频等使用二进制类型。但推荐存储在文件服务器上数据库中存储文件的路径以减少数据库的压力。 然后进一步确定具体的数据类型。 1在满足数据存储和扩展的前提下尽量使用更小的数据类型。这样可以节省一些存储通常性能也会更好。例如对于一个小型公司而言员工人数通常不会超过几百可以使用SMALLINT类型存储员工编号。 2尽量避免使用NULL属性。NULL需要更多的存储和额外的处理尽量使用NOT NULL加上默认值。 3如果一个字段同时出现在多个表中我们应该使用相同的数据类型。例如员工表中的部门编号dept_id字段与部门表的编号dept_id字段应该保持名称和类型一致。 1.3 数字类型 1.3.1 整数类型 MySQL支持TINYINT、SMALLINT、MEDIUMINT、INTINTEGER以及BIGINT整数类型。如果为整数类型指定了UNSIGNED属性可以存储的正整数范围将会扩大一倍。

MySQL 8.0.17开始整数类型的显示宽度例如INT(10)和ZEROFILL选项已经被弃用非标准型将来的版本中会删除。直接定义 INT 即可。 显示宽度被弃用的原因 MySQL 8.0.17 弃用整数类型的显示宽度和 ZEROFILL 选项是因为它们在实际使用中没有实际影响。显示宽度仅仅是用于控制在显示查询结果时整数的宽度并不会影响存储或计算。而 ZEROFILL 选项则是指定在显示整数时是否填充零。 然而这些选项在实际的数据存储和计算过程中并没有实际意义。整数类型在数据库中的存储大小是固定的不受显示宽度的影响。而 ZEROFILL 选项只是在显示整数时添加了填充零的功能并不会改变实际存储的值。 因此MySQL 社区决定弃用这些选项以简化整数类型的使用和理解。通过直接定义 INT 类型可以更清晰地表示整数类型的含义而无需关注显示宽度或 ZEROFILL 选项。 1.3.2 实数类型 MySQL提供了精确数字类型DECIMAL也支持浮点数类型FLOAT和DOUBLE。 DECIMAL(p, s)用于存储对精度要求严格的数据计算速度较慢例如财务。其中精度p表示总的有效位数刻度s表示小数点后允许的位数。例如123.04的精度为5刻度为2。 DECIMAL使用二进制格式存储每9个数字使用4字节表示。NUMERIC是DECIMAL的同义词。 FLOAT是单精度浮点数需要4字节存储空间DOUBLE是双精度浮点数需要8字节存储空间。浮点数使用近似运算速度比DECIMAL更快但可能丢失精度。 CREATE TABLE t(d1 DOUBLE, d2 DOUBLE); INSERT INTO t(d1, d2) VALUES (101.40, 80.0);// 此时查不到数据因为 21.4 只是近似数据和真实情况不完全匹配

SELECT * FROM t WHERE d1-d221.4; – 101.40-80.0d1|d2|

一种折衷的方案是使用BIGINT替代DECIMAL存储财务数据整数相对于DECIMAL计算速度更快所以可以考虑先将DECIMAL转换为整数然后使用BIGINT存储。例如要存储精确到万分之一分的金额可以将数据乘以100万倍之后存储到BIGINT可以减少存储并优化计算性能不过应用程序可能需要增加额外的处理。  1.4 字符串类型 1.4.1 CHAR与VARCHAR 1.4.2 BINARY与VARBINARY 1.4.3 TEXT与BLOB 1.4.4 ENUM类型 1.4.5 SET类型 1.5日期时间类型 1.5.1 日期类型 1.5.2 时间类型 2. 索引优化 2.1 索引简介 以下是一个简单的查询语句它的作用是查找编号为5的员工 SELECT * FROM employee WHERE emp_id 5; 如果没有索引数据库就只能扫描整个员工表然后依次判断每个数据记录中的员工编号是否等于5并且返回满足条件的数据。这种查找数据的方法被称为全表扫描Full Table Scan。 全表扫描最大的一个问题就是当表中的数据量逐渐增加时性能随之明显下降因为磁盘 I/O 是数据库最大的性能瓶颈。 当表中的数据量很小例如配置表或者查询需要访问表中大量数据数据仓库索引对查询的优化效果不会很明显。 为了解决大量磁盘访问带来的性能问题MySQL引入了一个新的数据结构索引Index。索引在MySQL中也被称为键Key。MySQL默认使用B-树B树索引它就像图书后面的关键字索引一样按照关键字进行排序并且提供了指向具体内容的页码。 B-树索引就像是一棵倒立的树树的节点按照顺序进行组织节点左侧的数据都小于该节点的值节点右侧的数据都大于该节点的值。B树索引基于B-树索引进行了优化 它们只在叶子节点存储索引数据降低树的高度从而减少了磁盘访问次数 并且增加了叶子节点或者兄弟节点之间的指针优化范围查询。 举例来说假设索引的每个分支节点可以存储100个键值100万条记录只需要3层B-树即可完成索引。 数据库通过索引查找指定数据时需要读取3次磁盘I/O每次磁盘I/O读取整个索引节点就可以得到查询结果。 如果采用全表扫描的方式数据库需要执行的磁盘I/O可能高出几个数量级。 当数据量增加到1亿条记录时 通过索引访问只需要增加一次磁盘I/O即可 全表扫描则需要再增加几个数量级的磁盘I/O。   主流数据库默认使用的都是B-树B树、 B树索引它们实现了稳定且快速的数据查找O(log n) 对数时间复杂度可以用于优化、、 BETWEEN、 IN运算符以及字符串的前向匹配“ABC%”等查询条件。   2.2 聚簇索引 聚集索引Clustered Index将表中的数据按照索引通常是主键 的结构进行存储。 也就是说聚集索引的叶子节点中直接存储了表的数据而不是指向数据的指针。 聚集索引其实是一种特殊的表 MySQLInnoDB和 Microsoft SQL Server 将这种结构的表称为聚集索引 Oracle数据库中将其称为索引组织表IOT。这种存储数据的方式类似于Key-Value存储适合基于主键进行查询的应用。 聚簇索引生成方式 如果定义了主键InnoDB使用主键聚集数据如果没有定义主键InnoDB使用第一个非空的UNIQUE索引聚集数据如果没有主键和可用的UNIQUE索引InnoDB使用一个隐藏的内部ID字段聚集数据。存在问题只有一个字段ID如果多个表都是用该方式构建聚簇索引此时内部ID的自增都是在同一个ID上自增。 2.3 辅助索引 MySQLInnoDB中的辅助索引也被称为二级索引Secondary Index叶子节点存储了聚集索引的键值通常是主键。 我们通过二级索上图中使用 name 作为二级索引引查找数据时系统需要先找到相应的主键值再通过主键索引查找相应的数据回表。因此创建聚集索引的主键字段越小二级索引就越小。这也是我们通常使用自增数字而不是UUID作为MySQL主键的原因之一。 二级索引叶子节点存储聚集索引键值的好处当当数据发生改变或移动时可以保证二级索引的稳定性只要ID不变则无需修改二级索引。 二级索引叶子节点存储聚集索引键值的缺点增加了一次回表操作。 2.4 复合索引 复合索引是基于多个字段创建的索引也叫多列索引。 复合索引可以避免为每个字段创建单独的索引使用复合索引时最重要的是索引字段的顺序。 复合索引首先按照第一个最左侧字段排序然后按照第二个字段排序以此类推。因此一个选择索引字段顺序的经验法则是将选择性最高的字段放在最前面。 通过如下命令可以查看不同前缀长度的选择性 SELECT count(DISTINCT emp_name)/count() emp_name_sel,count(DISTINCT sex)/count(*) sex_sel FROM employee;emp_name_sel|sex_sel| ——————-1.0000| 0.0800| 注意如果数据分布不均匀这种经验法则可能对于特定值的查询性能很差。 最左前缀匹配原则复合索引(col1, col2, col3)相当于以下三个索引 (col1)(col1, col2)(col1, col2, col3) 举例来说它可以用于优化以下查询条件左侧的确定了才能使用索引查找右侧的 WHERE col1 val1 AND col2 val2 AND col3 val3WHERE col1 val1 AND col2 val2WHERE col1 val1WHERE col1 val1 AND col2 BETWEEN val2 AND val3WHERE col1 BETWEEN val1 AND val2WHERE col1 LIKE ABC% 2.5 前缀索引 前缀索引Prefix Index是指基于字段的前一部分内容创建的索引。BLOB 、TEXT或者很长的VARCHAR类型字段必须使用前缀索引因为MySQL对索引的长度有限制。MySQL 5.7默认不能超过3072字节。 前缀索引的优点是可以节省空间 提高索引性能但缺点是会降低索引的选择性。 索引的选择性是指不重复的索引值基数和表中的数据总量的比值范围处于1/总数据量到1之间。选择性越高的索引查询效率越高因为可以过滤掉更多的数据。主键和唯一索引的选择性是1。 通过如下命令可以查看不同前缀长度的选择性 SELECT count(DISTINCT LEFT(email,3))/count(DISTINCT email) left3,count(DISTINCT LEFT(email,4))/count(DISTINCT email) left4,count(DISTINCT LEFT(email,5))/count(DISTINCT email) left5,count(DISTINCT LEFT(email,6))/count(DISTINCT email) left6

FROM employee;left3 |left4 |left5 |left6 |

0.6000|0.7200|0.9200|1.0000| 示例中当前缀长度到达6的时候选择性和索引整个email字段没有区别。因此可以基于该字段创建一个前缀索引 CREATE INDEX idx_employee_email ON employee(email(6)); 前缀索引也存在缺点MySQL不能使用前缀索引进行排序ORDER BY和分组GROUP BY也不能实现索引覆盖扫描。 前缀索引的设计关键在于保证足够的选择性同时又不能太长以便节约存储。 2.6 函数索引 MySQL 8.0支持函数索引Function-Based Index也被称为表达式索引Expression-Based Index是基于函数或者表达式创建的索引。 例如员工的电子邮箱不区分大小写并且唯一我们可以基于LOWER(email)函数创建一个唯一的函数索引。

explain select * from employee where lower(email) lower(ZhangFeishuguo.com);Name |Value |

id |1 | select_type |SIMPLE | table |employee | partitions | | type |ALL | // 代表全表扫描因为email建立的索引包含大小写上述查询时都转化为了小写 possible_keys| | key | | key_len | | ref | | rows |25 | filtered |100.0 | Extra |Using where|create unique index uk_emp_email_lower on employee((lower(email))); // 使用小写创建索引

analyze table test;explain select * from employee where lower(email) lower(ZhangFeishuguo.com);Name |Value |

id |1 | select_type |SIMPLE | table |employee | partitions | | type |const | possible_keys|uk_emp_email_lower| key |uk_emp_email_lower| // 使用新建的索引查询 key_len |403 | ref |const | rows |1 | filtered |100.0 | Extra | | 函数索引能够支持其他方式无法使用的数据类型例如JSON数据。 CREATE TABLE employees (data JSON,INDEX idx ((CAST(data-\(.name AS CHAR(30)) COLLATE utf8mb4_bin)) ); INSERT INTO employees VALUES({ name: james, salary: 9000 }),({ name: James, salary: 10000 }),({ name: Mary, salary: 12000 }),({ name: Peter, salary: 8000 });SELECT * FROM employees WHERE data-\).name James; 函数索引要求完全按照索引定义的相同方式指定查询中的条件。 扩展什么是 JSON 数据 在数据库中JSONJavaScript Object Notation是一种用于存储和表示结构化数据的格式。它是一种轻量级的数据交换格式常用于Web应用程序和分布式系统中的数据传输和存储。 JSON数据由键值对组成使用大括号 {} 包围。每个键值对由冒号 : 分隔键是一个字符串值可以是字符串、数字、布尔值、数组、对象或null。JSON数据具有以下特点 1简洁性JSON使用简洁的语法表示数据易于阅读和编写。 2可读性JSON数据采用文本格式可被人类读取和理解。 3可扩展性JSON支持嵌套结构可以构建复杂的数据层次。 下面是一个示例JSON数据 {name: John Doe,age: 30,email: johndoeexample.com,address: {street: 123 Main St,city: New York,state: NY},hobbies: [reading, traveling, photography] } 在这个例子中JSON数据表示一个人的信息。它包含了姓名、年龄、电子邮件和地址等属性。其中地址是一个嵌套的对象包含街道、城市和州。而兴趣爱好是一个数组包含多个字符串元素。 在数据库中JSON数据可以存储在特定的JSON字段中例如MySQL中的JSON数据类型或PostgreSQL中的JSONB数据类型。这样可以方便地存储和查询具有不同结构的数据而无需提前定义固定的表结构。 使用JSON数据类型数据库可以存储和检索非结构化或半结构化的数据适用于存储用户配置、日志、文档、社交媒体数据等。同时数据库系统提供了一系列的JSON函数和操作符用于在查询中处理和操作JSON数据。 2.7 降序索引 MySQL 8.0支持降序索引Descending index索引定义中的DESC不再被忽略而是以降序方式存储索引键值。 在之前的版本中索引支持反向扫描但是性能稍差一些。降序索引可以进行正向扫描效率更高。当查询需要针对某些列升序排序同时针对另一些列降序排序时降序索引使得优化器可以使用多列混合索引扫描。 CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, c1 INT, c2 INT,INDEX idx1 (c1 ASC, c2 ASC),INDEX idx2 (c1 ASC, c2 DESC),INDEX idx3 (c1 DESC, c2 ASC),INDEX idx4 (c1 DESC, c2 DESC) ); 优化器可以为不同的ORDER BY子句使用正向索引扫描而不需要执行 filesort 排序。

explain select * from t ORDER BY c1 ASC, c2 DESC; // 会自动使用最适合的索引方式进行查找Name |Value |

id |1 | select_type |SIMPLE | table |t | partitions | | type |index | possible_keys| | key |idx2 | key_len |10 | ref | | rows |1 | filtered |100.0 | Extra |Using index| MySQL 8.0不再对GROUP BY操作进行隐式排序排序需要明确指定ORDER BY。 2.8 隐藏索引 MySQL 8.0支持隐藏索引invisible index也称为不可见索引。隐藏索引不会被优化器使用。 主键不能设置为隐藏包括显式设置或隐式设置。 CREATE TABLE t1 (i INT,j INT,k INT,INDEX i_idx (i) INVISIBLE ) ENGINE InnoDB;CREATE INDEX j_idx ON t1 (j) INVISIBLE; // 创建一个名为 j_idx 的隐藏索引 ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE; // 添加一个名为 k_idx 的隐藏索引ALTER TABLE t1 ALTER INDEX m_idx INVISIBLE; // 将一个已经创建的索引修改为隐藏索引(假设 m_idx 索引已经被创建) 索引的可见性不会影响索引的维护。例如无论索引是否可见每次修改表中的数据时都需要对相应索引进行更新而且唯一索引都会阻止插入重复的列值。 MySQL系统变量optimizer_switch中的use_invisible_indexes设置控制了优化器构建执行计划时是否使用隐藏索引。如果设置为off默认值优化器将会忽略隐藏索引与引入该属性之前的行为相同。如果设置为on隐藏索引仍然不可见但是优化器在构建执行计划时将会考虑这些索引。 不可见索引特性可以用于测试删除某个索引对于查询性能的影响同时又不需要真正删除索引也就避免了错误删除之后的索引重建。对于一个大表上的索引进行删除重建将会非常耗时而将其设置为不可见或可见将会非常简单快捷。 隐藏索引应用场景软删除、灰度发布。 2.9 覆盖索引 在某些情况下查询语句通过索引访问就可以返回所需的结果不需要访问表中的数据回表此时我们把这个索引称为覆盖索引Covering Index。某些数据库中称之为Index Only Scan。 // emp_id 是主键 id在 dept_id 上面建立了辅助索引

explain select emp_id, dept_id from employee where dept_id 5; Name |Value |

id |1 | select_type |SIMPLE | table |employee | partitions | | type |ref | // 等值查询 possible_keys|idx_emp_dept| // dept_id 索引 key |idx_emp_dept| key_len |4 | ref |const | rows |8 | filtered |100.0 | Extra |Using index | // 使用索引查询且没有回表 上述查询语句要查询的值是 emp_id主键id和 dept_id而辅助索引 dept_id 本身包含了 dept_id叶节点中包含了emp_id主键id所以在查询这两个值时无需回表即可获得想要结果。此时的执行计划中Extra列显示Using index。 覆盖索引是优化器选择的一种执行计划或者也可以说任何索引在某种情况下都可能称为覆盖索引。 任何索引都包含了主键列可用覆盖通过索引查找主键的查询语句。 2.10 索引和排序 MySQL数据排序可通过 filesort 或者索引顺序扫描的方式实现。 文件排序filesort当查询中没有适用于排序的索引或无法使用现有索引时MySQL会使用文件排序。它的工作原理是将查询结果加载到临时文件中然后在文件中进行排序操作。这种方式需要将数据加载到磁盘上的临时文件中然后进行排序可能会导致较高的磁盘I/O和内存消耗。索引顺序扫描当查询中存在适用于排序的索引时MySQL可以直接利用索引的顺序来避免文件排序。它会按照索引的顺序扫描数据并返回按照排序要求的结果索引一般默认升序。 示例一

EXPLAIN SELECT * FROM employee e ORDER BY emp_name;Name |Value |

id |1 | select_type |SIMPLE | table |e | partitions | | type |ALL | // 全表扫描 possible_keys| | key | | key_len | | ref | | rows |25 | filtered |100.0 | Extra |Using filesort| // 此处代表排序但不一定是 filesort 排序也可能是内存排序 上述查询中由执行计划可看出在查询时走的是全表扫描。 走全表扫描而没有走索引扫描的原因查询语句中要查询的是记录的所有字段如果直接通过辅助索引emp_name进行查找会涉及到回表操作虽然辅助索引是有序的但辅助索引叶节点包含的主键 id 是无序的也就是说在进行回表时需要进行大量的随机 IO可参考优化器MRR最终导致查询性能低下不如直接进行全表扫描然后再排序。 优化器 MRRMySQL 优化器 MRR_mysql优化器-CSDN博客 示例二

EXPLAIN SELECT emp_id, emp_name FROM employee e ORDER BY emp_name;Name |Value |

id |1 | select_type |SIMPLE | table |e | partitions | | type |index | possible_keys| | key |idx_emp_name| key_len |202 | ref | | rows |25 | filtered |100.0 | Extra |Using index | 上述查询语句要查询的值是 emp_id主键id和 emp_name而辅助索引 emp_name 本身包含了 dept_id叶节点中包含了emp_id主键id所以在查询这两个值时无需回表即可获得想要结果。此时的执行计划中Extra列显示Using index即没有使用回表直接进行查询。 MySQL索引即可以用于查询数据也可以用于实现排序。前提是索引字段的顺序和ORDER BY子句字段的顺序完全一致最左前缀原则。 对于复合索引(col1, col2, col3)可以用于优化以下查询 WHERE col1 val1 ORDER BY col2, col3WHERE col1 val1 ORDER BY col2 DESCWHERE col1 BETWEEN val1 AND val2 ORDER BY col1, col2col1是范围查询必须出现在ORDERR BY里面 但是无法使用该索引实现以下查询中的排序 WHERE col1 val1 ORDER BY col2 DESC, col3复合索引中 col1, col2, col3 都是默认升序WHERE col1 val1 ORDER BY col3WHERE col1 BETWEEN val1 AND val2 ORDER BY col2, col3 如果查询连接了多个表只有ORDER BY子句字段全部属于第一个表时才能利用索引进行排序。 2.11 重复索引和冗余索引 MySQL允许在相同的字段上按照相同的顺序创建多个相同类型的索引也就是重复索引。这样会占用更多存储空间也导致优化器需要进行更多的评估。 CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY,c1 INT UNIQUE, c2 INT,INDEX idx_pk (id),INDEX idx1 (c1) ); 以上示例中的索引idx_pk和idx1都属于重复索引MySQLInnoDB自动为主键、唯一约束以及外键约束创建相应的索引。 复合索引字段顺序不同则不算重复索引。例如(col1, col2)和(col2, col1)不是重复索引。 索引类型不同则不算重复索引。例如INDEX(col)和FULLTEXT INDEX(col)不是重复索引。 冗余索引是指字段已经被其他索引包含的索引。 如果已经存在复合索引(col1, col2)那么索引(col1)就是冗余索引因为前者可用替代索引(col1)。不过需要注意索引(col2)不是冗余索引因为col2不是索引(col1, col2)的最左前缀列。 索引(col1, id)是一个冗余索引因为辅助索引中一定会包含主键字段。 一般建议基于已有的索引进行扩展而不是不断增加新的冗余索引但是也存在例外。 重复索引和冗余索引的处理方法就是删除索引但是删除之前需要确认不会产生副作用。MySQL 8.0可用利用不可见索引特性减少影响。 另外可能会存在从未使用过的索引通过系统视图sys.schema_unused_indexes查看建议确认后删除。 2.12 索引和DML 索引不仅会对查询产生影响对数据进行插入、更新和删除操作时也需要同步维护索引结构。 INSERT语句 对于INSERT语句而言索引越多执行越慢。插入数据必然导致增加索引项这种操作的成本往往比插入数据本身更高因为索引必须保持顺序和B树的平衡索引节点拆分。因此优化插入语句的最好方法就是减少不必要的索引。 没有任何索引时的插入性能是最好的因此在加载大量数据时可以临时删除所有的索引并在加载完成后重建索引。 UPDATE语句 UPDATE语句如果指定了查询条件可以通过索引提高更新操作的性能因为通过索引可以快速找到需要修改的数据。 另一方面UPDATE语句如果修改了索引字段的值需要删除旧的索引项并增加新的索引项。因此更新操作的性能通常也取决于索引的数量。为了优化UPDATE语句频繁更新的字段不适合创建索引同时应该尽量避免修改过多的字段。 DELETE语句 对于DELETE语句而言如果指定了查询条件可以通过索引提高删除操作的性能。因为它和UPDATE语句一样需要先执行一个SELECT语句找到需要删除的数据。 删除操作涉及的索引更新和插入操作类似只不过它是删除一些索引项并确保索引树的平衡。因此索引越多删除性能越差。不过有一个例外就是没有任何索引这个时候性能会更差因为数据库需要执行全表扫描才能找到需要删除的数据。 2.13 索引设计原则 推荐图书《数据库索引设计与优化》 三星索引 索引将相关的数据存储在一起减少需要扫描的数据量获得一星即针对需要查询字段建立索引索引中的数据顺序和查询排序顺序一致避免排序操作获得二星索引包含了查询所需的全部字段避免随机IO获得三星。 CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, c1 INT,c2 INT,INDEX idx1 (c1, c2)

);EXPLAIN SELECT * FROM t WHERE c1100 ORDER BY c1, c2;Name |Value |

id |1 | select_type |SIMPLE | table |t | partitions | | type |index | // 使用了索引查找 possible_keys|idx1 | key |idx1 | key_len |10 | ref | | rows |1 | filtered |100.0 | Extra |Using where; Using index| // Using where 指 where 判断Using index 指使用 index 查找且无需进行回表 既然索引可以优化查询的性能那么我们是不是遇到性能问题就创建一个新的索引或者直接将所有字段都进行索引显然并非如此因为索引在提高查询速度的同时也需要付出一定的代价 首先索引需要占用磁盘空间。索引独立于数据而存在过多的索引会导致占用大量的空间。其次进行DML操作时也需要对索引进行维护维护索引有时候比修改数据更加耗时。 一般来说可以考虑为以下情况创建索引 经常出现在WHERE条件或者ORDER BY中的字段创建索引可以避免全表扫描和额外的排序操作  多表连接查询的关联字段或者外键涉及的字段可以避免全表扫描和外键级联操作导致的锁表  查询中的GROUP BY分组操作字段。 对于交易类型的系统首先找出查询时间最长或者占用资源最多的语句检查它们涉及的表结构、索引结构判断表结构和索引是否合理。如果这些优化还不能满足要求另一个方法就是SQL查询优化。 如果需要本文 WORD、PDF 相关文档请在评论区留言  如果需要本文 WORD、PDF 相关文档请在评论区留言  如果需要本文 WORD、PDF 相关文档请在评论区留言