数据库优化

数据库

自己总结一些关于数据库优化的一些东西，没事的时候也可以查看一下；

#数据库的优化#

sql优化

  1. sql优化分析
  2. 索引优化
  3. 常用sql优化
  4. 常用优化技巧

数据库对象优化

  1. 优化表的数据结构
  2. 表拆分
  3. 逆规范式
  4. 使用中间表

mysql服务器优化

  1. mysql内存管理优化
  2. log机制优化
  3. 调整mysql并发相关参数

应用优化

  1. 数据库连接池
  2. 使用缓存减少压力
  3. 负载均衡建立集群
  4. 主主同步 主从同步 
1
mysql -uroot -p; //登录mysql服务器
1
show status; //查看服务状态

部分status参数说明

查询当前mysql本次启动后运行的时间

1
show status like "uptime";

查询当前mysql本次启动执行select的次数

1
show status like "com_select";

查询当前mysql本次启动执行update的次数 delete create都是类似的

1
show status like "com_update";

查询mysql服务器线程信息

1
show status like "Thread_%";

查询是图连接mysql服务器的数量，不管成功与否

1
show status like "connections";

查询是图连接mysql服务器的数量，不管成功与否

1
show status like "connections";

看创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。

1
show status like "threads_created";

查看立即获得的表的锁的次数。

1
show status like "table_locks_immediate";

查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制。

1
show status like "table_locks_waited";

查看创建时间超过slow_launch_time秒的线程数

1
show status like "slow_launch_threads";

查看查询时间超过long_query_time秒的查询的个数

1
show status like "slow_queries";

##命令操作##

1
show processlist //查看连接的进程数

##分析执行的sql语句##
1.explain
2.desc
3.select_type

###desc###

1
desc select * from blog;

简单分析一下扫描值的含义

select_type

  1. simple 简单表，不使用表连接或字查询。
  2. primary 主查询，即外层查询。
  3. union union在的第二个或者后面的查询语句
  4. subquery 子查询的第一个select

type

  1. all 全表扫描
  2. index 索引全扫描
  3. range 索引范围扫描
  4. ref 使用非唯一索引或唯一索引的前缀扫描
  5. eq_ref 类似ref，使用索引是唯一索引
  6. const/system 单表中最多有一个匹配行
  7. null 不使访问表或者索引，直接获得了结果 效率最高的就是null > const/system > all

###show profile###

1
select @@have_profiling; //查看数据是否支持 show profile

1
select @@profiling; //是否开始 show profile
1
set profiling = 1; //如果没有开启，开启profiling
1
show profiles; //查看profiles
1
show profile for query 2; //查看query_id为2的详情信息

##mysql索引优化##

索引的查看 

1
show index from table_name;
1
show key from table_name;

####b-tree索引####
最常见的索引类型，大部分的都支持

####hash索引#####
只有memory引擎支持，使用场景单一

####r-tree索引####
myisam的一个特殊的索引，空间索引主要是地理空间类型的数据

####full-text全文索引####
myisam的一个特殊的索引，Innodb从5.6开始支持

###索引创建###

  1. primary key 主键索引
  2. unique key 唯一索引
  3. index key 不同索引
  4. 全文索引
  5. 联合索引

##mysql中使用索引的方法##

  1. 匹配全值 

    1
    desc select id,title,class_id from blog where id = 1 and class_id = 2;

  2. 匹配值范围查询 大于小于 

    1
    desc select id,title,class_id from id > 10 and class_id > 10;

  3. 匹配最左前缀 联合索引的时候使用

  4. 仅仅对索引进行查询 不要使用*进行查询

  5. 匹配列前缀 

    1
    desc select title from blog where title like 'news%';

  6. 部分精确+部分范围 

    1
    desc select id,title,class_id from id > 10 and class_id = 10;

##不能使用索引的情况##

  1. 以%开头的like查询
  2. 数据类型出现隐式转换 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    select title from blog where id = "3"; //id是int类型的，这里带有了引号，类型转换了
    ```  
    3. 符合查询的条件不包含最左部分
    4. 即使使用索引但比全表扫描还慢
    5. 用or分隔开的条件

    经过自己测试发现int类型的数据并不会产生数据类型的隐式转换，对于索引是没有影响的；
    但是字段是字符型的时候就会有影响了，id char类型 id = 1 type=all，索引没有生效；

    ##定期优化表##
    1. optimize table table_name; //这个应该只能myisam进行使用
    2. 对myisam和innodb有效
    3. 合并表空间随便，消息空间浪费

    默认情况下对innodb使用optimize会有错误的提示；  
    Table does not support optimize, doing recreate + analyze instead  
    这个时候可以mysqld --skip-new 或者 mysqld --safe-mod 命令重启mysql 以便让其他引擎支持optimize

    ##查询优化##

    1. 查询的时候尽量避免全表扫描，首先考虑在where和orderby上建立索引
    2. 尽量避免在where中使用!= <> 操作符，一般会放弃使用索引进行全表扫描
    3. 尽量避免where字句中使用or连接条件，否则引擎放弃使用索引，进行全表扫描
    4. 乱用%导致全表扫描

desc select title from blog where match(title) agninst(“test”); //全文索引的使用，不支持中文一般不怎么使用

1
5. 尽量避免在where语句中使用表达式，引擎会放弃使用索引

desc select title from blog where id/2 = 4; //没有使用索引

1
2
```
desc select title from blog where id = 8; //使用索引

  1. 应当避免在where字句中使用函数进行操作

##常用优化##

  1. 优化嵌套查询，子查询可能需要在内存建立临时表，而关联查询不用建立临时表
    1
    select title from blog where class_id in (select id from class); //内存中建立了临时表
1
select title from blog as b left join class as c on n.class_id = c.id
  1. 不要使用select * 这种查询形式
  2. 组和索引，最左原则
  3. 很多时候使用 exists 代替 in 是一个好的选择 ； 区别不大
  4. 当索引中有大量的重复数据时，索引可能不会生效; type = 1 //测试发现2条重复的时候就不会使用索引了，这个时候不建立索引更好些

##数据库对象的优化##
procedure analyse()对当前应用进行分析，它会给出优化方案，用户可以自己选择是否进行优化，还是有些用处的

1
select * from blog procedure analyse();

##表拆分##

  1. 垂直拆分 字段多 拆分为两个表，主键id是相同的
  2. 水平拆分 数据多
    a. 表很大
    b. 表中的数据有独立性
    c. 需要把表存在多种介质
    ##使用中间表##
  3. 数据查询量大
  4. 数据统计，数据分析

##关于表引擎的命令##

  1. 查看存储引擎

    1
    show engines;

  2. 查看默认的存储引擎

    1
    show variables like '%storage_engine';

  3. 查看某一个表的具体引擎

    1
    show create table tablename;
1
show table status from blog where name = 'test';

##innodb引擎##

  1. 提供事务回滚，崩溃修复以及多版本并发的事务安全操作
  2. 支持自增长列 auto_increment
  3. 支持外键
  4. 支持事务以及事务关联操作
  5. 支持mvcc行锁
    ##myisam##
  6. 不支持事务和行级锁，只支持并发插入的表锁，主要用户高负载的select
  7. 三种类型存储结构； 静态型 动态型 压缩型

##myisam内存优化##

  1. key_buffer_size
    决定缓存区的大小，直接影响myisam表的存取效率，建议四分之一的内存； 

    1
    show variables like "%key%";

  2. read_buffer 读缓存

  3. write_buffer 写缓存 
    1
    vim /etc/my.cnf

##innodb内存优化##

  1. innodb_buffer_pool_size //存储引擎和索引数据的最大缓冲区大小 50%-80% 数据库服务器的话
  2. innodb_old_blocks_pct LRU 决定old sublist的比例
  3. innodb_old_blocks_time LRU 数据转移的间隔时间
    1
    show variables like "%old_blocks%";

