写点什么

Mysql 优化提高笔记整理,来自于一位鹅厂大佬的笔记

用户头像
极客good
关注
发布于: 刚刚

// 查看是否使用索引:


explain select * from articles where body like ‘%非科班 %’


// 正确的用法是:


select * from articles where match(title,body) against(‘非科班’);


说明:


  1. 在 mysql 中 fulltext 索引只针对 myisam 生效

  2. mysql 自己提供的 fulltext 针对英文生效->sphinx (coreseek) 技术处理中文

  3. 使用方法是 match(字段名..) against(‘关键字’)

  4. 全文索引一个 叫 停止词, 因为在一个文本中,创建索引是一个无穷大的数,因此,对一些常用词和字符,就不会创建,这些词,称为停止词.


4.唯一索引


当表的某列被指定为 unique 约束时,这列就是一个唯一索引


// 创建


create table user(id int primary key auto_increment , name varchar(32) unique);


这时, name 列就是一个唯一索引,unique 字段可以为 NULL,并可以有多 NULL, 但是如果是具体内容,则不能重复,主键字段,不能为 NULL,也不能重复。


创建唯一索引


create table user(id int primary key auto_increment, name varchar(32));


create unique index 索引名 on 表名 (列表..);


查询索引


desc 表名 //不能够显示索引名


show index(es) from 表名


show keys from 表名


删除索引


alter table 表名 drop index 索引名;


//如果删除主键索引。


alter table 表名 drop primary key


索引使用的注意事项


由于索引本身很大,占用磁盘空间,对 dml 操作有影响,变慢,满足以下条件的字段,才应该创建索引。


  1. 肯定在 where 条经常使用

  2. 该字段的内容不是唯一的几个值

  3. 字段内容不是频繁变化


explain 可以帮助我们在不真正执行某个 sql 语句时,就执行 mysql 怎样执行,这样利用我们去分析 sql 指令。



  1. id:查询的序列号。

  2. select_type:查询类型。

  3. table:查询表名。

  4. type:扫描方式,all 表示全表扫描。

  5. possible_keys:可是使用到的索引。

  6. key:实际使用到的索引。

  7. rows:该 sql 扫面了多少行。

  8. Extra:sql 语句额外的信息,比如排序方式


sql 语句的小技巧


  1. 在使用 group by 分组查询时,默认分组后,还会排序,可能会降低速度,在 group by 后面增加 order by null 就可以防止排序。如下图所示



  1. 有些情况下,可以使用连接来替代子查询。因为使用 join,MySQL 不需要在内存中创建临时表。


select * from dept, emp where dept.deptno=emp.deptno;


// 替换成


select * from dept left join emp on dept.deptno=emp.deptno;

正确的选择 mysql 的存储引擎

Myisam : 如果表对事务要求不高,同时是以查询和添加为主的,我们考虑使用 myisam 存储引擎. ,比如 bbs 中的 发帖表,回复表。


INNODB : 对事务要求高,保存的数据都是重要数据,我们建议使用 INNODB,比如订单表,账号表。



如果你的数据库的存储引擎是 myisam,请一定记住要定时进行碎片整理

分表技术

为什么要分表?


(1) 如果一个表的每条记录的内容很大,那么就需要更多的 IO 操作,如果字段值比较大,而使用频率相对比较低,可以将大字段移到另一张表中,当查询不查大字段的时候,这样就减少了 I/O 操作


(2)如果表的数据量非常非常大,那么查询就变的比较慢;也就是表的数据量影响这查询的性能。


(3)表中的数据本来就有独立性,例如分别记录各个地区的数据或者不同时期的数据,特别是有些数据常用,而另外一些数据不常用。


(4) 分表技术有(水平分割和垂直分割)


垂直分割


垂直分割是指数据表列的拆分,把一张列比较多的表拆分为多张表。垂直分割一般用于拆分大字段和访问频率低的字段,分离冷热数据。


垂直分割比较常见:例如博客系统中的文章表,比如文章 tbl_articles


(id, titile, summary, content, user_id, create_time),因为文章中的内容 content 会比较长,放在 tbl_articles 中会严重影响表的查询速度,所以将内容放到 tbl_articles_detail(article_id, content),像文章列表只需要查询 tbl_articles 中的字段即可。


垂直拆分的优点:可以使得行数据变小,在查询时减少读取的 Block 数,减少 I/O 次数。此外,垂直分区可以简化表的结构,易于维护。


垂直拆分的缺点:主键会出现冗余,需要管理冗余列,并会引起 Join 操作,可以通过在应用层进行 Join 来解决。此外,垂直分区会让事务变得更加复杂。


水平分割


水平拆分是指数据表行数据的拆分,表的行数超过 500 万行或者单表容量超过 10GB 时,查询就会变慢,这时可以把一张的表的数据拆成多张表来存放。水平分表尽可能使每张表的数据量相当,比较均匀。


水平拆分会给应用增加复杂度,它通常在查询是需要多个表名,查询所有数据需要 union 操作。在许多数据库应用中,这种复杂性会超过它带来的优点。


因为只要索引关键字不大,则在索引用于查询时,表中增加 2-3 倍数据量,查询时也就增加读一个索引层的磁盘次数,所以水平拆分要考虑数据量的增长速度,根据实际情况决定是否需要对表进行水平拆分。


水平分割最重要的是找到分割的标准,不同的表应根据业务找出不同的标准


  1. 用户表可以根据用户的手机号段进行分割如 user183、user150、user153、user189 等,每个号段就是一张表

  2. 用户表也可以根据用户的 id 进行分割,加入分 3 张表 user0,user1,


【一线大厂Java面试题解析+核心总结学习笔记+最新架构讲解视频+实战项目源码讲义】
浏览器打开:qq.cn.hn/FTf 免费领取
复制代码


user2,如果用户的 id%3=0 就查询 user0 表,


如果用户的 id%3=1 就查询 user1 表


  1. 对于订单表可以按照订单的时间进行分表

用户头像

极客good

关注

还未添加个人签名 2021.03.18 加入

还未添加个人简介

评论

发布
暂无评论
Mysql优化提高笔记整理,来自于一位鹅厂大佬的笔记