自我总结MySQL篇

最新推荐文章于 2024-07-25 16:06:56 发布

qq_43485644

最新推荐文章于 2024-07-25 16:06:56 发布

阅读量237

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_43485644/article/details/119830150

版权

SELECT * FROM t WHERE score = 0

1.范式：

第一范式1NF:满足原子性就是第一范式，所有关系型数据库都满足；

第二范式2NF:一范式的基础上满足非主键部分不能部分依赖主键；

第三范式3NF:满足二范式前提下不能有依赖传递

2.游标：

就是一个缓存，用来保存用户sql的执行结果；

3.mysql中 in 和 exists 区别

in exists：子查询大的用exists 小的用in

not in和not exists：前者不用索引，两个表全表扫描，not exists：子查询依旧可以用索引

4.char和varchar

前者长度不可变，空白部分用空格补缺，用空间换时间，效率快

后者长度可变，时间换空间，效率慢

5.drop、delete、truncate的区别：

6.事务

1）四大特性

原子性:要么一起成功，要么一起失败

一致性:执行事务前后，数据库一致

隔离性:事务直接互不影响，一般通过锁来实现。

持久性:事务一旦提交，其结果就是永久的。即便发生系统故障，也能恢复。

2）脏读、不可重复读、幻读

脏读：T1修改数据，T2读取改数据，然后T1撤回修改，导致T2读取无效数据

不可重复读：对相同事务查询返回了两条不同结果；（Update和delete）

幻读：T1操作某个表，然后T2在表内插入一条数据，但是T1没有执行这一事务却发现表内多了一条数据。（insert）

3）事务隔离级别

未提交读：允许脏读，别的事务修改但是没提交数据后本事务中可以读取修改后的数据；

提交读：只能读取到已经提交的数据；o'racle数据库的默认级别

可重复度：无论别的事务是否修改提交数据，本事务的数据都不会被影响；mysql的默认级别

串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。

7.索引

1）什么是索引：索引是建立在一列或者多列上的辅助对象；是一种排序的数据结构；作用是帮忙提高查询效率；索引的数据结构是：B树或者B+树

2）索引的优点：

使用索引可以帮忙提高查询效率；创建唯一索引可以保证数据库数据的唯一性；减少分组排序的时间；

3）索引的缺点：也需要实际物理空间；创建维护都需要花费时间，并且随着数据增多，索引也增多，所花费时间也就越多；表进行更新操作时，索引需要被重建，降低了数据的维护速度。

4）索引的使用场景

- 经常需要搜索的列上
- 作为主键的类上
- 经常用在链接的类上，这些连接一般都是外键
- 经常需要根据范围进行搜索的列上（例如分数。根据分数范围搜索）
- 经常需要进行排序的列上

5）索引的类型有哪些？5种

普通索引，数据可以重复，没有任何限制。
唯一索引，要求索引列的值必须唯一，但允许有空值；如果是组合索引，那么列值的组合必须唯一。
主键索引，是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在创建表的同时创建主键索引。
组合索引，在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。
全文索引，是对于大表的文本域：char，varchar，text列才能创建全文索引，主要用于查找文本中的关键字，并不是直接与索引中的值进行比较。fulltext更像是一个搜索引擎，配合match against操作使用，而不是一般的where语句加like。
6）如何建立一个索引

alter table 表名 add index（字段名）；

7）索引的原理：

把无序的数据结构变有序

先排序，生成倒排表拼接地址链，查询先查倒排表，再拿地址，取出数据

8）创建索引三种方式：

第一种方式：在执行CREATE TABLE时创建索引

第二种方式：使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

第三种方式：使用CREATE INDEX命令创建
CREATE INDEX index_name ON table_name (column_list);

MySQL百万级数据添加索引

导出原表数据
创建新表与原表结构保持一致，在该表上执行alter语句添加索引；
将表数据导入创建的新表；
修改新建的表名为原表名。

9）.百万级x别或以上的数据如何删除

先删索引，再删无用的东西，然后创建索引，再删数据；

10）聚簇索引非聚簇索引

前者会把索引和值放在一起都存起来；后者只存索引，索引结构的叶子节点指向了数据的对应行

11）

8.sql优化

sql执行顺序：

语法：

执行顺序：

FROM
<表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <join, left join, right join...> 
<join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>

这是不走索引的情况：

