个人总结之MySQL基础知识

MySQL

一、SQL基础

SQL语句主要分为以下四大类：

​ 1）数据定义语言DDL

​ 主要有create（创建）,drop（删除）,alter（修改）操作，对逻辑结构进行操作，包括表结构，视图和索引。

create database dbname;//创建数据库
create table tbname(
id number(10)not null  primary key,
username varchar(30)
);//创建表并设置主键
drop table tbname;//删除表
alter table tbname rename newname;//修改表名
alter table tbname add column age int(3);//添加一个字段
alter table tbname drop column age;//删除一个字段
alter table tbname change age AGE int(4);//字段改名
alter table tbname add birthday date after age;//在某个字段之后踢啊半差一个字段
alter table tbname modify age int(3) first;//将某个字段放在最前面

​ 2）数据查询语言DQL

​ 主要是select查询操作

​ 3）数据操纵语言DML

​ 主要有insert,update,delete（增、删、改）操作

insert into tbname(column1,column2...) values(value1,value2...);//插入一条数据
insert into tbname(column1,column2...) values(value1,value2,value3..),(value1,value2,value3..)..;//插入多条数据
update tbname set age=18 where id=1;//根据某个条件修改某个数据
delete from tbname where ename='dewi';//根据某个条件删除某条数据

​ 4）数据控制语言DCL

​ 主要有grant（给用户授权）,revoke（撤销授权）,commit,rollback，对数据库安全性能完整性操作

​ 5）事务控制语言TCL

​ commit; – 提交事务

​ rollback; – 回滚事务

二、MySQL存储引擎

一、MySQL 存储引擎概述：

​ MySQL 5.0 支持的存储引擎包括 MyISAM（默认的）、 InnoDB、 BDB、 MEMORY、 MERGE、 EXAMPLE、
NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED 等，其中 InnoDB 和 BDB 提供事务安全表，其他存储引擎都是非事务安全表。

1.存储引擎的使用：

​ 1）在创建新表的时候，可以通过增加 engine关键字设置新建表的存储引擎。

create table tbname(
......
)engine=innodb defualt charset=UTF-8;
// tbname表就是 InnoDB 存储引擎.

​ 2）使用 alter table语句，将一个已经存在的表修改成其他的存储引擎。

alter table tbname1 engine = innodb;
//表tbname1 从 MyISAM 存储引擎修改成 InnoDB 存储引擎

2.各种存储引擎的特性：

[外链图片转存失败(img-2h2wCSKr-1567580540699)(C:\Users\laoruan\AppData\Roaming\Typora\typora-user-images\1564652048730.png)]

二、常见四种存储引擎的区别：

1）MyISAM

​ MyISAM 是 MySQL 的默认存储引擎。MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以 select、insert 为主的应用基本上都可以使用，这个引擎来创建表。

每个 MyISAM 在磁盘上存储成 3 个文件，其文件名都和表名相同，但扩展名分别是：
	*.frm（存储表定义） ；
	*.MYD（MYData，存储数据） ；
	*.MYI （MYIndex，存储索引） 。
数据文件和索引文件可以放置在不同的目录，平均分布 IO，获得更快的速度。

​ **应用场景：**默认的 MySQL 插件式存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。MyISAM 是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

2）InnoDB

​ InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比 MyISAM的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

​ 1.自动增长列

​ InnoDB 表的自动增长列可以手工插入， 但是插入的值如果是空或者 0， 则实际插入的将是自动增长后的值。

​ 2.外键约束

​ MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

​ 3.存储方式

​ InnoDB存储表和索引有以下两种方式：

• 使用共享空间存储，这种方式创建的表的结构保存在。frm文件中，数据和索引保存在innodb_data_home_dir和innodb_file_path定义的表空间中，可以是多个文件。
• 使用多表空间存储，这种方式创建的表的表结构仍然保存在.frm 文件中，但是每个表的数据和索引单独保存在.ibd 中。如果是个分区表，则每个分区对应单独的.ibd文件，文件名是“表名+分区名” ，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的 IO 均匀分布在多个磁盘上。

​ **应用场景：**用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求， 在并发条件下要求数据的一致性， 数据操作除了插入和查询以外， 还包括很多的更新、删除操作，那么 InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback） ，对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。

3）MEMORY

​ MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件，格式是.frm。MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用 HASH 索引，但是一旦服务关闭，表中的数据就会丢失掉。

​ MEMORY 类型的存储引擎主要用在那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。

