mysql 数据库优化案例

一 问题探究

1、先不动表和数据库，主要以监控为主，确定sql性能低下的原因。

       mysqladmin -uroot -padmin ext|awk 'Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf("%d %d %d\n",q,c,r)}'

      确定查询数，连接数， 查询量

      2、

#!/bin/bash
while true
do
mysqladmin -uroot ext|awk '/Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf("%d %d %d\n",q,c,r)}' >> status.txt
sleep 1
done

        以脚本的形式，保存每秒的查询数，主要是考虑使用了redis, memache的缓存组件， 是否会集中过期。

        得到每秒钟的查询数

awk '{q=$1 - last; last = $1; print $1} status.txt  绘制结构， 生成excel报表。

mysql status
sending data 查询有大量的记录
copying to tmp table 移动临时表到磁盘， 耗时
tmp_table_size 临时表大小
set global tmp_table_size = 1024;
set session tmp_table_size = 1024;
set profiling = 1;
show profiles ;

二 数据库优化

尽量使用整行 int > datetime >char > varchar > block  够用就行
尽量不要使用Null， 索引过长 普通key_len 3 使用Null是4, 

oworld 有优势。 


mysql 默认是hash索引， O(1) hash 计算的结果是随机的， 随机查询慢, 算的快，查的慢。

B_tree 快速的排序好的快速查找索引
B_tree 索引有排序优化，比如差hello 和hell

左前缀发挥规则
index(a, b, c)
where a 1
where a = 1 and b = 2  1
where a = 1 and b = 2 and c = 3 1


where a = 1 and b > 3 and c = 4  2 只能使用 a 和 b 俩个， 相当于死了一半

usering filesort


show profile for query 2;


mysql -u root -p66D95CAF21B86F6F -e 'show databases'


sysbench

myasim 采用的是搞笑的一个查询引擎，索引优化在内存中，以指针的形式保存索引， 所以效率比较高， 
比如
create table user(id int, name varchar(20)) key(id)， key(name) 其中id和name构建一个Btree索引指针，指向磁盘，效率比较高。
innodb是一个聚族索引，主键索引的节点下，挂着所有的数据，即使不加主键，也会自动给你生成主键， 这样如果order by 的时候特别慢， 同时name索引指向id索引

特别是order by 的时候要考虑是否要移动大量的节点去排序。 切记

查询的时候最好使用索引覆盖， 这样效率高， 

大数据分页方法， 一是使用主键，二是使用索引覆盖，这样效率高。 不要Limt 1, 100这样扫描全表。

select * from user inner join(select id from user limit 1, 100) as tmp as x.id =tmp.id;

select * from user where id > 10000 limit 10;

确定索引的长度,char utf是三个字节， varchar 可变的，多一个到2个字节， 

有个方法确定索引长度，

比如 select distinct (left(name, 1))  from user;
确定使用索引的长