Accelerate the DB
### Quick Review
inner join 根據條件去搜尋的話,並不會出現 null 的狀況
lefr|right (outer) join 去搜尋的話,會根據左表或是右表的的標準去顯示,代表有可能出現 null
### Index Manipulate
`show index from table_name`
`drop index index_name on table_name`
`ALTER table table_name add index index_name (filed_1, field2);`
### Index Explaination
[MySQL 覆盖索引](https://yq.aliyun.com/articles/62419)
[mysql百万级数据量根据索引优化查询速度](https://blog.csdn.net/qq_37348649/article/details/78822705)
[B-Tree介紹以及插入演示](https://zhuanlan.zhihu.com/p/24309634)
### DB Architecture
[大型網站架構設計](https://www.kancloud.cn/kancloud/high-scaling-structure-notes/50214)
### Example join test
```
create table `user` (
`id` bigint(18) unsigned not null auto_increment,
`name` varchar(200) not null,
`sex` varchar(200) not null,
`age` int(10) not null,
`group` varchar(100),
primary key (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
create table `user_family` (
`id` bigint(18) unsigned not null auto_increment,
`user_id` bigint(18) unsigned not null,
`dad_name` varchar(200),
`mom_name` varchar(200),
`group` varchar(100),
primary key (`id`),
foreign key (`user_id`) references user(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
select * from user as u, user_family as f where u.id = f.id;
select * from user as u inner join user_family as f on u.group = f.group and u.id = f.user_id limit 15;
select * from user as u left outer join user_family as f on u.group = f.group and u.id = f.user_id limit 15;
select * from user as u right outer join user_family as f on u.group = f.group and u.id = f.user_id limit 15;
select * from user where id > 1000 and id < 1010 union select * from user where `id` in (select user_id from user_family where `group` is not null) limit 50;
```