MySQL高级（纯笔记，未整理）

马上找工作了，看好多面试都问SQL优化以及索引的知识，就根据B站黑马的视频学了一下。内容就不整理格式了，纯粹给我自己以后复习看的。

连接层
SQL优化是在服务层
索引在引擎层。不同存储引擎索引结构不一样-
存储层

InnoDB
表空间、段、区(1M)----64>页(16K)（一个结点放在一个页中）、行
5.5版本之后的默认引擎
特点：
DML操作遵循ACID模型，支持事务
行级锁，提高并发访问性能
支持外键约束，保证数据的完整性和一致性

更新删除操作较多，对数据的完整性和事务要求较高

MyISAM
早期默认引擎
特点：
不支持事务
支持表锁，不支持行锁
访问速度快

以读和插入为主，很少更新和删除，对事务的完整性和并发性要求不高

索引：帮助MySQL高效获取数据的数据结构
没有索引需要全表扫描，有索引需要维护对应的树
优缺点：
提高查询效率，降低数据库的IO成本
通过索引列队数据进行排序，降低排序成本，降低数据库的CPU消耗1

缺：
索引列占用空间
降低了更新表的效率，增删改效率降低

全文索引（Full-text）：是一种通过建立倒排索引，加快匹配文档的方式，如Solr

B+tree索引
二叉树缺点：顺序插入时，会形成一个链表，查询性能低，大数据量情况下，层级较深，检索较慢
红黑树缺点（也属于二叉树）：大数据量情况下，层级较深，检索较慢
B-Tree（多路平衡查找树） ：中间元素向上分裂
B+Tree:所有的元素都出现在叶子结点，非叶子结点起索引作用，叶子结点形成一个单向链表
MySQL对B+树做了一个优化，在原有的基础上，添加了一个指向相邻叶子节点的链表指针，
就形成了带有顺序结构的B+tree，提高区间的访问性能

B+Tree相比二叉树，层级更少，搜索效率高
相比B-tree树，叶子结点保存所有数据，这样在一页中就可以存储更多的键值，指针增多，提高性能
想比hash，B+tree 支持排序操作和范围索引

分类：
主键索引：只能有一个，默认创建 primary
唯一索引：可以有多个，unique
常规索引：可以有多个
全文索引：查找的是文本中的关键词，而不是比较索引中的值，可以有多个，FULLTEXT

根据存储形式来分
聚集索引：将数据存储和索引放到了一块，索引结构的叶子节点保存了行数据，必须有，而且只有一个
二级索引：将数据和索引分开，索引结构的叶子节点关联的是对应的主键。可以存在多个

聚集索引选取规则
存在主键，主键索引就是聚集索引
不存在主键，使用第一个唯一索引作为聚集索引
都没有，生成一个rowid作为隐藏的聚集索引

聚集的叶子节点下面挂的就是当前行的数据
二级索引叶子节点下面挂的是这一行数据对应的id （主键）

回表查询：先从二级索引找到对应的主键值，在从聚合索引中找到行数据

索引语法：
创建索引：---->构建对应的B+树
create [unique][fulltext] index index_name on table_name(index_col_name,…);联合索引、单列索引
查看索引：
show index from table_name;
删除索引：
drop index index_name on table_name;

SQL性能分析：
SQL执行频率:show [session|global] status [like ‘Com_______’]
例：show global status like ‘Com_______’;

慢查询日志：记录了所有执行时间超过指定参数的所有SQL语句的日志，默认关闭 set global slow_query_log=on;
查看：show variables like ‘slow_query_log’;

profile
查看是否支持：select @@have_profiling;
设置：set profling=1;
查看每一条SQL语句的耗时：show profiles;
查看在各个阶段的耗时情况：show profile for query 16(query_id);
查看cpu在各个阶段的耗时情况：show profile for query 16(query_id);

explain执行计划
获取MySQL如何执行select语句的信息
在select语句之前加上关键字explain或者desc
explain/desc select 字段列表 from 表名 where 条件;
查询到的各字段含义：
id ：select查询的序列号，表示执行select子句或者操作表的顺序（相同从上往下，不同值越大先执行）
select_type:表示查询的类型（了解就行）
type:表示连接类型，性能由好到差为（NULL不访问表,system访问系统表,const访问唯一索引或主键,eq_ref,ref访问非唯一索引,range,index,all）
possible_key：显示可能用到的索引，一个或多个
key:实际用到的索引，如何为NULL，则没有使用索引
key_len:表示索引中使用的字节数，该值为索引字段最大可能长度，在不损失精确性的前提下，长度越短越好
rows:要执行的行数（不一定准确）
filtered:表示返回结果的行数占需要读取行数的百分比，filtered的值越大越好
Extra：其它

