2-3-3-3、高性能索引使用策略及了解分区表

最新推荐文章于 2024-08-01 08:23:29 发布
最新推荐文章于 2024-08-01 08:23:29 发布
阅读量90 收藏
点赞数
分类专栏： 进阶 MySQL性能调优 文章标签： 数据库 mysql 索引调优 索引失效问题 MySQL分区
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_41381248/article/details/129274284
版权

目录


高性能索引使用

示例SQL

-- 示例表： 
CREATE TABLE `employees` ( 
 `id` int(11) NOT NULL AUTO_INCREMENT, 
 `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', 
 `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', 
 `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位', 
 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间', 
 PRIMARY KEY (`id`), 
 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE 
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表'; 

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW()); 
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW()); 
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

使用策略

不在索引列上做任何操作

例如计算、函数、（自动or手动）类型转换等，会导致索引失效而转向全表扫描
正例：

explain select * from employees where name = 'LiLei';

image.png
反例：

explain select * from employees where left(name, 3) = 'LiL';

image.png

尽量全值匹配

对于复合索引来说，尽量做到全值匹配，这样才能充分的利用索引，从而提高查询的命中率
正例：

explain select * from employees where name = 'LiLei' and age = 22 and position = 'manager';

image.png
反例：

explain select * from employees where name = 'LiLei';

image.png

最左前缀法则

对于复合索引来说，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
正例：

explain select * from employees where name = 'LiLei' and age = 22 and position = 'manager';

image.png
反例：

explain select * from employees where position = 'manager';

image.png

范围条件放最后

对于复合索引来说，如果存在范围查询，那么尽量将其放到最后，其原因有两点：

  1. 索引范围查询越后，就可以过滤掉更多的数据
  2. 索引范围查询越前，就会导致索引命中越低，甚至不使用索引

正例：

explain select * from employees where name = 'LiLei' and age = 22 and position > 'a';

image.png
反例：

explain select * from employees where name > 'LiLei' and age = 22 and position = 'manager';

image.png

尽量使用覆盖索引

只访问索引的查询（索引列包含查询列），减少回表，sql中防止select *
正例：

explain select name, age from employees where name = 'LiLei' and age = 22 and position = 'manager';

image.png
反例：

explain select * from employees where name = 'LiLei' and age = 22 and position = 'manager';

image.png

慎用取非操作

mysql 在使用不等于(!= 或者<>)，not in，not exists的时候可能会导致近似于全表扫描而无法使用索引
需要大量数据，并且通过成本计算才能判断是否走索引

慎用空值

is not null 容易导致索引失效，is null 则会区分被检索的列是否为 null，如果是 null 则会走 ref 类型的索引访问，如果不为 null，也是全表扫描

explain select * from employees where name is null;

image.png

explain select * from employees where name is not null;

image.png
总的来说，在设计表时列尽可能的不要声明为 null

注意like操作

一般来说，like 以通配符开头(‘%abc…’)，mysql 索引失效会变成全表扫描的操作
正例：

explain select * from employees where name like 'Lei%';

image.png
反例：

explain select * from employees where name like '%Lei';

image.png
解决方式：

  • 使用覆盖索引，查询字段必须是建立覆盖索引字段
explain select name from employees where name like '%Lei';

image.png

  • 使用专业搜索引擎代替，例如es等

字符类型注意引号

字符串不加单引号索引失效

explain select * from employees where name = 1;

image.png

注意使用or

一般情况下，使用or也会导致索引失效，因为根据索引拿到数据之后，由于另一个条件的存在，还需要全表扫描，因此，没必要走索引

explain select * from employees where name = 'aa' or age = 22;

image.png
当然，覆盖索引也能解决这个问题：

explain select name, age from employees where name = 'aa' or age = 22;

image.png

排序和分组尽量命中索引

只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL 才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当 0RDER BY 子句引用的字段全部为第一个表时，才能使用索引做排序
正例：

explain select * from employees where name = 'aa' order by name;

image.png
反例：

explain select * from employees where name = 'aa' order by position;

image.png
注意：

  • 排序的顺序要和索引的顺序一致，尽量避免部分升序，部分降序的情况
  • 排序的索引不能包含多个索引，这样也会导致利用索引排序失效

主键顺序性

尽可能保证主键是有序插入的，避免随机插入导致频繁的页分裂，数据移动等问题

注意count使用

COUNT()是一个特殊的函数，有两种非常不同的作用:

  • 统计某个列值的数量：count(column)
  • 统计返回结果集的行数：count(*)/count(1)/count(主键)

limit分页优化

根据自增且连续的主键排序的分页查询

对于下面sql，需要扫描10010条数据才能返回10条数据，这无疑给了mysql巨大的压力：

select * from employees limit 10000,10;

解决方案：

select * from employees where id > 10000 limit 10;

注意：

  1. 这条改写的SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致
  2. 如果原 SQL 是 order by 非主键的字段，按照上面说的方法改写会导致两条 SQL 的结果不一致

需要满足的条件：

  1. 主键自增且连续
  2. 结果是按主键排序的
根据非主键字段排序的分页查询

根据非主键字段排序的分页查询，SQL 如下：

select * from employees order by name limit 10000,5;

解决方案：

select e.* from employees e inner join (select id from employees order by name limit 10000,5) ed on e.id = ed.id;

实例巩固

假设存在索引index(a,b,c)

where 语句索引是否被使用
where a = 3Y，使用到a
where a = 3 and b = 5Y，使用到a，b
where a = 3 and b = 5 and c = 4Y，使用到a，b，c
where b = 3 或者 where b = 3 and c=4 或者 where c = 4N
where a = 3 and c = 5Y，使用到a，但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5Y，使用到a和b，c不能用在范围之后，b断了
where a = 3 and b like ‘kk%’ and c = 4Y，使用到a，b，c
where a = 3 and b like ‘%kk’ and c = 4Y，只用到a
where a = 3 and b like ‘%kk%’ and c = 4Y，只用到a
where a = 3 and b like ‘k%kk%’ and c = 4Y，使用到a，b，c

口诀速记

全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
LIKE 百分写最右，覆盖索引不写*
不等空值还有 OR，索引影响要注意
VARCHAR 引号不可丢， SQL 优化有诀窍

了解分区表

简介

分区是指根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库的应用而言，逻辑上只有一个表或一个索引,但是实际上这个表可能由数 10 个物理分区对象组成，每个分区都是一个独立的对象，可以独自处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑
分区表是一个独立的逻辑表，但是底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的的封装。对分区表的请求，都会转化成对存储引擎的接口调用。所以分区对于 SQL 层来说是一个完全封装底层实现的黑盒子，对应用是透明的，但是如果从底层的文件系统来看就会发现，每一个分区表都有一个使用#分隔命名的表文件
MySQL 在创建表时使用 PARTITION BY 子句定义每个分区存放的数据。在执行查询的时候，优化器会根据分区定义过滤那些没有所需数据的分区，这样查询就无须扫描所有分区一—只需要查找包含需要数据的分区就可以了

作用

分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关的数据存放在一起，另外，如果想一次批量删除整个分区的数据也会变得很方便。在下面的场景中,分区可以起到非常大的作用：

  • 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据
  • 分区表的数据更容易维护。例如,想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作
  • 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。可以使用分区表来避免某些特殊的瓶颈，例如 InnoDB 的单个索引的互斥访问、ext3 文件系统的 inode 锁竞争等
  • 如果需要,还可以备份和恢复独立的分区,这在非常大的数据集的场景下效果非常好

分区表本身也有一些限制，下面是其中比较重要的几点:

  • 一个表最多只能有 1024 个分区
  • 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
  • 分区表中无法使用外键约束

分区表的原理

分区表由多个相关的底层表实现，这些底层表也是由句柄对象（Handlerobject)表示，所以也可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎)，分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。分区表上的操作按照下面的操作逻辑进行:
虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁，例如 InnoDB，则会在分区层释放对应表锁。这个加锁和解锁过程与普通 InnoDB 上的查询类似

分区表的类型

MySQL 支持多种分区表，如下所示：

  • RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区
  • LIST 分区：类似于按 RANGE 分区，区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择
  • HASH 分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式
  • KEY 分区：类似于按 HASH 分区，区别在于 KEY 分区只支持计算一列或多列，且 MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值
  • 复合分区/子分区：目前只支持 RANGE 和 LIST 的子分区，且子分区的类型只能为 HASH 和 KEY

分区表的基本语法

RANGE

create table test_range_partition (
  id int not null primary key,
  first_name varchar(30),
  last_name varchar(30)
)
partition by range(id)(
  partition p0 values less than (11),
  partition p1 values less than (21),
  partition p2 values less than (31),
  partition p3 values less than (41)
);

上述例子代表的含义是：定义一个员工表，根据员工ID分区，110号员工一个分区，1120号员工一个分区，依次类推，共建立4个分区

LIST

create table test_list_partition (
	c1 int,
	c2 int
)
partition by list (c1) (
		partition p0 values in (1, 4, 7),
		partition p1 values in (2, 5, 8)
);

意味着 insert 时，c1 的值只能在 1, 4, 7,2, 5, 8 中，不在这个范围内插入就会报错

insert into test_list_partition(c1, c2) value (3,3);

image.png

HASH

create table test_hash_partition (
	id int not null,
	ename varchar (30),
	hired date not null default '1970-01-01',
	separated date not null default '9999-12-31',
	job varchar (30) not null,
	store_id int not null
) partition by hash ( store_id ) partitions 4;

上面是一个基于 store_id 列 HASH 分区的表，表被分成了 4 个分区，如果我们插入的记录 store_id=234，则 234 mod 4 = 2，这条记录就会保存到第二个分区。虽然我们在 HASH（）中直接使用的 store_id 列，但是 MySQL 是允许基于某列值返回一个整数值的表达式或者 MySQL 中有效的任何函数或者其他表达式都是可以的

KEY

create table test_key_partition (
	id int not null,
	ename varchar (30),
	hired date not null default '1970-01-01',
	separated date not null default '9999-12-31',
	job varchar (30) not null,
	store_id int not null
) partition by key ( job ) partitions 4;

上面是一个基于 job 字段进行 Key 分区的表，表被分成了 4 个分区。KEY ()里只允许出现表中的字段

不建议使用分区表

在实际的互联网中，MySQL 分区表用的极少，更多的是手动实现分库分表或者借助其他的分库分表的框架，例如mycat，shardingshere相关的产品
分库分表除了支持 MySQL 分区表的水平切分以外，还支持垂直切分，把一个很大的库（表）的数据分到几个库（表）中，每个库（表）的结构都相同，但他们可能分布在不同的 mysql 实例，甚至不同的物理机器上，以达到降低单库（表）数据量，提高访问性能的目的

分区表与分库分表的对比

  1. 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
  2. 一旦数据量并发量上来，如果在分区表实施关联，就是一个灾难
  3. 底层关联方式属于黑盒，对于维护有一定的困难
  4. 分区表无论怎么分，都是在一台机器上，天然就有性能的上限
析道者
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
高性能mysql读书笔记四-MySQL高级特性
liushangzaibeijing的博客
03-24 3万+
本篇博文是高性能Mysql一书中 mysql高级特性分析。主要介绍了分区表，视图、存储代码三大模块，详细内容包含：分区表概念，优劣,类型，原理、使用、示例等, 视图的概念，示例，存储代码主要包含 触发器、事件、存储过程、存储函数。本篇在介绍高级特性的时候，辅助以详尽的语法、示例，希望读者能有所收获。
cpp-pgpathman是一个PostgreSQL高性能表分区插件
08-16
**cpp-pgpathman：PostgreSQL的高性能表分区插件** `cpp-pgpathman` 是针对 PostgreSQL 数据库系统设计的一个高效表分区插件。它提供了对数据表进行分区的能力，以优化大型数据库的性能，尤其适用于处理大量数据的...
3、ClickHouse表引擎-MergeTree引擎
alanchanchn的专栏
05-05 4万+
数据的存储方式和位置支持哪些查询操作以及如何支持数据的并发访问数据索引的使用是否可以支持多线程请求是否可以支持数据复制MergeTree引擎该系列引擎是执行高负载任务的最通用和最强大的表引擎，它们的特点是可以快速插入数据以及进行后续的数据处理。该系列引擎还同时支持数据复制（使用Replicated的引擎版本），分区(partition)以及一些其它引擎不支持的额外功能。Log引擎该系列引擎是具有最小功能的轻量级引擎。
TPC-DS标准规范(3)
热门推荐
唐犁的博客
07-05 6万+
TPC-DS是一套决策支持系统测试基准，主要针对零售行业。提供99个SQL查询（SQL99或2003），分析数据量大，测试数据与实际商业数据高度相似，同时具有各种业务模型（分析报告型，数据挖掘型等等）。国内目前相关的翻译文章较少。本文尝试对官网的TPC BENCHMARK DS Standard Specification（下称“原文”）进行翻译。翻译主要参照的是2017年发布的2.6.0版本。现在可以在 http://www.tpc.org/tpc_documents_current_versions/c
Oracle分区表及分区索引的创建
linux__xu的博客
07-08 5万+
关于分区表和分区索引(About Partitioned Tables and Indexes)对于10gR2而言，基本上可以分成几类：•       　　 Range(范围)分区•       　　 Hash(哈希)分区•       　　 List(列表)分区•       　　 以及组合分区：Range-Hash,Range-List。　　对于表而言(常规意义上的堆组织表)，上述分区形式都可...
一篇就够了-带你走进DB2分区
qq_36297093的博客
07-09 3999
今天花了两个小时研究了一下DB2分区的一些相关内容，先总结如下 DB2分区有三个特性+ MQT（物化查询表) DISTRIBUTE BY HASH DPF —— 数据库分区特性 ORGANIZE BY DIMENSION MDC —— 多维聚类 PARTITION BY RANGE TP —— 表分区 接下来我们将会按照三个特性和MQT进行细致讲解 先决条件： 先带大家认识DB2数据库分区的概念和意义 参考文献https://blog.51cto.com...
Oracle-分区表解读
小工匠
10-29 1万+
概述Oracle-OLAP和OLTP解读Oracle-index索引解读Oracle-分区表解读Oracle-锁解读Oracle-等待事件解读Oracle-procedure/cursor解读Oracle关于分区的在线文档当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。 表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表
oracle的 分表 详解 -----表分区（转）
学会改变自己——才能突破
11-26 5442
       今天在群里讨论了一个分库分表的问题，再加上最近生产库中一个订单记录过大的处理方式，对表分区有了更深一步的理解，这里转载了一篇文章。oracle也好，mysql也好，都是支持分区的，通过时间等范围、列表、hash、组合等等。分库迎合了微服务，分表分区迎合了大数据拆表的需求。hbase、MongoDB等NOSQL数据库也都是支持分区（分片）的。结合场景多思考。例如：订单数据过大，就可以根...
oracle分区表中本地索引和全局索引的适用场景
sunbocong的博客
06-11 2万+
oracle分区表仲本地索引和全局索引的适用场景 转自：本地索引和全局索引的适用场景 背景 分区表创建好了之后，如果需要最大化分区表的性能就需要结合索引的使用，分区表有两种索引：本地索引和全局索引。既然存在着两种的索引类型，相信存在即合理。既然存在就会有存在的原因，也就是在特定的场景中就更能发挥出索引的性能的； 本文档通过测试，总结出两种索引的适合的场景； 测试环境 数据库版本：1...
9.x - 13.0 postgresql 分区表新特性及简单用法
Hehuyi_In的博客
12-08 4584
一、 分区表定义与意义 1. 分区表的定义 把一个大的物理表分成若干个小物理表，并使得这些小物理表在逻辑上可以被当成一张表来使用。 主表/父表/Master Table 主表是创建子表的模板，是一个正常的普通表，一般主表并不存任何数据。 子表/分区表/Chlid Table/Partition Table 子表继承并属于一个主表，与主表是一对多的关系，子表中存储所有的数据 2....
深入学习Oracle分区表及分区索引
编程猫的博客
07-09 1753
转载于:http://www.blogjava.net/rabbit/archive/2013/01/08/393955.html深入学习Oracle分区表及分区索引 关于分区表和分区索引(About Partitioned Tables and Indexes)对于10gR2而言，基本上可以分成几类：•       　　 Range(范围)分区•       　　 Hash(哈希)分区•    ...
高性能MySQL-第3版-中英文版本
11-27
2. **查询优化**：讲解了SQL语句的优化技巧，包括编写高效的查询语句、利用EXPLAIN分析查询执行计划、避免全表扫描、使用覆盖索引等，以及如何处理复杂的JOIN操作。 3. **存储引擎**：详细介绍了InnoDB和MyISAM两大...
高性能MySQL笔记-总结
06-29
【高性能MySQL笔记-总结】 MySQL作为一款广泛应用的关系型数据库管理系统，因其开源、免费且性能卓越的特性，在互联网行业中被广泛采用。本笔记旨在系统性地介绍MySQL的基础知识、性能优化及实战案例，帮助读者深入...
ORACLE-admin-12c-newfeatures-activity-guide-3
05-13
- **性能提升**：通过优化索引结构，可以提升查询性能。 - **操作流程**： - 创建一个新的辅助索引。 - 将旧索引中的条目逐步移动到新索引中。 - 最终将新索引替换为原索引。 ##### 4. 全局临时表（Global ...
简单的 mongoDB 学习
qq_19342829的博客
07-31 427
mongodb入门学习整理
社区养老服务小程序的设计
Karen198的博客
07-31 320
管理员账户功能包括：系统首页，个人中心，用户管理，服务人员管理，服务产品管理，服务预约管理，服务状态管理，服务退订管理，活动管理，视频管理。主要技术：Java,Spring,mybatis,mysql,jquery,html。服务器：SpringBoot自带 apache tomcat。微信端账号功能包括：系统首页，服务产品，活动，视频，我的。JDK版本：Java JDK1.8。数据库可视化工具： navicat。数据库版本： mysql5.7。开发工具：IDEA(推荐)开发系统：Windows。
二进制部署k8s集群之master节点和etcd数据库集群（上）
zx52306的博客
07-30 1116
【代码】二进制部署k8s集群之master节点和etcd数据库集群（上）
MySQL备份与恢复
最新发布
m0_74860678的博客
08-01 268
在生产环境中，数据的安全性至关重要任何数据的丢失都可能产生严重的后果造成数据丢失的原因程序错误人为操作错误运算错误磁盘故障灾难（如火灾、地震）和盗窃# 开启二进制日志功能[mysqld]# 重启服务# 有 mysql-bin.000001 就代表成功。
高性能MySQL(第2版)中文版-数据库管理学习资源
"高性能MySQL(第2版)中文版" 《高性能MySQL(第2版)中文版》是一本专为IT专业人士准备的关于MySQL数据库优化和管理的权威指南。这本书深入探讨了MySQL的各种性能提升策略，涵盖了从基础架构设计到高级调优技巧的广泛...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值