今天的这篇推文,是对前六天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引擎
主键上带有索引,存放在B+Tree结构中,第三行是叶子节点,存放的是地址信息,根据地址信息指向磁盘中数据库文件中这一索引所在的行,找到行后再返回数据,磁盘进行了多次的I/O,速度较慢, 是非聚簇索引的结构。
InnoDB引擎
对比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的源码
往期推荐
Day2 | 数据库操作-DDL
Day3 | 数据库操作DML
Day4 | 数据库操作DCL
Day5 | 数据库操作-TCL
Day6 | 数据库操作-索引补充、非空表达式、CASE WHEN函数