• 暂时停更一段时间!
  • 近期网站将陆续进行前端页面改造!
  • 招募网站编辑,联系站长!

MySQL索引的使用

文章目录[隐藏]

索引的使用

MySQL 索引使用的数据结构主要有 BTree 索引和哈希索引 。

对于哈希索引来说,底层数据结构就是哈希表,因此在绝大多数需求为单条记录查询的时候,可以选择使用晗希索引 ,查询性能最快;其余大部分索引场景,则建议选择 BTree 索引 。

MySQL 的 BTree 索引使用的是 B 树中的 B+Tree ,但对于主要的两种存储引擎其实现方式是不同的 。

1、 MylSAM: B+Tree 叶节点的 data 域存放的是数据记录的地址。 在索引检索时,首先按照 B+Tree 搜索算法搜索索引,如果指定的 Key 存在,则取出其 data 域的值,然后以 data 域的值为地址读取相应的数据记录。 这被称为非聚簇索引 。

2、 lnnoDB : 其数据文件本身就是索引文件 。 相比 MyISAM ,索引文件和数据文件是分离的,其表数据文件本身就是按 B+Tree 组织的一个索引结构,树的叶节点 data 域保存了完整的数据记录 。 这个索引的 key 是数据表的主键,因此 InnoDB 表数据文件本身就是主索引 。 这被称为聚簇索引(也叫聚集索引) 。而其余的索引都为辅助索引 ,辅助索引 data 域存储相应记录主键的值而不是地址,这也是和 MyISAM 不同的地方。 在根据主索引搜索时,直接找到 key 所在的节点即可取出数据;在根据辅助索引查找时,则需要先取出主键的值,再走一遍主索引 。 因此,在设计表的时候,不建议使用过长的字段作为主键,也不建议使用非单调的字段作为主键,这样会造成主索引频繁分裂 。

explain

此命令能够打印出 SQL 语句的执行计划,从而判断要执行的 SQL 语句是否能够命中索引,并做进一步调整 。 在优化 SQL 查询时,最好的方式就是使用此命令来判断执行计划是否合理。 如下:

explain select * from table_user where id=1 ;

• type : 显示了连接使用了哪种类别,有无使用索引 。 如果为 ALL ,那么说明要进行全表扫描 。

• key : 此列显示 MySQL 实际决定使用的键(索引) 。 如果没有选择索引,键是 NULL 。 要想强制 MySQL 使用或忽视 possible_keys 列中的索引,则在查询中使用 FORCE INDEX 、 USE INDEX 或者 IGNORE INDEX。 如果这里为 NULL ,则说明没有命中索引 。

• Extra :此列如果出现 Using filesort (需要额外的步骤来发现如何对返回的行排序)或者 Using temporary (需要创建一个临时表来存储结果),说明查询需要优化。

MySQL 自身是有查询优化器的,优化器的作用就是在一个查询所有可能的执行方式中找到其中最好的执行计划 。

因此 explain 获取到的执行计划并非是固定的,它会随着数据分布情况而变动,执行计划也有可能改变 。

而且当数据库计算出使用索引所耗费的时间长于全表扫描或其他操作时(比如当表中索引字段数据重复率太高),将不会使用索引 。

1 )最左前缀原则

MySQL 中的 索引可以以一定顺序引用多列,这种索引叫作联合索引 。 如 User 表的 name 和 city 加联合索引就是 ( name,city ) 。

而最左前缀原则指的是,如果查询的时候查询条件精确匹配索引的左边连续一列或几列,则此列就可以被用到 。

如下:

select * from user where name=xx and city=xx; //可以命中索引
select * from user where name=xx; //可以命中索引
select * from user where city=xx; //无法命中索引

这里需要注意的是,查询的时候如果两个条件都用上了,但是顺序不同,如 city= xx and name =xx,那么现在的查询引擎会自动优化为匹配联合索引的顺序,这样是能够命中索引的 。

由于最左前缀原则 ,在创建联合索引时,索引字段的顺序需要考虑宇段值去重之后的个数,较多的放前面。ORDER BY 子句也遵循此规则 。

2 )避免 where 子句中对字段施加函数,如 to_date(create_time) >xxx ,这会造成无法命中索引 。

3 )在使用 InnoDB 时使用与业务无关的自增主键作为 主键, 即使用逻辑主键,而不要使用业务主键。

