mysql优化总结

索引优化

尽量使用联合索引，没有必要的索引不要创建，会影响联合索引命中。
如果时间字段为条件中的关键业务字段。一定要确保时间索引占支配作用。如果命中了其他索引效率反而会变慢。但是时间BETWEEN查询范围如果过大，是不走索引的，具体原因。数据量过大时，建议对表进行分区。业务允许的话尽量限制时间查询范围。

创建联合索引

ALTER TABLE `case_info` ADD INDEX occurrence_district_type_index (`occurrence_date`, `district_name`, `dispose_unit_name`, `satisfaction`, `appeal_type`);

删除索引

ALTER TABLE `case_info` drop index occurrence_district_type_index

表分区

创建分区

将表中的数据进行分区，以月份为分区单位。分区字段必须为主键。

ALTER TABLE case_info_converge
PARTITION BY RANGE( YEAR(occurrence_date) * 100 + MONTH(occurrence_date) ) (
  PARTITION p201901 VALUES LESS THAN (201902),
  PARTITION p201902 VALUES LESS THAN (201903),
  PARTITION p201903 VALUES LESS THAN (201904),
  ...
  PARTITION p202112 VALUES LESS THAN (202201)
);

以时间为分区单位有个缺点：会造成分区内数据分布不均匀

删除指定分区

ALTER TABLE `case_info_converge`
DROP PARTITION p20230926;

新增一个分区

ALTER TABLE case_info ADD PARTITION (PARTITION `p202403` VALUES LESS THAN (202404));

自动分区

Mysql不能自动创建分区，需要使用mysql event事件的方式自动创建分区
创建分区的存储过程如下(每次执行先校验当前分区是否存在，如果存在则不处理；不存在则创建)：

分区创建存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `create_partition_by_month`(IN `IN_SCHEMANAME` varchar(64) CHARSET utf8mb4 COLLATE utf8mb4_general_ci,IN `IN_TABLENAME` varchar(64) CHARSET utf8mb4 COLLATE utf8mb4_general_ci)
BEGIN
	DECLARE ROWS_CNT INT UNSIGNED;
	DECLARE TARGET_DATE TIMESTAMP;
	DECLARE PARTITIONNAME VARCHAR(9) CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
	DECLARE PARTITION_ADD_DAY VARCHAR(9) CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

	SET TARGET_DATE = NOW() + INTERVAL 1 DAY;
	SET PARTITIONNAME = DATE_FORMAT( TARGET_DATE, 'p%Y%m' );
	SET TARGET_DATE = TARGET_DATE + INTERVAL 1 MONTH;
	SET PARTITION_ADD_DAY = DATE_FORMAT( TARGET_DATE, '%Y%m' );
	SELECT COUNT(*) INTO ROWS_CNT FROM information_schema.partitions WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND 	partition_name = PARTITIONNAME;
	
	IF ROWS_CNT = 0 THEN

		SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',

		' ADD PARTITION (PARTITION ', PARTITIONNAME, " VALUES LESS THAN (",

		PARTITION_ADD_DAY ,") ENGINE = InnoDB);" );

		PREPARE STMT FROM @SQL;

		EXECUTE STMT;

		DEALLOCATE PREPARE STMT;

	ELSE

		SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;

	END IF;

END

定时任务

每月最后一天执行

DELIMITER $$
#该表所在的数据库名称
USE `ry_vue_sjzz`$$

CREATE EVENT IF NOT EXISTS `case_info_generate_partition`

ON SCHEDULE EVERY 1 MONTH #执行周期，还有天、月等等

STARTS '2023-12-31 23:00:00'

ON COMPLETION PRESERVE

ENABLE

COMMENT 'Creating partitions'

DO BEGIN

CALL ry_vue_sjzz.create_partition_by_month('ry_vue_sjzz','case_info');

END$$

DELIMITER ;#注意有个空格

查看定时任务是否创建成功

SHOW EVENTS;

删除定时任务

DROP EVENT daily_generate_partition;

查看分区

SHOW CREATE TABLE case_info;

全文检索

字段case_name, description创建全文索引，必须用ngram分词器，不指定ngrm，分词效果很差。
分区表不支持全文检索。创建全文索引的字段必须是CHAR、VARCHAR或者TEXT类型，且表的存储引擎必须是InnoDB或者MyISAM 。

ALTER TABLE case_info_converge ADD FULLTEXT INDEX title_desc_fulltext ( `case_name`, `description` )  WITH PARSER ngram;

如果想对分区的表创建全文检索，可以将表进行copy。取消表的分区。然后对原表创建触发器同步数据。如果时间允许的情况，建议还是上ES。

取消case_info_converge表的分区

ALTER TABLE case_info_converge REMOVE PARTITIONING;

全文索引查询语句

SELECT * FROM case_info_converge WHERE MATCH(`case_name`, `description`) against('果蔬') 

触发器

CREATE TRIGGER case_info_converge_insert AFTER INSERT ON case_info 
FOR EACH ROW
INSERT INTO case_info_converge VALUES (NEW.case_code, NEW.source_row_id, NEW.channel_type);