高性能mysql读书笔记四-MySQL高级特性

mysql高级特性

本篇博客有关mysql高级特性描述均在mysql版本 5.7上使用验证。

1. 分区表

1.1 分区表概念

​ 分区表就是按照某种规则将一个大表分成若干个小表，规则可能是某一个列的值分区分，或者涉及某个列的表达式。这样大量的数据被分散到不同表中，数据量变小查询性能自然变高。

使用分区表的好处

• 表数据非常大或者表数据有明显的冷热数据之分。
• 分区表的数据更容易维护，比如批量删除 可以清除整个分区形式并对独立分区进行优化、检查、修复。
• 分区表的数据可以维护在不同的物理设备，水平扩展性好。
• 避免InnoDB的单索引互斥访问，减少锁竞争。

使用分区表的限制

• 所有分区必须使用相同的存储引擎。

• 每张表的分区数有限，最大分区数目不能超过1024。

• 分区表达式只能是整数或者返回整数的表达式(函数)。

• 分区所用的字段，必须是唯一索引或者主键（主键也是唯一索引）。

• 无法使用外键、全文索引。

1.2 分区表类型

查看mysql是否支持分区

#5.6版本
SHOW VARIABLES LIKE '%partition%'

# 5.6版本之后
show plugins;

• RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

• LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

• HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

• KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

1.3 分区表使用

range分区类型

创建分区表

-- 创建表
DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales` (
  `id` int(11) NOT NULL,
  `money` decimal(11,2) NOT NULL,
  `order_date` date NOT NULL,
  PRIMARY KEY (`id`,`order_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 PARTITION BY RANGE (YEAR(order_date))
(PARTITION p_2020 VALUES LESS THAN (2021) ENGINE = InnoDB,
 PARTITION p_2021 VALUES LESS THAN (2022) ENGINE = InnoDB,
 PARTITION p_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

使用日期范围range 进行分区。，order_date 为分区字段 则必须包含在主键字段内，否则会报如下错误

A PRIMARY KEY must include all columns in the table's partitioning function

对于 MySQL 分区表，无法从数据库层面保证非分区列在表级别的唯一性，只能确保其在分区内的唯一性。

插入数据

-- 插入数据 
INSERT INTO `sales` (`id`, `money`, `order_date`) VALUES('1','123.34','2021-12-01');
INSERT INTO `sales` (`id`, `money`, `order_date`) VALUES('2','12132.33','2022-01-15');
INSERT INTO `sales` (`id`, `money`, `order_date`) VALUES('3','32432.31','2020-03-22');

查询分区统计

 -- 查询数据分区分布
SELECT 
  PARTITION_NAME AS "分区",TABLE_ROWS AS "行数"
FROM
  information_schema.partitions 
WHERE table_schema = "cds-temp" 
 AND table_name = "sales"

list分区类型

创建分区表

-- 创建表
DROP TABLE IF EXISTS `sales`;

CREATE TABLE `sales` (
  `id` INT(11) NOT NULL,
  `money` DECIMAL(11,2) NOT NULL,
  `score` INT(2),
  `order_date` DATE NOT NULL,
  PRIMARY KEY (`id`,`score`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY LIST(score)
( 
  PARTITION pEven VALUES IN (1,3,5,7,9),
  PARTITION pOdd VALUES IN (2,4,6,8,10)
)

插入数据

-- 插入数据
INSERT INTO `sales` (`id`, `money`, `score`,`order_date`) VALUES('1','123.34',1,'2021-12-01');
INSERT INTO `sales` (`id`, `money`, `score`,`order_date`) VALUES('2','12132.33',2,'2022-01-15');
INSERT INTO `sales` (`id`, `money`, `score`,`order_date`) VALUES('3','32432.31',3,'2020-03-22');
INSERT INTO `sales` (`id`, `money`, `score`,`order_date`) VALUES('4','32432.31',4,'2020-03-22');

查询分区统计

 -- 查询数据分区分布
SELECT 
  PARTITION_NAME AS "分区",TABLE_ROWS AS "行数"
FROM
  information_schema.partitions 
WHERE table_schema = "cds-temp" 
 AND table_name = "sales"

HASH分区类型

创建分区表

使用hash函数进行分区，分区数为3 分为p0、p1、p2三个分区。

DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales` (
  `id` INT(11) NOT NULL,
  `money` DECIMAL(11,2) NOT NULL,
  `score