Mysql优化(二) | 昏喽喽

# 分区

分区就是把一张表的数据分块存储

目的：提升索引的查询效率

# 范围分区

CREATE TABLE `product_Partiton_Range` (
	`Id` BIGINT(8) NOT NULL,
	`ProductName` CHAR(245) NOT NULL DEFAULT '1',
	`ProductId` CHAR(255) NOT NULL DEFAULT '1',
	`ProductDescription` CHAR(255) NOT NULL DEFAULT '1',
	`ProductUrl` CHAR(255) NOT NULL DEFAULT '1',
	PRIMARY KEY (`Id`),
	INDEX `ProductId` (`ProductId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (Id) PARTITIONS 3 (
PARTITION part0 VALUES LESS THAN (12980), 
PARTITION part1 VALUES LESS THAN (25960), 
PARTITION part2 VALUES LESS THAN MAXVALUE);

1
2
3
4
5
6
7
8
9
10
11
12
13

分区原理：客户端请求-->Id和分区键进行比较-->找到指定分区-->查询

缺陷：

必须使用分区字段才行，不然分区查询就是失败，查询所有分区
RANGE是范围分区，但是分区大小是静态的，需要在创建的时候指定，会存在分区不均的情况。

# Hash分区

CREATE TABLE `product_Partiton_Hash` (
	`Id` BIGINT(8) NOT NULL,
	`ProductName` CHAR(245) NOT NULL DEFAULT '1',
	`ProductId` CHAR(255) NOT NULL DEFAULT '1',
	`ProductDescription` CHAR(255) NOT NULL DEFAULT '1',
	`ProductUrl` CHAR(255) NOT NULL DEFAULT '1',
	PRIMARY KEY (`Id`),
	INDEX `ProductId` (`ProductId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY HASH (Id) PARTITIONS 3;

1
2
3
4
5
6
7
8
9
10

缺陷：只能使用主键和整形字段进行分区，无法进行字符字段但分区。

# Key分区

# 建立复合主键
CREATE TABLE `product_Partiton_Key` (
	`Id` BIGINT(8) NOT NULL,
	`ProductName` CHAR(245) NOT NULL DEFAULT '1',
	`ProductId` CHAR(255) NOT NULL DEFAULT '1',
	`ProductDescription` CHAR(255) NOT NULL DEFAULT '1',
	`ProductUrl` CHAR(255) NOT NULL DEFAULT '1',
	PRIMARY KEY (`Id`),
	INDEX `ProductId` (`ProductId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY KEY (ProductName) PARTITIONS 3;

1
2
3
4
5
6
7
8
9
10
11

缺陷：不支持 text，blob类型做分区。

# List分区

CREATE TABLE `product_Partiton_List` (
	`Id` BIGINT(8) NOT NULL,
	`ProductName` CHAR(245) NOT NULL DEFAULT '1',
	`ProductId` CHAR(255) NOT NULL DEFAULT '1',
	`ProductDescription` CHAR(255) NOT NULL DEFAULT '1',
	`ProductUrl` CHAR(255) NOT NULL DEFAULT '1',
	`ProductStatus` int NOT NULL DEFAULT 0,
	PRIMARY KEY (`Id`,`ProductStatus`),
	INDEX `ProductId` (`ProductId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST(ProductStatus) (
    PARTITION a VALUES IN (1,5,6),
    PARTITION b VALUES IN (2,7,8)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 组合分区

CREATE TABLE `product-Partiton-flex` (
	`Id` BIGINT(8) NOT NULL,
	`ProductName` CHAR(245) NOT NULL DEFAULT '1',
	`ProductId` CHAR(255) NOT NULL DEFAULT '1',
	`ProductDescription` CHAR(255) NOT NULL DEFAULT '1',
	`ProductUrl` CHAR(255) NOT NULL DEFAULT '1',
	PRIMARY KEY (`Id`,`ProductName`),
	INDEX `ProductId` (`ProductId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (Id) PARTITIONS 3
SUBPARTITION BY KEY(ProductName)
  SUBPARTITIONS 2 (
    PARTITION p0 VALUES LESS THAN (12980),
    PARTITION p1 VALUES LESS THAN (25960),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 管理分区

# 删除分区

ALERT TABLE users DROP PARTITION p0; #删除分区 p0

# 重建分区

RANGE分区重建

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));  #将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。

List分区重建

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));#将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。

Hash/key分区重建

ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2; #用 REORGANIZE 方式重建分区的数量变成2，在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。

# 索引

# 索引失效

查询条件中有or

# 建表语句
CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_age` (`age`),
  KEY `index_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

like查询以'%'开头

explain
select * from product where ProductId like'%226'   # type 为 all

# 如果想让以'%'开头仍然使用索引，则需要使用覆盖索引，即只查询带索引字段的列：
explain
select ProductId from product where ProductId like'%226' #查询计划里的key用到了index_name。

1
2
3
4
5
6

查询的列有运算或函数

explain
select * from product where substr(ProductId,-2)='226'

1
2

查询条件类型和字段值类型不匹配，不走索引

explain
select * from product where ProductId=1226 # ProductId为CHAR类型

1
2

关联查询关联字段编码格式不一致

建表语句

CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_age` (`age`),
  KEY `index_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET gbk COLLATE gbk_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

注意user 表的name字段编码是utf8mb4，而job表的name字段编码为gbk。执行左外连接查询：

EXPLAIN 
select a.name,b.name,b.job
from user a
left JOIN job b ON a.name =b.name

1
2
3
4

mysql估计使用全表扫描要比使用索引快

CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_age` (`age`),
  KEY `index_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test`.`user`(`name`, `age`, `address`, `id`) VALUES ('光头强', 12, '狗熊岭', 1);
INSERT INTO `test`.`user`(`name`, `age`, `address`, `id`) VALUES ('熊大', 9, '狗熊岭2', 2);
CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test`.`job`(`id`, `userId`, `job`, `name`) VALUES (1, 1, 'java', '光头强');
INSERT INTO `test`.`job`(`id`, `userId`, `job`, `name`) VALUES (2, 2, 'php', '熊大');

EXPLAIN 
select a.name,b.name,b.job from
user a
left JOIN job b ON a.name =b.name

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

此时，由于要查询b.name，mysql需要回表，mysql认为走全表扫描会快一些，所以即使b表的name有索引，也不会走。

连接查询中，按照优化器顺序的第一张表不走索引

CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_age` (`age`),
  KEY `index_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test`.`user`(`name`, `age`, `address`, `id`) VALUES ('光头强', 12, '狗熊岭', 1);
INSERT INTO `test`.`user`(`name`, `age`, `address`, `id`) VALUES ('熊大', 9, '狗熊岭2', 2);
CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

插入数据的insert语句就不写了，要多插入一些，否则下面演示会失效，因为数据少mysql认为走全表扫面快一些，就怎么都用不到索引了。执行查询：

EXPLAIN 
select a.name,a.age,b.name,b.job
from user a
left JOIN job b ON a.name =b.name

1
2
3
4

此时a表user上的name是有索引的，b表name无索引，看下执行计划

a表，b表都是全表扫描，而建表语句不变，把左外连接改为右外连接，可以看到，a表用到了索引

EXPLAIN 
select a.name,a.age,b.name,b.job
from user a
right JOIN job b ON a.name =b.name

1
2
3
4

同样，将右连接改为内连接再看下，a表也是用到了索引

EXPLAIN 
select a.name,a.age,b.name,b.job
from user a
inner JOIN job b ON a.name =b.name

1
2
3
4

从上面三个连接查询来看只有左外连接a表没有用到索引的，这就是因为由于是左外连接，所以优化器的执行顺序是a表、b表，也就是说首先全表扫描a表，再根据a表的name查询b表的值，所以a 表无法用到索引。用段伪代码解释下：

//mysql代码
select a.name,a.age,b.name,b.job
from
user a
left JOIN job b
ON a.name =b.name
//相当于执行以下循环
List<Map<String,Object>> resultA=select a.name,a.age from user a
for(Map<String,Object> map: resultA)
{
    List<Map<String,Object>> resultB=select b.name,b.job from job b where b.name=map.get("name")
}

1
2
3
4
5
6
7
8
9
10
11
12

从这段伪代码可以看到a表没有where语句，所以根本无法用到name上的索引，而b表此时name上若无索引的话，导致两个表都是全表扫描，所以一般这种连接查询，A表关联B表，要在将优化器顺序的第二张表上关联的字段上加索引，而第一张表则无需加索引，无用的索引也会影响性能。而第三个例子中内连接就不一样了，如果是内连接优化器本身就会根据索引情况，连接表的大小等去选择执行顺序了，所以上例中的内连接执行顺序是b、a，这样仍然可以用到a的索引。

查询中没有用到联合索引的第一个字段

explain
select * from product where ProductDescription = '22'

1
2

联合索引OR失效

explain
select * from product where ProductName ='22' or ProductDescription = '22'

1
2

联合索引范围查询 > 部分索引失效

explain
select * from product 
where ProductName ='22' and ProductDescription = '22' and ProductId > '22'

1
2
3

IS NULL和IS NOT NULL
- 如果字段不允许为空，则is null 和 is not null这两种情况索引都会失效。
- 如果字段允许为空，则is null走 ref 类型的索引，而is not null索引失效。

# 索引创建原则

优先使用唯一索引，能够快速定位
为常用查询字段建索引
为排序、分组和联合查询字段建索引
一张表的索引数量不超过5个
表数据量少，可以不用建索引
尽量使用占用空间小的字段建索引
用idx_或unx_等前缀命名索引，方面查找
删除没用的索引，因为它会占一定空间