0802-MySql(3)

主键上填0是会自动补充，但是其他的不行

其他的比如bit上有默认值，你写0或者null表示你真的要把这个插进去

如果你除了主键外的其他字段要用默认，只能写default，不能写0或null之类的

然后就是几种思想，第一是把select读取除来的数据当作另一个表

然后用这个表跟原表进行左查询(left join)，以查出来的数据为机制在原本进行on过滤数据对应数据

就可以实现找出最大价格的所有电脑信息这么个功能，但是主题是要了解这个思想做法

同时用到了子查询和左查询，具体语句如下：

select *

from (select cate_name,max(price) as max_price from goods group by cate_name) as g_new

left join goods as g on g_new.cate_name=g.cate_name and g_new.max_price=g.price;

第二个：拆分为多个表

把原表的数据拆分，比如把我们的goods表的品牌名，类型拆分开，原来在cate_name和brand_name是直接写的联想啊台式机啊这些具体的名字

这样写有个坏处，如果假如这个品牌或者类型改名了，你想要换这个就很麻烦，所以我们把他拆开

就是把原来的cate_name和brand_name改成cate_id和brand_id，然后单独建立两个表，goods_cates和goods_brands来存储对应ID的品牌信息

重点是思想，比sql语句重要，实际代码很少

怎么拆呢，就新建个表，主键自增长无符号Int类型，name是varchar40类型，然后select分组原goods里面的cate_name东西拿出来再insert插入进去

然后这个表就根据原表的类型建好了，再统一一下就是把原表自关联goods_cates，on c的name和g的cate_name一样，就set g.cate_name=c.id

现在你会发现虽然类型和新表都建好了，但是你往原本里填个999，对应的表没有的也可以，这是因为他们直接还没有关联

有两个办法，第一个是程序里录入的时候判断在不在goods_cates里面，第二个是把这个cate_id设置成goods_cates的外键

怎么设置外键呢，如下：

alter table goods add foreign key(cate_id) references goods_cates(id);

分开可以把foreign key理解为外键，参数是你要设置的字段，references是和哪个表的哪个字段进行关联，也就是goods_cates里的id字段

为什么是add呢，因为实际上这个东西的应该叫外键约束，就像一种协议，用来归档这个字段必须存在于另一个表的主键里

实际开发中很少用到外键约束，因为他会极大的降低表的更新效率

这里转载一个58到家数据库的三十条军规

一、基础规范------

（1）必须使用InnoDB存储引擎

解读：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高

（2）必须使用UTF8字符集【】

解读：万国码，无需转码，无乱码风险，节省空间

 

（3）数据表、数据字段必须加入中文注释【】

解读：N年后谁tm知道这个r1,r2,r3字段是干嘛的

（4）禁止使用存储过程、视图、触发器、Event

解读：高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU计算还是上移吧

（5）禁止存储大文件或者大照片

解读：为何要让数据库做它不擅长的事情？大文件和照片存储在文件系统，数据库里存URI多好

二、命名规范-----

（6）只允许使用内网域名，而不是ip连接数据库

（7）线上环境、开发环境、测试环境数据库内网域名遵循命名规范

业务名称：xxx

线上环境：dj.xxx.db

开发环境：dj.xxx.rdb

测试环境：dj.xxx.tdb

从库在名称后加-s标识，备库在名称后加-ss标识

线上从库：dj.xxx-s.db

线上备库：dj.xxx-sss.db

（8）库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用

 

（9）表名t_xxx，非唯一索引名idx_xxx，唯一索引名uniq_xxx

三、表设计规范-----

（10）单实例表数目必须小于500

（11）单表列数目必须小于30

（12）表必须有主键，例如自增主键【】

解读：

a）主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用

b）主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率

c） 无主键的表删除，在row模式的主从架构，会导致备库夯住

（13）禁止使用外键，如果有外键完整性约束，需要应用程序控制【】

解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先

四、字段设计规范-----

