SQL语句的优化:

1)Insert语句的优化:我们如果说向数据库中插入数据，可以从下面几个方面来进行优化:

1.1)批量插入数据，values后面加上多个括号

1.2)手动控制事务，关闭自动提交，避免大量用到事务

1.3)按照主键顺序插入

2)大批量插入数据，比如说是几百条这样的数据

我们在这里面使用load指令来进行插入

2.1)客户端连接服务端的时候:mysql --local -infile -u root -p

2.2)设置全局参数local_infile 是1，开启从本地文件导入数据的开关:set global local_infile=1

2.3)我们可以进行查看指令是否开启:select @@local_infile

2.4)执行数据:将文件上传到指定路径

我们要知道文件路径load data local infile "里面是文件路径" into table 表名 fileds terminated by ',' lines terminated by '\n'

3)主键优化:

1)在满足业务需求的情况下，尽量降低主键的长度，尽量短，因为主键多了，二级索引的叶子结点数据就会比较多，占用空间比较大，逐渐越长，二级索引的叶子节点也是越长的

2)插入数据的时候，尽量选择自增主键，按照顺序插入使用auto_increment，否则如果说不适用顺序插入，可能会产生页分裂的现象

3)尽量不要使用UUID或者其他自然主键比如说身份证号，生成的UUID就是乱序插入，身份证号长度过长浪费大量磁盘IO

4)业务操作，尽量避免对主键的修改，因为可能会修改页

索引组织表:在我们的InnoDB引擎中，表的数据都是按照主键顺序进行存放的，这种存储方式的表称之为索引组织表

1)行数据都是存储在聚簇索引上面的叶子节点上面的，数据行是存储在逻辑结构页里面的，每一个页的大小是固定的，就是16K，一个也中存储的行也是固定的，如果说插入的行再该页中存储不下去了，将会存储到下一个页里面，叶和叶之间将会通过指针来进行连接

2)其实索引简单来说，其实就是通过二分法不断减少要筛选的数据，而主键值就是筛选的标准，以尽快定位到我们需要的数据

页分裂:

页可以为空，也可以填充一半，也可以填充100%

主键顺序插入效果:

1)在磁盘中申请页，主键顺序插入

2)申请一个页，第一个页没有满，向第一个页进行插入

3)当第一个页写满之后，在进行写入第二个页，页和页之间通过指针进行连接

4)第二页写满之后，在继续向第三页进行写入

1)现在咱们想要插入一条id=50的记录，此时我们不应该创建一个第三页，把50写到里面，因为主键是按照顺序来进行排放的，也就是说索引结构的叶子节点是存放有数据的，按照顺序应该排在47之后

2)但是此时47所在的第一页的数据已经存放满了，存储不了50了，那么此时的解决办法就是开辟一个新的页3

3)但是此时并不会将50直接存入到第三页，而是将第一页中一半的数据放到第三页，然后在第三页里面插入50

但是此时的指针指向是存在问题的

所以说此时的解决方法就是改变指针的指向，重新设置链表指针

页合并:

当我们对已有的数据进行删除的时候，实际上记录并没有被物理删除，而是说记录被标记删除并且他的空间允许被其他有用的记录所进行覆盖

当我们想进行删除15,16的数据的时候，只是做了一个标记，并没有真正的进行删除 

当我们也叶子中的记录达到页的50%的时候，InnoDB会去寻找最靠近的页，前面或者后面，看看是否可以将两个页合并在一起进行优化空间使用

3)针对order by来进行优化:---通过索引

3.1)咱们MYSQL的排序方式有两种:

Using filesort:通过表的索引或者全表扫描，读取满足条件的数据行，然后在排序缓冲区中完成排序操作，所有不是通过索引直接返回结果的排序称之为FileSort排序

Using index:通过有序索引扫描直接返回有序数据，这种情况称之为using index，不需要进行额外排序，操作效率比较高，不需要在数据库中的额外缓冲区中进行排序

3.2)针对上面的操作方式，我们使用using index的效率比较高，但是使用using fileport的效率比较低，所以要尽量优化成Using index

1)根据排序字段建立合适的索引，多字段进行排序的时候，也要尊寻最左前缀法则

2)尽量使用索引覆盖

3)尽量增大排序缓冲区的大小

