1.数据导出并压缩
mysqldump db_name -u db_user -p'passwd' | gzip -c | cat > db_name.sql.gz
tips: gzip对文本的压缩比非常大，可以节省大量的磁盘空间和传输时间
2.创建用户并赋予权限
grant all privileges on *.* to jack@'localhost' identified by "jack" with grant option;
tips: * . * 代表所有
3.in会走索引吗？
答案是肯定的
select * from user where profile_id in (45,89,199,3333)
image.png
可以看到的是:index range scan 索引内搜索，影响行数4行,而总条数超过100万条
4.建立索引的方法
CREATE INDEX `idx_user_profile_id` ON `dbtest`.`user` (profile_id) COMMENT '增加索引' ALGORITHM INPLACE LOCK DEFAULT
5.案例
CREATE TABLE `user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`tel` VARCHAR(20) NOT NULL,
`profile_id` INT(11) NOT NULL DEFAULT '0',
`username` VARCHAR(20) NOT NULL,
`truename` VARCHAR(10) NOT NULL,
`job` VARCHAR(100) NOT NULL DEFAULT '',
`company` VARCHAR(100) NOT NULL DEFAULT '',
`password` VARCHAR(50) NOT NULL,
`email` VARCHAR(30) NOT NULL,
`id_no` VARCHAR(20) NOT NULL,
`city_id` VARCHAR(10) NOT NULL DEFAULT '',
`address` VARCHAR(50) NOT NULL,
`summary` VARCHAR(2000) NOT NULL,
`gender` TINYINT(4) NOT NULL,
`age` TINYINT(4) NOT NULL,
`site` VARCHAR(100) NOT NULL DEFAULT '',
`uuid` VARCHAR(50) NOT NULL,
`created_at` DATETIME NOT NULL,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)
COMMENT='测试用户表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
- 数据行数 1390119
- 全表扫描结果
select id,tel from user where tel = '13383430001';
+----+-------------+
| id | tel |
+----+-------------+
| 52 | 13383430001 |
+----+-------------+
1 row in set
Time: 29.580s
- 添加索引耗时
ALTER TABLE user
-> ADD INDEX tel (tel);
Query OK, 0 rows affected
Time: 45.666s
- 添加索引后结果
select id,tel from user where tel = '13383430001';
+----+-------------+
| id | tel |
+----+-------------+
| 52 | 13383430001 |
+----+-------------+
1 row in set
Time: 0.009s
