mysql教程四本优化建模

2015年mysql教程春季第四本：优化建*******************温馨2015年mysql教程春季第四本：优化建*******************温馨提示1.百度搜索“辛星mysql2.辛星教程，时刻在进步*************2015年辛星mysql教程春季版1.辛星mysql程起20148，当时原计划写五本，但是只2.*********************纲领纲领：传播知识，传递温情特色：更新更全更实用**********************寄语前进的道路，辛星只要星哥在，编程充满爱争取做最优秀的、最开放的教程体系1/**************目录本书概 第**************目录本书概 第一节：建表详 第二节：数据类型(上 第三节：数据类型(下 第四节：范式与反范 第五节：建模实 第六节：建模经 第七节：优化基 第八节：索 第九节：数据碎 致敬读 ********************联系我个人邮箱2/本书概*********************概述本书概*********************概述这本书在以后的教程中可能会拆成两本来写MySQL优化，一本写MySQL的建模，虽然这两个话题的相似程度并不高，所谓MySQL优化，真的是包罗万象，而且对于不同的应用环境，化，对索引的优化，对SQL对应用程序的优化，对架3.所谓MySQL建模，相对于知识，它更加侧重于经验和应用环境，般来说读者只需要大概了解建模的要*****************知识结构验则很难有个定论了，因为对于应用的不同类型，不同的硬件配置，提高性能，在另一种情景下却是累赘3/第一节：建表详************************概述对第一节：建表详************************概述对于建表，我们目前还没有学习完整的建表语句，这一节我来补充一些基本概3.对于具体的修饰符，我们这里介绍几个比较关键的****************主键对于一些事物，我们有时候可以用一些特征或者信息来唯一的定它，在数据库中，也有类似的概念，我们称之为”主键主键有两大特性：唯一性和排他性所谓唯一性，即一个表里面只能有一个主键，关系数据库不允我们有两个主键，它的根本原因是为了防止存储冗余数据写完所有的字段之后，加一个primarykey(字段1，字段2 来表示一个主键7.下面我们创建一个以gid和uid为主键的表，范例8.我们可以插入两条数据，比如4/10.注意(gid,uid)是一个主键，这里的顺序是很重要的和(2,3)是两个主10.注意(gid,uid)是一个主键，这里的顺序是很重要的和(2,3)是两个主键，我们看操作如下MySQL拒绝执行我们的SQL句，这就从很大程度上避免了错**************唯一字段主键和自增好处就是处理方便，毕竟处理一个字段比处理两个字段要方便的多我们上面是在声明完所有字段之后使用primarykey种方式来3.其实我们还可以在某一列结束的时候加primarykey，5/1),比如非空(不允许该条记录的其1),比如非空(不允许该条记录的其他某一个字段有值，但是该字段没有值5.那么咱们指定一个正整数的id主键的方式通常是这样 intprimary7.那么我们可以创建一个t2表，我们看如下范9.6/10.当然我们可以10.当然我们可以修改这个自增值，这里我们使用altertable7/段的段的值加1.16.这里我们看一下具体的操作效果吧8/我们插入空值，那么id的下一我们插入空值，那么id的下一个是多少呢?答案是445，我们看一18.对于主键和自增，我们就讲到这里啦*********************非空2.如果我们指定了一个字段非空，那么当我们插入一条记录的时候，3.这里有必要说一下，那就是主键notnull饰符，因此我们在被primarykey修饰的字段是不能为空的。************不可重复1.我们可以在字段后面使用unique来表示该字段的值不能重复2.如果我们视图插入该字段重复的值，那么MySQL3.primarykey含了unique果的，如果一个字段被key修饰，那么就不用unique修饰符了9/**************默认值**************默认值default默认值来表示该字段********************统一演示2.是非空不允许重复的，第二个是num，它是非空并且默认值为60.4.当我们向people的num字段不插入数据的时候，它会自动用代替，范例如下5.那么我们可以看一下此时people中的数据，我们可以看到真填充的数据为10/6.但是当我们向num6.但是当我们向num字段插入null的时候是会报错的，如下范8.那么people表内都存储了哪些数据呢?我们来看一11/9.但是当我们9.但是当我们再次不给id定值的时候，也就是MySQL复插入0，那么就会出现重复的错误了，我们看下面操作实例：10.这里就是当插入值0的时候冲突了，就是因为unique的原因，不过这里我们只是使用unique定为不可重复，但是并没有指定******************说明型有关，因此我们还是到了特定的数据类型的时候再说我们就需要花些时间来好好了解MySQL的数据类型了。12/第二节：数据类型第二节：数据类型***********为何需要数据类型第三代编程语言普遍支持”数据类型”这个概念，比如Pascal支持”记录”,C言支持”指针”Python持”字典”,比如PHP持”资源”类型，而Ruby则支持”区间”.可能很多语言支持自己独特的数据类型，但是它们也往往支持一些共同的数据类型，比如”字符串”、”整型”、”浮点型”、”布尔类型”我们MySQL了存储数据的时候可以达到更高的效率，对数据类****************MySQL数据类型2.我们可以在MySQL命令控制台上查看当前版本支持的所有的数据类型，我们使用”?datatypes”即可查看所有的数据类型，如下操作：13/3.如果我们要查看某一种具体的数据类型3.如果我们要查看某一种具体的数据类型，我们就可以使用”?数据类型”的方式来查看某一种具体的数据类型，比如我们要查看int这种数据类4.MySQL275.可能不同的资料中对于数据类型的分法是不一样的，有人把blobtext独列出去，还有的把复合类型也划分到字符串里面来，还6.在这里，我还是主张我这种分法，当然各种分类都有自己的依据，*************数据类型的重要性那么我们存储一个ip地址可以有多少种存法呢?3.比如我们还是存储南开大学选课系统的ip地址，也就0，这里我们直接存储明文14/所属类 具有数据类型数 功整数类 tinyint、int等5 存储整数类浮点类 float、double等3 存储小数数字符类 char、varchar等12 存储文本信时间日期类 date、time等5 用于记录时复合类 enum、set共2 用于集合、枚位类 bit共1 用于位比析的时候析的时候每三位加一个点，我们只需要不1000可以了，它需要使用bigint，占据8个字节。使用char是varchar，它占据的字715等，它基本不用解析第三ip址的每一个数据都可以转化16制数，于是该ip可以存储为de1f200a，它此时需要8个字节。第四种方式认为我们存储3726516234可，这个数据是怎么来的，我们转化为ip地址的时候通过取模和商即可，它需要int类型，占据4个字节。9.那么对于老手又该怎么存储呢?老手肯定会存储为int类型的，不专门用于IP地址的转换，我们看一下具体的操作：15/存储方 占用空整体字符串存 7-15字整体整数存 8个字伪16进制字符 8个字伪256进制整 4个字11.然后我们可以查看一11.然后我们可以查看一下ip中存储的数据，看到了吧，它和我们12.那么我们怎么来得到我们想要的IP地址呢?我们只需要使inet_ntoa即可了，如下范例*************数值类型类型支持不同大小的数据，并且MySQL许我们指定数值字段中3.16/****************整数类型1.MySQL支持五种整数类型：tinyint、smallint、mediumint、intbigint****************整数类型1.MySQL支持五种整数类型：tinyint、smallint、mediumint、intbigint3.(1)tinyint1(2)smallint2(3)mediumint3(5)bigint据8***************有符号**************无符号2所谓无符号数，字面意思是无正负之分，实际上就是不包含负数，也就是只包含0和正数。所谓有符号数，也就是包括正数、负数和tinyint1字节，即一个byte8个bit，也就是8而每一位都有01种存储状态，因此它的值共计有28方状态，也就是256种状态5.对于无符号数来说，存储的最大范围就是256去1，为什么要减去1呢，因为我们必须分配一种情况给0，即存储范围从0到17/6.对于有符号数来说，通常使用的是补码来表示。对于1字节据，可以存储2566.对于有符号数来说，通常使用的是补码来表示。对于1字节据，可以存储256种情况，如果是有符号数，那么它的存储范围分成负数和非负数两部分的，负数那部分就是256除以2号，就是从-1到-128，非负数那部分是从0到7.****************修饰符1.MySQL在整数类型后面可以跟一个宽度指示器，它可以把显示的数据值加上到指定的长度来显示，比如int(12)就表示不足12数字来屏幕上占据12个数字的位置。2.值得注意的是，该宽度指示器并不会影响int取值范围，我们上面也讲了，int，和宽3.该宽度指示器只是影响我们显示的格式，而且默认使用空格填充，比如int(12)如果显示23，那么先输出10个空格，然后输出23这个4.我们可以使用unsigned修饰符来规定字段只保存正值5.而zerofill修饰符固定用0而不是空格来填充到宽度指示器的宽度zerofill就意味着它是无符号数据。*****************实战演示12.上面我们建立的这个表中，id是int型，默认显示位7，不足7位的时候用0填充，level是tinyint类型的，默认显示位数为2位，不足2位的时候用空格填充。18/3.通过上述示例3.通过上述示例，我们知道位数不足7位的，它会用零填充到7位，然后显示给我们，位数超过7位的，MySQL不会截断它。这里跟宽度指示器没关系，是因为它的大小超出了MySQL相应数据类型的存储范围，比如我们用tinyint256数据，肯定6.如果我们存储的字段信息超过了MySQL相应数据类型可以存储的范围，MySQL7.比如说tinyint，如果是有符号数的时候，存储大小为从-128但是我们插入的数据明显tinyint储范围，那么它存储的19/9.10.我们通过select可9.10.我们通过select可以看到里面的数据如11.对于宽度指示器和范围，我们就练习到这里啦*************类型转换2.3.既然连个警告都没有，那么插入的数据是多少呢?我们来看一下20/5.我们看一下刚才的两个警告吧6MySQL从字符串到整型的类型转换，和其他大多数编21/7.8.由于这些问题绝大多数编程7.8.由于这些问题绝大多数编程语言都会讲到，因此这里就不赘述了*******************小数类型小数，当然也可以用浮点类型所谓浮点数，它有自己的一套IEEE规范，比如Java采用的就是IEEE754标准。有些编程语言还分为单精度和双精度，比如C语言，有些语言就只有浮点类型，比如PHP语言。4.在MySQL中，小数可以用定点类型来存储，此时数据类型为decimal。也可以用浮点类型来存储，单精度浮点数为float，双精度浮点数为double。5.6.下面我们就分为浮点和定点来分别介绍下它们的具体使用吧22/数据类 字节 所属类 固定的4字 浮点类 固定的8字 浮点类 decimal(M,D)由M和D决 定点类**************浮点类型**************浮点类型比如float(4,2)这种格式，它一般用两个数来表示，作用如下：前面那个表示显示的总位数，通常称之为”精度后面那个表示显示的小数点后的位数，通常称之为”标度4.float和double的大小都是固定的，而且占据字节数也是固定的float占据四个字节，double占据八个字节当然double表示的范围比float要大，对于范围的计算IEEE规范，这里我们可以通过”?float”来查看float表示有符号数的时候，最小可以达到-3.41038方这个级别，最大3.41038方这个级别，从贴近0的角度看，负数可以达到-1.17乘以10的-38次方这个级别则可以1.171038方这个级别7.float在表示无符号数的时候，最大还是可以表示到3.4乘以1038次方数量级8.对于double话10308方数量级，具体我们就不介绍了，我们可以看一下MySQL官方给的解释：23/数据不是很特殊，一数据不是很特殊，一般都可**************浮点数操作实战12.3.三位，不足的用后缀0补齐即可，多余的位数也会四舍五入后干4.前面说到浮点数可能会保存数据不精确，这是为什么呢?可能对新手来说这是一个灵异事件，星哥以自己的经验来给大家展示下24/5.首先我们创建一个表5.首先我们创建一个表，它只有一个字段即可6.然后我们向里面存储两个数据，比如7.当我们再次从表中检索数据的时候，我们发现它里面的数据如下25/******************定点数1.我们用******************定点数1.我们用decimal来定义一个定点数，使用的语法就是decimal(m,d)在decimal(m,d)中，其中的m称之为精度，而d表示小数点右侧的位数，我们称之为标度对于decimal(5,2)这个数据类型，它表示该数据的最大5，其中小数点后面占据2位，比如存储22.34是没有问题的。字节，否则就是d+2,不过我们绝大多数都是要求m>d的。度四舍五入后插入。如果SQLMode是在严格模式下，系统会直接7.下面是MySQL官方对decimal的解释通过上述描述，我们可以知道M的最大取值为65，而D对于decimal理论部分我们就讲到这里啦，接下来就是具体的26/*************decimal实战操作1.首先我们创建一个包含*************decimal实战操作1.首先我们创建一个包含decimal2.然后我们向该表中插入三条数据，范例如下4.我想朋友们应该知道表中数据存储了什么吧，我们验证一下27/5.对于定点数，我们上面只是简单地演示了一下它的5.对于定点数，我们上面只是简单地演示了一下它的基本使用方法***************数值类型小结1.上面我们学习了整数的5种类型和小数的3种类型2.下面我们做了一个表格来简洁的说明一下吧3.***************字符串类型对于该字符串类型，共10，它们很相似，所占的存储空间大********************char类型1.我们char型来表示定长字符串，它的使用规则就是在圆括号2.该大小修饰符的取值范围为0-255，当我们向里面填充数据的时28/类 具体数据类 占据空 1 2 3int或 4字 8 4 8 不确3.比如我们指定name字段为char类型，且3.比如我们指定name字段为char类型，且占据20个字符，那使用如下定义即可:4.注意char后面跟的小括号中的数字是表示的字符数，而不是字数1.varchar型可以理char型的变体，它也可以后跟一个小括2.它的修饰符的数字会大一些，可以从065535，不过我们从第二本也讲了，这个范围指的是字节数latin1这种字符集，最大只能到65532，而对于多字节字符集，会更小一些。3.不过我们需要注namevarchar(4)中表示占据4字符，不*************char与2.对于char和varchar中有一个整数指示其大小，char和的处理方式是不同的，下面介绍如下(2)varchar存储短于指示器的varchar类型不会被空格填充，但是长于指示器的值仍然会被截短3.一般来说，对于char(m)类型的数据列里，每个值都会占用m个字符，如果某个长度小于m，那么MySQL在内部处理的时候会给它加上空格来补齐为m个字符，但是在检索的时候再把这些空格给4.在varchar(m)类型的数据列里，每个值只占用刚好够用的字节再加上用来记录其长度的字节，如果该字段的长度255，它只需255，就需要两个字节，29/因此它需要的实际空间为m因此它需要的实际空间为m个字符占据的空间，再加上1个或者个字节的空间空间，MySQL把这个表中的长度大于4字符的固定长度的数据8.一般来说，使用char型会加快检索的速度，它有点像我们C9.一般来说，使用varchar型会节省存储的空间，提高空间利用率，因为varchar类型可以根据实际内容动态改变存储值的长度，所以在不确定字段需要多少字符时使用varchar可以大大地节约磁10.不过在我们讲存储引擎的时候也看到了，对于InnoDB擎，我们不建议使用char类型，我们都建议使用varchar类型，因为InnoDB的特性，即使它使用char类型，也不会加快查找速度。一个charset=utf8或者charset=gbk即可支持中文了。*************char和varchar实战12.然后我们向里面插入一些字符串，这里注意尾部是有空格的30/4.可能有朋友4.可能有朋友会问：为char型的数据在查看的时候尾部的空格丢失了呢?其实这是MySQL的一个规定，那就是在保存的时候由5.对于char类型去掉尾部空格的这一规定，它是在服务器层规定的，6.其实当我们查询长度的时候，也会发现一些不同，范例操作如下7.对于char和varchar，我们就先讲到这里31/1.这两个数据类型的1.这两个数据类型的使用并不算特别广泛，它们在MySQL的binary、varbinary、char、varchar都可差别在于内部的处理个事，给外面的展示上好像差别并没有那么大binary按照字节数来计算的，而charvarchar是计算的字符数，则binary(6)占据6个字节，而char(6)则占据6个字符。4.可能朋友们不是很理解，我们首先建一个表5.然后我们插入数据如下6.32/8.不过对于binary8.不过对于binary个字段，它是使用00填充的，而varbinary是不会填充的，这charvarchar是很相似的，比如我们这9.然后我们向该表中插入两个a1011.其实MySQL在内部存储的时候是向该字段追加了0x00的，我可以通过查看十六进制来看到这一点33/12.这里我们可以看到它会向尾部添加0x00来补足位数1.12.这里我们可以看到它会向尾部添加0x00来补足位数1.对于较小的文本，我们是可以使用charvarchar但是对于一些较大的文本，我们通常考虑text或者blob系列。2.此时我们需要text家族和blob族，这两大家族都根据我们通常我们使用text来存储博客、邮件的文本信息，比如一篇博里面可能有很多内blob是binary blobtext主要区别是blob型是二进制方式存储的，而text是以文本方式存储的，因此blob区分大小写，而text区分大小6.虽然也可以用blob存储照片信息等二进制文件的信息，不过目7.在有些工程中，是通常把text字段放到一个单独的表中的，也就是说这个表中只有一个id段和一个text型的字段，也有些工程是允许一个表列较多的情况下仍然允许有text列。34/9.text和blob子类型主(1)tinytext和tinyblob，9.text和blob子类型主(1)tinytext和tinyblob，范围为0-(2)text和blob0-(3)mediumtext和mediumblob，范围为0-(4)longtext和longblob，范围为0-*******************text实例练习1.我们可以新建一个包含text字段的表，范例2.3.4.对于text35/*****************小结*****************小结对于整数类型，它们都是int列的，它们有着很相似的名字，它对于浮点数的常见问题，其实就是IEEE标准本身的一些问题，对IEEE准，这里并没有介绍，当它表示太精确的数的时候会有误对于字符串类型，常见的就是char、varchar、text这三个，原理前面讲过了，不过InnoDB说，charMySQL7.对于大段的文本，我们通常使用text，在具体情况下，我们通常text独立到另一个表中。36/第三节：数据类型***************概述2.****************日期时第三节：数据类型***************概述2.****************日期时间类型它们可以被分为简单的日期、时间类型和混合日期时间类型4.5.上面表格中的yyyy表示四位数的年份，比如1992和1987这种格式，mm表示两位数的月份，d表示天，h表示小时，m表示分钟，s表示秒钟。6.日期类型、时间类型、日期时间7.接下来我们首先介绍日期类型，比较常用的就是year和date37/类 占据字 显示格式(并非存储格式 用 存储年 yyyy-mm- 存储日 时间点 yyyy-mm- 日期和时 yyyy-mm-dd 日期和时****************日期类型****************日期类型year型用于记录年份，通常需要四位数，但是我们也可以输入(1如果这个数据在70到99间，那么会在前面加一个前缀比如输入88变(2)如果这个数据在0069会在前面加一个20，比如输入33会变成2033.3.咱们用date4.5.然后我们向里面插入数据如下式的差别还是很大的，不管我们是否加引号，不管我们是否使用连不过第一种写法是MySQL推荐的，也是最标准的方式。8.我们可以看看MySQL给我们的显示结果38/10.然后我们发现日期被自动用0填充非法的年份，是无法正常存储的，MySQL给我们存储了一堆0.39/***************时间类型1.time表示一个时间点，比如当前时间是下午五点***************时间类型1.time表示一个时间点，比如当前时间是下午五点26分，那么可以存储就像我们的date”-“作为分隔符一样，我们在time习惯使4.对于时间的插入来说，最常见的两种插入格式就是6.注意time40/7.那么MySQL怎么解3337.那么MySQL怎么解333还注意我们前面说的从后向前解析吗？因此它会解析为3分钟33秒，我们可以查看一下**************日期时间类型从datetime这个名字就可以看出来，它是date和time的终极版它即可以存储日期又可以存timestamp为汉语即”时间戳”,它是当前时Unix年也就是1970年1月1日0时0分0秒的秒数，因此它是一个比较大的4.由于时间的计算比较困难，比如现在时间是2015年2月417:44:00,我想计算一下它到19929138:22:12历了多5.但是使用时间戳，第3条提出的问题就不是问题，因为对于时间们直接做减法就OK了。41/timestamp和datetime接受同样的参数，返回的信息timestamp和datetime接受同样的参数，返回的信息的格式也都是yyyy-mm-ddhh:mm:ss的19个字符的宽度的格式，好像没有任datetimetimestamp区别在于内部存储机制，下面是具体的小于1970年的时间的.(3)而且datetime以”yyyy-mm-hh:mm:ss”的格式检索和显示支持的存储范1000-01-0100:00:009999-12-31(4)而且timestamp8.这里说一个比较有意思的事，那就是如果我们timestamp入一个空值，那么它会插入当前时间的时间戳，如果我们向插入空值，那么它会插入一个空值****************实战演练12.然后我们插入数据如下3.下面我们看一下这个表存储的数据吧42/4.第一种方式我们插4.第一种方式我们插入了两个null我们发现datetime存的也是一个null值，但是对于timestamp，它插入的确是当前时间。对了MySQL的内置函数now()来插入数据。5.其中datetimetimestamp重要的一点区别就是timestamp6.然后我们再次查看时间，我们发现效果如下7.查看当前时区我们使用showvariableslike‘time_zone’，范43/9.9.上面说到向timestampnull时候会自动设置为当前日期，在之前版本中是只对第一个有效地，不过现在对多个timestamp1010.然后我们向该表中添加三个null值，然后检索如下***************timestamp和datetime的区别1.第一个区别timestamp的时间范围比较小，其取值范围从19700101080001到2038年的某个时间，而datetime则是从1000-01-0100:00:00到9999-12-3123:59:59，范围更大。44/第二个区别就是如果插入null，那么timestamp第二个区别就是如果插入null，那么timestamp会自第三个区别就是timestamp插入和查询会受到当地时区的影响，更能反应出实际的日期。而datetime则只能反应插入时当地的时区，其导致错误的出现4.上面就是常见的区别了，其实我个人还是蛮偏向于使用的**************插入格式日期类型的插入格式有很多，可以使用整数(比如1992)、字符串(比如’1992’)、函数(比now())，那么到底什么样的格式才能正确的插入呢，我们以datetime为例来说明。对于yyyy--ddh:mm:ss或者yy--ddhh:mm:ss格式的字符串，允许不严格语法，任何标点符号都可以用作日期部分或时间。对于包括日期部分间隔符的字符串值，如果日和月的时间小于10，不需要指定两位数，即1979-6-91979-06-09相同的，同样的，对于包括时间分隔符的字符串值，如果时分秒的值小于10，不需要指定两位数对于yyyymmddhhmmssyymmddhhmmss式的没有时间19970323020202和970323020202都会被解释为1997-05-2309:15:28，但是971122129015是不合法的，因为分钟部90，它会变成0000-00-00对于数字类型，数字值应该为6、8、1214长，具体规则(1)如果一个数值为8或14位长，则假定为yyyymmddyyyymmddhhmmss格式，前4位数表示年(2)如果数字为612长，则假定为yymmddyymmddhhmmss格式，前2位表示年。(3)其他数字被解释为仿佛用0填充45/如now()或者current_date。7.我们可以看一下这个如now()或者current_date。7.我们可以看一下这个current_date9.10.然后我们发现都可以通过，检索数据效果为46/1213.对于日期和时间类型，就介绍这么多啦，一般来说，year、和datetime使用的比较多一些****************复合类型1.MySQL还支持两种复合数据类型:enum和set，它们扩展了规范enum型只允许从一个集合中取得一个值，而set型允许从一4.enum类型字段可以从集合中取得一个值或者使用null值，除之外的任何输入都会使得MySQL在这个字段中插入一个空字符串47/比如我们指定一个比如我们指定一个字段为enum类型，并且取值仅限于m和那么字段使用gender 插入m也可以插入f。enum在系统内部可以存储为数字，并且1始用数字作为一个enum类型最多可以包含65535个元素，其中一个元素被MySQL留，它用于存储错误信息，这个错误值用空字符串来表示，对应的索引值为0.****************enum实战1.首先我们建一个表，我们用id示成员编号，用gender2.然后我们插入四条记录，看如下操作3.我们上面插入了四条记录，第一条记录插入的是m，第二条记录插入的是f，注意这两条记录都是在enum的，第三条记录插入的是F，它按理来说是不在enum中的，但是我们把它改成小写之后就在enum中了。第四条记录也不在enum中，因此他会报错说数4.我们看一下存储的效果48/7.对于enum7.对于enum49/*******************enum说明1.由于enum*******************enum说明1.由于enum取值范围为0-65535，当范围从1-255时候，我们需要一个字节来存储，对于255-65535时候，需22.而enum最多允许有65535个成员，这一点上面也说了4.然后我们可以查看相应的结果一个没有预定义的值，都会让MySQL插入一个空字符串。3.么MySQL会保留合法的数据，除去非法的数据50/4.比如我们指定name字段为4.比如我们指定name字段为set类型，只需要使用 即可5.**************set实战1.首先我们创建一个表，然后在创建该表的时候指定该set哪2.上面的x是一个集合类型了，它可以包含a，b，c，d，e3.4.51/检索x列中含有b一项检索x列中含有b一项的所有信息，我们可以使用like作符或者regexp操作符，这里我们可以考虑使用regexp操作符，范例：7.其实不光是enum可以看到其数字形式，对于set，我们也可以看下来我们就来研究一下set的内部存储机制。52/****************set的内部机制1.一个set类型最多可以包含****************set的内部机制1.一个set类型最多可以包含64项元素在set元素中值被存储为一个分离的位序列，这些位表示与它相应的元素了重复的元素，所以set类型中不可能包含两个相同的元素。4.一个set型64元素，而且它们的取值都是一个6.比如我们想表示一个集合a元素和c素，那么它的二进制表示就是00101，那么它的十进制表示就是5.7因此，我们分析一个表达式x&5,它这里就是把x的标志位与00101比较一下，这里是位操作，如果两者都是1，那么改位为1，只要有一个为0，则改位为0，因此它的结果会导致只要xa或者c，那么就会被筛选出来。8.我们看一下实例操作吧53/集合元 二进制表 十进制表 9.而且我们此时可以知9.而且我们此时可以知道那些表中的数字是怎么得来的了，比如id3那条记录是包含d和e两个元素，因此它816，也就是24，我们看一下结果：10.对于set的数字的内部机制，我们就讲到这里啦**************集合附注1.我不知道朋友们对集合这个概念的了解是否足够深入，在数学上，2.这里我们需要注意的是，这里的元素的顺序并不重要，如下3.当我们检索数据的时候发现插入的数据是一样的，操作范例54/5.比如我们在一条记录中插入了多个a5.比如我们在一条记录中插入了多个a6.然后我们检索数据的时候，发现只会存储一个a，范7.********************set类型1.set和enum类型非常类似，也是一个字符串对象，里面可以包0-64个成员2.根据成员的不同，存储上也有所区别，具体操作如下(1)1-8个成员的集合，占1个字节(2)9-16个成员的集合，占2个字节(3)17-24个成员的集合，占3个字节(4)25-32个成员的集合，占455/(5)33-64个成员的集合，占8个字节set(5)33-64个成员的集合，占8个字节setenum了存储之外，最主要的区别在于set可以一次选取多个成员，而enum则只能选取一个。set类型可以从允许值集合中选择任意1个或者多个元素进行组合，的添加到set类型的列中的情况，合法值会插入到set类型中，非法值则不会插入。*******************bit********位类型对于bit类型，也就是位类型，可以用于存放字段值bit(m)可以用来存放多位二进制数，m164，如果不写则默认为1.3.对于位字段，直接使用select命令不会看到结果，可以使用bin()(显示为二进制格式)或者hex()(显示为十六进制格式),我们可以4.5.然后我们插入1，我们如下操作6.56/7.那么我们可以查看7.那么我们可以查看它的二进制情况，具体操作如下8.这里注意它只是一位，是不可能容2样的数29.10.然后我们插入什么数据呢？这里我们插入a的ASCII97，我们操作如下57/111213.当我们向bit型111213.当我们向bit型字段时，首先转换为二进制，如果位数允许，14.位运算还是比较方便的，不论是使用编程语言还是使用MySQL，**********************小结1.对于日期时间类型里面最重要的就year、datedatetime三2.对于set和enum，使用时一定要谨慎，要注意取值的域3.对于bit，常用于某些特殊的位标58/第四节：范式与反**********************概述到范式这第四节：范式与反**********************概述到范式这个词，没错，在本科生教材中就会有范式的概范式这个概念的提出就是为数据库建模提供一个理论基础，确实，*******************范式3.目前关系数据库共有六种范式第一范式(1NF)、第二范式(2NF)、第BC范式(BCNF)、第四范式(4NF)、第其中BC式中的BCBoyce-Codd式，也就是巴斯-科德范式BC式即可，这也是*******************第一范式2.其实第一范式也就是对属性的原子性约束59/低的，只要数据库是低的，只要数据库是关系型数据库，就自动满足第一范式了5.我们无法向关系型数据库中存储一个集合，注意我们的set*****************第二范式举个例子，假如我们创建people它里面可以有一个字******************完全依赖60/所谓完全依赖，就是所谓完全依赖，就是指不能存在于仅依赖于主键的一部分属性范例就是部分依赖，也就是不完全依赖。*****************第二范式分析键是比较好的解决办法*****************直接依赖什么叫函数依赖呢？比如ab，b赖c，那么ac4.比如我们建立了一个学生信息表，它存储如下信息①学号，②姓名，③班级，④班主任姓61/的学号*****************第三范式1.所谓第三范式，也就是任何非主属性不依赖于其他非主属性2.*****************各种关键字介绍一下7.62/****************BC范式要满足BC范式，必须建立在满足第三范式的基础上其实****************BC范式要满足BC范式，必须建立在满足第三范式的基础上其实BC式和第三范式很相似，猛一看好像看不到太多区别，那************第三范式和BC范式二者的区别在于：候选关键字和主关键字第三范式要求其他字段直接依赖候选关键字即可，但是BC式必4.①身份证②学③姓5.BC，为什么呢，原因(1)把该表拆分为两个表，分别为t1和(2)t1中保存身份证号和姓名，t2(3)此时t1t2满BC式8.那么朋友们想一下我们是建立一个表t好，还是拆分成两个表和t2好呢?朋友们不妨先想想63/************建模标准1.通常我们把建模的标准锁************建模标准1.通常我们把建模的标准锁定为第三范式或者BC式就可以了，其很多人会把规范锁定为第三范式，少数人也会追求下BC********************建模实例2.3.64/字 意 学 学生姓 班级编 班主任名4.这里小小的说明一下，我们之所以是建立了一个varchar(4)，而不是varchar(3)，是因为有些人的名字是四个字，这些情况多发生名字了，这个得4.这里小小的说明一下，我们之所以是建立了一个varchar(4)，而不是varchar(3)，是因为有些人的名字是四个字，这些情况多发生名字了，这个得想的话，可以更大一些，毕竟varchar不会保存冗余信息。传递依赖于id的，那么问题何在呢?7.我们先看一个信息表吧8.问题有如下几个插入时插入了冗余信息，比如我们知道了辛星是22班的么已经插入了刘强这条信息，但是下一条记录我们知道了刘强是班的，我们还是插入了刘强这条信息修改更加繁琐。假如我们要把22班的班主任从刘强换成张成，更多，但是实际上只需要修改一条数信息是无法存储的，因为我们没有学生信息就无法保存班级信息9.65/10.这里我们直接给出解决方案吧(1)创建两个表，分别为10.这里我们直接给出解决方案吧(1)创建两个表，分别为student和class，首先是student表然后是class(2)然后我们插入的信息应该是这样的，首先是studentclass66/******************反范式2.(1******************反范式2.(1(2(3*******************反范式范例67/3.然后我们创建一3.然后我们创建一个表示学生信息的表，操作范例如下4.然后我们向score表中插入几条数据5.然后我们向stu表中插入几条数据，范6.这里我们的sum就是一个典型的派生列，在学校查询成绩的朋友们有时候可能仅仅对总分感兴趣，那么这个如果我们没有sum列，那么我们就需要对score中的各个字段进行一遍运算，然后得到结7.由于我们没有在score列中保存学生姓名信息，因为我们已经在作就显得更加麻烦了，但是我们使用sum列就让这一步骤变得简单68/8.不过使用了sum列之后的缺点也随之而来，那就是当score中的8.不过使用了sum列之后的缺点也随之而来，那就是当score中的数据进行改变的时候stu表中的数据也必须随之改变才行，这里我改一处的时候也修改另一处，个人建议在应用程序中完*******************小结1.范式化的好处(1(3)由于很少有冗余数据，因此我们基本不需要使用distinctgroupby语句4.因为反范式化的数据库设计模式通常都是所有数据都在一个表中，69/第五节：建模实****************概述第五节：建模实****************概述PowerDesigner等，不过它是付费的，当然我们可以考虑下载免图形化界面的比如phpmyadmin之中，特别是我们完成了一次建其他人审核一下，然后确认通过，它会自SQL句，而且还们这里使用的是MySQLWorkbench6.1，这里来个截图吧：5.我之前喜欢用PowerDesigner，好1215用过，它的功能还比较的慢，后来就开始偏向MySQLWorkbench这种小型快捷的软70/***************开始建模1.首先我们点击***************开始建模1.首先我们点击Models右边2.我们开始建模的时候的界面是这样的3.里面使用表、视图等各种之前学习的概念，然后我们可以建模如下71/然后我们就可以保存它了，这里可以保存为mwb式，这里只需要使用File菜单中的SaveModelAs即可了，我们对保存之后的文他格式，比如我们可以导出为png格式，操作范例：7.我们可以看一下导出的png72/为SQL语句，操作范例：73/11.11.我们点击Finish即可生成SQL文件，我们这里可以来个截对于MySQLWorkbench的基本操作，我们就讲到这里啦该软件的具体操作细节，读者朋友们可以查询相关资料14.对于建模的实际操作，我们就讲到这里啦74/第六节：建模经*******************概第六节：建模经*******************概述能对应用的开发也会造成较所谓数据字典，大致就可以理解为数据包含什么，数据的注释，数据的约束，数据的流程和处理的集合。数据字典可以理解为一个。在需求分析中的另一个比较重要的东西，可能就是ER图了，ER图就是实体-关系图，而ER则是EntityRelationshipDiagram的简6.这里可以给一个ER图让朋友们看一75/7.ER图的矩形中表示的是实体7.ER图的矩形中表示的是实体名，椭圆中表示的是属性，它们是用理解为表，把属性理解为字段，比如上ER中如果我们建立一****************数据类型的选择2.(1(2(3)避免使用U。要更少的CPU期，比如整型比字符串操作就更加简单，这其中的对于日期和时间，我们应MySQL建的数据类型，不应该6.对于IP地址的存储，前面讨论过了，建议用无符号整型7.对于null76/很多人所欣赏Java不支持指针，但是我想和我当时一样曾经为空指针异常而困惑的人应该不在少数，当然C语言中有个大名鼎鼎的null指针，也就是无类型指针，值为0，此时成为悬空指针。PHPPHP4本以上也引入了null，null型唯一的值就是null，此时它表示一个变量没有值，也有可能是这个值为null，或者没有被赋值，也有可能被unset掉了数据库中也支持nul，它表示未知的数据，它通常可以细分为三(1)数据存在，但是不知道它的值(2)不知道数据是否存在，MySQL会自动填充为null(3)数据就是不存在，我们给一个字段赋值为null7.这里我还是权朋友们不要死扣定义，总之，知道null知道77/9.对于null，我们判断的9.对于null，我们判断的时候可以用is(not)null判断，如果我们用=null来判断的话，也会返回一个null，当然也可以用null安全1011.null=null回的也是一个null，因为本来就是两个不确定的数*************null的内部机制1.请允许我抄一段MySQL官方的解释：nullcolumnsrequireadditionalspaceintherowtorecordwhethertheirvaluesareNULL。forMyISAMtables,eachNULLcolumntakesonebitextra，roundeduptothenearestbyte也就是说，我们存储null是需要额外的空间的，而我们存储空其实我们已经讲解过InnoDB的存储行格式了，我们知道它在compact格式中存储null并不会占据空间的，它只是在表示列4.其实这也导致了为什么我们说notnull的效率会比null高一些，因为MySQL进行比较的时候，null参与字段的比较，因此它会5.本书后面会讲到索引，这里先说一下，B树索引是不会存储值的78/1.很多表都包含可为1.很多表都包含可为null的列，即使应用程序并不需要保存null也是如此，这是因为null列的默认属性，通常情况下最好指定列为notnull，除非真的要存储null值。2.如果查询中包含可为nullMySQL说更难优化，因为可为null的列使得索引、索引统计和值比较都更加复杂。可为null的列会使用更多的存储空间，在MySQL里也需要处理。可为null的列被索引时，每个索引记录需要一个额外的字节，在MyISAM甚至哈可能导致固定大小的索引变成可变大小的索引。通常把可为null列改为notnull来的性能提升比较小，在调避免设计成为可null的列。5.InnoDB使用单独的位来存储null值，但是MyISAM并没有这么做。InnoDB于稀疏数据有很好的空间效率，所谓稀疏数据，就是由很多取值为null。*****************数值型方面的问题1.CPU持原生的浮点计算，但是CPU支持对decimal直接计算，因此在MySQL5.0及其更高版本中，MySQL是自己实现的decimal的计算，因此使用decimal会比使用浮点型要慢。精确计算时才使用decimal，比如存储财务数据。在数据量较大的时候，可以考虑用bigint来代替decimal，然后将使用bigint存储较大的浮点数，可以避免浮点数存储计算不精确和decimal精确计算代价高的问题。************特殊功能的表********据，它们在数据检索的时候79/所谓的汇总表，所谓的汇总表，更多的是保存的通过groupby语句聚合数据的表，*******************小结特定场景下有意对于OLTP与OLAP应用来说，对于这两种截然不同的类型，80/第七节：优化基*****************一个悖论第七节：优化基*****************一个悖论说实话，看过不少MySQL至会得出了相反的结论在经历了初期的迷茫后，我发现了一个真理：优化是相对于特的应用环境来说的，抛开了特定的使用环境而空谈优化，毫无意义***************优化的方向要说源代码级别的优化不到，也跟我不是一个职业DBA有关，从未耐下心来看过源代然后就是编译的优化，这个就可以根据特定的运行平台来选择了，能经验特别丰富的DBA不一定能够配置的很好，有很多参数也需最后就是数据库建模和SQL语句的优化，一般来说它们和业务逻辑也有很大关系，对于数据库建模，我们用几节来讲解，对于SQL语句，主要就是查询的优化，我们主要讲解一些经验和索引、慢查是放到其他系列去讲解。81/好书，也在慢慢发现中8.由于本系列书的目标是MySQL，对于太多的内容肯定也讲不过来，因此对于前面的内容我们直接放弃不讲，只讲和MySQL本身操作关系最强的部分**************基本优化步骤第二个比较基本的步骤就是使用explainMySQL对某一条具体的SQL语句的执行计划，这是模拟MySQL的执行，它在极少数情况下和实际情况不一致，绝大多数我们可以认为MySQL在内优化中有一个东西能在MySQL化中起到立竿见影的作用，那无是用连接操作来优化子查询，因为MySQL的子查询做的还不算很82/********************慢查询日志1.第一个就是使用慢查********************慢查询日志1.第一个就是使用慢查询日志，慢查询日志记录了MySQL询比较慢的记录，如果朋友们使用的是wamp不开中添加如下参数来开启慢查询3.然后我们可以在MySQLlike‘log_slow_queries来查看慢查询是否用 启，操作范例如下83/84/②修改慢查询的超②修改慢查询的超时时间，把它调的比较小，这样就有没问题的查询成了8.这里我们可以修改my.ini我们把超时时长设置为0可了，9.然后我们可以执行一个查询操作，如下操作主机，查询的SQL句，当然还有时间戳等信息，当然还可以看到的就是查询出来的记录是6条。85/12.我们通过查看Query_time12.我们通过查看Query_timeLock_time判断是否是因为长时间的锁定而导致的。我们sessionpost表给锁14.然后我们在第一个session中释放表锁，操作如下15.此时我们可以看到第一个查询52.61，然后我们可以得1686/******************分析特定SQL语句******************分析特定SQL语句1.当我们用慢查询定位到某个SQL句的时候，我们就可以分析它2.对于分析SQL语句，我们通常可以使用explain来做到，它法格式就3.4.当然我们还可以通过rows来看到它扫描了12行数据，通过select_type道它是一个简单查询，通过table道它扫描哪些表，通过type可以知道这里是一个全表扫描。5.我们可以再看一个例子87/6.我们这里通过possible_keys以6.我们这里通过possible_keys以知道它可用的索key以知道它最终使用的是哪个索引，其中的key_len是索引长度，这system，系统表，表中只有一行记录explain的截图中就是一个const查询，因为它只需要从表中找到第个表中取出来的记录做联合，它和eq_ref大的区别是使用的索引的搜索包含null值的记录。(6)unique_subquery(7)index_subquery：使用冉是索引的子查询(8)range88/(10)all8.其中possible_keys表示可能用到的索引，这里列出的索引可能在实(10)all8.其中possible_keys表示可能用到的索引，这里列出的索引可能在实际中并没用到。keyMySQL际上要用的索引，当没有任何索引可用的时候，该字段的值为null。key_len显示了MySQL使用索引的长度。ref显示了哪些字段或者常量被用来和key配合从表中9.rows则是MySQL认为在查询中应该检索的记录数，注意这里只是MySQL认为的，它并不一定是真正运行检索的时候的行数，这个数据是怎么得到的呢?它是依靠MySQL的统计信息得到的，它不一定准确，我们在下一节会用analyzetable的方式让它刷新某个表10.而extra则是显示查询中MySQL的附加信息，常见的有usingfilesort示对查询的结果集进行了一次排序，虽然它有个file，但是该操作是在内存中完成的。temporary表示使用了临时表index表示使用了索引。1189/12.当然我们可以看到上面查询果然会得到两条记录是的12.当然我们可以看到上面查询果然会得到两条记录是的MySQL能中，并发、锁定、子查询、连接、索引等**********************代价1.上面我们用explain的方式可以查看MySQL在对一个具体的SQL语句的处理过程中的操作，其实对一个具体的查询语句，MySQL2.那么MySQL是怎么权衡这些算法的呢?答案就涉及MySQL4.然后我们可以通过如下方式来查看它的代价90/5.然后我们可以再进5.然后我们可以再进行一次查询，范例6.价才是2.199，但是第二次查询只查询了两条记录代价则是2.809，行筛选的时候会产生较大的开销91/它是MySQL5.1中引入的，它是MySQL5.1中引入的，我们可以用它来查看某一条具体的语句的执行状况默认它是关闭的，也就是它的值为0，我们可以查看一下它的取3.然后我们可以把它设置为1，然后我们操作如下4.5.然后我们可以使用profiles;来看看都进行了哪92/上面的Query_ID就是查询上面的Query_ID就是查询的具体编Duration就是查询的对它的要求也越发苛刻，后面的Query就是具体的查询语句了。*******************小结2.引，因为索引对于优化真的是太重要93/第八节******************价值如果大家第八节******************价值如果大家看介绍数据库优化的资料，基本都会提到索引，甚多资料第一个讲的就是索引******************简介索引，英文名称为indexMySQL也叫做键，即key，是存3.但是要说索引的具体实现，往往就没那么简单了，MySQL的索引主要分为B-Tree索引、哈希索引、数据空间索引(R-Tree)、全4.我们重点关注的索引是B-Tree引，如果没有特殊说明，一般的索引指的都是B-Tree而全文索引我们到架构设计那一部分再现的，它不是在服务器层实现的，因此没有统一的索引标准8.虽然远离差别较大，但是语法却都是一致的94/****************索引的创建2.(1)普通****************索引的创建2.(1)普通索引，即index或者说是key(2)唯一索引，即用unique修饰的列(3)主键索引primarykey4.我们首先看如何在创建表的时候创建索吧，范例如下首先是主键索引，它可以使用primarykey(字段1,字段 或者是在字段创建完毕之后用primarykey来修饰都可以①index[索引名][索引类型](列1,列 ②key[索引名][索引类型](列1,列 5.6.通索引的时候并未指定索引名，它会给我们自动创建一95/7.那么我们如何知道7.那么我们如何知道我们创建的索引起效了呢？我们可以用showcreatetable表名来查看一下我们刚才的建表语句，我们这里就可8.可以看到MySQL给我们反馈的标准形式，在这种形式中，主键索引永远使用primarykey指定，唯一索引使用uniquekey指定，普通索引永远使用key来指定。**************索引的创建继续1.其实我们有专门的索引创建语法的，也就是通过我们可以看一下MySQL给出的描96/格式为：create格式为：createindex索引名[索引类型]on表名(列名列表).它也可以创建唯一索引，基本语法格式为：createuniqueindex索引名[索引类型]on表名(列名列表)4这里我们首先创建一个表t2，操作范例如5.然后我们给uid建成为一个唯一索引，对midtid成为这里t2midtid两个字段共同创建了一个索引，看表的创建语句来查看，也就是说，我们通过createindex的语法我们可以通过showcreatetable表名的方式来查看该表的建表97/9.MySQL官方给出的语法格式吧：11.这里我们还是给个范例吧9.MySQL官方给出的语法格式吧：11.这里我们还是给个范例吧12.我们可以看一下此时的t2是什么样子的98/13.以上比较常见的创建索引的方法我们就介绍完了************查看索引1