一、索引基础知识1.1索引的优点大大减少了服务器需要扫描的数据量，加快数据库的检索速度，帮助服务器避免排序和临时表，并将随机io变成顺序io1.2索引的用处快速找到匹配WHERE子句的行排除行如果可以在多个索引之间进行选择，mysql通常会使用找到最少行的索引如果表有多个-列索引，优化器可以使用索引通过表的任何最左边的前缀查找行当存在表连接时，从其他表中检索行数据查找特定索引列的最小值或最大值如果排序或分组是，则对表进行排序doneonthemostleftofprefixofanavailableindexandgrouping在某些情况下，可以优化查询以检索值而不查询数据行是唯一且非空的主键索引：它是一个特殊的unique不允许空值的索引。（主键约束是主键索引）唯一索引：索引列中的值必须是唯一的，但允许空值。普通索引：MySQL中的基本索引类型，没有任何限制，允许在定义索引的列中插入重复值和空值，纯粹是为了更快的数据查询。全文索引：只能用在MyISAM引擎上，只能用在CHAR、VARCHAR、TEXT类型字段>什么是全文索引？该字段所属的记录行，例如“LOLLPL牧小农”，可以通过养小农查找该记录。这里说的是可能的，因为全文索引的使用涉及到很多细节，我们只需要知道大概的意思就可以了。一般开发中不会用到全文索引，因为占用物理空间大，减少记录修改，所以比较少用。复合索引：在表中多个字段的组合上创建的索引，只有在查询条件中使用了这些字段的左字段时才会使用，使用复合索引时遵循最左前缀集。例如，这里有一个索引，由三个字段组成：id、name、age。索引行按照id/name/age的顺序存储。索引可以索引以下字段组合(id,name,age),(id,name)or(id)。如果要查询的字段不构成索引的最左前缀，那么就不会使用索引，比如age或(name,age)的组合就不会使用索引查询。1.4面试专业术语返回表：数据库根据索引（非主键）找到指定记录的行后，还需要根据主键再次从数据块中获取数据。文章：经过三轮面试，落在一道sql题——sql性能优化最左匹配：指的是在联合索引中，如果你的sql语句使用了联合索引中的最左索引，那么这条sql语句就可以使用这个联合索引进行匹配，如果遇到范围查询（>、<、between、like），则停止匹配。select*fromtwherea=1andb=1andc=1;#这个可以使用定义的索引(a,b,c),usea,b,cselect*fromtwherea=1andb=1;#这个可以使用定义的索引(a,b,c),usea,bselect*fromtwhereb=1anda=1;#这样可以使用定义的索引(a,b,c)，使用a,c（mysql有查询优化器）select*fromtwherea=1;#这个也可以使用定义的索引(a,b,c),useaselect*fromtwhereb=1andc=1;#这个不能使用定义的索引(a,b,c)select*fromtwherea=1andc=1;#这个可以使用定义的索引(a,b,c)，但是只使用a索引，b,c索引不使用索引下推：称为IndexConditionPushdown(ICP)，这是MySQL提供的使用某个索引获取tuplesfromatableforaspecifictable”，注意我们这里特别强调了“一个”，这是因为这样的索引优化不是用于多表连接而是用于单表扫描，确切地说，它是一个单表扫描使用索引获取数据的方式。1.5索引使用的数据结构1.5.1哈希表的缺点：1、如果使用哈希来存储，需要把所有的数据文件都加到内存中，比较消耗内存空间。2、如果所有的查询都是等值查询，那么hash确实是非常快的，但是在企业或者实际工作环境中，范围内需要查找的数据比较多，而不是等值查询，所以hash并不适用。1.5.3B+树B-tree的特点：1.所有的键值分布在整棵树中2.搜索可能结束于非叶子节点，在关键字的完整集合中做一次搜索，则性能接近二分查找3.每个节点最多有m个子树4.根节点至少有2个子树5.分支节点至少有m/2个子树（除根节点和叶子节点外都是分支节点）6.所有叶子节点都在同一层，每个节点最多有m-1个key，按升序排列word和三个指向子树根节点的指针，指针存放的是子树的地址子节点所在的磁盘块。两个关键字划分的三个作用域对应三个指针指向的子树的数据作用域。以根节点为例，关键字为16和34，P1指针指向的子树数据范围小于16，P2指针指向的子树数据范围为16~34，且P3指针指向的子树的数据范围大于34。关键字查找过程：根据根节点找到磁盘块1，读入内存。【第一次磁盘I/O操作】比较关键字28在区间（16,34），找到磁盘块1的指针P2。根据P2指针找到磁盘块3，读入内存。【第二次磁盘I/O操作】比较关键字28在区间（25,31）内，找到磁盘块3的指针P2。根据P2指针找到磁盘块8，读入内存。【第三次磁盘I/O操作】在第8块磁盘的关建宁列表中找到关键字28。缺点：每个节点都有key，也有数据，每个page的存储空间有限。如果数据比较大，每个节点存储的key数量会变少。当存储的数据量很大时，会导致深度很大，查询时增加磁盘io数，影响查询性能。1.6索引匹配方式全值匹配：全值匹配是指匹配索引中的所有列explainselect*fromstaffswherename='July'andage='23'andpos='dev';匹配最左边的前缀：只匹配前几列匹配列前缀：可以匹配某列的值explainselect*fromstaffswherenamelike'J%';的开头部分explainselect*fromstaffswherenamelike'%y';匹配范围值：可以找到一定范围内的数据explainselect*fromstaffswherename>'Mary';精确匹配某一列，范围匹配另一列：可以查询第一列的全部和第二列的一部分explainselect*fromstaffswherename='July'andage>25;只访问索引的查询：查询时只需要访问索引，不需要访问数据行，本质上是一个覆盖索引explainselectname,age,posfromstaffswherename='July'andage=25andpos='dev';2、哈希索引是基于哈希表实现的。只有与索引的所有列完全匹配的查询才有效。在mysql中，只有内存存储引擎明确支持哈希索引哈希索引本身只需要存储对应的哈希值，因此索引的结构非常紧凑，这使得哈希索引的搜索速度非常快2.1的局限性哈希索引哈希索引只包含哈希值和行指针，不存储字段值，索引不能使用索引中的值来避免读取行。哈希索引数据不是按照索引值的顺序存储的，因此无法排序。哈希索引不支持部分列匹配查找。哈希索引使用索引列计算哈希值的全部内容哈希索引支持相等比较查询，不支持任何范围查询和访问散列索引数据非常快，除非有很多散列冲突。当存在哈希冲突时，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行哈希。如果冲突多，维护成本会很高。2.2案例当需要存储大量的URL，并根据URL进行查找时，如果使用B+树，存储的内容会非常大：selectidfromurlwhereurl=""也可以使用CRC32进行hash网址。可以使用如下查询方式：selectidfomurlwhereurl=""andurl_crc=CRC32("")这种查询之所以性能高，是因为它使用了一个小的索引来完成搜索3.复合索引当包含多个列作为索引，需要注意的是正确的顺序取决于索引的查询，同时需要考虑如何更好的满足排序和分组的需求案例：创建复合indexa,b,c,索引在不同SQL语句中的使用4.聚簇索引与非聚簇索引4.1聚簇索引不是一种单独的索引类型，而是一种数据存储方式，指的是数据行的紧凑存储和相邻键值，把数据存储和索引放在一起，找到索引就找到数据。如果没有定义主键，InnoDB将选择一个唯一的非空索引。如果没有唯一索引，InnoDB会隐式定义一个主键作为聚簇索引。InnoDB仅聚合同一页中的记录。包含相邻键值的页面可能相距很远。4.2非聚集索引数据文件与索引文件分开存放，数据存放在索引结构中。索引结构的叶节点指向数据的相应行。Myisam首先通过key_buffer将索引缓存在内存中。当需要访问数据时（通过索引访问数据），直接在内存中查找索引，然后通过索引在磁盘上找到对应的数据，这就是索引不命中keybuffer的原因，速度慢的原因是通过叶子节点指针查找数据页中的数据，所以非聚合聚簇索引是一个逻辑顺序V.覆盖索引5.1基本介绍如果一个索引包含了该索引的所有字段的值需要查询，我们称之为覆盖索引。并不是所有类型的索引都可以称为覆盖索引。覆盖索引必须存储索引列，不同值的存储有不同的覆盖索引实现方式。并不是所有的引擎都支持覆盖索引，内存也不支持覆盖索引。5.2优点1.索引条目通常比数据行的大小小很多。更大更少的数据访问2.因为索引是按照列值的顺序存储的，IO密集型范围查询会比从磁盘随机读取每一行数据的IO少很多。3.一些存储引擎如MYISAM在内存中只缓存了索引，而数据依赖于操作系统来缓存，所以访问数据需要系统调用，可能会造成严重的性能问题4.由于聚簇索引的INNODB，覆盖索引对INNODB表特别有用5.3案例演示1.发起索引覆盖查询时，在explain的extra列中可以看到使用索引的信息，此时使用了覆盖索引.2.在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中某些列的查询。不过可以进一步优化，可以使用innodb的二级索引覆盖查询。例如：actor使用innodb存储引擎，last_name字段有二级索引。索引列虽然不包含主键actor_id，但也可以用来对actor_id进行覆盖查询。6.优化细节当使用索引列进行查询时尽量不要使用表达式，将计算放在业务层而不是数据库层。尽量使用主键查询而不是其他索引，因为主键查询不会触发回表查询。使用前缀索引>有时需要索引很长的字符串，这会使索引变大变慢。通常，可以在列的开头使用部分字符串，这样可以大大节省索引空间，提高索引效率，但这会降低索引的选择性。索引的选择性是指没有重复的索引值占数据表记录总数的比例，范围从1/#T到1。索引的选择性越高，查询效率越高，因为更有选择性的索引可以让mysql在搜索的时候过滤掉更多的行。一般来说，列前缀的选择性足够高，可以满足查询的性能，但是对应BLOB、TEXT、VARCHAR类型的列，必须使用前缀索引，因为mysql不允许全长这些要索引的列，使用该方法的技巧是选择足够长的前缀，保证高选择性，但又不能太长。--创建数据表createtablecitydemo(cityvarchar(50)notnull);insertintocitydemo(city)selectcityfromcity;--重复以下sql语句5次insertintocitydemo(city)selectcityfromcitydemo;--更新城市表名称)limit1);--找到最常见的城市列表，发现每个值出现45-65次，selectcount(*)ascnt,cityfromcitydemogroupbycityorderbycntdesclimit10;--找到最常出现的城市前缀，以3个前缀字母开头，找到原来出现次数较多，可以截取多个字符查看城市出现次数selectcount(*)ascnt,left(city,3)aspreffromcitydemogroupbypreforderbycntdesclimit10;selectcount(*)ascnt,left(city,7)aspreffromcitydemogroupbypreforderbycntdesclimit10;--此时前缀的选择性接近于完整列的选择性——完整列的选择性也可以在另一个计算方式。可以看出，前缀长度达到7后，增加前缀长度，选择性有了很大的提升。选择计数（distinctleft（城市，3））/计数（*）assel3，计数（distinctleft（城市，4））/计数（*）assel4，计数（distinctleft（城市，5））/计数（*）assel5，计数（distinctleft(city,6))/count(*)assel6,count(distinctleft(city,7))/count(*)assel7,count(distinctleft(city,8))/count(*)assel8fromcitydemo;--之后计算完成，可以创建前缀索引altertablecitydemoaddkey(city(7));--注意:前缀索引是使索引更小更快的有效方法，但它也有缺点：mysql不能使用前缀索引做orderby和groupby使用索引扫描对mysql进行排序有两种方法可以生成排序结果：通过排序操作或者按索引顺序扫描，如果explain出来的type列的值为index，说明mysql使用索引扫描来排序，扫描索引本身是非常快的，因为它只需要从一条索引记录移动到下一条记录.但是如果索引不能覆盖查询需要的所有列，那么每次扫描一条索引记录都得回表查询对应的行，基本上是随机IO，所以索引中读取数据的速度order通常比Sequentialfulltablescan慢mysql可以使用相同的索引来满足排序和查找行。如果可能，索引设计应尽可能满足这两个任务。只有当索引的列顺序与orderby子句的顺序完全一致，并且所有列的排序方式相同时，mysql才能使用索引对结果进行排序。如果查询需要关联多个表，只有当orderby子句引用的字段都在第一个表中时，才可以使用索引进行排序。orderby子句与搜索查询有相同的限制，需要满足索引最左前缀的要求。否则MySQL需要顺序操作，不能使用索引排序。Unionall,in,or都可以使用索引，但是建议使用in。范围列可以使用索引。范围条件是：<、>。范围列可以使用索引，但是范围列后面的列不能使用索引。索引最多可用于一个范围列。强制类型转换会扫描全表createtableuser(idint,namevarchar(10),phonevarchar(11));altertableuseraddindexidx_1(phone);explainselect*fromuserwherephone=13800001234;(不会触发index)explainselect*fromuserwherephone='13800001234';(triggerindex)更新非常频繁，数据区分度低的字段不适合建立索引。更新会改变B+树。为频繁更新的字段建议索引会大大降低数据库的性能。与性别等不可区分的属性类似，索引是没有意义的，不能对数据进行有效过滤。一般情况下，当区分度在80%以上时，才能建立索引。可以使用count(distinct(列名))/count(*)计算列来创建索引，从而计算区分度。不允许为null，结果可能达不到预期结果，当需要join表的时候，最好不要超过三个表，因为需要join的字段必须是相同的数据类型。能用limit的时候，尽量用limit。建议单个索引字段数控制在5个以内。允许超过5个（复合索引）创建索引时要避免以下误区>索引越多越好（误区）>过早优化，不了解系统就优化（错误）
