一、常见数据库的优化操作

1、表的设计要符合三范式。

2、添加适当的索引，索引对查询速度影响很大，必须添加索引。主键索引，唯一索引，普通索引，全文索引

3、添加适当存储过程，触发器，事务等。

4、读写分离（主从数据库）

5、对sql语句的一些优化，（查询执行速度比较慢的sql语句）

6、分表分区

分表：把一张大表分成多张表。分区：把一张表里面的分配到不同的区域存储，

7、对mysql服务器硬件的升级操作。

二、适时提高效率

第一范式

原子性：表里面的字段不能再分割，只要是关系型数据库，就天然的自动满足第一范式。

关系型数据库：（有行和列的概念）mysql,sql server,oracle,db2,infomix,sybase,postgresql

在设计时，先有库-》表-》字段-》具体记录（内容）：在存储数据时，要设计字段。

非关系型数据库（泛指nosql数据库）：memcache/redis/momgodb/等

第二范式

一个表中没有完全相同的记录，通过一个主键即能解决。

第三范式

表中不能存储冗余数据，

反三范式设计

三、定位慢查询

慢查询：找出在一个网站中，查询速度比较慢的语句，可以开启一个日志文件，记录查询速度比较慢的sql语句。在默认情况下，慢查询日志是关闭的，默认记录时间是超过10秒 的sql语句。

1、以记录慢查询的方式来启动mysql,

先关闭mysql,进入到mysql的安装目录。

关闭mysql服务：可以通过计算机-》管理->服务-》mysqld的服务名称，单击停止。

mysql的安装目录

通过慢查询日志定位执行效率较低的SQL语句。慢查询日志记录了所有执行时间超过long_query_time所设置的SQL语句。

2、查看慢查询日志的存储路径。

开启了慢查询日志后，会建立一个慢查询日志文件。该日志文件保存在数据库的目录下，数据库的目录可以通过配置文件查看。

慢查询日志存放在mysql的data目录下. 在my.ini配置文件有data目录

#Pathto the database root

datadir="C:/Documentsand Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/“

3、进程测试：

查看当前数据库下慢查询记录时间：

show variables like ‘long_query_time’;

修改慢查询时间：

set long_query_time=2;

通过如下的一个函数来进行测试：

benchmark(count,expr)函数可以测试执行count次expr操作需要的时间

一般情况下，出现查询比较慢的语句，是没有添加索引导致的。

四、索引

建立的测试表：

创建一个班级表：

1、主键索引

可以在建立表的添加createtable emp(id int primary key)

可以在建立完表之后，添加：altertable tablename add primary key(列1,列2)

主键索引的特点：

（1）一个表中最多只有一个主键索引

（2）一个主键索引可以指向多个列

（3）主键索引的列，不能有重复的值，也不能有null

（4）主键索引的效率高。

2、唯一索引

可以在建立表的时候添加：createtable emp(name varchar(32) unique)

在建完表之后，添加：

alter table tablename add unique [索引名](列名)

唯一索引的特点：

（1）一个表中可以有多个唯一索引

（2）一个唯一索引可以指向多个列 ，

比如alter tabletablename add unique [索引名](列1，列2)

（3）如果在唯一索引上，没有指定not null，则该列可以为空，同时可以有多个null,

（4）唯一索引的效率较高。

3、普通索引

使用普通索引主要是提高查询效率

添加alter table tablename add index [索引名]（列1，列3）

4、全文索引

mysql自带的全文索引mysql5.5不支持中文，支持英文，同时要求表的存储引擎是myisam。如果希望支持中文，有两个方案，

（1）使用aphinx中文版coreseek (来替代全文索引)

（2）插件mysqlcft。

5、查看索引

（1）show index from 表名

（2）show indexes from 表名

（3）show keys from 表名

（4）desc 表名

6、删除索引

（1）主键索引的删除：

alter table tablename drop primary key;要注意：在删除主键索引时，要首先去掉auto_increment属性。

（2）唯一索引的删除

alter table tablename drop index 唯一索引的名字

（3）普通索引的删除：

alter table tablename drop index 普通索引的名字

7、添加索引主要的问题：

（1）较频繁的作为查询条件字段应该创建索引

select* from emp where empno = 1

唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

select* from emp where sex = '男‘

更新非常频繁的字段不适合创建索引

select* from emp where logincount = 1

（2）不会出现在WHERE子句中字段不该创建索

索引是由代价的，虽然是查询速度提高了，但是，会影响增该删的效率。而且索引文件会占用空间。

五、explain的使用

该工具能够分析sql执行效率，但是并不执行sql语句。主要是查看sql语句是否用到索引。

语法：explain sql语句\G 或 desc sql语句\G

使用索引时：

没有使用索引时：

explain工具的参数说明：

会产生如下信息：

select_type:表示查询的类型。

table:输出结果集的表

type:表示表的连接类型

possible_keys:表示查询时，可能使用的索引

key:表示实际使用的索引

key_len:索引字段的长度

rows:扫描出的行数(估算的行数)

Extra:执行情况的描述和说明

六、索引

1、对于创建的多列(复合)索引，只要查询条件使用了最左边的列，索引一般就会被使用。

该查询条件没有使用到最左边的列，所以没有使用到索引。

该条件中使用了复合索引的最左边的列，因此该查询会使用到索引。

2、对于使用like的查询，查询如果是”%XXX”,不会使用到索引，‘XXX%’会使用到索引。

注意：在有些情况下，还是会用到like查询，比如通过歌词搜索歌名，通过剧情搜索电影名称。借助于工具，sphinx里面的coreseek软件。

3、如果条件中有or，则要求or的索引字段都必须有索引，否则不能用到索引。

该email添加索引后，在测试，会用到索引

4、如果列类型是字符串，一定要在条件中将数据使用引号引用起来，否则不使用索引。

5、优化group by语句

默认情况下， mysql对所有的group by col1,col2进行排序。这与在查询中指定order by col1,col2类型，如果查询中包括group by 但用户想要避免排序结果的消耗，则可以使用order by null禁止排序。

6、当取出的数据量超过表中数据的20%，优化器就不会使用索引，而是全表扫描。扫描的行数太多了，优化器认为全表扫描比索引来的块。

7、查看索引的使用情况

注意：

handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。

handler_read_rnd_next:这个值越高，说明查询低效。

8、对应大批量插入数据

对于MyISAM：

先禁用索引：

alter table table_name disable keys;

loading data//insert语句; 执行插入语句

执行完成插入语句后，开启索引，统一添加索引。

alter table table_name enable keys;

对于Innodb：

1，将要导入的数据按照主键排序

2，setunique_checks=0,关闭唯一性校验。

3，setautocommit=0,关闭自动提交。