Mysql知识记录

Mysql5.7 yum安装

下载rpm包
wget http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm
安装rpm包
rpm -ivh mysql57-community-release-el7-8.noarch.rpm
yum安装
yum install mysql-server
service mysql start
grep "password" /var/log/mysqld.log 
alter user 'root'@'localhost' identified by '***';
update mysql.user set host='%' where host='localhost';
grant all on *.* to root@'%' identified by '***';
flush privileges;

批量导入数据

load data infile '/data/import200.txt' into table password fields terminated by ':' lines terminated by '\n' (password, sha);

跳过指定行数，场景：批量导入报错，再次导入跳过之前入库的数据

load data infile '/data/import200.txt' into table password fields terminated by ':' lines terminated by '\n' ignore 100 lines  (password, sha);

忽略错误

load data infile '/data/import200.txt' ignore into table password fields terminated by ':' lines terminated by '\n' (password, sha);

加快批量导入数据的速度

导入前ALTER TABLE...DISABLE KEYS

导入后ALTER TABLE...ENABLE KEYS

创建索引

CREATE INDEX part_of_sha1 ON password (sha1(10));

修改innodb配置，提高导入数据性能

innodb_flush_log_at_trx_commit=0或2
innodb_autoextend_increment=128
innodb_log_buffer_size=16
innodb_log_file_size=128

查看数据大小

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='alltext' and table_name='password';

SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2), 'MB') as data FROM TABLES WHERE TABLE_SCHEMA='alltext';

在一个耗时的查询语句中增加超时设置

select /*+ max_execution_time(1000)*/ * from tablea where email like "%dd_ii.com"

SET STATEMENT max_execution_time=1000 for SELECT * FROM `tablea` WHERE `email` LIKE '%@bb-ii.com' ORDER BY `id` LIMIT 20