一周学习小总结，MySQL进阶~

太过爱你忘了你带给我的痛 2023-02-21 06:16 22阅读 0赞

![format_png][]

今天的这篇推文，是对前六天MySQL学习的一个进阶学习和小总结，前两天是端午节，大家过得如何？休息之余，不要忘记坚持学习，加油！

## 不同岗位对MySQL的技术要求 ##

对于不用的岗位，我们对MySql的技术要求不同。

数据分析岗位，侧重查询和多表关联的复杂查询。对于数据分析来说，主要掌握查询，取数据，不关心性能。

数据库开发，程序员岗，注重设计系统，包括表的设计，增删改查，懂得数据库进行查询的原理，依据统计信息对查询进行性能优化，处理非常大的数据量的的查询，SQL的语言的复杂性较数据分析师的高。

DBA-数据库管理员，连接、配置服务器，保证SQL的服务性能、稳定性，修改慢查询，此外，数据库的集群管理，机房服务器连接的一致性管理也是重点。

## Python操作MySQL ##

主要使用库：Python DB-API

### 常用模块： ###

*  MySQLdb ：MySQL-Python，底层C语言实现，在Python2版本中使用多
 *  mysql-connector : MySqL官方提供，使用不是很友好
 *  pymysql：使用Python实现，Python三版本以上使用，**主要使用pymysql**

### 基本操作 ###

连接数据库

import pymysql
    db = pymysql.connect(host='',port=3306)

获取游标

cs = db.cursor()

Cursor是游标,指针的意思，通过cursor读取mysql中的数据，存放在计算机内存中。然后再用python读取 curosr，Cursor可以看做一个中间载体，摆渡船的作用。

选择数据库

db.select_db('db_name')

执行SQL

sql = 'show databases'
    result = cs.execute(sql)
    dbs = cs.fetchall()

## 索引 ##

在前几天的课程中，我们讲过索引，这里再复习一下，如果进行工作面试，对MySql的索引考察会是一块重点。

索引有一下几大特点：

*  对表中一列或多列的值进行排序
 *  定义一种存储在磁盘上的结构，通过索引的结构可以将数据快速从磁盘中读取到内存中
 *  通过索引可以快速检索到数据
 *  数据库内置的存储引擎来实现检索

索引类型，上周的课程提到过，可以翻回去复习。

对于索引的存储结构，这里有两个重要的概念

*  非聚簇索引 (Non-clustered Index)：索引树的叶子节点不存放数据，但存有储数据的位置信息，数据和索引分离
 *  聚簇索引（Clustered Index）：数据存储在索引树的叶子节点上

### 注意事项 ###

*  执行查询时，MySQL只能使用一个索引
 *  创建索引，可以提高查询速度，但有数据被添加和删除时，需要更新索引，降低了插入和修改数据的速度。
 *  索引列的数据类型存储大小越小越好，如int类型的索引列查询时优于char字符串类型
 *  尽量避免NULL，可以在创建列的时候限制，避免NULL

### InnoDB、MyISAM索引引擎 ###

*  MyISAM引擎：使用B+tree的索引结构，叶子节点存放是一个指针，记录地址，指向数据文件中这一索引所在的行的数据的位置。查询的步骤，根据查询语句的条件，在索引树中检索到叶子节点，根据叶子节点中的地址信息，找到数据文件中索引行的其他列的数据。-类似打开字典，在字典拼音目录中找到这一发音-索引，根据发音所指的页码-地址再去数据库文件-字典正文中提取信息，**索引结构与数据存储分离**。
 *  InnoDB引擎：与MyISAM索引和数据分开存放的不同是，InnoDB引擎数据文件本身就是一个索引，按照B+Tree的结构组织存放，叶子节点包含全部数据的信息，即数据存储在索引上。优势是对于按照主键索引有序排列的行，数据存储在索引上，相较于索引和数据存储分离的结构，可以更快速的读取索引所在行的全部数据，不用根据地址再去数据文件中寻找数据，提高查询的速度，但不总是速度会提高，查询速度还与查询语句的内容，数据库的数据量，索引的类型和主键的设置有关。实际中，大部分使用的是InnoDB引擎。此外，InnoDB使用行级锁，支持事务，和外键查询。

### 图形示意： ###

MyISAM引擎

![format_png 1][]

主键上带有索引，存放在B+Tree结构中，第三行是叶子节点，存放的是地址信息，根据地址信息指向磁盘中数据库文件中这一索引所在的行，找到行后再返回数据，磁盘进行了多次的I/O,速度较慢， 是非聚簇索引的结构。

InnoDB引擎

![format_png 2][]

对比MyISAM，InnoDB 采用的是聚簇索引结构，在第三行的叶子节点上，数据就直接存放在叶子节点上，首先根据主键索引，找到索引后，可以直接读取索引行的数据，减少了磁盘的I/O，提高了查询速度。

