电子商务网站的建设和流程,网页设计实验报告课程建议,龙岗网站多少钱,动易网站 教程目录
SQL性能分析
SQL执行频率
慢查询日志
profile详情
explain执行计划
索引的使用
最左前缀法则
范围查询
索引列运算
字符串加引号
模糊查询
or连接的条件
数据分布影响
SQL提示
覆盖索引
前缀索引
索引设计原则
SQL优化
insert优化
主键优化
页分裂
…目录
SQL性能分析
SQL执行频率
慢查询日志
profile详情
explain执行计划
索引的使用
最左前缀法则
范围查询
索引列运算
字符串加引号
模糊查询
or连接的条件
数据分布影响
SQL提示
覆盖索引
前缀索引
索引设计原则
SQL优化
insert优化
主键优化
页分裂
页合并
主键设计原则
order by优化
group by优化
limit优化
count优化
count的使用
update优化 SQL性能分析
SQL执行频率
在对SQL进行优化的时候我们需要知道该数据库主要是哪些语句执行次数多将优化重心就放在执行次数多的语句当中查询SQL执行次数语句如下
SHOW [GLOBAL|SESSION] STATUS LIKE Com_______; 在这里我们可以看到一张表主要是进行哪些操作。
慢查询日志
慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认10秒的所有SQL语句的日志。MySQL的慢查询日志默认关闭需要在MySQL的配置文件etc/my.cnf中配置如下信息
# 开启MySQL慢日志开关
slow_query_log 1
# 设置慢日志的超时时间为2s
long_query_time 2 慢日志记录存储位置为/var/lib/mysql/localhost-slow.log profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have profiling参数能够看到当前MySQL是否支持profile操作:
SELECT have_profiling;
默认profiling是关闭的可以通过set语句在session/global级别开启profiling:
SET profiling 1; 当开启之后可以执行如下语句 # 会查看到每一条SQL的执行时间
show profiles;
# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
# 查看指定query_id的SQL语句CPU使用情况
show profile cpu for query query_id; explain执行计划
EXPLAIN或者DESC命令可以获取MySQL如何执行SELECT语句的信息包括在SELECT语句执行过程中如何连接和连接顺序。 语法 # 在select语句前直接加EXPLAIN或是DESC
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件; 字段解释 id select查询的序列号表示查询中执行select子句或是操作表的顺序执行顺序从上到下id相同从上到下id不同值越大越先执行
select_type表示 SELECT 的类型常见的取值有 SIMPLE(简单表即不使用表连接或者子查询)、PRIMARY(主查询即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询)等
type表示连接类型性能由好到差的连接类型为NULL、system、const、eg_ref、ref、range、index、all。
NULL不查询表的时候性能为NULL项目中不可能优化到NULLsystem查询系统表的时候为systemconst查询主键或唯一索引时为constref查询非唯一性的索引为refindex使用了索引但还是遍历了整个索引树all全表扫描
possible_key显示可能应用在这张表上的索引一个或多个
key表示实际使用的索引如果为NULL则没有使用索引。
key_len表示索引中使用的字节数该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下长度越短越好
rowsMySQL认为必须要执行查询的行数在innodb引擎的表中是一个估计值可能并不总是准确的。
filtered表示返回结果的行数占需读取行数的百分比filtered 的值越大越好
extra额外信息
索引的使用
最左前缀法则
如果索引了多列(联合索引)要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始并且不跳过索引中的列。如果跳跃某一列索引将部分失效(后面的字段索引失效)[索引字段存在即可在语句中的位置顺序不重要]
比如说将一张表的nameagesex字段创建一个联合索引那么在查找时name字段必须存在查询条件中如果不包含name字段只查询age与sex字段那么将会全表扫描。如果查询name与sex字段那么只有name字段会走索引而sex字段的索引失效因为跳过了age字段。
范围查询
联合索引中出现范围查询范围查询右侧的索引失效。
比如说在查询条件中加入的age18的条件那么sex的索引将会失效。
解决方法在业务允许的情况下能够使用或的情况不要使用和。
索引列运算
不要在索引列上进行运算操作索引将失效。
字符串加引号
如果字符串类型的字段在使用时不添加引号那么索引失效。
模糊查询
如果仅仅是尾部模糊匹配索引不会失效如果是头部模糊匹配那么索引失效。
or连接的条件
用or分隔开的条件如果or前的条件中的列有索引而后面的列中没有索引那么涉及的索引都不会被用到。
解决方法对没有索引的字符也添加索引。
数据分布影响
如果MySQL评估使用索引比全表更慢则不使用索引。
比如说age18如果表中大多数数据都满足这个条件那么即使age字段存在索引那么也不会使用。
当age18这个条件表中大多数数据不满足时才会走索引。
SQL提示
SQL提示是优化数据库的一个重要手段简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
比如说name字段存在单列索引也和age、sex存在联合索引那么在只查询name字段时可能会走联合索引。此时我们可以人为干预name字段走单例索引。 使用语法 # use index 在搜索时使用该索引
explain select * from 表名 use index(索引名) where ……;
# ignore index 在搜索时不使用该索引名
explain select * from 表名 ignore index(索引名) where ……;
# force index 强制使用该索引
explain select * from 表名 force index(索引名) where ……;
覆盖索引
尽量使用覆盖索引查询中使用了索引并且需要返回的列在该索引中已经全部能够找到减少select *的使用。
比如说name与age字段建立了索引在查找语句时where里对name与age进行条件查询在返回的字段中只填写name、age字段那么在使用explain查看SQL执行计划时extra字段的信息显示的时using where;using index可能会随着MySQL版本不同而显示不同。
如果返回的字段为name、age与sex字段但是没有在where里使用sex字段即使sex建立了索引extra显示的信息也为using index condition。
using where;using index使用了索引但是需要的数据在索引列中可以找到不需要进行回表查询using index condition使用了索引但是需要回表查询
前缀索引
当字段类型为字符串(varchartext等)时有时候需要索引很长的字符串这会让索引变得很大查询时浪费大量的磁盘IO影响查询效率。此时可以只将字符串的一部分前缀建立索引这样可以大大节约索引空间从而提高索引效率。 语法 # 最对应字段的后面括号中指定前多少字符建立索引
create index idx_xxx on table_name(column(n)); 前缀长度的选择 可以根据索引的选择性来决定而选择性是指不重复的索引值基数和数据表的记录总数的比值索引选择性越高则查询效率越高唯一索引的选择性是1这是最好的索引选择性性能也是最好的。
# 计算选择性
select count(distinct 字段) / count(*) from 表名;
# 计算前五个字符当索引的选择性
select count(distinct substring(字段,1,5)) / count(*) from 表名; 前缀索引查询流程 首先根据创建索引时指定的索引长度截取条件中的的对应长度字符取索引中获取第一次出现该索引的id然后区聚集索引中根据id获取到行信息从行信息中获取对应字段信息与语句中的信息对比如果相同则返回该行信息。如果不同则在辅助索引中链表中向后查询有无相同的索引如果没有则返回空。
索引设计原则
针对于数据量较大(百万级时)且查询比较频繁的表建立索引。针对于常作为查询条件 (where)、排序(order by)、分组 (group by) 操作的字段建立索引。尽量选择区分度高比如说手机号邮箱等的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高。如果是字符串类型的字段字段的长度较长可以针对于字段的特点建立前缀索引。尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率。要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增删改的效率。如果索引列不能存储NULL值请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时它可以更好地确定哪个索引最有效地用于查询。
SQL优化
insert优化
选择批量插入当一条一条语句进行插入时需要频繁的向数据库建立连接释放连接性能较低。其次批量插入时尽量选择一次插入500到1000条数据当插入的数据过多时建议选择将一条语句拆分成多条语句。手动事务提交MySQL中默认的事务提交规则是自动提交每执行一条语句就需要开启事务提交事务。频繁的开始事务与提交事务也会损耗性能。主键顺序插入与表数据存储结构有关具体原因查看主键优化。
大批量插入数据时比如说百万级数据使用insert性能不高可以选择另一个指令load # 客户端连接服务端时加上参数 --local-infile。意为需要加载本地文件
mysql --local-infile -u root -p
# 设置全局参数local infile为1开启从本地加载文件导入数据的开关
set global local_infile 1;
# 执行load指令将准备好的数据加载到表结构中
load data local infile /root/sal1.log into table tb_user fields terminated by , lines terminated by \n;
#意思是将/root/sal1.log文件加载到tb_user表中字段之间使用,分隔数据之间使用\n分隔。
主键优化
在InnoDB中表数据都是根据主键顺序组织存放的这种存储方式的表称为索引组织表。
我们之前说到主键索引的存储结构如下图所示 非叶子节点起到索引数据的作用而叶子节点存储数据。而无论非叶子节点还是叶子节点都是存放在page当中的。接下来我们基于page查看向表中插入数据的流程
页分裂
首先我们需要知道page可以为空也可以填充一半也可以全部填充。每个页至少包含2条数据如果一条数据过大会出现行溢出现象。 先来查看一种主键顺序插入的情况 当一个页中已经无法满足主键为9的数据插入时会申请第二个页来存放主键为9的数据。 最后插入情况如下所示。 如果主键乱序插入的话则是另一种情况 在已经插入好的数据中需要插入主键为50的数据此时第一个页当中不满足将主键为50的数据插入。 此时会开辟第三个页并且将第一个页中超过百分之五十之后的数据移动到第三个页当中并将主键为50的数据插入到第三个页当中。 此时需要修改链表指针连接修改结果如下 乱序插入时出现的情况就是页分裂。
页合并
当删除一行数据时时间上数据并没有被物理删除而是会被标记(flaged)为被删除数据被标记的数据空间可以被其他数据使用。
当页中被删除的数据达到页合并阈值时默认为页的百分之50InnoDB会开始寻找最靠近的页前或后查看是否可以将两个页合并以优化空间使用。
比如说下图中第二个页中的主键为13141516的数据已经被标记删除了被删除数据达到页合并阈值。 主键设计原则
在满足业务需求的情况下尽量降低主键的长度。这是从索引的角度考虑二级索引会存储主键信息当索引过多时会需要额外的磁盘IO。
插入数据时尽量选择顺序插入选择使用AUTO-INCREMENT自增主键。这是为了避免页分裂。
尽量不要使用UUID做主键或者是其他自然主键如身份证号。
在业务操作时尽量避免对主键的修改
order by优化
首先需要解释EXPLAIN中Extra字段的两个值
Using filesort通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区sort buffer中完成排序操作所有不是通过索引直接返回排序结果的排序都叫FileSort排序。Using index通过有序索引顺序扫描直接返回有序数据这种情况即为using index不需要额外排序操作效率高。
当我们需要对字段进行排序时如果可以通过索引直接返回此时效率高。
比如说我们对一张user表的user与phone建立联合索引。默认的排序方式为升序此时我们执行order by user,phone。就是通过索引直接返回的数据。
但是如果执行order by user asc,phone desc。那么无法通过索引直接返回结果需要对user相同的数据进行phone倒序排列此时效率就低。又或是执行order phone,user。这样也是无法通过索引直接返回结果的。
使用Order By 的注意事项
根据排序字段建立合适的索引多字段排序时也遵循最左前缀法则。尽量使用覆盖索引。多字段排序,一个升序一个降序此时需要注意联合索引在创建时的规则(ASC/DESC)。如果不可避免的出现filesort大数据量排序时可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。
group by优化
在使用分组操作时如果不是通过索引获取的数据那么EXPLAIN中的Extra字段会出现Using temporary意为使用临时表。这种效率不高。
比如说我们对user表中的age与sex建立联合索引当我们执行
select age,sex,count(*) from user group by age,sex;
时我们可以通过索引拿到对应的数据因此效率就高如果执行
select age,sex,count(*) from user group by sex;
时不满足最左前缀法则因此需要走临时表但是如果执行的是
select age,sex,count(*) from user where age 18 group by sex;
时走的也是索引因为我们对age进行了过滤用到了age索引。
limit优化
在执行limit时需要将返回的数据之前的数据进行排序后在返回比如说返回limit 1000000,10。我们只需要返回1000000-1000010数据但是需要排序1000010个数据。这样会造成极大的损失。对limit的优化官方推荐覆盖查询加子查询的方式。比如说user表中存在500w数据我需要返回2000000-2000010条数据我只需要这十条数据的id即可通过执行
select * from user limit 2000000,10;
通过索引拿到10条数据的id。然后通过另一条查询语句获取这十条数据的信息。
需要注意的是limit不支持在in()中执行因此我们只能通过多表查询的方式进行查询。具体语句为
select u.* from user u,(select id from user limit 2000000,10) l where l.id u.id;
count优化
在MyISAM引擎当中把一个表的总行数存储在了磁盘上因此在执行count(*)时会直接返回这个数。
但在InnoDB中没有它需要一行行读取数据后累计计数返回。
对于count并没有很好的优化方案。我们可以选择自己计数。比如说我们可以基于内存的keyvalue数据结构的数据库比如Redis自己保存记录条数。执行insert时就加1。
count的使用 count(主键)统计总记录数。 InnoDB会遍历整张表把每一行的主键id取出来返回给服务层。服务层拿到主键后直接按行进行累加。 count(字段)统计该字段不为NULL的数量。 没有not null 约束InnoDB 引擎会遍历整张表把每一行的字段值都取出来返回给服务层服务层判断是否为null不为null计数累加。有not null约束InnoDB 引擎会遍历整张表把每一行的字段值都取出来返回给服务层直接按行进行累加。 count(1)只要查询一条记录不为null。那么便返回1。 InnoDB引擎遍历整张表但不取值。服务层对于返回的每一行放一个数字“1”进去直接按行进行累加。 count(*)记录总记录数。 InnoDB不会把全部字段取出来而是进行了优化不取值服务层直接按行进行累加。
update优化
update的优化主要在于MySQL中使用的是表级锁还是行级锁。
比如说有张user表存在id与name字段此时除了id没有其他索引。加入现在并发两条sql语句如下
update user set name 张三 where id 1;update user set name 李四 where id 2;
此时不会有任何问题因为InnoDB是支持行级锁的第一条SQL语句锁住的是id为1的数据而第二条SQL锁住的是id为2的数据他们互不干扰。 那么接下来如果并发如下语句 update user set name 张三 where id 1;update user set name 李四 where name 王五;
第一条语句还是锁住id为1的数据。但是第二条数据需要锁住整张表因为name字段没有索引需要整表扫描。此时其他sql语句就无法接着进行。
InnoDB的行锁是针对索引加锁不是针对记录加锁并且索引不能失效。否则会从行锁升级到表锁。
