PHP数据优化技术使用Sphinx全文检索

时间&＃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  原创发布。