3.3)现在我们针对一张表上面的字段建立索引，并进行查询

create index index_user on user (age,phone);

1)select * from user order by age desc,phone desc，这种情况下出现了using index，但是咱们在MYSQL中创建的索引叶子结点是从小到大的，但是此时我们的查询顺序是从大到小，扫描的时候，就会出现反向扫描，就会出现Backword index scan，在MYSQL 8.0之后我们还可以进行创建倒序索引

1.1)但是如果查询语句是这样的话:)select * from user order by phone,age,此时额外字段就会出现Using index，Using fileport，因为此时不遵循最左前缀法则，所以说即会使用using index，也会使using fileport

1.2)如果说要是这样进行查询的话:select * from user order by phone desc,age asc，这时候就会出现Using index，Using fileport，因为当我们进行创建联合索引的时候，如果说最终没有进行指定顺序，那么查询的时候，就是一个升序，一个降序，此时就会出现Using fileport

查看buffer pool缓冲区的大小:show variables like "sort_buffer_size";

group by进行分组操作:主要是有Using temporary

1)在分组操作的时候，我们要可以根据索引来进行提高查询效率

2)在进行分组操作的时候，索引的使用也是满足最左前缀法则的(多字段)

limit进行优化:

当我们的数据量比较大的时候，如果想要进行limit分页查询，在我们进行查询的时候，越往后，分页查询的效率越低

1)select * from user limit 0 offset 10速度最快

2)select * from user limit 10000 offset 10速度居中

3)select * from user limit 900000 offset 10速度最慢，我们找到了前90000条的数据，最后却只取后10条，这些前面的记录被丢弃，代价非常大

官方给出的优化方案是通过覆盖索引+子查询的方式来进行优化

先通过查询id(覆盖索引)，查询出来的结果看成一张表，然后进行连表查询

select * from user c，(select id from user order by id desc limit 10 offset 10) a where a.id=c.id

count优化:

1)当我们进行执行聚合函数的时候，如果说最终的数据量很大，在我们执行Count操作的时候，是十分耗时的

2)咱们的MYSIM引擎会自动的把总行数放到磁盘上面，如果说后面不加上任何的where查询语句，效率是很高的，但是如果说最后加上了where条件查询，那么就会变得很慢

3)咱们的InnoDB引擎也是很麻烦的，他在执行Count(*)的时候，需要将数据一行一行的从存储引擎中读出来在进行计数

4)count是一个聚合函数

4.1)当我们进行执行count(主键):InnoDB引擎会进行遍历整张表，把每一行的主键取出来，返回给服务层，服务层在拿到主键之后，直接进行按行累加(主键不能是空)

4.2)count(字段):没有not null约束，InnoDB会遍历整张表把每一行的字段都取出来，返回给服务层，服务层会进行判断是否为空，不为空就进行累加操作，如果说有not null约束，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，按照行来进行累加

4.3)count(数字)InnoDB直接会进行遍历整张表，但是不取值，服务层针对返回的每一行，直接放一个数字1进去，直接按照行来进行累加

4.4)count(*):InnoDB引擎并不会把所有字段取出来，而是专门做了优化，不取值，服务器直接进行累加

所以我们最终建议使用count(*)来进行操作

Update优化:

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，况且该索引不能失效，否则会从行锁升级到表锁

比如说当我们执行这条SQL语句的时候update user set name="张三" where id=7;这个时候应该是行锁，会进行锁定id=7这一行数据，但是当我们去执行下面这个SQL语句的时候:

select user set name="李四“ where name="声明"这个时候，由于where后面的条件没有使用索引，那么会导致行锁升级成表锁，那么会使性能降低

CMD链接MYSQL:

通过cd C:\Program Files\MySQL\MySQL Server 5.7\bin

输入MYSQL -uroot -p

视图:

定义:视图就是一种虚拟存在的表，视图中的数据实际上并不在数据库中存在，行和列数据来自定义试图查询中所使用的表，并且是在使用视图的时候动态生成的

1)创建视图:create or replace view 视图的名字 as +查询的SQL语句

比如说查询的SQL语句是这样的:(select id from User where userID<1)

2)查询视图:show create view 视图名字或者是select * from 视图名字

3)修改视图:create or replace view 视图的名字 as 查询语句 

