您所在的位置:首页 / 知识分享

php mysql优化

2023.05.11

447

ytj

php mysql优化

1、SQL语句中的关键词最好用大写来书写,第一易于区分关键词和操作对象,第二,SQL语句在执行时,MySQL会将其转换为大写,手动写大写能增加查询效率(虽然很小)。

  2、如果我们们经对数据库中的数据行进行增删,那么会出现数据ID过大的情况,用ALTER TABLE tablename AUTO_INCREMENT=N,使自增ID从N开始计数。

  3、对int类型添加 ZEROFILL 属性可以对数据进行自动补0

  4、导入大量数据时最好先删除索引再插入数据,再加入索引,不然,mysql会花费大量时间在更新索引上。

  5、创建数据库书写sql语句时 ,我们可以在IDE里创建一个后缀为.sql的文件,IDE会识别sql语法,更易于书写。更重要的是,如果你的数据库丢失了,你还可以找到这个文件,在当前目录下使用/path/mysql -uusername -ppassword databasename < filename.sql来执行整个文件的sql语句(注意-u和-p后紧跟用户名密码,无空格)。

  数据库设计方面优化

  1、数据库设计符合第三范式,为了查询方便可以有一定的数据冗余。

  2、选择数据类型优先级 int > date,time > enum,cha

1、SQL语句中的关键词最好用大写来书写,第一易于区分关键词和操作对象,第二,SQL语句在执行时,MySQL会将其转换为大写,手动写大写能增加查询效率(虽然很小)。

  2、如果我们们经对数据库中的数据行进行增删,那么会出现数据ID过大的情况,用ALTER TABLE tablename AUTO_INCREMENT=N,使自增ID从N开始计数。

  3、对int类型添加 ZEROFILL 属性可以对数据进行自动补0

  4、导入大量数据时最好先删除索引再插入数据,再加入索引,不然,mysql会花费大量时间在更新索引上。

  5、创建数据库书写sql语句时 ,我们可以在IDE里创建一个后缀为.sql的文件,IDE会识别sql语法,更易于书写。更重要的是,如果你的数据库丢失了,你还可以找到这个文件,在当前目录下使用/path/mysql -uusername -ppassword databasename < filename.sql来执行整个文件的sql语句(注意-u和-p后紧跟用户名密码,无空格)。

  数据库设计方面优化

  1、数据库设计符合第三范式,为了查询方便可以有一定的数据冗余。

  2、选择数据类型优先级 int > date,time > enum,char>varchar > blob,选择数据类型时,可以考虑替换,如ip地址可以用ip2long()函数转换为unsign int型来进行存储。

  3、对于char(n)类型,在数据完整的情况下尽量较小的的n值。

  4、在建表时用partition命令对单个表分区可以大大提升查询效率,MySQL支持RANGE,LIST,HASH,KEY分区类型,其中以RANGE最为常用,分区方式为:

  CREATE TABLE tablename{

  }ENGINE innodb/myisam CHARSET utf8 //选择数据库引擎和编码

  PARTITION BY RANGE/LIST(column),//按范围和预定义列表进行分区

  PARTITION partname VALUES LESS THAN /IN(n),//命名分区并详细限定分区的范围

