文章目录
自增长
案例思考
在某张表中,存在一个id列(整数),我们希望在添加记录的时候,该列从1开始,自动的增长,怎么处理
字段名 整形 primary key auto_increment
语法规则
insert into xxx(字段1,字段2......)values (null,'值'....);
insert into xxx(字段2......) values('值','值2'......);
insert into xxx values(null,'值',.....)
案例演示1
-- 演示自增长的使用
-- 创建表
create table t4
(id int primary key auto_increment,
email varchar(32)not null default '',
`name` varchar(32)not null default ''
);
desc t4
-- 测试自增长的使用
insert into t4
values(null,'taotao@qq.com','tao');
insert into t4
values(null,'guigui@qq.com','gui');
select * from t4;
案例演示2
insert into t4
(email,`name`) values('hong@qq.com','hong');
细节
- 一般来说自增长是和primary key 配合使用的
- 自增长也可以单独使用【但是需要配合一个unique】
- 自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用)
- 自增长默认从1开始,你也可以通过如下命令
alter table 表名 auto_increment = xxx;
- 如果你添加数据时,给自增长字段(列)指定的有值,则以指定的值为准<后面的值从上个指定值开始>
insert into values(100,'123@qq.com','123');
-- 那么下个自增长从101开始
MySQL索引
说起提高数据库性能,索引是最物美价廉的东西了,不用加内存,不用改程序,不用调sql,查询速度就可能提高百倍千倍
海量表【源码】
构建海量表8000000条数据
1、创建数据库
CREATE DATABASE `test_bai` -- 创建数据库
USE `test_bai` -- 切换对应的数据库
2.创建表
CREATE TABLE `app_user`(
`id` INT NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` VARCHAR(50) DEFAULT '' COMMENT '用户名称',
`email` VARCHAR(50) NOT NULL COMMENT '邮箱',
`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
`gender` TINYINT DEFAULT '0' COMMENT '性别(0-男 : 1-女)',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT DEFAULT '0' COMMENT '年龄',
`create_time` DATETIME DEFAULT NOW(),
`update_time` DATETIME DEFAULT NOW(),
PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT='app用户表'
3.书写新增100万条数据的函数
SET GLOBAL log_bin_trust_function_creators=TRUE; -- 创建函数一定要写这个
DELIMITER $$ -- 写函数之前必须要写,该标志
CREATE FUNCTION mock_data() -- 创建函数(方法)
RETURNS INT -- 返回类型
BEGIN -- 函数方法体开始
DECLARE num INT DEFAULT 1000000; -- 定义一个变量num为int类型。默认值为100 0000
DECLARE i INT DEFAULT 0;
WHILE i < num DO -- 循环条件
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES(CONCAT('用户',i),'2548928007qq.com',CONCAT('18',FLOOR(RAND() * ((999999999 - 100000000) + 1000000000))),FLOOR(RAND() * 2),UUID(),FLOOR(RAND() * 100));
SET i = i + 1; -- i自增
END WHILE; -- 循环结束
RETURN i;
END; -- 函数方法体结束
4.调用函数
SELECT mock_data(); -- 调用函数
select count(*) from app_user
举例说明(无索引)
举例说明(有索引)
创建索引前(ibd文件)
创建索引后(ibd文件)
索引的原理
画图详解
索引的代价
1.磁盘占用
2.对dml(update delete insert)语句的效率影响
解释2:数据从中间删除,二叉树要重新构建
思考2:在我们项目中,select多,还是update,delete,insert操作多?(实际上select已经占所有操作的百分之90)
索引的类型
- 主键索引,主键自动的为主索引(类型Primary)
- 唯一索引(UNIQUE)
- 普通索引(INDEX)
- 全文索引(FULLTEXT)[适用于MyISAM]
开发中考虑使用:全文搜索 Solr 和 ElasticSearch(ES)
举例
主键索引
create table t1(
id int primary key, -- 主键,同时也是索引,称为主键索引,查找速度非常快
name varchar(32)
)
唯一索引
carete table t1(
id int unique, -- id是唯一的,同时也是索引,称为unique索引
name varchar(32)
);
普通索引
create index id_index on emp(id)
全文索引
Mysql很少用FULLTEXT,不再介绍
使用的是:全文搜索 Solr 和 ElasicSearch(ES),后面Java高级会学,这里不再分析
索引的使用
添加索引
-- 如何选择索引
-- 1.如果某列的值,是不会重复的,则有限考虑使用unique索引,否则使用普通索引
-- 添加索引
-- 添加唯一索引
create unique index id_index on t5 (id);
-- 添加普通索引,方式1
create index id_index on t5(id);
-- 添加普通索引,方式2
alter table t5 add index id_index(id);
添加主键(索引)
-- 添加主键索引,方式1
create table t5(
id int,
`name` varchar(32)
);
alter table t5 add primary key(id);
-- 添加主键索引,方式2
create table t5(
id int primary key,
`name` varchar(32)
);
-- 添加主键索引,方式3(复合主键)
create table t5(
id int,
`name` varchar(32),
primary key(id)
);
删除索引
drop index id_index on t5; -- 按索引名称删除
删除主键索引(比较特别)
alter table t5 drop primary key
修改索引
先删除,再添加
查询索引(三种方式)
-- 查询索引,方式1
show indexes from t5;
-- 查询索引,方式2
show index from t5;
-- 查询索引,方式3
show keys from t5;
-- 查询索引,方式4(信息不详细)
desc t5;
创建索引规则
-
小结:哪些列上适合使用索引
-
较频繁的作为查询条件字段应该创建索引
select * from emp where empno = 1
-
唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
select * from emp where sex ='男‘
-
更新非常频繁的字段不适合创建索引
select * from emp where logincount = 1
-
不会出现在WHERE子句中字段不该创建索引
-