mysql高级-1

枫卿杨 2020-05-24

134

mysql的分层式的架构方式，其中主要体现的的区别在于，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离，这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

引擎的选择—MyISAM InnoDB NDB Archive Federated Memory Marge Partner Community Custom

优化的考虑方向：

1.连接层：

最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似tcp/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2.服务层

第二层架构主要完成大多少的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3.引擎层

存储引擎层：负责MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。

4.存储层

1.linux中常用命令

show engines;

MyISAM和InnoDB区别

在读写上面：读更多的使用MyISAM，写更多的用InnoDB

（这里如果搭建主从架构，这里可以主的为InnoDB,从机使用MyISAM）

二、（重点）索引优化分析

可能出现的问题：性能下降SQL慢，执行时间长，等待时间长

分析：查询语句写的有问题-索引失效-关联查询太多join（设计缺陷或不得已的需求）--服务器调优以及各个参数设置（缓冲、线程数等）

索引：1.单值 2.复合

举例：

单值—select * from user where name=’’;---只是对表的某一个字段进行查询

索引：create index idx_user_name on user(name);---idx_user_name(一般索引命名规则)

复合—select * from user where name=’ ’ and email=’ ’;

对热点查询语句建立索引：

create index idx_user_nameEmail on user(name,email);

2、常见通用的Join查询

（1）SQL执行顺序

1.手写

2.机读方式

3.进行SQL解析

join使用的几种方式

…

索引的概念：

官方对索引的定义：索引（index）是帮助msyql高效获取数据的数据结构

本质：索引是数据结构----排好序的快速查找数据结构

简单理解：排好序的快速查找数据结构---可以影响where后面的查找

order by的排序

数据库系统维护满足特定查找算法的数据结构，按照数据结构上高级查找算法，即B树或B+树

一般索引，以索引文件的形式存储到磁盘上面

索引的

优势：

1.类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本

2.通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

劣势：

1.实际索引也是一张表，该表保存了主键与索引字段，指向实体表的记录，所以索引列也是要占用空间的。

2.索引会提高查询速度，会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE,在我们更新表的时候，一方面要保存数据，还要保存索引文件每次更新添加了索引列的字段，都会因为调整因为更新带来的键值变化后的索引信息

3.索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询效率

索引：

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引（这里的建议，一张表最多建立的索引不要超过5个）

唯一索引：索引列的值必须唯一，但允许有空值

复合索引：即一个索引包含多个列

基础语法如下：

按照第二种方式建立索引：

这里了解相应的的mysql索引结构—

1.BTree索引（2.Hash索引 3.full-text全文索引 4.R-Tree索引）（了解）

B+树的结构是已经规定好的，这里我们需要对其使用索引的过程做详细的了解。

下面是对我们建立索引的基本原则：

（1）以下情况建立索引

（2）以下情况不要建立索引

这里举个例子：

（3）性能分析

1.MySql Query Optimizer

2.MySQL常见瓶颈

3.Explain(分析解析计划)

基本概念：使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈问题。

如何使用：Explain + SQL语句

对于Explain的分析

索引优化分析中所包含的信息：

现在我们对其中的字段进行解析：

（1）id：

总结：这里其实主要看的是表的读取顺序---原则：id值越大，表的读取顺序就越前。当id值相同的时候，就按照table字段中显示的，按照顺序走。

小表驱动大表，进行进一步优化。

（2）select_type

如下图所示，各字段的含义：

（3）table 显示这一行的数据是关于哪张表的

（4）type

字面意思就是访问类型

访问类型排列：

一般工作中常用的就是下面那行的排列

细则讲解：

1）system

表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个几乎可以忽略不计

2）const

表示通过索引一次就找到了，const用于比较primary key（主键）或者unique（唯一）索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量

3）eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

4）ref

非唯一性索引扫描，返回匹配某个单独值的所有行

这里的本质上也是一种索引访问，它返回所以匹配某个单独值的行，

然而，它可能会找到多个符合条件的行，属于查找和扫描的混合体

5）range

只检索给定范围的行，使用一个索引来选择行。Key列显示使用了哪个索引

一般就是在你的where语句中出现了between、<、>、in等的查询

这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点

不用扫描全部索引

6）index

Full Index Scan, index与All区别为index类型只遍历索引树，这通常比ALL快，因为索引文件通常比

数据文件小（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

这里可以理解为，我们进行查询的时候，还是通过索引来进行查询。

解析：这里id为主键索引，我们进行查询的时候，相当于对全索引扫描

7）all

Full Table Scan,将遍历全表以找到匹配的行

备注：一般来说，得保证查询至少达到range级别，最好能达到ref级别

（4）possible_keys

概念：显示可能应用在这张表的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出

但不一定被查询实际使用

（5）key

实际使用的索引。如果为NULL，则没有使用索引

查询中若使用了覆盖索引，则该索引仅出现在key列表中

我们在实际使用的时候，这两个一般是结合起来一起使用的。前一个是分析，后面一个是实际使用情况。

#覆盖索引，简单来说，就是我们在进行查询字段的时候，查询的字段正好，和我们的索引建立的字段正好相同，

（6）key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好

解析：key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得到的，不是通过表内检索出的。（所以，在我们使用expain进行解析时，在结果相同的情况下，精度越小越好）

（7）ref

概念：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。（p27）

（这里其实是对索引的进一步的反馈，是告诉我们用到了哪些索引）

（8）rows

概念：根据表统计信息及索引选用情况，大致估算找到所需的记录所需读取的行数（越少越好）

解析：就是在查询过程中，我们使用索引的情况，需要查询多少行记录，就可以得到相应的结果。

（9）Extra

字段分析：

1）Using filesort

概念：mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取

MySQL中无法利用索引完成的排序操作称为“文件排序”

解析：我们在进行例如Order by进行排序时，不是按照索引进行排序，这种情况也是未使用到索引，情况较为不好

2）Using temporay

使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表，常见于排序order by和分组查询group by

（出现这种情况，sql的查询效率就会大大降低，对我们整体上的查询效率有着不小的影响）

尽可能的，在我们使用Group by或者Order by的时候，使用索引里有的进行建立，在表中数据量过大的时候，就会出现较大的问题。（临时表的创建，是十分伤系统性能的，数据库自己需要自己进行申请空间，增加系统负担）

3）USING index

概念：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错

如果同时出现using where，表明索引被用来执行索引键值的查找

如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

如下图所示：

这里我们对“覆盖索引”进行解读：

覆盖索引（Covering Index）

理解方式一：就是select的数据列只用从索引中就能够取得，不必读取数据行，Mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，查询列要被所建的索引覆盖

理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫覆盖索引。

#注意：

如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *

因为如果要将所有字段一起做索引会导致索引文件过大，查询性能下降。

前三个字段比较重要，详细记忆！！！

4）Using where：表示使用了where条件查询语句

5）using join buffer：使用了连接缓存

6）impossible where：where子句的值总是false，不能用来获取任何元组

7) select tables optimized away

8）distinct

4、索引优化分析实操

（1）索引分析：

1）单表：

CREATE TABLE IF NOT EXISTS `article`(

`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

`author_id` INT (10) UNSIGNED NOT NULL,

`category_id` INT(10) UNSIGNED NOT NULL ,

`views` INT(10) UNSIGNED NOT NULL ,

`comments` INT(10) UNSIGNED NOT NULL,

`title` VARBINARY(255) NOT NULL,

`content` TEXT NOT NULL

);

INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES

(1,1,1,1,'1','1'),

(2,2,2,2,'2','2'),

(1,1,3,3,'3','3');

SELECT * FROM ARTICLE;

#查询category_id为1且comments大于1的情况下，views最多的articel_id

EXPLAIN SELECT id,author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;（这里我们建立多值索引，可能会出现索引失效的问题）

#删除索引

DROP INDEX idx_article_ccv ON article

#结论：很显然，type是ALL即最坏的情况，Extra里还出现了Using filesort，也是最坏的情况。优化是必须的

#优化方案：

#1.1新建索引+删除索引

CREATE INDEX idx_article_ccv ON article(category_id,comments,views);

#1.2第二次EXPLAIN

#结论

#type变成range，这个是没有问题的，这里extra里使用Using filesort 是无法接受的

#这里已经建立的索引，是按照Btree索引的工作原理，先排序category_id

#如果遇到相同的category_id则再排序comments,如果遇到相同的comments则再排序views

#当comments字段再联合索引里处于中间位置，因comments>1条件是一个范围值（所谓的range）

#MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效

CREATE INDEX idx_article_cv ON article(category_id,views);

#查询索引信息

SHOW INDEX FROM article

2）两表：

CREATE TABLE IF NOT EXISTS `class`(

`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

`card` INT (10) UNSIGNED NOT NULL

);

CREATE TABLE IF NOT EXISTS `book`(

`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

`card` INT (10) UNSIGNED NOT NULL

);

INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));

INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));

3）三表的基础+外加一张表：

CREATE TABLE IF NOT EXISTS `phone`(

`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

`card` INT (10) UNSIGNED NOT NULL

)ENGINE = INNODB;

INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));

这里我们对sql优化中的NestedLoop循环总次数：永远用小结果集驱动大的结果集

优先优化NestedLoop（嵌套循环）的内层循环：

保证Join语句中被驱动表上Join条件字段已经被索引

当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，这里不需要对JoinBuffer的设置。

（2）索引失效（应该避免）

只对重点部分，进行笔记：

#最佳左前缀法制：如果索引了多列，要遵循最做前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列

#不在索引列上做相应的范围，这样也可能会导致索引失效。

如图，这里进行解析，我们可以看到上面进行查询，使用到了索引。第二个加了索引，分析结果方式不同。

#这里通过第三点的结果，范围之后全表扫描，索引就就可能会失效

#尽量使用覆盖索引（只访问索引的的查询（索引列和查询列一致）），最好不要用select *

#like以通配符开头（‘%abc…’）MySQL索引失效会变成全表扫描。

解决like ‘%字符串%’ 时索引不被使用的方法

解决方案：建立覆盖索引，这里正好进行使用索引的时候，匹配相应的索引字段，而不会导致索引失效

如下图所示。

#注意两点：

1.将%号通配符，放在Like查询语句的右边

2.使用like ‘%字符串%’ 的时候，最好以覆盖索引的方式去解决。

#注意点：varchar类型，在做数据查询的时候，记得一定要带单引号，因为在不带单引号的情况下，会对其中的类型进行一次类型转换，有违背上面所说的，第三条：不要再索引上做任何的计算，类型转化等等，再数据量过大的时候，查询效率会大大的下降。

注意点：在用到group by的时候，这里进行分组的时候，最好和索引的顺序相同，保证用到索引，不产生“文件排序”，以及using tempory 临时表的产生。

建议：

1.对于单键索引，尽量选择针对当前query过滤性更好的索引

2.在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

3.在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引。

4.尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

小总结：

小口诀：

---------------------------------------------------------------------------

学习来源：

https://www.bilibili.com/video/BV1KW411u7vy?p=1

mysql

文章转载自枫卿杨，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

mysql高级-1

评论