mysql数据库优化

mysql数据库性能优化

 

个人博客网站：http://01time.cn

mysql数据库的优化，其他数据库类似

1.数据库优化之定位

查找定位慢查询

2.数据库优化手段

• 创建索引：创建合适的索引，我们就可以先在索引中查询，查询以后直接找对应的记录
• 分表：当一张表的数据比较多或者一张表的某些字段的值比较多并且很少使用时，采用水平分表或者垂直分表来优化
• 读写分离：当一台服务器不能满足需求时，采用读写分离的方式进行集群
• 缓存：采用redis来进行缓存

3.数据库优化之定位慢查询的方式

在项目自验项目转测试之前，在启动mysql数据库时开启慢查询，并且把执行慢查询的语句写到日志中，在

运行一定时间后，通过查看日志找到慢查询语句

开启慢查询方式一:修改配置文件

Windows：Windows 的配置文件为 my.ini，一般在 MySQL 的安装目录下或者 c:\Windows 下。

Linux：Linux 的配置文件为 my.cnf ，一般在 /etc 下

在 my.ini 增加几行:

 

long_query_time     ：  设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10s
slow_query_log      ：  指定是否开启慢查询日志
log_slow_queries    ：  指定是否开启慢查询日志(该参数要被slow_query_log取代，做兼容性保留)
slow_query_log_file ：  指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log
min_examined_row_limit：查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
log_queries_not_using_indexes: 不使用索引的慢查询日志是否记录到索引

4.数据库优化之分析慢查询

使用explain慢查询语句，来详细分析慢查询语句

 

mysql>explain select * from dept where loc='aaa' \G
*********************1.row ********************
               id:1                //查询序列号
select_type:SIMPLE      //查询类型
          table:dept          //查询的表名
          type:ALL  所描的方式，all表示全表扫描这列很重要,显示了连接使用了哪                                             种类别,有无使用索引.
从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL 
          possible_keys:NULL//可能使用到的索引
          key:NULL//实际使用到的索引
          key_len:NULL//
          rows:10//该sql语句扫描了多少行，可能得到的记录
          Extra:Using where //sql语句的额外信息，比如排序方式filesort

 

 

  5.数据库优化之遵循范式

   数据库表设计时需要遵循范式

  表的范式，是首先符合1NF，才能满足2NF，进一步满足3NF

1NF：即表的列具有原子性不可再分解，即列的信息，不能分解，只要数据库是关系型数据库，就自动的满足1NF，关系型数据库中是不允许分割列的

2NF：表中的记录是唯一的，通常我们设计一个主键来实现

3NF：即表中不要有冗余的数据，就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放（外键）

反3NF：没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当的保留冗余数据，具体做法是：在概念数据库设计时遵循第三范式，降低范式标准的工作放到物理数据模型设计时考虑，降低范式就是增加字段，允许冗余，订单和订单项，相册浏览次数和照片的浏览次数等

6.数据库优化之选择合适的存储引擎

在开发中，我们经常使用的存储引擎myisam/innodb/memory

MyISAM存储引擎

 如果表对事物要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎，比如bbs中的发帖表，回复表。

INNODB存储引擎

对事务要求高，保存的数据都是重要数据，我们建议使用INNODB存储引擎，比如订单表，账号表

Memory存储引擎

我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用Memmory存储引擎，速度极快

MyISAM存储引擎与INNODB存储引擎的主要区别：

1.事务安全 myisam不支持事务而innodb支持

2.查询和添加速度  myisam不用支持事务就不用考虑同步锁，查询和添加的速度快

3.锁机制 myisam只支持表锁，innodb支持行锁

4.外键 myisam不支持外键，innodb支持外键

5.支持全文索引 myisam支持全文索引，innodb不支持全文索引

7.数据库优化之创建合适的索引

索引（Index）是帮助DBMS高效获取数据的数据结构