5、选择数据库引擎时要注意innodb 和 myisam的区别。

  存储结构:MyISAM在磁盘上存储成三个文件。而InnoDB所有的表都保存在同一个数据文件中,一般为2GB

  事务支持:MyISAM不提供事务支持。InnoDB提供事务支持事务。

  表锁差异:MyISAM只支持表级锁。InnoDB支持事务和行级锁。

  全文索引:MyISAM支持 FULLTEXT类型的全文索引(不适用中文,所以要用sphinx全文索引引擎)。InnoDB不支持。

  表的具体行数:MyISAM保存有表的总行数,查询count(*)很快。InnoDB没有保存表的总行数,需要重新计算。

  外键:MyISAM不支持。InnoDB支持

  索引方面优化

  1、innodb是聚簇索引,存储索引时必须有主键,如果没有指定,引擎会自动生成一个隐藏的主键,生成一个主索引,索引内存放的是主键的物理地址,数据靠主键存放,每次使用索引时要先找到主索引,然后找到主索引下的数据。

  优点通过主键查找特别快,缺点是次级索引会变慢,因为需要先通过次级索引(次级索引里是主索引的位置。)找到主索引,然后通过主索引找数据。并且如果主键无规律,插入新值时需要移动较多数据块,会影响效率,所以要尽量使用有规律递增的int型做主键。还有因为数据紧跟着主键放,所以如果数据中有数据量特别大的列(text/blob),innodb查询时会跳过很多数据块,也会导致慢。

  2、myisam的索引各个索引都相同统一指向磁盘上各个行的地址,都是轻量级的指针数据。缺点是各个索引的建立不是通过主键,查询没有聚簇索引查找主键快。但其因为存储的是地址,所以在插入新值时比较方面移动改变。

  3、进行多条件查询时,对多条件分别建立索引时,执行sql查询时,MySQL只会选择一个最贴近的索引来使用,所以如果需要多条件查询,要建立联合索引,即使会造成数据冗余。

  联合索引的BTREE建立方法:对第一个条件建立索引,在第一个索引的BTREE区域对第二个条件建立索引,以此类推,所以,在使用索引时,不用第一个条件用第二个条件也不会用到联合索引。使用索引时要条件要有顺序,有序列的使用

       4、索引长度对查询也有很大影响,我们应该尽量建立短的索引长度,我们可以使用查询列

  SELECT COUNT(DISTINCT LEFT(column)) / COUNT(*) FROM tablename

  来测试对column列建立索引时选取不同的长度,索引的覆盖率有多大,我们选择一下接近饱和的n个长度来建立索引

  ALTER TABLE tablename ADD INDEX (column(n)); 来对某一列的前n个字符建立索引。若前n个字符相同,我们甚至可以对字符串进行反转存储,然后建立索引。

  5、对于经常修改导致的索引碎片的维护方式:ALTER TABLE tablename ENGINE oldengine;即再次应用一下表存储引擎,使其自动维护;也可以用 OPTIMIZE tablename 命令来进行维护。

  数据查询方面优化

  数据库操作尽量少查询,有查询时尽量不在数据库层面上进行数据操作,而是返回到PHP脚本中操作数据,减轻数据库压力。

  一旦发现有数据库性能问题,要及时解决,一般用慢查询日志记录查询很"慢"的语句,用EXPLAIN分析查询和索引使用情况,用PROFILE分析语句执行时的具体资源消耗。

慢查询日志:

  1、在my.ini或myf的[mysqld]下添加

  slow_query_log_file=/path //设置日志存储路径

  long_query_time=n //设置如果语句执行时间达到n秒,就会被记录下来

  2、然后在MySQL里设置SET slow_query_log='ON'来开启慢查询。

  3、记录下日志后,我们用/bin/目录下的mysqldumpslow filename来查看日志,其常用参数如下:

  -g pattern 使用正则表达式

  -t n返回前n条数据

  -s c/t/l/r 以记录次数/时间/查询时间/返回记录数来排序

  EXPLAIN语句

  使用方法,在要执行的查询语句前面加EXPLAIN

  EXPLAIN SELECT * FROM user;

下面是对每一项的解释:

  id 查询语句的id,简单查询无意义,多重查询时可以看出执行查询的顺序

  select-type 执行的查询语句的类型,对应多重查询,有simple/primary/union等。

  tabel 查询语句查询的数据表

  type 获得数据的类型 常见的类型效率从高到低为 null>const>eq_ref>ref>range>index>all

  possible-keys:可能使用到的索引

  key 使用到的索引

  key_len索引长度

  ref 使用哪个列与索引一起从表中选择。

  rows 查找到数据要扫描的大概行数,可看出索引的优劣

  extra 常见的有

  using filesort 查询到数据后进行文件排序,较慢,需要优化索引

  using where 读取整行数据后进行判断过滤,是否符合where条件

  using index 索引覆盖,即在牵引中已经有这存储了目标数据,直接读取索引,很快。

  PROFILE

  用SELECT @@frofiling来查看PROFILE的开启状态。

  如果未开启,用SET profiling=1来开启。

  开启之后,再执行查询语句,MySQL会自动记录profile信息。

  应用show profiles查看所有的sql信息,结果为 Query_ID Duration Query三列结果,分别是查询ID,用时和所用的sql语句。

  我们可以使用

  SHOW PFROFILE [type[,type]][FOR QUREY Query_ID][Limit rwo_count [OFFSET offset]]

  type常见有ALL(全部) BLOCK IO(显示IO相关开销) CPU(CPU开销) MEMORY(内存开销)等

