mysql索引:
MyISAM引擎使用B+Tree作为索引结构,和DB2的索引一样,叶节点的data域存放的是数据记录的地址,
InnoDB的引擎的cluster index叶子节点存放的就是数据本身,如果表没有primary key或者unique key,
那么InnoDB默认会以隐藏字段DB_ROW_ID作为key创建cluster index,二级索引的叶子节点则存放的是
主键的值.
问题:cluster index的主要目的是数据页的物理和索引键的顺序一致,这样在获取一组范围的数据的时候
可以读取连续数据页减少I/O和数据页数.InnoDB默认以DB_ROW_ID创建cluster index或者人为创建自增
字段作为主键,这样可以连续插入数据页,是可以避免insert或者update等写性能影响,可是这样的主键
又不是常用被搜索键值,所以这样的cluster index的好处是什么呢?使用二级索引的时候,岂不是要进行
二次索引搜索?
了解索引帮助分析explain输出,参考官方文档:
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
EXPLAIN Join Types:几种常见类型由好到差:
const,system-> ref->range->index->All
测试:create table t(a int primary key,b int,c int);
insert into t2 values(1,11,111),(2,22,222),(5,56,565),(9,32,322),(7,23,233);
create index bidx on t(b);
1.const:一行符合条件而且使用主键
mysql> explain select * from t where a=2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
2.ref:
ref官方解释和例子:
All rows with matching index values are read from this table for each combination
of rows from the previous tables. ref is used if the join uses only a leftmost prefix
of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if
the join cannot select a single row based on the key value). If the key that is used
matches only a few rows, this is a good join type
SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
这里测试使用非主键或非唯一键,返回匹配某个单独值的所有行,也是ref:
mysql> explain select * from t where b=22\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: bidx
key: bidx
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
如果把*改成a,b,extra就变成了using index,就是覆盖索引,即所有值都可以从索引中获得:
mysql> explain select a,b from t where b=22\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: bidx
key: bidx
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
3.rang:使用索引读取了一定范围的数据
Only rows that are in a given range are retrieved, using an index to select the rows.
The key column in the output row indicates which index is used. The key_len contains
the longest key part that was used. The ref column is NULL for this type.
mysql> explain select * from t where a>3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 3
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
4.index:SQL语句只访问索引列
mysql> explain select a,b from t \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: index
possible_keys: NULL
key: bidx
key_len: 5
ref: NULL
rows: 5
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
5.All:全表扫描
sql> explain select * from t where c=222\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 20.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
6.删除原来bidx索引,在b,c列创建联合索引create index bcidx on t(b,c);
排序时候可以使用索引:
mysql> explain select * from t where b=11 order by c\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: bcidx
key: bcidx
key_len: 5
ref: const
rows: 3
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
把联合索引b改为范围查找,索引b后面的字段失效,排序无法使用索引:
mysql> explain select * from t where b>11 order by c\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: bcidx
key: bcidx
key_len: 5
ref: NULL
rows: 4
filtered: 100.00
Extra: Using where; Using index; Using filesort
1 row in set, 1 warning (0.00 sec)
排序顺序和索引顺序不一样,排序也无法使用索引:
mysql> explain select * from t order by b desc,c\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: index
possible_keys: NULL
key: bcidx
key_len: 10
ref: NULL
rows: 7
filtered: 100.00
Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)