深入浅出Mysql的读书总结 |
您所在的位置：网站首页 › 深入浅出数据分析读后感 › 深入浅出Mysql的读书总结 |
深入浅出Mysql的读书总结
|
序言
mysql的名字取源于一个mysql开发者女儿的名字缩写。MY 第0章 MySQL实用技巧查询mysql的配置参数可以使用show variables like 'log_%' 查询mysql的运行状态：show status like 'innodb_row_lock%' 锁的状态 查看mysql的事务隔离级别：show variables like '%isolation%' 第一章 MySQL的安装对于MySQL的安装，在k8s大行其道的时代下，我们当然是使用docker镜像安装啦 docker run -d -p3306:3306 -eMYSQL_ROOT_PASSWORD=123456 --name mysql 第二章 SQL基础MySQL在标准的SQL语法上扩展了自己的使用方法。 SQL的全称是Structure Query Language的缩写。 SQL语句分为以下三种： DDL（data definition language）：对数据库、表、列、索引等数据库对象的定义。create、drop、alterDML（data manipulation language）：增删改查数据。DCL（data control language）：对数据库、表。列、索引对象的访问权限和安全级别的定义。grant、revokemysql默认的数据库： infomation_schema:存储系统的一些数据库对象信息。比如用户表信息，列信息，权限信息，字符集信息，分区信息cluster：系统的集群信息mysql：系统的用户权限信息test：系统自动创建的测试数据库DDL 创建数据库create databse name 执行语句前需要选择数据库use name 显示所有数据库show databases 显示当前数据库的所有表show tables 删除数据库drop database name 创建表: create table log( id bigint name varchar(10) ... )查看表结构：desc log 删除表：drop table log 修改表字段类型：alter table log modify name varchar(20) 添加表字段:alter table log add age int(3) 删除表字段:alter table log drop age 修改表名字:alter table log change name user_name int(3) 修改表字段位置：添加和修改语句都可以修改表字段的位置，语法如下：alter tablle log add password varchar(20) [first | after col_name] 表改名：alter table log rename loglog DML 插入:insert into log(name,age) values('1',2),('3',4)。insert into log(name,age) select name age from log 更新:update log set name = '8848', age=33 where id = 32 删除:delete from log where id = 123 查询:select * from log where id > 32 order by id desc limit 0 32 聚合语法： select * from tablename where condition group by filed with rollup having condition with rollup表示对分类聚合后的结果再汇总 表连接：left join、right join、inner join（join）、自然连接 子查询 记录联合：union（去重）、union all（直接聚合，不去重）DCL 授权语句：grant select,insert on name.* to 'root'@'localhost' identified by '123' MySQL的最新消息网站:https://www.mysql.com/news-and-events/newsletter 第三章 MySQL支持的数据类型选择字段的数据类型的准则： 在满足业务情况下，使用占用存储小的类型MySQL支持的数值数据类型 在sql中使用运算符的准则： 尽量不使用运算符，把计算转移到应用中。只使用能减少查询数据量的运算符在开发速度和性能之间取舍 支持运算符： 比较运算符  是针对null安全判断的等于between and 都是闭区间
逻辑运算符 运算符作用NOT 或！逻辑非AND 或&&逻辑与or或||逻辑或XOR逻辑异或 null跟任何值进行逻辑运算都是null位运算 在sql中使用函数的准则： 尽量不要在执行频繁的sql中使用函数尽量在应用中处理数据数据库的资源是宝贵的
日期函数 日期时间格式 date_format(now(), '%Y-%m-%d %H-%i-%s' ) 时间间隔类型 date_add(date,INTERVAL expr type) type就是下表的时间间隔类型
流程函数 MYSQL Administrator是MySQL公司提供图形管理工具 MySQL Query brower是官方提供的客户端查询工具 第七章 表类型（存储引擎）的选择插件式的存储引擎是MySQL数据库最重要的特性之一。 用户应该根据如何存储和索引数据、是否使用事务选择存储引擎 注！ 这是基于MySQL5.0版本的比对 MyISAM 存储一共三个文件：.frm（存储表定义）、.MYD（存储数据）、MYI（存储索引） 支持3种不同的存储格式：静态表（类似memcache的固定大小的chunk）、动态表（删除和更新会产生碎片）、压缩表（占用空间小） Innodb 具有提交、回滚、崩溃恢复能力的事务安全 写的效率差一些，占用更多的磁盘空间以保存数据和索引（相对于MyISAM） 支持外键（但是不推荐使用） 存储表和索引有两种方式： 共享表空间存储：表结构存储在.frm文件，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间多表空间存储：表结构存储在.frm文件，每个表的数据和索引单独保存在.ibd文件。 分区表的话，每个分区对应单独的.ibd文件Memory 表结构对应一个.frm文件，数据存储在内存。默认是hash索引。 应该没有分布式存储好用。但是分布式存储不支持sql。 如何选择 MyISAM适合读和插入为主的场景（日志） Innodb适合事务场景 第八章 选择合适的数据类型char vs varchar MyISAM是固定长度，推荐使用char Innodb的数据行使用指向数据列值的头指针。 使用varchar为好 text vs blob text支持存储字符，blob支持存储二进制 推荐使用单独的表存储 浮点数和定点数 需要小数点推荐使用decimal 或者乘以对应倍数 变成整数存储。 日期类型选择 date、time、datetime、timestamp 遵循最小存储量原则 只要年月日选择date只要时分秒选择timetimestamp只支持到2038年。并且支持多时区，每个时区的人看到的都是对的。占用4个字节datetime支持到9999年，但是占用字节是8个 第九章 字符集从通用性考虑：选择utf8mb4就对了。完美兼容各种字符 第十章 索引的设计和使用MyISAM和Innodb存储引擎的表默认创建的都是BTREE索引 索引列应该是where条件中的列或者连接子句的列可以使用唯一 索引就使用唯一索引可以使用短索引来增加查询索引的速度，减少IO和内存空间不要过度使用索引，不要超过5个innodb存储引擎的表，记录默认安装一定的顺序报错。优先级是：主键 ->唯一索引->自动生成内部类。按这个顺序查找，找到某一列后，按此列的顺序排放。索引会保存主键的键值，然后通过再一次io找到整条记录hash索引适合等值查询，不适合范围查询，但是速度是常数级别。btree天然支持排序，支持范围查询innodb的自适应的hash索引 第十一章 视图什么是视图？ 视图（view）是一种虚拟存在的表。行和列数据来自定义视图的查询中使用的表，并且是在使用时动态生成的。 视图的优点 对使用者来说简单，访问权限受控，数据独立 创建视图 注意 from关键字后面不能包含子查询。需要子查询的使用再建一个视图替代 CREATE OR REPLACE VIEW log_view AS select * from log where age > 18删除视图：drop view log_view 查询视图的创建语句：show create view log_view 第十二章 存储过程和函数存储过程和函数是事先经过编译并存储在数据库中的一段sql语句的集合 存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用 IN、OUT、INOUT 类型，而函数的参数只能是 IN 类型的。如果有函数从其他类型的数据库迁 移到 MySQL，那么就可能因此需要将函数改造成存储过程。
触发器是与表有关的数据库对象 创建触发器 CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt trigger_time 是触发时间，可以是before和aftertrigger_event可以是insert update 或者deletetrigger_stmt是被begin和end包括的一段sql可以使用old和new来引言触发器中发送变化的记录内容.删除触发器：drop trigger [schema_name] trigger_name 触发器的执行顺序是befor触发器、行操作、after触发器的顺序。任何一步错误，不会执行借下的语句。如果开启了事务那么整个事务会回滚 第十四章 事务控制和锁定语句控制事务的语法 START TRANSACTION | BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET AUTOCOMMIT = {0 | 1}innodb支持XA事务，两阶段提交。连接mysql的客户端作为事务管理器（TM），MySQL服务器作为资源管理器（RM） 第15章 SQL中安全问题SQL注入问题 一张表 CREATE TABLE user ( userid int(11) NOT NULL auto_increment, username varchar(20) NOT NULL default '', password varchar(20) NOT NULL default '', PRIMARY KEY (userid) ) TYPE=MyISAM AUTO_INCREMENT=3 ;登陆sql语句：select * from user where username = '$username'and password = $password 如果直接是字符串拼接：那么使用angle'or' 1=1 可以实现登陆。或者使用angel'/*和angel'# 直接将后面的语句注释掉 使用prepareStatement+bind-value来防止sql注入问题 第十六章 SQL Modesql model用来解决一下几类问题 设置sql model 完成不同严格程度的数据校验。ansi模式，符合sql语法。查看sql_mode：select @@sql_mode 比如：在mysql8.0的情况下，如果插入的字段查出限制的长度会直接报错。这就是sql mode的限制。 第十七章 SQL技巧使用正则：col regexp exp
提取随机行 select * from users order by rand()使用group by和with rollup子句做统计 使用with rollup 会在最后多一行统计聚合项的统计值。 with rollup和order by是互斥的 select sum(salary),department from salarys group by department select sum(salary),department from salarys group by department with rollupinndodb支持外键，但不推荐使用 对于大小写敏感性问题：建议是 表名大写、字段名大写、别名大写并且sql语句中也是大写。 第十八章 SQL优化使用explain分析sql explain select * from us_log where id regexp '^5|1' +--+-----------+------+----------+----+-------------+----+-------+----+-------+--------+-----------+ |id|select_type|table |partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra | +--+-----------+------+----------+----+-------------+----+-------+----+-------+--------+-----------+ |1 |SIMPLE |us_log|NULL |ALL |NULL |NULL|NULL |NULL|3961910|100 |Using where| +--+-----------+------+----------+----+-------------+----+-------+----+-------+--------+-----------+ select_type:simple（简单表，即不使用表连接或者子查询）、primary（主查询，即外层的查询）、union（union中的第二个或者后面的查询语句）、subquery（子查询中的第一个select）table：输出结果集的表partitions：分区type：表示表的连接类型，性能由好到坏的连接类型为 system（表中只有一行）const（单表中最多只有一个匹配行，unique index和primary key）eq_ref（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用primary key）ref（与eq_ref类似，区别在于不是使用primay key或者unique index，是使用了普通索引）ref_or_null（与ref类似，区别在于条件中包含对null的查询）index_merge（索引合并优化）unique_subquery（in的后面是一个查询主键字段的子查询）index_subquery（与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询）range（单表中的范围查询）index（对于前面的每一行都是查询索引得到的数据）all（对于前面的每一行都是通过全表扫描来得到数据的） possible_keys：查询时候可能使用的索引key：表示实际使用的索引key_len：索引字段的长度ref：索引行的级别rows：扫描行的数量（估算值）filtered：结果值占读取行的比例extra：执行情况的说明和描述索引匹配规则： 最左前缀like：后面是常量并且只有%号不在第一个字符对大文本只能全文索引索引列使用is null存在索引但不使用索引 mysql 估计使用索引比全表扫描更慢，则不使用索引。 key_part1均匀分布在1-100之间，下列查询中使用索引就不是很好where条件中不使用“=”进行索引列，不会使用索引。or条件，如果后面的列没有索引就需要全表扫描不是索引列的第一部分like是以%开始字符串列请使用引号包住常量值优化插入 大量插入会在values后面带上多个 使用join优化子查询 bad case explain select * from sales2 where company_id not in ( select id from company2）good case explain select * from sales2 left join company2 on sales2.company_id = company2.id where sales2.company_id is nullor优化 对于or的列都建立索引 使用索引hint hint explain select * from sales2 use index (ind_sales2_id) where id = 3IGNORE INDEX explain select * from sales2 ignore index (ind_sales2_id) where id = 3force index。给了用户强制选择索引的权利 explain select * from sales2 where id > 0 第十九章 优化数据库对象拆分提供表的访问效率 垂直拆分：把常用的放到一张表，不常用的列放到一张表，减少查询io。需要join水平拆分：减少数据和索引。 常常是按时间维度拆分为热库和历史库和归档库。或者是热库的表还是过大常常会按某种规则把单表拆分到多表。使用中间表提高统计查询速度。 常常有查询过去一段时间（一周、一个月、三个月）按某个维度统计值的需求。 把数据搬移到中间表并在中间表进行统计。 第二十章 锁问题MySQL不同的存储引擎支持不同级别的锁。 锁粒度特点表锁并发度低，锁冲突概率高，锁定粒度大行锁并发度高，锁冲突概率小，锁定粒度小页锁介于中间MySQL有读写锁。 读读并发，读写和写写互斥 Innodb 事务： 事务特性描述原子性（atomicity）全部执行或者不执行一致性（consistent）从一个状态到另一个状态隔离性（isolation）隔离机制，依赖隔离级别持久性（durable）永久性修改并发事务的问题和事务隔离级别 两种类型的行锁 共享锁：行锁。加锁前需要获取表的意向共享锁排他锁：行锁。加锁前需要获取表的意向排他锁意向共享锁：表锁意向排他锁：表锁 查看行锁竞争情况：show status like 'innodb_row_lock%';
行锁 innodb的行锁是给索引上的索引项加锁。不走索引项就会使用表锁。 因为是加锁是给索引项的，所以使用相同的索引键会出现锁冲突 建议不同事务使用不同的索引来锁定不同的行 在锁冲突分析中注意sql执行计划，MySQL会决定是否使用索引。不使用就会导致表锁 间隙锁 使用范围条件检索数据，并请求共享或排他锁时，innnodb会对符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但不存在的记录，叫做间隙（gap），innodb也会对这个间隙加锁。这种锁机制就是间隙锁（next-key lock） 除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，innodb也会使用间隙锁 表锁 insert into xx select * from bb：会获取bb的表锁并且给主键索引加入共享锁。 mysql内部的行锁是逐行加锁的。多行同时加锁可能导致死锁 binlog binlog是记录执行成功的insert update和delete等更新数据的sql语句，并由此实现mysql的恢复和主从复制 binlog是按事务提交的先后顺序记录的，恢复也是按这个顺序进行的 不同隔离级别下的一致性读及锁的差异 RR的隔离级别下 普通select 无锁，多版本并发读 select 。。。 lock in share model是共享锁 范围搜索是共享锁加间隙锁 select … for update 是排他锁。 范围搜索是排他锁+间隙锁 delete update insert 是排他锁。 范围搜索是排他锁+间隙锁 innodb innodb的锁是逐步获取的，满足了死锁产生的条件 有死锁检测机制，检测到后会使回退代价小的事务释放锁并回退 死锁 死锁产生的四要素：互斥条件：一个资源每次只能被一个进程使用；请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；不剥夺条件:进程 已获得的资源，在末使用完之前，不能强行剥夺；循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系 在应用中，不同的程序（接口）会并发存取多个表，应以相同的顺序访问表，可大大降低死锁的可能性批量处理数据时候，事先对数据排序，保证每个线程按固定的顺序来处理记录，可大大降低死锁的可能性要更新记录应该申请足够级别的锁（排他锁），不应该先申请共享锁，再申请排他锁。对不存在的记录加锁时 多个线程都能获取到锁 第二十一章 MySQL server调优主要是innodb一些参数上的调优。 innodb_buffer_pool_size：缓存表数据和索引数据的内存池最大的字节数innodb_flush_log_at_trx_commit: 事务提交后的刷盘策略。 innodb_flush_log_at_trx_commit说明0事务提交时不做处理，每秒刷新到磁盘1事务提交时写到缓冲日志文件，并且刷新到磁盘日志文件2事务提交时写到缓冲日志文件，每秒刷新到磁盘日志文件 innodb_lock_wait_timeout：行锁导致的死锁会自动检测。 表锁导致的死锁只能靠获取锁超时时间来终止。默认值是50sinnodb_log_buffer_size：日志缓冲（binlig?redolog?undolog?all?）的大小。每秒会刷新到文件，不需要太大。 第二十二章 磁盘I/O问题磁盘阵列（RAID：Redundant Array of Inexpensive Disks）：按照一定策略将数据分布到若干物理磁盘上，增加了数据可靠性，提供了数据读写的性能，实现了数据的“并行”读写 RAID 级别特性优点缺点raid0条带化，按一定策略将数据分别写在各个磁盘读写快，无消耗可靠性不高，磁盘利用率100%raid1磁盘镜像，数据同时写入到两个磁盘，读从任一一个磁盘即可可靠性高，读性能提供磁盘利用率50%raid10磁盘镜像的基础上使用条带化，每个镜像的数据写到多个磁盘结合了raid0 和 raid1优点磁盘利用率50% 增加运维成本raid5类似raid0，但是一个数据都有自己的校验纠错数据，分别写到多个磁盘比raid0增加了可靠性，比raid1和raid10降低了存储适合数据安全要求不高的场景。日志等土豪选择raid10 穷苦大众选择raid5 禁止操作系统更新文件的atime属性 linux/unix下读取文件后会写会读取的时间，影响IO性能。修改文件系统配置文件/etc/fstab 指定noatime 使用裸设备来存放innodb的表空间 第二十三章 应用优化使用连接池 减少频繁创建连接的消耗。hikari是最近比较火的连接池。 批量查询使用查询缓存（数据一致性问题）使用mysql主从提升读性能使用分布式数据库或者客户端hash推荐自定义id字段，不使用自增字段 第二十六章 MySQL日志二进制日志 binlog 记录了所有的DDL和DML语句 慢查询日志 文件名：在数据目录下的host_name-slow.log 文件格式：纯文本 范围：语句执行时间（获取表锁定的时间不包含）超过long_query_time（默认10s）指定的sql 第二十九章 MySQL复制复制是基于binlog的， 从服务器或拉取binlog的日志，然后在自己服务器执行。 支持一主多从的架构模式，也支持树状复制的架构模式 使用docker搭建MySQL主从架构 其他章节的内容偏向DBA 跳过阅读 |
日期时间类型 
字符串类型 
数值函数 
调用存储过程call sp_name([parameter[,]]) 调用存储过程就是把逻辑写在了数据库端 删除存储过程或者函数：DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name 查看存储过程或者函数SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
【本文地址】
今日新闻 |
推荐新闻 |