1.数据库优化的可以从如下几个方面着手:
1.>sql语句和索引.
2.>数据库表结构.
3.>系统配置.
4.>硬件
2.使用MySQL的慢查询日志对效率有问题的sql进行监控
1.>查看慢查询日志是否开启:show variables like 'slow_query_log'
2.>根据查看出来的日志存放路径,开启慢查询日志set global slow_query_log_file='/var/lib/mysql/mylinux-slow.log' (file后面的地址可以通过show variables like '%log%';获取)
3.>开启没有用索引的查询记录在日志中:set global log_queries_not_using_indexes=on;
4.>设置最长的查询时间,如果超过该时间,则被记录到日志中.set global long_query_time=1;
3.SQL及索引优化.
3.1MySQL自带的慢查询日志分析工具:
mysqldumpslow -h 可以查看相关参数.
mysqldumpslow -t 3 /var/lib/mysql/mylinux-slow.log | more 查看前3条性能比较差的sql.
3.2专业的慢查询分析工具:pt-query-digest.
3.3使用该工具进行查看慢查询日志:pt-query-digest /var/lib/mysql/mylinux-slow.log | more 查看详情.
4.通过慢查询日志发现有问题的SQL
5.SQL优化
5.1 max和count查询的优化
explain select max(box_lunch_time) from cart_personal /G (竖着显示) 如果查询数据较多,比较慢的话,可以为box_lunch_time做索引,eg:
create index idxboxlunchtime on cart_personal(box_lunch_time);
count函数中值得注意的: count*会把空值也都计算进去,而count id则不会
5.2子查询优化
当采用子查询效率上不如连接查询时,将子查询改为连接查询,但有一点需要值得注意,就是连接查询不会屏蔽一对多时出现数据重复的情况,此时如果业务需要,可以采用distinct关键字来处理.
eg: select * from t where t.id in (select t1.tid from t1);
可以用: select t.id from t join t1 on t.tid =t1.tid;代替,当t1中的tid有两条相同的数据时,那么第一条查询语句查询出来的结果包只含1条数据,而第二条查询语句能查出2条数据,此时可以改为:
select distinct t.id from t join t1 on t.tid =t1.tid; 这样查询结果就与第一条sql查询结果一致了!
5.3group by 优化
优化前:
优化后:
5.4 limit查询优化
优化前:
优化步骤一:
在order by操作中,尽量使用主键或者索引列来进行order by,这样效率高.
优化步骤二:
如果数据量比较大时,Limit50000就要扫描500000次,IO比较大,因此需要进一步优化.值得注意的是,在这种情况下,id必须是连续的,顺序增长的,如果Id不连续,需要添加一列,idindex,保证它是自增且连续的,为它添加索引,即可.
6.索引优化
6.1如何选择合适的列建立索引?
1.在where从句,group by从句,order by从句 on从句中出现的列建立索引
2.索引字段越小越好
3.离散度大的列放到联合索引的前面.(如何判断离散度? 可以使用count语句,比如两个字段staff_id和customer_id,可以使用select count distinct(staff_id), count distinct(customer_id) from payment; 查询结果中对应的数字越大,说明离散度越大)
6.2索引优化sql的方法
1.重复索引
2.冗余索引
3.如何查询出重复索引和冗余索引?
SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.COLUMN_NAME,
a.INDEX_NAME AS 'index1', b.INDEX_NAME AS 'index2'
FROM information_schema.STATISTICS a
JOIN information_schema.STATISTICS b
ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
AND a.COLUMN_NAME = b.COLUMN_NAME
WHERE a.SEQ_IN_INDEX = 1 AND a.INDEX_NAME <> b.INDEX_NAME
4.查找重复/冗余索引的工具:pt-duplicate-key-checker 工具检查重复及冗余索引
下载地址：http://www.percona.com/redir/downloads/percona-toolkit/percona-toolkit-1.0.1.tar.gz
安装方法：
先安装依赖:yum install -y perl perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl-Digest-MD5 perl-ExtUtils-MakeMaker
然后进入解压后的目录 执行:
perl Makefile.PL；make；make install
使用:pt-duplicate-key-checker --host=localhost --user=root --password=123456 --database=ordering 进行查询,根据查询出的结果和工具给出的建议进一步优化索引.
4.索引维护
pt-index-usage --host=localhost --user=root --password=123456 /var/lib/mysql/mylinux-slow.log
删除那些不经常使用的索引.
7.数据库表结构的优化
7.1用Int来存储时间
7.2用bigint来存储Ip地址
7.3范式优化
主要优化第三范式,也就是传递依赖,不优化的话会造成数据冗余,插入异常,更新,删除等异常.
第三范式定义:要求数据库表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式.
比如下图这个列子:
因此可以对表进行拆分 ,把具有传递函数依赖的字段拆分开来,以上图例子为例,拆分后符合要求的表结构如下:
7.4 反范式化
在实际开发中,为了提高查询效率,可以把原本符合第三范式的表适当增加冗余,这样的话就可以达到优化查询效率的目的,原本需要关联三张表的操作可以在一到两张表里完成,是一种牺牲空间来换取时间的操作.
7.5数据库表的垂直拆分
7.6数据库表的水平拆分
8.数据库系统配置的优化
8.1操作系统配置优化
8.2Mysql的配置文件优化
8.3第三方配置工具来配置mysql
关于该工具的使用更多的可以参考这里:https://www.imooc.com/video/4165
9.硬件优化