（14）必须把字段定义为NOT NULL并且提供默认值【】

解读：

a）null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化

b）null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多

c）null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识

d）对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录

（15）禁止使用TEXT、BLOB类型

解读：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能

（16）禁止使用小数存储货币【】

解读：使用整数吧，小数容易导致钱对不上

（17）必须使用varchar(20)存储手机号【】

解读：

a）涉及到区号或者国家代号，可能出现+-()

b）手机号会去做数学运算么？

c）varchar可以支持模糊查询，例如：like“138%”

（18）禁止使用ENUM，可使用TINYINT代替

解读：

a）增加新的ENUM值要做DDL操作

b）ENUM的内部实际存储就是整数，你以为自己定义的是字符串？

五、索引设计规范-----

（19）单表索引建议控制在5个以内

（20）单索引字段数不允许超过5个

解读：字段超过5个时，实际已经起不到有效过滤数据的作用了

（21）禁止在更新十分频繁、区分度不高的属性上建立索引

解读：

a）更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能

b）“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似

（22）建立组合索引，必须把区分度高的字段放在前面

解读：能够更加有效的过滤数据

六、SQL使用规范-----

（23）禁止使用SELECT *，只获取必要的字段，需要显示说明列属性

解读：

a）读取不需要的列会增加CPU、IO、NET消耗

b）不能有效的利用覆盖索引

c）使用SELECT *容易在增加或者删除字段后出现程序BUG

（24）禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性

解读：容易在增加或者删除字段后出现程序BUG

（25）禁止使用属性隐式转换

解读：SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中phone索引，猜猜为什么？（这个线上问题不止出现过一次

【

这个有点意思，问了下GPT，首先属性隐式转换是什么意思，是在查询语句中当属性的数据类型和条件或查找的数据类型不匹配时数据库会自动进行类型转换以满足语句的比较

在上面问题里，手机号一般都是varchar类型，但是匹配式匹配的数字类型，所以他会自动转换类型，这个就是属性隐式转换，他可能导致索引功能无法正常使用，不能正确命中目标所有

然后因为没有命中目标，导致触发全盘扫描，然后就是资源耗尽，超时之类的全盘扫描会出现的毛病，所以尽量避免属性隐式转换，正确写数据类型

】 

（26）禁止在WHERE条件的属性上使用函数或者表达式【】

解读：SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描

正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')

（27）禁止负向查询，以及%开头的模糊查询【】

解读：

a）负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描

b）%开头的模糊查询，会导致全表扫描

（28）禁止大表使用JOIN查询，禁止大表使用子查询

解读：会产生临时表，消耗较多内存与CPU，极大影响数据库性能

（29）禁止使用OR条件，必须改为IN查询

解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮助实施查询优化呢？

（30）应用程序必须捕获SQL异常，并有相应处理

总结：大数据量高并发的互联网业务，极大影响数据库性能的都不让用，不让用哟。

这个东西仔细看了下确实收获匪浅，可以详细看看，这个就是实际开发中底层优化上的一些条件

总结下来一点，拒绝全盘扫描（

所谓的镀金就是这样，你去那些大厂，他们的数据量是非常大的，你进去就学他们处理这些问题的方法，后面你自己才会

言归正传，所以我们要尽量不用外键约束，怎么删除呢

alter table goods drop foreign key 外键名称;

这个外键名称就是你show create table goods;出来的那个，他给你的外键名称

那么这个删了你就要在程序里自己做判断了，但是他做判断难道没有你程序快吗？

还真是这样，因为MySql有个限制了他的速度，他的大部分数据都是在硬盘上操作的，但是你的程序是在内存上操作的

CPU，次之内存，最后才是硬盘，固态硬盘要远远大于机械硬盘

所以实际开发里经验很重要，钱很重要，但是不能解决一切，找个好老大带你跑项目避坑是很重要的

好，开始干jing_dong的模拟

小技巧：num = input() if num == "1"和num = int(input()) if num==1比起来哪个好，前面的好，因为如果输的是字母那就废了

