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锁)
- 自增锁:针对自增列自增长的一个特殊表级锁
查询InnoDB锁等待时间
show status like ‘innodb_row_lock’