4. 索引的使用
索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。
4.1 验证索引提升查询效率
在我们准备的表结构tb_item 中, 一共存储了 300 万记录;
A. 根据ID查询
1 | select * from tb_item where id = 1999\G; |
查询速度很快, 接近0s , 主要的原因是因为id为主键, 有索引;
2). 根据 title 进行精确查询
1 | select * from tb_item where title = 'iphoneX 移动3G 32G941'\G; |
查看SQL语句的执行计划 :
处理方案 , 针对title字段, 创建索引 :
1 | create index idx_item_title on tb_item(title); |
索引创建完成之后,再次进行查询 :
通过explain , 查看执行计划,执行SQL时使用了刚才创建的索引
4.2 索引的使用
4.2.1 准备环境
1 | create table `tb_seller` ( |
4.2.2 避免索引失效
1). 全值匹配 ,对索引中所有列都指定具体值。
改情况下,索引生效,执行效率高。
1 | explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'\G; |
2). 最左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始,并且不跳过索引中的列。
匹配最左前缀法则,走索引:
违法最左前缀法则 , 索引失效:
如果符合最左法则,但是出现跳跃某一列,只有最左列索引生效:
3). 范围查询右边的列,不能使用索引 。
根据前面的两个字段name , status 查询是走索引的, 但是最后一个条件address 没有用到索引。
4). 不要在索引列上进行运算操作, 索引将失效。
5). 字符串不加单引号,造成索引失效。
由于,在查询是,没有对字符串加单引号,MySQL的查询优化器,会自动的进行类型转换,造成索引失效。
6). 尽量使用覆盖索引,避免select *
尽量使用覆盖索引(只访问索引的查询(索引列完全包含查询列)),减少select * 。
如果查询列,超出索引列,也会降低性能。
1 | TIP : |
7). 用or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。
示例,name字段是索引列 , 而createtime不是索引列,中间是or进行连接是不走索引的 :
1 | explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'\G; |
8). 以%开头的Like模糊查询,索引失效。
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
解决方案 :
通过覆盖索引来解决
9). 如果MySQL评估使用索引比全表更慢,则不使用索引。
10). is NULL , is NOT NULL 有时索引失效。
11). in 走索引, not in 索引失效。
12). 单列索引和复合索引。
尽量使用复合索引,而少使用单列索引 。
创建复合索引
1 | create index idx_name_sta_address on tb_seller(name, status, address); |
创建单列索引
1 | create index idx_seller_name on tb_seller(name); |
数据库会选择一个最优的索引(辨识度最高索引)来使用,并不会使用全部索引 。
4.3 查看索引使用情况
1 | show status like 'Handler_read%'; |
1 | Handler_read_first:索引中第一条被读的次数。如果较高,表示服务器正执行大量全索引扫描(这个值越低越好)。 |
转载请注明来源,欢迎对文章中的引用来源进行考证,欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论,也可以邮件至 1210331079@qq.com