数据库性能优化的五种方案

数据库性能优化的五种方案

文档修订摘要

目录

数据库性能优化的五种方案 (1)

1. 概述 (4)

1.1. 目的 (4)

1.2. 阅读对象 (4)

1.3. 名词解释 (4)

1.4. 转载出处 (4)

2. 操作步骤 (4)

(4)

2.1. 建立索引 (5)

2.1.1. Mysql索引概念 (5)

2.1.2. Mysql索引主要有两种结构：B+树和hash (5)

2.1.

3. Mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引

(5)

2.1.4. Mysql各种索引区别 (6)

2.1.5. INNODB与MyISAM两种表存储引擎区别 (6)

2.2. 优化SQL语句 (6)

2.2.1. 常用策略 (6)

2.2.2. 实例案例分析 (9)

2.3. 优化表结构 (9)

2.4. 表的拆分 (10)

2.5. 分库 (10)

1.概述

1.1.目的

数据库性能优化

1.2.阅读对象

1.3.名词解释

1.4.转载出处

https:///csflvcxx/article/details/81279024

2.操作步骤

关系型数据库在互联网项目中应用极为广泛，今天小编就和大家分享几个数据库优化的几种方案。

2.1.建立索引

数据库优化第一步就是建立合理的索引，这也是最初级的优化，也是DBA常用的优化方案！MySql索引类型有：普通索引，主键索引，唯一索引，组合索引！

2.1.1.Mysql索引概念

说说Mysql索引，看到一个很少比如：索引就好比一本书的目录，它会让你更快的找到内容，显然目录（索引）并不是越多越好，假如这本书1000页，有500也是目录，它当然效率低，目录是要占纸张的,而索引是要占磁盘空间的。

2.1.2.Mysql索引主要有两种结构：B+树和hash

hash:hash索引在mysql比较少用,他以把数据的索引以hash形式组织起来,因此当查找某一条记录的时候,速度非常快.当时因为是hash结构,每个键只对应一个值,而且是散列的方式分布.所以他并不支持范围查找和排序等功能.

B+树:b+tree是mysql使用最频繁的一个索引数据结构,数据结构以平衡树的形式来组织,因为是树型结构,所以更适合用来处理排序,范围查找等功能.相对hash索引,B+树在查找单条记录的速度虽然比不上hash索引,但是因为更适合排序等操作,所以他更受用户的欢迎.毕竟不可能只对数据库进行单条记录的操作.

2.1.

3.Mysql常见索引有：主键索引、唯一索引、普通索

引、全文索引、组合索引

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 )

FULLTEXT(全文索引) ALTER TABLE table_name ADD FULLTEXT ( column )

组合索引ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

2.1.4.Mysql各种索引区别

普通索引：最基本的索引，没有任何限制。

唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

主键索引：它是一种特殊的唯一索引，不允许有空值。

全文索引：仅可用于MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。

组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

2.1.5.INNODB与MyISAM两种表存储引擎区别

mysql数据库分类为INNODB为MyISAM两种表存储引擎了，常见的mysql表引擎有INNODB和MyISAM，主要的区别是INNODB适合频繁写数据库操作，MyISAM适合读取数据库的情况多一点。

2.2.优化SQL语句

从业务角度，优化SQL语句；这里给大家分享一个例子，查询三个小时内，未处理的订单；使用一条SQL语句的效率要远远低于使用18条SQL语句，每条SQL语句搜取10分钟；

2.2.1.常用策略

(i)核心要点

●使用索引；

●避免全表扫描；

●避免新增和删除时锁表，影响查询；

(ii)详细清单

1.对查询进行优化，要尽量避免全表扫描，首先应考虑在where 及order by 涉及的列上建立索引。

2.应尽量避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.

备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了，不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num = 0

3.应尽量避免在where 子句中使用!= 或<> 操作符，否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在where 子句中使用or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or Name = 'admin'

可以这样查询：

select id from t where num = 10union allselect id from t where Name = 'admin'

5.in 和not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用between 就不要用in 了：

select id from t where num between 1 and 3

很多时候用exists 代替in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

6.下面的查询也将导致全表扫描：

select id from t where name like ‘%abc%’

若要提高效率，可以考虑全文检索。

7.如果在where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num = @num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num = @num

.应尽量避免在where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2 = 100

应改为:

select id from t where num = 100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3) = ’abc’ -–name以abc开头的idselect id from t where datediff(day,createdate,’2005-11-30′) = 0 -–‘2005-11-30’ --生成的id 应改为:

select id from t where name like 'abc%'select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'

10.不要在where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。