1.索引的作用是一般的应用系统，读写的比例大约是10:1，插入操作和一般的很少有性能问题update操作，遇到最多的也是最容易出问题的还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。在数据量小、访问量小的情况下，mysql访问速度很快，是否加索引对访问影响不大。但是当数据量和访问量激增的时候，你会发现mysql变慢了，甚至变慢了，所以必须要考虑优化sql。为数据库建立正确合理的索引是mysql优化的重要手段。索引的目的是提高查询效率。它可以比作字典。如果我们要查找“mysql”这个词，必须定位到字母m，然后从上到下找到字母y，再找到剩下的sql。如果没有索引，那么你可能要翻遍所有的词才能找到你想要的。除了字典，索引的例子在生活中随处可见，比如火车站的火车时刻表、图书目录等等。它们的原理是一样的，都是通过不断缩小要获取的数据的范围来筛选出最终想要的结果，同时把随机事件变成顺序事件，也就是我们总是使用相同的搜索方式来锁定数据.在创建索引时，需要考虑在SQL查询中会用到哪些列，然后为这些列创建一个或多个索引。事实上，索引也是一张表，保存着主键或索引字段，以及一个将每条记录指向实际表的指针。索引对数据库用户是不可见的，它们仅用于加速查询。数据库搜索引擎使用索引来快速定位记录。INSERT和UPDATE语句在带有索引的表上执行需要更多时间，而SELECT语句执行速度更快。这是因为，在插入或更新时，数据库也需要插入或更新索引值。2、索引的创建和删除索引的类型：UNIQUE（唯一索引）：不能出现相同的值，和NULL值INDEX（普通索引）：允许相同的索引内容PROMARYKEY（主键索引）：不允许相同valuefulltextindex（全文索引）：可以针对value中的某个词，但是效率实在不敢恭维。组合索引：本质上就是多个字段建一个索引，列值的组合必须唯一温馨提示：根据《阿里巴巴Java开发手册》中的mysql协议，唯一索引建议命名为uk_field名称，并且公共索引名称是idx_field名称。（uk_是uniquekey；idx_是index的缩写）。(1)使用ALTERTABLE语句创建一个简单的应用程序，在创建表后添加。ALTERTABLE表名ADD索引类型(unique,primarykey,fulltext,index)[indexname](fieldname)//commonindexaltertabletable_nameaddindexindex_name(column_list);//唯一索引altertabletable_nameaddunique(column_list);//主键索引altertabletable_nameaddprimarykey(column_list);ALTERTABLE可用于创建三种索引格式：普通索引、UNIQUE索引和PRIMARYKEY索引。table_name是要加入索引的表名，column_list表示索引了哪些列。当有多个列时，列之间用逗号分隔。索引名称index_name是可选的。默认情况下，MySQL会根据第一个索引列分配一个名称。此外，ALTERTABLE允许在单个语句中更改多个表，因此可以同时创建多个索引。(2)使用CREATEINDEX语句给表添加索引CREATEINDEX可以用来给表添加普通索引或UNIQUE索引，也可以用来在创建表时创建索引。CREATEINDEXindex_nameONtable_name(用户名(长度));如果是CHAR、VARCHAR类型，长度可以小于字段的实际长度；如果是BLOB和TEXT类型，必须指定长度。//只能添加这两种类型的索引；CREATEINDEXindex_nameONtable_name(column_list)CREATEUNIQUEINDEXindex_nameONtable_name(column_list)table_name,index_name,column_list与ALTERTABLE语句中的含义相同，索引名不可选。此外，不能使用CREATEINDEX语句创建PRIMARYKEY索引。(3)删除索引删除索引可以使用ALTERTABLE或DROPINDEX语句来实现。DROPINDEX可以作为ALTERTABLE内部的一条语句来处理，其格式如下：dropindexindex_nameontable_name;altertabletable_namedropindexindex_name;altertabletable_namedropprimarykey;其中，在前面两条语句中，删除了table_name中的索引index_name。最后一条语句只用于删除PRIMARYKEY索引，因为一张表可能只有一个PRIMARYKEY索引，所以不需要指定索引名。如果没有创建PRIMARYKEY索引，但表有一个或多个UNIQUE索引，MySQL会删除第一个UNIQUE索引。如果从表中删除列，索引会受到影响。对于多列索引，如果其中一列被删除，该列也会从索引中删除。如果删除构成索引的所有列，则整个索引将被删除。(4)复合索引和前缀索引这里需要指出的是，复合索引和前缀索引是对索引技术的称呼，而不是索引的类型。为了更清楚，创建一个演示表如下。createtableUSER_DEMO(IDintnotnullauto_incrementcomment'主键',LOGIN_NAMEvarchar(100)notnullcomment'登录名',PASSWORDvarchar(100)notnullcomment'密码',CITYvarchar(30)notnullcomment'城市',AGEintnotnullcomment'年龄',SEXintnotnullcomment'性别(0:女1:男性）'，主键（ID））;为了进一步压榨mysql的效率，可以考虑构建复合索引，即将LOGIN_NAME,CITY,AGE构建成一个索引：ALTERTABLEUSER_DEMOADDINDEXname_city_age(LOGIN_NAME(16),CITY,AGE);建表时LOGIN_NAME的长度为100，这里使用16是因为name的长度一般不会超过16，这样可以加快索引查询速度，减小索引文件的大??小，提高INSERT和UPDATE的更新速度。如果分别为LOGIN_NAME、CITY、AGE创建单列索引，让表有3个单列索引，查询效率和复合索引的效率是天壤之别，甚至远低于我们的复合索引。此时虽然有三个索引，但是mysql只能使用其中一个，貌似是单列索引效率最高的，另外两个没有用，也就是说还是全表的过程扫描。建立这样一个复合索引相当于创建了以下三个复合索引：LOGIN_NAME,CITY,AGELOGIN_NAME,CITYLOGIN_NAME为什么没有CITY,AGE等复合索引呢？这是因为mysql复合索引的“最左前缀”的结果。简单理解就是只从最左边开始组合，并不是所有包含这三列的查询都会使用组合索引。也就是说，name_city_age(LOGIN_NAME(16),CITY,AGE)是从左到右索引的。如果没有左前索引，mysql就不会进行索引查询。如果索引列的长度过长，这种列索引会生成一个很大的索引文件，不便于操作。您可以使用前缀索引方法进行索引。前缀指数要控制在一个合适的点位，即0.31金价。（大于这个值可以创建）。SELECTCOUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*)FROMArctic;--这个值大于0.31创建前缀索引，Distinct重复ALTERTABLE`user`ADDINDEX`uname`(title(10));--增加前缀索引SQL，将name的索引设置为10，可以减小索引文件的大??小，加快索引查询速度3.索引的使用及注意事项EXPLAIN可以帮助开发者分析SQL问题。索引用于处理select语句和连接表，可以帮助选择更好的索引和编写更优化的查询语句。使用方法，在select语句前加上Explain即可：Explainselect*fromuserwhereid=1;尽量避免这些不使用索引的sql:SELECTname,phoneFROM`user`WHERE`age`+10=30;--不会使用索引，因为所有索引列都参与了SELECTname,phoneFROM`user的计算`WHERELEFT(`date`,4)<1990;--不会使用索引，因为使用了函数操作，原理同上SELECT*FROM`user`WHERE`name`LIKE'backing%'--goindexSELECT*FROM`user`WHERE`name`LIKE"%backing%"--donotgoindex--正则表达式不使用索引，这个应该很好理解，所以为什么在SQL中很难看出regexp关键字的原因--字符串的比较和数字不使用索引；-取索引EXPLAINSELECT*FROM`a`WHERE`a`=1--不去索引select*fromdeptwhereedname='xxx'orloc='xx'ordeptno=45--如果条件中有or，即使有条件带索引也不会用。换句话说，所有需要使用的字段都必须被索引。我们建议您尽量避免使用or关键字——如果mysql估计使用全表扫描比使用索引快，就不要使用索引。使用索引可能会带来相反的问题，索引也有缺点：虽然索引大大提高了查询速度，但是会降低更新表的速度，比如对表进行INSERT、UPDATE、DELETE。因为在更新表的时候，mysql不仅保存了数据，还保存了索引文件。索引索引文件会占用磁盘空间。一般情况下，这个问题不是太严重，但是如果要在大表上建立各种组合索引，索引文件会膨胀的很宽。索引只是提高效率的一种方式。时间研究建立良好的索引，或优化查询语句。在使用索引时，有一些技巧是索引不会包含NULL列，只要列包含NULL值，它就不会被索引。只要复合索引中有一个列包含NULL值，那么这个列就适合这个索引。它只是行不通。2.使用短索引对串行列进行索引，如果可能，应该指定一个前缀长度。例如，如果您有一个char(255)列，如果大多数值在前10或20个字符内是唯一的，则不要索引整个列。短索引不仅可以提高查询速度，还可以节省磁盘空间和I/O操作。3、索引列排序MySQL一张表查询只能使用一个索引。因此，如果where子句中已经使用了索引，则orderby中的列将不会使用该索引。因此，如果数据库默认的排序可以满足要求，就不要使用排序操作，尽量不要包含多列的排序。如果有必要，最好为这些列建立一个复合索引。这一点很容易被很多程序员忽略。比如where子句的字段已经建立了索引，排序的字段也已经建立了索引，但是是分开建的，以为会用到索引。事实上，排序的字段不会使用索引。除非您构建复合索引，否则请记住。4.like语句操作一般不鼓励使用like操作。如果一定要用，注意正确的使用方法。like'%aaa%'不会使用索引，但是like'aaa%'可以使用索引。5、不要对列进行操作6、不要使用NOTIN、<>、!=操作，但是<、<=、=、>、>=、BETWEEN、IN可以使用索引。7.索引应该建立在经常被选择的字段上。这是因为，如果这些列很少使用，索引的存在与否不会显着改变查询速度。相反，由于索引的增加，系统的维护速度降低，空间需求增加。8.索引应该建立在具有唯一值的字段上。9、对于定义为text、image、bit数据类型的列，不需要添加索引。因为这些列中的数据量要么很大，要么取的值很少。10、where和join中出现的列需要建立索引。11、如果where的查询条件中有不等号（wherecolumn!=...），mysql将无法使用索引。12、如果在where子句的查询条件中使用了函数（如：whereDAY(column)=...），mysql将无法使用索引。13、在join操作中（需要从多个数据表中提取数据时），mysql只有在主键和外键的数据类型相同时才能使用索引，否则即使指数成立。这一点很容易被忽略，记住，记住，记住！14、联表查询时，关联表的字段类型最好相同，长度相同，这样索引才能更好的发挥作用。15.组合索引时记住这个约束：组合索引中有多个字段，其中一个字段有范围判断，所以这个字段需要放在最后。例如，ALTERTABLEUSER_DEMOADDINDEXname_age(NAME,AGE)；因为age是有范围判断的，所以在构建复合索引的时候把AGE字段放在后面。16、字符集字段比对，UTF8和UTF-BIN联合查询无法索引。比如一张表的order_no字段类型是varchar(50)，另一张表的order_no字段类型是varchar(50)COLLATEutf8_BIN。这时候联合查询不能使用索引，切记。即两个表的字段类型如下：`order_no`varchar(50)COLLATEutf8_binNOTNULLDEFAULT''COMMENT'订单号';`order_no`varchar(50)NOTNULLDEFAULT''COMMENT'订单号';17、以下情况不适合建立索引：表记录太少、频繁插入、删除、修改表数据重复、表字段分布均匀。比如一个表有10万行记录，字段column1只有A和B两个值，每个值的分布概率大约是50%，那么索引这个表的column1字段一般不会提高数据库的查询速度。18.为表创建一个主键。对于没有主键的表，对查询和索引定义有一定的影响。19、为避免表字段为null，建议设置默认值（例如int类型设置默认值为0），这样索引查询的效率会高很多。20、重点关注orderby的索引：如果无条件查询中只有orderbycreate_time，即使create_time上有索引也不会被使用。因为优化器认为使用二级索引再回表的成本高于全表扫描排序，所以选择使用token扫描。无条件查询但orderbycreate_time限制m，如果m值较小，可以使用索引。因为优化器认为按照索引的顺序回表查数据就可以终止循环，然后得到m条数据，所以成本比全表扫描要低，所以它选择使用二级索引。即使没有二级索引，mysql也对orderbylimit进行了优化，使用的是堆排序。orderbysorting分为文件排序和索引，索引效率更高。但是以下情况不会使用索引排序：查询的行太多，没有使用多个索引，mysql一次只使用一个索引whereandorderby使用不同的索引，类似于前面的orderbyA非索引列添加到中，非索引列不在where中。使用leftjoin时，使用右边的表字段进行排序
