SQL优化

1.mysql的体系结构

2.存储引擎

存储引擎是基于表的，而不是基于数据库的
在创建表的时候他会要求你去选择哪一个存储引擎（mysql5.5之后默认是InnoDB)，而 创建数据库的时候却不会让你选择

InnoDB的优势就在于他支持行锁和外键
而MyISAM支持表锁（速度快，业务如果对事务的完整性不做要求，而且允许少量的数据丢失，此时就可以使用MyISAM）

3.索引

什么是索引：
索引是帮助mysql高效获取数据的数据结构

索引的好处和坏处

好处：使查询速度变快
坏处：
1.占用更多的内存空间
2.使增删改的速度变慢，因为每改变一条，索引的结构就会有变化

索引分类

一、单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了

1：普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一 点。一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了

2：唯一索引：索引列中的值必须是唯一的，但是允许为空值。

3：主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。这是在建表的时候根据主键默认创建的

二、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询。
innodb只支持上面的四种
三、全文索引：全文索引，只有在MyISAM引擎上才能使用(MySQL5.6之后innodb也支持了)，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 …" 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思。一般开发中，不贵用到全文索引，因为其占用很大的物理空间和降低了记录修改性，故较为少用。

聚簇索引和非聚簇索引

聚簇索引：将数据存储与索引放到一块，索引结构的叶子结点保存了行数据
非聚簇索引：将数据和索引分开存储，索引结构的叶子节点指向了数据相应的位置
一般聚簇索引就是主键索引，在聚簇索引之上创建的索引成为辅助索引，非聚簇索引都是辅助索引，像复合索引，前缀索引，唯一索引。辅助索引叶子结点存储的不在是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找（首先根据辅助索引查到相关的主键值，然后在根据主键索引进行查找。索引每个根据辅助索引查找的都需要二次查找 ）

辅助索引保存的不是地址，而是主键
这样设计的好处是：当新增删改数据的时候，原来的数据地址可能会改变，如果保存的是地址，则辅助索引的所有数据也会相应的全部改变。

聚簇索引的好处

-问题:每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗?聚簇索引的优势在哪?

由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同—数据页不同行记录时，已经把页加载到了Buffer中(缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这祥主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化，或者是我们需要查找的数据，在上一次Io读写的缓存中没有，需要发生一次新的Io操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的府储空间大小。

聚簇索引需要注意的地方

当使用主键为聚簇索引时，主键最好不要使用uid ，因为uid的值太过离散，不适合排序且可能出线新增加记录的uid,，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。
建以使用irt类型的自增，方便排序并且默认会在索引树的未尾增加主键值，对索引树的结杓影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到Io操作读取到的数据量。
为什么主键通常建议使用自增id
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即∶只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措瓶来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少,效率也高。

索引覆盖

如果查询只需要扫描索引而无须回表，将带来诸多好处。

（1）索引条目通常远小于数据行大小，如果只读取索引，MySQL就会极大地减少数据访问量。

（2）索引按照列值顺序存储，对于I/O密集的范围查询会比随机从磁盘中读取每一行数据的I/O要少很多。

（3）InnoDB的辅助索引（亦称二级索引）在叶子节点中保存了行的主键值，如果二级索引能够覆盖查询，则可不必对主键索引进行二次查询了。
覆盖索引就是从索引中直接获取查询结果，要使用覆盖索引需要注意select查询列中包含在索引列中；where条件包含索引列或者复合索引的前导列；查询结果的字段长度尽可能少。

索引的设计原则

4.优化SQL

show processlist
查看当前MYSQL在进行的线程，包括线程的状态，是否锁表等

查看SQL执行频率
show status like ‘Com_______’;
show global status like ‘Com_______’

explain

加在每天语句的最前面

ID

id值不同:id值越大越优先查询（本质：在嵌套子查询时，先查内部，后查外部）

id值相同:当每个表中的数据不一样的时候，最少的会在上面，最大的会在下面，这是因为 “笛卡尔积”，虽然几个表的最终乘积都是一样的，但是中间过程不一样，系统会优先选择中间过程少的，所以数量少的会先查询

select_type

type

extra

using index condition 回表查询，创建的索引不包括查询中的某字段，因为索引中没有该字段，所以需要回到表中才能查到我们想要的数据

5.索引失效

单表

最左前缀法则
当有三个字段，此时对三个字段添加复合索引（a,b,c）
此时当查询的时候，只能从左边开始，若没有a字段，直接从b字段开始，此时就不会走索引，如果只走了ac，此时之后走的索引长度只是a的，不带c
1.范围查询
范围查询的右边的列，不能使用索引，
比如
此时走的索引是name和status，范围查询之后的索引会失效
2.不要在索引列上进行运算操作，索引将失效
3.字符串不加单引号，造成索引失效，
一般当我们的字符串不加’'的时候，系统会给我们自动加上
4.尽量使用覆盖索引，避免select *
尽量使用覆盖索引，只访问索引的查询（索引列完全包含查询列）
5.or后面的字段没有索引会导致整个字段中的索引失效
6.模糊查询中 %加在前面会导致索引失效（解决方案：通过覆盖索引来解决）
7.如果MySQL评估使用索引比全表更慢，则不适用索引，
比如当某列数据大部分都是某一数据的时候，此时查询多的那一个数据的时候，就会全表扫描，而搜比例占比少的那一个数据的时候就会使用索引
8.单列索引和复合索引
尽量使用复合索引，少使用单列索引
在单列索引中，数据库会选择一个最优的索引（辨识度最高的索引）来使用，而不会使用全部
9.将含in的范围查询，放到where条件的最后，防止失效

编写过程select distinct …from …join…on …where …group by …having … order by
执行过程 from … on… join…where…group by …having…select distinct …order by
在添加索引的时候要按照执行的顺序来添加，

多表

大表驱动小表，

SQL优化

exists 和 in

如果主查询的数据集大，则使用in
如果子查询的数据集大，则使用exists

插入大批数据

1.手动提交事务
导入前设置set autocommit=0，关闭自动提交，导入结束后在执行=1
2.主键顺序插入
因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率，如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键。
3.load 命令导入数据
4.关闭唯一性校验
在导入数据前指向性set unique_checks=0，执行结束后再将它设为1

insert的优化

在事务中进行插入
主键顺序插入

order by的优化

filesort 和 index排序
通过 using index排序
通过索引直接返回有序数据，where 和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且 order by 的字段都是升序或都是降序，否则会出现filesort
但是有时候还是避免不了filesort的出现
优化filesort：
对于filesort，MySQL有两种排序算法
1）两次扫描算法：mysql4.1之前使用该方式，该操作可能会导致大量随机i/o操作
2) 一次扫描算法：一次性取出满足条件的所有字段。
mysql通过比较系统变量max_length_for_sort_data的大小和query语句取出的字段总大小，判断是否哪种排序，若max_length_for_sort_data更大，则使用一次扫描，否则使用第一种
可以适当提高max_length_for_sort_data和sort_buffer_size系统变量，来增大排序区的大小，从而提高排序的效率

