怎么提高数据库性能

通常在生产中总会涉及到或多或少与数据库相关的问题，这里我们可能因为自身服务器设置的问题，数据库内部变量参数设置的问题，事务的问题，抑或是外键，索引等相关数据库语言优化的问题，下面摘抄一下从网上看到的对数据库进行优化的几点建议：

本文主要是摘录从网上查找到的数据库性能优化的案例，未做验证
索引
1. 使用索引
说明：索引类似书的目录，无需查询整个表
优点：数据量越大越好用，适用于修改不多，但重复查询的场景
缺点：占用物理空间，创建和维护有消耗，数据库操作要多一步去维护索引，关系到数据库增删改查的性能

CREATE INDEX idx_username ON users(username);

可以通过 SHOW INDEX 命令查询索引

SHOW INDEX FROM users

分类：直接创建（sql 语句创建），间接创建（主键等）
2. 字段数据更新频率低，查询频率高，且查询内容大致在一定范围（order by/group by/MIN ()/MAX ()），可用索引；如同时存取多列，且每列含有重复值可用复合索引
3. 复合索引，将使用最频繁的列作为前导列，如果进行查询时，如果前导列不在查询条件中，则不会被查询

create unique index PK_GRADE_CLASS on student (grade, class)
select * from student where class = 2	//未使用到索引
select * from dept where grade = 3	//使用到了索引

4. 避免对前导列运算

select * from student where tochar(grade)=’2′

5. 比较值避免使用 NULL
6. 多表查询选择合适的表做内表
7. 查询列与索引列次序一致
8. 用多表连接代替 EXISTS
9. 把过滤记录数最多的条件放在最前面
10. 善于使用存储过程，慎用，因为通常情况下，前台应用程序的执行效率往往比后台数据库存储过程要高的多
11. 在建有索引的字段上尽量不要使用函数操作

事物

1. 原子提交，要么完成，要么所有都不做，失败自动回滚
2. 更优性能
3. 注意，当事务在执行过程中，数据库会被锁定，只有当当前用户执行的事务完成之后，才可以允许其他用户操作

网络

1. 网络拥塞，如利用集线器进行连接，导致企业内部网络广播泛滥

硬件

1. 硬盘小和少，硬盘寻道难
2. 服务器内存小，或这有些企业为降低成本，数据库服务器和应用服务器放在一个服务器，资源争用问题
3. CPU 数量
4. 其他改善服务器的配置
5. 注意差异备份和完全备份，还有注意备份的时间，如夜晚

其他

1. 字符串拼接使用 StringBuilder，String 会导致创建多个临时对象
2. 查询时不要返回太多结果集，只取所需
3. 少用 cursor.getColumnIndex ()

public static final String       HTTP_RESPONSE_TABLE_RESPONSE            = "response";
public List<Object> getData() {
	……
	cursor.getString(cursor.getColumnIndex(HTTP_RESPONSE_TABLE_RESPONSE));
	……
}
可以优化为
public List<Object> getData() {
	……
	cursor.getString(HTTP_RESPONSE_TABLE_RESPONSE_INDEX);
	……
}

4. 数据库视图设计，尽量缩小内容，宁可多增加视图
5. 数值数据处理速度要比文本快，尤其是比 text 快
6. 使用子查询 Sub-Queries，可以完成很多逻辑上需要很多步骤才能完成的 SQL 操作，同时也可以避免事务或死锁，但某些情况下使用连接 JOIN 会更有效率

//使用子查询
DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
//使用连接，则不需要在内存中创建临时表来达到我们最终需求的操作
SELECT * FROM customerinfo LEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo. CustomerID WHERE salesinfo.CustomerID IS NULL

7. 使用联合（UNION）来代替手动创建临时表

//注意所有select语句里字段数目一致
SELECT Name, Phone FROM client UNION SELECT Name, BirthDate FROM author UNION SELECT Name, Supplier FROM product

8. 由于事务的资源锁占用问题，导致多用户无法访问数据库表，我们可以使用锁定表的方式

LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHEREItem='book';
...
UPDATE inventory SET Quantity=11 WHEREItem='book';
UNLOCK TABLES

9. 使用外键，保证数据的关联性
   “ON DELETE CASCADE” 参数保证当 customerinfo 表中的一条客户记录被删除的时候，salesinfo 表中所有与该客户相关的记录也会被自动删除。
   如果要在 MySQL 中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表 InnoDB 类型。该类型不是 MySQL 表的默认类型，定义的方法是在 CREATE TABLE 语句中加上 TYPE=INNODB

CREATE TABLE customerinfo
(
	CustomerID INT NOT NULL ,
	PRIMARY KEY ( CustomerID )
) TYPE = INNODB;

CREATE TABLE salesinfo
(
	SalesID INT NOT NULL,
	CustomerID INT NOT NULL,
	PRIMARY KEY(CustomerID, SalesID),
	FOREIGN KEY (CustomerID) REFERENCES customerinfo (CustomerID) ON DELETECASCADE
) TYPE = INNODB;

10. 比较最好在相同类型之间比较，且有时由于数据库语句问题会导致索引无效

//下面sql语句虽然返回结果一样，但是后面的效率要优于前面的执行效率
SELECT * FROM order WHERE YEAR(OrderDate)<2001;

SELECT * FROM order WHERE OrderDate<"2001-01-01";
//同样的情形也会发生在对数值型字段进行计算的时候：
SELECT * FROM inventory WHERE Amount/7<24;

SELECT * FROM inventory WHERE Amount<24*7;

11. 在搜索字符型字段时，有时会使用 LIKE 关键字和通配符，这样虽然简单，但是是以牺牲系统性能为代价的

SELECT * FROM books WHERE name like "MySQL%"

//性能不如下面，他们返回结果一样
SELECT * FROM books WHERE name>="MySQL"and name<"MySQM"

12. 避免在查询中让 MySQL 进行自动类型转换，因为转换过程也会使索引变得不起作用
13. 注意 sql 语句尽可能使用大写字母，因为在执行时会默认从小写转换成大写，浪费时间
14. 尽可能不要在 sql 语句中使用运算符进行运算，或者是转型
15. 尽量不要使用 “+” 号作字符串连接中对于变量的引入，使用 StringBuilder 优于 String
16. 如 MySQL，可以调整内部变量来获得更高性能
    改变索引缓冲区长度 (key_buffer)
    改变表长 (read_buffer_size)
    设定打开表的数目的最大值 (table_cache)
    对缓长查询设定一个时间限制 (long_query_time)