五、MySQL (Inno DB)
1、聚簇索引与非聚簇索引
聚簇索引和非聚簇索引的定义:
- 聚簇索引:表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引,叶子结点即存储了真实的数据行,聚簇索引的叶子节点就是数据节点,不再有另外单独的数据页。 在一张表上最多只能创建一个聚集索引,因为真实数据的物理顺序只能有一种。
- 非聚簇索引:表数据存储顺序与索引顺序无关。对于非聚集索引,叶结点包含索引字段值及指向数据页数据行的逻辑指针,其行数量与数据表行数据量一致。非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。
- 总结一下:聚集索引是一种稀疏索引,数据页上一级的索引页存储的是页指针,而不是行指针。而对于非聚集索引,则是密集索引,在数据页的上一级索引页它为每一个数据行存储一条索引记录。
- 都使用 B+ 树作为数据结构
- 聚簇索引中数据存在主键索引的叶子结点中,得到 key 即得到 data ;非聚簇索引的数据存在单独的空间。
- 聚簇索引中辅助索引的叶子结点存的是主键;非聚簇索引中叶子结点存的是数据的地址;
- 聚簇索引的优势是找到主键就找到数据,只需一次磁盘 IO ;当 B+ 树的结点发生变化时,地址也会发生变化,这时非聚簇索引需要更新所有的地址,增加开销。
2、为何使用 B 树做索引而不是红黑树?
索引很大,通常作为文件存储在磁盘上面,每次检索索引都需要把索引文件加载进内存,所以磁盘 IO 的次数是衡量索引数据结构好坏的重要指标。应用程序在从磁盘读取数据时,不只是读取需要的数据,还会连同其他数据以页的形式做预读来减少磁盘 IO 的次数。数据库的设计者将每个节点的大小设置为一页的大小,同时每次新建节点时都重新申请一个页,这样检索一个节点只需要一次 IO,根据索引定位到数据只需要 h- 1(h 为 B 树高度,根节点常驻内存) 次 IO,而 d (度,可以理解为宽度)与 h 称反比,即 d 越大,高度就越小,所以树越扁,磁盘 IO 次数越少,即渐进复杂度为 logdN ,这也是为什么不选择红黑树做索引的原因。前面可以得出结论,d 越大,索引的性能越好。节点由 key 和 data 组成,页的大小一定,key 和 data 越小,d 越大。B + 树去掉了节点内的 data 域,所以有更大的 d , 性能更好。
B树&B+树&红黑树:
B树(Balance Tree)是一种多路平衡查找树(多路:每个节点的子节点可以多于两个),一个m阶的B树具有如下几个特征(阶:树中所有孩子结点个数的最大值称为该树的阶):
1.根结点至少有两个子女。
2.每个中间节点都包含k-1个元素和k个孩子,其中 m/2 <= k <= m
3.每一个叶子节点都包含k-1个元素,其中 m/2 <= k <= m
4.所有的叶子结点都位于同一层。
5.每个节点中的元素从小到大排列,节点当中k-1个元素正好是k个孩子包含的元素的值域分划。
B+树(B+ Tree)是B树的plus版本,在数据库应用中有着比B树更高的查询性能,他俩有许多共同点,但是B+树在B树基础上具备一些新的特征,一个m阶的B+树具有如下几个特征:
1.有k个子树的中间节点包含有k个元素(B树中是k-1个元素),每个元素不保存数据,只用来索引,所有数据都保存在叶子节点。
2.所有的叶子结点中包含了全部元素的信息,及指向含这些元素记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接。
3.所有的中间节点元素都同时存在于子节点,在子节点元素中是最大(或最小)元素。
红黑树(Red&Black Tree)除了符合二叉查找树(BST)的特性外,其的节点会分出一个存储位来标记其颜色(标颜色是为了更好地通过颜色相关的规则来检验树的平衡性),比BST多出如下的附加特性:
1.节点是红色或黑色。
2.根节点是黑色。
3.每个叶子节点都是黑色的空节点(NIL节点)。
4.每个红色节点的两个子节点都是黑色。(从每个叶子到根的所有路径上不能有两个连续的红色节点)
5.从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。
3、最左前缀原则
在 MySQL 中,可以指定多个列为索引,即联合索引。比如 index(name,age,sex,phonenumber) (这就是sql语句的写法!),最左前缀原则是指查询时精确匹配到从最左边开始的一列或几列(name;name&age),就可以命中索引。如果所有列都用到了,顺序不同,查询引擎会自动优化为匹配联合索引的顺序,这样是能够命中索引的。
4、什么情况下可以用到 B 树索引
(1) 定义有主键的列一定要建立索引。因为主键可以加速定位到表中的某行
(2) 定义有外键的列一定要建立索引。外键列通常用于表与表之间的连接,在其上创建索引可以加快表间的连接
(3) 对于经常查询的数据列最好建立索引。
① 对于需要在指定范围内快速或频繁查询的数据列,因为索引已经排序,其指定的范围是连续的,查询可以利用索引的排序,加快查询的时间
② 经常用在 where
子句中的数据列,将索引建立在 where
子句的集合过程中,对于需要加速或频繁检索的数据列,可以让这些经常参与查询的数据列按照索引的排序进行查询,加快查询的时间。
5、事务隔离级别
- Read uncommitted 读未提交,可能出现脏读,不可重复读,幻读。
- Read committed 读提交,可能出现不可重复读,幻读。
- Repeatable read 可重复读,可能出现脏读。
- Serializable 可串行化,同一数据读写都加锁,避免脏读,性能不忍直视。
Inno DB 默认隔离级别为可重复读级别,分为快照度和当前读,并且通过行锁和间隙锁解决了幻读问题。
6、MVCC (多版本并发控制)
- 实现细节(这不就是乐观锁思想吗,CAS)
- 每行数据都存在一个版本,每次数据更新时都更新该版本。
- 修改时 Copy 出当前版本随意修改,各个事务之间互不干扰。
- 保存时比较版本号,如果成功(commit),则覆盖原记录;失败则放弃 copy(rollback)。
- Inno DB 实现
在 InnoDB 中为每行增加两个隐藏的字段,分别是该行数据创建时的版本号和删除时的版本号,这里的版本号是系统版本号(可以简单理解为事务的 ID),每开始一个新的事务,系统版本号就自动递增,作为事务的 ID 。通常这两个版本号分别叫做创建时间和删除时间。(时间得考虑到时间的细度,就是更新的时间间隔,不然太长的话会显示出版本号相同)
7、MYSQL数据库的一些基础操作
/*websites 表名 NAME alexa url country 字段*/ SELECT * FROM websites; /* 查询表所有数据 */ SELECT NAME FROM websites; /* 查询表字段数据 */ SELECT * FROM websites where name = "广西"; /* 查询表字段下条件数据 */ SELECT * from websites where name like "_o%"; /* 模糊查询表下数据 */ SELECT * FROM websites where id BETWEEN "1" AND "5"; /* 查询表下字段范围数据 */ SELECT * FROM websites WHERE name in ("广西","百度"); /* 查询表字段下固定条件数据 */ SELECT DISTINCT country FROM Websites; /* 查询去重值 */ SELECT * FROM Websites WHERE country = "CN" AND alexa > 50; /*查询表下范围条件数据*/ SELECT * FROM Websites WHERE country = "USA" OR country="sh"; /* 查询表下条件不同值 */ SELECT * FROM Websites ORDER BY alexa; /* 查询表下值排序结果 */ SELECT * FROM Websites ORDER BY alexa DESC; /* 查询表下排序结果降序 */ SELECT * FROM Websites LIMIT 2; /* 查询表下范围数据 */ SELECT name as zzz from websites; /*别名查询表下数据*/- 关联查询:select 字段 from 表1 left join 表2 on 条件 (一般为表1与表2的关联条件)
- 左关联: left join ... on ...;右关联: right join... on ...
单表查询 --------------------- mysql> select * from user; +----+----------+------------+------+----------+ | id | username | birthday | sex | address | +----+----------+------------+------+----------+ | 1 | 王五 | 2017-11-25 | 3 | 南阳 | | 10 | 张三 | 2014-07-10 | 1 | 北京市 | | 16 | 张小明 | NULL | 1 | 河南郑州 | | 22 | 陈小明 | NULL | 1 | 河南郑州 | | 24 | 张三丰 | NULL | 1 | 河南郑州 | | 25 | 陈小明 | NULL | 1 | 河南郑州 | | 26 | 王五 | NULL | NULL | NULL | | 29 | 小黑 | 2017-11-26 | NULL | NULL | | 30 | 抖森 | 2017-11-25 | 1 | 山村 | +----+----------+------------+------+----------+ 9 rows in set (0.03 sec) mysql> select * from orders; +----+---------+---------+---------------------+------+ | id | user_id | number | createtime | note | +----+---------+---------+---------------------+------+ | 3 | 1 | 1000010 | 2015-02-04 13:22:35 | NULL | | 4 | 1 | 1000011 | 2015-02-03 13:22:41 | NULL | | 5 | 10 | 1000012 | 2015-02-12 16:13:23 | NULL | +----+---------+---------+---------------------+------+ 3 rows in set (0.03 sec) -------------------------- 关联查询 ------------------------- 左关联 ------------------------ mysql> select user.* ,orders.number from user left join orders on user.id = orders.user_id; +----+----------+------------+------+----------+---------+ | id | username | birthday | sex | address | number | +----+----------+------------+------+----------+---------+ | 1 | 王五 | 2017-11-25 | 3 | 南阳 | 1000010 | | 1 | 王五 | 2017-11-25 | 3 | 南阳 | 1000011 | | 10 | 张三 | 2014-07-10 | 1 | 北京市 | 1000012 | | 16 | 张小明 | NULL | 1 | 河南郑州 | NULL | | 22 | 陈小明 | NULL | 1 | 河南郑州 | NULL | | 24 | 张三丰 | NULL | 1 | 河南郑州 | NULL | | 25 | 陈小明 | NULL | 1 | 河南郑州 | NULL | | 26 | 王五 | NULL | NULL | NULL | NULL | | 29 | 小黑 | 2017-11-26 | NULL | NULL | NULL | | 30 | 抖森 | 2017-11-25 | 1 | 山村 | NULL | +----+----------+------------+------+----------+---------+ 10 rows in set (0.04 sec) ----------------------------- 右关联 ----------------------------- mysql> select user.* ,orders.number from user right join orders on user.id = orders.user_id; +----+----------+------------+-----+---------+---------+ | id | username | birthday | sex | address | number | +----+----------+------------+-----+---------+---------+ | 1 | 王五 | 2017-11-25 | 3 | 南阳 | 1000010 | | 1 | 王五 | 2017-11-25 | 3 | 南阳 | 1000011 | | 10 | 张三 | 2014-07-10 | 1 | 北京市 | 1000012 | +----+----------+------------+-----+---------+---------+ 3 rows in set (0.05 sec)GROUP BY 语法:在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。
SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;mysql> set names utf8; mysql> SELECT * FROM employee_tbl; +----+--------+---------------------+--------+ | id | name | date | singin | +----+--------+---------------------+--------+ | 1 | 小明 | 2016-04-22 15:25:33 | 1 | | 2 | 小王 | 2016-04-20 15:25:47 | 3 | | 3 | 小丽 | 2016-04-19 15:26:02 | 2 | | 4 | 小王 | 2016-04-07 15:26:14 | 4 | | 5 | 小明 | 2016-04-11 15:26:40 | 4 | | 6 | 小明 | 2016-04-04 15:26:54 | 2 | +----+--------+---------------------+--------+ 6 rows in set (0.00 sec)mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP; +--------------------------+--------------+ | coalesce(name, '总数') | singin_count | +--------------------------+--------------+ | 小丽 | 2 | | 小明 | 7 | | 小王 | 7 | | 总数 | 16 | +--------------------------+--------------+ 4 rows in set (0.01 sec)