数据库学习笔记(持续更新)_风哥mysql学习笔记

前言

文章主要用于记录自己学习笔记和理解，如有错误欢迎指出，与君共勉

此文章内容会持续完善，欢迎关注^ _ ^

---

一、索引

在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用指向数据，这样就可以在这些数据结构上实现高效查找，这些数据结构就是索引。

在文章的最后附上一张索引的脑图，脑图来源于网络。

索引的分类

• 单值索引：一个索引只包含单个列，一个表中可以有多个单值索引
• 唯一索引：索引列的值必须唯一，可为空
• 复合索引：一个索引包括多个列

索引的优势

1）提高数据检索效率，降低磁盘IO成本
2）通过对数据的排序，降低排序成本

索引的劣势

1）索引虽提高了查询效率，但同时降低了更新、修改、删除的效率，因为MySQL不仅要保存数据，还要维护数据和索引的关系。
2）需要成本去维护索引。一个性能良好的索引需要不断的去尝试，以找到最优解。

创建索引

创建索引： create index index_name on table_name(column_list)

数据结构

首先我们一起来了解一下索引的几种常见的数据结构。

索引的底层数据结构有五种，分别是二叉查找树（Binary Search Tree）、自平衡二叉查找树（Self-balancing Binary Search Tree）、B 树（B-Tree）、字典树（Trie-Tree）、空间数据分割树（Spatial Data Partitioning Tree）。

常见的是二叉树、B-Tree、红黑树、B+Tree

无意间发现的一个宝藏网站，能够可视化的观看数据变化引起的数据结构变化过程。
数据结构动态模拟网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

1. 二叉查找树

二叉查找树：是n个有限元素的集合，该集合或者为空、或者由一个称为根（root）的元素及两个不相交的、被分别称为左子树和右子树的二叉树组成，是一种二分查找树，有很好的查找性能，相当于二分查找。

关于MySQL不使用二叉树的原因我们来看下一张图，有1~7七个数据，如果插入顺序为4、3、5、2、6、1、7，结果是一棵高度为4的排序树。

但如果数据插入顺序变成了1、2、3、4、5、6、7，结果就退化成了一个拥有7个结点的链表

二叉树存在的问题：二叉树树的深度依赖元素插入顺序，而且插入数据比较大时，会导致树的深度比较高。数据查询的时间主要依赖于磁盘IO的次数，二叉树深度越大，查找的次数越多，性能越差，最坏的情况为退化成了链表。

2. 红黑树（自平衡二叉查找树）

红黑树：红黑树是一种特化的AVL树（平衡二叉树），都是在进行插入和删除操作时通过特定操作保持二叉查找树的平衡，从而获得较高的查找性能。 它虽然是复杂的，但它的最坏情况运行时间也是非常良好的，并且在实践中是高效的： 它可以在O(log n)时间内做查找，插入和删除，这里的n是树中元素的数目。特点：左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。

红黑树插入1、2、3、4、5、6、7，七个元素图：

红黑树存在的问题：尽量不让一棵树的单边变得太长而退化成链表，能有效地减少高度，高度变小降低了查找I/O的次数，性能比二叉树要好。但红黑树一个节点只能有两个子节点，虽然平衡了链表退化问题，但高度总体来看还是太高。

3. B-Tree

B-Tree：B树简单地说就是多叉树，每个叶子会存储数据，和指向下一个节点的指针。

B-Tree插入1、2、3、4、5、6、7，七个元素图：

B-Tree特点

1）叶子结点具有相同深度

2）所有索引元素不重复

3）节点中的数据索引从左到右依次递增

从查询流程我们能看出，B-Tree的查询效率好像也并不比平衡二叉树高，但查询所经过的结点数量要比二叉树少了1个，在数据量大的时候意味着要少很多次的磁盘IO，这对性能的提升是很大的。前面对B-Tree操作的图我们能看出来，元素就是类似1、2、3这样的数值，但是数据库的数据都是一条条的数据，如果某个数据库以B-Tree的数据结构存储数据，那数据怎么存放的呢？

具体存放如下图：

在进行数据库存储中，我们把元素部分拆分成了key-data的形式，key就是数据的主键，data就是具体的数据。
B-Tree存在的问题：B-Tree在每一个节点存储了索引和数据，导致进行搜索的时候需要把索引和数据都加载到内存中，这样就不是很划算，内存资源这么宝贵，多存些索引岂不是更好。

4. B+Tree

4.1 B+Tree概述及特点

B+Tree： 在B-Tree基础上的一种优化，使其更适合实现外存储索引结构

特点
1）非叶子节点不存储数据，只存索引（冗余），这样可以保证存放更多的索引
2）叶子节点存储所有索引字段
3）叶子节点用指针连接，提高区间访问性能

4.2 B+Tree存放数据示例

二、MySQL存储引擎

存储引擎

Mysql常用存储引擎有两种：MyISAM和InnoDB，MySQL默认的存储引擎是MyISAM，其他常用的就是InnoDB，InnoDB比较常用

存储结构

MyISAM：每张表存在三个文件中，xxx.frm文件存储表定义；xxx.MYD文件存储表数据；xxx.MYI文件存储表索引。
InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB

存储空间

MyISAM：可被压缩，存储空间较小。索引和数据是分开的，并且索引有压缩，能使用更多的索引，所以内存使用率比较高。
InnoDB：需要更多的内存和存储，会在内存中建立专用的缓冲池用于高速缓冲数据和索引。索引和数据紧密捆绑，索引无压缩，所以体积相对比较大。