​ **应用场景：**MEMORY：将所有数据保存在 RAM 中， 在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。MEMORY 的缺陷是对表的大小有限制，太大的表无法 CACHE 在内存中，其次是要确保表的数据可以复，数据库异常终止后表中的数据是可以恢复的。MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。

4）MERGE

​ MERGE 存储引擎是一组 MyISAM 表的组合， 这些 MyISAM 表必须结构完全相同， MERGE表本身并没有数据，对 MERGE 类型的表可以进行查询、更新、删除的操作，这些操作实际上是对内部的实际的 MyISAM 表进行的。

**应用场景：**用于将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上， 可以有效地改善MERGE表的访问效率。 这对于诸如数据仓储等VLDB环境十分适合。

三、常用SQL优化

对于sql的优化主要就是下面几个方面的优化：

​ 1.避免全表扫描

​ 2.避免索引失效

​ 3.避免排序，不能避免，尽量选择索引排序

​ 4.避免查询不必要的字段

​ 5.避免临时表的创建，删除

一.插入数据

​ 1.导入大批量数据

这个需要先关闭索引，插入数据后再打开索引。

1.1.针对MyISAM引擎可以通过以下方式提高导入数据效率，但是Innodb并不能提高这个效率。

alter table user disable keys; //关闭所有索引
alter table user enable keys;  // 开启索引

1.2.对于Innodb类型的表，由于Innodb的表是根据主键的顺序保存的，所以将导入的数据按照主键的顺序，可以提高效率

//在导入数据前关闭唯一性校验也可以提高效率 set unique_checks=0
//关闭自动提交，set auto-commit=0 采用手动提交也可以提高效率

​ 2.优化insert语句

2.1如果同时插入多行，采用多个值表更好。例如：

inset into test values(1,2),(1,3),(2,3);

二.排序 order by

​ 目标：尽量减少额外的排序，通过索引直接返回有序数据。排序情况有以下两种结果：

1、通过有序索引顺序扫描直接返回有序数据。这里分析结果Extra：*Using index ;*例如：

explain select customer_id from customer order by store_id;  
//这里customer_id是主键，store_id是索引

​ 2.、通过返回数据进行排序，explain 返回的Extra结果是Using fileSort。这个是不好的。例如：

explain select * from cutomer order by store_id;//store_id是一个索引
/**
方案：1>:where和order by使用相同的索引，并且order by 的顺序和索引顺序（如果复合索引）相同
 	2>:order by 字段都是同为升序或者降序，否则索引不生效，使用了Using fileSort
 	3>:当filesort索引无法避免情况下，想办法加快fileSort操作，设计到两次扫描算法和一次扫描算法，看情况使用某一种。
*/

​ 两次扫描算法：根据条件取出排序字段和行指针信息，之后再排序区排序，如果排序区不够在新建临时表。完成排序后在通过指针回表读取记录。

一次扫描算法：一次性取出满足条件的行的所有字段。然后再排序区完成排序后，直接输出数据，这个排序的时候内存消耗比较大，但是相比两次效率又高。

三.分组group by 优化

​ 默认情况下group by 对字段分组的时候，会排序。这和在查询order by 的情况类似。

​ 1. 如果在在分组的时候不需要排序，最好关掉排序命令：order by null。例如：

explain select name sum(money) from user group by name order by null;

四. 优化嵌套查询

​ 某些子查询可以通过join来代替。理由：join不需要在内存中创建一个临时表来存储数据。

explain select * from customer 
where customer_id 
not in (select customer_id from payment);
//上面的语句用下面的语句代替
explain select * from customer a 
left join payment b 
on a.customer_id=b.customer 
where b.customer_id is null;

五.优化or条件

​ 1. 对于单独的两个索引

explain select  * from sales where id=2 or year =1998;    //id和year都是索引

​ 这两个索引都是被使用到了的，但是这个查询时分别对两个条件进行查询，然后union两个结果的。

​ 2. 如果对复合索引(id 和year是复合索引)，那么就不能使用到了索引，采用的全文扫描。

六.优化分页查询

​ 常见的分页查询，查询到“limit 2000,20”;时候就会出现先查询前面2200个，然后抛弃前面2000个，造成查询和排序代价非常大。优化方式如下：

