开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 7 天，点击查看详情
上一篇文章比较详细的描述了我自学或者工作中遇到与用到的Redis数据库的部分知识,今天打算深入学习一下日常中常用的关系型数据库MySQL;
Mysql介绍知识:作为开源企业级关系型数据库,拥有成本低,体积小,速度快等优势在中小型项目中备受欢迎,尤其在web项目中,Mysql应用也非常广泛,直至目前我接触的项目中只有一个使用Oracle数据库,其余全部为Mysql数据库,在日常使用中SQL语句,索引最为常用,这也导致了解不够深;
MySQL使用C语言与C++语言进行编写,并支持大多数编程语言API,支持多线程,通用sql查询,有多种存储引擎,支持事务,最重要开源没有其他费用就可以进行商用;这里区分于Redis作为Key-Value的非关系型数据库,Mysql是通过表与表行与列之间的关系进行存储数据,所以属于关系型数据库,Oracle同样也属于关系数据库;
MySQL支持的多种存储引擎:
Mysql支持多种存储引擎,通过不同引擎特性支持不同业务场景,可以根据业务场景选择存储引擎来发挥MySQL的最优性能;
可以通过 show engines 查看mysql支持的所有存储引擎;
1:InnoDB:默认存储引擎(常用),支持事务,具备提交,回滚功能,它的行级锁,表级锁,无锁读提升了并发性能,使用聚集索引,当不指定主键时,会自动生成6比特int型作为主键索引,存储时分为frm文件存储结构,ibd文件存储数据内容,也是日常使用最多的存储引擎;支持外键来保证数据完整性(日常中设置外键较少);
2:MyISAM:也是较为常用的类型,常用于只读或以读操作为主的场景中,因为访问速度快,但是不支持事务;使用非聚集索引,支持全文索引,并且保留一个变量用来存储表的行数,不要求必须有主键,只支持表级别锁,可以进行压缩后查询,存储时frm文件存储结构,myd文件存储数据内容,myi文件存储索引文件;
3:Memory:存储数据在内存中,查询效率高,但是没有持久性,服务重启后数据丢失,Hash索引,不支持blog,text等大数据存储,使用较少;
4:BLACKHOLE:只接受不保存数据,又称为黑洞存储引擎;
5:CSV:文件存储为csv文件,所有列只能定义为notnull,没有自增列,没有索引,适合导入导出临时使用;
6:Archive:使用压缩协议存储数据,支持保存与查询,行级锁,不支持事务,适用数据采集或日志只用来记录与查看的场景;
其中最常用的前两种比较重要,其余适合特定场景,使用较少;
MySQL索引功能
-- 创建索引的基本语法 length默认我们可以忽略 建立索引时优先字段较小
CREATE INDEX indexName ON table(column(length));
索引特点:索引可以加快数据检索,同时索引也会存储一个文件,当索引过多时也会导致检索速度变慢,MySQL中限制了数据表中最多有16个索引,当操作过多时多因为操作数据会修改索引数据,会导致修改操作效率降低,InnoDB存储引擎的行级锁时在行数据有合适的索引才会生效;
索引类型:索引分为主索引(主键:PRIMARY),外键索引(建立外键约束的字段会自定义一个内部索引),普通索引(某个字段设为索引),复合索引(索引包含多个字段,只能使用在复合索引中排列在前的数据列组合,设定index(A,B,C),可以使用A或者A,B不能使用B或者B,C);
失效条件: 复合索引使用非最左匹配,也就是说使用了上文中B或者B,C的情况;当索引参与函数处理,计算时会扫描全表导致索引失效;当使用模糊查询时,如果占位符在条件开始('%索引字段')会失效;使用or关键字时一边为非索引字段,或者进行大于/小于对比时索引失效;is not null 查询,不等操作时普通索引结果集占比较大时也会生效,当mysql优化器认定全表扫描比索引查询更有效率时也会造成失效;
MySQL事务
事务:事务简单理解为全部成功或全部失败,可以减少错误带来的影响,是程序健壮性更强;
数据库事务的特性:ACID
A:原子性,事务为不可分割的,全部成功或全部失败;
C:一致性,事务必须开始到结束保持数据一致;
I:隔离性,当前事务不能影响其他事务执行,并发执行时事务独立执行;
D:持久性:事务提交数据永久改变,不应因其他问题影响已提交事务的数据;
事务带来的问题有脏读(读取未提交且回滚的数据),幻读(未读取已提交事务的数据),不可重复读(一个事务中两次读取数据不一致);
事务隔离级别
MySQL优化方法
当前日常开发中可以避免MySQL资源浪费的注意事项:根据业务场景不同使用不同字段并设定较为合适的大小;设置字段为NOTNULL避免数据库比较null值;多表查询时尽量使用内连接,外连接时可以将数据库表数量控制在小范围内,不一次性关联大量表;查询时查询需要字段减少大字段(text,glob)查询,使用union来替代单独查询临时表;使用索引,注意索引失效问题;分页数据使用合理分页方式;分表存储等方式在日常开发提高查询效率;
