赞
踩
学习方法这是大部分人都可以掌握的,但是学习的驱动力可能每个人都不太相同。有的人是真正的热爱,有的人是为生活所迫,而有的人是为了证明自己。
我不算是一个热爱编程的人,至少之前一直不算是,对于自己的职业生涯也没有一个很好的规划,一直不清楚自己适合做哪一行。我爸妈就是想让我找一份清闲的工作,朝九晚五那种且不用加班的,让我努力试一下看能不能进学校,前几个月就一直都在准备行测和自然专技的内容,本来也想做一个分享,但是因为行测的某些内容涉及某些敏感词汇无法发表。慢慢已经下定决定要在技术这条路一直走下去,加油!
下面都是一些基础性的东西,没有什么难度,但MYSQL的内容绝不仅仅只是下面的一个大概,可以深入的东西还是挺多的。
Mysql是关系型数据库管理系统RDBMS(Relational Database Management System),在Web应用上十分常用。
MYSQL在数据持久化上我们通常都是使用的InnoDB,但除了它以外还包含以下几种存储引擎:
注意:InnoDB在默认隔离级别下仍然可能出现幻读(已提交事务B对事务A产生影响),InnoDB使用MVCC和next-key locks解决幻读问题,MVCC(Multi-Version Concurrency Control)解决普通读(Consistent Read,快照读)的幻读,next-key locks(锁住本条记录以及索引区间)解决当前读(Locking Read)情况下出现的幻读。
Mysql架构与应用
主从复制结构中读写分离,主写从读:
索引提供指针以指向存储在表中指定列的数据值,再根据制定的排序次序排列这些指针。使用索引类似于使用书的目录,利用索引可以快速查找到所需要的信息。
在数据库中由于数据存储在数据库表中,因此索引是创建在数据库表对象上的,由表中的一个字段或多个字段生成的键组成,这些键存储在数据结构(B-树或哈希表)中,通过Mysql可以快速有效地查找与键值相关联的字段,根据索引的存储类型,可以将索引氛围B-树索引(BTREE)和哈希索引(HASH)。InnoDB和MyISAM存储引擎支持B-树索引。
《Mysql数据库开发实践》一书中是提到Mysql使用InnoDB采用的是B-树,但网上有很多都说是B+树,比如这篇文章:https://zhuanlan.zhihu.com/p/139002393
B-树是一颗平衡多路搜索树,类似于平衡二叉树,不过每个节点可以有更多的子节点,查找性能接近二分查找,使用平衡多路搜索树可以降低平衡二叉树的深度。B+树是B-树的变体,所有关键字存储在叶子结点且所有叶子结点增加一个链指针,查询时间复杂度O(logn)。B+树很好的运用了空间局部性原理(如果存储器某个位置被访问,它附近的位置也会被访问),InnoDB存储引擎的最小存储单元是页(Page),大小16K,一次IO就是读一页。因为索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数所以采用B树结构。
我们可以去Mysql官网看文档:https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html,可以看到文档中提到“Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions: Indexes on spatial data types use R-trees; MEMORY tables also support hash indexes; InnoDB uses inverted lists for FULLTEXT indexes.”即Mysql大部分索引都是存储在B-树(B树)结构上。除了某些特殊的数据类型使用R树,内存表支持Hash索引,InnoDB使用倒排表用于全文索引。
主索引和唯一索引是有区别的,Mysql Innodb中的索引数据结构是 B+ 树,普通索引,也叫做辅助索引,叶子节点存放的是主键值。主键上的索引叫做聚集索引,表里的每一条记录都存放在主键的叶子节点上。唯一索引本质上是辅助索引,然后加了唯一约束。
项目初期数据量相对较少,很多问题可能并不会暴露出来,比如分页查询使用物理分页通过LIMIT关键字取偏移量后面的数据,但是数据量一大,效率就会越来越低。SQL执行效率会对程序运行效率产生影响,所以SQL语句的优化就变得很有必要。
SQL优化的方法总结:
除了SQL语句的优化,我们通常也需要分析一些可能造成慢查询的语句,我们先看Mysql官网(8.0版本)对long_query_time参数的定义,long_query_time变量默认值时10秒,作用域是全局和Session(session会话)。
可以通过Explain关键字(Explain关键字模拟优化器执行SQL查询语句)分析慢查询语句。
举个例子:对基于角色的访问权限控制模型的表结构进行下面的SQL语句,
Explain SELECT `name` FROM `user` where id in (select user_id from `user_role` where role_id in (SELECT id from role where role.name = 'p8'))
上面的分析结果字段含义如下:
SIMPLE | PRIMARY | UNION | SUBQUERY | DERIVED | UNION RESULT | DEPENDENT SUBQERY | MATERIALIZED |
---|---|---|---|---|---|---|---|
简单的select查询,查询中不包含子查询或者UNION | 查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY | 若第二个SELECT出现在UNION之后,则被标记为UNION:若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED | 在SELECT或WHERE列表中包含了子查询 | 在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表中 | 从UNION表获取结果的SELECT | 如果包含子查询的查询语句不能够转为对应的半连接semi-join 的形式,并且该子查询是相关子查询,该子查询的第一个select关键字代表的查询类型就是dependent subquery | 子查询执行后的物化表与外层查询进行连接查询时 |
Using filesort | Using temporary | Using index | Using where | Using join buffer | impossible where | select tables optimized away | distinct |
---|---|---|---|---|---|---|---|
说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取 | 使用了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。 | 表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行。如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。 | 表明使用了where过滤 | 表明使用了连接缓存,比如说在查询的时候,多表join的次数非常多,那么将配置文件中的缓冲区的join buffer调大一些 | where子句的值总是false,不能用来获取任何元组 | 在没有GROUPBY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化 | 优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作 |
上面的例子里,第一行是select的子查询语句通过全表扫描进行查询,没有走索引。第二行则是从user表中查对应的信息通过唯一性索引进行查询,走了主键索引(索引占用4字节),索引引用的是子查询结果集的user_id。第三行和第四行记录都是子查询执行后与外层查询进行连接查询,第三行查的是role表,第四行查的是user_role表。
其实这个查询就是查询为P8级别的角色的用户,这样去查效率较低。可以通过JOIN把多个表关联起来,如下可以达到相同的效果,再EXPLAIN一下,结果如下,
SELECT u.`name` FROM `user` AS u JOIN `user_role` AS ur ON u.id = ur.user_id JOIN role as r on r.id = ur.role_id WHERE r.`name`='P8'
上面只是简单的Mysql基本内容和一些例子,只是起到一个引导作用。Mysql查询性能问题大部分都是SQL语句效率太低,也要看业务的数据量大小,具体问题具体分析。
补充:索引覆盖(Covering Index)即不需要回表操作,在explain分析时显示为Using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询。
另外,在实战中,比如使用Springboot + mybatis-plus + druid搭建,需要分析自己的SQL语句之前可以在配置中加上如下配置开启SQL语句输出打印,
- mybatis-plus:
- configuration:
- log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
在跑单测试就可以输出下面的预编译语句和影响的行,然后粘贴SQL去数据库进行分析。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。