查询语句可以变成:select id,name from user where userID<10

或者这么修改也是可以的:alter view 视图的名字+SQL语句

4)删除视图:delete view if exists 视图名字

1)create table student(id int,username varchar(30),password varchar(50),qq varchar(40));
//创建一张表,向数据库表中插入数据
insert into student values(1,"A","B",123456);
insert into student values(2,"N","C",1277);
2)create or replace view stu1 as select * from student where id<20;//创建视图
3)向视图中插入数据:insert into stu1 values(21,"C","1",12345);select * from student;
+------+----------+----------+--------+
| id   | username | password | qq     |
+------+----------+----------+--------+
|    1 | A        | B        | 123456 |
|    2 | N        | C        | 1277   |
|   21 | C        | 1        | 12345  |
+------+----------+----------+--------+select * from stu1;
+------+----------+----------+--------+
| id   | username | password | qq     |
+------+----------+----------+--------+
|    1 | A        | B        | 123456 |
|    2 | N        | C        | 1277   |
+------+----------+----------+--------+

1)当我们执行上面的SQL的时候，id超过20的记录看似是被插入到视图里面了，实际上是插入到原表里面了，但是实际上查询视图的时候，是无法进行查询到视图中id=20的记录的

2)因为我们在进行创建视图的时候，我们已经指定了视图创建的条件，所以我们无法成功进行插入

3)这样做显然是不算太规范的，id>20就不能插入到视图里面了

检查选项:

当我们使用wait check option子句来进行创建视图的时候，MYSQL会通过视图检查正在更改的每一行，例如说向视图中进行插入，更新，删除以符合视图的定义，就是符合视图创建的时候所制定的条件，MYSQL允许基于一个视图创建另一个视图，他还会进行检查依赖视图中的规则以及一致性，为了进行确定检查的范围，MYSQL提供了两个选项，cascaded和local，默认值是cascaded，如果说不加上这个语句，MYSQL就不会检查插入的语句是否符合视图中的选中条件

1)此时假设我们在创建视图的时候指定create or replace view stu1 as select * from student where id<20 with cascaded check option，此时我们再向视图中插入id>20的记录，就会对自己视图的条件进行查询，发现id>20，那么视图插入，也就是向基表中插入失败

2)现在比如说创建两个视图:

2.1)create or replace view stu1 as select * from student where id<20

2.2)create or replace view stu2 as select * from stu1 where id>10 with cascaded check option

当我们向表2中插入数据的时候，不仅会检查插入的数据是否满足表2中的where语句，他还会进行检查表1中的where语句是否符合条件，相当于是在表1中添加了一个with cascaded check option，也就是说我们进行插入id<10或者id>20的数据都会失败

2.3)现在我们基于第二个视图创建第三个视图: create or replace view stu3 as select * from stu2 where id<=15，这个时候没有加检查选项；

2.3.1)insert into stu3 values(19,"A","12345","1233");此时就会插入成功，因为此时第三个视图没有加检查选项，那么不会进行检查第三个视图的条件，但是第二个视图加上了选项，所以会检查第二个视图的条件，还会检查第一个视图

2.3.2)insert into stu3 values(23,"A","12345","1233");此时就会插入失败，他还是不会检查v3视图的条件，但是他是依赖v2视图的，v2视图会自动检查自身的条件以及它所依赖的视图的条件

所以说cascaded会检查自己的条件和调用链上面的所有条件

1)cascaded:比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。 则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1

2)local:比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。 则在执行检查时，只会检查v2，不会检查v2的关联视图v1

只要由cascaded染指，那么上面的local无用(没啥用)

视图的更新:

1)当我们使用聚合函数或者窗口函数(sum(*)，min()，max()，count(*));

2)distinct，group by，having，union，union all;

3)举个例子:create view stu_count as select * from student;

insert into stu_student values(10);

视图作用:

1)视图不仅可以简化用户对数据的理解，也可以简化他们的操作，那些被经常使用的查询可以定义为视图，从而使用户不必为以后的操作指定全部的条件(连表查询的结果可以封装到视图里面，以后进行连表查询的时候直接查询对应的视图就可以了)

2)安全:数据库可以进行授权，但是不能授权到数据库特定的行和列上面，通过视图用户只能查询和修改他们所能见到的数据