mysql数据库优化小结

àì夳堔傛蜴生んèń 2022-05-06 12:28 229阅读 0赞

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

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操作需要的时间

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

四、索引

建立的测试表：

[?][Link 1]

<table> 
 <tbody> 
 <tr> 
 <td> &nbsp; 1 &nbsp; 2 &nbsp; 3 &nbsp; 4 &nbsp; 5 &nbsp; 6 &nbsp; 7 &nbsp; 8 &nbsp; 9 &nbsp; 10 &nbsp; 11 &nbsp; 12 </td> 
 <td> &nbsp; create table &nbsp;user( &nbsp; &nbsp;&nbsp;&nbsp;id&nbsp;int primary &nbsp;key &nbsp;auto_increment, &nbsp; &nbsp;&nbsp;&nbsp;name varchar(32)&nbsp;not null &nbsp;default &nbsp;'', &nbsp; &nbsp;&nbsp;&nbsp;age tinyint unsigned&nbsp;not null &nbsp;default &nbsp;0, &nbsp; &nbsp;&nbsp;&nbsp;email&nbsp;varchar(32)&nbsp;not null &nbsp;default &nbsp;'', &nbsp; &nbsp;&nbsp;&nbsp;classid&nbsp;int not &nbsp;null &nbsp;default &nbsp;1 &nbsp; )engine myisam charset utf8; &nbsp; insert into &nbsp;uservalues(null,'xiaogang',12,'gang@sohu.com',4), &nbsp; (null,'xiaohong',13,'hong@sohu.com',2), &nbsp; (null,'xiaolong',31,'long@sohu.com',2), &nbsp; (null,'xiaofeng',22,'feng@sohu.com',3), &nbsp; (null,'xiaogui',42,'gui@sohu.com',3); </td> 
 </tr> 
 </tbody> 
</table>

创建一个班级表：

[?][Link 1]

<table> 
 <tbody> 
 <tr> 
 <td> &nbsp; 1 &nbsp; 2 &nbsp; 3 &nbsp; 4 &nbsp; 5 </td> 
 <td> &nbsp; create table &nbsp;class( &nbsp; &nbsp;&nbsp;&nbsp;id&nbsp;int not &nbsp;null &nbsp;default &nbsp;0, &nbsp; &nbsp;&nbsp;&nbsp;classname&nbsp;varchar(32)&nbsp;not null &nbsp;default &nbsp;'' &nbsp; )engine myisam charset utf8; &nbsp; insert into &nbsp;classvalues(1,'java'),(2,'.net'),(3,'php'),(4,'c++'),(5,'ios'); </td> 
 </tr> 
 </tbody> 
</table>

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、对于创建的多列(复合)索引，只要查询条件使用了最左边的列，索引一般就会被使用。

[?][Link 1]

<table> 
 <tbody> 
 <tr> 
 <td> &nbsp; 1 &nbsp; 2 &nbsp; 3 </td> 
 <td> &nbsp; mysql&gt;&nbsp;alter table &nbsp;user &nbsp;add &nbsp;index &nbsp;(name,email); &nbsp; Query OK, 5&nbsp;rows affected (0.08 sec) &nbsp; Records: 5 Duplicates: 0&nbsp; Warnings: 0 </td> 
 </tr> 
 </tbody> 
</table>

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

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

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、查看索引的使用情况

注意：