MySQL攻略 -MySQL索引详解、附赠“自增长“字段设置

自增长

案例思考

在某张表中，存在一个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');

细节

1. 一般来说自增长是和primary key 配合使用的
2. 自增长也可以单独使用【但是需要配合一个unique】
3. 自增长修饰的字段为整数型的（虽然小数也可以但是非常非常少这样使用）
4. 自增长默认从1开始，你也可以通过如下命令

alter table 表名 auto_increment = xxx;

5. 如果你添加数据时，给自增长字段（列）指定的有值，则以指定的值为准<后面的值从上个指定值开始>

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）

索引的类型

1. 主键索引，主键自动的为主索引（类型Primary）
2. 唯一索引（UNIQUE）
3. 普通索引（INDEX）
4. 全文索引（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;

创建索引规则

• 小结:哪些列上适合使用索引

  1. 较频繁的作为查询条件字段应该创建索引

     select * from emp where empno = 1

  2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

     select * from emp where sex ='男‘

  3. 更新非常频繁的字段不适合创建索引

     select * from emp where logincount = 1

  4. 不会出现在WHERE子句中字段不该创建索引