时间&＃xff1a;2020年8月5日09:51:45
一、mysql优化概述
页面静态化的内容&＃xff0c;memcache&＃xff0c;减少数据库的访问&＃xff0c;提高网站的访问速度&＃xff0c;无论如何优化&＃xff0c;还是要操作数据库的&＃xff0c;要从数据库的角度来优化&＃xff0c;提高访问速度。
设计角度&＃xff1a;存储引擎的选择&＃xff0c;字段类型选择&＃xff0c;范式
利用mysql自身的特性&＃xff1a;索引&＃xff0c;查询缓存&＃xff0c;分区分表&＃xff0c;存储过程&＃xff0c;sql语句优化配置&＃xff0c;
部署大负载架构体系&＃xff1a;主从复制&＃xff0c;读写分离。
硬件升级&＃xff1a;
二、分析需要优化的语句
要 查找执行速度比较慢的sql语句&＃xff0c;
1、使用mysql里面的 慢查询日志&＃xff0c;
慢查询日志&＃xff0c;是由mysql提供的&＃xff0c;用于记录sql执行时间超过了某个时间界限&＃xff0c;该时间界限我们可以自己设定&＃xff0c;比如我们设定的时间界限为0.5秒&＃xff0c;开启慢查询日志后&＃xff0c;会自动记录执行时间超过0.5秒的sql语句。慢查询日志默认没有开启&＃xff0c;默认的时间界限是10秒。
&＃xff08;1&＃xff09;如何开启慢查询日志&＃xff0c;
方式一&＃xff1a;打开mysql的配置文件,my.ini,添加如下语句&＃xff1a;修改完成后&＃xff0c;要重启mysql服务
&＃xff08;2&＃xff09;开始测试&＃xff0c;是否记录执行时间超过0.5秒 的sql语句。
select benchmark(执行次数,表达式);
在慢查询日志文件里面查看是否记录
2、使用profiles机制
该机制开启后&＃xff0c;会记录每个sql语句的执行时间&＃xff0c;精确到小数点后8位。
如果开启&＃xff1a;set profiling&＃61;1|0 值为1则是开启&＃xff0c;为0则是关闭。
查看sql语句的执行时间:show profiles
三、索引
索引&＃xff1a;利用字段的某些属性&＃xff0c;快速 的定位数据&＃xff08;磁盘&＃xff0c;柱面&＃xff0c;磁道&＃xff0c;扇区&＃xff09;
1、索引 的类型
唯一索引&＃xff08;unique key&＃xff09;:字段数据是唯一的&＃xff0c;数据内容里面能否为null,在一张表里面&＃xff0c;是可以添加多个唯一索引。
主键索引(primary key )&＃xff1a;数据记录里面不能有null,数据内容不能重复&＃xff0c;在一张表里面不能有多个主键索引。
普通索引(index )&＃xff1a;使用字段关键字建立的索引&＃xff0c;主要是提高查询速度。
全文索引(fulltext index)&＃xff1a;在比较老的版本中&＃xff0c;只有myisam引擎支持全文索引&＃xff0c;在最新的版本中&＃xff08;可能是mysql.5.6&＃xff09;innodb引擎也支持全文索引&＃xff0c;在mysql中全文索引不支持中文。
2、如何创建索引
&＃xff08;1&＃xff09;在创建表的时候&＃xff0c;同时创建索引&＃xff0c;
create table stu(
id int primary key auto_increment,
name varchar(32) not null,
age tinyint unsigned not null,
email varchar(32) not null,
intro text,
unique key (name),
index (email),
fulltext index (intro)
)engine myisam charset utf8;
&＃xff08;2&＃xff09;在修改表的时候&＃xff0c;添加索引
create table stu1(
id int primary key auto_increment,
name varchar(32) not null,
age tinyint unsigned not null,
email varchar(32) not null,
intro text
)engine myisam charset utf8;
alter table stu1 add unique key (name), add index (email), add fulltext index (intro);
3、删除索引
主键索引的删除&＃xff0c;在删除主键 索引时&＃xff0c;要删除到auto_increment属性&＃xff0c;
alter table 表名 drop primary key
普通索引的删除&＃xff1a;alter table 表名 drop index 索引名称 &＃xff08;如果索引名称没有指定则是索引的字段名称&＃xff09;
删除唯一索引&＃xff1a;alter table 表名 drop index 索引名称
4、查询索引&＃xff1a;
show index form 表名
show indexes from 表名
desc 表名&＃xff0c;
show create table 表名
5、创建索引的注意事项
&＃xff08;1&＃xff09;较频繁的作为查询条件字段应该创建索引
select * from emp where empno &＃61; 1
唯一性太差的字段不适合单独创建索引&＃xff0c;即使频繁作为查询条件
select * from emp where sex &＃61; &＃39;男‘
更新非常频繁的字段不适合创建索引
select * from emp where logincount &＃61; 1
&＃xff08;2&＃xff09;不会出现在WHERE子句中字段不该创建索
四、索引结构
查看索引的类型&＃xff0c;是BTREE结构。
innodb索引的索引结构&＃xff0c;innodb的索引叫聚簇索引。
innodb的主索引文件上 直接存放该行数据,称为聚簇索引,非主索引指向对主键的引用
注意: innodb来说,
1: 主键索引 既存储索引值,又在叶子中存储行的数据
2: 如果没有主键, 则会Unique key做主键
3: 如果没有unique,则系统生成一个内部的rowid做主键.
4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”
在插入大量的数据的时候&＃xff0c;造成频繁的页分裂.
五、explain(执行计划)工具的使用
主要用于分析sql语句的执行情况&＃xff08;并不执行sql语句&＃xff09;得到sql语句是否使用了索引&＃xff0c;使用了哪些索引。
语法&＃xff1a;explain sql语句\G 或 desc sql语句\G
在mysql之前的版本中&＃xff0c;explain只支持select语句&＃xff0c;但是在最新的5.6版本中&＃xff0c;它支持 explain update/delete了。
建表完成测试&＃xff1a;
create table user(
id int primary key auto_increment,
name varchar(32) not null default &＃39;&＃39;,
age tinyint unsigned not null default 0,
email varchar(32) not null default &＃39;&＃39;,
classid int not null default 1
)engine myisam charset utf8;
insert into user values(null,&＃39;xiaogang&＃39;,12,&＃39;gang&＃64;sohu.com&＃39;,4),
(null,&＃39;xiaohong&＃39;,13,&＃39;hong&＃64;sohu.com&＃39;,2),
(null,&＃39;xiaolong&＃39;,31,&＃39;long&＃64;sohu.com&＃39;,2),
(null,&＃39;xiaofeng&＃39;,22,&＃39;feng&＃64;sohu.com&＃39;,3),
(null,&＃39;xiaogui&＃39;,42,&＃39;gui&＃64;sohu.com&＃39;,3);
创建一个班级表&＃xff1a;
create table class(
id int not null default 0,
classname varchar(32) not null default &＃39;&＃39;
)engine myisam charset utf8;
insert into class values(1,&＃39;java&＃39;),(2,&＃39;.net&＃39;),(3,&＃39;php&＃39;),(4,&＃39;c&＃43;&＃43;&＃39;),(5,&＃39;ios&＃39;);
六、使用执行计划&＃xff0c;完成sql语句索引执行情况查询&＃xff1b;
复合索引&＃xff0c;有多列组合成一个索引。比如如下&＃xff0c;建立一个name和age的一个复合索引。
mysql> alter table user add index (name,age);
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
1、多列索引&＃xff1a;
&＃xff08;1&＃xff09;对于创建的多列(复合)索引&＃xff0c;只要查询条件使用了最左边的列&＃xff0c;索引一般就会被使用。
因为组合索引是需要按顺序执行的&＃xff0c;比如c1234组合索引&＃xff0c;要想在c2上使用索引&＃xff0c;必须先在c1上使用索引&＃xff0c;要想在c3上使用索引&＃xff0c;必须先在c2上使用索引&＃xff0c;依此。
2、对于使用like的查询&＃xff0c;查询如果是”%aaa”,不会使用到索引&＃xff0c;‘aaa%’会使用到索引。
比如&＃xff1a;根据电影的剧情查找电影的名称&＃xff0c;根据歌词查找歌名。like ‘%爱请%’
可以使用第三方查询工具&＃xff0c;sphinx
3、如果条件中有or&＃xff0c;则要求or的索引字段都必须有索引&＃xff0c;否则不能用到索引。
4、如果列类型是字符串&＃xff0c;一定要在条件中将数据使用引号引用起来&＃xff0c;否则不使用索引。
5、优化group by语句。
默认情况下&＃xff0c; mysql对所有的group by col1,col2进行排序。这与在查询中指定order by col1,col2类型&＃xff0c;如果查询中包括group by 但用户想要避免排序结果的消耗&＃xff0c;则可以使用order by null禁止排序。
6、当取出的数据量超过表中数据的20%&＃xff0c;优化器就不会使用索引&＃xff0c;而是全表扫描。
七、索引覆盖
索引覆盖是指&＃xff1a;如果查询的列恰好是索引的一部分&＃xff0c;那么查询只需要在索引文件上进行&＃xff0c;不需要回行到磁盘再找数据&＃xff0c;这种查询速度非常快&＃xff0c;称为“索引覆盖”
案例1&＃xff0c;如下对name字段添加了普通索引&＃xff0c;要查询name字段信息
八、前缀索引&＃xff0c;
利用字段数据的前部分作为索引&＃xff0c;称为前缀索引。减少索引长度&＃xff0c;提高索引效率。
比如&＃xff1a;统计密码的前7个字符&＃xff0c;作为不相同匹配条件&＃xff0c;几乎可以做到1:1
此时&＃xff0c;就可以利用前7个字符做索引关键字即可&＃xff08;离散程度高&＃xff09;
九、翻页优化
翻页的sql语句&＃xff1a;
select * from table_name limit offset N
使用如上语句&＃xff0c;在翻页时&＃xff0c;翻到最后&＃xff0c;越来越慢&＃xff0c;
原因&＃xff1a;并不是跨过offset行&＃xff0c;取出n条&＃xff0c;
是取出offset&＃43;N条数据&＃xff0c;舍弃前面的offset行&＃xff0c;只取出n条数据。
如何解决&＃xff1f;
&＃xff08;1&＃xff09;从业务上去解决&＃xff1a;
办法&＃xff1a;不允许翻过100页&＃xff0c;
以百度为例&＃xff0c;一般翻页到70页左右&＃xff0c;谷歌40页左右
&＃xff08;2&＃xff09;不用offset&＃xff0c;用条件查询&＃xff0c;条件中使用id查询&＃xff0c;使用到了索引&＃xff0c;
select * from user limit 10000,10;
select * from user where id>10000 limit 10;
下一页&＃xff1a;select * from user where id>10000&＃43;10 limit 10
该种方式要注意&＃xff1a;如果有数据被删除&＃xff0c;会导致select * from user limit 10000,10;
和select * from user where id>10000 limit 10;语句取出的结果不一样&＃xff0c;。
&＃xff08;3&＃xff09;假如不能使用限制翻页到100页&＃xff0c;数据有删除&＃xff0c;还要求翻页&＃xff0c;速度不能受影响。
思路&＃xff1a;通过翻页&＃xff0c;先取出id&＃xff08;主键&＃xff09;&＃xff0c;在根据id取出数据。
select name,age,email from user inner join (select id from user limit 10000,10) as tmp on tmp.id&＃61;user.id
非要物理删除&＃xff0c;还要用offset精确查询&＃xff0c;还不限制用户分页&＃xff0c;怎么办
我们现在必须要查&＃xff0c;则只查索引&＃xff0c;不查数据&＃xff0c;得到id
再用id去查具体条目&＃xff0c;这种技巧就是延迟索引。
十、碎片整理
比如建表测试&＃xff1a;
当delete from ceshi where id&＃61;1&＃xff0c;应该容量减去三分之一&＃xff0c;但是并没有被删除。
需要把里面的一些碎片给释放掉。
使用optimize table 表名&＃xff1b;或alter table 表名 engine myisam(innodb)
执行optimize table 表名&＃xff0c;命令后&＃xff0c;把原来的碎片空间给释放掉
注意&＃xff1a;修复表的数据及索引碎片&＃xff0c;就会把所有的数据文件重新整理一遍&＃xff0c;使之对齐&＃xff0c;这个过程&＃xff0c;如果表的行数比较大&＃xff0c;也是比较耗费资源的操作&＃xff0c;所以&＃xff0c;不能频繁的修复。
如果表的update操作很频繁&＃xff0c;可以按周月来修复
十一、锁机制讲解
场景:
下订单&＃xff1a;
库存 为100&＃xff0c;买一件&＃xff1a;
&＃xff08;1&＃xff09;取出库存的数量 100
&＃xff08;2&＃xff09;库存减去1 99
&＃xff08;3&＃xff09;把剩余的库存再写入到表里面。 99
如果是两个人同时操作&＃xff1a;
刘备&＃xff1a;
100-1&＃61;99
99
曹操&＃xff1a;
100-1&＃61;99
99
锁机制&＃xff0c;
mysql 的锁有以下几种形式&＃xff1a;
表级锁&＃xff1a;开销小&＃xff0c;加锁快&＃xff0c;发生锁冲突的概率最高&＃xff0c;并发度最低。myisam引擎属于这种类型。
行级锁&＃xff1a;开销大&＃xff0c;加锁慢&＃xff0c;发生锁冲突的概率最低&＃xff0c;并发度也最高。innodb属于这种类型。
1、表锁的演示&＃xff1a;
对myisam表的读操作&＃xff08;加读锁&＃xff09;&＃xff0c;不会阻塞其他进程对同一表的读请求&＃xff0c;但会阻塞对同一表的写请求。只有当读锁释放后&＃xff0c;才会执行其他进程的操作。
对myisam表的写操作&＃xff08;加写锁&＃xff09;&＃xff0c;会阻塞其他进程对同一表的读和写操作&＃xff0c;只有当写锁释放后&＃xff0c;才会执行其他进程的读写操作。
read:所有人都只可以读&＃xff0c;只有释放锁之后才可以写。
write:只有锁表的客户可以操作这个表&＃xff0c;其他客户读都不能读。
语法&＃xff1a;
lock table 表名 read|write,
解锁&＃xff1a;
unlock table
读锁的演示&＃xff1a;
要注意&＃xff1a;对表添加锁定后&＃xff0c;只能操作锁定的表&＃xff0c;如果想要操作其他表&＃xff0c;则可以在锁定表时&＃xff0c;一次性锁定多张表。语法&＃xff1a;lock table 表1 read,表2 read;
2、行锁的演示&＃xff1a;
是innodb支持的一种锁&＃xff0c;在使用时&＃xff0c;要添加条件限制是要操作哪行数据。
语法&＃xff1a;
begin;
执行语句&＃xff1b;
commit;
文件锁&＃xff0c;flock_file
十二、分区分表技术
基本概念&＃xff0c;把一个表&＃xff0c;从逻辑上分成多个区域&＃xff0c;便于存储数据。
采用分区的前提&＃xff1a;数据量非常大。
1、分区类型&＃xff1a;
list :条件值为一个数据列表。
通过预定义的列表的值来对数据进行分割
例子&＃xff1a;假如你创建一个如下的一个表&＃xff0c;该表保存有全国20家分公司的职员记录&＃xff0c;这20家分公司的编号从1到20.而这20家分公司分布在全国5个区域&＃xff0c;如下表所示&＃xff1a;
职员表&＃xff1a;
id name store_id(分公司的id)
北部 1,4,5,6,17,18
南部 2,7,9,10,11,13
东部 3,12,19,20
西部 8,14,15,16
id name store_id(分公司的id)
1 李小龙 3
2 大刀王五 8
北部 1,4,5,6,17,18
南部 2,7,9,10,11,13
东部 3,12,19,20
西部 8,14,15,16
create table emp(
id int,
name varchar(32),
store_id int
)engine myisam charset utf8
partition by list (store_id)(
partition p_north values in (1,4,5,6,17,18),
partition p_east values in(2,7,9,10,11,13),
partition p_south values in(3,12,19,20),
partition p_west values in(8,14,15,16)
);
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
2020年8月5日15:51:51 原创发布。
京公网安备 11010802041100号