大型存储方面优化

  数据库主从复制和读写分离

  1、master将改变记录到二进制日志中,slave将master的二进制拷贝到它的中继日志中,重新将数据返回到它自己的数据中,达到复制主服务器数据的目的。

  主从复制可以用作:数据库负载均衡、数据库备份、读写分离等功能。

  2、配置主服务器master

  修改my.ini/my.conf

  [mysqld]

  log-bin=mysql-bin //启用二进制日志

  server-id=102 //服务器唯一ID

  3、配置从服务器slave

  log-bin=mysql-bin //启用二进制日志

  server-id=226 //服务器唯一ID

  4、在主服务器上授权从服务器

  GRANT REPLICATION SLAVE ON *.* to 'slavename'@'IP' identified by 'root'

5、在从服务器上使用

  change master to

  master_host="masterip",

  master_user="masteruser",

  master_password="masterpasswd";

  6、然后使用start slave命令开始进行主从复制。

  不要忘记在每次修改配置后重启服务器,然后可以在主从服务器上用show master/slave status查看主/从状态。

  实现数据库的读写分离要依赖MySQL的中间件,如mysql_proxy,atlas等。通过配置这些中间件来对主从服务器进行读写分离,使从服务器承担被读取的责任,从而减轻主服务器的负担。

  数据库的sharding

  在数据库中数据表中的数据量非常庞大的时候,无论是索引还是缓存等压力都很大,对数据库进行sharding,使之分别以多个数据库服务器或多个表存储,以减轻查询压力。

  方式有垂直切分、水平切分和联合切分。

  垂直切分:在数据表非常多的时候,把数据库中关系紧密(如同一模块,经常连接查询)的表切分出来分别放到不同的主从server上。

  水平切分:在表不多,而表里的数据量非常大的时候,为了加快查询,可以用哈希等算法,将一个数据表分为几个,分别放到不同的服务器上,加快查询。水平切分和数据表分区的区别在于其存储介质上的不同。

  联合切分:更多的情况是数据表和表中的数据量都非常大,则要进行联合切分,即同时进行垂直和水平分表,将数据库切分为一个分布式的矩阵来存储。

  这些数据库的优化方式,每一种拿出来都可以写作一篇文章,可谓是博大精深,了解并记忆了这些方式,可以在有需要的时候进行有目的的选择优化,达到数据库效率的高效。


r>varchar > blob,选择数据类型时,可以考虑替换,如ip地址可以用ip2long()函数转换为unsign int型来进行存储。

  3、对于char(n)类型,在数据完整的情况下尽量较小的的n值。

  4、在建表时用partition命令对单个表分区可以大大提升查询效率,MySQL支持RANGE,LIST,HASH,KEY分区类型,其中以RANGE最为常用,分区方式为:

  CREATE TABLE tablename{

  }ENGINE innodb/myisam CHARSET utf8 //选择数据库引擎和编码

  PARTITION BY RANGE/LIST(column),//按范围和预定义列表进行分区

  PARTITION partname VALUES LESS THAN /IN(n),//命名分区并详细限定分区的范围

