所谓索引就是对特定的mysql字段进行一些特定的算法排序，比如二叉树算法，哈希算法。哈希算法就是通过建立特征值来快速搜索。用的最多，也是mysql默认的二叉树算法BTREE。BTREE算法索引的字段，比如扫描20行，在使用BTREE之前可以得到扫描2^20行的结果。ExplainoptimizedquerydetectionEXPLAIN可以帮助开发者分析SQL问题。Explain展示了mysql如何使用索引来处理select语句和join表，可以帮助选择更好的索引，编写更优化的查询语句。使用方法，在select语句前加上Explain即可：Explainselect*fromblogwherefalse;在执行查询之前，mysql会分析每条发送过来的sql，决定是使用索引还是全表扫描如果发送select*fromblogwherefalse，mysql将不会执行查询操作，因为经过sql分析器的分析，mysql已经知道不会有匹配操作的语句。示例mysql>EXPLAINSELECT`birthday`FROM`user`WHERE`birthday`<"1990/2/2";--result:id:1select_type:SIMPLE--查询类型（简单查询、联合查询、子查询）table:user--显示该行数据是关于哪个表的。type:range--区间索引（小于1990/2/2区间的数据），这是一个重要的列，显示使用的是什么类型的连接。连接类型从好到坏依次是system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL。const表示命中一次，ALL表示扫描全表判断结果。一般来说，要保证query至少达到range级别，最好能达到ref。possible_keys:birthday--指示MySQL可以使用哪些索引来查找此表中的行。如果为空，则没有关联索引。此时为了提高性能，检查WHERE子句是否引用了某些字段，或者检查字段是否不适合建立索引。key:birthday——实际使用的索引。如果为NULL，则不使用索引。如果是primary，则表示使用主键。key_len:4--最长的索引宽度。如果键为NULL，则长度为NULL。长度越短越好，而且不会损失准确性。ref:const--显示哪个字段或常量与键一起使用。rows:1--这个数字表示mysql要遍历多少数据才能找到，这在innodb上是不准确的。额外：在哪里使用；Usingindex--执行状态描述，你可以在这里看到的坏例子是Usingtemporary和Usingselect_typesimple:simpleselect(donotuseunionorsubquery)。primary：最外层的选择。union:union中的第二个或后续select语句。Dependentunion：union中的第二个或后续的select语句依赖于外部的查询。联合结果：联合的结果。子查询：子查询中的第一个选择。依赖子查询：子查询中的第一个select依赖于外部查询。derived：派生表的select（from子句的子查询）。其他说明Distinct：MYSQL一旦找到与该行匹配的行，就不再查找。不存在：MYSQL优化了LEFTJOIN，一旦找到符合LEFTJOIN条件的行，就不再查找。RangecheckedforeachRecord(indexmap:#)：没有找到理想的索引，因此对于前一个表中行的每个组合，MYSQL检查使用哪个索引并使用它从表中返回行。这是使用索引的最慢连接之一。Usingfilesort：当你看到这个时，查询需要优化。MYSQL需要做一个额外的步骤来发现如何对返回的行进行排序。它根据连接类型和存储排序键值的行指针以及与条件匹配的所有行对所有行进行排序。Usingindex：列数据从一个表中返回，该表只使用索引中的信息，而没有实际读取操作。当表的所有请求列都是同一索引的一部分时，就会发生这种情况。Usingtemporary当你看到这个的时候，查询需要优化。在这里，MYSQL需要创建一个临时表来存储结果，这通常发生在不同列集的ORDERBY上，而不是GROUPBY上。Whereused使用WHERE子句来限制哪些行将匹配下一个表或返回给用户。如果您不想返回表中的所有行，并且连接类型是ALL或索引，或者查询在解释不同的连接类型（按效率排序）时出现问题，就会发生这种情况。系统表只有一行：系统表。这是const连接类型的特例。const：表中能匹配本次查询的一条记录的最大值（索引可以是主键也可以是唯一索引）。因为只有一行，所以这个值其实是一个常量，因为MYSQL先读取了这个值，并把它当作一个常量。eq_ref：在连接中，MYSQL在查询时从上一张表中为每个记录并集从表中读取一条记录，当查询使用索引作为主键或所有唯一键时使用。ref：只有当查询使用非唯一键或主键或这些类型的一部分（例如，使用最左边的前缀）时，才会出现此连接类型。对于前一个表的每个行并集，将从表中读取所有记录。这种类型很大程度上取决于索引匹配的记录数，越少越好。范围：此连接类型使用索引返回范围内的行，就像您使用>或<查找内容时发生的情况一样。index：这种连接类型对上一张表中的每条记录并集进行全扫描（优于ALL，因为索引一般小于表数据）。ALL：这种连接类型对之前的每个记录联合执行全扫描，这通常是不好的，应该尽可能避免。wheretype：如果是Onlyindex，表示只使用索引树中的信息来检索信息，比扫描全表要快。如果是whereused，则使用where限制。如果whereimpossiblewhere，说明where不需要，一般查不出什么。如果这个信息显示Usingfilesort或者Usingtemporary，那就很难了。WHERE和ORDERBY索引往往不能同时考虑。如果索引是根据WHERE确定的，那么在ORDERBY的时候必然会出现Usingfilesort，这取决于先过滤再排序，还是先排序再过滤，才划算。索引类型UNIQUE唯一索引不能有相同的值，可以有NULL值。INDEX普通索引允许出现相同的索引内容。PRIMARYKEY主键索引不允许有相同的值，也不能为NULL值，一张表只能有一个primary_key索引。fulltextindex全文索引以上三个索引都是作用于列的值，但是全文索引可以针对值中的某个词，比如一篇文章中的某个词。索引ALTERTABLE的CURD索引的创建适合在建表后添加。ALTERTABLEtablenameADDindextype(unique,primarykey,fulltext,index)[indexname](fieldname)ALTERTABLE`table_name`ADDINDEX`index_name`(`column_list`)--索引名称，可有可无；如果不是，则当前索引名称是字段名称。ALTERTABLE`table_name`ADDUNIQUE(`column_list`)ALTERTABLE`table_name`ADDPRIMARYKEY(`column_list`)ALTERTABLE`table_name`ADDFULLTEXTKEY(`column_list`)CREATEINDEXCREATEINDEX可以添加普通索引或UNIQUE索引到表。--例子：只能添加这两个索引CREATEINDEXindex_nameONtable_name(column_list)CREATEUNIQUEINDEXindex_nameONtable_name(column_list)另外，也可以在建表时添加：CREATETABLE`test1`(`id`smallint(5)UNSIGNEDAUTO_INCREMENTNOTNULL,--注意下面创建了主键索引，所以不需要创建`username`varchar(64)NOTNULLCOMMENT'username',`nickname`varchar(50)NOTNULLCOMMENT'nickname/name',`intro`text,PRIMARYKEY(`id`),UNIQUEKEY`unique1`(`username`),--索引名，可不可选，与列名KEY相同index1`(`nickname`),FULLTEXTKEY`intro`(`intro`))ENGINE=MyISAMAUTO_INCREMENT=4DEFAULTCHARSET=utf8COMMENT='backgroundusertable';deleteindexDROPINDEX`index_name`ON`talbe_name`ALTERTABLE`table_name`DROPINDEX`index_name`--这两句是等价的，都是删除table_name中的索引index_name；ALTERTABLE`table_name`DROPPRIMARYKEY--删除主键索引，注意只能通过这种方式删除主键索引seeshowindexfromtablename;changetheindex,deleteandrebuildaskillthatcancreateanindex使用高维列创建索引。数据列中唯一值的个数，个数越大，维度越高。比如数据表中有8行数据a,b,c,d,a,b,c,d。这张表的维度是4，要为gender、age等高维的列创建索引，age的维度要高于gender。诸如性别之类的列不适合建立索引，因为它们的维度太低。对出现在where、on、groupby、orderby中的列使用索引。对较小的数据列使用索引，这将使索引文件更小，并且可以在内存中加载更多的索引键。对较长的字符串使用前缀索引。不要创建太多索引。除了增加额外的磁盘空间外，对DML操作的速度也会有很大的影响，因为每次添加或删除时，都必须重建索引。使用复合索引可以减小文件索引的大小，使用时比多个单列索引速度更快。使用指标的技巧1.轴向长度平衡了辨别力和长度技巧。截取不同长度，测试判别力#这里假设截取6个字符计算判别力，直到判别力达到0.1，可以将该字段的长度作为索引mysql>selectcount(distinctleft([varchar]],6))/count(*)来自表；#注意：设置前缀索引时指定的长度表示字节数，对于非二进制类型（CHAR，ForVARCHAR，TEXT）字段，字段长度表示字符数，所以#因此，在设置前缀之前index，计算出来的字符数需要换算成字节数。常用字符集与字节的关系如下：#latinsinglewordSection:1B#GBK双字节:2B#UTF8三字节:3B#UTF8mb4四字节:4B#myisam表的索引大小默认为1000bytes，innodbtable的索引大小默认为767字节，可以在文件中修改innodb_large_prefix#项的值来配置增加innodb索引的大小，最大为3072字节。差异可以达到0.1。2.不易区分的左对齐细分索引构建方式。这样的细分，左边有大量重复的字符。一些URL细分和汇总http://store，反向索引。添加一个伪哈希变量，将字符串转换为整数3.索引覆盖的概念：如果查询列恰好是索引的一部分，那么查询只需要在索引文件上进行，不需要返回到磁盘。这种查询速度极快，世人称之为索引覆盖4.延迟关联根据条件查询数据时，如果查询条件不能使用索引，可以先输入数据行的id，然后根据id取数据行。//普通查询不使用索引select*frompostwherecontentlike"%news%";//延迟关联优化后，内层查询使用内容索引，取出id，使用join查询所有行selecta.*frompostasainnerjoin(selectidfrompostwherecontentlike"%news%")asbona.id=b.id;5.索引排序 排序库增加索引提高速度。6、重复索引和冗余索引重复索引：在同一列或几列以相同顺序建立多个索引，成为重复索引，没有任何意义，减少冗余索引：两个或多个索引覆盖列重叠，而对于列m,n,加上索引m(m),indexmn(m,n)，称为冗余索引。7、索引碎片和维护在数据表长期变化的过程中，索引文件和数据文件都会出现空洞，形成碎片。修复表的过程是非常耗费资源的，修复表的周期比较长。//cleanup方法alerttablexxxengineinnodb;//或优化表xxx；8.Innodb引擎索引注意事项Innodb表尽量自己指定主键，如果有几个列是唯一的，就选择最频繁的作为访问条件另外，InnoDB表的普通索引会保存主键的键值key，所以为主键选择快照的数据类型，可以有效降低索引的磁盘占用，提高索引的缓存效果。复合索引和前缀索引注意，这两个名称是索引技术的名称，而不是索引的类型。复合索引MySQL单列索引和复合索引有什么区别？为了直观对比两者，先建表：CREATETABLE`myIndex`(`i_testID`INTNOTNULLAUTO_INCREMENT,`vc_Name`VARCHAR(50)NOTNULL,`vc_City`VARCHAR(50)NOTNULL,`i_Age`INTNOTNULL,`i_SchoolID`INTNOTNULL,PRIMARYKEY(`i_testID`));假设表中有1000条记录，在这10000条记录中，vc_Name=”erquan”的5条记录分布在7上8下，只是城市、年龄、学校的组合不同。看看这个T-SQL：SELECT`i_testID`FROM`myIndex`WHERE`vc_Name`='erquan'AND`vc_City`='Zhengzhou'AND`i_Age`=25;--协会搜索；首先考虑构建MySQL单列索引：在vc_Name列上创建索引。在执行T-SQL时，MYSQL很快将目标锁定在vc_Name=erquan这5条记录上，取出来放在一个中间结果集中。在这个结果集中，先排除vc_City不等于“郑州”的记录，再排除i_Age不等于25的记录，最后过滤掉唯一符合条件的记录。虽然在vc_Name上建立了索引，MYSQL在查询时不需要扫描整张表，效率有所提高，但离我们的要求还有一定的距离。同样，在vc_City和i_Age中建立MySQL单列索引的效率也差不多。为了进一步压榨MySQL的效率，需要考虑构建复合索引。就是把vc_Name,vc_City,i_Age建成索引：ALTERTABLE`myIndex`AD??DINDEX`name_city_age`(vc_Name(10),vc_City,i_Age);建表的时候，vc_Name的长度是50，这里为什么用10呢？这就是下面要说的前缀索引，因为一般名字的长度不会超过10，这样会加快索引查询速度，减小索引文件的大??小，增加INSERT的更新速度。执行T-SQL时，MySQL不需要扫描任何记录就可以找到唯一的记录！如果分别在vc_Name、vc_City、i_Age上创建单列索引，让表有3个单列索引，查询效率会不会和上述组合索引一样？答案截然不同，远低于我们的综合指数。此时虽然有3个索引，但是MySQL只能使用其中一个，貌似是单列索引效率最高的，另外两个没有用，也就是说还是全表的过程扫描。建立这样一个组合索引其实相当于建立三个组合索引：vc_Name、vc_City、i_Agevc_Name、vc_Cityvc_Name！为什么没有vc_City、i_Age等复合索引呢？这是mysql复合索引的“最左前缀”的结果。简单的理解就是只从最左边开始组合。不仅包含这三列的查询会使用这个复合索引，下面的T-SQL也会使用它：SELECT*FROMmyIndexWHREEi_Age=20ANDvc_City="郑州"SELECT*FROMmyIndexWHREEvc_City="郑州"即name_city_age(vc_Name(10),vc_City,i_Age)从左到右的索引,如果没有左前索引Mysql不会进行索引查询。前缀索引如果索引列的长度过长，这种列索引会生成一个很大的索引文件，不便于操作。您可以使用前缀索引方法来索引前缀索引。前缀索引要控制在一个合适的点，就是0.31gold是的（大于这个值可以创建）。从Arctic中选择COUNT(DISTINCT(LEFT(title,10)))/COUNT(*)；—该值大于0.31创建前缀索引，重复ALTERTABLEuserADDINDEXuname(title(10));--增加PrefixindexSQL，name的索引建立在10，可以减小索引文件的大??小，加快索引查询。什么样的sql不使用索引尽量避免这些sqlSELECT`sname`FROM`stu`W??HERE`age`+10=30;--不会使用索引，因为所有索引列都参与计算SELECT`sname`从`stu`到左边(`date`,4)<1990;--不会使用索引，因为使用了函数操作，原理同上SELECT*FROM`houdunwang`WHERE`uname`LIKE'backing%'--取索引SELECT*FROM`houdunwang`WHERE`uname`LIKE"%backing%"--donotusetheindex--正则表达式不使用索引，这个应该很容易理解，那么为什么在SQL中很难看到regexp关键字原因--stringand数字比较不使用索引；创建表`a`(`a`char(10));EXPLAINSELECT*FROM`a`WHERE`a`="1"--遍历索引EXPLAINSELECT*FROM`a`WHERE`a`=1--不使用索引select*fromdeptwheredname='xxx'orloc='xx'ordeptno=45--如果条件中有or，即使有带index的条件也会被使用。换句话说，所有需要使用的字段都必须被索引。我们建议您尽可能避免使用or关键字——如果mysql估计使用全表扫描比使用索引更快，那么就不要使用索引。多表关联时的索引效率SELECTsnameFROMstuWHERELEFT(date,4)<1990;--不会使用索引，因为使用了函数操作，原理同上索引从上图可以看出，所有表的类型都是all，即全表索引。即666，一共遍历了216条查询。除了第一个代表全表索引（必须的，必须关联其他表），其余都是范围（从索引区间获取），即6+1+1+1，一共是9个遍历查询就够了。因此，我们建议您在连接多个表时，连接的表越少越好，因为如果您不小心，这将是一次可怕的笛卡尔积扫描。另外，我们也建议尽量使用leftjoin，以少关联多。因为如果使用join的话，必须对第一个表进行全扫描，通过lessassociation和more可以减少扫描次数。索引的缺点不要盲目创建索引，只为查询操作频繁的列创建索引。创建索引会使查询操作更快，但会减慢添加、删除和更新操作的速度，因为这些操作也会影响索引文件的重新排序或更新。但是在互联网应用中，查询语句比DML语句大很多，甚至可以占到80%~90%，所以不用太在意，但是在导入大数据的时候，可以删除索引首先，然后批量插入数据。最后添加索引。更多学习内容，可访问【与各大厂商对比】优质PHP架构师教程目录。只要能读懂，就能保证你的薪水更上一层楼（持续更新中）。以上内容希望对大家有所帮助。很多PHPer总会遇到一些问题和瓶颈，业务代码写多了没有方向感，不知道从哪里开始改进，我整理了一些这方面的资料，包括但不限于：分布式架构，高可扩展性、高性能、高并发、服务器性能调优、TP6、laravel、YII2、Redis、Swoole、Swoft、Kafka、Mysql优化、shell脚本、Docker、微服务、Nginx等知识点免费分享给你如果你需要高级干货。有需要的可以点击链接领取高级PHP月薪30k>>>架构师成长之路【免费获取视频和面试资料】
