mysql 积累

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