关于mysql的优化相关内容的理解

mysql：
        innodb: 依靠主键索引进行检索 数据存放在一个文件里面，非主键索引检索时 通过辅助索引找到子节点，而子节点存放的是主键索引子节点引用，没有主键索引时，会默认创建一个主键索引，6byte
        
        myisam：索引和数据分别存放，索引存放在MYI文件里面，而索引的子节点则存放数据区的引用，数据存放在MYD文件中
    离散性：即指数据差异性越大，离散性越好，
    
    联合索引：最左匹配原则、离散高、宽度小的列优先（最少空间原则）
    覆盖索引: 指定列索引时会通过索引的结构直接选中到该数据，从而达到提升新能的目的，同时也能减少DB io的次数，将随机io变成顺序io
    
    mysql查询流程：
        1、客户端/服务端通讯：半双工（可以互相通讯，同一时间只能往一个方向发送通讯，另一端需要等待处理完后才能继续通讯，一请求一响应）
            连接状态查询: 可通过指令：show full  processlist /show processlist  
                Sleep  : 线程正在等待客户端发送数据
                Query  ：连接线程正在执行查询
                Locked ：线程正在等待表锁的释放
                Sorting result：线程正在对结果进行排序
                Sending data：向请求端放回数据
            对出现的问题的连接可通过 kill {id} 的方式进行连接的杀掉
            
            查询优化器如何找到最优执行计划（mysql的查询优化器是基于成本计算原则）
                1、使用等价变化原则
                2、将可转换的外连接查询转换成内连接查询
                3、优化count、max、min等函数（通过判断查询引擎）
                4、覆盖索引扫描
                5、子查询优化
                6、提前终止查询
                7、in的优化（查询是会给数据排序，通过二分法形式去查询，减少查询次数）
            
        
        2、查看查询计划 explain + 查询语句 \G
            select_type :查询类型
            table ： 查询计划设计到的表名或者表的别名
            type：访问类型 sql查询语句里面很重要的指标  
                system > cost > eq_ref(唯一索引扫描：唯一索引或者主键) > ref(非唯一索引扫描：普通索引) > rang (检索指定范围) > index(使用索引进行全表扫描) > all
            extra : 额外信息
            
        3、慢查询定位：
            查询慢查询设置： show variables like 'show_query_log';
            慢查询日志文件的设置，在mysql安装时在my.ini中的[mysqld]下加两个配置
            log-slow-queries：文件路径
            long_query_time：查询时间超过多少秒才记录
            开启慢查询日志：set globel show_query_log=on;(off:关闭/on：开启)
            1、通过文件查看的方式查看慢查询日志文件（可以查看执行时间、执行语句等）
            2、通过mysql自带的工具（mysqldumpslow --hlep:查看工具相关参数信息）查询 具体使用即为： mysqldumpslow +(加该工具的参数，着重看 -s和 -t) + 慢查询日志文件路径（可通过查询慢查询设置获得）
            
    mysql的锁机制
        事务：
            事务的隔离级别
        事务所带来的问题及相应的解决办法;
            1、脏读： 可通过添加共享锁的方式（允许查询，不允许修改）
            2、可重复读: 可通过排他锁解决（当存在排他锁时，不允许其他操作：排他锁操作、共享锁操作）
            3、幻读：可通过临键锁解决（查询一定范围内的数据时，命中该范围的数据后，会对其所在区域的及临键区域进行加锁，该加锁区域内）
        共享锁： 多个线程可以同同时查询不可进行修改操作的锁操作 在查询语句后面 添加LOCK IN SHARE MODE 即可
        排他锁：对 delete/update/insert 等操作 默认添加 排他锁，获取排他锁后在没释放前，不可被其他获取（包括共享锁和排他锁）
        注：innodb的行锁 锁了什么：
            1、innodb的行锁 是通过给索引上的索引项（普通索引或者主键）加锁来实现的
                
            2、只有通过索引项进行检索innodb才会对数据进行行级锁，否者innodb将使用表锁（锁住所有数据）
            在普通索引的索引项上加锁后，innodb会对普通索引对应的主键索引上加锁
            
        加表锁的命令：lock table xx read / write
        
        临键锁：当检索操作为范围检索时，命中检索项后，通过左开右闭的方式锁住当前区间和临键区间（区间的划分由表中的数据决定）
            临键锁之所以能作为innodb的默认行级锁是因为 innodb的数据区顺序 跟B+ tree 是一致的（数据顺序和检索/逻辑顺序是一致的）
        间隙锁（Gap锁）: 是当临键锁没有命中检索项时，锁住检索条件所在的区间（锁住Gap区间）
        Gap锁只在RR事物隔离级别存在，因为只有在RR中才会存在幻读
        记录锁：唯一性（主键/唯一）索引，条件为精准匹配，退化成Record Lock
        
        mvcc（多版本并发控制）；
        通过创建事务时产生的自增id，来对多事务的控制
        undo Log 两个功能：为实现事务的原子性而产生的产物；为innodb提供多版本的控制
        快照读：普通的select查询是快照读，读的是历史版本（通过mvcc的方式解决幻读）
                innodb快照读读取的数据有原数据cache + undo（事物修改过的数据）两部分组成
        当前读：通过delete/update/insert/lock in share mode 等方式读取时候是当前读（通过临键锁解决幻读：next_key lock）

MySQL的优化总结：
分几块
1、表设计期间，建立合适的索引，不要建立冗余的索引，不宜建大字段的列会影响查询效率，如图片等，可以选择存放路劲
2、sql语句方面：通过最左匹配原则，离散性以及空间最少原则，取决于索引的结构 主要是b+tree的数据结构
3,在查询语句中，
    1、少使用*，用指定的查询字段代理，提高查询效率，
    2、能使用in 尽量不要使用or 因为mysql对in的查询条件做了优化处理（先排序，后依据二分法进行查询）
    3、多使用聚集索引，在检索条件后指定具体的检索条件，可以直接命中，提升查询效率
    4、尽量不要在检索条件里面使用函数及相关的逻辑处理，会是索引失效，如无法避免尽量放置检索条件的右边，最左匹配原则
    5、使用模糊查询时，%号在前会使索引失效，%在后时，如果查询的数据过多且数据离散差时也会是索引失效
    6、执行的sql不能低于rang的执行类型，
    
4、择优的利用mysql的事务隔离性以及合理利用innodb相关的所机制和mvcc 
    1、innodb可以通过共享锁来解决事务带来的脏读问题，因为在锁期间 只允许查询不允许修改操作
    2、innodb可以通过排他锁来解决事务带来的不可以重复读的问题，在排他锁期间，不允许其他排他锁操作和共享锁操作
    3、innodb的临键锁和mvcc（事务的多版本控制）可以解决幻读的问题
        mvcc的快照读获取undo里面存放的历史数据，使其在进行排他锁操作时也可以进行查询，避免了堵塞，提高了查询效率
        mvcc只有在本地读时，才回去读取最新的数据
        
5、也可以通过修改mysql相关的系统配置来提升查询效率，如最大连接数等（最大连接数与连接数配置、Linux句柄和mysql句柄等有关）

索引常见的数据结构：
    有b-tree 和 b+tree 尽可能的遵循最少空间原则，这样每棵树存放的路数就会多，进而减少IO的次数，提升查询效率
    b-tree：多路平衡查找树，树的路数= 子节点树 + 1 ，数据存放节点的数据区中
    b+tree：可以理解为b-tree的强化版，
        1、b+tree 采节点关键字搜索采用闭合区间
        2、b+tree 非叶节点不保存数据，只保存关键字和子节点的引用
        3、b+tree 关键字对应的数据都保存在叶子节点中
        4、b+tree 叶子节点都是顺序的，并且相邻节点具有顺序引用的关系
    
索引的特性：
    原子性：一起成功一起失败
    一致性：操作的数据和状态改变时一致的
    隔离性：一个事务在提交之前对其他事务的可见性设定
    持久性：事务所做的修改是持久的，不会因为系统故障而丢失
    
隔离级别：
    未提交读RU：未解决事务并发问题
    已提交读RC：解决脏读
    可重复读RR：解决可重复读，但是幻读为解决（在innodb中得以解决）
    串行化：最高隔离级别
    
innodb、myisam的区别：
    1、innodb是由第三方开发出来的一款搜索引擎
    2、innodb的数据检索方式不同：
        myisam的数据和索引分别存放在两个不同的文件中，索引的数据区存放的是数据的引用，
        myisam当一个数据通过索引检索到索引所在的数据区时会去通过其中的引用指向数据存放文件中的具体的数据。
        辅助索引和主键索引采用相同的查询方式
        innodb的数据则采用的主键索引的形式，索引和数据放在一个文件中，当一个表中没有索引时，innodb会自动的创建一个隐性的主键索引来提供检索，
        innodb的辅助索引检索时则是先通过辅助索引知道主键索引所对应的主键id，然后再通过主键索引找到对应的数据区
        
innodb的锁：
    1、共享锁/意向共享锁：
    2、排他锁/意向排他锁：
    3、自增锁
    4、临键锁next_key lock
    5、间隙锁Gap lock （临键锁没命中时，降级为间隙锁）
    6、记录锁record lock