您的位置:新葡亰496net > 网络数据库 > MySQL主键设计,mysql基本操作

MySQL主键设计,mysql基本操作

发布时间:2019-11-30 06:23编辑:网络数据库浏览(88)

    开始不设置主键

    前言

    [TOC]
    在项目过程中遇到一个看似极为基础的问题,但是在深入思考后还是引出了不少问题,觉得有必要把这一学习过程进行记录。

    mysql基本操作,

    数据库及表的操作

    增:

    创建数据库:create database mpc [charset utf8];(设置数据库编码)

    创建数据表:create table [if not exists] 表名(字段1,字段2…[索引1,索引2…,约束1,约束2…])[表选项1,表选项2…];

    字段设定形式:
    字段名 类型 [字段属性1,字段属性2…]
    类型:int ,tinyint, float, double, char, varchar, text, datetime, date, time, timestamp…
    字段属性:
    auto_increment:整数自增(一定是主键)
    primary key:设定主键,且是唯一的,一个表里面只能设置一个,不能为空(null)
    unique key:设定唯一索引,一个数据表里面可以设置多个唯一索引,这个字段中可以多个值为空(null)
    not null:不能为空
    default:设定默认值
    comment ‘字段说明文字’:字段的说明;

    create table if not exists createtest(
    id int auto_increment primary key,
    name varchar(40) not null unique key,
    password varchar(40) not null,
    age tinyint default 18,
    email varchar(40) comment '电子邮件'
    );
    

    索引:对数据库内部的某个表所有数据进行预先的某种排序;
    普通索引:key(字段名);
    唯一索引:unique key(字段名);
    主键索引: primary key(字段名);
    联合主键:primary key(多个字段名用逗号隔开);
    全文索引:fulltext(字段名);
    外键索引:foreign key(字段名) references 表名(字段名);(一个表的字段是另一个表的某个字段(一定得是主键),如果输入的值不再外面表的字段里,则不能录入)

    create table tab1_key(
    id int auto_increment,
    name varchar(20) not null,
    emai varchar(50) not null,
    age int not null default 18,
    grades1 int,
    primary key(id),
    unique key(name),
    foreign key(grades1) references tab2_key(grades2)
    );
    create table tab2_key(
    stu_id int not null,
    teacher varchar(20) not null,
    floor int not null,
    grades2 int auto_increment primary key
    );
    

    约束:要求数据满足什么条件的一种规定
    唯一约束:unique key(字段名);
    主键约束: primary key(字段名);
    外键约束:foreign key(字段名) references 表名(字段名);(一个表的字段是另一个表的某个字段(一定得是主键),如果输入的值不再外面表的字段里,则不能录入)
    非空约束:not null;设定一个字段时不能为空值
    默认约束:default XX值,设定一个字段的默认值为xx;
    检查约束:check(某种判断语句)

    create table tab_check(
    age tinyint,
    check (age>0 and age<100)
    );-- 但是mysql并不能生效,语法能够通过
    

    表选项:创建一个表的时候,对该表的整体设定:
    charset=要使用的字符编码;
    engine=要使用的存储引擎(也叫表类型);(默认是InnoDB)

    关系数据库表是用于存储和组织信息的数据结构,可以将表理解为由行和列组成的表格,类似于Excel的电子表格的形式。有的表简单,有的表复杂,有的表根本不用来存储任何长期的数据,有的表读取时非常快,但是插入数据时去很差;而我们在实际开发过程中,就可能需要各种各样的表,不同的表,就意味着存储不同类型的数据,数据的处理上也会存在着差异,那么。对于MySQL来说,它提供了很多种类型的存储引擎,我们可以根据对数据处理的需求,选择不同的存储引擎,从而最大限度的利用MySQL强大的功能。这篇博文将总结和分析各个引擎的特点,以及适用场合,并不会纠结于更深层次的东西。我的学习方法是先学会用,懂得怎么用,再去知道到底是如何能用的。下面就对MySQL支持的存储引擎进行简单的介绍。

    图片 1

    auto_increment=设定当前表的自增长字段的初始值,默认是1
    comment=‘该表的一些说明文字’create table 表2 like 表1;

    按照一个表的结构创建一个新表:created table 表1 like 表2;

    在已有的表结构中插入相似表结构表中的所有数据:insert into 表1 select * from 表2;

    删:

    删除数据库:drop database [if exists] 数据库名;(if exists如果不存在不会报错)

    删除数据表:drop table [if exists] 表名;(if exists如果不存在不会报错)

    改:

    修改数据表:
    添加字段:alter table 表名 add [column] 新字段名 字段类型 [字段属性列表];
    修改字段:alter table 表名 change [column] 旧字段名 新字段名 字段类型 [字段属性列表];
    删除字段:alter table 表名 drop [column] 字段名;
    添加普通索引:alter table 表名 add key(字段名1[,字段名2,字段名3…]);
    添加唯一索引(约束):alter table 表名 add unique key(字段名1[,字段名2,字段名3…]);
    添加主键索引(约束):alter table 表名 add primary key(字段名1[,字段名2,字段名3…]);

    图片 2

     

    数据的操作

     

    增:

    形式1、insert [into] 表名[(字段名1,字段名2,...)] values(值表达式1,值表达式2,...),(...),...;

      最常用的插入语句,可以一次性插入多行数据,用逗号隔开;其中的值表达式可以是一个“直接值”或“函数调用结果”;如果对应字段是字符或时间类型,值应该使用单引号;

    形式2、replace [into]表名[(字段名1,字段名2,...)] values(值表达式1,值表达式2,...),(...),...;

      用法和形式1相同,唯一区别是如果插入语句的主键或唯一键相同,则会直接替换已经存在的数据;

    形式3、insert [into] 表名[(字段名1,字段名2,...)] select 字段名1,字段名2,...from 其他表名;

      将select语句查询的结果数据都插入到指定的表中,需要注意select语句查询出来的对应字段;

    形式4、insert [into] 表名 set 字段名1=值表达式1,字段名2=值表达式2,...;

    形式5、load data infile ‘文件名(绝对路径)’ into table 表名;

      load data:适用于载入如下图所示的“结构整齐的文本数据”(要注意有的编辑器会给文档头部添加BOM字符来占位,以致于不能够载入)

    图片 3

    图片 4

    删:

    delete from 表名 [where 条件] [order by 排序字段] [limit 限定行数];

      删除数据依然是以“行”为单位进行,通常删除数据都应该带上where条件否则就会删除所有数据(极度危险),order by 和limit 是为了筛选一些特定的数据,在实际应用中用得较少;

    改:

    update 表名 set 字段1=值1,字段2=值2,...[where 条件] [order by 排序字段] [limit 限定行数];

      通常update语句也都带上where条件,否则就是对所有数据进行修改(极度危险),order by 和limit 是为了筛选一些特定的数据,在实际应用中用得较少;

    查:

    select [all | distinct] 字段或表达式列表 [from子句] [where 子句]
    [group by 子句] [having 子句] [order by 子句] [limit 子句]

    字段:来源于表,表达式:类似于8 3,now()等

    图片 5 图片 6

    all默认值:显示所有数据;
    distinct:显示所有非重复的数据;
    as:另起别名。select id as 序号 from tab1;

    where 类似于其他语言的if条件判断语句,有true 和false 表示永真和永假

       where子句是对from子句中的“数据源”中的数据进行筛选的条件设定,筛选的机制是“一行一行”进行判断,其作用和其他语言的 if 语句相似;where子句通常都需要使用各种运算符:算术运算符( ,-,*,/,%),比较运算符(>,<,>=,<=,[=,<>]国际标准的等于和不等于,==,!=),逻辑运算符(and,or,not)。

      select * from test where id>2 and sex!='man'; id大于2且sex不等于man;

      select * from test where id>2 or sex='man'; id大于2或者sex等于man;

      select * from test where not(sex='man'); sex不等于man;

    group by子句是用于对已经查询出的数据进行分组

      group by 字段1[desc|asc],字段2[desc|asc],...; 分组的结果可以指定排序方式默认是asc(正序),通常分组使用一个字段,2个以上的字段很少

      分组过后的数据就剩下一个一个组了,所以尽量不输出所有字段,有的字段不能针对一个组(比如带有姓名,年龄,性别字段的数据表以性别字段分组过后,姓名和年龄字段就没有意义了),所以分组过后一般有用的信息就是总量[count(*)得出该组共有多少数据],和原来数据中的数据类型字段的聚合信息[max(age)得出最大年龄,min(age)得出最小年龄,avg(age)得出平均年龄,sum(age)得出所有人年龄的总和]。

      select 班级,count(*) as 人数, max(体育成绩) as 体育成绩最高分,min(体育成绩) as 体育成绩最低分,avg(体育成绩) as 体育成绩平均分,sum(体育成绩) as 所有人体育成绩总和 from test group by 班级;

    having 子句的作用和where完全一样,但是是对已有的分组数据进行筛选,所以having子句一定在group by子句使用后才能使用

      select 班级id,count(*) as 人数, max(体育成绩) as 体育成绩最高分,min(体育成绩) as 体育成绩最低分,avg(体育成绩) as 体育成绩平均分,sum(体育成绩) as 所有人体育成绩总和 from test group by 班级id having 体育成绩平均分>60;

    order by 子句是将前面已经取得的数据以设定的字段进行排序后输出(desc倒序,asc顺序,默认是asc)

      order by 字段1 [asc|desc], 字段2 [asc|desc], ...; 多个字段的排序都是建立在上一个排序的基础上如果还有“相等值”再继续排序;

      select * from test order by age;

      select * from test order by age, weight;

    limit子句用于将已经得到的数据取出指定的几行出来

      limit 起始行号, 取出的行数; 起始行号从0开始计时,起始行号可以省略,省略后默认从第0行开始取出多上行;

      select * from test where order by age desc limit 0,1;

    在select语句的形式上很多的子句都可以省略,但是如果出现他们的顺序不能被打乱,where子句依赖from子句,having子句依赖group by子句,select 的字段依赖于from子句,有依赖关系的不能在所依赖的对象之前出现也不能凭空出现;用from语句查询出数据,使用where语句将查询的数据进行按需筛选,使用group by语句将筛选过的数据进行分组,使用having子句将分组的数据进行筛选,使用order by语句将筛选后的数据进行排序,用limite x,y语句将排序后的特定行输出。

      select classid,avg(age) as age from test where sex='man' group by classid order by desc limit 0,1;

    数据库及表的操作 增: 创建数据库:create database mpc [charset utf8];(设置数据库编码) 创建数据表: create table [if not exists...

    在使用InnoDB存储引擎时,如果没有特别的需要,请永远使用一个与业务无关的自增字段作为主键,除非高并发写入操作可能需要衡量自增主键或有业务安全性要求,后面会讲。

    表的设计如下:

    图片 7

    图片 8

    本文主要介绍了关于MySQL主键为0与主键自排约束的关系,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧。

    MySQL主键设计原则

    • MySQL主键应当是对用户没有意义的。
    • MySQL主键应该是单列的,以便提高连接和筛选操作的效率
    • 永远也不要更新MySQL主键
    • MySQL主键不应包含动态变化的数据,如时间戳、创建时间列、修改时间列等
    • MySQL主键应当有计算机自动生成。

    经常看到有帖子或博客讨论主键选择问题,有人建议使用业务无关的自增主键,有人觉得没有必要,完全可以使用如学号或身份证号这种唯一字段作为主键。不论支持哪种论点,大多数论据都是业务层面的。如果从数据库索引优化角度看,使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。下面从各个方面来讨论一下。

    如果id的位置有好几个0的话:设置主键并且自动排序时,0会从1开始递增;

     图片 9

    图片 10

    开始不设置主键表的设计如下:

    主键设计的常用方案

    一、首先不管主键策略是什么,这两点都是必须遵守的。

    Insert 进去 id = 0的数据,数据会从实际的行数开始增加,和从0变化不一样;

     图片 11

    图片 12

    图片 13

    如果id的位置有好几个0的话:设置主键并且自动排序时,0会从1开始递增;

    自增ID

    优点

    1、数据库自动编号,速度快,而且是增量增长,聚集型主键按顺序存放,对于检索非常有利。

    2、 数字型,占用空间小,易排序,在程序中传递方便。

    缺点
    1、不支持水平分片架构,水平分片的设计当中,这种方法显然不能保证全局唯一。
    2、表锁

    在MySQL5.1.22之前,InnoDB自增值是通过其本身的自增长计数器来获取值,该实现方式是通过表锁机制来完成的(AUTO-INC LOCKING)。锁不是在每次事务完成后释放,而是在完成对自增长值插入的SQL语句后释放,要等待其释放才能进行后续操作。比如说当表里有一个auto_increment字段的时候,innoDB会在内存里保存一个计数器用来记录auto_increment的值,当插入一个新行数据时,就会用一个表锁来锁住这个计数器,直到插入结束。如果大量的并发插入,表锁会引起SQL堵塞。
    在5.1.22之后,InnoDB为了解决自增主键锁表的问题,引入了参数innodb_autoinc_lock_mode:

    • 0:通过表锁的方式进行,也就是所有类型的insert都用AUTO-inc locking(表锁机制)。
    • 1:默认值,对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作,对于bulk insert 则还是使用表锁的方式进行。
    • 2:对所有的insert-like 自增长值的产生使用互斥量机制完成,性能最高,并发插入可能导致自增值不连续,可能会导致Statement 的 Replication 出现不一致,使用该模式,需要用 Row Replication的模式。

    3、自增主键不连续

    Create Table: CREATE TABLE `tmp_auto_inc` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `talkid` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=gbk
    1 row in set (0.00 sec) 
    

    当插入10条记录的时候,因为AUTO_INCREMENT=16,所以下次再插入的时候,主键就会不连续。

    1. 主键不可修改

    现在主键是没有0的,如果把某个id改成0的话,0不会变!直接会进行排序;

     图片 14

    图片 15

    Insert 进去 id = 0的数据,数据会从实际的行数开始增加,和从0变化不一样;

    UUID

    优点
    1、全局唯一性、安全性、可移植性。

    2、能够保证独立性,程序可以在不同的数据库间迁移,效果不受影响。

    3、保证生成的ID不仅是表独立的,而且是库独立的,在你切分数据库的时候尤为重要

    缺点
    1、针对InnoDB引擎会徒增IO压力,InnoDB为聚集主键类型的引擎,数据会按照主键进行排序,由于UUID的无序性,InnoDB会产生巨大的IO压力。InnoDB主键索引和数据存储位置相关(簇类索引),uuid 主键可能会引起数据位置频繁变动,严重影响性能。
    2、UUID长度过长,一个UUID占用128个比特(16个字节)。主键索引KeyLength长度过大,而影响能够基于内存的索引记录数量,进而影响基于内存的索引命中率,而基于硬盘进行索引查询性能很差。严重影响数据库服务器整体的性能表现。

    对于数据库来说,主键其实是可以修改的,只要不和其他主键冲突就可以。但是,对于应用来说,如果一条记录要修改主键,那就会出大问题。

    再insert一个id=0的看看,居然还是跟刚才一样直接跟行数相关!

     图片 16

    图片 17

    现在主键是没有0的,如果把某个id改成0的话,0不会变!直接会进行排序;

    自定义序列表

    所谓自定义序列表,就是在库中建一张用于生成序列的表来存储序列信息,序列生成的策略通过程序层面来实现。如下所示,构建一张序列表:

    CREATE TABLE `sequence` (
        `name` varchar(50) NOT NULL,
        `id` bigint(20) unsigned NOT NULL DEFAULT '0',
        PRIMARY KEY (`name`)
    ) ENGINE=InnoDB;
    

    注意区别,id字段不是自增的,也不是主键。在使用前,我们需要先插入一些初始化数据:

    INSERT INTO `sequence` (`name`) VALUES 
    ('users'), ('photos'), ('albums'), ('comments');
    

    接下来,我们可以通过执行下面的SQL语句来获得新的照片ID:

    UPDATE `sequence` SET `id` = LAST_INSERT_ID(`id`   1) WHERE `name` = 'photos';
    SELECT LAST_INSERT_ID();
    

    我们执行了一个更新操作,将id字段增加1,并将增加后的值传递到LAST_INSERT_ID函数, 从而指定了LAST_INSERT_MySQL主键设计,mysql基本操作。ID的返回值。

    实际上,我们不一定需要预先指定序列的名字。如果我们现在需要一种新的序列,我们可以直接执行下面的SQL语句:

    INSERT INTO `sequence` (`name`) VALUES('new_business') ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`   1);
    SELECT LAST_INSERT_ID();
    

    这种方案的问题在于序列生成的逻辑脱离了数据库层,由应用层负责,增加了开发复杂度。当然,其实可以用spring来解决这一问题,因为在spring JDBC中已经对这种序列生成逻辑进行了简单的封装。
    我们可以看一下spring的相关源代码:MySQLMaxValueIncrementer.
    图片 18

        @Override
        protected synchronized long getNextKey() throws DataAccessException {
            if (this.maxId == this.nextId) {
                /*
                * Need to use straight JDBC code because we need to make sure that the insert and select
                * are performed on the same connection (otherwise we can't be sure that last_insert_id()
                * returned the correct value)
                */
                Connection con = DataSourceUtils.getConnection(getDataSource());
                Statement stmt = null;
                try {
                    stmt = con.createStatement();
                    DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
                    // Increment the sequence column...
                    String columnName = getColumnName();
                    stmt.executeUpdate("update "  getIncrementerName()   " set "   columnName  
                            " = last_insert_id("   columnName   "   "   getCacheSize()   ")");
                    // Retrieve the new max of the sequence column...
                    ResultSet rs = stmt.executeQuery(VALUE_SQL);
                    try {
                        if (!rs.next()) {
                            throw new DataAccessResourceFailureException("last_insert_id() failed after executing an update");
                        }
                        this.maxId = rs.getLong(1);
                    }
                    finally {
                        JdbcUtils.closeResultSet(rs);
                    }
                    this.nextId = this.maxId - getCacheSize()   1;
                }
                catch (SQLException ex) {
                    throw new DataAccessResourceFailureException("Could not obtain last_insert_id()", ex);
                }
                finally {
                    JdbcUtils.closeStatement(stmt);
                    DataSourceUtils.releaseConnection(con, getDataSource());
                }
            }
            else {
                this.nextId  ;
            }
            return this.nextId;
        }
    

    spring的实现也就是通过update语句对incrementerName表里的columnName 列进行递增,并通过mysql的last_insert_id()返回最近生成的值。并保证了事务性及方法的并发支持。只是这个实现有些过于简单,比如:一个表对应一个序列的做法在实际应用开发中显得过于零碎,所以在实际应用中需要对其实现进行修改,实现一条记录对应一个序列的策略。另外对水平分片的支持并不在这一实现考虑范围内。同时,这种做法依然无法回避表锁的机制,所以这里通过CacheSize()的做法,实现了一次申请并缓存在内存中,以减少表锁的发生频率。

    因为主键的第二个作用是让其他表的外键引用自己,从而实现关系结构。一旦某个表的主键发生了变化,就会导致所有引用了该表的数据必须全部修改外键。很多Web应用的数据库并不是强约束(仅仅引用主键但并没有设置外键约束),修改主键会导致数据完整性直接被破坏。

    再重置一下自动排序,看看这个0会不会有变化,

    再insert一个id=0的看看,居然还是跟刚才一样直接跟行数相关!

    如何解决水平分片的需求

    2. 业务字段不可用于主键

    先取消自排;

    图片 19

    图片 20

    再重置一下自动排序,看看这个0会不会有变化,先取消自排;

    UUID

    由于UUID出现重复的概率基本可以忽略,所以对分片是天生支持的。

    所有涉及到业务的字段,无论它看上去是否唯一,都决不能用作主键。例如,用户表的Email字段是唯一的,但是,如果用它作主键,就会导致其他表到处引用Email字段,从而泄露用户信息。

    再加上自排,果然跟想的一样,0要变成1了,错误提示说的是主键有重复的1所以不让自排,

     图片 21

    再加上自排,果然跟想的一样,0要变成1了,错误提示说的是主键有重复的1所以不让自排,

    独立的序列库

    单独建立一个库用来生成ID,在Shard中的每张表在这个ID库中都有一个对应的表,而这个对应的表只有一个字段, 这个字段是自增的。当我们需要插入新的数据,我们首先在ID库中的相应表中插入一条记录,以此得到一个新的ID, 然后将这个ID作为插入到Shard中的数据的主键。这个方法的缺点就是需要额外的插入操作,如果ID库变的很大, 性能也会随之降低。所以一定要保证ID库的数据集不要太大,一个办法是定期清理前面的记录

    此外,修改Email实际上是一个业务操作,这个操作就直接违反了上一条原则。

    修改之后,果真可以了,0变成1了,

     图片 22

    图片 23

    修改之后,果真可以了,0变成1了,

    复合标识符

    这种做法是通过联合主键的策略,即通过两个字段来生成一个唯一标识,前半部分是分片标识符,后半部分是本地生成的标识符(比如使用AUTO_INCREMENT生成)

    那么,主键应该使用哪个字段呢?

           我觉得也就这几种情况吧,无符号的情况应该没什么区别,还有什么没有考虑的希望大家给我留言,可以告诉我你是怎么想的,我也很想知道,现在抛砖引玉我把我的总结和想法写一下:

    小结

    带分库策略的自定义序列表

    这种做法可以基于上面提到的自定义序列表的方法的基础上,做一些技巧性的调整。即如下:

    UPDATE `sequence` SET `id` = LAST_INSERT_ID(`id`   1) WHERE `name` = 'photos';
    SELECT LAST_INSERT_ID();
    

    这里的id初始值设定上要求不同的分片取不同的值,且必须连续。同时将每次递增的步长设定为服务器数目。
    比如有3台机器,那么我们只要将初始值分别设置为1,2,3. 然后执行下面的语句即可:

    UPDATE `sequence` SET `id` = LAST_INSERT_ID(`id`   3) WHERE `name` = 'photos';
    SELECT LAST_INSERT_ID();
    

    这就可以解决主键生成冲突的问题。但是如果在运行一段时间后要进行动态扩充分片数的时候,需要对序列初始值做一次调整,以确保其连续性,否则依然可能存在冲突的可能。当然这些逻辑可以封装在数据访问层的代码中。

    主键必须使用单独的,完全没有业务含义的字段,也就是主键本身除了唯一标识和不可修改这两个责任外,主键没有任何业务含义。

      对我来说,0在数据库里很特殊。

    我觉得也就这几种情况吧,无符号的情况应该没什么区别,还有什么没有考虑的希望大家给我留言,可以告诉我你是怎么想的,我也很想知道,现在抛砖引玉我把我的总结和想法写一下:

    主键的必要性

    表中每一行都应该有可以唯一标识自己的一列(或一组列)。虽然并不总是都需要主键,但大多数数据库设计人员都应保证他们创建的每个表有一个主键,以便于以后数据操纵和管理。其实即使你不建主键,MySQL(InnoDB引擎)也会自己建立一个隐藏6字节的ROWID作为主键列,详细可以参见[这里]

    因为,InnoDB引擎使用聚集索引,数据记录本身被存于主索引(一颗B Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL 会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)
    图片 24

    所以在使用innoDB表时要避免随机的(不连续且值的分布范围非常大)聚簇索引,特别是针对I/O密集型的应用。例如:从性能角度考虑,使用UUID的方案就会导致聚簇索引的插入变得完全随机。

    类似的,看上去唯一的用户名、身份证号等,也不能用作主键。对这些唯一字段,应该加上unique索引约束。

      使用limit查看指定范围数据的时候这时候表就会是从0开始往下排的顺序,但是insert添加一行数据的时候反而是跟行数有关系,这时候又是按照从1开始往下排的顺序。如果使用主键自排约束以前表里有0,再设置完主键自排以后所有的0又不会根据行数,而是直接按照自上而下的顺序从1开始排。如果把表中的某个主键的数改成0,那直接就会进行排序放到正数前面,也就是说主键自排是允许有0存在的,那为什么本身存在的0要去修改成从1开始的递增序列呢?哪怕没加主键自排以前只有一个0,加了主键自排以后还是会变成1。

    对我来说,0在数据库里很特殊。

    主键的数据类型选择

    关于主键的类型选择上最常见的争论是用整型还是字符型的问题,关于这个问题《高性能MySQL》一书中有明确论断:
    整数通常是标识列的最好选择,因为它很快且可以使用AUTO_INCREAMENT,如果可能,应该避免使用字符串类型作为标识列,因为很消耗空间,且通常比数字类型慢。

    如果是使用MyISAM,则就更不能用字符型,因为MyISAM默认会对字符型采用压缩引擎,从而导致查询变得非常慢。
    参考:
    1、
    2、
    3、
    4、《高性能MySQL》
    5、《高可用MySQL》

    二、主键应该用什么类型?

      开始有0,增加主键自排约束,0依次变为1,2,3,4.......

    使用limit查看指定范围数据的时候这时候表就会是从0开始往下排的顺序,但是insert添加一行数据的时候反而是跟行数有关系,这时候又是按照从1开始往下排的顺序。如果使用主键自排约束以前表里有0,再设置完主键自排以后所有的0又不会根据行数,而是直接按照自上而下的顺序从1开始排。如果把表中的某个主键的数改成0,那直接就会进行排序放到正数前面,也就是说主键自排是允许有0存在的,那为什么本身存在的0要去修改成从1开始的递增序列呢?哪怕没加主键自排以前只有一个0,加了主键自排以后还是会变成1。

    上面说了,不考虑业务,从数据库索引优化角度看,使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。

      开始没0,增加主键自排约束,新添加的主键是0的行会根据行数自行变化,注意这里是新添加的行,使用的是insert。

    开始有0,增加主键自排约束,0依次变为1,2,3,4.......

    下面先简单说说MySQL索引实现。在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

      开始没0,把某个主键的数修改成0,这个0会直接在排好序了再在表里显示出来。

    开始没0,增加主键自排约束,新添加的主键是0的行会根据行数自行变化,注意这里是新添加的行,使用的是insert。

    2.1 MyISAM存储引擎

    说得简单一点就是,增加主键自排约束后:

    开始没0,把某个主键的数修改成0,这个0会直接在排好序了再在表里显示出来。

    MyISAM引擎使用B Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图:

       主键的值:修改成的0,可以存在,就是排个序。

    说得简单一点就是,增加主键自排约束后:

    图片 25

            新添加的0,不允许存在,要根据行号改变。

    主键的值:修改成的0,可以存在,就是排个序。

    这里设表一共有三列,假设我们以Col1为主键,则上图是一个MyISAM表的主索引(Primary key)示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引,则此索引的结构如下图所示:

            本身存在的0,不允许存在,要从1开始递增变化。

    新添加的0,不允许存在,要根据行号改变。

    图片 26

      回去想了想,问了问人,终于搞清楚了为什么会出现这种情况,解释这种情况的原因我写在了下一篇随笔:

    本身存在的0,不允许存在,要从1开始递增变化。

    同样也是一颗B Tree,data域保存数据记录的地址。因此,MyISAM中索引检索的算法为首先按照B Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。

    总结

    MyISAM的索引方式也叫做“非聚集”的,之所以这么称呼是为了与InnoDB的聚集索引区分。

    以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对脚本之家的支持。

    2.2 InnoDB存储引擎

    虽然InnoDB也使用B Tree作为索引结构,但具体实现方式却与MyISAM截然不同。

    第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。

    图片 27

    是InnoDB主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

    第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说,InnoDB的所有辅助索引都引用主键作为data域。例如,下图为定义在Col3上的一个辅助索引:

    图片 28

    这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

    了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B Tree,非单调的主键会造成在插入新记录时数据文件为了维持B Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。

    2.3 InnoDB自增主键

    上文讨论过InnoDB的索引实现,InnoDB使用聚集索引,数据记录本身被存于主索引(一颗B Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)。

    如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如下图所示:

    图片 29

    这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。

    如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置:

    图片 30

    此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

    因此,只要可以,请尽量在InnoDB上采用自增字段做主键。

    三、主键自增带来的劣势是什么?

    3.1 自增锁

    对于高并发工作负载,在InnoDB中按主键顺序插入可能会造成明显的争用。主键上界会成为”热点”,因为所有的插入都发生在这里,所以并发插入可能导致间隙锁竞争。另一个热点可能是AUTO_INCREMENT锁机制:如果遇到这个问题,则可能需要考虑重新设计表或者应用,或者更改innodb_autoinc_lock_mode配置。

    自增长在数据库中是非常常见的一种属性,也是很多DBA或开发人员首选的主键方式。在InnoDB存储引擎的内存结构中,对每个含有自增长值的表都有一个自增长计数器。当对含有自增长的计数器的表进行插入操作时,这个计数器会被初始化,执行如下的语句来得到计数器的值:

    1select max(auto_inc_col) from t for update;

    插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称为AUTO-INC Locking。这种锁其实是采用一种特殊的表锁机制,为了提高插入的性能,锁不是在一个事务完成后才释放,而是在完成对自增长值插入的SQL语句后立即释放。

    虽然AUTO-INC Locking从一定程度上提高了并发插入的效率,但还是存在一些性能上的问题。首先,对于有自增长值的列的并发插入性能较差,事务必须等待前一个插入的完成,虽然不用等待事务的完成。其次,对于INSERT….SELECT的大数据的插入会影响插入的性能,因为另一个事务中的插入会被阻塞。

    从MySQL 5.1.22版本开始,InnoDB存储引擎中提供了一种轻量级互斥量的自增长实现机制,这种机制大大提高了自增长值插入的性能。并且从该版本开始,InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式,该参数的默认值为1。在继续讨论新的自增长实现方式之前,需要对自增长的插入进行分类。如下说明:

    insert-like:指所有的插入语句,如INSERT、REPLACE、INSERT…SELECT,REPLACE…SELECT、LOAD DATA等。

    simple inserts:指能在插入前就确定插入行数的语句,这些语句包括INSERT、REPLACE等。需要注意的是:simple inserts不包含INSERT…ON DUPLICATE KEY UPDATE这类SQL语句。

    bulk inserts:指在插入前不能确定得到插入行数的语句,如INSERT…SELECT,REPLACE…SELECT,LOAD DATA。

    mixed-mode inserts:指插入中有一部分的值是自增长的,有一部分是确定的。入INSERT INTO t1(c1,c2) VALUES(1,’a’),(2,’a’),(3,’a’);也可以是指INSERT…ON DUPLICATE KEY UPDATE这类SQL语句。

    接下来分析参数innodb_autoinc_lock_mode以及各个设置下对自增长的影响,其总共有三个有效值可供设定,即0、1、2,具体说明如下:

    0:这是MySQL 5.1.22版本之前自增长的实现方式,即通过表锁的AUTO-INC Locking方式,因为有了新的自增长实现方式,0这个选项不应该是新版用户的首选了。

    1:这是该参数的默认值,对于”simple inserts”,该值会用互斥量(mutex)去对内存中的计数器进行累加的操作。对于”bulk inserts”,还是使用传统表锁的AUTO-INC Locking方式。在这种配置下,如果不考虑回滚操作,对于自增值列的增长还是连续的。并且在这种方式下,statement-based方式的replication还是能很好地工作。需要注意的是,如果已经使用AUTO-INC Locing方式去产生自增长的值,而这时需要再进行”simple inserts”的操作时,还是需要等待AUTO-INC Locking的释放。

    2:在这个模式下,对于所有”INSERT-LIKE”自增长值的产生都是通过互斥量,而不是AUTO-INC Locking的方式。显然,这是性能最高的方式。然而,这会带来一定的问题,因为并发插入的存在,在每次插入时,自增长的值可能不是连续的。此外,最重要的是,基于Statement-Base Replication会出现问题。因此,使用这个模式,任何时候都应该使用row-base replication。这样才能保证最大的并发性能及replication主从数据的一致。

    这里需要特别注意,InnoDB跟MyISAM不同,MyISAM存储引擎是表锁设计,自增长不用考虑并发插入的问题。因此在master上用InnoDB存储引擎,在slave上用MyISAM存储引擎的replication架构下,用户可以考虑这种情况。

    另外,InnoDB存储引擎,自增持列必须是索引,同时必须是索引的第一个列,如果不是第一个列,会抛出异常,而MyiSAM不会有这个问题。

    mysql> create table test(id int primary key not null,count int auto_increment not null);

    ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

    3.2 无法水平切分

    虽然使用自增主键后,无法做水平切分。但是数据库自增最大的问题还不在于数据库单点造成无法水平切分,因为绝大部分公司还撑不到业务需要分库的情况就倒闭了。

    3.3 业务安全性

    自增主键最大的问题是把公司业务的关键运营数据完全暴露给了竞争对手和VC。举个例子,用户表采用自增主键,只需要每周一早上去注册一个用户,把上周注册的ID和本周注册的ID一比,立刻就知道了该公司一周的新增用户数量。如果网站声称新增了10万用户,但ID却只增加了1千,就只能呵呵了。

    因为主键的本质是保证唯一记录,并不要求主键是连续的。实际上不连续的更好,这样既避免了运营数据泄露,也给黑客预测ID制造了障碍,具有更高的安全性。

    用字符串主键就不存在这个问题。如果我们用一个UUID作为主键,即varchar(32),除了占用的存储空间较多外,字符串主键具有不可预测性。

    有人觉得UUID完全随机,主键本身没有按时间递增,不利于直接主键排序。其实解决这个问题很简单。

    方法一,直接用时间戳 UUID构造一个主键,时间戳注意补0,这样生成的主键就是按时间排序的。这个方法简单粗暴,缺点是主键更长了。

    方法二,自定义一个算法,时间戳放高位,序列号放低位,还可以保留机器位,然后用base32编码,可以把长度控制在20个字符内。

    有人会问,根据方法二,构造包含时间戳和序列号的64位整数作为主键是否可行?

    理论上来说是可行的,因为时间戳0xffffffff可以表示到2100年。但是剩下的位不是ffffffff而是只有fffff,如果给机器分配ff作为标识,那么每秒只能最多生成0xfff 1=4096个主键,对一些大型应用不太够用。

    为啥64位整数除掉时间戳只能用后面的fffff位呢?这是因为JavaScript的Number类型是56位精度,它能表示的最大整数是0x1fffffffffffff,而我们迟早会用REST跟JavaScript打交道,所以要把64位整数的范围限制在0x1fffffffffffff内,否则与JavaScript交互就会出错。

    虽然理论上64位整数做时间戳 序列号的主键是没问题的,但是实践中是没法绕开与JavaScript交互的,综合考虑,字符串主键最可靠。

    转自:

    本文由新葡亰496net发布于网络数据库,转载请注明出处:MySQL主键设计,mysql基本操作

    关键词: