Mysql架构体系

mysql的基本架构示意图

MySql可以分为Server层和储存引擎层

Server层包括:连接器,查询缓存,分析器,优化器,执行器等

      基本流程:

                        sql语句先进入连接器,连接数据库

                        进入后先判断缓存里面有没有数据,有就直接返回,没有的话进入下一步分析器

                       在分析器中检测语法是否正确,正确的话进入优化器

                        优化器会对sql语句进行适当优化,完成后进入执行器

                        执行器会根据当前mysql设置引擎,选择运行操作

                        引擎层和数据层是分离的,可以在执行时随便调用引擎,而不影响数据  

Mysql引擎

        常见的三种引擎

                innoDb 

                        可以支持事务,行锁,外键

                        功能强大,默认使用

                MyISAM

                        功能不多

                        存储速度快,占用少

                Memory

                        速度快,主要是直接存在内存,所以需要有足够的内存

                        生命周期短,一般是一次性的

如何选择引擎

                一般情况表优先选择innoDb

                一些不是很重要的日志表可以用MYISAM

               临时表可以用Memory

内存表VS临时表VS试图

                        内存表就是储存使用了Memory

                        临时表就是mysql自己创建的储存数据的表,用完就释放

                        试图虚拟的表,用于隐藏或储存时的sql语句

索引

        在大量数据下,要想提高效率就要使用索引

                索引是在储存的数据结构之外,就是在引擎层的数据

                        索引的创建,会根据数据的多少,一点程度上影响了插入和删除的效率

                        所以使用索引也需要控制,不能乱用

        索引的基本结构

                        B+树(Tree)索引

                                在二叉树的基础上有多个分支,提高了效率

                                B+树就是在B树的基础上,使用叶子节点储存数据,并使用链表连接

                                 在InnoDB中的B+树有多高

                                        一个数节点就是一个索引页,一个索引页可以存16K数据 

                                         高度为2的时候：n8+（n+1） 6= 16* 1024，最终n为1170的，如果数据的大小是1k，那么能够存储的数据量是1171*16=18736.          

                                         高度为3时,储存数量是1171x1171x16=21939856

                                        高度为4时,可以储存几十亿条数据以上

        Hash索引

                主要是数组,链表,红黑树

                直接通过key和算列算法就可以找到对应的值

                mysql在8.0.18之后支持hash

      R-Tree索引

                空间索引是MYISAM引擎的一个特殊索引类型,主要是地理空间数据类型,用的少

     FullText全文索引

                倒叙索引的方式,用于文本,或者字符串比较多的字段

                将数据通过分词,装换成不同的单词,然后用这些单词做索引,连接到具体数据

        索引的分类

                        单列索引

                                唯一索引,必须要求字段的内容是唯一,并且值不能是null        

                                主键索引,是每个表的聚簇索引,直接连接的是具体数据

                                其他的所有都是非聚簇索引,叶子节点储存的不是具体的数据,而是主键的值

                                在使用其他的索引查询的结果,是主键,然后再使用主键查询,既是回表

        普通索引

                在使用一般的索引的时候是需要防止回表提高查询速度的

                可以通过索引覆盖防止回表

                查询的结果是索引的一部分,那就需要回表

        组合索引

                可以为多个字段联合在一个表组成索引

                在使用组合索引时要考虑到,最左前缀原则

                          有多个字段组成的复合索引,在使用where查询的时候,必须要从最左边开始

                          如果跳字节进行查询,索引会失效

        前缀索引

                如果一个字段内容很多,在创建索引的时候我们选择一部分作为索引

                可以有效减少索引长度 

        全文索引

                如果储存的字数有几千字或者更多,可以使用全文索引

                通过match进行查询,但不支持中文

分析工具 

         Show profiles

                        是mysql用来分析当前sql语句执行的资源消耗情况,可以用于sql调优测量

        explian

                        可以分析需要执行的sql情况

                                id:执行sql语句

                                select_type:查询类型,一般都是simple

                                type(重点):表示连接类型,性能由好到差的类型:system,const,eq_ref,ref,range,index,all

                             possible_keys:预测用到的索引

                             key:实际用到的索引

                             key_len:索引的长度越短越好

                             ref:是否引用其他表

                             rows:通过应用查询的数据量,在innodb引擎中,是一个估算值,可能不准确

                              filter(重点):放入server层的数据,是否都是想要查询的数据

                                    Extra(额外):判断当前sql是否使用了某个规则

                                        null:出现回表情况

                                        using index:使用了索引覆盖的规则,不会回表,性能最高

                                        using where:使用where扫描过滤

                                                如果只有where还是会出现回表,最好避免

                                                如果同时出现using index ,using where,不会出现回表,还是要使用where进行过滤

                                        using index condition:索引下推,是打破最左原则之后的优化

                                                        将本来是server层过滤操作,推到引擎层进行操作

索引的使用原则

        在数据量大时,在经常需要搜索的列上,可以加快索引的查询速度

        在经常需要排序(order by) ,分组(group by),和distion 列上加索引,可以加快排序查询的时间(单独使用order by 用不了索引,可以考虑加上where或者limit)

        尽量使用区分度高的列作为索引,尽量建立唯一索引,区别度越高,查询速度越快

        如果是字符串类型的字段,字段越长,可以针对字段的特性使用前缀索引

        哪里使用组合索引,减少单列索引,联合索引很多时候可以覆盖索引,节省空间,拒绝回表,提高效率

        控制索引的数量,索引不是越多越好,索引越多,维护的代价越大,会影响增删改的效率

        如果索引不能存null值,在建表的时候使用not null约束它,当优化器知道每类null值时,他可以更好的确定哪个索引,有效用于查询

        对于为text,image和bit数据类型的列不应该增加索引

索引失效

        最左原则,在使用复合索引的时候,第一个字段不是复合索引的最左边的字段,那么索引会失效,在最左原则中,like只有%最后的时候,会使用索引下推,其他情况都会失效

        如果有or条件,只要其他字段没有索引就会失效,尽量少使用or

        出现null,索引会失效

SQL优化

        库表结构优化

        查询优化

        索引优化,主要掌握常用的索引规则,

                聚簇索引

                回表

                最右原则

                索引覆盖

                索引下推

库表结构优化

        主键设计,尽量使用数值类型,使用自动增长,避免使用字符串类型

        主键如果最大值用完之后会怎么样

                如果是自定义的主键,那么不会自动增长

                mysql有一个内置的主键row_id,到上线之后会从0开始

                一般情况下使用igint,主键不可能用完

char,varchar,和text的选择

        如果短,固定使用char

        超过4000字,使用text,并且分表

        其他情况都使用varchar

关于时间类型

        最好使用Date类型,但是不会保存时间

        如果储存时间优先选择Timestamp而不是Datetime

尽量别使用Blob类型

查询优化

        order by

                出现using filesort,说明要先将对应字段进行排序,在根据排序完的字段,从数据库中拿出来

                        而且在file sort时候还要用到sort_buffer如果,sortbuffer储存空间不足,还会借助文件储存

                        解决方案就是添加索引,并且保证不回表,排序顺序要和索引顺序一致

                        如果不可避免的出现了file sort,可以通过修改sort_buffer的最大值,来防止出现文件储存

        group by

                不使用索引会出现Using temporary

                需要为分组的字段添加索引

        count优化

                1，count(主键)：InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加（主键不可能为null）；

                2，count(字段)：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null约束；innoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

                3，count(1)：InnoDB引擎遍整张表，但不区值。服务层对于返回的每一行，方一个数字"1"进去，直接按行进行累加。

                4，count(*): InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直进行按行累加

                5，按照效率排序的话，conut(字段)<count(主键 id)<count(1)<=count(`*`),所以尽量使用count（`*`）

        优化Limit

                慢的主要原因是偏移量过大,需要扫描整个表

                可以通过where优化过滤

                也可以利用主键的特效,进行子表联查

join优化

        DBA不让使用join,因为join使用了笛卡尔积,假如只有两个10W级别的表相连,直接造成上百亿的查询

        没有索引的时候

                Simle Nested-loop Join(8.0.18之前)mysql为了优化join操作使用了join buffer的缓存，但是如果缓存不足，就会使用文件存储，这样会大量导致的

                Hash join的方式,性能也得到了大提升

                优化方式

                        增大join_buffer_size(缓存)

                        可以使用临时表

        有索引的时候

                需要开启BKA算法,来提高两个表的效率