mysql 索引及查询优化总结

背景：总结mysql索引相关的知识点.

MySQL 索引及查询优化总结

MySQL 索引及查询优化总结

ps：其中的b+树有误。对or的使用有误。总体还是很详尽的

注意：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换。MEMORY/HEAP存储引擎：支持HASH和BTREE索引。

1、索引我们分为四类来讲单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引、

mysql索引类型：

1. 主键索引 primary key 不允许null
2. 唯一索引  union key  可以为null
3. 普通索引 index
4. 联合索引
5. 全文索引

建立索引的原则：

1.  最左匹配原则 遇到范围查询(>、<、between、like)就停止匹配。 则mysql实际建的索引为：(a) (a,b) (a,b,c) (a,b,c,d)
2. 尽量选择区分度高的列作为索引
3. =和in可以乱序
4. 索引列不能参与计算，保持列“干净”。 假如索引列参与计算的话，那每次检索时，都会先将索引计算一次，再做比较，显然成本太大。
5. 尽量的扩展索引，不要新建索引

补充：尽量扩展索引、不要新建索引 mysql目前主要索引有：FULLTEXT,HASH,BTREE 好的索引可以提高我们的查询效率，不好的索引不但不会起作用，反而给DB带来负担，基于BTREE结构，插入、修改都会重新调整索引结构，存储成本增加，写效率降低，同时DB系统也要消耗资源去维护。 基于刚才的最左匹配原则，尽量在原有基础上扩展索引，不要新增索引。 能用单索引，不用联合索引；能用窄索引，不用宽索引；能复用索引，不新建索引。 回到线上案例： nc_tms_order、ct_order看看分别有哪些索引

索引的不足：空间，时间，开销

常用优化总结：

1. 有索引但未被用到的情况（不建议） (1) Like的参数以通配符%开头时 (2) where条件不符合最左前缀原则时 (3) 使用！= 或 <> 操作符时（使用>或<会比较高效。）(4) 索引列参与计算 (5) 对字段进行null值判断 (6) 使用or来连接条件（两端都有索引才行）

2. 避免select *  在解析的过程中，会将'*' 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

3. order by 语句优化  1）重写order by语句以使用索引；2）为所使用的列建立另外一个索引 3）避免在order by子句中使用表达式

4. 提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉

5. 使用 varchar/nvarchar 代替 char/nchar

6. 在Join表的时候使用相当类型的例，并将其索引

7. 优化嵌套查询：子查询可以被更有效率的连接(Join)替代；
8. 很多时候用 exists 代替 in 是一个好的选择

mysql索引的分类

 MySQL有哪些索引类型 ?

MySQL索引背后的数据结构及算法原理（讲的很透彻）

MySQL索引的分类（根据数据结构）（明白具体的分类）

理解mysql搜索引的实现：

MyISAM索引实现：

MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

InnoDB索引实现：

MyISAM索引和InnoDB索引的不同：

第一个重大区别是InnoDB的数据文件本身就是索引文件。innodb 聚集索引data域存放的是数据的记录，mylsam的data域存储的是记录的地址

从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。mylsam辅助索引data域存储的是地址。

exists和in的区别？