MySql数据库

最新推荐文章于 2024-11-01 12:20:35 发布

工大枸杞微铺

最新推荐文章于 2024-11-01 12:20:35 发布

阅读量75

点赞数

分类专栏： Mysql 文章标签：数据库

本文链接：https://blog.csdn.net/weixin_45466462/article/details/118974767

版权

Mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

引擎

InnoDB、MyISAM
分类：
• Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
• MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
• MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

区别：
• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
• InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
• MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
• InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

索引

索引是一种数据结构。数据库中的一个排序的数据结构。
通俗讲索引就相当于表的目录，对表中内容建立索引形成目录，方便查找。

原理：把无序的数据变成有序的查询
 把创建了索引的列的内容进行排序
 对排序结果生成倒排表
 在倒排表内容上拼上数据地址链
 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

设计原则：

适合索引的列是出现在where子句中的列，或者连接子句中指定的列
基数较小的类，索引效果较差，没有必要在此列建立索引
使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可

创建原则：
1）最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2）较频繁作为查询条件的字段才去创建索引
3）更新频繁字段不适合创建索引
4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
6）定义有外键的数据列一定要建立索引。
7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
8）对于定义为text、image和bit的数据类型的列不要建立索引。

实质：索引是一个文件，是要占据物理空间的。

目的：协助快速查询

数据结构：B+树（innoDB引擎，mysql默认的算法）、hash。

B树和B+树的不同：B+树的叶子节点之间是通过指针连接起来的。可以用于范围和模糊查询。在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
B+树的叶子节点有一条链相连，而B树的叶子节点各自独立
hash实现对于单条数据命中速度快，不能范围查询。

分类：聚簇索引、非聚簇索引。皆为B+树数据结构。
在这里插入图片描述
使用场景：

聚簇索引：数据的存储和索引放在一快
非聚簇索引：B+树的叶子节点不存储数据，存储的是数据行的地址。即根据索引找到数据行地址然后去磁盘取数据。
优势：
1、查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率
要高
2、聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的
3、聚簇索引适合用在排序的场合，非聚簇索引不适合
劣势：
1、维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插
入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成
碎片。使用独享表空间可以弱化碎片
2、表因为使用UUId（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面
更慢，所以建议使用int的auto_increment作为主键
3、如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键
值，会导致非叶子节点占用占用更多的物理空间

类型：

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引
全文索引：是目前搜索引擎使用的一种关键技术。
• 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引