MySQL进阶（索引）

MySQL进阶

一、存储引擎

索引是在存储引擎中实现的，所以不同的存储引擎有不同的索引结构

MySQL5.5版本后默认存储引擎是innoDB

什么是存储引擎

显示所有的存储引擎

指定存储引擎 engine = ？

存储引擎特点

innoDB特点：

1. 事物
2. 外键
3. 行级锁

逻辑存储结构：

1. 表空间
2. 段
3. 区
4. 页
5. 行

myisam存储引擎

memory存储引擎

区别

存储引擎的选择

二、索引

什么是索引：一种高效获取数据的数据结构

索引的优缺点

索引的结构

一般我们使用或者谈到的都是B+Tree索引

1.二叉树

我们使用二叉树进行存储的时候，如果顺序插入数据那么就会形成一个链表，性能会降低，这时候我们就使用红黑树来存储

2.B树

使用b树

3.B+树

使用b+树

MySQL的b+树

4.hash索引

hash索引

面试题：为什么使用B+树

树的数据结构中我们遍历的树的深度越深查询效率越慢，二叉树在顺序插入的时候会形成链表性能低，红黑树也是二叉树层数也比B+树更深，B树中叶子节点和非叶子节点都会存储数据，这样也会导致层数加深，而hash索引不支持范围查询和排序

索引的分类

问题

索引的语法

SQL性能分析

1.执行频率

在我们的客户端上使用show global status like ‘Com____________________’

获取全局的状态信息，模糊查询Com后面7个字符的信息，我们可以通过这个获取数据库当前查询多还是增删改多

2.慢查询日志

3.profile

4.explain

可以使用类型判断语句的效率，也就是type属性

索引效率检测

最左前缀法则

联合查询中：最左侧列的索引存在后续才存在，否则失效

索引的失效

1.索引列运算

2.字符串不加引号

3.头部模糊查询

4.or连接

这里是否走索引取决于查询的效率是否不如全表扫描，如果走索引不如全表扫描那么MySQL评估就不会走索引

NULL 和 NOT NULL 也是一样的，如果当前查询数据NULL 非常多，那么就可能走全表扫描而不走索引，NOT NULL同理

5.评估决策

SQL提示（指定索引）

覆盖索引（回表查询）

理解：如果我们当前查询的数据存在索引之中，那么我们先走二级索引必然可以直接返回当前需要的数据，因为二级索引本身就包含id和本身的数据。

但是如果我们查询的数据没有索引的话，那么我们必须要走聚集索引通过id才能查到我们对应的数据

解决办法：最好的方法是建立username和password的联合索引，这样当我们查询数据的时候直接通过二级索引就可以返回结果，但如果我们只对username创建索引的话，那么我们必然需要连表查询，这样增大时间的花费

前缀索引

联合索引

索引设计原则