然后就是这里，07这个，视频里说直接commit提交操作就可以，不用像GPT那样改什么事务级别

增删改都会涉及到数据库的操作，所以这仨要commit，但是查不用，而且自动增长的值是会提交之前你写入的时候就在增长了，防止并发

顺便，你可以提交也可以回滚，conn.rollback()就可以了

然后这个作业嘛，就等后面PRTS改造的时候融入进去叭

就登录的那里改用数据库，然后爬虫如果可行的话也加进去吧，剩下俩小时就整点飘的，看看Sql高级把这一章完结了吧

下面这些内容，特别是事务和索引，这些是面试特别容易被问到的（实际开发我不知道奥，但是GPT说还是蛮重要的，但是用的还没有前面俩多

索引：我想加速度

视图：我的程序都写好了，但是我部署到另外电脑上的时候想改数据库，但是程序不能变

事务：高并发防止事情出错

这里面的不需要背，不需要详细到Sql语句上，用的时候查一下

1. 视图-->虚拟的表

程序不能改，但是数据库要动怎么办

就是建立一个虚拟的，抽象的表，程序根据这个表来写，然后再用这个表对应具体的数据库，起到了隔离的作用

这个sql语句很简单，就create view xxxx as <这里是你要求查询出来的结果的sql语句>

这个完了之后tables里面就能看到了，删除的话也是drop view不是drop table，原数据改的时候虚拟表一定改，但是他只能查，不能改

2. 事务-->操作序列(python里自动默认开启了事务的)

就是一个操作序列，要么同时都执行完，要么度不执行，他是一个不可分割的工作单位

四大特性(ACID)，面试经常会问，总之就是保证了将来数据百分之百成功的一种方式

原子性(Atomicity)，一致性(Consistency)

隔离性(Isolation)，持久性(Durability)

书里面大概是这个意思：我先开个头，如果中间任何操作出错，不成功都回滚，如果全都成功了才提交

扩展：有本书《高性能MySql》这个里面有写事务的四种隔离等级，如果想可以去看看

两个事务，类似于时停了，单独分支出了一个位面，这个位面在回归前做的任何操作都不受其他事务影响，回归的一瞬间在原有的基础上计算结果

所以同时开俩事务，先提交的那个改变结果，后的那个改变前事务改变后的事实结果

3. 索引-->存储引用的空间

一种特殊的数据结构，存储了某些数据的引用，在C里面就是指针

就好比是书前面的目录，这个东西是帮助你找东西，不是把所有东西塞里面

扩展：set profiling = 1开启运行时间检测，show profiles显示时间

为title_index的title列建立索引，索引表的名字为test_index

create index title_index on test_index(title(10))

title是字符串型之类的直接写个10，就是他的长度，int之类的就不用

这个加了之后时间解决差了两个数量级，而且你差还是原来的句子

索引，视图这俩都是为了查询用的，因为查的次数要远远大于修改的次数，就比如你看QQ空间，你可能看一百次都不一定发一次空间，这个比例差距相当大

原理跟现实中的一样，都是通过缩小数据的规模来实现的，举个例子：当我建立索引的时候，在程序里就建立了一棵树

面试考数据结构一堆，什么乱七八糟的排序啥的，二叉树啊一堆的，实际工作基本不用，所以这个东西不常用，不用特别去学，如果以后要工作了可以再去学

这里上面又一个图，我就不超下来了，我截个图吧，总结就是分治法，万物起源分治法

还有就是，我写程序专门写的速度很慢，然后后面他找我再优化速度就另外收钱（

就很简单的一个，让你优化，你就把time.sleep(10)改成time.sleep(2) HHH

然后你就可以给上面报，我优化了五分之四的时间，我厉害吧！

注意的是，如果数据量大而且经常读取才建索引，如果量不大就不用建

xshell，之前安装的那个软件，就是拿来连接虚拟机的，这里我就不弄了，视频是01-视图