MySQL进阶篇2-索引的创建和使用以及SQL的性能优化

索引

mkdir mysql
tar -xvf mysqlxxxxx.tar -c myql
cd mysql
rpm -ivh   .....rpm
yum install openssl-devel
​
systemctl start mysqld
​
gerp 'temporary password' /var/log/mysqld.log
​
mysql -u root -p
mysql>
show variables like 'validate_password.%'
set global validate_password.policy = 0;
set global validate_pawwword.length = 4;
alter user 'root'@'localhost' identified by '1234'
​
create user 'root'@'localhost' identified with mysql_native_password by '1234';
​
​
​

索引概述

高效获取数据的有序数据结构。数据库除了存储原始的数据外，还要存储索引这种数据结构。 通过这些数据结构来指向原始的数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

select * from emp where age > 35;

无索引状况：

1、根据条件逐条查找，直到最后一条记录。【全表扫描，性能极低】

有索引状况：

1、对年龄二叉树【二叉排序树】进行维护。

优点：

1、提高数据检索效率，降低数据库IO成本。

2、通过索引对数据排序，降低数据怕徐成本，降低CPU消耗。

缺点：

1、索引也是占用空间的。

2、虽然提高了查询效率，但是要维护索引，降低了表更新的速度。

索引数据结构

二叉树缺点：顺序插入时，一直往左侧插入，形成一个单向链表，查询性能大大降低。数据量较大的情况下，层级较深，检索速度慢。

【红黑树，需要自平衡的二叉树。减少了层级】，但是数量大的情况下，检索也会较慢。

B树：

多路平衡查找树，不止两个子节点。指针比父节点的元素+1。【根据阶数，满阶就向上分裂。p68级有完整演示】

B+树：

所有的元素都会出现在叶子节点。上边的节点起到了索引的作用，叶子节点是用来存放数据的。

MySQL的B+树索引结构

哈希索引

采用一定的哈希算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储到hash表中。

1、先算出这张表当中，每一行数据的hash值，接下来再拿到name字段的所有值，针对于name字段通过它内部的hash函数去计算每一个name值它应该落在哪一个hash槽位上。

【hash表的槽位上存储：字段属性值+字段属性值所在的行的hash值。】

如果产生了哈希冲突，那么则在槽位上存储一个链表来记录新值。

特点：

1、只能用于等值匹配(=,in)，不能用户范围查询。【between < > ... 】

2、无法利用索引完成排序操作

3、查询效率较高，通常一次检索就够了，效率通常要高于B+树索引。

4、目前只有Memory存储引擎支持hash索引。【但是InnoDB引擎会在指定条件下，自动将B+树索引构建为hash索引】

思考：

为什么InnoDB引擎选了B+树索引，而不是其他的呢？

二叉树：顺序插入，形成了链表，搜索时造成性能下降。

红黑树：本周上也是一棵二叉树，变成了平衡树。

B树：叶子节点和非叶子节点都存放数据，都存放到一页中，但是页的大小是有限的，为16K，这将导致一页当中存储的数据更少，指针跟着减少，只能增加树的高度，导致性能降低。放到B+树中，能存储更多的数据，层数更少，所以性能更优。

B+树：