Innodb
存储引擎的表数据和索引是存储在同一个表空间里面,在一定程度上索引的效率没有MyISAM
快,但这丝毫不影响InnoDB
存储引擎成为主流的存储引擎。
拓展:MyISAM
存储引擎的表的数据和索引是分开存储的,即每个 MyISAM
在磁盘上存储 .frm(存储表定义)、.MYD(存储数据)、.MYI(存储索引)
,数据和索引存放在不同的文件里,获取速度更快。
EXPLAIN 查看执行计划信息的重点列
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
| 1 | SIMPLE | user | ref | in_name | in_name | 93 | const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
如上所示,explain
显示了多列内容,其中type、possible_keys、key、key_len、ref、rows、Extra
是优化SQL
时必须要关注的。
-
type
:mysql
决定如何查找表中的行,下面是type的类型,从上到下性能由好到差NULL:MySQL在优化阶段分解语句,在执行阶段甚至不需要访问表或者索引,例如从索引列中查找最小的索引通过单独查找索引就可以完成,而不需要访问表数据 system,const:通过主键查询数据,只能返回一行数据,主键包括单列主键和联合主键,联合主键必须都存在,否则type列是ref类型,例如联合主键(id,name),id和name都在where条件中才可以 eq_ref:MySQL最多只会返回一行数据,待实验 ref:使用索引查找,但不是主键和唯一索引,它可能会返回多个匹配的行 range:有范围的索引扫描,即带有 BETWEEN 或在 WHERE 子句里面带有 > 的查询,有时候使用in或or也会显示范围扫描 index:全表扫描,但会按索引次序进行而不是行,避免了排序,缺点是按索引次序读取数据整个表。如果在 Extra 列中看到了 Using index,说明使用的是覆盖索引,只扫描索引的数据,而不是按照索引次序。 All:全表扫描
-
possible_keys
可能用到的索引 -
key
用到的索引 -
key_len
索引的长度。例如联合索引(name,state,address)
,如果只使用name
部分的索引,那么长度是name
部分索引长度,如果使用了name+state
部分索引,那么长度是两部分索引长度和。 -
ref
显示key
列使用的索引中查找值所用的列或常量 -
rows
涉及的行数 -
Extra
# 索引相关 using index:使用了覆盖索引 using where:在查找使用索引的情况下,需要回表查询数据 using index condition:查找使用了索引,但是需要回表查询查询数据 using index ; using where:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询 # 排序相关 using index:使用单列索引排序;使用联合索引排序,且必须是联合索引的首列在前,并且多列使用相同的排序。无所谓查询的列是否只是索引列 using filesort:使用一般列排序或联合索引的非首列 using index ; using filesort:多字段排序时,部分使用索引字段,部分是一般列;或联合索引的多列时,非首列在在前;或联合索引的多列时,首列在前但多列使用不同的排序 # 分组相关 using temporary ; using filesort:group by默认情况下会排序 using temporary :使用order by null优化后的情况 using index:使用索引分组
索引的使用和优化
-
是否使用索引与
where
后的条件顺序无关 -
索引列使用比较符号,会使用索引,单列索引和联合索引都可以,但是索引后面的索引不会被使用,联合索引比较详见第5条的案例
-
尽量使用联合索引,减少使用单列索引。例如列
name、state、address
联合索引(name,state,address)
相当于创建了三个索引:name、name+state、name+state+address
单列索引name、state、address
,当三个索引一起作为查询条件时,MySQL
会选择一个最优的索引(数据的辨识度高的索引)来使用,并不会使用全部索引。
辨识度高是指根据索引查出的数据量小,例如数据库中有100w
条数据,name
和state
都是单列索引,其中name='瞎子' and state = 0
的数据只有一条,其他数据均为name='瞎子' and state = 1
,对于select * from table_name where name = '瞎子' and state = 0;
,MySQL
会使用索引status
-
索引全值匹配,例如联合索引
(name,state,address)
,SQL:select * from table_name where state = '1' and name = '小乔' and address = '北京';
-
SQL
符合联合索引的最左前缀法则,例如联合索引(name ,state,address)
,要想用到索引,条件中必须要包括联合索引中的第一个列,具体会用几个索引参考下面的代码# 用到三个列的索引 where name = '' and state = '' and address = '' # 用到两个列的索引 where name = '' and state = '' # 用到name列的索引,因为跳过了state列,所以address的列不会使用索引 where name = '' and address = '' # 用到name和state列的索引,address的列不会使用索引 where name = '' and state > '1' and address = '' # 没有用到索引 where state = '' and address = ''
-
通常情况下,我们都会根据
where
条件来创建合适的索引,这是索引优化的一个方面,但数据库通过索引查询数据后要不要回表查询,这也是索引优化的方面,也就是说MySQL
通过索引查数据时,索引的叶子节点上已经包含要查询的数据,这样就不需要回表查询了。这就是著名的覆盖索引。MySQL
的InnoDB
引擎只有B-Tree
类型的索引具有覆盖索引,哈希索引、空间索引和全文索引都没有覆盖索引。
覆盖索引:查询只需要访问索引,而无须访问数据行
覆盖索引的原理:索引的叶子节点上包含该行的所有索引列
例如id、name、state、address
四列,其中id
为主键,(name,state,address)
是联合索引# 对于下面三个sql,explain查看执行计划,发现Extra列的值为Using where; Using index,对应上面Extra的类型 select name , address from table_name where name = 'p7'; select id , name from table_name where name = 'p7'; select state from table_name where name = 'p7'; # 下面这个sql的Extra列的值是Using where,因为查询的password不是索引列也不是索引列的一部分 select id , name , password from table_name where name = 'p7';
-
like
尾部模糊查询索引有效,头部模糊查询索引失效,利用覆盖索引优化like
头部模糊查询# 通过覆盖索引查询id select id from table_name where name like '%p7%'; # 通过id查询数据 select * from table_name where id = xxx;
-
in
索引有效,not in
索引无效 -
or
前的条件中使用索引,后面条件没有使用索引,那么查询不使用索引 -
MySQL
执行SQL
时发现全表扫描比索引快,则不使用索引。
示例1:name
是单列索引,非主键,数据库中99条数据的name
都是盲僧,只有一条数据是p7
,在执行where name = '盲僧'
时,MySQL
会放弃索引,进行全表扫描。
示例2:name
是单列索引,非主键,如果数据库中所有name
字段没有null
的情况,is null
使用索引,is not null
进行全表扫描;如果name
为null
占大多数,is null
进行全表扫描,is not null
使用索引
上面两种情况,均是MySQL
内部优化,根据数据库中的数据占比而定的