mysql提高_MySQL提升

关系型数据库主要要点：

1， 架构

2， 索引

3，锁

4， 语法

5，理论范式

如何设计数据库？

为何要使用索引？

一般情况下 全表扫描 ，加载到内存中，之后轮训，当数据多的时候，这样会很慢，所以要索引。索引主流就是用到字典。 之后根据某个字段设置索引 。

什么样的信息能成为索引？

主键。唯一键等。能让数据具备一定区分性的字段。

生成索引，建立二叉查找树，进行二分查找。

当我们一直在插右子树的时候 从 O(logn) 变成了 O(n)， 可以利用树的旋转特性来保持 平衡二叉树，可是 我们 检索深度每增加1 就会进行一次IO，这样树深的时候 ，就要比全表扫描慢的多，这个时候我们就用到了b-tree。又叫平衡多路查找树。

生成索引，建立b-tree 结构进行查找。

每个节点存储(关键字 和指向孩子指针) 左关键字 > 右关键字 关键字永远比孩子指针少一个。左边关键字的值 > 左孩子的值。 右边关键字的的值 > 右孩子的值。其他的关键字的值在左右关键字值之间 开区间。

1 根节点至少包括两个孩子。

2 树中的每个节点最多包含m个孩子(m>=2)

3 除根节点和叶子节点外，其他每个节点至少有ceil(m/2)(取上限)个孩子。

4 所有叶子节点都位于同一层。

生成索引，建立b+ -tree 结构进行查找。

b+ - tree 是b-tree的变体。基本相同 除了：

--非叶子节点的子树指针与关键字个数相同。

--非叶子节点的子树关键字的值p[i] 必须小于p[i+1]的值 如 10 -p2 的值要小于20-p3的值。

--非叶子节点仅用来索引 数据都保存在叶子节点中。

--所有叶子节点均有一个链指针指向下一个叶子节点。

所以 b+ -tree 所有的检索都是从根部开始，一直检索到叶子节点才能结束 。非叶子节点只存储索引，可以存储更多关键字。这样b+-tree就变得更矮。这样查找数据时 在非叶子节点就终止掉了。

因此：B+ -tree 更适合用来做存储索引。因为：

B+tree 的磁盘读写代价更低。

B+-tree 查询效率更加稳定。(每个关键字查找路径相同 料率相同 O(log(n))。

B+-tree 更有利于数据库的扫描。(b-tree 提高磁盘IO的同时，并没有解决元素遍历效率低下的问题，而B+ -tree只需要遍历叶子节点，就可以解决对全部关键字信息的扫描。所以查询范围的时候，有更高的性能。)

生成索引，建立hash 结构进行查找。

哈希索引 要比b+-tree高。但是也有缺点；

BitMap索引 不是主流索引 oracle 有

只适用于某个字段的值固定的情况 如 男女 新增删除时 锁的力度非常大

适合并发少 统计运算多。

索引模块；

密集索引：

叶子节点保存的不仅仅是键值 还保存同一行记录里的其他列信息。由于密集索引决定了表的物理排列顺序，一个表只能有一个物理排列顺序。一个表只能创建一个密集索引。

稀疏索引：

叶子节点只保存了 仅保存了键位信息，以及该行数的地址。有的 稀疏索引保存了键位信息和主键。定位到叶子节点之后，通过地址或键位信息 进一步定位到数据。

mysql 两种存储引擎： 这两种引擎不显示支持hash

1 MyISAM

主键索引 唯一键索引 普通索引 均属于稀疏索引。

2 innoDB

只有一个密集索引

规则：

innoDB 它的索引和数据是存在一块的。myIsam 索引和数据是分开存储的。

如何定位并优化慢查询sql？

一 ：根据慢日志定位慢查询sql 步骤：

1 show variables like '%query%'

2 找到慢日志 - slow_query_log 设置为 on 状态。

3 找到slow_query_log_file .慢日志会写到这里。

4 long_query_time 每隔几秒 会写到 3里。 通常设置1秒。超过1秒 就是慢的了

5 show status like '%slow_queries%' 搜锁状态 会将慢查询数量显示出来。

6 set global slow_query_log = on;

7 set global slow_query_time = 1 ;

在my.ini 里设置。my.cnf .

实际运行时间是在 file文件里。

二： 用explain工具分析

explain + 慢查询语句 (eg： select 字段 from 表);

其中 index 和all 走的是全表扫描。

三 ：修改sql 或尽量让sql走索引。

注意： DML语句才会走慢查询

查询优化器会根据分析走哪个索引。

联合索引的最左匹配原则成因？

mysql 创建复合索引的规则：

1 对复合索引最左边的复合索引进行排序。

2 在第一个索引字段排序的基础上再对第二个索引字段进行排序。

3 所以 第一个字段绝对有序，第二个字段就是无序的了。

4 所以直接使用第二个字段判断 是用不到索引的。

索引建立越多越好吗？

不是的。

排他锁 --- X锁 写锁

共享锁 --- S锁 读锁

MyIsAM

对于MyIsAM 对数据进行select 的时候 会自动加上一个表级别的读锁，

对数据进行增删改的时候，会为我们操作的表加上一个表级别的写锁，

当读锁未被释放的时候，另外一个session想要对同一张表加上写锁的时候 就会被阻塞。

直到所有的读锁释放为止。

读锁 也叫共享锁

对表加读锁？

lock tables 表名 read；

释放：

unlock tables;

加写锁？

lock tables 表名 write；

当上读锁在上写锁 是不能的。 需要等待解锁的释放。

先上写锁 在上写锁也是不可能的。

写锁也叫排他锁。

对增删改查上写锁，也可以对select 上写锁。

INNODB

mysql默认自动提交事务。

innoDB用的二段锁，加锁和解锁是按照两个步骤来进行的。

先对同一批事务里的一批操作分别进行加锁，然后commit的时候，对事务里加上的锁进行统一的解锁。而commit 是自动提交的。看起来和myISAM没有区别 ，实际上是有的。

表级锁和索引无关。

当不走索引的时候 整张表就会被锁住 用的是表级锁。

INNODB在sql 没有用到索引的时候用到的是表级锁。

sql用到索引的时候用的是行级锁和gep锁 。

锁的力度越细 代价越高，相比表级锁 在表的头部加锁来讲，行级锁在扫描到某行的时候，对其上锁，这样代价比较大。INNODB支持事务的同时，也相比MYISAM引擎带来了更大的开销。同时之前从索引部分也了解到INNODB是有也且只有一个聚集索引，数据文件是和索引把绑在一起的。必须要有主键。通过主键索引效率很高，但是辅助索引需要查两次，先查到主键，再通过主键查询到数据。而myISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引相互独立的。因此myIsAM引擎在纯检索系统中 也就是增删改很少的系统中，其性能要好用innoDB。

MyISAM 和INNODB适合场景：

数据库锁的分类

数据库事务的四大特性

mysql 和 oracle 默认事务隔离级别;