##mysql并发相关参数##

  1. max_connections 控制mysql的最大连接数 默认151 如果 connection_errors_max_connections不为0且一直增加，考虑调整

    1
    show variables like "%_connections%";

  2. back_log 如果需要数据库短时间内大量的链接，可以考虑适当增大这个值

  3. table_option_cache 控制sql执行线程打开的缓存的数量，这个参数应该根据最大连接数max_connections以及每个关联查询所中所涉及的最大个数决定
  4. thread_cache_size 控制mysql缓存客户端线程数量，加快数据库的连接速度。可以根据cacahe失效率threads_created/connections来衡量thread_cache_size是否合适
  5. innodb_lock_wait_timeout 控制innodb事务等待行锁的时间，默认50ms

##数据库主从复制以及读写分离的配置##

101.201.221.41 master主服务器
101.200.132.171 sliver从服务器

  1. 配置主库数据库参数
    vim /etc/my.cnf
    开启 log-bin=mysql-bin
    server-id = 1
    1
    service mysql restart; //重启mysql
1
show master status; //进入数据库后，查看一下master
  1. 添加一个操作用户并且授权 
    1
    2
    CREATE USER 'fuzhi'@'%' IDENTIFIED BY '123456'; //添加一个fuzhi的用户
    ```

grant replication slave on . to ‘fuzhi‘@’101.200.132.171’ identified by ‘123456’; //添加授权

1
2
3
4
5
```
flush privileges; //刷新授权       
```  

3. 实际的环境可以先锁定一下表，最后再解除锁定

FLUSH TABLES WITH READ LOCK; //锁表 unlock tables; //解锁表

1
2
3
4
5
6
7
8
9

4. 从库的配置修改
导入主库的sql数据文件  
vim /etc/my.cnf   
开启 log-bin=mysql-bin  
server-id = 3  
重启数据库服务

5. 在从库中链接主库的mysql

mysql -h101.201.221.41 -ufuzhi -p //测试一下主库连接是否成功

1
2
3
```
change master to master_host = '101.201.221.41',master_user='fuzhi',master_password='123456',master_log_file='mysql-bin.000087',master_log_pos=120;
```

start slave; show slave status;

1
2
3
4
5
6
7
如果中间修改，先stop slave 然后重新运行 change；  
这是不同版本间出现的checksum的问题，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none  
我们可以只在我们主库进行写操作，在从库进行读操作，这样就可以实现读写分离了；

##主主复制##
多个master可以避免一个数据库服务器出问题了，应用就不能运行的问题；  
A

CREATE USER ‘fuzhi‘@’%’ IDENTIFIED BY ‘123456’; //添加一个fuzhi的用户

1
2
3
```
grant replication slave on *.* to 'fuzhi'@'101.200.132.171' identified by '123456'; //添加授权 
```

flush privileges; //刷新授权

1
B

CREATE USER ‘fuzhi‘@’%’ IDENTIFIED BY ‘123456’; //添加一个fuzhi的用户

1
2
3
```
grant replication slave on *.* to 'fuzhi'@'10.0.0.11' identified by '123456'; //添加授权 
```

flush privileges; //刷新授权
` 


//A服务器修改的内容
vim /etc/my.cnf
log-bin = mysql-bin
server-id = 1
binlog-do-db = fafu_platform //同步的数据库
binlog-ignore-db = mysql //忽略数据库1
binlog-ignore-db = test //忽略数据库2
slave-skip-errors = all
auto_increment_increment = 2
auto_increment_offset = 1


flush tables with read lock;
show master status\G;
change master to master_host = '192.168.193.131',master_user='master',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=615;

本文标题:数据库优化

文章作者:wenqing

发布时间:2017-02-08, 11:15:52

最后更新:2018-08-27, 14:51:25

原始链接:http://yoursite.com/2017/02/08/数据库优化/

许可协议: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。

文章目录
  1. 1. sql优化
  2. 2. 数据库对象优化
  3. 3. mysql服务器优化
  4. 4. 应用优化
  5. 5. 部分status参数说明
|