Mysql 基础知识点

写在前面：该篇文章在博文" https://thinkwon.blog.csdn.net/article/details/104778621"的基础上进行编写和总结，如果有兴趣的同学可以阅读原博文，原博文描述的更全面

什么是索引？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

索引有哪些优缺点？

索引的优点

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
• 空间方面：索引需要占物理空间。

索引使用场景

• where后的字段加索引，查询效率会有明显的提升
• order by后的字段加索引，查询效率会有明显的提升，建立索引alter table 表名 add index(字段名)，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。
• 对join语句匹配关系（on）涉及的字段建立索引能够提高效率
• 注意：如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。但值得注意的是不要想着为每个字段建立索引，因为优先使用索引的优势就在于其体积小。

索引类型

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

• 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
• 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

• 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
• 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

全文索引： 是目前搜索引擎使用的一种关键技术。

• 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

创建索引的原则

1） 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

题外：百万级别或以上的数据如何删除

1. 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
2. 然后删除其中无用数据（此过程需要不到两分钟）
3. 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
4. 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

六种关联查询

• 交叉连接（CROSS JOIN）

• 内连接（INNER JOIN）

1. 等值连接：ON A.id=B.id
2. 不等值连接：ON A.id > B.id
3. 自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

• 外连接（LEFT JOIN、RIGHT JOIN）

1. 左外连接：LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN
2. 右外连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN

• 联合查询（UNION、UNION ALL）

1. 就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并
2. 如果使用UNION ALL，不会合并重复的记录行
3. 效率 UNION 高于 UNION ALL

• 全连接（FULL JOIN）

1. MySQL不支持全连接，Oracle支持
2. 可以使用LEFT JOIN 和UNION和RIGHT JOIN联合使用

子查询

注：一般在子查询中，程序先运行在嵌套在最内层的语句，再运行外层。因此在写子查询语句时，可以先测试下内层的子查询语句是否输出了想要的内容，再一层层往外测试，增加子查询正确率。否则多层的嵌套使语句可读性很低。

一、where型子查询（指把内部查询的结果作为外层查询的比较条件）

① 如果是 where 列 =（内层 sql） 则内层 sql 返回的必须是单行单列，单个值。

select name,age,sex from student where age = 20; select name,age,sex from student where age = (select max(age) from student);

二、form型子查询（把内层的查询结果当成临时表，供外层sql再次查询）

select name,age from student select * from (select name,age from student) as 自定义临时表名

三、in子查询（内层查询语句仅返回一个数据列，这个数据列的值将供外层查询语句进行比较）

select name,age,sex from student id in (select id from student where sex='男')

四、exists子查询（遍历外层的查询结果，拿到内层，看内层是否成立，内层查询至少返回一条数据就为true，外层的该条遍历数据就会存入结果集，如果内层返回false，外层的该条遍历数据被丢弃）

select name,age,sex from student where exists (select id from student where age=22) select name,age,sex from student where sex='男' and exists (select id from student where age=22)

五、any子查询（只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件）

select name,age,sex from student id > any (select id from student where age=22)

六、all子查询（内层子查询返回的结果需同时满足所有内层查询条件）

select name,age,sex from student id > all(select id from student where age=22)

exists和in区别

1、exists：

对外表用loop遍历查询，每次遍历的时候都会去执行子查询，如果子查询未查询到至少一行数据，则返回false，遍历外表的这条数据就会被丢弃，如果遍历的时候执行子查询查询到至少一行数据，不在乎是否是什么数据，只要有数据查询出来就返回true，遍历外表的这条数据就会被放到结果集中，直到遍历完外表。

not exists与exists相反，也就是当exists条件有结果集返回时，loop到的记录将被丢弃，否则将loop到的记录加入结果集

-- 返回年龄大于20且性别为男的数据
select * from student where sex='男' and exists(select id from student where age>20);

总结：如果外表结果集有n条记录，那么exists查询就是将这n条记录逐条取出，然后判断n遍exists条件 。

2、in：

先执行子查询，将子查询结果放到临时表中，然后通过临时表的数据去遍历外表，通过关联关系得到外表需要的数据，可以理解为多个or条件拼接

select * from student where id in(1,2,3) ;
-- 等同于 
select * from student where id = 1 or id = 2 or id = 3 ;


-- not exists与exists相反 
select * from student where id not in(1,2,3)  ;
-- 等同于 
select * from student where id != 1 or id != 2 or id != 3 ;

总的来说，in查询就是先将子查询条件的记录全都查出来，假设结果集为B，共有m条记录，然后在将子查询条件的结果集分解成m个，再进行m次主查询

注意：in查询的子条件返回结果必须只有一个字段，但exists没有这个限制

select * from student where id in(select id from student where name='张三') ;
-- 如果是下面这样则会报错 
select * from student where id in(select id,name from student where name='张三') ;


select * from student where id exists(select id from student where name='张三') ;
-- 也可以写成下面这样，不影响结果 
select * from student where id exists(select id,name from student where name='张三');

3、in与exists选择：

其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，

如果是exists，那么以外层表为驱动表，先被访问；如果是IN，那么先执行子查询，

所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了。

总结：从查询上来说，数据量小的返回查询结果会更快，如果外表数据量大，内表数据量小，那么内表查询结果会更快，选择使用IN；

相反，如果外表数据量小，内表数据量大，那么外表查询结果会更快，选择以外表驱动为主的exists

varchar与char的区别

char的特点

• char表示定长字符串，长度是固定的；
• 如果插入数据的长度小于char的固定长度时，则用空格填充；
• 因为长度固定，所以读写速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
• 对于char来说，最多能存放的字符个数为255，和编码无关

varchar的特点

• varchar表示可变长字符串，长度是可变的；varchar(50)中的50表示最多存放50个字符，但是在varchar(50)和varchar(100)存储hello所占用的空间是一样的，但是varchar(100)在排序时会消耗更多内存；
• 插入的数据是多长，就按照多长来存储；
• varchar在读写方面与char相反，它读写慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
• 对于varchar来说，最多能存放的字符个数为65532；

总结：性能方面char更优，节省磁盘空间varchar更优，视项目具体需求设计。

mysql中int(10)和char(10)以及varchar(10)的区别

• int(10)中10表示展示数据的长度，不是指存储数据的大小，存储范围不变，不影响内部存储，只是影响当int类型字段带有zerofill定义的时候，如果值不等于x长度，会在前面自动补0，易于展示，如展示为0001；
• chart(10)中10表示10位固定字符串，不足补空格 最多10个字符，空格表示占位不算一个字符，占用更多的存储空间；
• varchar(10) 中10表示10位可变字符串，最多10个字符，空格也按一个字符存储；

FLOAT和DOUBLE的区别

• FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。
• DOUBLE类型数据可以存储至多18位十进制数，并在内存中占8字节。

drop、delete与truncate的区别

纬度	drop	truncate	delete
类型	属于DDL（数据定义语言 ，对数据库中的某些对象(例如，database,table)进行管理 ）	属于DML（数据操纵语言 ，对数据库中的数据进行一些简单操作，如insert,delete,update,select等 ）	属于DML（数据操纵语言 ，对数据库中的数据进行一些简单操作，如insert,delete,update,select等 ）
回滚	不可回滚	不可回滚	可回滚
删除内容	删除表结构、表数据、索引、权限	只是删除表中的所有数据，保留表结构	只是删除表数据（满足某个条件的数据或表中的所有数据），保留表结构
删除速度	最快	快	最慢
总结：不需要某个表的时候使用drop，保留表结构，删除所有数据的时候使用truncate，想删除某部分数据的时候使用delete