菜单开关

周梦康 发表于 2017-08-05 4608 次浏览 标签 : 网站架构实战

https://segmentfault.com/a/1190000010455076#articleHeader26

数据索引相关的文章网上很多了,不足的地方大家补充。

表设计 - 拥抱 innodb

现在大多数情况都会使用innodb类型了。具体原因是 mysql 专家给的意见。
我自己对 mysql 的优化不了解,每一个细分领域都是一片汪洋,每个人的时间精力是有限的,所以大家也不用什么都非要深入去研究,往往是一些计算机基础更为重要。
参考这份ppt https://static.mengkang.net/upload/file/20170806/1501990586261700.pdf

表设计 - 主键索引

  1. innodb 需要一个主键,主键不要有业务用途,不要修改主键。
  2. 主键最好保持顺序递增,随机主键会导致聚簇索引树频繁分裂,随机I/O增多,数据离散,性能下降。

举例:
之前项目里有些索引是article_id + tag_id 联合做的主键,那么这种情况下,就是业务了属性了。主键也不是顺序递增,每插入新的数据都有可能导致很大的索引变动(了解下数据库b+索引的原理)

表设计 - 字段选择

  1. 能选短整型,不选长整型。比如一篇文章的状态值,不可能有超过100种吧,不过怎么扩展,没必要用int了。
  2. 能选 char 就避免 varchar,比如图片资源都有一个hashcode,固定长度20位,那么就可以选char了。
  3. 当使用 varchar 的时候,长度够用就行,不要滥用。
  4. 大文本单独分离,比如文章的详情,单独出一张表。其他基本信息放在一张表里,然后关联起来。
  5. 冗余字段的使用,比如文章的详情字段,增加一个文章markdown解析之后的字段。

索引优化

大多数情况下,索引扫描要比全表扫描更快,性能更好。但也不是绝对的,比如需要查找的数据占了整个数据表的很大比例,反而使用索引更慢了。

  1. 没有索引的更新,可能会导致全表数据都被锁住。所以更新的时候要根据索引来做。
  2. 联合索引的使用
  3. explain 的使用

联合索引“最左前缀”,查询优化器还会帮你调整条件表达式的顺序,以匹配组合索引的要求。

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(10) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL,
  `c` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_abc` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

能使用到索引

explain select * from test where a=1;
explain select * from test where a=1 and b=2;
explain select * from test where a=1 and b=2 and c=3;
explain select * from test where a=1 and b in (2,3) and c=3;
explain select * from test where a=1 and b=2 order by c desc;

不能使用索引

explain select * from test where a=1 and b in (2,3) order by c desc;
explain select * from test where b=2;

explain 搜到一篇不错的: http://blog.csdn.net/woshiqjs/article/details/24135495
很重要的参数type,key,extra

type 最常见的

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

说明
const通过索引直接找到一个匹配行,一般主键索引的时候
ref没有主键索引或者唯一索引的条件索引,查询结果多行,在联合查询中很常见
index利用到了索引,有可能有其它排序,where 或者 group by 等
all全表扫描,没有使用到索引

extra

如果有Using filesort或者Using temporary的话,就必须要优化了

收集慢查询

my.ini 配置里增加

long_query_time=2
log-slow-queries=/data/var/mysql_slow.log

使用 nosql

redis 丰富的数据类型,非常适合配合mysql 做一些关系型的查询。比如一个非常复杂的查询列表可以将其插入zset 做排序列表,然后具体的信息,通过zset里面的纸去mysql 里面去查询。

评论列表