Mysql优化

优化查问语句的性能是 MySQL 数据库治理中的一个重要方面。在优化查问性能时，抉择正确的索引对于缩小查问的响应工夫和进步零碎性能至关重要。然而，如何确定 MySQL 的索引抉择策略？MySQL 的优化器是如何抉择索引的？ 在这篇《索引生效了？看看这几个常见的状况！》文章中，咱们介绍了索引区分度不高可能会导致索引生效，而这里的“不高”并没有具体量化，实际上 MySQL 会对执行打算进行老本估算，抉择老本最低的计划来执行。具体咱们还是通过一个案例来阐明。 案例还是以人物表为例，咱们来看一下优化器是怎么抉择索引的。 建表语句如下： CREATE TABLE `person` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(64) NOT NULL, `score` int(11) NOT NULL, `age` int(11) NOT NULL, `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_name_score` (`name`,`score`) USING BTREE, KEY `idx_age` (`age`) USING BTREE, KEY `idx_create_time` (`create_time`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;而后插入 10 万条数据： create PROCEDURE `insert_person`()begin declare c_id integer default 3; while c_id <= 100000 do insert into person values(c_id, concat('name',c_id), c_id + 100, c_id + 10, date_sub(NOW(), interval c_id second)); -- 须要留神，因为应用的是now()，所以对于后续的例子，应用文中的SQL你须要本人调整条件，否则可能看不到文中的成果 set c_id = c_id + 1; end while;end;CALL insert_person();能够看到，最早的 create_time 是 2023-04-14 13:03:44。 ...

下图展现了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相干的 7 种用法。 具体合成如下： 1.INNER JOIN（内连贯） SELECT <select_list> FROM Table_A AINNER JOIN Table_B BON A.Key = B.Key2.LEFT JOIN（左连贯） SELECT <select_list>FROM Table_A ALEFT JOIN Table_B BON A.Key = B.Key3.RIGHT JOIN（右连贯） SELECT <select_list>FROM Table_A ARIGHT JOIN Table_B BON A.Key = B.Key4.OUTER JOIN（外连贯） SELECT <select_list>FROM Table_A AFULL OUTER JOIN Table_B BON A.Key = B.Key5.LEFT JOIN EXCLUDING INNER JOIN（左连贯-内连贯） SELECT <select_list> FROM Table_A ALEFT JOIN Table_B BON A.Key = B.KeyWHERE B.Key IS NULL6.RIGHT JOIN EXCLUDING INNER JOIN（右连贯-内连贯） ...

MySQL反对的数据类型十分多，抉择正确的数据类型对于取得高性能至关重要。本文将介绍MySQL的数据类型，以及通过数据类型简略介绍对应的开发标准。 注：在本章节中所提到的严格模式，指的是STRICT_TRANS_TABLES和STRICT_ALL_TABLES两个中的一个启用或者都启用。 1 . 抉择优化的数据类型MySQL反对的数据类型有很多，抉择正确的数据类型对于取得高性能至关重要。咱们在抉择数据类型上，有几个简略的准则。 更小的通常更好个别状况下，应该尽量应用能够正确存储数据的最小数据类型。例如，只须要存100以内的整数，TINYINT更好。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，解决时须要的CPU周期也更少。 然而要确保没有低估须要存储的值的范畴。因为在schema中的多个中央减少数据类型的范畴是一个十分耗时和苦楚的操作。如果无奈确定哪个数据类型是最好的，就抉择你认为不会超过范畴的最小类型（如果零碎不是很忙或者存储的数据量不多，或者是在能够轻易批改设计的晚期阶段，这时候批改数据类型比拟容易）。 简略就好简略数据类型的操作通常须要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规定（排序规定）使字符比拟比整型比拟更简单。比方：应该应用MySQL内建的类型（DATE，TIME，DATETIME）而不是字符串来存储日期和工夫；以及应该用整型存储IP地址。 尽量避免NULL很多表都蕴含可为NULL（空值）的列，即便应用程序并不需要保留NULL也是如此。这是因为可为NULL是列的默认属性（如果定义表构造时没有指定列为NOT BULL，默认都是容许为NULL的）。通常状况下，最好指定列为NOT NULL，除非真的须要存储NULL值。 如果查问中蕴含可为NULL的列，对MySQL更难优化。因为可为NULL的列使得索引、索引统计和值比拟都更简单。可为NULL的列会应用更多的存储空间，在MySQL里也须要非凡解决。当可为NULL的列被索引时，每个索引记录须要一个额定的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。 通常把可为NULL的列改为NOT NULL带来的性能晋升比拟小，所以（调优时）没有必要首先在现有schema中查找并批改掉这种状况，除非确定这会导致问题。然而，如果打算在列上建索引，就应该尽量避免设计成可为NULL的列。 当然也有例外，例如，InnoDB应用独自的位（bit）存储NULL值，所以对于稠密数据（很多值为NULL,只有少数行的列有非NULL值）有很好的空间效率。但这一点不适用于MyISAM。 下一步是抉择具体类型。很多MySQL的数据类型能够存储雷同类型的数据，只是存储的长度和范畴不一样、容许的精度不同，或者须要的物理空间（磁盘和内存空间）不同。雷同大类型的不同子类型数据有时也有一些非凡的行为和属性。 例如，DATETIME和TIMESTAMP列都能够存储雷同类型的数据：工夫和日期，准确到秒。然而TIMESTAMP只应用DATETIME一半的存储空间，并且会依据时区变动，具备非凡的自动更新能力。另一方面，TIMESTAMP容许的工夫范畴要小得多，有时候它的非凡能力会成为阻碍。 2 . 整数类型整数类型是数据库中最根本的数据类型，包含整数和实数。如果存储整数，能够应用：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。别离应用8、16、24、32、64位存储空间。它们能够存储的值的范畴从-2(N-1)到2(N-1)-1,其中N是存储空间的位数。 MySQL能够为整数类型指定宽度。例如INT(11)，对大多数利用这是没有意义的：它不会限度值的非法范畴，只是规定了用来显示字符的宽度。对于存储和计算来说，INT(1)和INT(20)是雷同的。 整数类型有两个属性：UNSIGNED和ZEROFILL。 整数类型有可选的UNSIGNED属性，示意不容许负值，这大抵能够使负数的下限进步一倍。例如TINYINT UNSIGNED能够存储的范畴是0 ~ 255，而TINYINT的存储范畴是-128 ~ 127。 ZEROFILL是数字前须要填充一些0值的时候应用的。例如‘0001’，‘0002’，比方咱们常常看到的股票代码。在应用ZEROFILL参数时，MySQL会主动为该列增加UNSIGNED属性。ZEROFILL也只是一个显示属性，底层存储仍是一般整数。如果存储的数据长度超过ZEROFILL定义的宽度时，此数据会被残缺的显示进去而不进行0的填充；如果存储的数据长度小于设定的宽度，则主动填充0。 3 . 实数类型实数是带有小数局部的数字。然而，它们不只是为了存储小数局部；也能够应用DECIMAL存储比BIGINT还大的整数。MySQL既反对准确类型，也反对不准确类型。 FLOAT和DOUBLE类型是用来示意近似数值的数据类型，应用规范的浮点运算进行近似计算。单精度浮点数（FLOAT）应用4个字节存储，双精度浮点数（DOUBLE）应用8个字节存储。 MySQL容许非标准语法：FLOAT(M,D),DOUBLE(M,D)。M和D别离示意精度和标度。M是数据的总长度，D是小数点后的保留长度。比方，定义为FLOAT(7,4)的一个列能够显示为-999.9999。MySQL在保留值时会进行四舍五入。因而在FLOAT(7,4)列内插入999.00009的近似后果是999.0001。 如果插入值的精度高于理论定义的精度，零碎会主动进行四舍五入解决，使插入的值合乎咱们的定义。所以在一些须要准确小数的状况下，比方：财务工资类型这种场景，请不要应用FLOAT和DOUBLE。 从MySQL 8.0.17开始，不倡议应用非标准语法，并且在未来的MySQL版本中将删除对FLOAT(M,D)和DOUBLE(M,D)的反对。 SQL规范容许在关键字FLOAT前面的括号内用来指定精度（但不能为指数范畴），就是FLOAT(p)。FLOAT(p)中的p也是示意精度（以位数示意），但MySQL只应用该值来确定列的数据类型为FLOAT或DOUBLE。当 0≤p≤24 时，MySQL 把它当成 FLOAT 类型，当 25≤p≤53 时，MySQL 把它当成 DOUBLE 型。 咱们发现：此种类型的FLOAT只能保障前6位整数不四舍五入，而FLOAT(M,D)则不会这样。 DECIMAL和NUMERIC类型存储准确的数值类型，比方财务数据、足球比赛中的赔率等等。在MySQL中，NUMERIC是以DECIMAL来实现的。因而无关DECIMAL的阐明同样实用于NUMERIC。 MySQL中DECIMAL以二进制格局存储值。每4个字节存9个数字。这种存储形式对于整数与小数局部是离开存储的。每9个数字须要4个字节，剩下的数字所需的存储空间如下所示： 图4-1 举例来说，DECIMAL(18,9)小数点两边各有9个数字，因而整数和小数局部别离各须要4个字节，小数点自身占1个字节。DECIMAL(20,6)有14个整数和6个小数，整数局部中的9个数字须要4个字节，剩下的5个数字须要3个字节；小数局部6个数字须要3个字节。 在DECIMAL列申明中，能够（通常是）指定精度和小数位数。例如：salary DECIMAL(5,2)。其中，5是精度，2是小数位数。精度示意值存储的有效位数，小数位数示意小数点后能够存储的位数。 规范语法要求DECIMAL(5,2)可能存储具备五位数字和两位小数的任何值。因而能够存储在salary列中的值的范畴是从-999.99到999.99。 在规范语法中，语法DECIMAL(M)等价于DECIMAL(M,0)。MySQL也反对这种变体。默认的M是10。 如果小数位数是0，表明DECIMAL不含小数局部。 小数点和正数的‘-’符号不包含在M中。DECIMAL反对的M为65，D是30。如果调配给此类型的值小数点后位数超过指定的标度D容许的范畴，值将按标度D进行转换（准确的行为是特定于操作系统的，然而通常是将其截断为容许的位数）。 DECIMAL列不存储结尾的+、-和0数字。如果你向DECIMAL(5,1)的列中插入+0003.1，MySQL会存储为3.1。对于正数，‘-’字符不会被存储。 NUMERIC和FIXED都是DECIMAL的同义词。 咱们能够通过试验来看下DECIMAI数据类型。 官网介绍的DECIMAL是高精度类型，但当产生截断时，也会呈现四舍五入的景象。所以在设置精度和标度的时候要足够长，不让它产生截断数据的操作。 因为CPU不反对对DECIMAL的间接计算，所以在MySQL5.0及更高版本中，MySQL服务器本身实现了DECIMAL的高精度计算。相对而言，CPU间接反对原生浮点计算，所以浮点运算显著更快。 因为须要额定的空间和计算开销，所以应该尽量只在对小数进行准确计算时才应用DECIMAL，例如存储财务数据。但在数据量比拟大的时候，能够思考应用BIGINT代替DECIMAL，将须要存储的货币单位依据小数的位数乘以相应的倍数即可。假如要存储财务数据准确到万分之一分，则能够把所有金额乘以一百万，而后将后果存储在BIGINT中，这样能够同时防止浮点存储计算不准确和DECIMAL准确计算代价高的问题。 ...

本篇是介绍MySQL执行打算的铺垫，明天终于想好了该如何组织这部分内容，先是大抵介绍查问的实现，再由此引出执行打算。概述咱们日常的查问，根本能够分为三类: 单表查问子查问连贯查问这三种能够组合，也能够离开，下面的程序也是咱们学习SQL的程序，咱们上面介绍其实现，也是依照下面这种程序。看本篇之前倡议先看这个本篇的前几篇: MySQL优化学习手札(一)MySQL优化学习手札(二)MySQL优化学习手札(三)当然如果你对MySQL的B+树索引比拟相熟也能够不看。 单表拜访办法咱们写了一个单表查问的语句，MySQL是如何获取咱们查问语句所对应的记录的呢： SELECT * FROM Student WHERE ID = 1;咱们疏忽语法解析、连贯建设这些步骤，这些都搞定了，那么MySQL该如何定位记录呢？MySQL中执行查问的形式一共有以下两种: 全表扫描(一条记录一条记录的去比拟)应用索引进行查问，索引也有不同的类型，所以就算是应用索引进行查问，也分为几种不同的状况: 针对主键或惟一二级索引的等值查问针对一般二级索引的等值查问针对索引列的范畴查问间接扫描整个索引在MySQL中执行查问语句的形式称之为拜访办法或者拜访类型。 通过主键列等值匹配来定位记录-constSELECT * FROM Student WHERE ID = 1; ID是主键Id是Student这张表的主键，这里让咱们在回顾一下MySQL存储数据的根本构造: InnoDB将数据划分为若干页，以页作为磁盘和内存之间交互的根本单位，InnoDB中页的大小个别为16KBInnoDB存储引擎会主动为主键(如果没有它会主动帮咱们增加)建设聚簇索引，聚簇索引的叶子结点蕴含残缺的用户记录。每个索引都对应一颗B+树，B+树分为好多层，最下边一层是叶子结点，其余的是内结点。所有的用户记录都存储在B+树的叶子结点。所有的目录项记录都存储在内结点咱们也能够为本人感兴趣的列建设二级索引，二级索引的叶子结点蕴含的用户记录由索引列+主键组成，所以如果想通过二级索引来查找残缺的用户记录的话，须要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找残缺的用户记录。B+树的每层结点都是依照索引列值的从小达到的程序排序而组成了双向链表，而每个页内的记录(不论是用户记录还是目录项记录)都是依照索引列的值从小达到的程序而造成了一个单链表。如果是联结索引的话，则页面和记录先依照联结索引前边的列排序，如果该列的值雷同，再依照联结索引后边的列进行排序。 也就是ID这一列是聚簇索引，同时按ID进行排序，所以这个相当快，能够先用定位到目录项地行为这一列位于哪个数据页，定位到之后，再用二分查找定位这条记录在哪个地位。当初让咱们为Student再加上一列name，并为该列建设惟一索引，咱们去执行如下查问: select * from student where name ='aa'也同聚簇索引相似，然而name列因为是非聚簇索引列，叶子结点没有残缺的记录，定位到name= ‘aa’这条记录后，还用用这条记录对应的主键去主键索引列去查残缺的记录。即使有回表的代价，MySQL的开发人员依然认为这种查问形式是十分快的，将这种拜访办法定义为: const,也就是常数级别。但如果查问NULL值，状况就又有所不同: select * from student where name is null因为惟一索引并不限度NULL值的数量，所以下面的查问语句可能会拜访到多条记录。 ref用惟一索引列去查找NULL值，会查问到多行，这种状况和用非惟一索引列去匹配记录相似，查问步骤和用惟一索引列去查问是一样的，定位这条记录在哪个数据页，而后到具体的页外面去匹配记录，因为咱们是select * , 所以还要回表查问。 如果匹配的记录比拟少，回表的代价还是比拟低的，MySQL就更偏向于应用索引+回表的形式来查找，采纳二级索引进行等值查问记录的形式，MySQL将其定义为ref。 但对于某个蕴含多个索引列的二级索引列来说，只有最右边的间断索引列是与常数的等值比拟就可能采纳ref的拜访办法。至于为什么是可能起因在于还是老本的掂量，如果你是select * ， 这就要回表。如果查问的记录比拟多，让MySQL感觉与其索引列+回表还不如间接扫描全表的话。 当初让咱们再为Student再加: age, sex，同时为age、sex建一个一般的索引。如果咱们查问的语句写成了上面这样: select * from student where age = '18' and sex > '女'这种拜访办法在MySQL中的查问级别就不是ref，起因在于对sex这一列应用的是范畴查问。 ref_or_null依据一般索引进行匹配，但同时查找该索引列为null的值，像上面这样: select * from student where name = 'aa' and name is null这种查问级别在MySQL中咱们称之为ref_or_null. ...

引言 内容为慕课网的"高并发 高性能 高可用 MySQL 实战"视频的学习笔记内容和集体整顿扩大之后的笔记，本节内容讲述的索引优化的内容，另外本局部内容波及很多优化的内容，所以学习的时候倡议打开《高性能Mysql》第六章进行回顾和理解，对于Mysql数据的开发同学来说大抵理解外部工作机制是有必要的。 因为文章内容过长，所以这里拆分为两局部，高低局部的内容均应用sakila-db，也就是mysql的官网案例。第一局部讲述优化的实践和Mysql过来的优化器设计的缺点，同时会介绍更高的版本中如何修复欠缺这些问题的（然而从集体看来新版本那些优化基本算不上优化，甚至有的优化还是照抄的Mysql原作者的实现的，倒退了这么多年才这么一点问题还是要归功于Oracle这种极致商业化公司的功绩）。 如果内容比拟难，能够追随《Mysql是怎么样运行》集体读书笔记专栏补补课，集体也在学习和同步更新中。 地址如下：https://juejin.cn/column/7024...。 【知识点】Mysql索引内容的介绍索引的应用策略和应用规定查问优化排查，简略理解Mysql各个组件的职责前置筹备sakila-db sakila-db是什么？国外很火的一个概念，指的是国外的电影租赁市场应用租赁的形式进行电影的观看非常受外国的喜爱。这里介绍是因为后续的内容都用到了这个案例。所以咱们须要提前把相干的环境筹备好，从如下地址进行下载： 下载地址：https://dev.mysql.com/doc/ind... 《高性能Mysql》的SQL 案例也是应用官网的example work-bench work-bench是官网开发的数据库关系图的可视化工具，应用官网案例的具体关系图展现成果如下，通过这些图能够看到Sakila-db之间的大抵关系： work-bench也是开源免费软件，下载地址如下： https://dev.mysql.com/downloa... 装置workbench和下载sakila-db的形式这里不做记录，在运行的时候须要留神先建设一个数据库运行sheme文件，而后执行data的sql文件，最终在navicat中查看数据： 注释局部索引类型 首先是索引的特点以及作用： 索引的目标是为了晋升数据的效率。对于ORM框架来说索引的应用至关重要，然而ORM的优化往往难以顾及所有业务状况，后续被逐步废除。不同的索引类型实用于不同的场景。索引关键在于缩小数据须要扫描的量，同时防止服务器外部对内容排序和长期表（因为长期表会索引生效），随机IO转程序IO等特点 上面介绍Mysql相干的索引类型： 哈希索引：哈希索引适宜全值匹配和准确查找，查问的速度十分快 在MySQL中只有memory存储引擎显式反对此索引，memory还反对非惟一哈希索引的，是哈希索引设计外面比拟非凡的。空间索引：空间索引是myisam表反对，次要用作天文数据存储，这里蕴含一个叫做GIS的玩意，然而GIS在Postgre中应用比MySQL要杰出很多，所以mysql中空间索引是无关紧要的货色。全文索引：全文索引也是myisam独有反对的一种索引类型。适宜应用的场景为全值匹配的场景和关键字查问，对于大文本的关键字匹配能够无效解决。聚簇索引：聚簇索引是innodb存储引擎的默认存储引擎。前缀压缩索引：留神这个索引针对的是myisam存储引擎，目标是为了让索引放入内存中排序，，前缀压缩的办法是首先保留索引块的第一个值，而后在保留第二个值，存储第二个值相似（长度,索引值）的模式寄存前缀索引。其余索引类型注意事项： Archive 在5.1之后才反对单列自增索引。 MyISAM 反对压缩之后的前缀索引，使得数据结构占用更小。 哈希索引 在Mysql中惟一显式实现哈希索引的存储引擎为Memory，Memory是存在非惟一哈希索引，同时BTree也反对“自适应哈希索引的形式“兼容哈希索引。 上面是哈希索引特点： 键存储的是索引哈希值，留神不是索引值自身，而值存储的是指向行的指针留神此哈希索引无奈防止行扫描，然而在内存中指针十分快通常能够忽略不计留神只有哈希值依照程序排序，然而行指针不是依照程序排序哈希不反对：局部索引笼罩，只反对全索引笼罩，因为应用全副的索引列计算哈希值哈希索引反对等值匹配操作不反对范畴查问，比方等于，in子查问，不全等。如果呈现哈希抵触，哈希索引将进化为链表程序查问，同时保护索引的开销也会变大聚簇索引 聚簇示意数据行的值紧凑存储在一起。而innodb聚簇的值就是主键的值，所以通常应用都是主键上的索引，针对主键索引的抉择非常重要。因为本局部着重索引优化，聚簇索引这里就不再讲述了。 MyISam和Innodb的主键索引区别是MyISam的索引很简略，因为数据行只蕴含行号，所以索引间接存储列值和行号，数据独自寄存另一处，相似于一个惟一非空索引，索引和数据不在一处，MyISam的索引设计比InnoDB简略很多，这和MyIsam不须要反对事务也有间接关系，而innodb将索引和行数据放入一个数据结构，将列进行紧凑的存储。 聚簇索引有上面长处 紧凑存储数据行，所以能够只扫描大量磁盘就能够获取到数据数据拜访的速度十分快，索引和数据放在同一颗BTree中，比非聚簇索引查问快很多笼罩索引能够间接缩小回表当然索引也有上面的毛病： 对于非IO密集型利用，聚簇索引的优化无意义。插入速度依赖于插入程序，然而如果不是自增插入则须要optimize table从新组织表。更新代价十分高，因为BTree要保障程序排序须要移动数据页地位和指针。主键数据插入过满数据页存在页决裂问题，行溢出会导致存储压力加大。聚簇索引导致全表扫描变慢，页决裂导致数据问题等。二级索引须要回表查问聚簇索引能力查问数据。二级索引因为须要存储主键开销会更大，至多在InnoDb中保护一个二级索引的开销是挺大的。压缩索引 压缩索引的特点是应用更少的空间寄存尽可能多的内容，然而这样的解决形式仅仅实用于IO密集型的零碎，压缩前缀存储模式最大的缺点是无奈应用二分法进行查找，同时如果应用的倒序索引的形式比方order by desc 的形式可能会因为压缩索引的问题存在卡顿的状况。 Bree索引的特点 叶子结点存在逻辑页和索引页两种，通常非最底层叶子结点都是索引页，最底层索引页由链表串联。Btree索引会依据建表程序对于索引值进行排序，索引建表时候倡议将常常查问的字段往前挪。Btree索引适宜的查问类型：前缀查问，范畴查问，键值查问（哈希索引）。自适应哈希索引 当innodb发现某些索引列和值应用频繁的时候，BTree会在此基础上主动创立哈希索引辅助优化，然而这个行为是不受内部管制的，齐全是外部的优化行为，如果不须要能够思考敞开。 Btree查问类型 针对Innodb的Btree索引，有上面几种常见的查问形式： 全值匹配：等值匹配的形式，全值匹配适宜哈希索引进行查问最左匹配准则：二级索引的查问条件放在where最右边前缀匹配：只应用索引的第一列，并且like ‘xxx%’范畴匹配：范畴匹配索引列到另一列之间的值范畴查问和准确匹配联合，一个全值匹配，一个范畴匹配笼罩索引查问：笼罩索引也是一种查问形式，索引策略 上面是对于建设索引的一些常见策略： 第一件事件须要思考的是预测那些数据为热点数据或者热点列，依照《高性能Mysql》介绍，对于热点列来说有时候要违反最大选择性的准则，通过建设时常搜寻的索引作为最左前缀的默认的设置。同时优化查问须要思考所有的列，如果一个查问的优化会毁坏另一个查问，那么就须要优化索引的构造。第二件事件是思考where的条件组合，通过组合多种where条件，须要思考的是尽可能让查问重用索引而不是大规模的建设新索引。防止多个范畴进行扫描，一方面是范畴查问会导致，然而对于多个等值的条件查问，最好的方法是尽量管制搜寻范畴。 对于索引的策略咱们还须要理解上面的细节 ...

1.多张表查问的索引剖析2.在什么状况下适宜/不适宜建设索引3.如何防止索引生效4.查问优化 1.多张表查问的索引剖析 假如咱们当初有三张表： CREATE TABLE `t_student` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '学生名字', `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='学生表';CREATE TABLE `t_class` ( `id` int NOT NULL AUTO_INCREMENT, `class_name` varchar(255) COLLATE utf8_bin DEFAULT NULL, `create_time` datetime DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='课程表';CREATE TABLE `t_score` ( `id` int NOT NULL AUTO_INCREMENT, `student_id` int DEFAULT NULL, `class_id` int DEFAULT NULL, `score` int DEFAULT NULL COMMENT '得分', `create_time` datetime DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='分数表';假如咱们要对这三张表进行联查，如何能力建设优良的索引？ ...

原文转载自「刘悦的技术博客」https://v3u.cn/a_id_206 举凡后端面试，面试官不言数据库则已，言则必称SQL优化，说起SQL优化，网络上各种“指南”和“圣经”难以枚举，不一而足，好像SQL优化未然是妇孺皆知的实践常识，而后依据少数无知（Pluralistic ignorance）实践，人们印象里感觉少数人会怎么想怎么做，但这种印象往往是不精确的。那SQL优化到底应该怎么做？本次让咱们褪去SQL富丽的躯壳，以最通俗，最粗鄙，最下里巴人的形式解说一下SQL优化的前因后果，前世今生。 SQL优化背景首先要明确一点，SQL优化不是为了优化而优化，就像冬天要穿羽绒服，不是因为有羽绒服或者羽绒服自身而穿，是因为天儿太冷了！那SQL优化的起因是什么？是因为SQL语句太慢了！从狭义上讲，SQL语句蕴含增删改查，但个别的业务场景下，SQL的读写比例应该是一比十左右，而且写操作很少呈现性能问题，即便呈现，大多数也是慢查问阻塞导致。生产环境中遇到最多的，也是最容易出问题的，还是一些简单的查问操作，所以查问语句的优化显然是第一要务。 那咱们怎么晓得那条SQL慢？开启慢查问日志(slow\_query\_log) 将 slow\_query\_log 全局变量设置为“ON”状态 mysql> set global slow_query_log='ON';设置慢查问日志寄存的地位 mysql> set global slow_query_log_file='c:/log/slow.log';查问速度大于1秒就写日志： mysql> set global long_query_time=1;当然了，这并不是标准化流程，如果是实时业务，500ms的查问兴许也算慢查问，所以个别须要依据业务来设置慢查问工夫的阈值。 当然了，本着“防微杜渐”的准则，在慢查问呈现之前，咱们齐全就能够将其扼杀在摇篮中，那就是写出一条sql之后，应用查问打算(explain)，来理论检查一下查问性能，对于explain命令，在返回的表格中真正有决定意义的是rows字段，大部分rows值小的语句执行并不需要优化，所以基本上，优化sql，实际上是在优化rows，值得注意的是，在测试sql语句的效率时候，最好不要开启查问缓存，否则会影响你对这条sql查问工夫的正确判断： SELECT SQL_NO_CACHESQL优化伎俩(索引)除了防止诸如select *、like、order by rand()这种陈词滥调的低效sql写法，更多的，咱们依附索引来优化SQL，在应用索引之前，须要弄清楚到底索引为什么能帮咱们进步查问效率，也就是索引的原理，这个时候你的脑子里必定浮现了图书的目录、火车站的车次表，是的，网上都是这么说的，事实上是，如果没坐过火车，没有应用过目录，那这样的生存索引样例就并不直观，作为下里巴人，咱们肯定吃过包子： 毫无疑问，当咱们在吃包子的时候，其实是在吃馅儿，如果没有馅儿，包子就不是包子，而是馒头。那么问题来了，我怎么保障一口就能吃到馅儿呢？这里的馅儿，能够了解为数据，海量数据的包子，可能直径几公里，那么我怎么能疾速失去我想要的数据(馅儿)？有生存教训的吃货肯定会通知你，找油皮儿，因为馅儿外面有油脂，更贴近包子皮儿的中央，或者包子皮儿簙的中央，都会被油脂渗透，也就造成了油皮儿，所以如果照着油皮儿下嘴，至多要比咬其余中央更容易吃到馅儿，那么，索引就是油皮儿，有索引的数据就是有油皮儿的大包子，没有索引的数据就是没有油皮儿的大包子，如此一来，索引的原理不言而喻，通过放大数据范畴（油皮儿）来筛选出最终想要的后果（馅儿），同时把随机的查问（轻易咬）变成程序的查问（先找油皮儿），也就是咱们总是通过同一种查问形式来锁定数据。 SQL索引的数据结构B+tree晓得了背景，理解了原理，当初咱们须要某种容器（数据结构）来帮咱们实现包子的油皮儿，这种容器能够帮助咱们每次查找数据时把咬包子次数管制在一个很小的数量级，最好是常数数量级。于是B+tree闪亮退场。 那么，假如数据库中有1-7条数据，一次查问，B+tree到底怎么帮咱们疾速检索到数据呢？ SELECT SQL_NO_CACHE id from article where id = 4 如图所示，如果要查找数据4，那么首先会把B+tree的根节点加载到内存，此时产生一次咬包子(IO读操作)，在内存中用二分查找确定4在3和5之间，通过根节点所存储的指针加载叶子节点(3,4)到内存中，产生第二次咬包子，完结查问，总计两次。如果不应用索引，咱们须要咬四口包子能力把4咬进去。而在生产环境中，2阶的B+树能够示意上百万的数据，如果上百万的数据查找只须要两次IO读操作，性能进步将是微小的，如果没有索引，每个数据项都要产生一次IO读取，那么总共须要百万次的IO，显然老本是微小的。 同时，咱们晓得IO次数读写取决于B+树的层级，也就是高度h，假如以后数据表的数据为N，每个存储容器的数据项的数量是m，则有h=㏒(m+1)N，当数据量N肯定的状况下，m越大，h越小；而m = 存储容器的大小 / 数据项的大小，存储容器的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比方int占4字节，要比bigint8字节少一半。这也是为什么B+树要求把实在的数据放到叶子节点而不是非叶子节点，一旦放到非叶子节点，存储容器的数据项会大幅度降落，导致树的层数增高。当数据项等于1时将会进化成线性表，又变成了程序查找，所以这也是为啥索引用B+tree，而不必B-tree，根本原因就是叶子节点存储数据高度就会减小，而高度减小能力帮咱们更快的吃到馅儿。 说白了就是B-tree也能实现索引，也能让咱们更快的拜访数据，然而B-tree每个节点上都带着一点儿馅儿，而这个馅儿占据了原本油皮的空间，所以为了扩容，只能减少B-tree的高度进行扩容，随着馅儿越来越多，导致B-tree的高度也越来越高，高度越高，咱们咬包子的次数也越来越频繁，读写效率则越来越慢。 当B+树的数据项是复合的数据结构，即所谓的联结索引，比方(name,age,sex)的时候，B+树是依照从左到右的程序来建设搜寻树的，比方当(小明,20,男)这样的数据来检索的时候，B+树会优先比拟name来确定下一步的所搜方向，如果name雷同再顺次比拟age和sex，最初失去检索的数据；但当(20,男)这样的没有name的数据来的时候，B+树就不晓得下一步该查哪个节点，因为建设搜寻树的时候name就是第一个比拟因子，必须要先依据name来搜寻能力晓得下一步去哪里查问。比方当(小明,F)这样的数据来检索时，B+树能够用name来指定搜寻方向，但下一个字段age的缺失，所以只能把名字等于小明的数据都找到，而后再匹配性别是男的数据了， 这个是十分重要的性质，即索引的最左匹配个性，对于最左准则能够参照这篇文章：mysql联结索引的最左前缀准则以及b+tree。 最根本的索引建设准则无外乎以下几点： 1.最左前缀匹配准则，十分重要的准则，mysql会始终向右匹配直到遇到范畴查问(>、<、between、like)就进行匹配，比方a = 1 and b = 2 and c > 3 and d = 4 如果建设(a,b,c,d)程序的索引，d是用不到索引的，如果建设(a,b,d,c)的索引则都能够用到，a,b,d的程序能够任意调整。 2.=和in能够乱序，比方a = 1 and b = 2 and c = 3 建设(a,b,c)索引能够任意程序，mysql的查问优化器会帮你优化成索引能够辨认的模式。 ...

索引的问题，曾经跟大家聊了两篇文章了～明天再聊一个索引下推问题，也是十分有意思！ 索引下推是从 MySQL5.6 开始引入一个个性，英文是 index condition pushdown，个别简称为 ICP，索引下推通过缩小回表的次数，来进步数据库的查问效率。 有的小伙伴可能也看过一些对于 ICP 的概念，然而我感觉，概念比较简单，说一下很容易懂，然而在理论利用中，各种各样的状况十分多。所以接下来的内容我想通过几个具体的查问剖析来和大家分享 ICP 到底是怎么一回事。 1. 索引下推为了给大家演示索引下推，我用 docker 装置了两个 MySQL，一个是 MySQL5.5.62，另一个是 5.7.26，因为索引下推是 MySQL5.6 中开始引入的新个性，所以这两个版本就能够给大家演示出索引下推的特点（不懂 docker 的小伙伴能够在公众号后盾回复 docker，有松哥写的入门教程）。 1.1 筹备工作首先我有如下一张表： CREATE TABLE `user2` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `age` int(11) DEFAULT NULL, `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`), KEY `username` (`username`(191),`age`)) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;我在 MySQL5.5 和 MySQL5.7 中别离执行如上 SQL，确保两个 MySQL 中都有这样一张表。这张表中有一个由 username 和 age 组成的复合索引，索引名字就叫 username，在本文接下来的内容中，我说 username 索引就是指该复合索引。 ...

官网文档：《云原生分布式数据库 PolarDB-X》 -- 物理执行打算 EXPLAIN EXECUTE select * from order_goods limit 100 -- 表构造 show create table lp_order_goods -- 全局二级索引CREATE GLOBAL INDEX `g_i_order_id` ON order_goods (`order_id`) dbpartition by hash(`order_id`);

WGCLOUD是一款轻量实用的运维监控工具，以笨重、性能优良、易扩大、指标全、集成度高、部署简略、易上手等特点 下载：www.wgstart.com 装置步骤网站有比拟具体的阐明，此处不在形容，咱们间接进入正题，如何应用wgcloud监测MySQL运行时候的慢sql数量 **1.装置好后，咱们点击左侧菜单数据监控->数据源治理，咱们先增加数据源，点击增加** ** 2.数据源增加胜利后，点击左侧菜单【数据表治理】，增加数据表，点击增加按钮** show global status like 'slow_queries' 在数据库客户端执行后果 这是查问慢sql的语句，将返回的列名Value输出到【sql执行后果的取值列名】即可 数据表监控，只能监测返回后果为一行，获取一列为数字的列值，如果返回多行零碎默认取第一行 **3.增加实现后，零碎默认每1小时扫描一次数据表，会逐步造成趋势图，如下，提醒：能够在server/config/application.yml批改扫描时间**

设计规范以下所有标准会依照【高危】、【强制】、【倡议】三个级别进行标注，恪守优先级从高到低 对于不满足【高危】和【强制】两个级别的设计，DBA有权力强制打回要求批改 库名1.【强制】库的名称必须管制在32个字符以内，相干模块的表名与表名之间尽量体现join的关系，如user表和user_login表 2.【强制】库的名称格局：业务零碎名称_子系统名，同一模块应用的库名尽量应用对立前缀 3.【强制】个别分库名称命名格局是库通配名_编号，编号从0开始递增，比方wenda_001以工夫进行分库的名称格局是“库通配名_工夫” 4.【强制】创立数据库时必须显式指定字符集，并且字符集只能是utf8或者utf8mb4。创立数据库SQL举例：create database db1 default character set utf8; 表构造1.【强制】表必须有主键，且设置id为自增主键 2.【强制】表禁止应用外键，如果要保障残缺下，应由程序端实现，外键使表之间互相耦合，影响update、delete等性能，有可能造成死锁，高并发环境下容易导致数据库性能瓶颈 3.【强制】表和列的名称必须管制在32个字符以内，表名只能应用字母、数字和下划线，一律小写。如表名过长能够采纳缩写等形式 4.【强制】创立表时必须显式指定字符集为utf8或utf8mb4 5.【强制】创立表时必须显式指定表存储引擎类型，如无非凡需要，一律为InnoDB。当须要应用除InnoDB/MyISAM/Memory以外的存储引擎时，必须通过DBA审核能力在生产环境中应用。因为Innodb表反对事务、行锁、宕机复原、MVCC等关系型数据库重要个性，为业界应用最多的MySQL存储引擎。而这是其余大多数存储引擎不具备的，因而首推InnoDB 6.【强制】建表必须有comment，表级别和字段级别都要有comment 7.【倡议】建表时对于主键：(1)强制要求主键为id，类型为int或bigint（为了当前延展性，这里要求新建表对立为bigint），且为auto_increment(2)标识表里每一行主体的字段不要设为主键，倡议设为其余字段如user_id，order_id等，并建设unique key索引。因为如果设为主键且主键值为随机插入，则会导致innodb外部page决裂和大量随机I/O，性能降落 8.【倡议】外围表（如用户表，金钱相干的表）必须有行数据的创立工夫字段create_time和最初更新工夫字段update_time，便于查问题 9.【倡议】表中所有字段必须都是NOT NULL default 默认值 属性，业务能够依据须要定义DEFAULT值。因为应用NULL值会存在每一行都会占用额定存储空间、数据迁徙容易出错、聚合函数计算结果偏差以及索引生效等问题 10.【倡议】倡议对表里的blob、text等大字段，垂直拆分到其余表里，仅在须要读这些对象的时候才去select 11.【倡议】反范式设计：把常常须要join查问的字段，在其余表里冗余一份。如user_name属性在user_account，user_login_log等表里冗余一份，缩小join查问 12.【强制】两头表用于保留两头后果集，名称必须以tmp_结尾。备份表用于备份或抓取源表快照，名称必须以bak_结尾。两头表和备份表定期清理 13.【强制】对于线上执行DDL变更，必须通过DBA审核，并由DBA在业务低峰期执行 列数据类型优化1.【倡议】表中的自增列（auto_increment属性），举荐应用bigint类型。因为无符号int存储范畴为-2147483648~2147483647（大概21亿左右），溢出后会导致报错 2.【倡议】业务中选择性很少的状态status、类型type等字段举荐应用tinytint或者smallint类型节俭存储空 3.【倡议】业务中IP地址字段举荐应用int类型，不举荐用char(15)。因为int只占4字节，能够用如下函数互相转换，而char(15)占用至多15字节。一旦表数据行数到了1亿，那么要多用1.1G存储空间。 SQL：select inet_aton('192.168.2.12'); select inet_ntoa(3232236044); PHP: ip2long(‘192.168.2.12’); long2ip(3530427185); 4.【倡议】不举荐应用enum，set。 因为它们节约空间，且枚举值写死了，变更不不便。举荐应用tinyint或smallint 5.【倡议】不举荐应用blob，text等类型。它们都比拟节约硬盘和内存空间。在加载表数据时，会读取大字段到内存里从而节约内存空间，影响零碎性能。倡议和PM、RD沟通，是否真的须要这么大字段 6.【倡议】存储金钱的字段，倡议用int，程序端乘以100和除以100进行存取。或者用decimal类型，而不要用double 7.【倡议】文本数据尽量用varchar存储。因为varchar是变长存储，比char更省空间。MySQL server层规定一行所有文本最多存65535字节 8.【倡议】工夫类型尽量选取datetime。而timestamp尽管占用空间少，然而有工夫范畴为1970-01-01 00:00:01到2038-01-01 00:00:00的问题 索引设计1.【强制】InnoDB表必须主键为id int/bigint auto_increment,且主键值禁止被更新 2.【倡议】惟一键以“uk_”或“uq_”结尾，一般索引以“idx_”结尾，一律应用小写格局，以字段的名称或缩写作为后缀 3.【强制】InnoDB和MyISAM存储引擎表，索引类型必须为BTREE；MEMORY表能够依据须要抉择HASH或者BTREE类型索引 4.【强制】单个索引中每个索引记录的长度不能超过64KB 5.【倡议】单个表上的索引个数不能超过5个 6.【倡议】在建设索引时，多思考建设联结索引，并把区分度最高的字段放在最后面。如列userid的区分度可由select count(distinct userid)计算出来 7.【倡议】在多表join的SQL里，保障被驱动表的连贯列上有索引，这样join执行效率最高 8.【倡议】建表或加索引时，保障表里相互不存在冗余索引。对于MySQL来说，如果表里曾经存在key(a,b)，则key(a)为冗余索引，须要删除 分库分表、分区表1.【强制】分区表的分区字段（partition-key）必须有索引，或者是组合索引的首列 2.【强制】单个分区表中的分区（包含子分区）个数不能超过1024 3.【强制】上线前RD或者DBA必须指定分区表的创立、清理策略 4.【强制】拜访分区表的SQL必须蕴含分区键 5.【倡议】单个分区文件不超过2G，总大小不超过50G。倡议总分区数不超过20个 6.【强制】对于分区表执行alter table操作，必须在业务低峰期执行 7.【强制】采纳分库策略的，库的数量不能超过1024 ...

转自MySQL优化之剖析SQL执行过程(explain、show profile、trace)

以前备份binlog时，都是先在本地进行备份压缩，而后发送到近程服务器中。然而这其中还是有肯定危险的，因为日志的备份都是周期性的，如果在某个周期中，服务器宕机了，硬盘损坏了，就可能导致这段时间的binlog就失落了。 而且，以前用脚本对近程服务器进行备份的形式，有个毛病：无奈对MySQL服务器以后正在写的二进制日志文件进行备份。所以，只能等到MySQL服务器全副写完能力进行备份。而写完一个binlog的工夫并不固定，这就导致备份周期的不确定。 从MySQL5.6开始，mysqlbinlog反对将近程服务器上的binlog实时复制到本地服务器上。 mysqlbinlog的实时二进制复制性能并非简略的将近程服务器的日志复制过去，它是通过MySQL 5.6颁布的Replication API实时获取二进制事件。站长博客实质上，就相当于MySQL的从服务器。与一般服务器相似，主服务器产生事件后，个别都会在0.5~1秒内进行备份。 备份命令 复制代码 代码如下:mysqlbinlog --read-from-remote-server --raw --host=192.168.244.145 --port=3306 --user=repl --password=repl --stop-never mysql-bin.000001 解释如下： --read-from-remote-server：用于备份近程服务器的binlog。如果不指定该选项，则会查找本地的binlog。 --raw：binlog日志会以二进制格局存储在磁盘中，如果不指定该选项，则会以文本模式保留。 --user：复制的MySQL用户，只须要授予REPLICATION SLAVE权限。 --stop-never：mysqlbinlog能够只从近程服务器获取指定的几个binlog，也可将一直生成的binlog保留到本地。指定此选项，代表只有近程服务器不敞开或者连贯未断开，mysqlbinlog就会一直的复制近程服务器上的binlog。 mysql-bin.000001：代表从哪个binlog开始复制。 除了以上选项外，还有以下几个选项须要留神： --stop-never-slave-server-id：在备份近程服务器的binlog时，mysqlbinlog实质上就相当于一个从服务器，该选项就是用来指定从服务器的server-id的。默认为-1。 --to-last-log：代表mysqlbinlog不仅可能获取指定的binlog，还能获取其后生成的binlog，获取完了，才终止。如果指定了--stop-never选项则会隐式关上--to-last-log选项。 --result-file：用于设置近程服务器的binlog，保留到本地的前缀。譬如对于mysql-bin.000001，如果指定--result-file=/test/backup-，则保留到本地后的文件名为/test/backup-mysql-bin.000001。留神：如果将--result-file设置为目录，则肯定要带上目录分隔符“/”。譬如--result-file=/test/，而不是--result-file=/test，不然保留到本地的文件名为/testmysql-bin.000001。 有余： 这个形式有个问题，对于惯例的主从复制来说，如果主从间接的连贯断开了，则从会主动再次连贯，而对于mysqlbinlog，如果断开了，并不会主动连贯。 解决方案： 可通过脚本来补救上述有余。 !/bin/shBACKUP_BIN=/usr/bin/mysqlbinlog LOCAL_BACKUP_DIR=/backup/binlog/ BACKUP_LOG=/backup/binlog/backuplog REMOTE_HOST=192.168.244.145 REMOTE_PORT=3306 REMOTE_USER=repl REMOTE_PASS=repl FIRST_BINLOG=mysql-bin.000001 time to wait before reconnecting after failureSLEEP_SECONDS=10 create local_backup_dir if necessarymkdir -p ${LOCAL_BACKUP_DIR} cd ${LOCAL_BACKUP_DIR} 运行while循环，连贯断开后期待指定工夫，从新连贯while : do if [ ls -A "${LOCAL_BACKUP_DIR}" |wc -l -eq 0 ];then LAST_FILE=${FIRST_BINLOG} else ...

什么是mysql?如果你的答复是关系型数据库，那就会显得有些肤浅。咱们平时工作中必定会用到mysql,然而谈到mysql,就不能只说增删改查。接下来咱们从另一个角度认识一下mysql(其实不仅仅是mysql,对于任何一个产品、服务，咱们都应该有一个抽象化的架构，而不能局限于这个产品的某一个区域)mysql的逻辑分层 连贯层：提供客户端的连接功能和权限认证,服务层： 提供用户应用的接口（curd,主从配置,数据备份等）sql优化器（mysql query optimizer）# 联结索引 a b c select * from table1 where a=xxx and c=xxx and b=xxx；#通过优化器优化后能够应用索引，引擎层 :提供存储数据的形式（innodb myisam archive ,memory,csv,federated ），Mysql在V5.1之前默认存储引擎是MyISAM；在此之后默认存储引擎是InnoDB，myisam 和innodb的区别：https://segmentfault.com/a/11... mysql> show engines -> ;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine | Support | Comment | Transactions | XA | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES || MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO || MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO || BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO || MyISAM | YES | MyISAM storage engine | NO | NO | NO || CSV | YES | CSV storage engine | NO | NO | NO || ARCHIVE | YES | Archive storage engine | NO | NO | NO || PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO || FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+mysql> show variables like '%storage_engine%';+----------------------------------+--------+| Variable_name | Value |+----------------------------------+--------+| default_storage_engine | InnoDB || default_tmp_storage_engine | InnoDB || disabled_storage_engines | || internal_tmp_disk_storage_engine | InnoDB |+----------------------------------+--------+TODO 具体存储引擎和相干应用场景待补充 ...

咱们都晓得mysql中的数据最终都会存到磁盘上,而咱们又晓得磁盘的读写速度和cpu不是在一个数量级上,所以咱们猜想mysql存储引擎里必然有缓冲这一概念,本节咱们就好好掰扯掰扯myslq中的缓存.先到官网看看,存储innodb存储引擎的构造从上图咱们能够看到,INNODB架构,大抵分为内存局部构造和磁盘局部构造, 而内存局部又有BufferPool,ChangeBuffer,Adaptive Hash Index,LogBuffer等.咱们将依照本图内存局部构造一点一点掰扯每一个缓存局部.3:首先来看BufferPool说白了就是内存里一个最次要的缓存数据,并且在专用服务器上(这里能够了解mysql的生产环境) 80%的物理内存都调配给了buffer pool。 可见buffer的重要性。那数据在buffer里是怎么存储的呢？ 在看官网给的架构图 先不细剖析这个构造,大抵看了下就是应用了LRU(least recently used 这个大家都很相熟吧.不相熟自行补充)算法. 并且把整个列表分了new和old两局部. 还有一点就是列表里每一项到底缓存数据的维度是什么？所以上面将分3个局部介绍 1:buffer pool 缓存的数据维度是什么? 2:为什么LRU算法还有new和old局部,为什么会这样设计? 3:实战看下buffer pool相干的配置 1:buffer pool 缓存的数据维度是什么? When room is needed to add a new page to the buffer pool, the least recently used page is evicted and a new page is added to the middle of the list. 从官网这句话,咱们能够得悉每一项都是一页数据 . 这个前期写mysql索引文章的时候,会详细分析为什么会是页数据为单位. 2:为什么LRU算法还有new和old局部,为什么会这样设计? 其实大家想想只有LRU算法的毛病就晓得了. 看下图 就是随着新的数据页进来,列表中的数据会被大量置换进来. 没有达到缓存的目标. 其实官网也介绍了哪些状况下会呈现这种状况[见下图]. 说白了,全表扫描,什么mysqldump操作,select语句没有where条件，都会把大量数据放进buffer bool,即时这些数据并不是热点数据, 但同时还会把热点数据给排斥进来. 那减少了new和old局部,当呈现下面操作,又是怎么爱护热点数据呢？ 可从上图看出,新读入的数据都放在old列表,并且不会替换掉new列表中的热点数据. 那问题来了,什么时候old列表中数据会进入new列表呢? 其实是进入buffer pool的数据,能被真正读取到, 并停留肯定工夫窗口,才会进入new列表 [有点像jvm内存分代,多大年龄能够进入老年代]. ...

应用explain的12个字段阐明 0. 前情提要: 用到的几个表阐明:0.1. goods表和goods2两个表构造和数据雷同(复制的表)-test库mysql> show create table goods;CREATE TABLE `goods` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `price` double DEFAULT NULL, UNIQUE KEY `id` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=150001 DEFAULT CHARSET=utf8mysql> show create table goods2;CREATE TABLE `goods2` ( `id` bigint(20) unsigned NOT NULL DEFAULT '0', `name` varchar(10) CHARACTER SET utf8 DEFAULT NULL, `price` double DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4mysql> select count(*) from goods;+----------+| count(*) |+----------+| 150000 |+----------+1 row in set (0.02 sec)mysql> select count(*) from goods2;+----------+| count(*) |+----------+| 150000 |+----------+1 row in set (0.05 sec)0.2. t2表是goods表中的前10条-test库mysql> select * from t2;+----+---------+--------+| id | name | price |+----+---------+--------+| 1 | 商品1 | 200.17 || 2 | 商品2 | 200.87 || 3 | 商品3 | 200.81 || 4 | 商品4 | 200.43 || 5 | 商品5 | 200.73 || 6 | 商品6 | 200.36 || 7 | 商品7 | 200.61 || 8 | 商品8 | 200.98 || 9 | 商品9 | 200.06 || 10 | 商品0 | 200.38 |+----+---------+--------+10 rows in set (0.00 sec)mysql> show create table t2;CREATE TABLE `t2` ( `id` bigint(20) unsigned NOT NULL DEFAULT '0', `name` varchar(10) CHARACTER SET utf8 DEFAULT NULL, `price` double DEFAULT NULL, UNIQUE KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb40.3. sakila库的film表:mysql> show create table film;CREATE TABLE `film` ( `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(128) NOT NULL, `description` text, `release_year` year(4) DEFAULT NULL, `language_id` tinyint(3) unsigned NOT NULL, `original_language_id` tinyint(3) unsigned DEFAULT NULL, `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3', `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99', `length` smallint(5) unsigned DEFAULT NULL, `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99', `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G', `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`film_id`), KEY `idx_title` (`title`), KEY `idx_fk_language_id` (`language_id`), KEY `idx_fk_original_language_id` (`original_language_id`), CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE, CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4;1 row in set (0.00 sec)mysql> select count(*) from film;+----------+| count(*) |+----------+| 1000 |+----------+1 row in set (0.00 sec)0.4. sakila库的film_category表mysql> show create table film_actor;CREATE TABLE `film_actor` ( `actor_id` smallint(5) unsigned NOT NULL, `film_id` smallint(5) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`,`film_id`), KEY `idx_fk_film_id` (`film_id`), CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE, CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;mysql> select count(*)from film_actor;+----------+| count(*) |+----------+| 5462 |+----------+1 row in set (0.00 sec)0.5. sakila库的film_category表mysql> show create table film_category;CREATE TABLE `film_category` ( `film_id` smallint(5) unsigned NOT NULL, `category_id` tinyint(3) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`film_id`,`category_id`), KEY `fk_film_category_category` (`category_id`), CONSTRAINT `fk_film_category_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`) ON UPDATE CASCADE, CONSTRAINT `fk_film_category_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 mysql> select count(*) from film_category;+----------+| count(*) |+----------+| 1000 |+----------+1 row in set (0.00 sec)0.6. sakila库的actor表mysql> show create table actor;CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`)) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 mysql> select count(*) from actor;+----------+| count(*) |+----------+| 200 |+----------+1 row in set (0.00 sec)explain一共有12个字段, 上面具体介绍: ...

1. not in 和 <> 的优化-->left joinSELECT customer_id, first_name, last_name, email FROM customerWHERE customer_id NOT IN (SELECT customer_id FROM payment);--优化后SELECT a.customer_id, a.first_name, a.last_name, a.emailFROM customer aLEFT JOIN payment b ON a.customer_id=b.customer_idWHERE b.customer_id IS NULL;2. 大表的数据批改要分批解决比方1000万行记录在表中要删除, 或者更新100万行记录; 优化计划: 一次只删除/删除5000条, 而后sleep几秒(暂停几秒)-->给主从同步缓冲工夫; 3. 汇总查问的优化 select count(*)-> 减少汇总表减少一个汇总表, 每天更新一次汇总值; 而后每次查问时, 汇总表+实时表(time>date(now()))即可; SELECT COUNT(*) FROM product_comment WHERE product_id=999;--减少汇总表 product_comment_cntCREATE TABLE product_comment_cnt(product_id INT, cnt INT);--查问时:SELECT SUM(cnt) FROM( SELECT cnt FROM product_comment_cnt WHERE product_id = 999 UNION ALL SELECT count(*) FROM product_comment WHERE product_id = 999 AND timestr>DATE(now()));--mysql> select DATE(now());+-------------+| DATE(now()) |+-------------+| 2020-09-12 |+-------------+1 row in set (0.01 sec)

1. pt-query-digest简介第三方工具. perl脚本; 能够剖析 binlog, general log, slowlog; 也能够通过 show processlist 或者 通过 tcpdump 抓取MySQL协定数据来进行剖析. 能够把剖析后果输入到文件中. 剖析过程是 先对查问语句的条件 参数化, 而后对参数化的查问进行分组统计, 统计出各个查问的执行工夫, 次数, 占比等, 能够借助剖析后果, 找出问题, 进而进行优化. 2. pt-query-digest装置2.1 装置perl模块须要装置perl模块 yum install -y perl-CPAN per-Time_HiRes2.2 percona上下载 percona-toolkit工具https://www.percona.com/downl... wget https://www.percona.com/downl... 下载rpm包, 下载实现后: yum localinstall -y percona-toolkit-3.2.1-1.el7.x86_64.rpm装置即可 2.3 装置遇到的问题: libmysqlclient.so我装置中遇到: 谬误：软件包：perl-DBD-MySQL-4.023-6.el7.x86_64 (base) 须要：libmysqlclient.so.18(libmysqlclient_18)(64bit)谬误：软件包：perl-DBD-MySQL-4.023-6.el7.x86_64 (base) 须要：libmysqlclient.so.18()(64bit)您能够尝试增加 --skip-broken 选项来解决该问题您能够尝试执行：rpm -Va --nofiles --nodigest 解决形式: 找到mysql装置的tar包, 装置这两个: [root@niewj download]# rpm -ivh mysql-community-libs-5.7.31-1.el7.x86_64.rpm [root@niewj download]# rpm -ivh mysql-community-libs-compat-5.7.31-1.el7.x86_64.rpm [root@niewj download]#yum localinstall -y --skip-broken percona-toolkit-3.2.1-1.el7.x86_64.rpm最初装置 percona-toolkit就能够了! ...

1. mysqldumpslow简介mysql装置好后自带的, perl工具. 2. 查看命令用法:mysqldumpslow --help[root@niewj download]# mysqldumpslow --helpUsage: mysqldumpslow [ OPTS... ] [ LOGS... ]Parse and summarize the MySQL slow query log. Options are --verbose verbose --debug debug --help write this text to standard output -v verbose -d debug -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time -r reverse the sort order (largest last instead of first) -t NUM just show the top n queries -a don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN grep: only consider stmts that include this string -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time[root@niewj download]# 3. mysqldumpslow参数之-(1): -v或--verbose打印明细信息 ...

1. 查看慢查问是否开启-- 查看慢查问日志开启否 show variables like 'slow_query_log';slow_query_log OFF2. 开启慢查问记录-- 开启慢查问日志记录 set global slow_query_log=on;1 queries executed, 1 success, 0 errors, 0 warnings查问：set global slow_query_log=on 共 0 行受到影响 执行耗时 : 0.008 sec传送工夫 : 0 sec总耗时 : 0.008 sec 3. 查看慢查问日志存哪-- 查看慢查问日志存在哪里 show variables like '%slow_query_log%';Variable_name Valueslow_query_log ONslow_query_log_file /var/lib/mysql/niewj-slow.log4. 多慢才算慢查问show variables like 'long_query_time';+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+默认是10秒, 试验时能够改成2s: set global long_query_time=2;留神换个session能力查到(须要从新进入) 从新输出用户名明码: mysql> show variables like 'long_query_time';+-----------------+----------+| Variable_name | Value |+-----------------+----------+| long_query_time | 2.000000 |+-----------------+----------+1 row in set (0.00 sec)5. 模仿慢查问看会否进入慢查问日志:select sleep(11);tail -f /var/lib/mysql/niewj-slow.log ...

上文中曾经在主库/从库都装置了数据库,接下来进行挂载原理; 数据库主从配置1.数据库挂载配置1.查看主数据库状态:默认条件下数据库是不能充当主库的,咱们须要自行去开启二进制sqlyog命令:show master status;2.开启数据库二进制文件:批改主库外围配置文件--my.cnf-->vim /etc/my.cnf-->增加服务ID号sever-id=1/数据库日志文件log-bin=mysql-bin/(datedir为生成的二进制文件的地位)3.重启mysql数据库:当批改好my.cnf文件之后,须要重启数据库,查看二进制日志文件是否存在cd /var/lib/mysql/进入mysql目录-->systemctl restart mariadb重启数据库-->ls查看是否有mysql-bin.000001二进制文件4.再次查看数据库状态:5.批改从库二进制文件:vim /etc/my.cnf-->增加服务ID号sever-id=2(此处要与主库辨别)/数据库日志文件log-bin=mysql-bin-->重启数据库systemctl restart mariadb-->查看库状态 2.实现主从库挂载1.挂载剖析:从库挂载主库 主库IP地址/主库端口号/登录用户名/登录的明码/二进制日志文件/读取地位 这些都须要用到.2.挂载实现:SQLYog代码:留神分段选中执行 /*我是从库信息 130*/ SHOW MASTER STATUS; /*实现主从的挂载*/ CHANGE MASTER TO MASTER_HOST="192.168.126.129", MASTER_PORT=3306, MASTER_USER="root", MASTER_PASSWORD="root", MASTER_LOG_FILE="mysql-bin.000001", MASTER_LOG_POS=245/*启动主从服务*/ START SLAVE /*查看主从的状态*/ SHOW SLAVE STATUS /*如果报错 则重新配置*/ STOP SLAVE查看主从状态显示两个YES则示意配置正确3.主从同步测试:批改主库中的数据,查看从库数据是否同步即可 3.Mycat介绍mycat--数据库分库分表中间件(阿里) 4.实现数据库读写拆散机制1.原理:通过代理数据库将写操作交由主库,读操作交由从库,主从库还会进行挂载2.上传mycat服务:将tar.gz压缩包拖至MobaXterm中src目录下-->tar -xvf命令解压3.编辑配置文件-server.xml:用户与代理数据库的链接通过server.xml文件进行配置. IP/PORT/USER/PASSWORD <!--配置默认端口号--><property name="serverPort">8066</property> <!--用户标签--> <user name="root"> <property name="password">root</property> <!--与schema.xml中的配置雷同 留神数据库的大小写--> <property name="schemas">jtdb</property> </user> <user name="user"> <property name="password">user</property> <property name="schemas">jtdb</property> <property name="readOnly">true</property> </user>4.编辑配置文件-schema.xml: <?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd"><mycat:schema xmlns:mycat="http://io.mycat/"> <!--name属性是自定义的 dataNode示意数据库的节点信息 jtdb示意逻辑库--> <schema name="jtdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="jtdb"/> <!--定义节点名称/节点主机/数据名称--> <dataNode name="jtdb" dataHost="localhost1" database="jtdb" /> <!--参数介绍 UTF-8 中文报错--> <!--balance 0示意所有的读操作都会发往writeHost主机 --> <!--1示意所有的读操作发往readHost和闲置的主节点中--> <!--writeType=0 所有的写操作都发往第一个writeHost主机--> <!--writeType=1 所有的写操作随机发往writeHost中--> <!--dbType 示意数据库类型 mysql/oracle--> <!--dbDriver="native" 固定参数 不变--> <!--switchType=-1 示意不主动切换, 主机宕机后不会主动切换从节点--> <!--switchType=1 示意会主动切换(默认值)如果第一个主节点宕机后,Mycat会进行3次心跳检测,如果3次都没有响应,则会主动切换到第二个主节点--> <!--并且会更新/conf/dnindex.properties文件的主节点信息 localhost1=0 示意第一个节点.该文件不要随便批改否则会呈现大问题--> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select 1</heartbeat><!--心跳检测--> <!--配置第一台主机次要进行写库操作,在默认的条件下Mycat次要操作第一台主机在第一台主机中曾经实现了读写拆散.因为默认写操作会发往137的数据库.读的操作默认发往141.如果从节点比较忙,则主节点分担局部压力. --> <writeHost host="hostM1" url="192.168.126.129:3306" user="root" password="root"> <!--读数据库1--> <readHost host="hostS1" url="192.168.126.130:3306" user="root" password="root" /> <!--读数据库2--> <readHost host="hostS2" url="192.168.126.129:3306" user="root" password="root" /> </writeHost> <!--定义第二台主机 因为数据库外部曾经实现了双机热备.--> <!--Mycat实现高可用.当第一个主机137宕机后.mycat会主动收回心跳检测.检测3次.--> <!--如果主机137没有给Mycat响应则判断主机死亡.则回启东第二台主机持续为用户提供服务.--> <!--如果137主机复原之后则处于期待状态.如果141宕机则137再次继续为用户提供服务.--> <!--前提:实现双机热备.--> <!--<writeHost host="hostM2" url="192.168.126.130:3306" user="root" password="root"> <readHost host="hostS1" url="192.168.126.130:3306" user="root" password="root" /> <readHost host="hostS2" url="192.168.126.129:3306" user="root" password="root" /> </writeHost>--> </dataHost></mycat:schema>5.替换/usr/local/src/mycat/conf目录下的server.xml以及schema.xml文件 ...

数据库备份数据库备份是应用数据库必不可少的一环,能够对咱们不小心的操作及时做出补救.数据库备份分为以下两种. 数据库冷备份定义:定期将数据库中的数据实现转储.毛病: 1.数据库冷备份由人工操作,十分的不不便 2.数据库冷备份因为是定期执行,所以可能会导致数据失落. 当初的网络环境异常简单.数据库冷备份是复原数据的最初的无效伎俩. 数据库热备份定义:以实现数据库实时的备份,保证数据尽可能不失落.原理:原理剖析:1.当数据库主库执行更新操作时,会将更新的内容写入到二进制日志文件中. 并且写入二进制文件是一个异步的过程. 2.从库会开启IO线程去读取主库的二进制日志文件,之后写入中继(长期存储)日志中. 3.从库会开启SQL线程去读取中继日志中的信息.之后将数据同步到从库中. 上述的操作是由从库向主库获取数据, 所以从库实践上能够配置无数个(个别2-3台即可); 从库装置数据库为了实现数据库的热备份,咱们就须要筹备一个从库,那咱们就须要先筹备一个新的虚拟机,并在虚拟机中装置数据库 装置虚拟机不再赘述. 装置数据库依照上一篇文章中的步骤装置数据库 注意事项因为要实现数据库的主从的同步,所以必须保障数据库的信息统一. 步骤: 1.将主库的信息应用冷备份的形式导出.2.在数据库的从库中导入对应sql文件即可.

首先找到监控主集的mysql的过程id号 而后在过程治理菜单，点击关上，增加 输出对应的过程 保留实现后，稍等一会就能看到mysql的内存和cpu信息了

笔者实习小白，公司让我优化一下数据库，这几天复习了一下数据库的相干常识并且查阅了一些材料，筹备过几天把高性能MySQL看一下。MYSQL优化的次要形式之一：减少索引一、索引：1.定义： 是一种数据结构。（残缺定义：数据自身之外，数据库还保护着一个满足特定查找算法的数据结构，这些数据结构以某种形式指向数据，这样就能够在这些数据的根底上实现高级查找算法，这种数据结构就是索引） 2.分类： BTREE索引（B+树是一个均衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针互相链接。）在B+树上的惯例检索，从根节点到叶子节点的搜寻效率根本相当，不会呈现大幅稳定，而且基于索引的程序扫描时，也能够利用双向指针疾速左右挪动，效率十分高。因而，B+树索引被广泛应用于数据库、文件系统等场景。 <center>图1 B+树</center>哈希索引：只须要做等值比拟查问，而不蕴含排序或范畴查问的需要，适宜应用哈希索引。比方对于身份证号的匹配。哈希索引定义：哈希索引基于哈希表实现，只有准确匹配索引的所有列的查问才无效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保留指向每个数据行的指针。 <center>图2 哈希构造</center> 3.索引劣势：不用检索全表，进步检索效率，（应用相似二分查找的形式）升高数据库的IO老本；升高数据排序的老本，升高了CPU的耗费。 4.索引劣势：索引也是占空间的；尽管进步了查问速度，然而会升高表的更新速度，在表更新时，也会对索引更新； 5.须要索引的状况：主键主动建设惟一索引；频繁查找的字段；查问中与其余表关联的字段，通过外键关系建设索引；频繁更新的字段不要建设索引；where条件里用不到的不要建设索引； 二、MYSQL优化查问语句EXPLAIN: MySQL 提供了一个 EXPLAIN 命令, 它能够对 SELECT 语句进行剖析, 并输入 SELECT 执行的详细信息, 以供开发人员针对性优化.EXPLAIN 命令用法为：在 SELECT 语句前加上 EXPLAIN，例如: <center>图2 EXPLAIN利用</center> EXPLAIN进去的信息有10列，别离是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,上面对这些字段呈现的可能进行解释： 1.id 它是SQL执行的程序的标识,从大到小的执行2.select_type示意查问中每个select子句的类型(1) SIMPLE(简略SELECT,不应用UNION或子查问等)(2) PRIMARY(查问中若蕴含任何简单的子局部,最外层的select被标记为PRIMARY)(3) UNION(UNION中的第二个或前面的SELECT语句)(4) DEPENDENT UNION(UNION中的第二个或前面的SELECT语句，取决于里面的查问)(5) UNION RESULT(UNION的后果)(6) SUBQUERY(子查问中的第一个SELECT)(7) DEPENDENT SUBQUERY(子查问中的第一个SELECT，取决于里面的查问)(8) DERIVED(派生表的SELECT, FROM子句的子查问)(9) UNCACHEABLE SUBQUERY(一个子查问的后果不能被缓存，必须从新评估外链接的第一行) 3.table显示这一行的数据是对于哪张表的，有时不是实在表名字，subquery示意子查问的表。 4.type示意MySQL在表中找到所需行的形式，又称“拜访类型”。罕用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行index: Full Index Scan，index与ALL区别为index类型只遍历索引树range:只检索给定范畴的行，应用一个索引来抉择行ref: 示意上述表的连贯匹配条件，即哪些列或常量被用于查找索引列上的值eq_ref: 相似ref，区别就在应用的索引是惟一索引，对于每个索引键值，表中只有一条记录匹配，简略来说，就是多表连贯中应用primary key或者 unique key作为关联条件const、system: 当MySQL对查问某局部进行优化，并转换为一个常量时，应用这些类型拜访。如将主键置于where列表中，MySQL就能将该查问转换为一个常量,system是const类型的特例，当查问的表只有一行的状况下，应用system。NULL: MySQL在优化过程中合成语句，执行时甚至不必拜访表或索引，例如从一个索引列里选取最小值能够通过独自索引查找实现。 ...

为什么大家都说SELECT * 效率低 作者 ： 陈哈哈来源： https://urlify.cn/ZvM3qe 面试官：“小陈，说一下你常用的SQL优化方式吧。” 陈小哈：“那很多啊，比如不要用SELECT *，查询效率低。巴拉巴拉...” 面试官：“为什么不要用SELECT * ？它在哪些情况下效率低呢？”陈小哈：“SELECT * 它好像比写指定列名多一次全表查询吧，还多查了一些无用的字段。” 面试官：“嗯...”陈小哈：“emmm~ 没了” 陈小哈：“....??（几个意思）” 面试官：“嗯...好，那你还有什么要问我的么？”陈小哈：“我问你个锤子，把老子简历还我！” 无论在工作还是面试中，关于SQL中不要用“SELECT *”，都是大家听烂了的问题，虽说听烂了，但普遍理解还是在很浅的层面，并没有多少人去追根究底，探究其原理。 废话不多说，本文带你深入了解一下"SELECT * "效率低的原因及场景。 本文很干！请自备茶水，没时间看记得先收藏 -- 来自一位被技术经理毒打多年的程序员的忠告一、效率低的原因先看一下最新《阿里java开发手册（泰山版）》中 MySQL 部分描述： 4 - 1. 【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。 说明： 增加查询分析器解析成本。 增减字段容易与 resultMap 配置不一致。 无用字段增加网络 消耗，尤其是 text 类型的字段。开发手册中比较概括的提到了几点原因，让我们深入一些看看： 1. 不需要的列会增加数据传输时间和网络开销用“SELECT * ”数据库需要解析更多的对象、字段、权限、属性等相关内容，在 SQL 语句复杂，硬解析较多的情况下，会对数据库造成沉重的负担。 增大网络开销；* 有时会误带上如log、IconMD5之类的无用且大文本字段，数据传输size会几何增涨。如果DB和应用程序不在同一台机器，这种开销非常明显 即使 mysql 服务器和客户端是在同一台机器上，使用的协议还是 tcp，通信也是需要额外的时间。 2. 对于无用的大字段，如 varchar、blob、text，会增加 io 操作准确来说，长度超过 728 字节的时候，会先把超出的数据序列化到另外一个地方，因此读取这条记录会增加一次 io 操作。（MySQL InnoDB） ...

以前学习了不少东西，都忘了不少，最近就想着总结一下，就想到想写博客文章来总结下自己这些年学习的东西，记录下各方面技术学习情况。 如果觉得好看，请给个赞 你有一个思想，我有一个思想，我们交换后，一个人就有两个思想If you can NOT explain it simply, you do NOT understand it well enough简单介绍下这篇文章的流程 1.为什么使用索引A：那还用说，肯定在某些方面有特长呗，比如你知道的【它很快，非常快】 我也很赞同这个答案，但说的不够具体，你得说明它为啥这么快 如果问你选择索引的原因就是一个【快】字，面试也就不会出那么多幺蛾子了。你有没有问过你自己 索引在所有场景下都是快的吗？知道它很快，何为快？怎样度量？索引（翻译官方文档）是帮助MySQL高效获取数据的排好序的数据结构拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。 2.索引数据结构详解在创建索引时，通常采用的数据结构有：Hash、二叉树、红黑树、B树以及B+树 可以在线查看数据结构的网页 Data Structure 二叉树:定义规则为左边节点值比根节点小，右边节点值比根节点大，并且左右子节点都是排序树 要是索引采取这种结构，数值递增那种，就要满足右边节点值比根节点大，导致检索数据会导致查了6遍磁盘 红黑树：（在jdk8之后，用数组+链表+红黑树来实现hashmap，当碰撞的元素个数大于8时 & 总容量大于64，会有红黑树的引入。）红黑树是一种自平衡二叉树，主要解决二叉搜索树在极端情况下退化成链表的情况，在数据插入的时候同时调整整个树，使其节点尽量均匀分布，保证平衡性，目的在于降低树的高度，提高查询效率。（右边的树的高度不会大于左边树的高度超过1，大于等于1级后会自动平衡，自己可在数据结构上插入试试） 特点： 节点是红色或者黑色根节点是黑色每个叶子的节点都是黑色的空节点（NULL）每个红色节点的两个子节点都是黑色的从任意节点到其每个叶子的所有路径都包含相同的黑色节点 优点：解决二叉搜索树的极端情况的退化问题。缺点：检索时间依旧与树的高度有关，当数据量很大时，树的高度就会很高，检索的次数就会比较多，检索的时间会比较久，效率低。从前面分析情况来看，减少磁盘IO的次数就必须要压缩树的高度，让瘦高的树尽量变成矮胖的树，所以B-Tree就在这样伟大的时代背景下诞生了 B-Tree 基于以上进行扩容，每个横向的节点变多了意味的存放的数据变多了，整个树的高度也变小了，减少磁盘io的搜索速度 特点1.叶节点具有相同的深度，叶节点的指针为空2.所有索引元素不重复3.节点中的数据索引从左到右递增排列 缺点：可以看到存放的数据类似key+value 的数据 要是InnoDB 的话data可能存放的是除了索引外的字段 页节点mysql默认推荐的是16k大小 ( show global status like 'Innodb_page_size';)，假如大节点的每个节点的data存的数据比较大，那么意味着横向能存储的索引就会变很少，大节点的能存储的索引变少意味着整颗树的高度受到限制 B+Tree(B-Tree变种 MySql默认使用索引结构) 1.非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引2.叶子节点包含所有索引字段3.叶子节点用指针连接，提高区间访问的性能 （快速定位范围查询，例如查询大于20，第一次io从根节点查询三次定位到20，然后通过后面的指针查询大于20的数据，就不用再从根节点的重新再查询，提高性能，叶子节点开始结束节点也是用指针连接串起来的） Hash前面说的mysql默认索引结构是B+Tree，还有一种索引结构是Hash如果是hash 的话是通过 hash（值）运算然后在磁盘中快速查找对应的磁盘文件指针从而找到行数据hash 索引查数据是很方便也快的，但是不支持范围性查找 例如 >= < between and 也不支持排序Hash索引适合等值查询 ，不适合范围查询 总结为什么mysql索引结构默认使用B+Tree为什么mysql索引结构默认使用B+Tree，而不是Hash，二叉树，红黑树？B-tree：因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；Hash：虽然可以快速定位，但是没有顺序，IO复杂度高。二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。红黑树：树的高度随着数据量增加而增加，IO代价高。 索引是如何支持千万级表的快速查询索引可以把它想象跟旁边的指针的成对存在的（指针是指向下一个节点的磁盘位置（占用6字节））索引假设字段为数字类型 Bigint 8b+ 指针默认占用空间6b = 14b （索引跟旁边的指针的成对存在的大小总和）大节点能存放 16kb数据 那么最多能存放 16kb * 1024/ 14= 1170个索引  ...