4 )合理利用索引覆盖

覆盖索引( covering index )指一个查询语句的执行只需要从辅助索引中就可以得到查询记录,而不需要查询聚集索引中的记录,也可以称之为实现了索引覆盖。

简单来说就是查询条件命中了索引,而查询宇段也属于索引中的字段,那么就实现了索引覆盖。 当实现覆盖索引的时候, explain 命令的 Extra 会显示 using Index。

5 )避免冗余索引

冗余索引指的是索引的功能相同,能够命中 A 就肯定能命中 B ,那么 A 就是冗余索引 。如( name,city)和(name)这两个索引就是冗余索引,能够命中后者的查询肯定是能够命中前者的 。

在大多数情况下,都应该尽量扩展已有的索引而不是创建新索引 。

MySQLS.7 版本后,可以通过查询 sys 库的 schemal_redundant_indexes 表来查看冗余索引。

6 )将打算加索引的列设置为 NOT NULL ,否则将导致引擎放弃使用索引而进行全表扫描 。

7 )删除长期未使用的索引,不用的索引的存在会造成不必要的性能损耗 。

MySQL 5.7 后可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用 。

8 )联表查询必须存在的情况下,可以使用索引提高性能。

联表的索引使用要注意如下两点 :

1、确保 ON 和 USING 中的列上有索引 。 在创建索引的时候就要考虑关联的顺序 。 当表 A 和表 B 用列 c 关联的时候,如果优化器关联的顺序是 A 、 B ,只需要在 B 的 c 字段建立索引 即可 。

2、确保任何的 GROUP BY 和 ORDER BY 中的表达式只涉及 一个表中的列,这样 MySQL 才有可能使用索引来优化 。

9 )在使用 limit offset 查询缓慢时,可 以借助索引来提高性能:

SELECT * FROM table_user a JOIN (select id from table_user limit ? , ?) b ON a.id= b.id order by create_time desc;

10 )查询条件的字段应使用正确的数据类型,否则 MySQL 会自动做类型转换,导致无法命中索引 。

例如 table_user 表中 mobile 列为字符串类型,查询的时候如果没有加‘ ’,那么就会进行强制类型转换 。

索引可以加快查询速度,但索引也是有代价的:索引文件本身要消耗存储空间,并且在被索引的表上 INSERT 和 DELETE 会变慢;另外, MySQL 在运行时也要消耗资源维护索引,因此索引并不是越多越好 。

在如下两种情况下不建议建索引 :

1、表记录 比较少,例如一两千条甚至只有几百条记录的表,没必要建索引 。

2、索引的选择性较低 。 所谓索引的选择性( Selectivity ),是指不重复的索引值(也叫基数, Cardinality )与表记录数( #T )的比值:

Index Selectivity = Cardinality  / #T

显然选择性的取值范围为(0, 1 ),选择性越高的索引价值越大,这是由 B+Tree 的性质决定的 。

在 MySQL 5.6 后, MySQL 库下的 innodb_index_stats 表的 stat_value 字段记录了某张表在某个索引的不同取值的记录个数, innodb_table_stats 的 n_rows 字段记录了某张表总的记录数目,两者相除即为索引的区分度 。

此外,需要提到的是 MySQL 也支持全文索引( 5.6.24 之前 MyISAM 引擎支持,之后 InnoDB 也开始支持):

CREATE TABLE user note (
     id INT AUTO INCREMENT NOT NULL PRIMARY KEY ,
     title VARCHAR (200),
     FULLTEXT(title)
) TYPE=MYISAM ;

SELECT * FROM `user_note` WHERE MATCH (`title`) AGAINST (’篮球’);

丨极客文库, 版权所有丨如未注明 , 均为原创丨
本网站采用知识共享署名-非商业性使用-相同方式共享 3.0 中国大陆许可协议进行授权
转载请注明原文链接:MySQL 索引的使用
喜欢 (0)
[247507792@qq.com]
分享 (0)
多啦H梦
关于作者:
热爱开源,热爱分享,谢谢大家的资瓷!

邀请您免费 注册账号 登录 即可参与讨论!

(1)个小伙伴在吐槽
  1. 涨姿势了
    马云2018-08-19 22:54 Windows 10 | Chrome 67.0.3396.99