MySql常见知识梳理

MySql简介

数据库系统（Database system）= 数据库管理系统（DBMS,Database Management System）+数据库（Database）

数据库管理系统（DBMS）可分为两类：一类为基于共享文件系统的DBMS,另一类为基于客户机-服务器的DBMS。

Mysql是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。

MySQL的优点：

MySQL是开源的，所以不需要支付额外的费用。

MySQLMysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。

MySQL使用标准的SQL数据语言形式。

MySQL可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。

MySQL对PHP有很好的支持，PHP是目前最流行的Web开发语言。

MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。

MySQL是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。

数据库对于JAVA开发的重要程度

业务开发：懂基本SQL语句的编写。

SQL优化：懂索引，懂引擎。

分库分表，懂主从，懂读写分离。

安全，懂权限，懂备份，懂日志。

索引的概念，常见索引类型，索引的优缺点

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。（百度百科）

从上面的定义中我们可以知道，索引是一种存储结构；是数据库表中一列或若干列值的集合，或者是指向表中数据页物理标识的逻辑指针清单。

索引在数据库中叫做键（Key)，是存储引擎用于快速找到记录的一种数据结构，当表中数据量增大的时候，索引对性能的影响愈发重要，因此索引优化是查询性能优化最有效的手段。

创建表:

CREATE TABLE `example_table` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID', `name` varchar(10) NOT NULL COMMENT '姓名', `age` smallint(5) unsigned NOT NULL COMMENT '年龄', `card_id` smallint(5) unsigned NOT NULL COMMENT '学生卡号', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '表更新时间', PRIMARY KEY (`id`), UNIQUE KEY `idx_card_id` (`card_id`), KEY `idx_age` (`age`,`create_time`) ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

B-Tree索引(技术上的B+树):对于上图表中索引结构，给出可能的索引数据结构。

 B-Tree可以加快访问数据的速度，存储引擎不会进行全表扫描而是从索引的根节点进行搜索，向下查找。B-Tree对索引列是按照顺序存储的，适合范围查找。B-Tree索引对多个值的排序根据创建语句定义的顺序查找，B-Tree索引只适用全键值，键值范围或者键值前缀查找，键值前缀查找适合最左前缀查找。 全键值：比如查找age = 18, create_time = 2012-05-25 21:18:12 的人。最左前缀：所有age = 18的人，只是用索引的第一列。匹配列前缀：比如查询age以1开始的所有人，也是只索引的第一列。匹配范围： 比如age > 10 and age < 100的人，也只是索引的第一列。精确怕匹配前一列后一列进行范围查找: 比如 age = 18 and create_time >= 2012-05-25 21:18:12 and create_time <= 2019-05-25 21:18:12 ，索引的前一列准确查询后一列范围查询。只访问索引查询：select age / select age , create_time from example_table where ....，查询字段即使索引，在后续的优化中会对，只查询索引，避免查询数据行的情况 "覆盖索引”；如果不是按照索引的最左前缀查找无法使用索引，因为索引的排序规则根据创建表语句中索引的定义顺序来排序。不能跳过列进行查询，即index(a,b,c),如果查询a = ? and c = ?只能用到第一列索引(a);如果查询中，某个列进行范围查询，后面的列都不能使用索引查询。

Hash索引:（Key-Value)

通过哈希函数计算hash值在对应的槽位存放指向对应行数据的指针。 哈希索引只保存哈希值和行指针，而不存放字段值，所以不能使用索引中的值来避免读取行。哈希索引数据不是根据索引值顺序存储的，所以无法排序。哈希索引也不支持部分索引列匹配，因为哈希值是根据所有哈希内容来计算哈希值的。哈希索引只支持等值比较查询，包括=，IN()，<=>，不支持任何的范围查询。访问哈希索引的数据非常快O(1)，但是如果哈希冲突严重的话，必须遍历链表来查找对应的数据，代价非常大。

全文索引&空间数据索引：可以自己探索下，全文索引多用于搜索引擎（Solr等）

索引优点总结：

 索引的列可以保证行的唯一性，生成唯一的rowId

索引大大减少了服务器需要扫描的数据量，可以有效缩短数据的检索时间

索引可以帮助服务器避免排序和临时表

索引可以将随机I/O改成顺序I/O

索引缺点总结：

创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大

创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间）

会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长。

