温馨提示PC端浏览体验感最佳~~索引索引概述索引index是帮助MySQL高效获取数据的数据结构有序。在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用指向数据这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。索引的优缺点索引结构MySQL的索引是在存储引擎层实现的不同的存储引擎有不同的结构主要包含以下几种不同存储引擎对索引的支持情况注意我们平常所说的索引如果没有特别指明都是指B树结构组织的索引。数据结构二叉树二叉树缺点顺序插入时会形成一个链表查询性能大大降低。大数据量情况下层级较深检索速度慢。红黑树自平衡的二叉树红黑树缺点大数据量情况下层级较深检索速度慢。B-Tree多路平衡查找树以一颗最大度数max-degree为55阶的b-tree为例每个节点最多存储4个key5个指针说明树的度数指的是一个节点的子节点个数。B-Tree构建过程示例具体动态变化的过程可以参考网站https://www.cs.usfca.edu/~galles/visualization/BTree.htmlBTree以一颗最大度数max-degree为44阶的btree为例BTree构建过程示例BTree相对于B-Tree的区别所有的数据都会出现在叶子节点叶子节点形成一个单向链表MySQL索引数据结构中BTree的应用MySQL索引数据结构对经典的BTree进行了优化。在原BTree的基础上增加一个指向相邻叶子节点的链表指针就形成了带有顺序指针的BTree提高区间访问的性能。Hash哈希索引就是采用一定的hash算法将键值换算成新的hash值映射到对应的槽位上然后存储在hash表中。如果两个或多个键值映射到一个相同的槽位上他们就产生了hash冲突也称为hash碰撞可以通过链表来解决。Hash索引特点Hash索引只能用于对等比较in不支持范围查询between…无法利用索引完成排序操作查询效率高通常只需要一次检索就可以了效率通常要高于Btree索引存储引擎支持在MySQL中支持hash索引的是Memory引擎而InnoDB中具有自适应hash功能hash索引是存储引擎根据BTree索引在指定条件下自动构建的。InnoDB存储引擎选择使用Btree索引结构的原因相对于二叉树层级更少搜索效率高对于B-tree无论是叶子节点还是非叶子节点都会保存数据这样导致一页中存储的键值减少Page的大小固定为16K指针跟着减少要同样保存大量数据只能增加树的高度导致性能降低相对Hash索引Btree支持范围匹配及排序操作索引分类在InnoDB存储引擎中根据索引的存储形式又可以分为以下两种聚集索引选取规则如果存在主键主键索引就是聚集索引。如果不存在主键将使用第一个唯一UNIQUE索引作为聚集索引。如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引。聚集索引与二级索引示例说明其中id为主键根据id建立的主键索引就是聚集索引聚集索引的叶子节点下面挂的是这一行的数据。而以name字段建立的索引是二级索引二级索引的叶子节点下面挂的是这一行数据的主键id。回表查询假如执行以下SQL语句select*fromuserwherenameArm;则SQL语句的执行过程为先根据name字段对应的二级索引找到数据对应的主键值再根据主键值在聚集索引中拿到这一行的行数据。InnoDB主键索引的Btree不同高度可存储的数据量的计算假设一行数据大小为1k一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间主键即使为bigint占用字节数为8。高度为2n * 8 (n 1) * 6 16 * 1024算出n约为 1170其中n为非叶子节点存储key的数量所以高度为2的BTree能够存储的数据量大概为1171 * 16 18736高度为3高度为3的BTree能够存储的数据量大概为1171 * 1171 * 16 21939856索引语法创建索引CREATE[UNIQUE|FULLTEXT]INDEXindex_nameONtable_name(index_col_name,...);补充一个索引既可以只关联一个字段称为单列索引也可以关联多个字段称为联合索引或组合索引。查询索引SHOWINDEXFROMtable_name;删除索引DROPINDEXindex_nameONtable_name;SQL性能分析SQL执行频率MySQL客户端连接成功后通过show [session | global] status命令可以提供服务器状态信息。通过如下指令可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次SHOWGLOBALSTATUSLIKECom_______;执行指令后的结果如图慢查询日志慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认10秒的所有SQL语句的日志。查看慢查询日志开关的语法showvariableslikeslow_query_log;MySQL的慢查询日志默认没有开启需要在MySQL的配置文件/etc/my.cnf中配置如下信息这部分实操时可能跟课程有出入例如MySQL配置文件的位置、慢日志文件的位置以及名称# 开启MySQL慢日志查询开关slow_query_logl# 设置慢日志的时间为2秒SQL语句执行时间超过2秒就会视为慢查询记录慢查询日志long_query_time2配置完毕之后通过 systemctl restart mysqld 指令重新启动MySQL服务器进行测试查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。profile详情show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数能够看到当前MySQL是否支持profile操作SELECThave_profiling;默认profiling是关闭的可以通过set语句在session/global级别开启profilingSETprofiling1;执行一系列的业务SQL的操作然后通过如下指令查看指令的执行耗时#查看每一条SQL的耗时基本情况showprofiles;#查看指定query_id的sQL语句各个阶段的耗时情况showprofileforquery query_id;#查看指定query_id的SQL语句CPU的使用情况showprofile cpuforquery query_id;explain执行计划EXPLAIN或者DESC命令获取MYSQL如何执行SELECT语句的信息包括在SELECT语句执行过程中表如何连接和连接的顺序。语法#直接在select语句之前加上关键字explain/descEXPLAINSELECT字段列表FROM表名WHERE条件;执行指令后的结果EXPLAIN执行计划各字段含义idselect查询的序列号表示查询中执行select子句或者是操作表的顺序id相同执行顺序从上到下id不同值越大越先执行。select_type表示SELECT的类型常见的取值有SIMPLE简单表即不使用表连接或者子查询、PRIMARY主查询即外层的查询、UNIONUNION中的第二个或者后面的查询语句、SUBQUERYSELECT/WHERE之后包含了子查询等。type表示连接类型性能由好到差的连接类型为NULL不访问任何表在实际业务开发中不可能、system访问系统表、const根据主键或唯一索引进行访问、eq_ref、ref使用非唯一性的索引进行查询时、range、index虽然用了索引但会对索引进行扫描遍历整个索引树、all全表扫描。在优化SQL语句时尽量将type往前优化possible_key显示可能应用在这张表上的索引一个或多个。key实际使用的索引如果为NULL则没有使用索引。key_len表示索引中使用的字节数该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下长度越短越好。rowsMySQL认为必须要执行查询的行数在InnoDB引擎的表中是一个估计值可能并不总是准确的。filtered表示返回结果的行数占需读取行数的百分比filtered的值越大越好。索引使用规则最左前缀法则如果索引了多列联合索引要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始并且不跳过索引中的列。如果跳跃某一列索引将部分失效后面的字段索引失效。可以通过类似以下SQL语句进行验证#假设已为tb_user表的profession、age、status三个字段建立了联合索引idx_user_pro_age_sta#且建立联合索引时的字段列表为(profession, age, status)#满足最左前缀法则通过索引进行查询profession、age、status三个字段的索引都生效explainselect*fromtb_userwhereprofession软件工程andage31andstatus0;#满足最左前缀法则通过索引进行查询profession、age两个字段的索引都生效explainselect*fromtb_userwhereprofession软件工程andage31;#满足最左前缀法则通过索引进行查询profession字段的索引生效explainselect*fromtb_userwhereprofession软件工程;#满足最左前缀法则通过索引进行查询profession字段的索引生效但status字段的索引失效因为中间跳过了age字段explainselect*fromtb_userwhereprofession软件工程andstatus0;#注意最左前缀法则与SQL语句的书写顺序没有关系只要最左列字段存在即可例如下面这条SQL语句是满足最左前缀法则的(其他字段也是如此)explainselect*fromtb_userwhereage31andstatus0andprofession软件工程;#不满足最左前缀法则通过全表扫描进行查询因为查询没有从索引的最左列——profession开始explainselect*fromtb_userwhereage31andstatus0;#不满足最左前缀法则通过全表扫描进行查询因为查询没有从索引的最左列——profession开始explainselect*fromtb_userwherestatus0;范围查询联合索引中出现范围查询范围查询右侧的列索引失效可以通过类似以下SQL语句进行验证#假设已为tb_user表的profession、age、status三个字段建立了联合索引idx_user_pro_age_sta#且建立联合索引时的字段列表为(profession, age, status)#age字段使用了进行范围查询所以age右侧的status字段的列索引失效explainselect*fromtb_userwhereprofession软件工程andage30andstatus0;#age字段使用的是进行范围查询其右侧的status字段的列索引不会失效使用联合索引时在业务允许的情况下尽量使用或这样的运算符explainselect*fromtb_userwhereprofession软件工程andage30andstatus0;#注意范围查询右侧的列索引是由创建索引时的字段列表的顺序决定的跟SQL语句的书写顺序无关#例如以下SQL语句中status字段的列索引仍会失效explainselect*fromtb_userwhereprofession软件工程andstatus0andage30;索引列运算尽量不要在索引列上进行运算操作否则索引将失效。可以使用类似以下SQL语句进行验证#假设已为tb_user表的phone字段建立了索引#因为对phone字段进行了运算操作所以phone字段的索引失效explainselect*fromtb_userwheresubstring(phone,10,2)15;字符串不加引号字符串类型的字段使用时不加引号索引将失效。可以使用类似以下的SQL语句进行验证#假设已为tb_user表的profession、age、status三个字段建立了联合索引idx_user_pro_age_sta#且建立联合索引时的字段列表为(profession, age, status)其中status的字段类型为字符串类型#因为status0存在隐式类型转换所以status字段的索引失效注意其他两个字段的索引仍生效explainselect*fromtb_userwhereprofession软件工程andage31andstatus0;模糊查询如果仅仅是尾部模糊匹配索引不会失效。如果是头部模糊匹配索引失效。可以使用类似以下的SQL语句进行验证#假设已为tb_user表的profession、age、status三个字段建立了联合索引idx_user_pro_age_sta#且建立联合索引时的字段列表为(profession, age, status)#尾部模糊匹配且符合最左前缀法则索引生效explainselect*fromtb_userwhereprofessionlike软件%;#头部模糊匹配索引失效explainselect*fromtb_userwhereprofessionlike%工程;#头部模糊匹配索引失效explainselect*fromtb_userwhereprofessionlike%工%;or连接的条件用or分割开的条件如果or前的条件中的列有索引而后面的列中没有索引那么涉及的索引都不会被用到。可以使用类似以下的SQL语句进行验证#假设已为tb_user表的profession、age、status三个字段建立了联合索引idx_user_pro_age_sta#且建立联合索引时的字段列表为(profession, age, status)#id为主键主键索引#age的联合索引不生效不满足最左前缀法则此时or的一侧id字段有索引另一侧age字段没有索引故id和age的索引都失效explainselect*fromtb_userwhereid10orage23;由于age没有索引所以即使id有索引索引也会失效。所以需要针对于age也要建立索引。数据分布影响如果MySQL评估使用索引比全表更慢则不使用索引。可以使用类似以下的SQL语句进行验证#假设已为tb_user表的phone字段建立了索引#tb_user表的数据请看下面的第一张图#此时表中的大部分数据都满足条件MySQL评估使用索引比全表更慢所以不使用索引直接全表扫描select*fromtb_userwherephone17799990005;#此时表中只有少部分数据满足条件MySQL评估使用索引比全表更快所以使用索引select*fromtb_userwherephone17799990015;SQL提示SQL提示是优化数据库的一个重要手段简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。user index建议MySQL使用指定的索引MySQL可以接受建议也可以不接受explainselect*fromtb_useruseindex(idx_user_pro)whereprofession软件工程;ignore index让MySQL忽略指定的索引explainselect*fromtb_userignoreindex(idx_user_pro)whereprofession软件工程;force index强制MySQL必须使用指定的索引explainselect*fromtb_userforceindex(idx_user_pro)whereprofession软件工程;覆盖索引尽量使用覆盖索引查询使用了索引并且需要返回的列在该索引中已经全部能够找到减少select *。观察以下SQL语句执行结果中的Extra列#假设已为tb_user表的profession、age、status三个字段建立了联合索引idx_user_pro_age_sta#且建立联合索引时的字段列表为(profession, age, status)#id为主键主键索引#name没有索引explainselectid,professionfromtb_userwhereprofession软件工程andage31andstatus0;explainselectid,profession,age,statusfromtb_userwhereprofession软件工程andage31andstatus0;explainselectid,profession,age,status,namefromtb_userwhereprofession软件工程andage31andstatus0;explainselect*fromtb_userwhereprofession软件工程andage31andstatus0;说明using index condition查找使用了索引但是需要回表查询数据。using where; using index查找使用了索引但是需要的数据都在索引列中能找到所以不需要回表查询数据。效率更高前缀索引当字段类型为字符串varchartext等时有时候需要索引很长的字符串这会让索引变得很大查询时浪费大量的磁盘IO影响查询效率。此时可以只将字符串的一部分前缀建立索引这样可以大大节约索引空间从而提高索引效率。语法createindexidx_xxxxontable_name(column(n));前缀长度可以根据索引的选择性来决定而选择性是指不重复的索引值基数和数据表的记录总数的比值索引选择性越高则查询效率越高唯一索引的选择性是1这是最好的索引选择性性能也是最好的。#计算整个email字段的选择性selectcount(distinctemail)/count(*)fromtb_user;#计算email字段只截取前5个字符的选择性selectcount(distinctsubstring(email,1,5)/count(*)fromtb_user;前缀索引查询流程说明假设我们将email字段的前5个字符建立了前缀索引当执行上图中的SQL语句时会先截取email的前五个字符去二级索引进行查找找到相同的前缀后再拿着id去聚集索引进行查找查找到对应的数据后不会直接返回而是会将数据中的email字段拿出来与SQL语句中的email进行比对如果相同则证明这条数据是符合查询条件的数据并且比对成功后还会继续接着查找辅助索引的下一个比对下一个前缀是否也相同例如图中定位到lvbu6并且数据也比对成功后还会继续看xiaoy是否符合条件这里显然不符合如果相同则继续拿着id进行回表查询重复之前的步骤若不相同直接组装结果并返回数据。单列索引与联合索引单列索引即一个索引只包含单个列。联合索引即一个索引包含了多个列。在业务场景中如果存在多个查询条件考虑针对于查询字段建立索引时建议建立联合索引而非单列索引。单列索引情况explainselectid,phone,namefromtb_userwherephone17799990010andname韩信;执行以上SQL语句的结果说明多条件联合查询时MySQL优化器会评估哪个字段的索引效率更高会选择该索引完成本次查询。联合索引的结构说明对于上图中的联合索引phone在前name在后会优先根据phone进行排序如果phone相同再根据name进行排序。索引设计原则1、针对于数据量较大且查询比较频繁的表建立索引。2、针对于常作为查询条件where、排序order by、分组group by操作的字段建立索引。3、尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高。4、如果是字符串类型的字段字段的长度较长可以针对于字段的特点建立前缀索引。5、尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率。6、要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增删改的效率。7、如果索引列不能存储NULL值请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时它可以更好地确定哪个索引最有效地用于查询。