MySQL学习笔记2

Group by 分组查询

连接查询

单表查询如果不满足范式设计一和范式设计二，则需要进行单边的拆分操作。进行多表查询。

内部连接

外部连接：左连接和右连接

给表名个别名

多表查询

内连接查询（inner(可省略) join）

语法：SELECT a.属性名1,a.属性名2,...,b,属性名1,b.属性名2... FROM table_name1 a inner join table_name2 b on a.id = b.id where a.属性名 满足某些条件;

预置条件：uid:1 cid:2

select score from exame where uid=1 and cid=2;

select a.uid,a.name,a.age,a.sex from student a where a.uid=1;

select c.score from exame c where c.uid=1 and c.cid=2;

// on a.uid=c.uid 区分大表 和 小表，按照数据量来区分，小表永远是整表扫描，然后去大表搜索

// 从student小表中取出所有的a.uid，然后拿着这些uid去exame大表中搜索

// 对于inner join内连接，过滤条件写在where的后面和on连接条件里面，效果是一样的

select a.uid,a.name,a.age,a.sex,c.score from student a

inner join exame c on a.uid=c.uid where c.uid=1 and c.cid=2;

select b.cid,b.cname,b.credit from course b where b.cid=2;

select a.uid,a.name,a.age,a.sex,b.cid,b.cname,b.credit,c.score

from exame c

inner join student a on c.uid=a.uid

inner join course b on c.cid=b.cid

where c.uid=1 and c.cid=2;

select a.uid,a.name,a.age,a.sex,b.cid,b.cname,b.credit,c.score

from exame c

inner join student a on c.uid=a.uid

inner join course b on c.cid=b.cid

where c.cid=2 and c.score>=90.0;

select b.cid,b.cname,b.credit,count(*)

from exame c

inner join course b on c.cid=b.cid

where c.score>=90.0

group by c.cid

having c.cid=2;

select b.cid,b.cname,b.credit,count(*) cnt

from exame c

inner join course b on c.cid=b.cid

where c.score>=90.0

group by c.cid

order by cnt;

SELECT a.属性名1,a.属性名2,...,b,属性名1,b.属性名2... FROM table_name1 a inner join table_name2

b on a.id = b.id where a.属性名 满足某些条件;

外连接查询：包括左连接查询和右连接查询

对于相同偏移，不同字段数量对查询的数量是不同的

可以通过采用加索引字段进行过滤效率较高（比如加where ，去掉偏移量）

Select * from user 150000 , 10; 进行优化如下两种

1）添加索引：Select * from user where id>150000 limit 10;

2）使用内连查询：Select a.id, a.email, a.password from user a inner join (select id from user limit 150000,10) b on a.id=b.id;  红色部分为临时表

使用外链接好处是不会产生中间表

左连接查询

// 把left这边的表所有的数据显示出来，在右表中不存在相应数据，则显示NULL

select a.* from User a left outer join Orderlist b on a.uid=b.uid where a.orderid is null;

select a.*, b.* from student a inner join exame b on a.uid=b.uid

右连接查询

// 把right这边的表所有的数据显示出来，在左表中不存在相应数据，则显示NULL

select a.* from User a right outer join Orderlist b on a.uid=b.uid where b.orderid is null;

select * from student where uid not in (select distinct uid from exame);

通过select dittinct uid from exame 产生一张中间表存储结果供外面的sql来查询 not in 对于索引的命中并不高（有些资料说not in 不能提供索引）

存储引擎

MyISAM的表在磁盘上存储3个文件，其文件名和表名都相同，扩展名分别为：

.frm（存储表定义）

.MYD（存储数据）

.MYI（存储索引）

InnoDB存储引擎存储在同一个文件，所以InnoDB在表磁盘上只有两个文件，文件名和表名相同，扩展名分别是：

.frm（存储表定义）

.idb（索引和数据）

MeMORY存储引擎使用内存中的内容来创建，每个MEMORY表只对应一个磁盘文件，格式是.frm

MEMORY 类型的表访问非常快，因为它的数据是放在内存中的，并且默认使用 HASH 索引（不适合做范围查询），但是一旦服务关闭，表中的数据就会丢失掉。

锁机制：表示数据库在并发请求访问的时候，多个事务在操作时，并发操作的粒度。

B-树索引和哈希索引：主要是加速SQL的查询速度。

外键：子表的字段依赖父表的主键，设置两张表的依赖关系。

事务：多个SQL语句，保证它们共同执行的原子操作，要么成功，要么失败，不能只成功一部分，失败需要回滚事务。

索引缓存和数据缓存：和MySQL Server的查询缓存相关，在没有对数据和索引做修改之前，重复查询可以不用进行磁盘I/O（数据库的性能提升，目的是为了减少磁盘I/O操作来提升数据库访问效率），读取上一次内存中查询的缓存就可以了

