1.索引是什么?为什么要创建索引?
索引其实就是一种数据结构,能够使MySQL引擎快速定位到某行数据所在的位置。当数据量过大时(千万级),如果进行全表扫描所需要耗费的时间是很长的,因此需要构建索引来帮我们快速定位到数据的位置并返回。
2.那索引在平常的开发中你都是如何使用的呢?
在日常开发中,经常用于计算的(充当where条件时)列中,我们会为其构建索引,根据业务场景的不同可将索引划分为主键索引、唯一索引、非空索引、联合索引(除此之外,有一些较深层次的聚簇索引、覆盖索引)。
3.建立索引的优缺点?
由于索引本身就是一种数据结构,因此在构建索引时需要对列进行一定的计算,获取数据所在的位置(key-Value的形式,因此需要占据一定的空键),随着数据量的增大,索引所占用的空间也会越来越大,并且每次对数据进行变动,例如增、删、改时都会使数据库重新计算索引,因此构建索引时需要慎重。
4.针对上述情况,你如何去构建索引呢?
对于某些重复率较高的字段,例如性别、年龄等,构建索引完全是浪费。大量数据会占据大量的空间,而且因为字段重复的原因,索引并不能够准确的定位到所需要的某列数据,因此回表查询的数据量依旧很高。对于文本类型的来说,也不适用于建立索引,因为占据的空间和重复率太高。除此之外,建立联合索引时应遵循最左匹配原则,例如key1,key2,两个字段中应考虑将命中率最高的索引置于key1。其他情况,根据业务场景来就可以了,例如身份证:唯一索引;姓名:非空索引
5.在进行查询时如何有效的利用建立好的索引呢?(索引失效)
(1)索引列不要进行计算,例如:slary = 9000-(4000*0.06+100+100+50);
(2)避免隐式转换,例如:当id为整形时:id=‘101’,会导致字符转为整形,常出现的日志字段。
(3)避免使用or关键字,这种有可能会发生全表扫描
(4)in、not in替换为exits、not exits
6.你是如何判断索引有没有失效呢?
常规状态下,如果逻辑没有什么问题的话,索引一般是不会失效的,当然可能会因为个人的一些粗心而导致索引失效。对于执行效率较低(时间过长)的SQL语句,我们可以采用explain关键字来查看当前SQL的执行计划(不是执行顺序),根据MySQL返回的结果查看当前索引是否失效--------->explain select count(1) from db_user where name = “张三”;
7.你都用那哪种索引的数据结构呢?
下面两个索引结构有点复杂,这里不多说了,建议新手先百度下
B+树结构
Hash索引
- B-Tree
相较于以往的二叉树来说,B树(也就是B+树)将其从叶子节点上仅存储键变为叶子节点即存储键也存储值。在 InnoDB 里,索引B+ Tree的叶子节点存储了整行数据的是主键索引,也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引,也被称之为非聚簇索引。。B+树的叶子节点中存放的元素>=2,这样就在原有二叉树的基础上从一颗高瘦树(二叉树每个节点存放<=2个元素)变为了一个矮胖树,虽然对比的次数没有明显减少,但是IO的次数缺大大降低了(两个树中每个层级数据的对比都要先IO到内存中,B+树由于是一个矮胖树,层级较少,因此IO的次数少),要知道IO和内存计算的效率可不是一个数量级的,这样就大大增加了查询的效率。 - Hash索引
Hash索引是通过计算key所对应的Hash值而快速定位到某行数据所在的位置(Hash表)。但也是因为如此,Hash索引适用于等值查询,仅仅能满足"=",“IN"和”<=>"查询,不适用于范围比较,因此也不适用于范围排序。相较于B+树而言,Hash表的散列效率是非常高的(常规情况下,hash冲突为0的情况下效率是最高的),但在数据量较大的情况下,发生Hash冲突的可能行较高,一旦发生Hash冲突,Hash表的遍历效率就非常低了,这也是链表的特性。除此之外,如果出现性别、年龄等字段,Hash冲突的几率近乎为50%,因此,一般情况下,Hash索引并不适用于大量数据查询。
8.其他
索引下推,这个是MySQL5.7增加的一个特性。可参考MySQL性能优化:什么是索引下推