聚簇索引，非聚簇索引的区别？底层实现原理以及两者的优缺

       聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。 一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。

      聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

      MYISAM是按列值与行号来组织索引的。它的叶子节点中保存的实际上是指向存放数据的物理块的指针。 从MYISAM存储的物理文件我们能看出，MYISAM引擎的索引文件（.MYI）和数据文件(.MYD)是相互独立的。而InnoDB按聚簇索引的形式存储数据，所以它的数据布局有着很大的不同

     INNODB的二级索引与主键索引有很大的不同。InnoDB的二级索引的叶子包含主键值，而不是行指针(row pointers)，这减小了移动数据或者数据页面分裂时维护二级索引的开销，因为InnoDB不需要更新索引的行指针。

    INNODB和MYISAM的主键索引与二级索引的对比：

    InnoDB的的二级索引的叶子节点存放的是KEY字段加主键值。因此，通过二级索引查询首先查到是主键值，然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。而MyISAM的二级索引叶子节点存放的还是列值与行号的组合，叶子节点中保存的是数据的物理地址。所以可以看出MYISAM的主键索引和二级索引没有任何区别，主键索引仅仅只是一个叫做PRIMARY的唯一ID.

设计表规约以及提高性能策略（SQL优化）_中型数据表

 数据库设计方面 建表约束 必须有字段id（主键id，一般设置为自增（步数为1），当并发性需求较高的时候，不建议自增，可以自定义实现机制）, create_time（建表时间）， update_time(更新时间，设置为自动更新)。表名和字段名必须使用小写字母或数字，禁止数字开头，同时使用下划线分割不同的单词，Windows环境下Mysql忽略大小写，Linux下大小写是有区分的，因此为了统一，全部是小写字母加数字，同时命名不能用复数名词，表明和字段名应用来表示含义。表示有是否含义的字段时，必须用is_XXX来表示字段名，用tinyint unsigned（1是， 0否)，当然也可以使用char(1)来表示'Y'（是）, 'N'（否）。同时禁止使用MySql保留字段，desc，range等，用过的同学应该知道这个会报错。主键的索引名必须为pk_xxx，唯一索引名为uk_xxx，普通索引名为idx_xxx。对于数字类型的定义比如bigint(1)与bigint(5)的原理和占用空间都是一样的，不同的长度是不同工具对数字的表形式不同，两者没有任何差别。小数类型用decimal来储蓄，尽量根据单位换算用Bigint unsigned等整形来表示，因为MySql小数的运算相比整数消耗资源会更多一点。char用来表示定长字符，varchar用来储蓄可变字符，根据需求进行字段长度的设置，可以多余，但要适量。对于业务上不发生改变的字段设为枚举，在MySql中枚举的储蓄也更加的紧凑，查询的效率也更好点。对于需要用有限数字来表示字段信息时，不要用‘0’来表示某种状态，某些情况下JAVA中部分字段的默认值为‘0’，可能会发生一些不必要的错误。索引约束 业务上唯一的字段，索引必须为唯一索引。比如user_id（假设每个用户只有一个id，若此字段需要建立索引，必须使用唯一索引。在varchar字段上建立索引时，索引长度必须指定，根据业务情况的区分度来设置，一半20的区分度为90%。建立组合索引时，区分度高的在左边。同时，索引的数量单表至多6个，因为索引的维护（频繁的删除和插入操作），会带来比较高的资源消耗，因此不要在频繁删除和更新的字段加索引。案列表 CREATE TABLE `product` ( `id` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增ID', `uuid` CHAR ( 32 ) NOT NULL COMMENT '产品UUID', `user_uuid` CHAR ( 32 ) NOT NULL COMMENT '用户UUID', `pack_amount` SMALLINT ( 6 ) NOT NULL COMMENT '打包价格\r\n', `term` INT ( 10 ) UNSIGNED NOT NULL COMMENT '借款限期，单位-天', `remark` VARCHAR ( 5 ) DEFAULT '' COMMENT '备注', `product_type` TINYINT ( 4 ) NOT NULL COMMENT '产品类型（1：赚，2：借款，3：转售）', `status` TINYINT ( 4 ) UNSIGNED NOT NULL COMMENT '产品状态', `expend_rate` DECIMAL ( 16, 2 ) NOT NULL COMMENT '利率', `effective_time` datetime NOT NULL COMMENT '标的发布的有效期（时间戳）', `create_time` datetime NOT NULL COMMENT '创建时间', `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY ( `id` ), UNIQUE KEY `idx_uuid` ( `uuid` ), KEY `idx_user_uuid` ( `user_uuid` ), KEY `idx_create_time` ( `create_time` ) ) ENGINE = INNODB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8 COMMENT = '产品基础信息表';

 

字段的选取（最小原则） 越小的数据类型，通常处理的速度更快，因为占用的磁盘，内存，CPU缓存更少，处理时需要的CPU周期也更短。对于不确定的数值范围，选取最小的可储蓄类型。 数据类型数值范围字节常用场景tinyint unsigned0-2551表示人的年龄等smallint unsigned0-655352乌龟年龄等int unsigned0-42.9亿4恐龙化石时间等bigint unsigned0-10^198太阳的年龄char(N)最大为N个字节N对于固定长度的字段（比如人名)varchar(N)最大为N个字节，N尽量小于255/5000N根据业务需求datatime1601-9999年8常用的日期储蓄timestamp1601-9999年4有自动更新机制，update_time简单原则 简单的数据类型需要的CPU周期更短，整形比字符整体的操作代价少很多，建议IP的储蓄用整型。尽量避免使用NULL 通常情况下，字段设置为NOT NULL，NULL为默认属性，对于查询来说有NULL的列，MySql优化很难，查询起来会更麻烦，占用的储蓄空间可更多SQL查询优化 查询优化，在where以及order by 涉及的字段上建立索引。避免使用where 属性 = null，这样MySQL会进行全局查询。避免在where使用 != 或 < > 等字段。避免where 与 or 进行连接查询，使用 union all 进行代替.避免使用 in 和 not in 等，连续的范围尽量用between避免使用like "%XX%".避免where 的字段使用表达式操作.where num/2 = x等避免where的字段使用函数操作。字段含有大量的相同值比如(sex..)，索引的优化没有效果，，MySQL根据字段的内容进行优化，因此进行建立索引;索引提高的select 的效率，但降低了insert ,update的效率，建议索引最多建立6个.字段的内容如果都为数字的话，避免使用字符作为属性，字符根据每个字符进行比较，数字只会比较一次;select * from table，尽量使用字段代替 * ,减少查询量，即使需要查询字段也要用全部字段代替 *。维护索引的代价比较高，主键索引尽量使用自增形式（数据库自增/业务实现自增ID），避免不必要的索引页调整。。 数据库版本，服务器内存与性能对比（X轴表示内存容量GB，Y轴每秒查询次数