1）模糊查询尽量不要 %？？% 而是加在最后？？% ，会导致数据库引擎放弃索引进行全表扫描。

2）in/not in 尽量不要用，会导致数据库引擎放弃索引进行全表扫描。

如果是连续的两个数用 between 2 and 3

子查询用exists

3）尽量少用or，会导致数据库引擎放弃索引进行全表扫描。 用union

4）尽量不用null做判断条件，会导致数据库引擎放弃索引进行全表扫描。

SELECT * FROM t WHERE score IS NULL

优化：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t WHERE score = 0

5）尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。

可以将表达式、函数操作移动到等号右侧。如下

-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9

6）当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：

SELECT username, age, sex FROM T WHERE 1=1
优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。
7)查询条件不能用 <> 或者 !=

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

8)隐式类型转换造成不使用索引

select col1 from table where col_varchar=123;

9) where条件仅包含复合索引非前置列

就是索引顺序如果a没有就会成为断点；

eg:select col1 from table where key_part2=1 and key_part3=2

这里索引包含key_part1，key_part2，key_part3三列，然后1就成为断点了就不走索引

10） order by 条件要与where中条件一致，否则order by不会利用索引进行排序


-- 不走age索引
SELECT * FROM t order by age;
 
-- 走age索引
SELECT * FROM t where age > 0 order by age;

当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。

这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

SELECT语句其他优化

1）避免使用select *

2）避免出现不确定结果的函数

3）多表联查小表在前大表在后

4）使用表别名

5）用where代替having

6）调整Where字句中的连接顺序

MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

sql查询优化

1.复杂查询加一个临时表

2.优化group by（如果查询包括 GROUP BY 但你并不想对分组的值进行排序，你可以指定 ORDER BY NULL禁止排序。）

3.优化join语句

4.优化union查询

如果不是非要去重用union all

5.拆分复杂SQL为多个小SQL，避免大事务

9.聚合函数

1. avg 求平均数
select AVG(Amount) from Details;
2. min求最小值

select MIN(Amount) from Details;

3. max求最大值
select MAX(Amount) from Details;

4. sum求总和
select SUM(Amount) from Details;
5. count求数据的条数
select COUNT(Amount) from Details;

基于Mycat实现数据库的读写分离、分表分库；

准备两台虚拟机；安装JDK,下载压缩包、解压、进入解压后的文件夹、去config、修改server.xml文件在<user>标签里添加数据库信息这里的user相当于mysql数据库，

name：相当于给数据库起了个名字，这个schmas要改成接下来你在schema.xml文件里添加的数据库；

schema.xml中

name：对应数据库名称，写三个

datenode：对应数据库

具体,<dataHost>标签有个心跳语句就是去查询数据库是否正常

重点：：：：balance：这个参数改成1才开启

配置主从：主是<writeHost> 从是在这个标签内部写一个<readhost>

分片规则“：rule对应的是conf中的rule.xml文件，具体去里边写；

这些结束后去mycat的bin目录里输入 ./macat start 就好了

最后查看一下：就去navicat去连接用户名就用我们起的那个名字，密码就是设置的密码，端口改成mycat默认端口：8066，然后我们项目中数据库连接就和这个mycat数据库连接就好了

MySQL行转列：

if：

select s_name,

max(if(course='语文',score,0)) as 语文,

max(if(course='数学',score,0)) as 数学,

max(if(course='英语',score,0)) as 英语,

from tb_student

group by s_name;

case:

select s_name,

max(case course when '语文' then score else 0 end) as 语文,

max(case course when '数学' then score else 0 end) as 数学,

max(case course when '英语' then score else 0 end) as 英语,

from tb_student

group by s_name;

列转行

select * from

(select s_name,'语文' as course,Chinese as score from table

union all

select s_name,'语文' as course,Chinese as score from table;)

as t order by t.score desc;

qq_43485644

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
自我总结MySQL篇

1.范式：第一范式1NF:满足原子性就是第一范式，所有关系型数据库都满足；第二范式2NF:一范式的基础上满足非主键部分不能部分依赖主键；第三范式3NF:满足二范式前提下不能有依赖传递2.游标：就是一个缓存，用来保存用户sql的执行结果；3.mysql中 in 和 exists 区别in exists：子查询大的用exists 小的用innot in和not exists：前者不用索引，两个表全表扫描，not exists：子查询依旧可以用索引4.char和varchar
复制链接

扫一扫