Everyday sentence
Time is like a river, the left bank is unable to forget the memories, right is worth grasp the youth, the middle of the fast flowing, is the sad young faint. There are many good things, buttruly belong to own but not much. See the courthouse blossom,honor or disgrace not Jing, hope heaven Yunjuanyunshu, has no intention to stay. In this round the world, all can learn to use a normal heart to treat all around, is also a kind of realm!
拎拎概念
InnoDB和myISAM是mysql数据库的数据库引擎
ACID是据库事务正确执行的四个基本要素的缩写
PostgreSQL是一个自由的对象-关系数据库服务器，功能类似于Mysql。mysql是Oracle公司较于oracle数据库更轻量级的产品，而postgre是由一批庞大的志愿者进行维护的
下面展示一个基本的从建表，插入，到查询的过程：
CREATE TABLE orders(
id int auto_increment PRIMARY key,
user_id varchar(50),
item_id varchar(50),
pay_time varchar(50),
item_num int
);
insert into orders (user_id, item_id, pay_time, item_num)
VALUES ("001","201","2018-08-31 00:00:01",1),
("002","203","2018-09-02 12:00:02",2),
("003","203","2018-09-01 00:00:01",1),
("003","203","2018-09-04 09:10:30",1);
SELECT o.user_id, o.item_id,
case when o.pay_time is not Null then 1 else 0 end as "已购买",
case when o.pay_time is not NUll and f.fav_time is Null then 1 else 0 end as "购买未收藏",
case when o.pay_time is Null and f.fav_time is not Null then 1 else 0 end as "收藏未购买",
case when o.pay_time is not Null and f.fav_time is not Null then 1 else 0 end as "收藏且购买"
from orders o left join favorites f on o.user_id = f.user_id and o.item_id = f.item_id
UNION
SELECT f.user_id, f.item_id,
case when o.pay_time is not Null then 1 else 0 end as "已购买",
case when o.pay_time is not NUll and f.fav_time is Null then 1 else 0 end as "购买未收藏",
case when o.pay_time is Null and f.fav_time is not Null then 1 else 0 end as "收藏未购买",
case when o.pay_time is not Null and f.fav_time is not Null then 1 else 0 end as "收藏且购买"
from orders o right join favorites f on o.user_id = f.user_id and o.item_id = f.item_id
order by user_id, item_id;
MYSQL索引
是帮助MYSQL高效获取数据的数据结构，索引本身也很大，往往存储在磁盘上，可能单独存储在索引文件中，也可能和数据一起存储在数据文件中
通常说的索引，包括聚集索引，覆盖索引，组合索引，前缀索引，唯一索引等，默认都是用B+树结构组织
优点是提高数据检索效率，降低数据库IO成本；降低了数据的排序成本，降低了CPU消耗
缺点是占据磁盘空间，降低更新表的效率
创建索引用create (unique)index index_name on table(column(length))或者 alter语句
删除索引用drop index xx on xx
查看索引用show index xx from xx
索引原理
不同的存储引擎会使用不同的索引
MyISAM和InnoDB存储引擎只支持B+树索引，Memory/Heap存储引擎只支持Hash和B树索引
B树和B+树
B+树只在叶子结点上产生数据，
B树高度一般在2-4，三层树可以支撑20G的数据，四层可以支撑几十T
MyISAM是非聚集索引，数据和索引不在一起。主键索引在叶子结点存储数据指针的地址，次要索引同理
InnoDB是聚集索引，要求表必须有主键，主键索引数据直接挂在主键的叶子结点下，次要索引叶子结点上挂的是主键。所以在 * + where检索辅助索引的时候，需要回表查询检索两次，第一次找主键，第二次才取到数据。在组合索引 + where的时候就不需要回表，称为覆盖索引
Comments