mysql数据库优化

sql索引优化

如何分析sql查询

演示数据库说明：

使用mysql提供的sakil数据库，可以通过下边这个url获取这个数据库

http://dev.mysql.com/doc/index-other.html

sakil数据库的表结构信息可以通过下网站查看

http://dev.mysql.com/doc/sakila/en/sakila-installation.html

数据库基于mysql5.5版本的优化器有一定差别

如何发现有问题的sql？

使用Mysql慢查询日志对有效率问题的sql进行监控

.show variables like 'slow_query_log '

.set global slow_query_log_file='/home/mysql/sql_log/mysql-slow.log'

.set global log_queries_not_using_indexes=on;

.set global long_query_time=1

命令：

-- 是否开启慢查询日志

show variables like 'slow_query_log';

-- 看一下变量的设置 log_queries_not_using_indexes 没有使用索引的查询

show variables like '%log%';

-- 慢查询日志的记录的时间 生产环境最好不要设置为0

show variables like 'long_query_time';

-- 打开 log_queries_not_using_indexes的记录

set global log_queries_not_using_indexes=on;

-- 打开慢查询日志的记录

set global slow_query_log=on;

-- 查看 慢查询日志

show variables like 'slow%';

慢查询日志所包含的内容：

执行sql的主机信息

#User@Host:root[root] @localhost[ ]

sql的执行信息

#Query_time:0.00000024 Lock_time: 0.000000 Rows_sent:0 Rows_examined:0

SQL执行时间

set timestamp=1402389328

SQL的内容

select Concat('storage engine:'，@@storage_engine ) as INFO;

慢查询日志的分析工具

1：mysqldumpslow mysql自带的 反馈的信息比较少

--分析前三条慢sql

mysqldumpslow -t 3 日志地址 | more

2：pt-query-digest

输出到文件

pt-query-digest slow-log > slow_log.report

输出到数据库表

pt_query-digest slow.log -review \

h=127.0.0.1,D==test,p=root,P=3306,t=query_review \

--create-reviewtable \

--review-history t = hostname_slow

--分析前三条慢sql

pt_query-digest 3 日志地址 | more

如何通过慢查日志发现有问题的SQL？

1:查询次数多且每次查询占用时间长的sql？

通常为pt-query-digest分析的前几个查询

2：IO大的sql

注意pt-query-digest分析中的Rows examine项（扫描的行数）

3.未命中索引 的SQL

注意pt-query-digest分析中Rows examine 和 Rows Send的对比

（如果Rows examine 远远大于 Rows Send 说明索引命中率不高）

如何分析sql查询

使用explain查询SQL的执行计划

含义：

table : 显示这一行的数据是关于哪张表的

type : 这是最重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index 和 ALL

possible_keys : 显示可能应用在这张表中的索引。如果为空，没有可能的索引。

key : 实际使用的索引。如果为NULL，则没有使用索引。

key_len : 使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref : 显示索引的哪一列被使用了，如果可能的话，是一个常数

rows ：MYSQL认为必须键查的用来返回请求数据的行数。

Count() 和 Max()的优化方法

Max()：查询最后支付时间-优化Max () 参数

select Max(IMPORT_TIME) from event_src

explain select Max(IMPORT_TIME) from event_src

create index idx_importTime on event_src(IMPORT_TIME) 创建覆盖索引之后提高效率

Count()：

在同一条sql中同时查出2006和2007年的电影数量--优化count()函数

select count(release_year='2006' or null ) as '2006年电影数量'，

count(release_year='2007' or null ) as '2007年电影数量' from film;

优化Limit()查询

select film_id,description from sakila.film order by title limit 50,5;

优化步骤1：使用索引的列或者主键进行Order by 操作

能避免文件排序（use Filesorts）减少io，扫描行数是55 会随着limit后面的书改变还需要优化

select film_id,description from sakila.film order by film_id limit 50,5;

优化步骤2：记录上次返回的主键，在下次查询时使用主键过滤 避免了数据量大时扫描过多

的记录 （需要设置主键排序）

select film_id,description from sakila.film where film_id>55 and film_id

order by film_id limit 1,5;

如何选则合适的列建立索引？

1.在where从句，group by 从句，order by从句，on从句中出现的列

2.索引字段越小越好

3.离散度大的列放到联合索引的前面

select * from payment where staff_id =2 and customer_id=584;

是index(sftaff_id,customer_id )好，还是index(customer_id,staff_id)好？

由于 customer_id的离散度更大，所以应该使用index(customer_id,staff_id)

索引的维护及优化 ---重复及冗余的索引

重复索引是指相同的列以相同的顺序建立的同类型的索引，如下表中的primary key 和ID列上的索引就是重复索引

create table test(

id int not null primay key,

name varchar(10) not null,

title varchar(50) not null,

unique(id)

)engine=innodb

冗余索引是之多个索引 的前缀列相同，或是在联合索引中包含了主键的索引，下面这个例子中的key(name,id)就是一个冗余索引。

create table test(

id int not null primay key,

name varchar(10) not null,

title varchar(50) not null,

key(name,id)

)engine=innodb

使用pt-duplicate-key-checker工具检查重复及冗余索引

pt-dumplicate-key-checker \

-uroot \

-p ''\

-h 127.0.0.1

索引的维护及优化 ---删除不用的索引

目前MySQL中还没有记录索引的使用情况，但是在PerconMySQL和MariaDB中可以通过INDEX_STATISTICS表来看那些索引未使用，但在MySQL中目前只能通过慢查日志配合pt-index-usage工具来进行索引使用情况分析，

pt-index-usage \

-uroot -p' ' \

mysql -slow.log

数据库结构优化

选择合适的数据类型

数据类型的选则，重点在于合适二字，如何确定选则的数据类型是否合适？

1.使用可以存下你的数据的最小的数据类型

2. 使用简单的数据类型，Int要比varchar类型在mysql处理上简单。

3. 尽可能的使用not null 定义字段

4. 尽量少的用text类型，非用不可的时候最好考虑分表

使用int存储日期时间，利用FROM_UNIXTIME(),UNIX_TIMESTAMP()两个函数来进行转换

create table test(id int AUTO_INCREMENT NOT NULL

,timestr INT,PRIMARY KEY(id)); //创建int类型存储时间

-- 插入时用UNIX_TIMESTAMP（）

INSERT INTO test(timestr) VALUES(UNIX_TIMESTAMP('2014-06-01 13:12:00'));

-- 查询时用FROM_UNIXTIME（）

SELECT FROM_UNIXTIME(timestr) FROM test;

使用bigint存储ip地址，利用INET_ATON(),INET_NTOA()两个函数来进行转换

create table session(id int AUTO_INCREMENT NOT NULL

,ipaddress BIGINT,PRIMARY KEY(id)); //创建bigint类型存储ip

-- 插入时用INET_ATON（）

INSERT INTO sessions(ipaddress) VALUES(INET_ATON('192.168.0.1'));

-- 查询时用INET_NTOA（）

SELECT INET_NTOA(ipaddress) FROM sessions;

表的范式化和反范式化

范式化是指数据库设计的规范，目前说到范式化一般是指第三设计范式，也就是要求数据表中不存在非关键字段对人一候选关键字段的传递函数依赖则符合第三范式。

不符合第三范式的表会有以下问题：

1.数据冗余：（分类，分类描述）对于每一个商品都会有记录

2. 数据的插入异常，

3. 数据的更新异常

4. 数据的删除异常

反范式化是指为了查询效率的考虑把原本符合第三范式的表适当的增加冗余，以打到优化查询效率的目的，反范式化是一种以空间来换取时间的操作。