网站的开发费用吗,网上商城毕业设计论文,wordpress安装脚本,express wordpress优化MySQL的几点#xff1a;
从设计上优化
从查询上优化
从索引上优化
从存储上优化
1#xff0c;SQL的执行频率
MySQL客户端连接成功后#xff0c;通过show [session/global] status命令可以查看服务器状态信息。通过查看状态信息可以查看对当前数据库的主要操作类型…优化MySQL的几点
从设计上优化
从查询上优化
从索引上优化
从存储上优化
1SQL的执行频率
MySQL客户端连接成功后通过show [session/global] status命令可以查看服务器状态信息。通过查看状态信息可以查看对当前数据库的主要操作类型
参数含义Com_select执行select操作的次数一次查询只累加1Com_insert执行INSERT 操作的次数,对于批量插入的 INSERT操作,只累加一次Com_update执行UPDATE 操作的次数Com_delete执行DELETE 操作的次数Innodb_rows_readselect查询返回的行数Innodb_rows_inserted执行INSERT操作插入的行数Innodb_rows_updated执行UPDATE 操作更新的行数Innodb_rows_deleted执行DELETE 操作删除的行数Connections试图连接 MySQL服务器的次数Uptime服务器工作时间Slow_queries慢查询的次数
-- 查看当前会话SQL执行类型的统计信息
show session status like Com_______;-- 7个_-- 查看全局会话SQL执行类型的统计信息(自从上次MySQL服务器启动至今)
show global status like Com_______;-- 查看针对InnoDB引擎的统计信息
show status like Innodb_rows_%;2定位低效率执行SQL
方式
慢日志查询:通过慢查询日志定位那些执行效率较低的SQL语句
show processlist:该命令查看当前MySQL在进行的线程包括线程的状态、是否锁表等可以实时地查看sQL的执行情况同时对一些锁表操作进行优化
1慢日志查询
-- 查询慢查询日志配置信息
show variables like slow_query_log%;-- 开启慢查询日志
set global slow_query_log1;-- 查看慢查询日志的最低时间 查询大于等于10秒的会记录
show variables like long_query_time%;-- 修改最低时间
set global long_query_time5;
2show processlist
show processlist; 1id列用户登录mysql时系统分配的connection_id可以使用函数connection_id()查看
2user列显示当前用户。如果不是root这个命令就只显示用户权限范围的sql语句
3host列显示这个语句是从哪个ip的哪个端口上发的可以用来跟踪出现问题语句的用户
4db列显示这个进程目前连接的是哪个数据库
5command列显示当前连接的执行的命令一般取值为休眠(sleep查询(query)连接(connect等
6time列显示这个状态持续的时间单位是秒
7state列显示使用当前连接的sq语句的状态很重要的列。state描述的是语句执行中的某一个状态。一个sqi语句以查询为例可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
8info列显示这个sql语句是判断问题语句的一个重要依据
3explain分析执行计划
字段含义idselect查询的序列号是一组数字表示的是查询中执行select子句或者是操作表的顺序select_type表示SELECT的类型常见的取值有SIMPLE(简单表即不使用表连接或者子查询)、PRIMARY(主查询即外层的查询)、UNION (UNION中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个SELECT)等table输出结果集的表type表示表的连接类型性能由好到差的连接类型为( system . const .……eq_ref ….ref ..…ref_or_null-- index_merge --- index_subquery-- range ---…- index --- all)possible_keys表示查询时。可能使用的索引key表示实际使用的索引key_len索引字段的长度rows扫描行的数量extra执行情况的说明和描述
-- 查询执行计划
explain select * from user where uid1;-- 查询执行计划
explain select * from user where uname张飞;
1id
-- 1、id相同表示加载表的顺序是从上到下
explain select * from user u,user_role ur,role r where u.uidur.uid and ur.ridr.rid;-- 2、不同id越大优先级越高越先被执行
explain select * from role where rid (select rid from user_role where uid(select uid from user where uname张飞));-- 3、id相同也有不同同时存在id相同可认为是一组从上往下顺序在所有组中id值越大优先级越高越先执行
explain select * from role r,(select * from user_role ur where ur.uid (select uid from user where uname 张飞)) t where r.ridt.rid;
2select_type
select_type含义SIMPLE简单的select查询查询中不包含子查询或者UNIONPRIMARY查询中若包含任何复杂的子查询,最外层查询标记为该标识SUBQUERY在SELECT或WHERE列表中包含了子查询DERIVED在FROM列表中包含的子查询被标记为DERIVED(衍生)MYSQL会递旧执行这些子查询把结果放在临时表中UNION若第二个SELECT出现在UNION之后则标记为UNION;若UNION包含在FROM子句的子查询中外层SELECT将被标记为:DERIVEDUNION RESULT从UNION表获取结果的SELECT
explain select * from user;-- SIMPLE
explain select * from user u,user_role ur where u.uidur.uid;--SIMPLEexplain select * from role where rid (select rid from user_role where uid(select uid from user where uname张飞));-- PRIMARYexplain select * from role where rid (select rid from user_role where uid(select uid from user where uname张飞));-- SUBQUERYexplain select *from (select * from user limit 2) t;-- DERIVEDexplain select * from user where uid1 union select * from user where uid3;-- UNION/UNION RESULT
3type
type含义NULLMySQL不访问任何表索引直接返回结果system系统表少量数据往往不需要进行磁盘IO;如果是5.7及以上版本的话就不是system了而是all即使只有一条记录const命中主键(primary key)或者唯一(unique)索引;被连接的部分是一个常量(const)值;eq_ref 对于前表的每一行后表只有一行被扫描。(1) join查询;(2)命中主键(primary key)或者非空唯一(unique not null)索引;(3)等值连接; 左表有主键,而且左表的每一行和右表的每一行刚好匹配 ref 非唯一性索引扫描返回匹配某个单独值的所有行。对于前表的每一行(row)后表可能有多于一行的数据被扫描 左表是普通索引和右表匹配时可能会匹配多行 range只检索给定返回的行使用一个索引来选择行。where之后出现 between , , , in等操作index需要扫描索引上的全部数据all全表扫描此时id上无索引
结果值从最好到最坏以此是: system const eq_ref ref range index ALL
explain select * from user;-- typeexplain select now();-- NULL explain select * from mysql.tables_priv;-- system 查询系统表explain select * from user where uid2;-- const
create unique index index_uname on user(uname);-- 添加普通索引
explain select * from user where uname张飞;-- const
drop index index_uname on user;explain select * from user where uid2;-- rangerexplain select uid from user;-- indexexplain select * from user;-- ALL
4table
显示这一步所访问数据库中表名称有时不是真实的表名字可能是简称
5rows
扫描行的数量
6key
possible_keys:显示可能应用在这张表的索引一个或多个
key :实际使用的索引如果为NULL则没有使用索引
key_len:表示索引中使用的字节数该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下长度越短越好
7extra
extra含义using filesort说明mysql会对数据使用一个外部的索引排序而不是按照表内的索引顺序进行读取称为“文件排序,效率低using temporary需要建立临时表(temporary table)来暂存中间结果常见于order by和group by;效率低using indexSOL所需要返回的所有列数据均在一棵索引树上避免访问表的数据行效率不错
4show file分析SQL
通过have_profiling参数,能够看到当前MySQL是否支持profile:
select have_profiling;
set profiling1; -- 开启profiling开关
执行完上述命令之后再执行show profiles指令来查看sQL语句执行的耗时:
show profiles;
show profiles;-- 查看每条时间
show profile for query 154;-- 查看cup耗费时间
show profile cpu for query 154;5trace优化器
打开trace设置格式为JSON并设置trace最大能够使用的内存大小避免解析过程中因为默认内存过小而不能够完整展示
SET optimizer_traceenabledon , end_markers_in_jsonon;
set optimizer_trace_max_mem_size1000000;
select * from information_schema.optimizer_trace \G;
6索引优化
1全值匹配
和字段匹配即可和字段无关
-- 全值匹配
explain select * from tb_seller where name华为 and status0 and address北京市;
2最左前缀法
如果索引有多列要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
-- 最左前缀法
-- 如果索引有多列要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
explain select * from tb_seller where name华为;-- 403explain select * from tb_seller where name华为 and status0;-- 410
explain select * from tb_seller where status0 and name华为;-- 410-- 违法最左前缀法索引失效
explain select * from tb_seller where status0;-- 跳跃一个只有最左端索引失效
explain select * from tb_seller where name华为 and address北京市;-- 403
3其他匹配原则
范围查询右边的列,不能使用索引
-- 根据前面的两个字段name , status查询是走索引的但是最后一个条件address没有用到索引
explain select * from tb_seller where name华为 and status1 and address北京市;-- 不要在索引列上进行运算操作,索引将失效
explain select * from tb_seller where substring(name,3,2)科技;-- 字符串不加单引号索引失效
explain select * from tb_seller where name华为 and status0;extra解释using index使用索引覆盖就会出现using where在查找使用索引的情况下需要回表去查询所需的数据using index condition查找使用了索引但是需要回表查询数据using index,using where查找使用了索引但是需要的数据都在索引列中能找到所以不需要回表查询数据
-- 尽量使用覆盖索引避免使用select *
explain select * from tb_seller where name华为 and address北京市;-- 效率低
-- 从索引树中获得所有数据
explain select name from tb_seller where name华为 and address北京市;-- 效率高
explain select namestatus,password from tb_seller where name华为 and address北京市;-- 效率低
1用or分割开的条件如果or前的条件中的列有索引而后面的列中没有索引那么涉及的索引都不会被用到
2以%开头的Like模糊查询索引失效。——》弥补不足不用*使用索引列
3如果MySQL评估使用索引比全表更慢,则不使用索引由数据本身决定
4is NULL , is NOT NULL有时有效,有时索引失效数据少的一方用索引
5in走索引not in索引失效
6单列索引和复合索引,尽量使用符合索引如果一张表有多个单列索引即使where中都使用了这些索引列则只有一个最优索引生效
7SQL优化
1大批插入数据
1主键顺序插入
因为InnoDB类型的表是按照主键的顺序保存的所以将导入的数据按照主键的顺序排列可以有效的提高导入数据的效率。如果innoDB表没有主键那么系统会自动默认创建一个内部列作为主键所以如果可以给表创建一个主键将可以利用这点,来提高导入数据的效率
通过load向表加载数据时保持主键有序提高效率
主键有序时间主键无序时间
-- 1、首先检查一个全局系统变量 local_infile 的状态 如果得到如下显示 ValueOFF则说明这是不可用的
show global variables like local_infile;-- 2、修改local_infile值为on开启local_infile
set global local_infile1;
2关闭唯一性校验
在导入数据前执行SET UNIQUE_CHECKSO关闭唯一性校验在导入结束后执行SETUNIQUE_CHECKS1恢复唯一性校验,可以提高导入的效率。
2insert优化
1如果需要同时对一张表插入很多行数据时应该尽量使用多个值表的insert语句这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快
2在事务中进行数据插入
3数据有序插入
3order by优化
1两种排序方式
第一种是通过对返回数据进行排序也就是通常说的filesort排序所有不是通过索引直接返回排序结果的排序都叫FileSort 排序
第二种通过有序索引顺序扫描直接返回有序数据这种情况即为using index不需要额外排序操作效率高
-- 创建组合索引
create index idx_emp_age_salary on emp(age,salary);-- 排序,order by
explain select * from emp order by age; -- Using filesort
explain select * from emp order by age,salary; -- Using filesortexplain select id from emp order by age; -- Using index
explain select id,age from emp order by age; -- Using index
explain select id,age,salary,name from emp order by age; -- Using filesort-- order by后边的多个排序字段要求尽量排序方式相同
explain select id,age from emp order by age asc, salary desc; -- Using index; Using filesort
explain select id,age from emp order by age desc, salary desc; -- Backward index scan; Using index-- order by后边的多个排序字段顺序尽量和组合索引字段顺序一致
explain select id,age from emp order by salary,age; -- Using index; Using filesort 2Filesort优化
通过创建合适的索引能够减少Filesort的出现但是在某些情况下条件限制不能让Filesort消失那就需要加快Filesort的排序操作。对于Filesort , MySQL有两种排序算法:
1两次扫描算法:MySQL4.1之前使用该方式排序。首先根据条件取出排序字段和行指针信息然后在排序区sort bufer中排序如果sort buffer不够则在临时表temporary table中存储排序结果。完成排序之后再根据行指针回表读取记录该操作可能会导致大量随机/O操作
2一次扫描算法:一次性取出满足条件的所有字段然后在排序区sort buffer 中排序后直接输出结果集。排序时内存开销较大但是排序效率比两次扫描算法要高
MysQL通过比较系统变量 max_length_for_sort_data的大小和Query语句取出的字段总大小来判定是否那种排序算法如果max_length_for_sort_data更大那么使用第二种优化之后的算法;否则使用第一种
可以适当提高sort_buffer_size和max_length_for_sort_data 系统变量来增大排序区的大小提高排序的效率
4子查询优化
使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的sQL操作同时也可以避免事务或者表锁死并且写起来也很容易。但是有些情况下子查询是可以被更高效的连接JoIN替代
explain select * from user where uid in (select uid from user_role );
explain select * from user u join user_role ur on u.uidur.uid;
连接(Join)查询之所以更有效率一些是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作
5limit优化
方法一
在索引上完成排序分页操作最后根据主键关联回原表查询所需要的其他列内容
explain select * from tb_user limit 900000,10;explain select * from tb_user a,(select id from tb_user order by id limit 900000,10) b where a.idb.id;
方法二
该方案适用于主键自增的表可以把Limit查询转换成某个位置的查询
explain select * from tb_user where id900000 limit 10;