//1.在索引上完成排序分页的操作。根据主键关联回原表查询所需要的其他内容。例如：
explain select a.last_name , a.first_name from user a 
inner join (select id from user order by id limit 2000,20) b on a.id=b.id;
//2.把limit查询转换成某一个位置查询。可以通过把上一页的最后一条记录记下来。
//例子：
select * from payment order by rental_id desc limit 2000,20;    //这样效率非常低下
//如上面是通过 rental_id 降序来排列的 ，那么我们在查询 limit 1800,20时候，记录下2000位置的rental_id,加入这里的rental_id的值，假设这里的值是“5000” ，那么sql语句就可以转换成如下：
select * from payment where rental_id < 5000 order by rental_id desc limit 10;

​ *注意事项： 这个只适合在排序字段不会出现重复值的特定环境，能够减轻分页翻页的压力，如果排序字段出现重复值，那么就会出现记录丢失。

七.使用SQL提示

​ 常见的SQL提示如下：

//1. use index  这个表示希望sql去参考的索引，就可以让mysql不在考虑其他可用的索引了
explain select count(*) from user user index(idx_user_id);

//2.ingore index 只是单纯的希望mysql忽略一个索引，或者多个。例如：
explain select count(*) from rental ignore index(idx_rental_date)

//3.force index 强制mysql使用一个索引
explain select * from user  use index (idx_fk_inventory_id) where inventory_id >1;

​ 默认inventory_Id都是大于1的，所以一般会全表扫描，如果强制使用这个所以，那么msyql还是会使用这个索引。

八.查询的一些注意项

​ 1.慎用模糊查询，使用 like 两边加“%”–造成索引失效；左边没有%,这个索引不会失效

​ 2.尽量不要使用select * ,使用需要的具体字段查询

​ 3.不要在查询条件where后面对字段做函数处理

​ 4.优先使用union all ,避免使用union.

​ UNION 因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。一般来说，如果使用UNION ALL能满足要求的话，务必使用UNION ALL。还有一种情况，如果业务上能够确保不会出现重复记录

​ 5.使用not exist代替not in

​ 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引

​ 6.in 和 exist 区别选择。

​ in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。因此，in用到的是外表的索引， exists用到的是内表的索引。

​ 如果查询的两个表大小相当，那么用in和exists差别不大，

​ 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in

​ 7.避免在索引列上作如下操作，当在索引列上使用如上操作时，索引将会失效，造成全表扫描

• 避免在索引字段上使用<>，!=
• 避免在索引列上使用IS NULL和IS NOT NULL
• 避免在索引列上出现数据类型转换（比如某字段是String类型，参数传入时是int类型）

四、事务控制

简单来说：一个Session中所进行所有的操作，要么同时成功，要么同时失败

ACID — 数据库事务正确执行的四个基本要素

• 包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

一、事务隔离级别

1. Serializable【可避免脏读，不可重复读，虚读】
2. Repeatable read【可避免脏读，不可重复读】
3. Read committed【可避免脏读】
4. Read uncommitted【级别最低，什么都避免不了】

• **脏读：**一个事务读取到另外一个事务未提交的数据

  例子：A向B转账，A执行了转账语句，但A还没有提交事务，B读取数据，发现自己账户钱变多了！B跟A说，我已经收到钱了。A回滚事务【rollback】，等B再查看账户的钱时，发现钱并没有多。

---

• **不可重复读：**一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修注：A查询数据库得到数据，B去修改数据库的数据，导致A多次查询数据库的结果都不一样【危害：A每次查询的结果都是受B的影响的，那么A查询出来的信息就没有意思了】

---

• **虚读(幻读)：**是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

  注：和不可重复读类似，但虚读(幻读)会读到其他事务的插入的数据，导致前后读取不一致

---

简单总结：脏读是不可容忍的，不可重复读和虚读在一定的情况下是可以的【做统计的肯定就不行】。

五、数据库的乐观锁和悲观锁

一、乐观锁和悲观锁是并发控制主要采用的技术手段。

• 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作

  • 在查询完数据的时候就把事务锁起来，直到提交事务
  • 实现方式：使用数据库中的锁机制

• 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

  • 在修改数据的时候把事务锁起来，通过version的方式来进行锁定

  注：和不可重复读类似，但虚读(幻读)会读到其他事务的插入的数据，导致前后读取不一致

---

简单总结：脏读是不可容忍的，不可重复读和虚读在一定的情况下是可以的【做统计的肯定就不行】。

五、数据库的乐观锁和悲观锁

一、乐观锁和悲观锁是并发控制主要采用的技术手段。

• 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
  • 在查询完数据的时候就把事务锁起来，直到提交事务
  • 实现方式：使用数据库中的锁机制
• 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
  • 在修改数据的时候把事务锁起来，通过version的方式来进行锁定
  • 实现方式：使用version版本或者时间戳