国内最专业的IT技术学习网

Mysql数据库

当前位置:主页 > Mysql数据库 >

银河官网:深入剖析 MySQL 索引和 SQL 调优实战

发布时间:2019/09/26标签:   mysql      SQL      调优    点击量:

原标题:银河官网:深入剖析 MySQL 索引和 SQL 调优实战

InnoDB索引实现

如果我们在Col2上建立一个辅助索引,则此索引的结构如下图所示:

先运行看看是否真的很慢,注意设置SQL_NO_CACHE

MySQL优化

如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如下:

如何优化

 

EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

当客户端连接数据库服务器时,服务器会进行主机名解析,并且当DNS很慢时,建立连接也会很慢。

然后开始下一步的分析,既然msgid是被设置为了主键,那一定是全局唯一的,所有,有多少数据量就至少会有多少条msgid;那么检索msg_id基本就是检索整个表了。

如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。

如图所示,如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计

数据库也是一样,但显然要复杂许多,因为不仅面临着等值查询,还有范围查询(>、<、between)、模糊查询(like)、并集查询(or)、多值匹配(in【in本质上属于多个or】)等等。数据库应该选择怎么样的方式来应对所有的问题呢?

从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。

最后看看 type 值,是range,那么就说明这个查询要么是范围查询,要么就是多值匹配。

b+树的索引结构解释

 PRIMARY KEY (`msg_id`,`to_id`), 

这里设表一共有三列,假设我们以Col1为主键,则上图便是一个MyISAM表的主索引(Primary key)示意图。

记录二进制日志不是没有开销的,所以如果你在一个非主节点的复制节点上不需要它的话,那么建议关闭这个选项。

这项设置告知InnoDB是否需要将所有表的数据和索引存放在共享表空间里(innodb_file_per_table = OFF) 或者为每张表的数据单独放在一个.ibd文件(innodb_file_per_table = ON)。每张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。

 `msg_id` bigint(20) unsigned NOT NULL COMMENT '消息ID'

为什么MySQL索引要用B+树,而不是B树?

真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的

幸运的是从MySQL 5.5之后,崩溃恢复的性能的到了很大提升,这样你就可以同时拥有较高的写入性能和崩溃恢复性能了。

这是redo日志的大小。redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。一直到MySQL 5.1,它都难于调整,因为一方面你想让它更大来提高性能,另一方面你想让它更小来使得崩溃后更快恢复。

咱们去图书馆借书也是一样,如果你要借某一本书,一定是先找到对应的分类科目,再找到对应的编号,这是生活中活生生的例子,通用索引,可以加快查询速度,快速定位。

版权信息Copyright © 银河官网 版权所有    ICP备案编号:鲁ICP备09013610号