所谓索引就是对特定的mysql字段进行一些特定的算法排序，比如二叉树算法，哈希算法。哈希算法就是通过建立特征值来快速搜索。用的最多，也是mysql默认的二叉树算法BTREE。BTREE算法索引的字段，比如扫描20行，在使用BTREE之前可以得到扫描2^20行的结果。具体的实现方法会在本博客的一个算法专题中进行具体的分析和讨论；ExplainoptimizedquerydetectionEXPLAIN可以帮助开发人员分析SQL问题，explain展示了mysql如何使用索引来处理select语句和join表，可以帮助选择更好的索引，编写出更优化的查询语句。使用方法是在select语句前加上Explain：Explainselect*fromblogwherefalse;在执行一个查询之前，mysql会分析每条发出的sql，决定是使用索引还是全表扫描，如果你发送一个select*fromblogwherefalsemysql就不会执行查询操作，因为经过sql分析器的分析，mysql已经知道不会有匹配操作的语句；examplemysql>EXPLAINSELECT`birthday`FROM`user`WHERE`birthday`<"1990/2/2";--result:id:1select_type:SIMPLE--查询类型（简单查询、联合查询、子查询）table:user--显示这一行的数据是关于哪个表表的类型：range--区间索引（区间小于1990/2/2的数据），这是一个重要的列，显示使用的是什么类型的连接。从***到worst的连接类型是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上是不准确的。Extra:Usingwhere;Usingindex--执行状态描述，这里可以看到的坏例子有Usingtemporary和Usingselect_typesimplesimpleselect(不要使用union或subquery)primarytheoutermostselectunionthesecondorsubsequentselectinunionThesecondorsubsequentselectstatementdependentunionunion中的statementdependentunion依赖于外查询unionresultunion的结果。子查询中的第一个selectsubquery依赖子查询中的第一个select依赖于外层查询derived派生表的select（from子句的子查询）Extraandtype详细说明Distinct:OnceMYSQLfindsOnceitfindsarowthatmatchesLEFTJOIN条件，不再搜索Notexists：MYSQL优化LEFTJOIN，一旦找到匹配LEFTJOIN条件的行，它不再搜索RangecheckedforeachRecord(indexmap:#):Noidealindex找到了，因此对于前一个表中行的每个组合，MYSQL检查使用哪个索引并使用它从表中返回行。这是使用索引最慢的连接之一使用文件排序：当您看到这个时，需要优化查询。MYSQL需要做一个额外的步骤来发现如何对返回的行进行排序。它根据连接类型和存储排序键的行指针以及与条件匹配的所有行对所有行进行排序。使用索引：列数据从只使用索引中的信息而不读取实际操作的表中返回，这发生在使用临时表时，当表的所有请求列都是同一索引的一部分时，查询需要进行优化。这里MYSQL需要创建一个临时表来存储结果，这通常发生在不同列集的ORDERBY上，而不是在GROUPBY上。用户。如果不想返回表中的所有行，并且连接类型是ALL或者索引，就会出现这种情况，或者查询有问题不同连接类型的解释（按效率顺序排序系统表只有一行：系统表，这是一个const连接const类型的特例：表中一条记录的最大值可以匹配到这个查询（索引可以是主键也可以是唯一索引），由于有只有一行，这个值其实是一个常量，因为MYSQL先读取这个值，并把它当作常量来对待eq_ref：在连接中，MYSQL在查询的时候，从上一张表的每一个recordunion，从表中读取一条记录，即当查询使用索引作为主键或所有唯一键时使用ref：此连接类型仅在查询使用非唯一键或主键或这些类型的一部分时发生（例如，使用最左边的前缀）.对于上表的每个行并集，所有记录将从Read表中检索。这种类型在很大程度上取决于有多少记录与索引匹配-越少越好+范围：这种连接类型使用索引返回范围内的行，例如使用>或<查找某些东西时会发生什么+索引：这个join类型对上表中的每条记录并集进行全扫描（优于ALL，因为索引一般小于表数据）+ALL：该连接类型对上表中的每条记录并集进行全扫描，一般情况下比较差，应该尽量避免这种类型：如果是Onlyindex，表示只从索引树中的信息中获取信息，比扫描全表要快。如果是whereused，说明使用了wherelimit。如果whereisimpossiblewhere表示Thereneedforwhere，一般什么都查不出来。如果这个信息显示Usingfilesort或者Usingtemporary，那就很难了。WHERE和ORDERBY索引往往不能同时考虑。如果索引是根据WHERE确定的，那么在ORDERBY的时候，必然会导致Usingfilesort，这要看是先过滤再排序，还是先排序再过滤，才划算。#p#索引类型UNIQUE唯一索引不能有相同的值，可以有NULL值可以有一个primary_key索引fulltextindex全文索引以上三个索引都是作用于列的值，但是全文索引可以针对值中的某个词，比如一篇文章中的某个词，但是没有egg的使用，因为只支持myisam和英文，效率不敢恭维，但是可以使用coreseek、xunsearch等第三方应用来完成这个需要索引的CURD索引的创建。ALTERTABLE适用于在创建表后添加ALTERTABLE表。nameADDindextype(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,--注意，Index下面创建主键，不需要创建`username`varchar(64)NOTNULLCOMMENT'用户名',`昵称`varchar(50)NOTNULLCOMMENT'昵称/姓名',`intro`text,PRIMARYKEY(`id`),UNIQUEKEY`unique1`(`username`),--索引名称，可选与否，不能只与列名相同KEY`index1`(`nickname`),FULLTEXTKEY`intro`(`intro`))ENGINE=MyISAMAUTO_INCREMENT=4DEFAULTCHARSET=utf8COMMENT='后台用户表';索引删除DROPINDEX`index_name`ON`talbe_name`ALTERTABLE`table_name`DROPINDEX`index_name`--这两句是等价的，都是删除table_name中的索引index_name;ALTERTABLE`table_name`DROPPRIMARYKEY--删除主键索引，注意只有这样才能删除主键索引。Viewshowindexfromtablename\G;索引变化是一个线程，删除和重建一个就可以#p#创建索引的技巧1.高维列创建索引数据列中唯一值的个数。数字越大，维度越高。例如数据表中有8行数据a,b,c,d,a,b，表c和d的维度为4，为性别、年龄等维度高的列创建索引，年龄维度高于性别维度。此类列不适合做索引，因为维度太低2.对于where,on,groupby,orderby中出现的列使用索引3.对较小的数据列使用索引，会使索引文件变小，at同时，可以在内存中加载更多的索引键4.对于较长的字符串使用前缀索引5.不要创建太多的索引。除了增加额外的磁盘空间外，对DML操作的速度也有很大的影响，因为每次增删改查都必须重新构建。6.使用组合索引可以减少文件索引大小，使用时速度优于多个单列索引组合索引和前缀索引注意这两个名称是索引技术的一种名称，不是索引的类型;MySQL单列索引和复合索引的复合索引有什么区别？为了直观对比两者，首先创建一个表：CREATETABLE`myIndex`(`i_testID`INTNOTNULLAUTO_INCREMENT,`vc_Name`VARCHAR(50)NOTNULL,`vc_City`VARCHAR(50)NOTNULL,`i_Age`INTNOTNULL,`i_SchoolID`INTNONULL,PRIMARYKEY(`i_testID`));假设表中有1000条记录，在这10000条记录中，vc_Name="erquan"的5条记录分布在7上8下，但是城市年龄学校的组合看这个T-SQL：SELECT`i_testID`FROM`myIndex`WHERE`vc_Name`='二泉'AND`vc_City`='郑州'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个单列索引，查询效率和上面说的组合索引一样，答案却大相径庭，much低于我们的综合指数。此时虽然有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.31（大于这个值可以创建）SELECTCOUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*)来自北极；--如果值大于0.31，可以创建前缀索引，Distinct重复ALTERTABLE`user`ADDINDEX`uname`(title(10));--增加前缀索引SQL，在10处建立name的索引，可以减小索引文件大小，加快索引查询#p#什么样的sql不使用索引，尽量避免这些sqlSELECT`sname`FROM`stu`WHERE`age`+10=30;--不会使用索引，因为所有索引列都参与SELECT`sname`FROM`stu`WHERELEFT(`date`,4)<1990;的计算；--不会使用索引，因为使用了函数操作，原理同上`uname`LIKE"%backing%"--donotusetheindex--正则表达式不使用索引，这个应该很好理解，那为什么在很难看出SQL中regexp关键字的原因——字符串和数字的比较没有使用索引；-取索引EXPLAINSELECT*FROM`a`WHERE`a`=1--不去索引select*fromdeptwhereedname='xxx'orloc='xx'ordeptno=45--如果条件中有or，即使有一个带索引的条件，也就是说，所有需要使用的字段都必须被索引。建议尽量避免使用or关键字--如果mysql估计使用全表扫描比使用索引快，多表关联时不要使用索引索引效率SELECT`sname`FROM`stu`W??HERELEFT(`日期`,4)<1990;--不会使用索引，因为使用了函数操作，原理同上SELECT*FROM`houdunwang`WHERE`uname`LIKE'Backing%'--去索引SELECT*FROM`houdunwang`WHERE`uname`LIKE"%backing%"--不去索引从上图可以看出，所有表的类型都是all，表示全表索引；即666，一共遍历查询了216次；除了整个表的索引（必填，必须与其他表关联），其余都是范围（从索引区间获取），即6+1+1+1。总共9次遍历查询足够的;因此，我们建议您在连接多个表时，连接的表越少越好，因为如果您不小心，这将是一次可怕的笛卡尔积扫描。另外，我们也建议尽量使用leftjoin，避免Lessassociations等。因为如果用join的话，第一个表肯定是全扫描的，关联少了就可以??减少扫描的次数。索引的缺点是不能盲目创建索引，只为查询操作频繁的列创建索引，创建索引会使查询操作更快，但会降低增删改查操作的速度，因为索引文件在执行这些操作时将被重新排序或更新；但是在互联网应用中，比DML大很多的查询语句Statements甚至可以占到80%~90%，所以不要太在意，但是在导入大数据的时候，可以先删除索引，然后批量插入数据，最后添加索引；