索引

是建立在数据库表中的某些列上的。因此在创建索引的时候，应该考虑在那些列上可以创建索引，那些列上不能创建索引

索引分类：物理上聚集性索引，非聚集型索引

普通索引：

唯一索引：

主键索引：使用Primary Key修饰的字段会自动创建索引（MYISAM、InnoDB），此外被uniquen修饰也是

单列索引：在一个字段上创建索引

多列索引：在表的多个字段上创建索引（uid+cid 多列索引必须使用到第一个列，才呢呢个用到多列索引，否则索引用不上）

索引的特点：

一次sql查询只能用一个索引

索引需要占用物理空间

对表数据的增删改查，索引也需要动态维护

索引存在自动排序：可以帮助服务器避免排序和创建临时表，索引可以将随机IO变成顺序IO

索引提高查询索引

索引创建和删除

1、创建表的时候创建：

CREATE TABLE index1(id INT,

name VARCHAR(20),

sex ENUM('male', 'female'),

INDEX(id,name));

2 在已经创建的表上创建索引

CREATE [UNIQUE] INDEX 索引名 ON 表名（属性名（length） [ASC | DESC]);

CREATE INDEX btree_index ON example_table (age);   将age添加B树索引

3、 删除索引：DROP INDEX 索引名 ON 表名;

MySql 索引优化：

1. 经常需要用到where过滤的字段，应该加索引优化
2. 给字符串列创建索引的时候，不管这个索引列字符串的长度有多长，也不好。即索引列的值越长越不好
3.  Mysql查询过滤的时候存在类型转换。如果原来的索引存在数据类型转换，则不能用索引了，这个转换的字段用到mysql的函数也无法用到索引了

B-树介绍

好处：非常少的磁盘IO

二叉树一个节点里面一个数据，两个指针。B树里面一个m个节点，一个节点里面有许多数据域，许多指针域

MYIASM和InoDB底层是B+树

Memory底层是哈希索引

SeleCT* from student where uid=5执行流程：

Uid有索引 —> 存储引起—>kernel—>磁盘IO（读取引文件）—>内存上 —>用索引的数据结构构建B树加速搜索

一次磁盘I、O的读取磁盘块内容，刚好存储在B树的一个节点中

B+树

1）B-树的每一个节点，存了关键字和对应的数据地址，而B+树的非叶子节点只存关键字，不存数据地址。B+树的每一个非叶子节点存储的关键字是远远多于B-树的，B+树的叶子节点存放关键字和数据。从树的高度上来说，B+树的高度要小于B-树，使用的磁盘I/O次数少，因此查询会更快一些。

2）B-树由于每个节点都存储关键字和数据，因此离根节点进的数据，查询的就快，离根节点远的数据，查询的就慢；B+树所有的数据都存在叶子节点上，因此在B+树上搜索关键字，找到对应数据的时间是比较平均的，没有快慢之分。

3）在B-树上如果做区间查找，遍历的节点是非常多的；B+树所有叶子节点被连接成了有序链表结构，因此做整表遍历和区间查找是非常容易的。

INNODB存储引擎：数据和索引存续在一起(.ibd文件），因此又可以叫做聚集索引。使用B+树作为索引结构。

下图是主键索引对应的存储结构与耳机索引树

回表：

Select * from student where name = ‘linfeng’

这段语句牵扯到回表

1. 搜多name的二级索引树，找到linfeng对应的主键uid：4
2. 再拿uid=4回表，在主键索引上搜索uid哪一行的记录

上面的需要回表，下面的直接查找name不需要回表，直接用name的索引。

这个因为需要拿age，age没有添加索引，所以需要通过获得的uid再去搜索age，因此需要回表

碰到using filesort一定要优化。

创建多列索引：create index name_age_idx on student(age,name)

对于多列索引，只用使用第一列索引才有效（即只要包含age索引有效，只有name则无效）

MyISAM存储引擎：数据和地址是分开放的（非聚集式索引）

哈希索引：

Create index nameidx on student（name）using hash；

Show indexs from student；

哈希索引只能进行等值比较，范围搜索、、前缀搜索不适合（因为这些操作需要搜索整个表），因此hash索引适合在内存上的存储引擎（MeMORY）

InnoDB 自适应hash索引

InnoDb监测到同样的二级索引不断被使用，那么会根据这个二级索引，在内存上根据二级索引树（B+树）上的二级索引值，在内存上构建一个哈希索引，来加锁搜索。如上图构建的hash索引通过黄色线直接可以搜索数据

索引常见问题

1. 查询有多个字段，其中有部分建立索引：a=1 and b=2 and c=3; a和b分别有单独的索引。Mysql则按照那个索引出来的数据最少选择那个索引。
2. 一次查询只能用一个索引
3. 可以强制指定使用某个索引select * from xxx FORCE INDEX（IX_addtime）；