索引使用：
最左前缀法则：
如果索引了多列（联合索引），要遵循最左前缀法则，最左前缀法则指的是查询从索引
的最左列开始，并且不跳过索引中的列，如果跳跃某一列，索引将部分失效（后面的字段索引失效）

范围查询
联合索引中，出现范围查询（>,<）,范围查询右侧的列索引失效，使用(>=,<=)不会失效

索引列运算
不要在索引列上进行运算操作，否则索引失效

字符串不加引号
字符串类型字段使用时不加引号，索引失效

模糊查询
如果头部模糊匹配，索引失效

or连接的条件
用or分割开的条件，只有两侧的列中都有索引才能生效，否则失效

数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引（is null ,is not null）

SQL提示
是优化的一个重要手段，简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的
指定索引，写在表名之后 例use index（索引名）
use index 建议使用
ignore index 忽略
force index 强制使用

覆盖索引
尽量使用覆盖索引（查询使用了索引，并且需要返回的列在该索引中已经全部能够找到，减少select *）

Extra中
using index condition:查找使用了索引，但是需要回表查询数据
using where;using index 查找使用管理索引，所需要的数据都在索引列中能找到，不需要回表

前缀索引
当字段类型为字符串时，有时需要索引很长的字符串，这会让索引变得很大，
查询时，浪费大量的磁盘IO，影响查询效率，此时，可以只将字符串的一部分
前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率
create index idx_xxx on table_name(column(n));
select count (distinct substing(email,1,8))/count(*) from 表名;
==1选择性最好

索引设计原则：
针对数据量较大且查询频繁的表建立索引
针对常作为查询条件(where)、排序(order by)、分组(group by)的字段建立索引
尽量使用区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，可以节省空间，避免回表，提高查询效率
尽量控制索引的数量，索引并不是多就好，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
如果索引列不能存储NULL值，请在创建表时使用not null 来约束它，当优化器知道每列
是否包含NULL值时，它可以更好的确定哪个索引最有效的用于查询

SQL优化
插入数据：
insert优化：
批量插入
手动提交事务
主键顺序插入
大批量插入数据使用load指令进行插入

主键优化：
数据组织方式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，
这种存储方式的表称为索引组织表
页分裂（插入）、页合并（删除）
主键设计原则：
满足业务需求的情况下，尽量降低主键的长度
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
尽量不要使用UUID做主键或者其它自然主键，如身份证号
业务操作时，避免对主键的修改

order by优化
using index 效率较高 （是通过索引排序的，不需要额外排序）直接通过索引返回数据，性能高
using filesort ：需要将返回的结果在排序缓冲区排序
创建索引指定排序规则 例：（age asc,phone desc）
根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
尽量使用覆盖索引
多字段排序，一个升序，一个降序，此时需要注意联合索引在创建时的规则
如果不可避免的出现（filesort），大数据排序时，可以适当的增大排序缓冲区的大小sort_buffer_size(默认256k)

group by优化
using temporary用到了临时表，效率低了
在分组操作时，可以通过索引来提高效率
分组操作时，索引的使用也满足最左前缀法则

limit优化
limit 索引 ,偏移量
优化思路：一般分页查询时，通过创建覆盖索引能够较好的提高性能，
可以通过覆盖索引加子查询的形式进行优化
例：explain select * from tb_sku t,(select id from
tb_sku order by id limit 200000,10) a where t.id=a.id;

count优化
InnoDB引擎在执行count()的时候，需要把数据一行一行的从引擎里面读出来，然后累计计数
优化思路：自己计数
count函数的参数非null +1
效率排序：count()≈count(1)>count(主键id)>count(字段)
count() InnoDB引擎并不会把全部字段取出来，而是专门做了优化，所以应该尽量使用count()

update优化
更新时根据索引，这样只会锁行，直到commit，否则表锁 ，会降低性能
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁

视图是一种虚拟存在的表。视图中的数据并不在数据库中真实存在，行和列数据来自定义视图的查询中使用的表，
并且在使用视图时动态生成的。
通俗的讲：视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在
创建这条SQL查询语句上

锁
按照锁的粒度分：
全局锁：锁定数据库中的所有表 ，只能读，其它操作会阻塞（数据备份）
表级锁：每次操作锁住整张表
行级锁：每次操作锁住对应的行数据

表锁：表锁、元数据锁、意向锁
表锁：
表共享读锁
表独占写锁
语法：
加锁：lock tables 表名… read/write
释放锁：unlock tables/客户端断开连接
读锁不会阻塞其它客户端的读，但会阻塞写，写锁既会阻塞其它客户端的读，
又会阻塞其它客户端的写
元数据锁：自动控制 为了避免DML与DDL冲突，保证读写的正确性

行级锁：每次加锁操作锁住对应的行，并发度最高
InnoDB的数据是基于索引组织的，行锁是通过对索引项加锁来实现的，而不是对
记录加的锁：
行锁、间隙锁、临键锁