mysql 索引（一）

观察服务器周期性波动

缓存 or sql (io wait or execute) --> buy machine  or repeat

加缓存或分散缓存失效，如果仍有延迟，

通过show processlist or slow query找出慢sql，profiling+explain(产生查询，5.6支持其他非查询，列的解释)，

找出io等待长（缓冲区） or  执行时间长（取了多少行和扫描多少行    ：多表关联 索引 语句）=》切分或是分解 | 语句优化（count group by limit  union min/max  关联查询（子查询（in exists from））），

如果仍没解决就看看是不是达到了基准测试瓶颈，如果是的话就加机器，不是的话就重来一遍上面的

               

索引类型：Btree（全值 左前缀 范围 聚族索引 ） Hash（不常用）

表类型:innodb（主键） mysiam （物理行）

高性能索引策略：

列（长度和区分度）

多列（顺序 长度和频率）

聚族索引（顺序 随机 和页分裂）

索引覆盖（覆盖和回行  innodb二级索引和索引覆盖 延迟关联）

索引排序（2种排序方式 索引覆盖和排序  列顺序和排序）

荣誉索引（重复索引和冗余索引）

索引维护：行碎片  行间碎片 剩余空间碎片

假设某个表有一个联合索引（c1,c2,c3,c4）一下——只能使用该联合索引的c1,c2,c3部分

A where c1=x and c2=x and c4>x and c3=x

B where c1=x and c2=x and c4=x order by c3

C where c1=x and c4= x group by c3,c2

D where c1=x and c5=x order by c2,c3

E where c1=x and c2=x and c5=? order by c2,c3

 

create table t4 (

c1 tinyint(1) not null default 0,

c2 tinyint(1) not null default 0,

c3 tinyint(1) not null default 0,

c4 tinyint(1) not null default 0,

c5 tinyint(1) not null default 0,
index c1234(c1,c2,c3,c4)

);

insert into t4 values (1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);

 

对于A:

c1=x and c2=x and c4>x and c3=x  <==等价==> c1=x and c2=x and c3=x and c4>x

因此 c1,c2,c3,c4都能用上. 如下:

mysql> explain select * from t4 where c1=1 and c2=2 and c4>3 and c3=3 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t4

         type: range

possible_keys: c1234

          key: c1234

      key_len: 4 #可以看出c1,c2,c3,c4索引都用上

          ref: NULL

         rows: 1

        Extra: Using where

 

对于B: select * from t4 where c1=1 and c2=2 and c4=3 order by c3

c1 ,c2索引用上了,在c2用到索引的基础上,c3是排好序的,因此不用额外排序.

而c4没发挥作用.

mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order by c3 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t4

         type: ref

possible_keys: c1234

          key: c1234

      key_len: 2

          ref: const,const

         rows: 1

        Extra: Using where

1 row in set (0.00 sec)

 

mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order by c5 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t4

         type: ref

possible_keys: c1234

          key: c1234

      key_len: 2

          ref: const,const

         rows: 1

        Extra: Using where; Using filesort

1 row in set (0.00 sec)

 

对于 C: 只用到c1索引,因为group by c3,c2的顺序无法利用c2,c3索引

mysql> explain select * from t4 where c1=1 and c4=2 group by c3,c2 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t4

         type: ref

possible_keys: c1234

          key: c1234

      key_len: 1 #只用到c1,因为先用c3后用c2分组,导致c2,c3索引没发挥作用

          ref: const

         rows: 1

        Extra: Using where; Using temporary; Using filesort

1 row in set (0.00 sec)

 

mysql> explain select * from t4 where c1=1 and c4=2 group by c2,c3 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t4

         type: ref

possible_keys: c1234

          key: c1234

      key_len: 1

          ref: const

         rows: 1

        Extra: Using where

1 row in set (0.00 sec)

 

D语句: C1确定的基础上,c2是有序的,C2之下C3是有序的,因此c2,c3发挥的排序的作用.

因此,没用到filesort

mysql> explain select * from t4 where c1=1 and c5=2 order by c2,c3 \G 

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t4

         type: ref

possible_keys: c1234

          key: c1234

      key_len: 1

          ref: const

         rows: 1

        Extra: Using where

1 row in set (0.00 sec)

 

E: 这一句等价与 elect * from t4 where c1=1 and c2=3 and c5=2 order by c3;

因为c2的值既是固定的,参与排序时并不考虑

 

mysql> explain select * from t4 where c1=1 and c2=3 and c5=2 order by c2,c3 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t4

         type: ref

possible_keys: c1234

          key: c1234

      key_len: 2

          ref: const,const

         rows: 1

        Extra: Using where

1 row in set (0.00 sec)

 

覆盖索引用通俗的话讲就是在select的时候只用去读取索引而取得数据，无需进行二次select相关表。这样的索引的叶子节点上面也包含了他们索引的数据。

select a  from table_name where b ·····；这样的一个查询，都知道索引应该加在b上面，

查询的处理过程：

首先去检索b索引找到与其对应的索引，

然后根据索引区检索正确的数据行。

注意：没选中索引列和where子句中使用了like，是不能覆盖索引的

覆盖索引不用遵循最左的顺序，但是要建在一个索引内，哪怕是单列的索引也可以是 using index
EXPLAIN SELECT
 
  houseRoleType

FROM house WHERE  houseFine=0;


//where 子句内遵循最左前缀
EXPLAIN SELECT
 
  houseTags

FROM house WHERE  houseRoleType>0 AND houseType>0 AND houseFine>0


Order 使用索引情况：
Order By Yes: 
 * where column order x,y   支持最左前缀：column,x,y   
 * select y from table order by x  x,y不必是同一个索引，但必须是索引

Order By No:
 * 不同的索引 
 * 非连续的索引 
 * asc+desc  
 * 表达式和多个值


特别提示:mysql一次查询只能使用一个索引。