mysql索引优化

索引优化心得
1.尽量使用单表查询
2.or和in使用的同一个字段尽量使用in(最多1000个)
3.如果SQL中where条件不同字段使用or需要对字段分别添加独立索引，否则索引不生效
4.使用explain查看SQL执行计划结果（重点关注 select_type,type）
    4.1 explain结果中select_type：SIMPLE最好
        4.2 explain结果中type：all,index,range,ref,eq_ref,const。从左到右，它们的效率依次是增强的
        type:表示表的连接类型
        ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
        index: Full Index Scan，index与ALL区别为index类型只遍历索引树
        range:只检索给定范围的行，使用一个索引来选择行
        ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
        eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
        const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
        NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
   4.3 explain结果中key_len:索引字段的长度
    4.4 explain结果中ref:
    4.5 explain结果中rows:扫描出的行数(估算的行数 越少越好 过多考虑索引是否合适)
    4.6 explain结果中filtered:按表条件过滤的行百分比(越少越好)

   4.7 explain结果中Extra:（性能从好到坏:useing index>usinh where > using temporary | using filesort）

• using index：只用到索引,可以避免访问表.

• using where：使用到where来过虑数据. 不是所有的where clause都要显示using where. 如以=方式访问索引.

• using tmporary：用到临时表

• using filesort：用到额外的排序. (当使用order by v1,而没用到索引时,就会使用额外的排序)

• range checked for eache record(index map:N)：没有好的索引.

实战：
创建一张表index_test
CREATE TABLE `index_test` (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `uuid` varchar(64) NOT NULL COMMENT 'uuid(orderId_skuId)',
  `order_id` bigint(20) NOT NULL COMMENT '订单号',
  `order_amount` bigint(20) NOT NULL COMMENT '订单金额',
  `merchant_code` varchar(32) NOT NULL COMMENT '商家码',
  `created` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_uuid` (`uuid`),
  KEY `index_order_id` (`order_id`),
  KEY `uniq_merchant_code_created` (`merchant_code`,`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='索引测试表';

案例分析：

1.什么情况下 explain结果中type：all
    SQL：explain select 1 from index_test where order_amount=1
    分析原因：order_amount字段没有索引只能全表扫描 还有一种情况就是没有where条件也会全表扫描

2.什么情况下 explain结果中type：index
    SQL：explain select 1 from index_test order by order_id 
    分析原因：排序需要遍历索引树

3.什么情况下 explain结果中type：range
    SQL：explain select 1 from index_test where order_id=1
    分析原因：order_id是索引直接会走索引范围内查找

4.什么情况下 explain结果中type：ref
    SQL：explain select 1 from index_test where merchant_code='201911554'
    分析原因：merchant_code走到了组合索引（并且只匹配上了一个） 如果全匹配成功 explain结果中type：range

5.什么情况下 explain结果中type：ref
    SQL：explain select 1 from index_test where uuid='xderfe'
    分析原因：唯一索引(UNIQUE KEY) (如果索引添加非null, explain结果中type：const)

6.什么情况下 explain结果中type：const
    explain select 1 from index_test where id='1'
        分析原因：主键索引(PRIMARY KEY)

注意点
1.不同搜索引擎(ENGINE)优化索引的方式都有些差异。
2.字符编码(CHARSET)最好用utf8防止乱码
3.PRIMARY要特别珍惜，一张表只有一个
  数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引（又叫二级索引）两种。
  在Innodb的聚簇索引在同一个B-Tree中保存了索引列和具体的数据
  “聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。一个表只能有一个聚簇索引，因为在一个表中数据的存放方式只有一种
  聚簇索引的优点
    聚簇索引将索引和数据行保存在同一个B-Tree中，查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率要高。
    聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的，
  聚簇索引的缺点
    聚簇索引的更新代价比较高，如果更新了行的聚簇索引列，就需要将数据移动到相应的位置。这可能因为要插入的页已满而导致“页分裂”。
    插入速度严重依赖于插入顺序，按照主键进行插入的速度是加载数据到Innodb中的最快方式。如果不是按照主键插入，最好在加载完成后使用OPTIMIZE TABLE命令重新组织一下表。
    聚簇索引在插入新行和更新主键时，可能导致“页分裂”问题。
    聚簇索引可能导致全表扫描速度变慢，因为可能需要加载物理上相隔较远的页到内存中（需要耗时的磁盘寻道操作）。
  非聚簇索引
    非聚簇索引，又叫二级索引。二级索引的叶子节点中保存的不是指向行的物理指针，而是行的主键值。当通过二级索引查找行，存储引擎需要在二级索引中找到相应的叶子节点，获得行的主键值，然后使用主键去聚簇索引中查找数据行，这需要两次B-Tree查找

（1）InnoDB的主键采用聚簇索引存储，使用的是B+Tree作为索引结构，但是叶子节点存储的是索引值和数据本身（注意和MyISAM的不同）。
（2）InnoDB的二级索引不使用聚蔟索引，叶子节点存储的是KEY字段加主键值。因此，通过二级索引查询首先查到是主键值，然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。
（3）MyISAM的主键索引和二级索引叶子节点存放的都是列值与行号的组合，叶子节点中保存的是数据的物理地址
（4）MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址
（5）为什么用B+Tree 不是BTree：
B-Tree：如果一次检索需要访问4个节点，数据库系统设计者利用磁盘预读原理，把节点的大小设计为一个页，那读取一个节点只需要一次I/O操作，完成这次检索操作，最多需要3次I/O(根节点常驻内存)。数据记录越小，每个节点存放的数据就越多，树的高度也就越小，I/O操作就少了，检索效率也就上去了。
B+Tree：非叶子节点只存key，大大滴减少了非叶子节点的大小，那么每个节点就可以存放更多的记录，树更矮了，I/O操作更少了。所以B+Tree拥有更好的性能。

4.如果索引字段是varchar 使用数字查找 索引会失效（explain结果中type：all） 但是不影响结果。
5.使用索引字段最好为not null节省一个字节索引长度（explain结果中key_len 这个值仅供参考）
6.组合索引有左匹配原则（如果where条件中索引字段是组合索引右边将会失效）

 

explain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
id:选择标识符
select_type:表示查询的类型。
(1) SIMPLE(简单SELECT，不使用UNION或子查询等)
(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
不损失精确性的情况下，长度越短越好 

ref:列与索引的比较
列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows:扫描出的行数(估算的行数)
估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明