mysql优化

---

mysql优化概述

前面我们讲页面静态化，memcache是通过减少对mysql 操作来提升访问速度，但是一个网站总是要操作数据库，我们如何提升对mysql的操作速度。
方针：
设计角度：存储引擎的选择，字段类型选择，范式。
利用mysql自身的特性：索引，查询缓存，分区分表，存储过程，sql语句的优化
部署大负载架构体系：主从复制（读写分离）

存储引擎的选择

存储引擎是什么？是数据库的文件系统，是mysql数据库服务器存储数据的数据结构，处于最底层的状态。

001.jpg-31kB

innodb存储引擎

从mysql5.5.x开始，默认的存储引擎变更为innodb引擎，支持事务ACID属性（原子性一致性，隔离性，持久性），是为处理巨大数据量时拥有最大性能而设计的。它的cpu效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。

数据存储方式

表结构，单独是一个文件，文件名为 table.frm
表数据和表的索引是存储到data目录下面的 ibdata1里面的。

002.jpg-17.6kB

数据记录的存储是按照主键顺序插入的

create table t1(
    id int primary key,
    name varchar(32)
)engine innodb charset utf8;

insert into t1 values(4,'xiaogang'),(2,'xiaolong'),(1,'dagang'),(3,'xiaofeng');

当有大量数据插入时，会变慢，会影响插入效率，因为是按照主键顺序插入，要有一个排序的过程。

并发性

实现了行锁，擅长并发处理，不会影响其他行的操作

数据完整性

支持事务ACID属性（原子性一致性，隔离性，持久性）

myisam存储引擎

索引序列管理方法是indexed sequential access method(索引顺序存取方法)的缩写优势，在索引的处理上索引独立存储

数据存储方式

表结构、表数据、表索引是分别来存储的。创建一个myisam引擎的表后，会形成三个文件

002.png-4.6kB

数据记录的存储是按照插入顺序存储的

create table t2(
    id int primary key,
    name varchar(32)
)engine myisam charset utf8;

insert into t2 values(4,'xiaogang'),(2,'xiaolong'),(1,'dagang'),(3,'xiaofeng');

并发性

实现的表锁，不擅长并发处理，锁定整张表后，会影响其他的进程操作该表

支持全文索引

在最新的mysql5.6以后，innodb引擎也支持全文索引了

memory

一些访问频繁，变化频繁，又没有必要入库的数据，比如用户在线状态
memory(数据是存储到内存里面的，重启mysql服务会丢失) 如果没有memcached或者redis, 但是数据操作频繁，可以考虑使用memory存储引擎,比如好友在线状态。适合做高速缓存。
查看存储引擎：show engines;

002.png-11.5kB

查找需要优化的sql语句

要对，执行速度比较慢的sql语句进行优化，如何查找执行速度比较慢的sql语句呢？

慢查询日志

是一种mysql提供的日志，记录所有执行时间超过某个时间界限的sql的语句。这个时间界限，我们可以指定。在mysql中默认没有开启慢查询，即使开启了，只会记录执行的sql语句超过10秒的语句。
开启慢查询日志：在配置文件中my.ini文件，

002.png-33.9kB

该慢查询日志存储的位置是：默认是和数据表同一个目录里面。

002.png-20kB

使用命令查看慢查询日志的时间界限：
show variables like ‘long_query_time’

002.png-2.8kB

也可以通过命令，在当前会话下重新设置慢查询日志的时间界限。
set long_query_time=1;

002.png-7kB

测试慢查询日志：

使用benchmark(count,expr)函数可以测试执行count次expr操作需要的时间。

002.png-2.5kB

打开慢查询日志的文件进行查看：

002.png-11.3kB

使用mysql的profiles机制

该机制精确的记录执行sql语句的时间，精确到小数点后8位。
开启profile机制
执行 set profiling=1

002.png-1.4kB
使用show profiles查看sql语句的执行时间；
002.png-4.1kB
关闭profiles机制
set profiling=0,如果不需要查找执行的慢的sql语句，要关闭该机制。
002.png-1.4kB
一般情况下，一个sql语句执行速度比较慢原因是没有添加索引。
002.png-8.6kB

索引的讲解

索引就是，利用关键字的某些特性，快速定位数据的一种技术。