## SQL优化 ##

### 优化的一般原则 ###

一般来说查询性能低下的原因是访问了太多的数据：

*  是否请求了不需要的数据
 *  使用where子句进行过滤
 *  使用临时表，将需要查询的数据汇总到临时表中
 *  批量处理法，对于需要处理大量数据的语句，批量进行处理

### 索引的优化 ###

*  使用自增ID作为主键，业务主键用unique key
 *  一般来说，性别，status，type，类别这类字段，值得分布很窄，不适合单独作为索引字段
 *  索引并不是越多越好，无用的索引要删除
 *  不要使用%xxx%，前后双通配符，这种模糊匹配，会导致全表扫描，降低查询速度

## 写在最后 ##

对于刚起步学习数据分析的同学们，掌握好查询是首要任务，可以不考虑性能，平时多在网上搜索资料，最好是带有答案讲解的题目，做题练习，这里推荐：

SQL面试50题

https://www.bilibili.com/video/BV1q4411G7Lw?from=search&seid=818452521832640792。

从事大数据，ETL工程师数据开发等岗位，需要了解数据库的内部原理，对慢查询进行优化，建表时为了提高查询的性能，索引的设置非常关键。对业务进行分析时，往往需要新建表，保留原有的数据，建表需要结合实际的业务需求，有兴趣的同学可以参考以下资料加深对数据库知识的学习。

*  MySQL 官方手册：https://dev.mysql.com/doc/refman/8.0/en/
 *  《高性能MySQl》
 *  《高可用MySQL》
 *  《深入理解MySQL核心技术》
 *   MySQL的源码

往期推荐

[Day1 | 认识MySQL][Day1 _ _MySQL][Day2 | 数据库操作-DDL][Day2 _ _-DDL][Day3 | 数据库操作DML][Day3 _ _DML][Day4 | 数据库操作DCL][Day4 _ _DCL]

[Day5 | 数据库操作-TCL][Day5 _ _-TCL]

[Day6 | 数据库操作-索引补充、非空表达式、CASE WHEN函数][Day6 _ _-_CASE WHEN]

![format_png 3][]

### 每一个在看都是一种支持 ###

![format_png 4][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi9xaWFTbDNSVDFSYzFWVTREQk8xanBxQjZCUGNvaHh0SkFZUVBBUGQ0YXJXSkdLdUNiTlBnN2cwSWlhTWJOdzhQZ2NjaWNqVUxRVk5hTDgzVVNLTmVrcFJpYWcvNjQw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzBEQWo0QWRqOGJ0dFl6TEVETEFQanpNRWt0d0pHMGhhQmxhd1dEaEpQbk9iZFJUdXRYUkQyeUltUkhRS2psb0JpYnpPRDc5YVNyUFNBLzY0MA?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzBEQWo0QWRqOGJ0dFl6TEVETEFQanptU0RmVUVsYXZicFAxajhmWjdqMmpFWHJPWlV4VnBmaWM1ZzZPakV2eTVpYVRqaWM0ZDNYRWRPbHcvNjQw?x-oss-process=image/format,png
[Day1 _ _MySQL]: https://blog.csdn.net/weixin_40247412/article/details/106913422
[Day2 _ _-DDL]: https://blog.csdn.net/weixin_40247412/article/details/106935361
[Day3 _ _DML]: https://blog.csdn.net/weixin_40247412/article/details/106953644
[Day4 _ _DCL]: https://blog.csdn.net/weixin_40247412/article/details/106964907
[Day5 _ _-TCL]: http://mp.weixin.qq.com/s?__biz=MzIwMTMxMTQxNQ%3D%3D&chksm=8157eaadb62063bbb5495c8be2559e88e1d7140e01d8b7a1562c2e9cf265a6255421ad833473&idx=1&mid=2454495093&scene=21&sn=a937f81cd75e98b90575a632421cf8a2#wechat_redirect
[Day6 _ _-_CASE WHEN]: https://blog.csdn.net/weixin_40247412/article/details/106990982
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9xaWFTbDNSVDFSYzJWQ2oyRmhpY2ljYm9maWFoNGFpY2xQM291ZVBoa2thbVlFZ1FwbWVLc2JWanplSXNpYXFpYU44dWE0WWJnamc4SG1HV3paVnZpYndJejFnczJnLzY0MA?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi96S0J3RGR1OFU4RVVoMmNOWHZaUlprMUJVTG15ZVluaktpYnlLYng5U0gwZ0FvWGlhZFZoVHRhNDFaaDFJdDg4aWFLbnJlWDBBdExBcGRVR2dWZnJZUlZUZy82NDA?x-oss-process=image/format,png