MySQL Tuning little Note

For InnoDB, the primary key is the clustered index, and if there is no primary key definition, the first unique non-null index is used as the clustered index.
If there is no primary key and no appropriate unique index, then InnoDB generates a hidden primary key as a clustered index.
This hidden primary key resembles an auto-increment ID (int).

Remove and re-add primary keys
ALTER TABLE tbname drop PRIMARY key;

ALTER TABLE Tbname add primary key (COL1,COL2,COL3);

CREATE INDEX idx_mete_base_area_station_cabinet on CMS. T_cabinet_electricity_hh_hive (
Base_name ASC,
Area_name ASC,
Station_Name ASC,
Cabinet_name ASC
);

Select DISTINCT device_name, Regexp_extract (Device_name, "-(. *)", 0)
From T_mete_config
WHERE lsc_id in (30,31)
and device_type_name= ' ups distribution '
and station_name like '% Data room% '
ORDER by Device_name;

SELECT regexp_extract (' asdfsadf-job ', '-([A-z]) ', 0) from CMS. DUAL LIMIT 1;

Composite partitioning example,
Take the time granularity day as an example to partition, then partition according to the geography ID,
MySQL supports only the sub-partitions of range and list, and the sub-partition can only be hash or key.

CREATE TABLE T_cabinet_electricity_hh_hive2
(
base_id int not NULL,
Base_name varchar (20),
Lsc_name varchar (255),
area_id bigint,
Area_name varchar (255),
station_id int not NULL,
Station_Name varchar (255),
device_type_id varchar (255),
Device_type_name varchar (255),
device_id varchar (255) is not NULL,
Device_name varchar (255),
mete_id int not NULL,
Mete_name varchar (255),
Max_report_time varchar (20),
Max_mete_value Double,
Min_report_time varchar (20),
Min_mete_value Double,
Avg_mete_value Double,
Hour_sum_value Double,
Hour_times int,
DT_HH varchar (ten) is not NULL,
Hour_last_value Double,
Cabinet_name varchar (255),
DT varchar (not NULL),
lsc_id int not NULL,
PRIMARY KEY (Dt,dt_hh,base_id,lsc_id,mete_id,device_id,station_id,mete_name)
)
PARTITION by RANGE (To_days (DT))
Subpartition by HASH (lsc_id)
Subpartitions 6
(
PARTITION P0 VALUES Less THAN (to_days(' 20170810 ')),
PARTITION P1 VALUES Less THAN (to_days (' 20170811 ')),
PARTITION P2 VALUES Less THAN (to_days (' 20170812 ')),
PARTITION P3 VALUES Less THAN (to_days (' 20170813 ')),
PARTITION P4 VALUES Less THAN (to_days (' 20170814 ')),
PARTITION P5 VALUES Less THAN (to_days (' 20170815 ')),
PARTITION P6 VALUES Less THAN (to_days (' 20170816 ')),
PARTITION P7 VALUES Less THAN (to_days (' 20170817 ')),
PARTITION P8 VALUES Less THAN (to_days (' 20170818 ')),
PARTITION P9 VALUES Less THAN (to_days (' 20170819 ')),
PARTITION P10 VALUES Less THAN (to_days (' 20170820 '))
)
;

ALTER TABLE t_cabinet_electricity_hh_hive2 ADD PARTITION (PARTITION P11 VALUES less THAN (to_days (' 20170821 ')));
ALTER TABLE t_cabinet_electricity_hh_hive2 ADD PARTITION (PARTITION P12 VALUES less THAN (to_days (' 20170822 ')));
ALTER TABLE t_cabinet_electricity_hh_hive2 ADD PARTITION (PARTITION P13 VALUES less THAN (to_days (' 20170823 ')));
ALTER TABLE t_cabinet_electricity_hh_hive2 ADD PARTITION (PARTITION P14 VALUES less THAN (to_days (' 20170824 ')));
ALTER TABLE t_cabinet_electricity_hh_hive2 ADD PARTITION (PARTITION P15 VALUES less THAN (to_days (' 20170825 ')));
ALTER TABLE t_cabinet_electricity_hh_hive2 ADD PARTITION (PARTITION P16 VALUES less THAN (to_days (' 20170826 ')));
ALTER TABLE t_cabinet_electricity_hh_hive2 ADD PARTITION (PARTITION P17 VALUES less THAN (to_days (' 20170827 ')));
ALTER TABLE t_cabinet_electricity_hh_hive2 ADD PARTITION (PARTITION P18 VALUES less THAN (to_days (' 20170828 ')));
ALTER TABLE t_cabinet_electricity_hh_hive2 ADD PARTITION (PARTITION P19 VALUES less THAN (to_days (' 20170829 ')));
ALTER TABLE t_cabinet_electricity_hh_hive2 ADD PARTITION (PARTITION P20 VALUES less THAN (to_days (' 20170830 ')));
ALTER TABLE t_cabinet_electricity_hh_hive2 ADD PARTITION (PARTITION P21 VALUES less THAN (to_days (' 20170831 ')));
ALTER TABLE t_cabinet_electricity_hh_hive2 ADD PARTITION (PARTITION P22 VALUES less THAN (to_days (' 20170901 ')));

--View partition information
Select
Partition_name,table_rows
From information_schema.partitions where table_name= ' xxx ';


Problem, the date type in the Hive table Yyyy-mm-dd to the corresponding date field in MySQL, but cannot be imported successfully
In fact, look at the sqoop conversion of Java code, you can see that this is a sqoop bug.
There are similar (java.sql.Date) value inside
The conversion failed to null, so entering the field where I have a non-null constraint fails.
The reason is that the date format is indeterminate, so go straight to the problem,
It is recommended that the field directly set the temporary table is varchar, followed by processing.


Sqoop eval--connect jdbc:mysql://xx:3306/cms--username root--password xxx \
--query "TRUNCATE TABLE Xx.stage_t_xx"

Sqoop Export--connect jdbc:mysql://xxx:3306/xxx\
--username root--password xxx--table stage_t_xxe \
--hcatalog-database xxx--hcatalog-table stage_txxx

Sqoop eval--connect jdbc:mysql://xxx:3306/cms--username root--password xxx \
--query "INSERT INTO xxx.t_xxx_xx_hh_xx select * from Xxx.stage_t_xx_xx_hh_hive"

MySQL Tuning little Note