索引的分类

普通索引：
利用特定的关键字，标识数据记录的位置（磁盘上的位置，盘号，柱面，扇面，磁道）。
唯一索引：
限制索引的关键字不能重复的索引，数据字段内容可以为null，一个表中可以有多个唯一索引。
主键索引：
限制索引的关键字不能重复，并且不能为NULL。（不能为NULL的唯一索引）。一个表中只允许有一个主索引。
全文索引：
索引的关键字，不是某个字段的值，而是字段值中有意义的词来作为关键字建立索引。
复合索引，如果一个索引（以上四种任何都可以），是依赖于多个字段创建的化，称之为复合索引。

创建索引的语法

//是在创建表时，直接创建索引。
create table index1(
    id int auto_increment comment '主键索引',
    name varchar(32)  comment '唯一索引',
    age int comment '普通索引',
    intro varchar(256) comment '全文索引',
    primary key (id),
    unique key (name),
    index (age),
    fulltext index (intro)
)engine myisam charset utf8;

//在创建表完成后，再修改表结构创建索引。
create table index2(
    id int auto_increment comment '主键索引',
    name varchar(32)  comment '唯一索引',
    age int comment '普通索引'
)engine myisam charset utf8;

//添加索引
alter table index2 add unique key (name),
add index (age),
add fulltext index (intro);

查看索引

show index from table_name;
show indexes from table_name
desc table_name
show create table_name

002.png-10kB

删除索引

删除主键索引
alter table table_name drop primary key ;
在主键索引时，如果有auto_increment属性，则不能直接删除主键索引的，要先删除auto_increment属性，再删除主键索引。

002.png-11.5kB

删除非主键索引；
alter table table_name drop index 索引名称

002.png-12.3kB

创建索引的注意事项

//（1）较频繁的作为查询条件字段应该创建索引
select * from emp where empno = 1 
//（2）唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
select * from emp where sex = '男' ;
//（3）更新非常频繁的字段不适合创建索引
select * from emp where logincount = 1 
//（4）不会出现在WHERE子句中字段不该创建索 

索引的数据结构

002.png-5.5kB

1、myisam引擎的索引的数据结构。

索引的节点中存储的是数据的物理地址（磁道和扇区）
在查找数据时，查找到索引后，根据索引节点中的物理地址，查找到具体的数据内容。排好序的快速查找结构

002.png-21.2kB

2、innodb引擎的索引结构

innodb的主键索引文件上 直接存放该行数据,称为聚簇索引,非主索引指向对主键的引用（非主键索引的节点存储是主键的id）

002.png-35.7kB

注意: innodb来说,
1: 主键索引 既存储索引值,又在存储行的数据
2: 如果没有主键, 则会Unique key做主键
3: 如果没有unique,则系统生成一个内部的rowid做主键.
4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

explain(执行计划)工具使用

主要分析索引的使用情况，分析 当前查询是否用到了索引，索引效率如何。
语法：explain sql语句\G或desc sql语句\G

002.png-5.3kB

以下添加索引和没有索引的对比情况。

002.png-12.9kB

type列：是指查询的方式，非常重要，是分析“查数据过程”的重要依据。
可能的值：all index range ref const

all：

是扫描所有的数据行。

002.png-5.5kB

index:

比all性能稍好一点，是指要扫描所有的节点，即在索引文件中进行查找，无需根据物理地址查找具体的数据。

索引覆盖的查询情况下，能利用上索引，但是又必须全索引扫描。

002.png-7.6kB

是利用索引来排序，但只能取出索引的列。

002.png-5.4kB

range:

002.png-5.5kB

意思是查询时，能根据索引做范围扫描，根据索引查找出一部分数据。id>10000就决定了要查找出一部分数据。

ref:

是指，通过索引列，可以直接引用到某些数据行

002.png-5kB

const,system,null

这3个分别指查询优化到常量级别，甚至不需要查找时间。
一般按照主键来查询时，易出现 const,system
或者直接查询某个表达式，不经过表时，出现null.

002.png-4.9kB
002.png-5.5kB

rows:

是指估计要扫描多少行。

extra:

using index :是指用到了索引覆盖(直接在索引文件中查找数据，无需定位数据所在的实际位置)，效率非常高
using where:是指光靠索引定位不了，还得where判断一下。
using temporary:是指用上了临时表，group by 与order by不同列时，或grop by,order by 别的表的列。
using filesort:文件排序（文件可能在磁盘，也可能在内存）

索引的使用细节

多列索引（复合索引）

对于创建的多列(复合)索引，只要查询条件使用了最左边的列，索引一般就会被使用。

002.png-10.9kB
002.png-9.3kB

因为组合索引是需要按顺序执行的，比如c1234组合索引，要想在c2上使用索引，必须先在c1上使用索引，要想在c3上使用索引，必须先在c2上使用索引，依此。

//假设某个表有一个联合索引（c1,c2,c3,c4）
alter table table_name add index (c1,c2,c3,c4)
A where c1=x and c2=x and c4>x and c3=x 
B where c1=x and c2=x and c4=x order by c3 
C where c1=x and c4= x group by c3,c2 
D where c1=x and c5=x order by c2,c3 
E where c1=x and c2=x and c5=x  order by c2,c3 

例如：where cat_id=5 and shop_price>100.00;//查询第5个栏目，100元以上的商品。
误区：cat_id和shop_price上都加上索引。
只能用上cat_id或shop_price索引，因为是独立的索引，同时只能用上一个
可以创建一个cat_id和价格的复合索引。

对于使用like的查询，查询如果是”%aaa”,不会使用到索引，‘aaa%’会使用到索引

002.png-9.8kB

比如根据歌词查找歌曲名称，根据电影剧情来查找电影名称，该场合一般使用like ‘%’开头的查询，使用后面讲的sphinx解决。

如果条件中有or，则要求or的索引字段都必须有索引，否则不能用到索引

比如id建立了主键索引，name建立的普通索引，进行测试查询

002.png-11.5kB

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

002.png-10.1kB

优化group by语句

默认情况下， mysql对所有的group by col1,col2进行排序。这与在查询中指定order by col1,col2类型，如果查询中包括group by 但用户想要避免排序结果的消耗，则可以使用order by null禁止排序。
数据输出的结果：

002.png-5.2kB

使用group by输出结果，发现根据classid排序了。

002.png-3.3kB
在默认情况下面使用group by 会根据group by的字段进行排序。
002.png-5.7kB
添加完成order by null,就没有对calss_id排序，按原来插入的顺序来显示
002.png-3.6kB

当取出的数据量超过表中数据的20%，优化器就不会使用索引，而是全表扫描

002.png-10.8kB

查看索引的使用情况

show status like ‘Handler_read%’;
大家可以注意：
handler_read_key:利用索引获得纪录的次数。 
这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next:这个值越高，说明查询低效

002.png-5.4kB

索引覆盖

索引覆盖是指：如果查询的列恰好是索引的一部分，那么查询只需要在索引文件上进行，不需要回行到磁盘再找数据，这种查询速度非常快，称为“索引覆盖”
比如使用name 建立索引，要查的字段是name,就用到了索引覆盖。
比如使用index (name,age,email)建立的复合索引，要查到字段是name,age,email此时就用到了索引覆盖。

002.png-5.1kB
002.png-4.3kB

前缀索引

利用字段数据的前部分作为索引，称为前缀索引。目标：减少索引长度，提高索引效率。
比如password字段（32）如果用该字段建立索引，则索引的长度为32*3=96,如果我们使用该密码字段前若干个字符作为索引字段，就能查找出该字段数据。
比如使用password来举例子，
在user表，添加10000行数据，

002.png-1.8kB

在 user表添加一个字段：

002.png-2.5kB
给user新建的字段添加内容：
002.png-2.6kB

最后确定密码字段前几位用于创建索引。

002.png-3.7kB

前缀索引的语法：

002.png-7.9kB

对于做前缀不易区分的列，建立索引的技巧
如 ：url列 http://www.baidu.com http://www.sohu.com
列的前11个字符都是一样的，不易区分，可以用如下2个办法来解决。
（1）把列的内容到过来存储，并建立索引，
（2）伪哈希索引效果，同时存储url_hash列
create table t8 (id int,url varchar(32),crcurl int unsigned)
可以对url字段使用crc32函数，存储建立索引，

select * from user where name=’’ and password=’sfsdf’;