深圳市建设局网站首页,seo网站推广多少钱,wordpress的中文插件安装教程视频,长沙网页制作网站一、mysql架构 连接层#xff1a;负责接收客户端的连接请求#xff0c;可以进行授权、认证(验证账号密码)。服务层#xff1a;负责调用sql接口#xff0c;对sql语法进行解析#xff0c;对查询进行优化#xff0c;缓存。引擎层#xff1a;是真正进行执行sql的地方#x…一、mysql架构 连接层负责接收客户端的连接请求可以进行授权、认证(验证账号密码)。服务层负责调用sql接口对sql语法进行解析对查询进行优化缓存。引擎层是真正进行执行sql的地方不同的引擎特点不同。物理文件存储层使用各种文件以及各种日志文件来存储数据。
与数据库交互一次首先通过语言向数据库发送SQLSQL经过服务层经过一系列处理然后到达引擎层选择不同的引擎进行处理最后将数据存储在文件或者日志中。
二、mysql引擎
引擎对数据处理的不同的方式(锁的方式、事物方式)。mysql中提供了多种不同的引擎在实际的开发中可以根据需要选择不同的引擎这样会提高灵活度。
1、查看数据库支持的所有引擎 show engines;
2、查看表的引擎 show table status like user;
3、修改引擎
方式1将 mysql.ini 中 default-storage-engineInnoDB重启服务.
方式2创建表时指定引擎
create table user(id int,account int
)enginemyisam; 方式3修改表的引擎
alter table 表名 engine INNODB;
存储引擎主要有MyIsamInnoDBMemoryBlackholeCSVPerformance_SchemaArchiveFederatedMrg_Myisaminnodb是唯一一个支持事物的引擎
主要分析InnoDB和MyIsam
对比项Innodb默认MyISAM事务支持不支持强外键约束支持不支持缓存缓存索引还缓存真实数据只缓存索引不缓存真实数据锁行级锁(对某行数据操作时只会锁定某一行数据效率高)表锁(进行增删改操作时会锁定整张表效率低)主要适用于增删改多的场景查询多的场景存储表的总行数不支持支持表空间大小关注点事务性能
三、索引
为什么要用到索引数据量大的时候需要查询一个数据效率非常低。因为要从第一个开始进行查找直到找到数据为止。
索引是一种有序的数据结构利用索引可以提高mysql的查询效率。类似于书的目录不需要翻遍每一页去查询数据可以通过目录快速定位获取。创建数据库表的时候会为一些列创建索引这样查询效率高。
本质都是通过不断地缩小范围来获取数据同时把随机的事件变成顺序的事件。 为了加快col2的查找给col2创建索引就会给col2列创建一个二叉树。
优势提高查询效率体现在与硬盘IO次数减少了由于是排好序的降低了查询次数cpu消耗低。
劣势实际上索引也是一张表该表保存了主键与索引字段并指向实体表的记录。所以索引需要存储空间新增、修改、删除操作时也需要维护索引需要的时间开销。
1、索引创建原则
哪些情况需要创建索引
主键会自动创建唯一的索引查询条件的列where后面的语句)加快条件的判断速度。外键的列排序的列分组中的字段
哪些列不适合建立索引
表记录太少经常增删改的表因为更新表时MySQL不仅要保存数据还要保存一下索引文件Where条件里用不到的字段数据重复率高的列例如性别男、女
2、索引分类
查看索引show index from 表名;
1.主键索引设定为主键后数据库会自动建立索引
alter table 表名 drop primary key;#删除主键(索引)
ALTER TABLE test add PRIMARY KEY test(id);#添加主键
2.单值索引一个索引中只包含一个列一个表可以有多个单列索引 创建单值索引create index 索引名 on 表名(列名); 删除索引alter table 表名 drop index 索引名; create index index_student_name on student(name);#创建单值索引
explain select * from student where name张三 #测试
alter table student drop index index_student_name;#删除索引 3.唯一索引索引列的值必须唯一允许为null
create unique index 索引名 on 表名(列名);
4.组合索引(复合索引)一个索引中可以包含多个列。组合索引比单值索引的开销小 创建复合索引create index 索引名 on 表名(列 1,列 2...); 删除索引alter table 表名 drop index 索引名; create index index_student_name_gender on student(name,gender);#创建
组合索引要满足最左前缀原则
例如表中有a、b、c 3列a、b两列创建组合索引。当查询条件中用到组合索引时必须使用到最左边列否则索引失效。
create index index_test_a_b on test(a,b); #a列就是最左边列
explain select * from test where a aa and bbb
explain select * from test where b aa and abb
explain select * from test where a aa and cbbexplain select * from test where b aa and cbb -- 组合索引失效 因为没有用到最左边列 5.全文索引可以解决模糊查询索引失效的问题。模糊查询时索引会失效。
explain select * from test where name like %j%#模糊查询时索引失效 create fulltext index 索引名 on 表名(字段名) with parser ngram; select 结果 from 表名 where match(列名) against(搜索词) create fulltext index index_content ON test(content) with parser ngram;#test表中,content列创建了全文索引explain select * from test where MATCH(content) against(中国)#content列中有没有出现“中国”
3、索引的数据结构
二叉树、红黑树在自增主键中使用效率不高。
使用B树
排好序的一个节点可以存储多个数据使得B树的高度不会太高。数据存储在叶子节点叶子节点之间还有连接非叶子节点只存储索引可以放更多的索引。 4、聚簇索引和非聚簇索引
聚簇索引找到了索引就找到了数据。innodb中的主键索引就是聚簇索引主键索引的B树的叶子节点存储的就是数据。
一个表生成的数据、索引、表结构都存储在.ibd文件中因为数据和主键的索引树绑在一起的。 innodb中除了主键索引是聚簇索引其他索引是非聚簇索引。例如name列添加了索引但是不能直接通过name找到数据先通过name索引树找到主键索引再通过主键找到数据需要两次查找所以是非聚簇索引。 非聚簇索引找到了索引并没有找到数据需要二次查询。myisam引擎中的索引都是非聚簇索引因为表结构、索引、数据都在不同的表中存储。 5、回表查询
回表查询也就是二次查询。例如通过学号/姓名等信息作为条件查询首先通过学号/姓名找到主键索引再通过主键索引找到数据称为回表查询。
例如学生表 id 主键索引 no 唯一索引 name 单值索引 如果我们通过主键查询学生信息因为主键索引是聚簇索引找到主键索引就找到了数据所以不需要回表查询。
select * FROM student WHERE id 1 如果通过学号去查询学号和姓名因为学号是非聚簇索引通过学号找到主键索引再通过主键索引找到学号和姓名需要二次回表查询。
SELECT NO,NAME FROM student WHERE NO 123
如果通过学号只查询学号是否存在找到学号索引就找到了数据此种场景不需要再回表查询了也属于聚簇索引。
SELECT NO FROM student WHERE NO 123
四、锁机制
多个事物(线程)对同一个表中的同一行数据进行操作需要通过锁进行保护。按照粒度锁可以分为表锁、行锁、间隙锁。innodb引擎支持行级锁可以将锁的粒度到行级。
表锁操作时会锁住整张表效率低。myisam就只支持表锁。
行锁操作时会锁住操作的行并发量高。innodb支持支持表锁和行锁且出于性能考虑绝大多数情况下使用的都是行锁
间隙锁当条件为一个范围区间时只对某个区间进行加锁。innodb支持间隙锁。
共享锁(S)又称读锁。一个事物对数据A进行读操作时如果添加了共享锁其他事物可以对数据A进行读操作但是其他事物就不能对数据A进行修改不能加排他锁。添加共享锁语法select … lock in share mode;
排他锁()又称写锁。事物A对数据进行增删改操作时会自动添加锁其他事物不能对数据加任何锁直到事物A释放锁才可以。增删改会自动加锁查询时如果需要添加排他锁可以使用select … for update;语句。
五、事务
mybatis中提交事物sqlsession.commit();
spring对事物进行管理编程式事物、声明式事物(xml配置, 注解)
事物数据库操作的过程中可能包含多条sql执行多条sql执行应该是一个整体保多条sql语句要么全部执行 要么全部不执行。 用来管理insert、update、delete语句。
mysql中只有innodb引擎支持事物
1、事物特征(ACID)
原子性(Atomicity)一个事物中有多条sql要么都成功执行要么都不执行执行过程中如果发生错误会被回滚到事务开始前的状态就像这个事务从来没有执行过一样。
持久性(Durability)在事物提交后要保证数据的持久化。
隔离性(Isolation)在多个事物并发访问下对多个事物操作要进行隔离各个事务之间不能互相干扰。
一致性(Consistency)最终目标就是保证数据操作的一致性。例如经过多次转账操作后最终的结果和预期的结果应该一致。
2、事务设置
默认情况下MySQL启用自动提交模式变量autocommit为on。只要执行DML操作的语句MySQL会立即隐式提交事务。
用SET来改变MySQL的自动提交模式
SET GLOBAL autocommit0; #禁止自动提交
SET GLOBAL autocommit1;#开启自动提交
查看autocommit模式
SHOW GLOBAL VARIABLES LIKE autocommit;
例如
begin;/START TRANSACTION;#开始一个事务insert into test(id,name) value(2,aaa);
commit#事务确认rollback#事务回滚
3、事务隔离级别
只有InnoDB支持事务所以这里说的事务隔离级别是指InnoDB下的事务隔离级别。
理论上在某个事务对数据进行操作时其他事务应该进行排队当该事务提交之后其他事务才可以继续操作这个数据。但是这样的话对性能影响太大所以设计数据库的大叔提出了各种隔离级别来最大限度的提升系统并发处理事务的能力。
查看隔离级别SELECT global.transaction_isolation,transaction_isolation;
设置隔离级别SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
多个事物对同一个数据操作时采用哪种隔离级别。mysql中默认的隔离级别是可重复读。
4种隔离级别
1.读 未提交(read uncommitted)A事物正在对数据操作事物没有提交此时B事物可以读到A事物修改后未提交的数据。可能出现的问题脏读、不可重复读、幻读。
2.读 已提交(read committed)A事物对数据正在操作B事物只能读到A事物已经提交的数据如果未提交是读不到的。解决了脏读问题但是仍然有 不可重复读、幻读问题。
3.可重复 读(repeatable read)事物B在事物A中连续读取同一个数据两次读到结果是一致的哪怕在此期间有别的事物修改数据。解决了 不可重读问题。
mysql8中的可重读的正常查询解决了幻读问题如果在查询语句后面添加for update语句会出现幻读问题。
4.串行化(serializable)当一个事物对一行进行操作时(即使是读操作)还未提交其他事物不能进行增删改操作。 脏读B事务读取A事务中的数据但A事物有可能回滚B事物读到的就是脏数据。 不可重复读在同一个事物中两次读同一个id的数据不相同的。事务A将数据改了事务B读不到只能读到和之前一样的 幻读在一个事物两次读到数量不一致。事务A新增了一条数据事务B读不到只能读到和之前一样的 4、事物实现原理和增删改相关的
1.原子性原理
在底层使用undolog(回滚日志)日志保存一个相反的操作。例如执行insert操作那么undolog日志中会记录一个相反的delete操作执行delete操作记录一个insert当事物回滚时执行反向操作。
2.持久性实现原理
使用redolog(重做日志)保证持久性。执行sql时先将sql写入到redolog中然后再执行保存到数据库万一这个过程中数据还没有写入到硬盘突然断电那么下次mysql启动时会重新执行redolog日志保证持久性。
3.隔离性
多版本并发控制(MVCC Multi-Version Concurrent Control) 为了实现可重复读底层每次事物操作时都会生成一个版本对于数据的多个操作每个版本之间都存在关系形成一个版本链。
如果是读已提交每次会生成一个最新的版本快照读到最新提交的数据。
如果是可重复读会在第一次读的时候生成一个版本快照之后再次读取时仍然从第一次生成的快照中读。
4.一致性
通过满足原子性、持久性、隔离性最终达到一致性。
SQL优化
1. 使用varchar代替char varchar字符串长度不固定时使用按数据的实际长度存储可以节省存储空间 如姓名地址QQ号char字符串长度固定时使用不足补空格 如性别手机号 1char定长字符串, 不足的会以空格补齐最多可以存储255个字符 2varchar变长字符串有几个字符就存储几个字符最多可以存储65535个字符 2. 清空表时优先使用truncate 一张表里有100万条数据需要清空怎么做 truncate table比delete速度快且使用的系统和事务日志资源少。 delete 语句每次删除一行并在事务日志中为所删除的每行记录一项。truncate table 通过释放存储表数据所用的数据页来删除数据 delete是一行一行删的 truncate是DDL语句对表结构操作的 3.查询SQL尽量不要使用select *而是具体字段 节省资源、减少网络开销。 可能用到覆盖索引减少回表提高查询效率。 4.表连接不宜太多索引不宜太多一般5个以内 联的表个数越多编译的时间和开销也就越大 每次关联内存中都生成一个临时表 应该把连接表拆开成较小的几个执行可读性更高 5. 尽量使用数值替代字符串类型 主键idprimary key优先使用数值类型int 性别sex0代表女1代表男数据库没有布尔类型mysql推荐使用tinyint 因为引擎在处理查询和连接时会逐个比较字符串中每一个字符而对于数字型而言只需要比较一次就够了 字符会降低查询和连接的性能并会增加存储开销 6. 提高 group by 语句的效率 反例先分组再过滤 正例先过滤后分组 7. inner join 、left join、right join优先使用inner join 三种连接如果结果相同优先使用 inner join inner join 内连接只保留两张表中完全匹配的结果集 left join 会返回左表所有的行即使在右表中没有匹配的记录 right join 会返回右表所有的行即使在左表中没有匹配的记录 8.首先考虑在where及 order by涉及的列上建立索引
9.避免在where子句中使用or来连接条件 反例SELECT * FROM user WHERE id1 OR salary5000 正例使用union all把两个两个SQL结果合并 对于or没有索引的salary这种情况假设它走了id的索引但是走到salary查询条件时它还得全表扫描 10. 避免在索引列上使用内置函数索引失效
11. 避免索引失效 1. 模糊查询将导致全表扫描两边都有% select id from t where name like %abc% 2. 避免在where子句中使用or来连接条件否则导致索引失效而进行全表扫描如:select id from t where num10 or num20 3. in和not in要慎用否则会导致全表扫描如select id from t where num in(1,2,3)对于连续的数值能用between就不用in因为B树中叶子节点之间有联系范围查找快一些。select id from t where num between 1 and 3 4. 避免where子句中对字段进行函数操作这将导致引擎放弃使用索引而进行全表扫描。如: select id from t where substring(name,1,3)abc
