SQL优化一.插入优化二.主键优化1页分裂此时为主键乱序插入50应该放在47后面但如果47所在的页已经放不下50这个数据了这时候mysql会创建一个新页并找到47所在页的50%的位置把50%后的数据放在新页中再在后面插入新数据最后调整链表指针保持顺序这种现象叫做页分裂。2页合并此时 2#page 中删除记录达到了阈值会判断相邻两页是否可以合并合并完如下3主键设计原则1.满足业务需求的情况下尽量降低主键的长度因为聚簇索引一张表只有一个但二级索引可以有很多个二级索引的叶子节点存放的是主键主键过长会占过多磁盘空间而且搜索的时候会浪费磁盘 I/O2.插入数据时尽量选择顺序插入选择使用自增主键顺序插入时一个页写不下了就写下一个页就行了乱序插入时会有页分裂现象3.尽量不要使用 UUID 做主键或使用其他自然主键如身份证号。因为这些数据没有顺序就会乱序插入而且长度通常较长4.业务操作时避免对主键的修改三.order by 优化创建索引前使用的是 filesort此时给agephone创建联合索引创建索引后使用的是Using index 直接通过索引返回了有序数据根据age和phone倒序排也没有使用filesort Backward index scan反向扫描索引之前都是先根据age排再根据phone排序如果先根据phone再根据age排会怎么样呢此时也用到了 filesort 因为我们创建索引时第一个字段是age 先按phone排序违背了最左前缀法则要是按 age asc phone desc排序呢还是用到了filesort以为创建索引时是按age asc 再 phone asc 创建的此时按phone 倒序排 就需要额外的排列按age asc phone desc创建一个新的索引会怎么样呢查询索引发现该索引的Collection 是D 其他的都是A 可知此处D代表降序的意思再次查询 此时走的索引是刚才创建的索引 这次没有使用filesort按两种不同的方法排序所创建的索引的区别如下注意事项四.group by优化删掉索引后 查询profession结果如下创建联合索引后查询profession结果如下查询age 结果如下查询profession和age结果如下先用profession过滤 结果如下注意事项五.limit优化六.count优化七.update 优化视图一.介绍及基本语法介绍创建根据student的id和name字段创建视图stu_v_1创建好了左侧会有显示查询修改很明显根据左侧的数据就能看出来 修改成功使用第二种方法也修改成功删除二.检查选项cascaded视图既然是一个虚拟的表自然可以增删改查但是视图实际上并不存储数据那么如果增加一条数据数据去哪了再次根据student表重新创建视图并且成功插入一条数据结果数据插入到了student表里再次插入一条id为30的数据student表里也成功插入了这条数据但是再次对视图进行查询发现id为30的数据没有查出来这是因为我们创建这个视图时规定了where id 20我们插入的数据有的却查不到那要怎么办呢可以在后面加上with cascaded check option加上这个语句重新创建视图插入数据可以发现插入id为30的数据时报错了避免了之前的情况于是引出了检查选项这个概念视图除了基于表创建也可以基于另一个视图创建创建第一个视图时不加 with check option增删改查时不会检查where条件但要是第二个视图基于第一个视图创建时加上了with check option 给视图v2增删改查时 不仅会检查v2的条件也会检查v1的条件例子v2插入7报错因为不满足v2的id10插入26报错因为不满足v1的id20插入15成功如果又有视图v3基于v2创建且不加with check option 会怎末样呢执行过程插入11 满足id15 成功插入17 因为v3没加检查选项 不会对v3本身的条件进行检查 但是会接着检查v2的条件 因为v2加了with check option 满足接着检查v1的条件因为v2基于v1创建且加了检查选项满足成功插入 28 检查v2的条件满足检查v1的条件不满足失败三.检查选项local给v2加上with local check option对v2增删改查时同样会看他所依赖的视图有没有加检查选项但是如果他所依赖的视图 不加检查选项的话 就不会像cascaded那样检查了所有它的依赖视图的条件了四.视图的更新及视图的作用五.案例比如案例二根据三表联查创建视图后面直接查视图就能简化操作