最新博文

• 主表和从表（父表和子表）

  MySQL

  1.主表：在数据库中建立的表格即Table，其中存在主键(primary key)用于与其它表相关联，并且作为在主表中的唯一性标识。

  2.从表：以主表的主键（primary key）值为外键 (Foreign Key)的表，可以通过外键与主表进行关联查询。从表与主表通过外键进行关联查询。

  总结：主表不定义外键。

  风口下的猪2019-06-09【MySQL】

  阅读更多

• 组合索引—-最左原则

  MySQL

         通常我们在建立联合索引的时候，也就是对多个字段建立索引，相信建立过索引的同学们会发现，无论是oralce还是mysql都会让我们选择索引的顺序，比如我们想在a,b,c三个字段上建立一个联合索引，我们可以选择自己想要的优先级，a、b、c,或者是b、a、c 或者是c、a、b等顺序。为什么数据库会让我们选择字段的顺序呢？不都是三个字段的联合索引么？这里就引出了数据库索引的最左前缀原理。

         比如：索引index1:(a,b,c)有三个字段，我们在使用sql语句来查询的时候，会发现很多情况下不按照我们想象的来走索引。

          select * from table where c = '1'          这个sql语句是不会走index1索引的，select * from table where b =‘1’ and c ='2' 这个语句也不会走index1索引。

          什么语句会走index1索引呢？

          答案是：

  select * from table where a = '1'

  select * from table where a = '1' and b = ‘2’

  select * from table where a = '1' and b = ‘2’  and c='3'

         其实这和索引的查找原理有关，索引的查找是按照B+tree有顺序地进行的。如果忽略左边靠前的字段去直接查找后面的字段，那么前面字段的查询就会变成无序的。

  风口下的猪2019-06-09【MySQL】

  阅读更多

• 索引的分类及使用

  MySQL

  一.索引分类

  mysql的索引分为单列索引(主键索引,唯索引,普通索引)、组合索引和全文索引.

  1.单列索引:一个索引只包含一个列,一个表可以有多个单列索引；

  2.组合索引:一个组合索引包含两个或两个以上的列；

  3.全文索引:如果文本中出现多个一样的字符,而且需要查找的话,全文索引就起作用了。

  二.【创建索引】

  这里要注意数据库中CREATE和ALTER的区别

  CREATE用于创建新的数据，ALTER是在数据已经存在的情况下更新数据（ALTER与普通的update不同的是ALTER强调更新数据结构，而update强调更新数据内容）

  indexName（索引名）    tableName（数据表名）   columnName（字段名）

  1.单列索引

  1.1普通索引

  （1）独立创建：CREATE INDEX indexName ON tableName(columnName);

  （2）修改：ALTER TABLE tableName ADD INDEX indexName(columnName);

  （3）建表时创建：

  CREATE TABLE 'table'(

  'id' int(11) NOT NULL AUTO_INCREMENT,

  'title' char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

  'content' text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

  'time' int(10) NULL DEFAULT NULL ,

  PRIMARY KEY ('id'),

  INDEX indexName (title(length))

  );

  1.2唯一索引

  （1）独立创建：CREATE UNIQUE INDEX indexName ON tableName (columnName);

  （2）修改：ALTER TABLE tableName ADD UNIQUE indexName(columnName);

  （3）建表时创建：

  CREATE TABLE 'table' (

  'id' int(11) NOT NULL AUTO_INCREMENT ,

  'title' char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

  'content' text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

  'time' int(10) NULL DEFAULT NULL ,

  PRIMARY KEY ('id'),

  UNIQUE indexName (title(length))

  );

  1.3主键索引（主键亦是一种索引）

  （1）修改：ALTER TABLE tableName ADD PRIMARY KEY (columnName);

  （2）建表时创建（先定义）：

  CREATE TABLE biao(

  'id' int(4) PRIMARY KEY, //直接在字段定义时约束

  'name' varchar(8)

  );

  （3）建表时创建（后指定）

  CREATE TABLE biao(

  'id' int(4),

  'name' varchar(8),

  PRIMARY KEY('id') //所有字段定义完，最后指定

  );

  2.全文索引

  （1）独立创建：CREATE FULLTEXT INDEX indexName ON tableName(columnName);

  （2）修改：ALTER TABLE tableName ADD FULLTEXT indexName(columnName);

  （3）建表时创建：

  CREATE TABLE 'table' (

  'id' int(11) NOT NULL AUTO_INCREMENT ,

  'title' char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

  'content' text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

  'time' int(10) NULL DEFAULT NULL ,

  PRIMARY KEY ('id'),

  FULLTEXT (content)

  );

  3.组合索引

  （1）独立创建： CREATE INDEX indexName ON tableName(columnName1, columnName2, columnName3);

  （2）修改：ALTER TABLE tableName ADD INDEX indexName(columnName1,columnName2,columnName3);

  （3）建表时创建：

  CREATE TABLE 'table'(

  'id' int(11) NOT NULL AUTO_INCREMENT,

  'title' char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

  'content' text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

  'time' int(10) NULL DEFAULT NULL ,

  PRIMARY KEY ('id'),

  INDEX indexName (title(length),time(length))

  );

  三.【删除索引】

  可利用ALTER TABLE或DROP INDEX语句来删除索引。

  （1）直接用DROP删除:  DROP INDEX indexName ON tableName;

  （2）ALTER删除普通和组合索引：ALTER TABLE tableName DROP INDEX indexName;

  （3）ALTER删除主键索引：ALTER TABLE tableName DROP PRIMARY KEY;

  （其他同创建时同理，即将ON换成DROP即可）

  四.【查看索引】

  show indexName from tableName;

  show keyName from tableName;

  风口下的猪2019-06-09【MySQL】

  阅读更多

• 索引原理—-B+tree

  MySQL

         索引实际上也相当于一个小型的数据表，对于建有索引的数据表来说，索引就像目录，能帮助我们快速找到想要查询的数据。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。但同时也得注意，由于索引的本质是一段小的数据储存，对数据表的任何数据插入、更新等操作，都要同时备份到索引当中，因此会减缓这些操作的速度。

  一.基本查找原理

         本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

  二 磁盘IO与预读

         考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

  三、索引的数据结构（b+tree）

         任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。

  

         如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

  3.1.b+tree的查找过程

         如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

  3.2.b+树性质

  （1）索引字段要尽量的小

         通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

  （2）索引的最左匹配特性（即从左往右匹配）

         当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

   

  总结：

  1.索引的功能就是加速查找；

  2.mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能。

  风口下的猪2019-06-09【MySQL】

  阅读更多