分类：普通索引/唯一索引/主键索引/全文索引

普通索引：允许重复的值出现

唯一索引：除了不能有重复的值外，其他的和普通索引一样

主键索引：是随着设定主键而创建的，也就是把某个列设为主键的时候，数据库就会给该列自动创建索引，这就是主键索引，唯一并且没有null值

全文索引：用来对表中的文本域（char,varchar,text)进行索引，全文索引针对myisam

8.数据库优化之索引使用小技巧

索引弊端

1.占用磁盘空间

2.对dml（插入，修改，删除）操作有影响，变慢

使用场景

a:肯定在where条件经常使用，如果不做查询就没有意义

b:该字段的内容不是唯一的几个值

c:字段内容不是频繁变化，不会出现在where语句中字段不该创建索引

具体技巧

1.对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引

 

alert table dept add index my_index(dname,loc);//dname左边的列，loc就是右边的列
explain select * from dept where dname='aaa' \G会使用到索引
explain select * from dept where loc='aaa' \G就不会使用到索引

 2.对于使用like查询，查询如果是'%aaa' 就不会使用到索引，而‘aaa%'会使用到索引

 

 

explain select * from dept where dname like '%aaa' \G 不会使用到索引
explain select * from dept wehre dname like 'aaa%' \G 会使用到索引

  所以在like查询时，关键字的最前面不能使用%或者_这样的字符，如果一定要前面有变化的值，则考虑使用全文索引->sphinx

 

3.如果条件中有or，有条件没有使用索引，即使其中有条件带索引也不会使用，换言之，就是要求使用的所有字段，都必须单独使用时能使用索引

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

explain select * from dept where dname='111'//使用索引
explain select * from dept where dname =111//不使用索引

5.如果myslq估计使用全表扫描比使用索引快，则不用索引

如：表里面只有一条记录

9.数据库优化之分表

分表分为水平（按行）分表和垂直（按列）分表

水平分表:

根据经验，mysql表数据一般达到百万级别，查询效率会很低，容易造成表锁，甚至堆积很多连接，直接挂掉；水平分表能够很大程度减少这些压力，按行数据进行分表

垂直分表:

如果一张表中某个字段非常多（长文本，二进制），而且只有在很少的情况下会查询，这时候就可以把字段多个单独放到一个表，通过外键关联起来。按列进行分表

比如：考试详情表，一般我们只关注分数，不关注详情

水平分表策略

1.按时间分表

    这种分表方式有一定的局限性，当数据有较强的实效性，如微博发送记录，微信消息记录等，这种数据很少有用户会查询几个月前的数据，如就可以按月分表

2.按区间范围分表

   一般在有严格的自增id需求上，如按照user_id 水平分表：

   table_1 user_id 从1~100w

   table_2 user_id 从101~200w

    table_3 user_id 从201~300w

3.hash分表******（用的最多）

   通过一个原始目标的ID或者名称通过一定的hash算法计算出数据存储表的表名，然后访问相应的表

10.数据库优化之读写分离

    当一台数据库支持的最大并发连接数是有限的，如果用户并发访问太多，一台服务器满足不了要求时，就可以集群群里，mysql的集群处理技术最常用的就是读写分离。

1.主从同步

         数据库最终会把数据持久化到磁盘，如果集群必须确保每个数据库服务器的数据是一直的，能改变数据库的操作都往主数据库去写，而其他的数据库从主数据库上同步数据

2.读写分离

使用 负载均衡来实现写的操作都往主数据库去，而读的操作都往从服务器去

 

11.数据库优化之缓存

      在持久层（dao）和数据(db)之间添加一个缓存层，如果用户访问的数据已经缓存起来时，在用户访问数据库直接从缓存中获取，不用访问数据库，而缓存是在内存级别的，访问速度快。

作用：减少数据库服务器压力，减少访问时间。

java中常用的缓存有reids，memcache

可以使用redis作为中央缓存