携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第6天，点击查看活动详情
索引深入剖析
下篇预告 MVCC
第一种：在读取数据前，进行加锁，阻止其他事务对其修改，Lock Based Concurrency Control（LBCC）
缺点：太暴力，读就不允许写，降低并发性能
第二种：生成一个数据请求时间点的一致性数据快照，并且用这个快照来提供一定级别（语句级别或事务级）的一 致性读取（MVCC）Multi Version Concurrency Control
（图源gupao科技，侵删）
索引是什么
首先索引有多牛逼？
可以看到差不多两秒左右
创建索引的结构:
500w行的数据，创建索引耗时15s
一样的语句可以看到，只花了0.042s
只能说是nb
还有一个索引，叫主键索引，这个在你刚创建主键的时候就有了，在unique的基础上加上not null的要求
fulltext索引用在比如长文本的里面，在查询的时候格式也改变了
select * from fulltext_test
where match (content) against('value ' IN NATURAL LANGUAGE MODE);
索引的存储结构
索引的内容如果用有序数组存储的话，查找没事，但是如果要更新数据的话就需要大规模移动数据
所以就想到**可以二分查找的链表**
BST
Binary Search Tree
二叉查找树
但是
平衡二叉树
这个树不太平衡，查找的话时间复杂度太高
就有了平衡二叉树（AVL TREE-Balanced Binary Search Tree)
左右子树深度差不超过1
B树
更加优化一些：B树
注意右上方的N和N+1的意思
B+树
B树已经很牛逼了，但是MySQL里用的是另外一种，B+树
注意到叶子节点也是有箭头指向的，对于查找范围数据很友好，不需要从头开始查询。
可以看到索引的方式其实有两种：
- hash
时间复杂度O（1）
重复值很多就会有hash冲突
- BTREE
但是发现InnoDB里面其实是没法选择Hash的
索引在存储引擎中的实现
如何查看mysql在服务器的位置？
MyISAM和InnoDB
相关内容可以查看博客：
在服务器对应的地址里，cd你的数据库名字可以看到这
MyISAM
分两种，一个是主键索引，一个是辅助索引
检索的时候没有区别，都是先检索myi文件B+树上得到磁盘地址
InnoDB
索引即数据
叶子结点存放数据
主键值对数据进行排序（如上图）
聚集索引，决定了数据存放顺序
innodb里面主键索引一定是聚集索引
当主键索引不存在呢？
会优先选unique not null、等等的字段，实在没有，默认会有ROWID
所以一张表不可能没有索引，也不可能没有聚集索引
当有其他的索引（二级索引）（辅助索引）（在innodb里和主键索引有清晰区别）
（先从辅助索引查到主键索引，然后再去主键索引再去找到）
（这个时候没有主键索引的话，就到上面定义聚集索引的情况了）
索引的创建和使用原则
这时候就有聪明的小明问：那我每个字段都建立索引，查询速率不是嘎嘎快？？
当然不是
索引创建原则
列的离散度
（distinct count）/count
简而言之就是，重复字段越少越好
相信也很容易理解
举例：
500w条数据
gender和name字段
如果在gender上建立索引
预估要扫描250w行才能得到数据！
而，在name上建立索引
预估扫描1行！
在查找的效率差不多和全表扫描差不多时，系统会放弃 索引查找
联合索引最左匹配
第一，联合，就是多个字段联合在一起
ALTER TABLE user
add INDEX `索引名字` (`字段1`, `字段2`...);
第二，最左匹配
举例：
ALTER TABLE user
add INDEX `index1` (`a`, `b`, `c`);
- SELECT * FROM user WHERE a = 1 AND b = 2 AND c = 3;
- SELECT * FROM user WHERE b = 1 AND c = 2 AND a = 3;
-
SELECT * FROM user WHERE a = 1;
-
SELECT * FROM user WHERE b = 2 AND c = 3;
1,2,3可以，4用不到（具体可以在语句前面加上explain）
1符合条件
2会有个OPTIMIZER优化器，自动调节语句顺序
3这个就是最左匹配的意思
4就是没有用到这个索引的案例（中间也不能中断）
所以，当查询条件是name、name和phone的时间比较长的时候，只要建立后面的联合最左匹配索引就行
覆盖索引
先介绍回表
像上面我们InnoDB的情况，当查询了辅助索引之后回再拿着主键去查询主键索引，这就是回表，增加了性能消耗
避免回表，比如
EXPLAIN SELECT name, phone FROM user WHERE name = 'a' AND phone = '133333';
Using index说明覆盖了索引
如果改成*，当然不能覆盖索引，没有索引的就必须到主键索引里去找了
（这就是为什么阿里员工手册里写的，sql语句不用，要写全）*
7前缀
8无序，插入比较乱
3：
（从左往右找的，前面给个%不知道查哪个字符串）
4：不确定，是由Optimizer优化器决定的
Cost Based Optimizer(CBO)
根据I/O、CPU成本
