mysql相关

最新推荐文章于 2024-09-20 21:26:51 发布

牛虻~

最新推荐文章于 2024-09-20 21:26:51 发布

阅读量81

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_35134375/article/details/114930031

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

mysql优化

变量大小能小则小
尽量使用类型匹配的进行存储，例如，日期类型
能用整形的情况下，绝不使用字符串
如果可以避免的情况下，尽量不存储null
如果字符串长度波动小，并且小于255以内，应该首选char
修改数据类型，会导致锁表，所以慎用
如果存储的字符串固定(例如男/女) 用枚举代替字符串存储，数据库底层会采用1-2个字节进行存储
适当的冗余，以空间换时间
适当的拆分，将一些不常用的字段，拆分到另外一张表中，当需要用到这张表的数据时，进行关联查询
使用到索引时，采用索引全匹配/索引最左匹配/索引前缀匹配( ALTER table test add index title_pre(key(7)); )
当使用索引列进行查询的时候，尽量不要使用表达式或者函数，把计算放到业务层而不是数据层
尽量使用主键查询，因为主键查询不会触发回表查询
利用索引扫描进行排序操作，（满足最左匹配）如果多个字段排序，要么都是升序，要么全是降序
范围列可以用到索引，但是索引列后面的列无法用到索引
强制类型转换会全表扫描
更新十分频繁，数据区分度不高的字段不宜建立索引
16.1. 更新会变更B+树，更新频繁的字段会大大降低数据库性能
16.2. 类似于性别这类区分不大的属性，建立索引时没有意义的，不能有效过滤数据
16.3. 一般区分度在80%以上的时候可以建立索引，区分度可以使用count(distinct)/count来计算
创建索引列，不允许为null，可能会得到不符合预期的结果
当需要进行表连接的时候，最好不要超过3张表，因为要join的字段，数据类型必须一致（join相当于多层for循环），可以将小表放到缓存中，大表进行查询
如果明确只有一条数据返回，limit 1能够提高效率
单表索引建议控制在5个以内
单索引字段不允许超过5个(组合索引)
建议表加别名
只查询需要的字段，不要使用*
重新定义关联顺序
将外连接转换为内连接，内连接效率要高于外连接
聚合函数使用索引(group by 索引)
尽量用等值判断来代替范围
每次查询的数据尽量在查询的索引内包含(覆盖索引)，避免回表
尽量用关联查询替代子查询
使用分页，当页数较多时，采用join的方式先查询id再进行关联，可以提高效率

mysql使用了哪些存储引擎

MyISAM、InnoDB、MEMORY
MyISAM、InnoDB采用B+树
MEMORY采用hash

什么情况下使用MEMORY

不需要持久化
快速访问
临时数据或者安全性不高的数据

聚簇索引跟非聚簇索引的区别

聚簇索引:数据行跟键值紧凑的存储在一起(InnoDB)，页节点存放的是数据
优点：

可以把相关数据保存在一起
数据访问更快，因为索引跟数据时保存在同一个树种
使用覆盖索引扫描的查询，可以直接使用页节点中的主键值

缺点：

聚簇索引最大限度的提高了IO密集型应用的性能，如果聚簇索引数据全部放在内存，那么就没什么优势了
增删数据可能会采用页分裂和页合并，效率会降低
更新聚簇索引，顺带的也会将数据行进行移动
聚簇索引插入新行，可能会面临页分裂问题

非聚簇索引:数据文件跟索引文件分开存放(MyISAM)，页节点存放的是地址

如何让导入大量数据效率提高

先把索引关掉，再进行导入，导入进来后，再把索引打开，减少页分裂

索引分类

主键索引、唯一索引、普通索引、组合索引、全文索引

explain 中type优先级

system>const>eq_ref>ref>range>inex>ALL
越往左，优先级越高，一般情况下，至少得保证查询到达range级别，最好能达到ref

行转列

关联查询方式

select distinct name,
(select score from score t1 where course='语文' and t1.name=s.name) as '语文',
(select score from score t1 where course='数学' and t1.name=s.name) as '数学',
(select score from score t1 where course='英语' and t1.name=s.name) as '英语'
from score s;

case when方式

select 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 score
group by name;

join方式

select distinct s.name, ifnull(t1.score, 0) as '语文', ifnull(t2.score, 0) as '数学', ifnull(t3.score, 0) as '英语' from score s
inner join score t1 on t1.name=s.name and t1.course='语文' 
inner join score t2 on t2.name=s.name and t2.course='数学' 
inner join score t3 on t3.name=s.name and t3.course='英语'

mysql日志文件

redo、undo -> InnoDB
binlog -> mysql server

事务四大特性

原子性(A) -> 通过undolog实现
隔离性(I) ->锁实现
持久性(D) -> 通过redolog实现

mysql锁

MyISAM和MEMORY存储引擎采用的是表级锁，InnoDB采用行级锁，也支持表级锁
MyISAM的表级锁有两种模式，表共享读锁，表独占写锁

InnoDB（给索引加锁，如果没有索引，那么会升级为表锁）

共享锁(共享锁就是多个事务对于同一数据可以共享一把锁，都能访问数据库，但是只能读不能修改)
select * from student where id=1 lock in share mode;
排他锁(排他锁不能与其他锁并存，如一个事务获取了一行数据的排他锁，其他事务就不能再获取该行的锁，只有当前获取了排他锁的事务可以对数据进行读取和修改)
select * from student where id=1 for update;
意向共享锁：表示事务准备给数据加入共享锁，也就是说一个数据行在加共享锁之前先取得该表的意向共享锁(IS锁)
意向排他锁：表示事务准备给数据行加入排他锁，也就是说一个数据行在加排他锁之前必须先取得该表的意向排他锁(IX锁)
自增锁：针对自增列自增长的一个特殊表级锁