mysql优化概述

一：mysql优化概述：
设计角度：存储引擎的选择，字段类型选择，范式。
利用mysql自身的特性：索引，查询缓存，分区分表，存储过程，sql语句的优化
部署大负载架构体系：主从复制（读写分离）

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

Paste_Image.png

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

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

Paste_Image.png

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

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');

Paste_Image.png

当有大量数据插入时，会变慢，会影响插入效率，因为是按照主键顺序插入，要有一个排序的过程。
并发性：
实现了行锁，擅长并发处理，不会影响其他行的操作。
数据完整性
支持事务ACID属性（原子性一致性，隔离性，持久性）

2、myisam存储引擎
ISAM:索引序列管理方法
是indexed sequential access method(索引顺序存取方法)的缩写优势，在索引的处理上索引独立存储。
数据存储方式：
表结构、表数据、表索引是分别来存储的。创建一个myisam引擎的表后，会形成三个文件。

Paste_Image.png

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

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');

Paste_Image.png

并发性：
实现的表锁，不擅长并发处理，锁定整张表后，会影响其他的进程操作该表。
支持全文索引
在最新的mysql5.6以后，innodb引擎也支持全文索引了。

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

查看存储引擎：show engines;

Paste_Image.png

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

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

Paste_Image.png

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

Paste_Image.png

在data目录里面会看到生成的慢查询日志文件。

Paste_Image.png

使用命令查看慢查询日志的时间界限：

show variables like ‘long_query_time’

Paste_Image.png

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

set long_query_time = 1;

Paste_Image.png

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

Paste_Image.png

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

Paste_Image.png

2、使用mysql的profiles机制，该机制精确的记录执行sql语句的时间，精确到小数点后8位。

开启profile机制   
执行  set profiling = 1

Paste_Image.png

使用show profiles查看sql语句的执行时间；

Paste_Image.png

关闭profiles机制
set profiling=0,如果不需要查找执行的慢的sql语句，要关闭该机制。

Paste_Image.png

一般情况下，一个sql语句执行速度比较慢原因是没有添加索引。

Paste_Image.png

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

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

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;

Paste_Image.png

（2）在创建表完成后，再修改表结构创建索引。

Paste_Image.png Paste_Image.png

3、查看索引

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

Paste_Image.png

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

Paste_Image.png

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

Paste_Image.png

5、创建索引的注意事项

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

五、索引的数据结构

Paste_Image.png

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

Paste_Image.png

排好序的快速查找结构
2、innodb引擎的索引结构
innodb的主键索引文件上 直接存放该行数据,称为聚簇索引,非主索引指向对主键的引用（非主键索引的节点存储是主键的id）

Paste_Image.png

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

六、explain(执行计划)工具使用
主要分析索引的使用情况，分析 当前查询是否用到了索引，索引效率如何。
语法：explain sql语句\G或desc sql语句\G

Paste_Image.png Paste_Image.png

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

Paste_Image.png

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

Paste_Image.png

index:比all性能稍好一点，是指要扫描所有的节点，即在索引文件中进行查找，无需根据物理地址查找具体的数据。
（1）索引覆盖的查询情况下，能利用上索引，但是又必须全索引扫描。

Paste_Image.png

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

Paste_Image.png

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

Paste_Image.png

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

商品表、    栏目表
cat_id       id
select * from goods where cat_id=2;

Paste_Image.png

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

Paste_Image.png Paste_Image.png

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

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

Paste_Image.png Paste_Image.png

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

假设某个表有一个联合索引（c1,c2,c3,c4）

alter table table_name add index (c1,c2,c3,c4)
A desc select * from 表名 where c1=x and c2=x and c4>x and c3=x 

Paste_Image.png

B where c1=x and c2=x and c4=x order by c3

Paste_Image.png

C where c1=x and c4= x group by c3,c2

Paste_Image.png

D where c1=x and c5=x order by c2,c3

Paste_Image.png

E where c1=x and c2=x and c5=x order by c2,c3

Paste_Image.png

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

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

Paste_Image.png

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

3、如果条件中有or，则要求or的索引字段都必须有索引，否则不能用到索引。
比如id建立了主键索引，name建立的普通索引，进行测试查询。

Paste_Image.png

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

Paste_Image.png

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

Paste_Image.png

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

Paste_Image.png

在默认情况下面使用group by 会根据group by的字段进行排序。

Paste_Image.png

添加完成order by null,就没有对calss_id排序，按原来插入的顺序来显示。

Paste_Image.png

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

Paste_Image.png

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

Paste_Image.png

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

Paste_Image.png Paste_Image.png

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

比如使用password来举例子，
在user表，添加10000行数据，

Paste_Image.png

在 user表添加一个字段：

Paste_Image.png

给user新建的字段添加内容：

Paste_Image.png

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

Paste_Image.png

前缀索引的语法：
alter table user add index(password(7))

Paste_Image.png

对于做前缀不易区分的列，建立索引的技巧
如 ：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’;