mysql数据库整理

最新推荐文章于 2024-08-29 21:59:59 发布

wcl1800

最新推荐文章于 2024-08-29 21:59:59 发布

阅读量247

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/wcl1800/article/details/114401186

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一四大特性

1): https://www.cnblogs.com/dwxt/p/8807981.html
2): https://www.cnblogs.com/dwxt/p/8807790.html
3): https://www.cnblogs.com/dwxt/p/8807899.html

二数据库索引

2.1 什么是索引

索引是用来存储数据表中指定列值的一种数据结构，可以有效提高检索速度。

2.2 索引的数据结构类型

1).B-Tree（B-树）–是最常用的用于索引的数据结构

  特性：⑴时间复杂度低， 查找、删除、插入操作都可以在对数时间内完成。

             ⑵ 在B-Tree中的数据是有序的。

2).哈希索引–寻找值时哈希表效率极高，对于比较字符串是否相等的查询能够极快的检索出值

  特性：⑴哈系索引是将列的值作为索引的键值（key），和键值相对应实际的值（value）是指向该表中相应行的指针

  缺点：⑵无顺的数据结构，对于对比数据大小时间长短等方面不如B-tree效率高 。

3).R-Tree

  特性：常用来解决空间问题（比如“查询出所有距离我两公里之内的星巴克”）

4).位图索引

  特性：这类索引适合放在包含布尔值(true 和 false)的列上。

2.3 索引类型

主键索引（PRIMAY KEY）

唯一索引（UNIQUE）

常规索引（INDEX）

全文索引（FULLTEXT）

normal即常规的B-Tree索引，字段值没限制

unique是唯一索引，列中字段值不允许重复

2.4 索引是如何提高查询性能的

: 有序性：假设我们在表Employee的 Employee_Name这一列上创建一个B-Tree索引。这意味着当我们用SQL(SELECT * FROM Employee WHERE Employee_Name = ‘Jesus’) 查找姓名是‘Jesus’的雇员时，不需要再扫描全表。而是用索引查找去查找名字为‘Jesus’的雇员，因为索引已经按照按字母顺序排序。索引已经排序意味着查询一个名字会快很多，因为名字少字母为‘J’的员工都是排列在一起的。

2): 指针：索引中除了存储列的值，还存储着一个指向在行数据的索引。也就是说，索引中的Employee_Name这列的某个值（或者节点）可以描述为 (“Jesus”, 0x82829)， 0x82829 就是包含 “Jesus”那行数据在硬盘上的地址。如果没有这个引用，你就只能访问到一个单独的值（“Jesus”），而这样没有意义，因为你不能获取这一行记录的employee的其他值-例如地址（address）和年龄（age）。

3): 数据库自动识别: 当这个SQL （SELECT * FROM Employee WHERE Employee_Name = ‘Jesus’ ）运行时，数据库会检查在查询的列上是否有索引。假设Employee_Name列上确实创建了索引，数据库会接着检查使用这个索引做查询是否合理。（补充但是有些场景下，使用索引比起全表扫描会更加低效）

2.5 创建索引

创建标准索引： CREATE INDEX 索引名 ON 表名 (列名) TABLESPACE 表空间名;

创建唯一索引: CREATE unique INDEX 索引名 ON 表名 (列名) TABLESPACE 表空间名;

创建组合索引: CREATE INDEX 索引名 ON 表名 (列名1,列名2) TABLESPACE 表空间名;

创建反向键索引: CREATE INDEX 索引名 ON 表名 (列名) reverse TABLESPACE 表空间名;

2.6 索引使用原则

索引字段建议建立NOT NULL约束

经常与其他表进行连接的表，在连接字段上应该建立索引；

经常出现在Where子句中的字段且过滤性很强的，特别是大表的字段，应该建立索引；

可选择性高的关键字，应该建立索引；

可选择性低的关键字，但数据的值分布差异很大时，选择性数据比较少时仍然可以利用索引提高效率

复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

A、正确选择复合索引中的第一个字段，一般是选择性较好的且在where子句中常用的字段上；

B、复合索引的几个字段经常同时以AND方式出现在Where子句中可以建立复合索引；否则单字段索引；

C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；

E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

频繁DML的表，不要建立太多的索引；

不要将那些频繁修改的列作为索引列；

2.7 索引什么情况下会失效

1、索引会占用空间 - 你的表越大，索引占用的空间越大

2、性能损失（主要值更新操作），当你在表中添加、删除或者更新行数据的时候，在索引中也会有相同的操作

3、如果条件中有or，即使其中有条件带索引也不会使用（要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引）

4、.like查询是以%开头
5、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
6、如果mysql估计使用全表扫描要比使用索引快,则不使用索引

查看索引的使用情况：

show status like ‘Handler_read%’;
注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数

handler_read_rnd_next:这个值越高，说明查询低效

7、not in ,not exist

8、单独引用复合索引里非第一位置的索引列.

9、使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引.
错误的例子：select * from test where round(id)=10;
说明，此时id的索引已经不起作用了

正确的例子：首先建立函数索引，
create index test_id_fbi_idx on test(round(id));
然后 select * from test where round(id)=10; 这时函数索引起作用了

10、<> 或者单独的>,<

https://blog.csdn.net/u013093761/article/details/99311264

2.8 索引的底层原理

https://www.zhihu.com/question/26113830

三 mysql的存储原理

存储过程是一个可编程的函数, 它在数据库中创建并保存, 当存储时会把sql语句解析成对应的指令并进行优化,最后存储. 这个存储过程类似于编程中的面向对象. 具有以下优点:
1): 存储过程能实现较快的执行速度
2): 存储过程允许标准组件式编程
3): 存储过程可以用流程控制语句编写,灵活
https://blog.csdn.net/kimboyang/article/details/79151674

四 mysql数据库引擎

常用的引擎主要有两个myisam和innodb, 它们之间的异同点主要有以下方面:
1): innodb支持事务,myisam不支持,事务是一种高级处理方式,在增删改中出现错误可以回滚,myisam不可以
2): myisam查询速率更快,因此更适合以查询为主的应用, innodb适合频繁修改涉及到安全性较高的应用
3): innodb支持外键,myisam不支持
4): myisam是默认引擎, innodb需要修改配置

多种引擎的对比如下:
在这里插入图片描述

4.1 Innodb

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。InnoDB主要特性有

4.2 Myisam

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。MyISAM主要特性有

4.3 Memory

MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问

五优化查询

1): 存储引擎选择: 如果数据表需要事务处理,应该考虑使用innodb, 如果不需要事务处理, 使用默认存储引擎myisam
2): 考虑在where及order by涉及的列上建立索引
3): 避免在where语句中对null, not null等判断,否则将导致引擎放弃索引使用全表扫描
4): 避免在where语句中使用!= <>等判断,否则将导致引擎放弃索引使用全表扫描
5): 避免在where语句中使用or来连接条件
6): update语句只更新1,2个字段,不要全部update全部字段
7): 尽量使用数字型字段
8): 只查询自己需要的字段