优化嵌套查询

用多表联合查询来代替子查询

or优化

or所关联的字段都必须要有索引才能生效

用union来代替or

6.应用优化

1.使用连接池

2.减少对MySQL的访问

1.避免对数据进行重复检索
2.增加cache层

3.负载均衡

查询缓存优化

开启：


缓存失效情况：
1.注意这里是完全相同，当大小写不同也会导致缓存失效
2.当查询语句中有一些不确定的函数时，则不会缓存。如：

3.不适用任何表的查询
4.查询系统数据库时也不会缓存
5.在存储的函数，触发器或事件的主体内执行的查询
6.如果表更改则该表的所有告诉缓存查询都将变为无效并从告诉缓存中删除

建表优化

SQL建表时如何优化
：
1.表字段尽量避免null的出现，因为null值无法走索引，若想使用，可以用0或特殊的值或空字符串；
2.尽量使用int而不是bigint,如果是非负的话还可以UNSIGNED，因为符号也会占一位，加上他的话可以扩大两倍；
3.char和varchar的选择，若想要查询速度快一些的话就用char，若想要省一些空间的话就用varchar，如果字段的长度确定，那就选择char,不确定就用varchar
4.小数尽量用精确值decimal，而不是double、float，在db中这两种保存的是近似值
5.日期datetime、timestamp，时间年限跨度比较大的用datetime,受时区影响的用timestamp.

内存优化

InnoDB的内存优化

mysql并发参数调整

7.锁

锁概述

锁的分类

mysql锁

加锁：
lock table 表名 锁类型
解锁：
unlock tables;

MyISAM表锁：

1.在一个链接中对某表加读锁，其他链接也可以读该表。
2.加了锁的这个链接只可以对当前表进行读操作，不可以进行写操作，也不能对其他的表进行读操作和写操作。
3.其他的链接中对该表进行写操作时，会发生阻塞状态，只有当读锁解除过后写操作才生效。

在一个衔接中对某表加入写锁，本链接可以对该表进行任何操作，其他线程不能对这个表进行任何操作，

InnoDB行锁

举例：当一个链接关闭了事务的自动提交，此时又对一个id 为 3的用户进行了更新操作，没有进行commit操作，此时其他的链接是无法对id为3的线程进行更改操作
行锁有可能会升级为表锁，
上面的索引失效都会导致行锁升级为表锁
比如一个字段为varchar类型，但是我们给他赋值的时候没有带’'，此时会导致行锁变为表锁

间隙锁

举例：取消事务自动提交，当我们有一些数据，分别id 为 1,2,4,此时对他们进行update的操作，将id<5的进行更改，不commit.此时我们另外一个连接就无法插入id为3 的数据这就是间隙锁存在的原因

总结

Mysql日志

错误日志

二进制日志

默认是不开启的，

查询日志

默认未开启

慢查询日志

慢查询日志：MySQL提供的一种日志记录，用于记录mysql中相应时间超过阈值的SQL语句（默认10秒）
慢查询日志默认是关闭的
建议：开发调优打开，最终部署关闭

mysql主从复制

复制原理

redis和mysql的复制区别

Redis 是主机将操作写在rdb文件里，然后从机拿到这个文件直接覆盖自己的数据 且初始化数据，是从头开始复制；
mysql不是，mysql是从接入点开始复制；简单来说在确定从属关系的时候，从机才开始复制，复制的是确立关系之后的数据；
复制原理也不一样：redis是从属关系搭起来之后，主机做什么就直接同步给从机；
– mysql是直接写在二进制日志中，然后从机读取这个日志在写入relay log中，然后在读在从机中执行；MySQL复制是异步的且串行化的
– 所以mysql主从复制中会存在延时问题；一般生产环境会进行读写分离，一般主机做写操作，从机读操作；

建表