前言索引相信大家都听说过，但是真正懂得使用的又有多少呢？平时工作写SQL的时候，他们真正考虑的是这个SQL如何使用索引，如何提高执行效率？这篇文章详细介绍了索引优化的几个原则。只要你能在工作中随时应用它们，相信你写的SQL一定是最高效最强大的。文章脑图如下：索引优化规则1.like语句的前导模糊查询不能使用索引select*fromdocwheretitlelike'%XX';--不能使用索引select*fromdocwheretitlelike'XX%';--可使用非前导模糊查询严禁对页面搜索进行左模糊或全模糊索引，必要时可使用搜索引擎解决。2.union,in,orcan都命中指标。推荐使用inunion打索引，MySQL占用CPU最少。select*fromdocwherestatus=1unionallselect*fromdocwherestatus=2;in可以命中索引，查询优化比unionall更耗cpu，但是可以忽略。一般情况下，推荐使用in.select*fromdocwherestatusin(1,2);或者新版本的MySQL可以命中索引，查询优化比in更耗CPU，不推荐频繁使用or。select*fromdocwherestatus=1orstatus=2补充：有的地方说如果在where条件中使用or，索引就会失效，导致全表扫描。这是一个误解：①where子句中使用的所有字段都必须被索引；②如果数据量太小。mysql在制定执行计划时发现全表扫描比索引查找速度快，所以不会使用索引；③确保mysql版本为5.0以上，查询优化器有index_merge_union=on，即变量optimizer_switch中有index_merge_union并且是on。3、索引不能用于否定条件查询否定条件包括：!=、<>、notin、notexists、notlike等。例如下面的SQL语句：select*fromdocwherestatus!=1andstatus!=2;可以像在查询中一样进行优化：select*fromdocwherestatusin(0,3,4);4.如果联合索引最左前缀的原则是在(a,b,c)三个字段上创建联合索引，那么他会自动创建一个|(a,b)|(a,b,c)组索引。对于登录业务需求，SQL语句如下：selectuid,login_timefromuserwherelogin_name=?andpasswd=?可以建立(login_name,passwd)的联合索引。因为业务中几乎没有passwd的单条件查询需求，但是login_name的单条件查询需求很多，所以可以构建(login_name,passwd)的联合索引，而不是(passwd,login_name)。建立联合索引时，区分度最高的字段在最左边。当存在非等号和等号混合判断条件时，在建索引时，将等号条件的列放在前面。例如，a>?而b=?，那么即使a的辨别度更高，b也一定要放在指标的最前面。使用最左前缀查询时，并不意味着SQL语句的where顺序必须与联合索引保持一致。下面的SQL语句也可以命中(login_name,passwd)联合索引：selectuid,login_timefromuserwherepasswd=?andlogin_name=?不过建议where后面的顺序和联合索引一致，养成好习惯。如果index(a,b,c)，其中a=3，b如'abc%'，c=4，a可以用，b可以用，c不能用。5、索引中范围条件右边的列不能使用（范围列可以使用索引），范围列之后的列的索引全部无效。范围条件包括：<、<=、>、>=、between等。一个索引最多只能用于一个范围列。如果查询条件中有两个范围列，则无法充分利用索引。如果有联合索引（empno、title、fromdate），那么在下面的SQL中，emp_no可以使用索引，title和from_date不能使用索引。select*fromemployees.titleswhereemp_no<10010'andtitle='SeniorEngineer'andfrom_datebetween'1986-01-01'and'1986-12-31'6.不要对索引列进行任何操作（计算，函数），否则索引会变得无效转为全表扫描，比如下面的SQL语句，即使在日期建立了索引，也会扫描全表：select*fromdocwhereYEAR(create_time)<='2016';可以优化计算值，如下：select*fromdocwherecreate_time<='2016-01-01';例如，下面的SQL语句：select*fromorderwheredate<=CURDATE();可以优化为：select*fromorderwheredate<='2018-01-2412:00:00';如果表扫描字符串类型不加单引号，索引就会失败，因为mysql会自己做类型转换，相当于对索引列进行操作。如果电话字段的类型为varchar，则以下SQL无法命中索引。select*fromuserwherephone=13800001234可以优化为：select*fromuserwherephone='13800001234';8、不适合为更新非常频繁、数据区分度低的列建索引。更新会改变B+树，为频繁更新的字段建立索引会大大降低数据库性能。“性别”是一个几乎没有歧视的属性。建立索引是没有意义的，不能有效的过滤数据。性能类似于全表扫描。一般当区分度在80%以上时才可以建立索引，区分度可以通过count(distinct(columnname))/count(*)来计算。9、使用覆盖索引进行查询操作，避免回表，减少使用select*覆盖索引：查询的列数与建立索引的列数相同，字段是相同的。对于查询的列，可以通过row-locator从索引中获取数据，而不是从行中获取数据，即“查询的列必须被内置索引覆盖”，这样可以加快查询速度。比如登录业务需求，SQL语句如下。Selectuid,login_timefromuserwherelogin_name=?andpasswd=?可以创建(login_name,passwd,login_time)的联合索引。由于索引中已经建好login_time，所以查询uid和login_time不需要从行中取数据，从而加快了查询速度。10.索引将不包含具有NULL值的列。只要列包含NULL值，它们就不会包含在索引中。只要复合索引中有一列包含NULL值，那么该列对于复合索引就是无效的。所以我们在设计数据库的时候，尽量使用notnull约束和默认值。11.isnull,isnotnull不能使用索引12.如果有orderby和groupby场景，请注意索引的顺序。orderby的最后一个字段是复合索引的一部分，放在索引复合序列的末尾，避免出现file_sort的情况，影响查询性能。例如，对于语句wherea=?和b=?按c排序，可以创建一个联合索引(a,b,c)。如果索引中存在范围搜索，则不能使用索引顺序，如WHEREa>10ORDERBYb;，则无法对索引(a,b)进行排序。13.使用短索引（前缀索引）对列进行索引，如果可能，指定前缀长度。例如有一个CHAR(255)列，如果该列在前10个或20个字符以内，则可以实现前缀索引接近全列索引的判别，所以不索引整个专栏。因为短索引不仅可以提高查询速度，还可以节省磁盘空间和I/O操作，减少索引文件的维护开销。您可以使用count(distinctleftIndex(columnname,indexlength))/count(*)来计算前缀索引的区分度。但缺点是不能用于ORDERBY和GROUPBY操作，也不能用于覆盖索引。但在很多情况下，并不需要对所有字段都进行索引，索引长度可以根据实际的文本区分来确定。14.使用延迟关联或子查询优化超多分页场景。MySQL不会跳过offset行，而是取offset+N行，然后在放弃前返回offset行，返回N行。当偏移量特别大时，效率高。很低，要么控制返回的总页数，要么对超过一定阈值的页进行SQL重写。例子如下，先快速定位到需要获取的id段，然后关联：selecta.*fromtable1a,(selectidfromtable1whereconditionlimit100000,20)bwherea.id=b.id;15.如果知道只返回一个结果，limit1可以提高效率。例如，下面的SQL语句：select*fromuserwherelogin_name=?;可以优化为：select*fromuserwherelogin_name=?limit1我知道只有一个结果，但是数据库不知道。明确告诉它让它停止光标移动。16.最好不要连接三个以上的表。需要连接的字段必须是相同的数据类型。多表关联查询时，保证关联字段需要有索引。比如：leftjoin是由左边决定的，左边的数据必须是可用的，所以右边是我们的重点，必须在右边建索引。当然，如果索引在左边，可以使用rightjoin。17、建议单表索引控制在5个以内。18、SQL性能优化中的type说明：至少要达到range级别，ref级别是必须的。如果可以是const，最好是const：单张表（主键或唯一索引）最多匹配一行，优化阶段可以读取到数据。ref：使用普通索引（NormalIndex）。range：对索引执行范围检索。当type=index时，扫描所有索引物理文件，速度很慢。19、对于业务中具有唯一性的字段，即使是多个字段的组合，也必须建立唯一性索引。不要认为唯一索引会影响插入速度。这个速度损失可以忽略不计，但是对搜索速度的提升是很明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，按照墨菲定律，还是不可避免的会产生脏数据。20、创建索引时避免以下误解索引越多越好，认为需要为一个查询建立索引。宁短勿乱，认为索引会消耗空间，严重拖慢更新和添加的速度。抵制唯一性索引，认为业务的唯一性需要在应用层通过“先查找后插入”来解决。过早的优化，在不了解系统的情况下开始。索引选择性和前缀索引既然索引可以加快查询速度，那么是不是只要查询语句需要就建索引呢？答案是不。虽然索引加快了查询速度，但是索引也有代价：索引文件本身会消耗存储空间，索引会增加插入、删除、修改记录的负担。另外，MySQL在运行时也会消耗资源来维护索引。所以索引并不是越多越好。一般来说，有两种情况不建议建立索引。第一种情况是表记录比较少，比如一张表有一两千条甚至只有几百条记录，不需要建索引，让查询做全表就可以了扫描。至于记录多少算多，这个人有自己的看法。我个人的经验是以2000为分界线。如果记录数不超过2000条，可以考虑不建索引，如果记录数超过2000条，可以考虑酌情建立索引。另一种不建议建立索引的情况是当索引的选择性较低时。所谓索引选择性（Selectivity）是指不重复的索引值（也称基数，Cardinality）与表记录数（#T）的比值：IndexSelectivity=Cardinality/#T显然，取值范围selectivity为(0,1]``，selectivity越高，index的值越大，这是由B+Tree的性质决定的。比如employees.titles表中，如果title字段为经常单独查询，我们需要建立索引，看一下它的选择性：SELECTcount(DISTINCT(title))/count(*)ASSelectivityFROMemployees.titles;+------------+|Selectivity|+----------------+|0.0000|+------------+标题的选择性小于0.0001（准确值为0.00001579），所以真的没必要为它单独建一个索引，有一种与索引选择性相关的索引优化策略叫做前缀索引，就是使用列的前缀而不是整个列作为索引键。当the前缀长度合适，前缀索引的选择性可以接近全列索引。它很短，减少了索引文件的大??小和维护开销。下面以employees.employees表为例，介绍前缀索引的选择和使用。假设employees表只有一个索引，如果我们要按名字查找一个人，只能扫描全表：EXPLAINSELECT*FROMemployees.employeesWHEREfirst_name='Eric'ANDlast_name='Anido';+----+--------------+------------+------+--------------+------+----------+------+------+------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+------------+------------+------+----------------+------+--------+------+------+------------+|1|SIMPLE|employees|ALL|NULL|NULL|NULL|NULL|300024|Usingwhere|+----+-----------+------------+------+----------------+------+--------+-----+--------+------------+如果你经常按名字搜索员工，这显然是效率低下，可以考虑索引。有两个选项，buildorSELECTcount(DISTINCT(first_name))/count(*)ASSelectivityFROMemployees.employees;+------------+|Selectivity|+-------------+|0.0042|+------------+SELECTcount(DISTINCT(concat(first_name,last_name)))/count(*)ASSelectivityFROMemployees.employees;+-----------+|Selectivity|+------------+|0.9313|+------------+
