哪些维度可以进行数据库调优？
1.索引失效、没有充分利用到索引–> 索引建立
2.关联查询太多join（最好不要超过3张表，设计缺陷或不得已的需求）–> sql优化
3.服务器调优及各个参数设置（缓冲，线程数等） --> 调整my.cof文件
4.数据过多 --> 分库分表
#SQL优化技术有很多，但是大方向上可以分为：“物理查询优化” 和 “逻辑查询优化”两大块
1.物理查询优化是通过“索引”和“表连接方式”等技术来进行优化，需要重点掌握索引的使用。
2.逻辑查询优化是通过“SQL等价变换”来提升查询效率，直白一点讲，换一种查询写法执行效率可能更高
#索引失效：
用不用索引，都是优化器说了算。优化器是基于什么的优化器？基于cost开销（CostBaseOptimizer），怎么样开销小就怎么来，
另外，SQL语句是否索引，跟数据库版本、数据量、数据选择度都有关系
#计算、函数、类型转换（自动或手动）都会导致索引失效！
#范围条件右边的列索引失效！
如：select * from 表 where age = 30 and classid>20 and name = ‘abc’;
拥有age+classid+name的联合索引，那么仅仅使用age+classid作为索引搜索，而name没有使用上。是因为classid是范围条件
#不等于（!=或者<>）索引失效！
#is null 可以使用索引，is not null索引失效！
#like以通配符%开头索引失效！
#or前后存在非索引列索引失效！
#数据库和表的字符集统一使用utf8mb4或者utf8mb3！
不同的字符集进行比较前需要进行转换会造成索引失效！
#总结：
1.单列索引，尽量选择针对当前query过滤性更好的索引
2.组合索引，对当前query过滤性更好字段在索引顺序中越靠前越好
3.组合索引，尽量选择能够包含当前query中的where字句中更多字段的索引
4.组合索引，如果某个字段可能出现范围查询时，尽量在索引顺序中放在最后
#关联查询优化：
#外连接：左外连接（left join）、右外连接（right join）
#以左外链接为例：
1.如果只能添加“一个”索引，那么最好给被驱动表的链接条件添加
2.如果链接条件都有索引，那么数据类型最好一样，不然转换会导致索引失效
#内链接：inner join
如果链接条件都有索引时，驱动表和被驱动表可能被“查询优化器颠倒”
如果只能添加“一个”字段有索引，则有索引的字段所在的表会被作为被驱动表出现。查询优化器的策略
#内链接：在两个表的链接条件都存在索引的情况下，会选择数据量少的表作为驱动表。“小表驱动大表”
#join语句的底层原理
join方式链接多个表，本质就是各个表之间数据的循环匹配！
驱动表就是主表，被驱动表就是从表。
可以通过“EXPLAIN工具查看驱动表和被驱动表”，在被查询优化器优化后的顺序，排在前面的就是驱动表，后面的就是被驱动表
#简单嵌套循环连接（Simple Nested-Loop Join）：驱动表中的每一条记录都与被驱动表记录进行判断
#索引嵌套循环连接（Index Nested-Loop Join）：要求被驱动表上必须“有索引”，设计思路主要是为了“减少内层表数据的匹配次数”！
#块嵌套循环连接（Block Nested-Loop Join）：不在逐条获取驱动表的数据，而是一块一块的获取，引入了“Join buffer”缓冲区，将驱动表join相关的部分
数据列（大小受join buffer的限制）缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和缓冲区的所有驱动表记录进行匹配
将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率！select 后面的lie也会缓存起来！
1.block_nested_loop：查看是否开启块嵌套循环，默认开启，查看方式：show variables like ‘%optimizer_switch%’;
2.join_buffer_size：驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下是256k！
32位的系统可以申请4G，64位可以申请大于4G的（windows下除外，大于4G被截断并发出警告）查看方式：show variables like ‘%join_buffer%’;
#Join小结：
1.整体效率：INLJ < BNLJ < SNLJ
2.永远用小结果集驱动大结果集（本质就是减少外层循环的数据量，小的度量单位是：表行数*每行大小）
3.为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）
4.增大join buffer size 的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）
5.减少驱动表不必要的字段查询（字段越少，join buffer所缓存的数据就越多）
#mysql8.0.20开始就“废弃”了Block Nested-Loop Join（块嵌套循环连接），因为从8.0.18就加入了Hash Join！
且默认都使用Hash Join
#Hash Join：
mysql8.0.20开始就“废弃”了Block Nested-Loop Join（块嵌套循环连接），因为从8.0.18就加入了Hash Join！
且默认都使用Hash Join
Hash join 和 nested loop的区别：
1.nested loop对于被连接的数据子集较小的情况，
2.Hash Join 是做“大数据集连接”时的常用方式，优化器使用两个表中较小（相对）的表利用Join key 在内存中建立
散列表，然后扫描较大的表并探测散列表，找出与Hash表匹配的行
#这种方式适用与较小的表，完全可以放入内存。
#在表很大的情况下并不完全放入内存，这时优化器会将它分割成“若干不同的分区”，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高I/O性能
#它能够很好的工作于没用索引的大表和并行查询的环境中，并提供最高的性能，大多数人都说它是join的重型升降机！
#HashJoin只能用于等值连接，这是Hash的特点决定的
#子查询优化：
从4.1开始支持子查询，使用子查询可以进行select语句的嵌套查询，即一个select查询的结果作为另一个select语句的条件。
子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作！
但是，子查询执行效率不高！
#原因：
#执行子查询时，mysql需要为内层查询语句的结果“建立一个临时表”，然后外层查询语句从临时表中查询记录，完毕后在撤销临时表
#子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都“不会存在索引”！所以查询性能会受到一定的影响
#对于返回结果集比较的子查询，其对查询性能的影响也越大
#可以使用Join（连接）查询来代替子查询，连接查询“不需要建立临时表”！
#排序优化！！！！重点
#问：在where条件字段上添加了索引，为什么还要在order by 字段上还要加索引呢？
答：在mysql中，支持两种排序方式：“FileSort” 和 “Index”排序
1.Index排序：索引可以保证数据的有效性，不需要在进行排序，效率更高
2.FileSort排序：一般在内存中进行排序，占用CPU较多，如果待结果较大，会产生临时文件I/O到磁盘进行排序情况，效率低
#优化建议：
1.在sql中，可以在where字句和order by字句中使用索引，目的是为了where字句中避免全表扫描，在order by字句中避免使用FileSort排序
2.尽量使用Index完成Order by 排序，如果where和order by后面是相同的列就使用单列索引，不同就使用联合索引
3.无法使用index时，需要对FileSort进行调优
#失效情况：
1.索引列顺序错
2.索引列方向不一致（即一个asc排序，一个desc排序）
#FileSort算法：双路排序 和 单路排序
1.双路排序（慢）：4.1之前使用，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，从磁盘取排序数据，在buffer进行排序，再从磁盘取其他字段
2.单路排序（快）：从磁盘读取查询需要的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出，避免了第二次读取数据，把随机I/O变成了顺序I/O，
由于单路是后出的，总体而言好过双路，
但是用单路有问题，在sort_buffer中，单路比多路要“多占用很多空间”，因为单路是把所有字段都取出，所以有可能取出的数据总大小超出了sort_buffer容量，导致每次只能取sortbuffer容量的大小进行排序，排完在取，从而多次I/O，得不偿失
#单路排序优化策略
1.提高sort_buffer_size的空间
2.尝试提高max_length_for_sort_data：会增加用改进算法的概率，默认1024字节，在1024-8192字节间调整
3.order by 时select *是一个大忌，最好只Query需要的字段。
#GroupBy优化：
groupby使用索引的原则几乎和order by 一致，groupby即使没有过滤条件用到索引，也可以直接使用索引
groupby先排序后分组，遵照索引的最佳左前缀法则
#当无法使用索引列，同样可以优化“sort_buffer_size” 和 “max_length_for_sort_data” 参数设置
where效率高于having，能使用where的条件就不要写在having中
#减少使用order by和业务沟通能不排序就不排序，或将排序放到程序端使用，orderby、groupby、distinct这样都较为耗费cpu资源
#包含了orderby、groupby、distinct这些查询语句，where条件过滤出来的结果集最好保持1000行以内，否则sql会很慢
#Limit分页优化：
一般分页查询时，通过创建覆盖索引能够比较好地提高性能
优化思路：
1.在索引上完成排序分页操作，最后根据主键回表查询所需要的其他列内容
2.可以把limit查询转换为某个位置的查询，比如：where id>200000 limit 10;
#覆盖索引的使用：
理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不比读取整个行。毕竟索引叶子节点存储了他们索引的数据，
当能通过读取索引就可以得到想要的数据，那就不需要读取行了。“一个索引包含了满足查询结果的数据就叫做覆盖索引”！
理解方式二：非聚簇复合索引的一种形式，它包括在查询里面的select、join和where字句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及到的字段）
#简单来说：索引列+主键 包含select 到 from 之间的查询的列。
#使用它是因为不用进行回表读其他列数据！！
#好处：
1.避免innodb表进行索引的二次查询（回表）
2.可以把随机I/O变成顺序I/O加快查询
覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段
#弊端：索引字段的维护总是有代价的
#索引（条件）下推（ICP）
是mysql5.6中的新特性，是一种在存储引擎层使用的索引过滤数据优化方式，一般针对“联合索引”！
#如果部分where条件仅可以使用索引中的列进行筛选，则mysql服务器会把这部分where条件放到存储引擎筛选，然后引擎通过使用索引条目来筛选数据，并且只有满足这一条件时才从表中读取
#好处：ICP可以减少存储引擎访问基表的次数和mysql服务器必须访问存储引擎的次数
#但是，ICP的“加速效果”取决于存储引擎内通过“ICP筛选”掉的数据的比例。
默认情况下是开启的
#可以通过设置系统变量 optimizer_swich控制：
打开：
set optimizer_swich = ‘index_condition_pushdown=on’;
关闭：
set optimizer_swich = ‘index_condition_pushdown=off’;
#索引（条件）下推（ICP）使用条件：
1.表访问类型为range、ref、eq_ref和ref_of_null可以使用ICP
2.ICP可以用于innodb和MyISAM表，包括分区表
3.对于Innodb表，ICP仅用于二级索引！ICP目标就是减少全行读取次数，从而减少I/O操作
4.当SQL使用覆盖索引时，不支持ICP
5.相关子查询的条件也不能使用ICP
#其他查询优化策略
#关于EXISTS 和 IN 的区分
问：那种情况使用EXISTS，那种情况使用IN？
答：索引是个前提，选择与否要看表的大小。选择的标准可以理解为：“小表驱动大表”
A表小于B表时，使用EXISTS，因为EXISTS的实现，相当于外表循环
当B表小于A表时，使用IN
#count()与count(具体字段)效率
#前提：统计的是某个字段的非空数据行数！
答：在mysql中，可以使用三种方式：count()、count(1)、count(具体字段)，
count()、count(1)都是对所有结果进行count。
count()、count(1)本质是没有区别，但是二者的执行时间可能略有差别，可以看做是执行效率相等
count(具体字段)，要尽量使用二级索引，因为主键是聚簇索引，包含信息多，明显大于二级索引。多个二级索引，会使用key_len小的二级索引进行扫描，没有二级索引才会使用主键索引
#关于select *
在表查询中，建议明确字段，不要使用作为查询列表
原因：
1.在mysql解析过程中，会通过“查询数据字典”将按序转换为所有列名，大大的耗费资源和时间
2.无法使用覆盖索引
#Litmit 1 对优化的影响
如果是针对全表扫描，确定结果集是1条，那么加上limit 1，找到一条结果后就不在继续扫描，这样加快查询数据
如果数据表已经对字段建议了唯一索引，那么可以通过索引查询，不会全表扫描时，就不需要加上limit 1了
#多使用COMMIT
只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所是否的资源而减少
#COMMIT所释放的资源：
1.回滚段上用于恢复数据的信息
2.被程序语句获得的锁
3.redo/undo log buffer中的空间
4，管理上述3中资源的内部花费
#主键如何设计？
答：尽量不要使用业务字段做主键
可以参考淘宝的主键设计：
#使用订单号 = 时间+去重+用户ID，来设计主键
#非核心业务：使用自增ID作为主键
#核心业务：主键设计至少应该是全局唯一且是单调递增
#推荐最简单的主键设计：UUID：全局唯一，占用36个字节，数据无序，插入性能差！
#MySQLUUID组成：时间+UUID版本（16字节）-时间序列（4字节）- MAC地址（12字节） = 根据32个字节字符串存储，设计时还有无用的4个“-”字符，因此需要36个字节
#MySQL8.0可以更换时间地位和时间高位的存储方式，这样UUID就是有序的了。
还解决存储空间占用问题，除了无用字符“-”，并且使用二进制存储，这样存储空间由36个字节降为16个字节
使用方法：select uuid_to_bin(uuid(),true);
#良好的数据库设计应该具备的优点：
1.节省数据的存储空间
2.能够保证数据的完整性
3.方便进行数据库应用系统的开发
#范式（Normal Form）NF
什么是范式？
答：在关系型数据库中，关于数据表设计的基本原则、规则就称为范式！
#范式包括哪些？
答：目前关系型数据库中由六种常见范式，按着级别，从低到高分别为：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）、第五范式（5NF，完美范式）
范式设计越高阶，冗余度就越低，同时高阶的范式一定符合低阶范式的要求，满足最低要求的范式是第一范式（1NF）
一般来说，最高遵循到BCNF，普遍还是3NF，但是也不绝对，有时候为了提供查询性能，还需要破坏范式规则，称为“反规范化”！
#键和相关属性的概念：
1.超键：能唯一标识元组（一行）数据的叫做超键！（主键+任意键）都可以构成
2.候选键：如果超键不包括多余的属性，那么这个超键就是候选键！（最小的超键）
3.主键：用户可以从候选键中选择一个作为主键！
4.外键
5.主属性：包含在任一候选键中的属性称为主属性！
6.非主属性：与主属性相对，不包含在任一候选键中的属性！
#第一范式：
主要是确保数据表中每一个字段的值必须具有“原子性”（主观的，即我认为不可以拆分），也就是每个字段的值“不能再次拆分”的最小数据单元！
如：用户表的用户信息字段同是存储了姓名、电话、住址等可拆分的信息，就不满足第一范式！
#第二范式：
在满足第一范式的基础上，还要“满足数据表里的每一条数据记录，都是可唯一表示的，而且非主键字段，都必须完全依赖主键，只能只依赖主键的一部分”！
如：成绩表（学号，课程号，成绩）关系中，（学号，课程号）可以决定成绩！但是学号不能决定成绩，课程号也不能决定成绩，所以“（学号，课程号）-> 成绩” 就是“完全依赖关系！”
不然会造成“数据冗余”、“插入异常”、“删除异常”、“更新异常”的问题！
#第一范式告诉我们字段属性需要是“原子性”！第二范式告诉我们一张表就是一个“独立的对象”！一张表只表达一个意思！#第三范式：
在满足第二范式的基础上，确保数据表中的每一个非主键字段和主键字段“直接相关”！也就是说，“要求数据表中的所有非主键字段不能依赖于其他非主键字段”！
通俗的讲：2NF和3NF：每个非主键属性依赖于键，依赖于整个键，并且除了键别无他物！
#范式小结：
关于数据库设计，有三个范式遵循
1.1NF：确保每列“原子性”
2.2NF：确保每列和主键“完全依赖”
3.3NF：确保每列和主键列“直接相关”，不是间接相关！
范式的优点：数据的标准化有助于消除数据库中的“数据冗余”，3NF在通常被认为在性能、扩展性、数据完整性方面达到了最好的平衡
范式的缺点：范式的使用，可能降低查询的效率，因为范式等级越高，设计出来的表就越多、越精细，数据冗余度就越低，进行查询就要关联多张表，代价昂贵，也可能使一些“索引策略无效”！
#反范式化
遵循“业务优先”原则，通过“增加少量的冗余”或重复的数据来提供数据库的“读取性能”，减少关联查询，join表的次数，实现“空间换取时间”为目的！
#规范化VS性能
1.为满足某种商业目标，数据库性能比规范化数据库更重要！
2.在数据规范化的同时，要考虑数据库的性能
3.通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息的所需时间
4.通过在给定表中插入计算列，以方便查询！
#数据库的设计原则：一般原则：“三少一多”
1.数据表越少越好
2.字段个数越少越好
3.联合主键越少越好
4.使用主键和外键越多越好
#数据库调优措施：
#目标
1.节省系统系统（吞吐量更大）
2.提高响应速度
3.减少系统的瓶颈
#定位：
1.用户反馈
2.日志分析
3.服务器资源监控：CPU、内存、I/0等情况
4.数据库内部状况监控：活动会话监控！
5.其他：事务、锁等待
#调优步骤（宏观）：
1.合适的DBMS
2.优化表设计
3.优化逻辑查询
4.优化物理查询：正确使用索引！
5.使用Redis或Memcached作为缓存！
6.库级优化：读写分离、数据分片、
