MySQL基础学习
Source
Edit
History

MySQL数据库 - 初步认识

Catalogue
  1. 1. 初识MySQL数据库
    1. 1.1. 专有名词
    2. 1.2. 数据类型
    3. 1.3. 索引
      1. 1.3.1. 索引简介
      2. 1.3.2. 索引优劣
      3. 1.3.3. 索引结构
      4. 1.3.4. B+Tree索引的性能分析
      5. 1.3.5. 索引分类
    4. 1.4. MySQL常见存储引擎
    5. 1.5. 完整型约束
    6. 1.6. 查询优化
      1. 1.6.1. 单表使用索引以及常见的索引失效
      2. 1.6.2. 关联查询优化
      3. 1.6.3. 子查询优化
      4. 1.6.4. 排序分组优化
    7. 1.7. pymysql使用
  2. 2. 写在后面

摘要
有关MySQL数据库的基础知识，包括一些专用名称解析以及必会知识点，有待更正与完善～

初识MySQL数据库

我们在编写任何程序之前，都需要事先写好基于网络操作一台主机上文件的程序（socket服务端与客户端程序），于是有人将此类程序写成一个专门的处理软件，这就是mysql等数据库管理软件的由来，但mysql解决的不仅仅是数据共享的问题，还有查询效率，安全性等一系列问题。

总之，把程序员从数据管理中解脱出来，专注于自己的程序逻辑的编写~。

专有名词

数据（Data）：描述事物的符号记录称为数据，描述事物的符号既可以是数字，也可以是文字、图片，图像、声音、语言等，数据由多种表现形式，它们都可以经过数字化后存入计算机；在计算机中描述一个事物，就需要抽取这一事物的典型特征，组成一条记录，就相当于文件里的一行内容。

数据库（Databases，简称DB）：数据库库即存放数据的仓库，只不过这个仓库是在计算机存储设备上，而且数据是按一定的格式存放的；数据库是长期存放在计算机内、有组织、可共享的数据即可。数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享。

数据库管理系统（DataBase Management System 简称DBMS）在了解了Data与DB的概念后，如何科学地组织和存储数据，如何高效获取和维护数据成了关键~~

这就用到了一个系统软件—数据库管理系统如MySQL、Oracle、SQLite、Access、MS SQL Server

常见的数据库模型分为关系型数据库（MySQL、Oracle、SQL Server…）和非关系型数据库（文档存储数据库MongoDB；键值存储数据库Redis、Memcached、列存储数据库HBase、图形数据库Neo4J）

数据类型

  1. 数字：

    • 整型：

      tinyint [(m)] [unsigned] [zerofill]：小整数，数据类型用于保存一些范围的整数

      数值范围：

      ​ 有符号：-128 ～ 127

      ​ 无符号：0 ～ 255

      注意： MySQL中无布尔值，使用tinyint(1)构造。

      int [(m)] [unsigned] [zerofill]：整数，数据类型用于保存一些范围的整数

      数值范围：

      ​ 有符号：-2147483648 ～ 2147483647

      ​ 无符号：0 ～ 4294967295

      bigint [(m)] [unsigned] [zerofill]：大整数，数据类型用于保存一些范围的整数

      数值范围：

      ​ 有符号：-9223372036854775808 ～ 9223372036854775807

      ​ 无符号：0 ～ 18446744073709551615

    zerofill 使用说明：例如 int(5)表示当数值宽度小于 5 位的时候在数字前面加’0’填满宽度,如果不显示指定宽度则默认为 int(11)，zerofill默认为int(10)。

    注:当使用zerofill 时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为-128～+127，无符号为0~256。

    • 小数：m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30。

      ​ float [(M,D)] [unsigned] [zerofill]：单精度浮点数（非准确小数值）

      ​ 特性：随着小数的增多，精度变得不准确

      ​ double [(M,D)] [unsigned] [zerofill]：双精度浮点数（非准确小数值）。

      ​ 特性：随着小数的增多，精度比float要高，但也会变得不准确

      ​ decimal [(M[,D])] [unsigned] [zerofill]：准确的小数值。

      ​ m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。

      ​ 特性：随着小数的增多，精度始终准确。decaimal能够存储精确值的原因在于其内部按照字符串存储。

  2. 字符串

    • char（10）：定长 不够的给你补上。特点：简单粗暴，浪费空间，存取速度快。
    • varchar：变长 传几个给你写几个，但不要超过字符个数精准。特点：节省空间，但存取速度慢。
    • 大于255字符，可以考虑将文件路径存放到数据库中，即数据库中只存路径或者url。

  3. 时间类型

    • year ：年
    • date ：年月日
    • time ：时分秒
    • datetime ：年月日时分秒

  4. 枚举类型与集合类型：字段的值只能在给定范围中选择，如单选框，多选框

    • enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
    • set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3…）

