数据库优化

2016-08-21
  • 字段设计
    • 适当冗余
    • 分离不必要字段
  • 索引
    • 尽量选择区分度高的列作为索引
    • 对where、order by字段建索引
    • where中避免使用!=
    • 避免在 where 子句中对字段进行 null 值判断
    • 避免在 where 子句中使用 or 来连接条件
      • select id from t where num=10 or num=20 改为如下会更好
      • select id from t where num=10 union all select id from t where num=20
    • like尽量不要使用”%XX”
    • 能用 between 就不要用 in
    • 避免在where中对字段进行函数操作、表示式操作
      • where num/2=100 where substring(name,1,3)=’abc’
    • 联合索引
      • 会自动调整where子句的条件顺序以使用适合的索引
      • where中字段顺序尽量与索引顺序一致
      • where中必须使用联合索引中的第1个字段，不一定位于where子句第1位
      • 最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配
        • 比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的。
    • 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
  • 分页查询
  • 分表（水平UNION、垂直JOIN）
  • 分库
  • 读写分离
    • 一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。
  • 添加缓存
  • 存储过程

索引

索引类型

  • 普通索引
  • 唯一性索引
    • 主键为一种特殊的唯一性索引。
  • 单列索引
    • 单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。
  • 多列索引
    • 只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。
  • 全文索引（仅myISAM存储引擎支持）
    • 使用FULLTEXT参数可以设置索引为全文索引。
    • 使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。
    • 如查询文章表的文章内容。
  • 空间索引（仅myISAM存储引擎支持）
    • 使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。
    • MySQL中的空间数据类型包括GEOMETRY和POINT、LINESTRING和POLYGON等。索引的字段不能为空值。

索引操作

  • 主键索引PRIMARY KEY
    • ALTER TABLE table_name ADD PRIMARY KEY (column)
  • 唯一索引UNIQUE
    • ALTER TABLE table_name ADD UNIQUE (column)
  • 普通索引INDEX
    • ALTER TABLE table_name ADD INDEX index_name (column)
  • 多列索引
    • ALTER TABLE table_name ADD INDEX index_name (column1, column2, column3)
  • 全文索引FULLTEXT
    • ALTER TABLE table_name ADD FULLTEXT (column)

创建索引

  • CREATE TABLE
  • ALTER TABLE
    • 可创建普通索引、UNIQUE索引或PRIMARY KEY索引
    • ALTER TABLE table_name ADD INDEX index_name (column_list)
    • ALTER TABLE table_name ADD UNIQUE (column_list)
    • ALTER TABLE table_name ADD PRIMARY KEY (column_list)
  • CREATE INDEX
    • 可对表增加普通索引或UNIQUE索引
    • CREATE INDEX index_name ON table_name (column_list)
    • CREATE UNIQUE INDEX index_name ON table_name (column_list)

删除索引

  • 删除普通索引
    • DROP INDEX index_name ON talbe_name
    • ALTER TABLE table_name DROP INDEX index_name
  • 删除主键索引
    • ALTER TABLE table_name DROP PRIMARY KEY
  • 对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

读写分离

Master-Slave1-Slave2-Slave3
主库master用来写入，slave1—slave3都用来做select，每个数据库分担的压力小了很多。
要实现这种方式，需要程序特别设计，写都操作master，读都操作slave，给程序开发带来了额外负担。当然目前已经有中间件来实现这个代理，对程 序来读写哪些数据库是透明的。官方有个mysql-proxy，但是还是alpha版本的。

  • 数据量比较大时使用分页查询，简单的limit offset即可。
  • 若页面偏移量offset较大（如 limit 100000,10），查询会变慢，此时分表。
  • 分表还比较慢，则分库。

Kommentare: