mysql—自增长和索引

本次博客带领大家学习mysql数据库中的自增长和索引。

自增长

• 自增长的基本介绍：

基本语法：
字段名 整型 primary key auto_increment

添加 自增长的字段方式：
INSERT INTO xxx(字段1，字段2...) VALUES(NULL,'值'...);
INSERT INTO xxx(字段2...) VALUES('值1','值2'...);
INSERT INTO xxx VALUES(NULL,'值1'...);
1
2
3
4
5
6
7

• 案例1：

CREATE TABLE t24
	(id INT PRIMARY KEY AUTO_INCREMENT,
	email VARCHAR(32) NOT NULL DEFAULT '',
	`name` VARCHAR(32) NOT NULL DEFAULT '');
	
DESC t24;

INSERT INTO t24 VALUES(NULL,'tom@qq.com','tom');

INSERT INTO t24(email,`name`) VALUES('ld@qq.com','ld');
1
2
3
4
5
6
7
8
9
10

• 自增长使用细节

  1. 一般来说自增长是和primary key 配合使用的。
  2. 自增长也可以单独使用[但是需要配合一个unique]。
  3. 自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用)。
  4. 自增长默认从1开始，你也可以通过如下命令修改 alter table 表名 auto_increment = 新的开始值。
  5. 如果你添加数据时，给自增长字段(列)指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则来添加数据。

• 案例2：

CREATE TABLE t25
	(id INT PRIMARY KEY AUTO_INCREMENT,
	email VARCHAR(32) NOT NULL DEFAULT '',
	`name` VARCHAR(32) NOT NULL DEFAULT '');
	
ALTER TABLE t25 AUTO_INCREMENT = 100;

INSERT INTO t25 VALUES(NULL,'tom@qq.com','tom');

INSERT INTO t25 VALUES(666,'ld@qq.com','ld');
1
2
3
4
5
6
7
8
9
10

索引

• 索引的原理：

  • 没有索引为什么会慢？ 因为全表扫描。
  • 使用索引为什么会快？形成一个索引的数据结构，比如二叉树。

• 索引的代价：

  1. 磁盘占用。
  2. 对dml(update、delete、insert)语句的效率影响。

• 索引的类型：

  1. 主键索引，主键自动的为主键索引(类型Primary key)

  2. 唯一索引(UNIQUE)

  3. 普通索引(INDEX)

  4. 全文索引(FULLTEXT)[适用于MyISAM]

     一般开发，不适用mysql自带的全文索引，而是开发中考虑使用：全文搜索Solr 和 ElasticSearch(ES)

• 索引的使用：

  1. 添加索引：

  create [unique] index index_name on tbl_name (col_name[(length)] [ASC | DESC],...)
  
  alter table table_name ADD index [index_name ] (index_col_name,...)
  
  CREATE UNIQUE INDEX id_idex ON t26(id);
  CREATE INDEX id_index ON t26 (id);
  ALTER TABLE t26 ADD INDEX id_index (id);
  1
  2
  3
  4
  5
  6
  7

  2. 添加主键(索引)：

  ALTER TABLE 表名 ADD PRIMARY KEY(列名,...);
  
  ALTER TABLE t26 ADD PRIMARY KEY (id);
  1
  2
  3

  3. 删除索引：

  drop index index_name on tabl_name;
  
  alter table table_name drop index index_name;
  
  DROP INDEX id_index ON t26;
  ALTER TABLE t26 DROP PRIMARY KEY; 
  1
  2
  3
  4
  5
  6

  4. 查询索引：

  SHOW INDEX FROM t26;
  SHOW INDEXES FROM t26;
  SHOW KEYS FROM t26;
  desc t26;
  1
  2
  3
  4

• 小结：

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

  select * from emp where empno=1;
  1

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

  select * from emp where sex='男';
  1

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

  select * from emp where logincount=1;
  1

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