mysql优化
1、优化概述
设计角度:存储引擎的选择(myisam/innodb),字段类型的选择(数字型/日期型/字符型)、范式
自身特性：索引、查询缓存、分区分表、存储过程、sql语句优化配置
部署大负载架构体系:主从赋值、读写分离
硬件升级
2、分析需要优化的语句
查找出执行速度比较慢的sql语句,来具体分析sql语句
1、 慢查询
是mysql提供的日志，记录所有超过了某个时间界限的sql语句，在mysql中默认是没有开启的,就算开启了，也只能记录执行超过10s的语句
在my.ini 中进行配置
Log-slow-queries = ‘man-log’ // 也可以指定在d:/slow-log
Long_query_time = 0.5 // 指定事件界限
测试: select benchmark(10000000,10000*10000)
查看:
Show variables like ‘long_query_time’ // 查看设置的时间界限
Set ‘long_query_time’= 1 // 把时间界限设置为1
2、 精确查询
使用mysql提供的profile机制完成,记录每次sql语句的执行时间
Set profiling = 1|0 (开启或关闭) // 设置时间界限
Show profiles // 查看记录时间
注意:不需要分析时,最好将其关闭,set profiling = 0
对字段的查询,一般情况下一个没有添加索引的语句,执行的速度是比较慢的
3、索引
索引：利用数据的某个特征，快速定位该数据位置的技术
在myisam引擎里面,索引是单独的一个文件 . MYD 在添加索引后文件大小将会增加
1、 索引的类型
主键索引(primary key) àprimary key
唯一索引(unique) àunique key
普通索引(index) à key
全文索引(fulltext index) àFulltext key
联合索引[ index（name,age）] àkey
在添加索引时,如果没有指定索引的名称,则是把字段的名称当成索引的名称
查看索引:show create table table_name
增加索引：alter table table_name add index (索引名称);
删除索引:alter table table_name drop index (索引的名称)
索引结构:show keys from 表名
2、 myisam引擎的索引的数据结构
索引的节点中存储的数据是物理地址（磁道和扇区）
在查找数据时,查找到索引后,根据索引的节点的物理地址，再查找到具体的内容
通过在sql语句中的字段查找到索引的物理地址,并查找到具体的数据内容
3、 innodb的索引的数据结构
innodb的主键索引文件上直接存放该行数据,称为聚簇索引
innodb的非主键索引,非主键索引指向对主键的引用
innodb的特点:
1) 主键索引既存储索引值,又在叶子中存储行的数据
2) 如果没有主键,则会把unique key 做主键
3) 如果没有unique则系统生成一个内部的roword做主键
4) 像innodb中,主键的索引结构中,既存储了主键,又存储了行数据,这种结构称为‘聚簇索引’
5) 在innodb里面,数据是按照主键的顺序插入数据的,因此在大量的插入数据时，会影响效率,每插入一条数据,会重新排序
4、 创建索引的注意事项
a) 较频繁的作为查询条件应该创建索引
b) 唯一性太差的字段不适合单独创建索引（枚举性的数据）
c) 更新非常频繁的字段不适合创建索引
d) 不会出现在where子句中字段不该创建索引
e) where 后边加id 获取是group by / order by 后的字段加索引
4、执行计划
执行计划通常是使用explain/desc sql \G 来分析sql语句的执行情况,并不是真实的执行sql语句,只是查看sql语句的执行情况,执行sql语句都用到了哪些方法
1、Type:查询的方式
all 扫描了全文的所有的数据
index 比all要好一点,是指要扫描所有的索引节点,但是在索引覆盖(一般情况下是innodb引擎下,非索引字段中都存储着主键id索引,故在查询的时候会出现索引覆盖的情况)的查询的情况下,能利用上索引,但是还是必须进行全索引扫描; 利用索引来进行排序,但只能取出索引的列
range: 范围扫描在非主键索引当中使用大小于号 where c_id >10
ref : 通过非主键索引列直接引用到某些数据行 where c_id=?
const/system/null 通常在使用主键查找的情况下,很容易出现此类型
2、possible_key: 可能用到的索引
3、key 最终用到的索引
4、key_len使用索引的最大长度
5、rows 扫描的最大行数
6、extra：using index 用到了索引覆盖，效率非常高
Using where 用索引还无法进行定位，还需使用where进行判断
using temporary 临时表
5、索引覆盖
如果查询的列刚好是索引(主键索引和非主键索引)的一部分,那么查询只需要在索引文件上进行,而不需要在磁盘上查找数据，这样的查询方式非常的快
6、索引的使用细节
1、全值匹配
条件字段使用’=’，条件字段必须建立索引才能得到
Desc select * from stu where name=”xiaoming” // 当name字段为索引的时候才能用到索引
2、范围匹配
<= | >= | between…and… 当使用主键id时为primay key，非索引字段的时候为key
3、左值匹配
Select * from stu where name like ‘abc%’ // 此句可以使用索引匹配
Select * from stu where name like ‘%abc’ // 当%放在字符的前边的时候不会使用索引
Select * from stu where name=’abc%’; // 此处为非模糊匹配
拓展: select * from stu where name like name=’a%’
Select * from stu where name like name=’%a’
Select * from stu where name like name=’%a%’
Select * from stu like name=’a_’ // a后边接单个的字符
4、独立的列
指的是索引的列,不能是表达式,也不能是函数的参数
Desc select * from stu where id=1+1（1+1 = id）(不可以id+1=2必须独立的)
5、如果存在有or运算,规定or两边的字段都存在索引字段才能用到索引
Desc select * from stu where id=2 or name=’xiaofeng’ (id和name 均为索引才可以)
6、多列索引（联合索引）
当创建了联合索引，在查询的时候当使用了左边的索引,索引一般就会被使用
且联合索引都是需要按字段的顺序执行的;例C1C2C3想要在C2上使用索引,必须先使用C1索引,想要使用C3索引,必须先使用C2索引
desc select * from t4 where c1=1 and c2=2 and c4>2 and c3=3
7、当取出的数据量超过了20%，优化器将不会使用,而是全表扫描
7、表的分割
1、垂直分割：
1) 单一事物：将不经常查询的字段,单独使用一张表来进行存储（图书的分类和内容分开）
2) 多个事物（主表+附表）
将事物共同的字段(类名，图片)作为一张表，不同的事物再单独的使用一张表存储
2、水平分割：
如用户注册,使用取模算法,三张用户表,一张用户的注册数列表num,通过对num来进行取模运算,来决定用户分配到哪一张表中
8、翻页优化和延时缓存
Limit offset,N mysql在查询的时候,并不是将前面offset跳过,而是先将offset+N 条记录查询出来,再返回N条数据,故offset偏移量越大,翻页就会越缓慢
需求：限制总的翻页数方案
方案一：
先计算出总的页数,使用min(70,$pages)函数来进行最大限制
方案二：
不使用offset 来进行查询
Select * from stu limit 1000,10 当记录不连续的时候,id值将会错位
Select * from stu where id>1000 limit 10
故在数据库中不要轻易地删除数据,可以为其添加is_delete 字段来进行软删除
方案三：
当物理删除,且不能offset，还不能限制分页时,此时可以使用延时索引
延时索引：现将需要查询的记录数的id查询出来，然后根据内连接进行查询
Select * from stu a inner join (select * from stu limit 1000,10) as tmp on a.id = tmp.id
9、mysql锁机制
Myisam: 表级锁，开销小，锁冲突高,并发率小
读锁lock table table_name read 自己可读,别人可读，均不能写
写锁 lock table table_name write 自己可读可写，别人不可读不能写
解锁：unlock tables;
操作多张表的时候：lock table tab1,tab2,….read/write
Innodb: 行锁，开销大，锁冲突低，并发率大
Innodb是给索引加上索引项来加锁实现的,只用通过索引条件来进行检索数据才能开启行锁,否则将会开启表锁
Begin 开启锁机制
Commit 提交 // 事物处理
10、数据的碎片与维护
对于myisam表会产生3个文件名：.frm(表结构) .myd .myi
当我们再删除表中的一些字段后,会产生数据表结构并没有调整也没有将所占的文件大小改变,故一下两种方法来实现,碎片的整理
1、改变表的引擎来实现
Alter table table_name engine 原来的引擎
2、使用
optimize table 表名
11、数据表的分区
当数据量非常大的时候,通常采用数据的逻辑分区，便于存储数据
create table table_name(
字段信息，
索引，
)表选项[engine myisam,charset utf8]
partition by 分区算法（分区选项）
分区算法：list、range、hash、key
create table emp(
id int primary key auto_increment,
name varchar(32) not null,
store_id int
)engine myisam charset utf8
partition by list (store_id)( // store_id 分公司的id来进行关联
partition bei values in (1,4,5,6,17,18), //1,4,5....分公司编号
partition nan values in (2,7,9,10,11,13),
partition dong values in (3,12,19,20),
partition xi values in (8,14,15,16)
);
通过以下指令来进行查询
explain partitions select * from p_list where store_id=20 and id=3 \G
分区表的限制
只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列
对id,store_id 来进行分区(关联)
如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内
不支持外键
不支持全文索引（fulltext）
按日期进行分区很非常适合，因为很多日期函数可以用。但是对于字符串来说合适的分区函数不太多
12、主从复制,读写分离
主从复制的条件
至少使用两台mysql服务器，一台作为主服务器,一台作为从服务器，将主服务器上的写(增删改)操作，同步到从服务器上,主服务器只进行写操作，从服务器只进行查询操作
主从复制的原理
在mysql中存在有一个log-bin日志专门用来记录mysql下所有修改过数据库的sql语句,
主从复制的原理是多台服务器都开启bin日志(在my.ini中修改),然后主服务器会把执行过的sql语句记录到bin日志中,之后把这个bin日志发送给从服务器,从服务器将bin日志文件中的语句执行一次,就可以和主服务器上的数据一致了
主从复制的使用
1、在主从表中创建一个用户并为其分配权限
master主服务器：grant all on *.* to 'xiaoming'@'%' identified by '123456';
slave 从服务器： grant all on *.* to 'xiaomei'@'192.168.28.28' identified by '123456';
另: 删除用户 drop user ‘用户名’@’授权登录的主机ip地址’
2、在my.ini中开启log-bin文件[linux下是my.cnf]
log-bin = mysql-bin (或者自己指定log-bin = d:/nginx/log.txt),当在重启mysql的时候将会在data下产生一个log-bin:000001的文件,该文件就记录着执行的sql语句
log-bin日志的相关函数
reset master // 清空所有的log-bin日志,产生一个新的log-bin文件
flush logs // 产生一个新的log-bin日志文件
show master status // 查看一个最新的log-bin日志文件,并包含pos位置
查看log-bin日志
mysqlbinlog --no-defaults 日志文件的名称(全路径)
mysqlbinlog --no-defaults d:/wamp/mysql/data/mysql-bin.000003
使用log-bin日志完成数据的恢复
1、 创建一张表
2、 执行flush logs，产生一个新的日志文件
3、 使用insert语句完成数据的插入
4、 执行flush logs(产生一个新的日志)
5、 将刚才添加的数据删除
6、 使用log-bin 来进行日志的数据恢复
msyqlbinlog - -no-defaults 日志文件的名称(全路径) | mysql –uroot –proot 数据库的名称
恢复某一时间段内的数据[一般是在某个离的近的时间段,但是时间如果太长将不能很好的恢复] 可以使用 --start-post=’开始位置’ --stop-position = ‘结束的位置’
mysqlbinlog --no-defaults --start-position=’?’ --stop-postion=’?’ d:/wamp/mysql/mysql-bin.0001
主从复制的配置
1、 对主从服务器进行编号
主：server-id =1
从：server-id = 2
2、 在主服务器上面授权一个用户,从服务器就是通过该账号来读取log-bin中的日志信息的
master主服务器：grant replication slave on *.* to ‘xiaomei’@’%’ identified by ‘123456’
3、查看主服务器的最新的log-bin日志,此时禁止任何人对主武器的任何修改
show master
3、 从服务器的配置
关闭从服务器stop slave;
开始配置:
change master to master_host =’主服务器的ip地址’,master_user = ‘主服务器上的username’,master_password=’密码’,master_log_file=’bin日志文件名’，master_log_pos = bin日志中的position值
配置完成：start slave
查看服务器的状态：show slave status \G
Slave_IO_Running:yes 此进程负责从服务器从主服务器上读取日志,并写入从服务器上
Slave_SQL_Running:yes 此进程负责读取并且执行中继日志中的binlog日志
测试完成：在主服务器上面创建一个新的数据库,并创建一张新表,并添加记录,查看从服务器是否同步来完成测试
配置完成后，要禁止对服务器执行增删改的操作
撤销从服务器
停止从服务器：stop slave
删除从服务器的配置：reset slave all;
读写分离
class mysql{
$dbm=主服务器
$dbs1=从服务器
$dbs2=从服务器
public function query(){
在query里面进行语句判断，分析连接不同的mysql服务器。
如果是增删改的操作，就连接主服务器，如果是查询操作，则随机连接从服务器。
}
}
TP支持读写分离
修改TP框架项目的配置文件：
'DB_DEPLOY_TYPE'=>1,//分布式数据库支持
'DB_TYPE' => 'mysql', // 数据库类型
'DB_HOST' => 'localhost,192.168.3.250', // 服务器地址，多个用逗号隔开，默认第一个是主服务器，其余是从服务器。
'DB_NAME' => 'php,php', // 数据库名
'DB_USER' => 'root,xiaogang', // 用户名
'DB_PWD' => 'root,1234', // 密码
'DB_PORT' => '3306', // 端口
'DB_PREFIX' => '',
'DB_RW_SEPARATE'=>true,//支持读写分离
'DB_MASTER_NUM' => 1, // 读写分离后 主服务器数量
'DB_SLAVE_NO' => '', // 指定从服务器序号，如果为空，则随机连接从服务器。
浙公网安备 33010602011771号