索引

索引简介

索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

所以，索引就是一种帮助MySQL高效获取数据的排好序的快速查找的数据结构。

索引优劣

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

优势：类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本；通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息；实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

索引结构

各种结构探寻：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

  • 二叉树（红黑树：也叫二叉平衡树）
  • HASH
  • B - TREE
    • 度(Degree)-节点的数据存储个数
    • 叶节点具有相同的深度
    • 叶节点的指针为空
    • 节点中的数据key从左到右递增排列
  • B + TREE（B - TREE演变）
    • 非叶子节点不存储data,只存储key,可以增大度
    • 叶子节点不存储指针
    • 顺序访问指针,提高区间访问的性能

B+Tree索引的性能分析

一般使用磁盘I/O次数评价索引结构的优劣，根据索引获取一条数据使用的I/O次数越少越优。

  • 预读：磁盘一般会顺序向后读取一定长度的数据(页的整数倍)放入内存
  • 局部性原理：当一个数据被用到时,其附近的数据也通常会马上被使用
  • B+Tree节点的大小设为等于一个页,每次新建节点之间申请一个页的空间，这样就保证了一个节点物理上页存储在一个页里就实现了一个节点的载入只需一次I/O
  • B+Tree的度d一般会超过100,因此h非常小(一般为1到3之间,极限到5)

一般操作系统的最小存储单元为页,1页大小为4K

'SHOW GLOBAL STATUS like 'Innodb_page_size’语句可以查看mysql文件页大小

索引分类

  • 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
  • 唯一索引：索引列的值必须唯一，但允许有空值
  • 主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引
  • 复合索引：即一个索引包含多个列

MySQL常见存储引擎

  • MyISAM索引实现(非聚集)
    • MyISAM索引文件和数据文件时分离的
  • InnoDB索引实现(聚集)
    • 表数据文件本身就是按B+Tree组织的一个索引结构文件
    • 聚集索引 - 叶节点包含了完整的数据记录
    • InnoDB表必须有主键,并且推荐使用整型的自增主键
    • 非主键索引结构叶子节点存储的时主键值(一致性和节省存储空间)

完整型约束

完整型约束的作用：用于保证数据的完整性和一致性

  1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
  2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
  3. 是否为key ：主键 primary key、外键 foreign key、索引 (index,unique…)
  • PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录（不为空且唯一）
  • FOREIGN KEY (FK) 标识该字段为该表的外键
  • NOT NULL 标识该字段不能为空
  • UNIQUE KEY (UK) 标识该字段的值是唯一的
    • 单列唯一 ：在字段后加unique，指的是这个字段的记录是唯一的不能重复
    • 联合唯一 ：例如ip和端口均是唯一的，这种叫联合唯一
  • AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
  • DEFAULT 为该字段设置默认值

查询优化

单表使用索引以及常见的索引失效

  • 全值匹配
  • 最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
  • 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
  • 存储引擎不能使用索引中范围条件右边的列；
  • mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描；
  • is not null 也无法使用索引,但是is null是可以使用索引的；
  • like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作；
  • 字符串不加单引号也会引起索引失效

建议：

对于单键索引，尽量选择针对当前query过滤性更好的索引；在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引；

在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面；

关联查询优化

  1. 保证被驱动表的join字段已经被索引
  2. left join时，选择小表作为驱动表（也就是主表），大表作为被驱动表（从表）
  3. inner join时，mysql会自动将小结果集的表选为驱动表
  4. 子查询尽量不要放在被驱动表，有可能使用不到索引
  5. 能够直接多表关联的尽量直接关联，不使用子查询

子查询优化

尽量不要使用not in 或者 not exists，用left outer join on xxx is null 替代；

排序分组优化

～待补充

pymysql使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
import pymysql

user=input('user>>: ').strip()
pwd=input('password>>: ').strip()

# 建立链接
conn=pymysql.connect(
    host='192.168.1.123',
    port=3306,
    user='root',
    password='123',
    db='db10',
    charset='utf8'
)

# 拿到游标
cursor=conn.cursor()

# 执行sql语句
# sql='select * from userinfo where user = "%s" and pwd="%s"' %(user,pwd)
# print(sql)
# rows=cursor.execute(sql)

sql='select * from userinfo where user = %s and pwd=%s'
#由execute作为拼接，不用你自己去拼接了，在拼接过程中给你过滤掉这种非法操作
rows=cursor.execute(sql,(user,pwd)) #提交给游标执行  execute这个接口拿到的是2 rows in set (0.00 sec) 2那个行数，如果值不为0说明就输对了

cursor.close()
conn.close()

# 进行判断
if rows:
    print('登录成功')
else:
    print('登录失败')

写在后面

以上是学习MySQL数据库的一下学习笔记，记录下来以供随时翻阅，查漏补缺，感谢阅读～