事务处理

MyISAM：不支持外键，不支持事务。
InnoDB：支持外键，支持事务。

锁

MyISAM：只支持表级锁，所有操作都会给表加锁，包括select。
InnoDB：支持表锁、行锁。行锁提高了多用户并发操作的性能，

索引

MyISAM

select * from tablename where id = 20
MyISAM查询流程：先去MYI文件查询索引数据，定位到 id = 20的叶子节点处，拿到20所对应的数据文件地址"0x6A"，然后从MYD文件中查找，根据此文件地址定位到具体id = 20的那一行记录。

InnoDB
注：InnoDB的主键索引为聚集索引（索引和数据存放在一起），而一个二级索引位置存放的数据为主键ID的值。

select * from tablename where id = 20
InnoDB主键索引查询流程：直接从IDB文件中查询id = 20的索引即可直接获取当前行数据。

select * from tablename where name = ‘Alice’
InnoDB非主键索引查询流程：直接从IDB文件中查询name = Alice 的索引，得到主键id = 18，再从当前文件中查找id为18的数据。

全文索引

MyISAM：支持 FULLTEXT类型的全文索引。不支持中文。
InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

优劣

MyISAM更适合(1)做很多count的计算。(2)插入不频繁，查询非常频繁。(3)没有事务
InnoDB更适合(1)可靠性要求高，要求事务。(2)更新和查询都相当频繁，表锁定的机会比较大。

三、SQL优化

1、应该尽量避免在where子句中进行null值判断

select id from t where num is null; 
改为将num的默认值设为0，确保num列没有null值 
select id from t where num='0'；
1
2
3

2、应尽量避免在where子句中使用 != 或 <> 操作符

select id from t where num<>'1';
改为将num值='1'的查出来，然后取不包含这些值的数据
select id from t t1 where not exists 
	(select id from t t2 where t2.num='1' and t1.id=t2.id)
1
2
3
4

3、应尽量避免在where子句中使用or来连接条件

select id from t where num = '10' or num = '20';
可以改成：
select id from t where num = '10'
union all
select id from t where num = '20'
1
2
3
4
5

4、not in要慎用，否则将会导致全表扫描

select id from t where num not in (1,2,3);
可以改为：
select id from t a where not exists 
(select b.id from t b where b.num in (1,2,3) and a.id=b.id)
或者
select id from t a where not exists 
(select id from (select id from t where num=1
union all 
select id from t where num=2
union all
select id from t where num=3) 
b where a.id=b.id)

in 子查询可以用exists代替，非子查询可以用union
1
2
3
4
5
6
7
8
9
10
11
12
13
14

5、左侧模糊查询也将导致全表扫描

select id from t where name like '%abc%';
尽量用右查询代替
select id from t where name like 'abc%';

数据量不大，可以用左查询，影响不大，数据量大的时候，建议用ElasticSearch等大量数据检索工具来做查询
1
2
3
4
5

6、应当尽量避免在where子句中对字段进行表达式的操作

select id from t where num/2 = 100
可以改为
select id from t where num = 100*2
1
2
3

7、应尽量避免在where子句中对字段进行函数操作

select id from t where substring(name,1,3) = 'abc' (name以abc开头的id)
应改为：
select id from t where name like 'abc%'
1
2
3

8、应尽量避免在where子句中等于号’='左面进行函数、算数运算或其他表达式运算
9、尽量使用数字类型 如果只含有数值信息的字段尽量不要设计成字符型，这会降低查询和连接的性能，并会增加存储开销，这是因为引擎在处理查询和连接时会逐个比较字符串中的每一个字符，而对于数字型而言，只需要比较一次就够了。
10、尽可能使用varchar代替char 因为首先变长字段存储空间小，可以节省空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然更高一些。
11、当数据量大时，避免使用where 1=1的条件 应在代码中进行逻辑判断，有条件就加where，没有就去掉
12、where 条件仅包含非前置索引字段将不会走索引

联合索引包含key_part1，key_part2，key_part3
select id from t where key_part2=2 and key_part3=3
不走索引，将全表扫描
1
2
3

13、隐式类型转换造成不使用索引

select id from t where num=123
num为字符串varchar类型时，给定的值123是数值类型，涉及隐式类型转换，不走索引
1
2

14、order by 条件与where条件一致否则不走索引

不走索引
select * from t order by num
走索引
select * from t where num>0 order by num
1
2
3
4

对于上面的语句，数据库的处理顺序是：
第一步：根据where条件和统计信息生成执行计划，得到数据。

第二步：将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。

第三步：返回排序后的数据。

当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

15、避免出现 select *，尽量查询明确使用的字段
16、mysql多表关联时，小表在前，大表在后,oracle相反

from 后的表关联查询是从左往右执行的（Oracle相反）

17、使用表别名，减少解析的时间并减少哪些友列名歧义引起的语法错误
18、调整Where字句中的连接顺序

MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集
oracle相反，过滤数据多的条件往后放

19、union all和union，在不需要做去重的时候，不要加all，加all会做数据的唯一性校验
20、合理使用分页

select t.* from t where t.a = 1000 and t.b=0 order by t.c asc limit 0,15
改为
select t.* from t left join 
    (select id from t where t.a = 1000 and t.b=0 order by t.c asc limit 0,15) a 
    on a.id=t.id where a.id is null;
1
2
3
4
5

---

附：索引脑图