5、选择数据库引擎时要注意innodb 和 myisam的区别。

  存储结构:MyISAM在磁盘上存储成三个文件。而InnoDB所有的表都保存在同一个数据文件中,一般为2GB

  事务支持:MyISAM不提供事务支持。InnoDB提供事务支持事务。

  表锁差异:MyISAM只支持表级锁。InnoDB支持事务和行级锁。

  全文索引:MyISAM支持 FULLTEXT类型的全文索引(不适用中文,所以要用sphinx全文索引引擎)。InnoDB不支持。

  表的具体行数:MyISAM保存有表的总行数,查询count(*)很快。InnoDB没有保存表的总行数,需要重新计算。

  外键:MyISAM不支持。InnoDB支持

  索引方面优化

  1、innodb是聚簇索引,存储索引时必须有主键,如果没有指定,引擎会自动生成一个隐藏的主键,生成一个主索引,索引内存放的是主键的物理地址,数据靠主键存放,每次使用索引时要先找到主索引,然后找到主索引下的数据。

  优点通过主键查找特别快,缺点是次级索引会变慢,因为需要先通过次级索引(次级索引里是主索引的位置。)找到主索引,然后通过主索引找数据。并且如果主键无规律,插入新值时需要移动较多数据块,会影响效率,所以要尽量使用有规律递增的int型做主键。还有因为数据紧跟着主键放,所以如果数据中有数据量特别大的列(text/blob),innodb查询时会跳过很多数据块,也会导致慢。

  2、myisam的索引各个索引都相同统一指向磁盘上各个行的地址,都是轻量级的指针数据。缺点是各个索引的建立不是通过主键,查询没有聚簇索引查找主键快。但其因为存储的是地址,所以在插入新值时比较方面移动改变。

  3、进行多条件查询时,对多条件分别建立索引时,执行sql查询时,MySQL只会选择一个最贴近的索引来使用,所以如果需要多条件查询,要建立联合索引,即使会造成数据冗余。

  联合索引的BTREE建立方法:对第一个条件建立索引,在第一个索引的BTREE区域对第二个条件建立索引,以此类推,所以,在使用索引时,不用第一个条件用第二个条件也不会用到联合索引。使用索引时要条件要有顺序,有序列的使用

       4、索引长度对查询也有很大影响,我们应该尽量建立短的索引长度,我们可以使用查询列

  SELECT COUNT(DISTINCT LEFT(column)) / COUNT(*) FROM tablename

  来测试对column列建立索引时选取不同的长度,索引的覆盖率有多大,我们选择一下接近饱和的n个长度来建立索引

  ALTER TABLE tablename ADD INDEX (column(n)); 来对某一列的前n个字符建立索引。若前n个字符相同,我们甚至可以对字符串进行反转存储,然后建立索引。

  5、对于经常修改导致的索引碎片的维护方式:ALTER TABLE tablename ENGINE oldengine;即再次应用一下表存储引擎,使其自动维护;也可以用 OPTIMIZE tablename 命令来进行维护。

  数据查询方面优化

  数据库操作尽量少查询,有查询时尽量不在数据库层面上进行数据操作,而是返回到PHP脚本中操作数据,减轻数据库压力。

  一旦发现有数据库性能问题,要及时解决,一般用慢查询日志记录查询很"慢"的语句,用EXPLAIN分析查询和索引使用情况,用PROFILE分析语句执行时的具体资源消耗。

慢查询日志:

  1、在my.ini或myf的[mysqld]下添加

  slow_query_log_file=/path //设置日志存储路径

  long_query_time=n //设置如果语句执行时间达到n秒,就会被记录下来

  2、然后在MySQL里设置SET slow_query_log='ON'来开启慢查询。

  3、记录下日志后,我们用/bin/目录下的mysqldumpslow filename来查看日志,其常用参数如下:

  -g pattern 使用正则表达式

  -t n返回前n条数据

  -s c/t/l/r 以记录次数/时间/查询时间/返回记录数来排序

  EXPLAIN语句

  使用方法,在要执行的查询语句前面加EXPLAIN

  EXPLAIN SELECT * FROM user;

