MySQL-索引

索引是什么

数据库中专门用于帮助用户快速查找数据的一种数据结构。类似于字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,然后直接获取。

索引有什么用

加速查找和约束

举个栗子:如果要查询一条数据,在没有索引的情况下是一条一条的从头开始往后查,在量小的情况下感觉不出来,如果在上百万条数据的情况下,查询起来将会非常的慢(在SSD情况下查询一条数据大约耗费2-3s,造成1.用户的体验非常差 2.消耗硬盘)

索引的原理

MySQL从5.5开始默认innodb引擎,算法默认是BTree,本身很难,举个简单的容易理解的二分查找,但不代表BTree算法;

范围1-100,如果查询66,先从中间切一刀,看看66比两边的数大还是小,然后在和这个数相近的一边继续切割查找,比从1到66这样一个个的查要快速很多。

本质都是:通过不断地缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是说,有了这种索引机制,我们可以总是用同一种查找方式来锁定数据。

索引的分类

  • 普通索引
  • 唯一索引
  • 主键索引
  • 组合索引

普通索引

仅加速查找

添加索引:

create index 索引的名字 on 表名(列名)

删除索引:

drop index 索引的名字 on 表名

查看索引:

show index from 表名  

唯一索引

唯一索引有两个功能:加速查找和唯一约束(可含null)

create unique index 索引名 on 表名(列名)

drop unique index 索引名 on 表名

主键索引

主键索引有两个功能: 加速查找和唯一约束(不含null)

alter table 表名 add primary key(列名);

alter table 表名 drop primary key;

组合索引

组合索引是将n个列组合成一个索引

create index 索引名 on 表名(列名1,列名2);

索引的使用

在使用like模糊匹配、函数、or情况下,类型不一致、排序 索引会失效

组合索引最左前缀会失效

   (1)避免使用select *
   (2)count(1)或count(列) 代替count(*)
   (3)创建表时尽量使用char代替varchar
   (4)表的字段顺序固定长度的字段优先
   (5)组合索引代替多个单列索引(经常使用多个条件查询时)
   (6)尽量使用短索引 (create index ix_title on tb(title(16));特殊的数据类型 text类型)
   (7)使用连接(join)来代替子查询
   (8)连表时注意条件类型需一致
   (9)索引散列(重复少)不适用于建索引,例如:性别不合适

SQL优化-执行计划

explain + 查询SQL – 用于显示SQL执行信息参数,根据参考信息可以进行SQL优化

mysql> explain select * from userinfo;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | userinfo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2989671 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+

type字段:

性能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

参考链接:https://www.cnblogs.com/majj/p/9196025.html 参考链接:http://www.cnblogs.com/linhaifeng/articles/7274563.html

 

发表评论