为什么选择B+树而不是 二叉排序树，Hash,B树，查找树，AVL树，优缺点是什么呢？

二叉排序树：根节点大于左边小于右边，极端情况下，比如有序的插入，就会出现退化的现象，二叉排序树退化成链表，因此推出平衡树。AVL，红黑树：保证了树的平衡性，查询性能稳定提高，但是因为都是单节点，数据量大的时候，树的高度会非常高，查询效率会减少，因此提出多路查询。B树：多路搜索树，他每个节点可以拥有多余两个孩子节点，M路的B树最多有M个子节点，此时树的高度降低，查询效率同时提高，如果无线多路则退化成有序数组，因其子节点是索引，对于范围查询时需要部分中序遍历来完成，效果不好。Hash:  其优缺点可以查看Hash索引类型的介绍。       聚簇索引的优缺点： 优点： B+-tree的查询效率更加稳定  由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。                                                                                                                    B+-tree的磁盘读写代价更低  B+-tree的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。  举个例子，假设磁盘中的一个盘块容纳16bytes，而一个关键字2bytes，一个关键字具体信息指针2bytes。一棵9阶B-tree(一个结点最多8个关键字)的内部结点需要2个盘快。而B+ 树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候，B 树就比B+ 树多一次盘块查找时间(在磁盘中就是盘片旋转的时间)。自适应Hash，InnoDB会对经常访问的索引创建哈希表，加快访问速度。缺点： 聚簇索引提高了IO密集型应用的性能，但如果数据都放在内存中，访问的顺序就不再重要，聚簇索引也没有优势。插入速度依赖于插入的顺序。按照逐渐顺序插入是加载数据到InnoDB表中速度最快的方式，因为主键的值是顺序的，InnoDB把每一条疾苦都存储在一条记录的后面，当达到页的填充因子时（15/16），下一条记录就会写入新的页中。如果使用UUID,InnoDB无法简单的将新行插入到索引的最后，而是为新行寻找合适的位置，通常在已有数据的中间位置。 写入的目标页可能已经刷新到磁盘上，并且从缓存中移除，或者没有被加载进缓存中，InnoDB不得从磁盘将页导入内存，导致大量的随机IO。写入是乱序的，InnoDB不得不频繁的做页分裂操作，以便为新的行分配空间，页分裂导致大量的数据移动。二级索引可能比想象的大，因为二级索引叶子节点包含引用的主键列。

文献：

《高性能MySQL-第三版》https://www.mysql.com/why-mysql/benchmarks/