下面是对每一项的解释:

  id 查询语句的id,简单查询无意义,多重查询时可以看出执行查询的顺序

  select-type 执行的查询语句的类型,对应多重查询,有simple/primary/union等。

  tabel 查询语句查询的数据表

  type 获得数据的类型 常见的类型效率从高到低为 null>const>eq_ref>ref>range>index>all

  possible-keys:可能使用到的索引

  key 使用到的索引

  key_len索引长度

  ref 使用哪个列与索引一起从表中选择。

  rows 查找到数据要扫描的大概行数,可看出索引的优劣

  extra 常见的有

  using filesort 查询到数据后进行文件排序,较慢,需要优化索引

  using where 读取整行数据后进行判断过滤,是否符合where条件

  using index 索引覆盖,即在牵引中已经有这存储了目标数据,直接读取索引,很快。

  PROFILE

  用SELECT @@frofiling来查看PROFILE的开启状态。

  如果未开启,用SET profiling=1来开启。

  开启之后,再执行查询语句,MySQL会自动记录profile信息。

  应用show profiles查看所有的sql信息,结果为 Query_ID Duration Query三列结果,分别是查询ID,用时和所用的sql语句。

  我们可以使用

  SHOW PFROFILE [type[,type]][FOR QUREY Query_ID][Limit rwo_count [OFFSET offset]]

  type常见有ALL(全部) BLOCK IO(显示IO相关开销) CPU(CPU开销) MEMORY(内存开销)等

大型存储方面优化

  数据库主从复制和读写分离

  1、master将改变记录到二进制日志中,slave将master的二进制拷贝到它的中继日志中,重新将数据返回到它自己的数据中,达到复制主服务器数据的目的。

  主从复制可以用作:数据库负载均衡、数据库备份、读写分离等功能。

  2、配置主服务器master

  修改my.ini/my.conf

  [mysqld]

  log-bin=mysql-bin //启用二进制日志

  server-id=102 //服务器唯一ID

  3、配置从服务器slave

  log-bin=mysql-bin //启用二进制日志

  server-id=226 //服务器唯一ID

  4、在主服务器上授权从服务器

  GRANT REPLICATION SLAVE ON *.* to 'slavename'@'IP' identified by 'root'

5、在从服务器上使用

  change master to

  master_host="masterip",

  master_user="masteruser",

  master_password="masterpasswd";

  6、然后使用start slave命令开始进行主从复制。

  不要忘记在每次修改配置后重启服务器,然后可以在主从服务器上用show master/slave status查看主/从状态。

  实现数据库的读写分离要依赖MySQL的中间件,如mysql_proxy,atlas等。通过配置这些中间件来对主从服务器进行读写分离,使从服务器承担被读取的责任,从而减轻主服务器的负担。

  数据库的sharding

  在数据库中数据表中的数据量非常庞大的时候,无论是索引还是缓存等压力都很大,对数据库进行sharding,使之分别以多个数据库服务器或多个表存储,以减轻查询压力。

  方式有垂直切分、水平切分和联合切分。

  垂直切分:在数据表非常多的时候,把数据库中关系紧密(如同一模块,经常连接查询)的表切分出来分别放到不同的主从server上。

  水平切分:在表不多,而表里的数据量非常大的时候,为了加快查询,可以用哈希等算法,将一个数据表分为几个,分别放到不同的服务器上,加快查询。水平切分和数据表分区的区别在于其存储介质上的不同。

  联合切分:更多的情况是数据表和表中的数据量都非常大,则要进行联合切分,即同时进行垂直和水平分表,将数据库切分为一个分布式的矩阵来存储。

  这些数据库的优化方式,每一种拿出来都可以写作一篇文章,可谓是博大精深,了解并记忆了这些方式,可以在有需要的时候进行有目的的选择优化,达到数据库效率的高效。



相关新闻

产品设计是什么?

2019.02.20

5726

产品设计究竟是什么?产品设计师是干什么的?什么样的算是优秀的产品设计师?今天的这篇文章将会回答这些问题。

PHP如何读取一个大文件txt

2018.12.06

2942

文件已经一次性全部被载入到内存中并将文件的每一行保存到了一个php数组中,一次性载入这个202MB的文件file函数用了0.67秒钟、file_get_contents函数用了0.25秒钟(看起来file_get_content要比file靠谱的多

【开源】Westore 1.0 正式发布 - 世界上最小却强大的小程序框架

2018.09.27

2229

众所周知,小程序通过页面或组件各自的 setData 再加上各种父子、祖孙、姐弟、嫂子与堂兄等等组件间的通讯会把程序搞成一团浆糊,如果再加上跨页面之间的组件通讯,会让程序非常难维护和调试。