代码做网站seo网站图片优化

当前位置： 首页 > news >正文

代码做网站,seo网站图片优化,双线网站,wordpress文章统计插件目录 一、准备数据 1.1、创建表结构 1.2、创建存储过程 二、索引介绍 2.1、类型介绍 2.2、建立索引 2.3、建立复合索引 2.4、查看所有建立的索引 2.5、删除索引 三、EXPLAIN分析参数说明 四、SQL优化案例 4.1、避免使用SELECT * 4.2、慎用UNION关键字 4.4、避免使…目录 一、准备数据 1.1、创建表结构 1.2、创建存储过程  二、索引介绍 2.1、类型介绍 2.2、建立索引 2.3、建立复合索引 2.4、查看所有建立的索引 2.5、删除索引 三、EXPLAIN分析参数说明 四、SQL优化案例 4.1、避免使用SELECT * 4.2、慎用UNION关键字 4.4、避免使用or条件有争议 4.5、LIKE语句优化 4.6、字符串字段优化 4.7、最左匹配原则重要 4.8、COUNT查询数据是否存在优化  4.9、LIMIT关键字优化  4.10、提升GROUP BY的效率 4.11、避免使用!或 4.12、避免NULL值判断 4.13、避免函数运算 4.14、JOIN的表中使用索引字段 4.15、用EXISTS代替IN 一、准备数据 提前准备一张学生表数据和一张特殊学生表数据用于后面的测试用。 1.1、创建表结构 创建一个学生表 CREATE TABLE student (id int(11) unsigned NOT NULL AUTO_INCREMENT,name varchar(50) DEFAULT NULL,age tinyint(4) DEFAULT NULL,id_card varchar(20) DEFAULT NULL,sex tinyint(1) DEFAULT 0, address varchar(100) DEFAULT NULL,phone varchar(20) DEFAULT NULL, create_time timestamp NULL DEFAULT CURRENT_TIMESTAMP,remark varchar(200) DEFAULT NULL,PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8; 再创建一个特殊学生表 CREATE TABLE special_student (id int(11) unsigned NOT NULL AUTO_INCREMENT,stu_id int(11) DEFAULT NULL,PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8; 1.2、创建存储过程  在学生表中插入100w条数据手动开启和提交事务每插入1w条记录后手动COMMIT一次事务最后再COMMIT一次以提交剩下的记录这样可以让插入速度更快,因为不需要为每条记录都 COMMIT,从而降低 IO 次数。 CREATE PROCEDURE insert_student_data() BEGINDECLARE i INT DEFAULT 0; DECLARE done INT DEFAULT 0; DECLARE continue HANDLER FOR NOT FOUND SET done 1;START TRANSACTION; WHILE i 1000000 DOINSERT INTO student(name,age,idcard,sex,address,phone,remark)VALUES(CONCAT(姓名,i), FLOOR(RAND()*100),FLOOR(RAND()*10000000000),FLOOR(RAND()*2),CONCAT(地址,i), CONCAT(12937742,i),CONCAT(备注,i));SET i i 1; IF MOD(i,10000) 0 THEN COMMIT;START TRANSACTION;END IF; END WHILE; COMMIT; END 执行特殊学生表的存储过程  CALL insert_special_student(); 二、索引介绍 2.1、类型介绍 普通索引    最基本的索引没有任何限制 唯一索引    与普通索引类似不同的就是索引列的值必须唯一但允许有空值 主索引    即主键索引关键字PRIMARY 复合索引    为了更多的提高MySQL效率可建立组合索引遵循“最左前缀”原则 全文索引    可用于MyISAM表MySQL5.6之后也可用于innodb表用于在一篇文章中检索文本信息的, 针对较大的数据生成全文索引很耗时和空间 2.2、建立索引 CREATE INDEX id_card_index ON student(id_card); 2.3、建立复合索引 CREATE INDEX name_address_phone_index ON student(name,address,phone); 2.4、查看所有建立的索引 SHOW INDEX FROM student; 2.5、删除索引 ALTER TABLE 表名 DROP INDEX 索引名称; 三、EXPLAIN分析参数说明 1、idSELECT语句的编号。可以通过id来区别多条SELECT语句。 2、select_typeSELECT类型主要有SIMPLE、PRIMARY、DERIVED等类型。 SIMPLE简单的SELECT不含子查询及UNION。 PRIMARY查询中最外层的SELECT。 DERIVED包含的子查询中的SELECT。 3、table显示这一行的数据是关于哪张表的。 4、partitions匹配的分区信息。 5、type显示连接使用了何种类型。 最好到最差的连接类型为 system const eq_reg ref range index ALL. system 表只有一行记录(等于系统表)const使用常量进行索引查询eq_ref唯一索引扫描通常使用主键约束ref非唯一性索引扫描range索引范围扫描index全索引扫描ALL全表扫描 6、possible_keys显示可能应用在这张表中的索引。 7、key实际使用的索引。 8、key_len使用的索引的长度。 9、ref显示索引的哪一列被使用。 10、rows根据表统计信息及索引条件估算查询返回的且接近的行数 11、filtered显示了通过条件过滤出的行数的百分比估计值。 12、Extra包含不适合在其他列展示但是需要展示的信息。 四、SQL优化案例 4.1、避免使用SELECT * 有的时候我们为了图方便会直接使用SELECT * 一次性查出表中所有的数据 SELECT * FROM student 执行结果如图所示 可以看到执行时间花了2s左右耗时很长 在实际开发中我们给页面展示的数据可能就只要2-3个字段如果直接全部查出来了岂不是白白浪费了字段同时也损耗了性能这是因为SELECT * 不会走覆盖索引会出现大量的回表操作从而导致SQL性能大幅度降低。 我们上面建立了联合索引我们就可以只查询索引列这样会大幅度提升查询效率优化的SQL如下 SELECT name,address,phone FROM student 执行结果如图所示 这样执行的速度大大提高 分析SQL 使用EXPLAIN关键字执行这段SQL EXPLAIN SELECT name,address,phone FROM student 执行结果如图所示 确实走了我们建立的复合索引。  4.2、慎用UNION关键字 例如我们根据性别去查询所有学生的信息虽然这种操作多此一举直接SELECT *就好了为了演示这2个关键字的详细区别使用UNION关键字执行的SQL如下 SELECT * FROM student WHERE sex 0 UNION SELECT * FROM student WHERE sex 1 执行结果如图所示  我滴妈查了100w条足足整整等了32s左右这个速度要是放到系统上查个数据等到娃娃菜都凉了 这是因为在使用UNION执行完SQL后会帮我们获取所有数据并去掉重复的数据性能的损耗就在这里而UNION ALL和UNION相反帮我们获取所有数据但会保留重复的数据。 我们改用UNION ALL关键字优化的SQL如下 SELECT * FROM student WHERE sex 0 UNION ALL SELECT * FROM student WHERE sex 1 执行结果如图所示 同样查询100w条数据这边执行速度大大提高了只用到了3s左右  4.3、小表驱动大表  言简意赅意思就是让小表查出来的数据去再查询大表当中的数据。比如我们想查询学生表当中特殊学生的信息我们就可以使用以special_student这个小表去驱动student这个大表SQL如下 SELECT * FROM student WHERE id IN (SELECT stu_id FROM special_student) 执行结果如图所示 只用了0.02s速度很可观因为IN关键字中的子查询语句子查询语句的数据量很少所以查询速度会很快 4.4、避免使用or条件有争议 如果我们要查询指定的性别或者指定的身份证号码的学生执行SQL如下 SELECT * FROM student WHERE sex 0 OR id_card 7121877527789执行结果如图所示 总共查询了近50w条数据耗时1.4s左右我们改用UNION ALL关键字查询 SELECT * FROM student WHERE sex 0 UNION ALL SELECT * FROM student WHERE id_card 7121877527789 执行结果如图所示 和上面查询的数据量一样好事在1.7s左右怎么会没什么区别呢 分析SQL  使用EXPLAIN关键字分析一下使用OR关键字的这段SQL EXPLAIN SELECT * FROM student WHERE SEX 0 OR id_card 7121877527789 执行结果如图所示  很明显虽然可能会用到建立id_card的索引正因为sex这个字段没有建立索引还是走了一次全表扫描。 使用EXPLAIN关键字执行这段SQL EXPLAIN SELECT * FROM student WHERE sex 0 UNION ALL SELECT * FROM student WHERE id_card 7121877527789 执行结果如图所示 很明显条件是sex的走了全表但是id_card走了索引所以依旧还是走了一次全表扫描所以网上说的关于UNION ALL代替OR的我这边实测感觉还是存在争议的 4.5、LIKE语句优化 平时我们日常开发用到的LIKE关键字进行模糊匹配会非常多但是有的情况会使索引失效导致查询效率变慢例如 只要身份证字段包含50就查出来执行SQL如下 SELECT * FROM student WHERE id_card like %50% 执行结果如图所示 用了0.8s左右。 只要身份证号码以50结尾就查出来执行SQL如下 SELECT * FROM student WHERE id_card like %50 执行结果如图所示 用了0.4s左右。 只要身份证号码以50开头的就查出来执行SQL如下 SELECT * FROM student WHERE id_card like 50% 执行结果如图所示 这次执行非常快0.08s左右。 分析SQL 使用EXPLAIN关键字执行这段SQL EXPLAIN SELECT * FROM student WHERE id_card like %50% 执行结果如图所示 很明显走了全表扫描 使用EXPLAIN关键字执行这段SQL EXPLAIN SELECT * FROM student WHERE id_card like %50 执行结果如图所示 这次便走了索引 4.6、字符串字段优化 查询指定的身份证号码的学生如果我们平时疏忽了给身份证号码加上单引号执行SQL如下 SELECT * FROM student WHERE id_card 5040198345 执行结果如图所示 耗时0.4s左右。 给身份证号码加上单引号优化的SQL如下 SELECT * FROM student WHERE id_card 5040198345 执行结果如图所示 耗时0.02s左右这次明显快多了 分析SQL 使用EXPLAIN关键字执行这段SQL EXPLAIN SELECT * FROM student WHERE id_card 5040198345 执行结果如图所示 可能用到了id_card的索引但是还是走了全表扫描 使用EXPLAIN关键字执行这段SQL EXPLAIN SELECT * FROM student WHERE id_card 5040198345 执行结果如图所示  这边走了索引。  这边走了索引。  4.7、最左匹配原则重要 上面我们按照nameaddress和phone这个顺序建立了复合索引相当于建立了namename、address和name、address、phone三个索引如果我们查询的where条件违背了建立的顺序则复合索引就失效了下面直接进行SQL分析 分析SQL 使用EXPLAIN关键字执行这段SQL EXPLAIN SELECT * FROM student WHERE name 姓名_4 and phone 7121877527 and address 地址_4 执行结果如图 为什么明明违背了最左匹配原则依旧还是走了复合索引呢可能是如下原因 1、通过索引过滤性能足够好所以还是选择利用索引。 2、联合索引中前几个字段过滤效果较好所以仍然选择利用索引。 可能的执行计划大概是 1、优先通过phone字段过滤将要扫描的记录减少一部分。 2、然后通过address字段继续过滤,再减少一部分记录。 3、最后通过name字段过滤,已经剩下很少的记录需要扫描。 4、尽管违反了最左匹配解释器可能认为仍然利用索引效率比较高。 所以总的来说就是解释器会根据实际情况进行权衡即使是违反最左匹配原则也可能会选择利用索引。但这并不是一个良好的查询优化最好还是严格遵守最左匹配原则。 以下是严格遵守最左匹配原则的SQL SELECT * FROM student WHERE name 姓名_4 SELECT * FROM student WHERE name 姓名_4 and address 地址_4 SELECT * FROM student WHERE name 姓名_4 and address 地址_4 and phone 7121877527
4.8、COUNT查询数据是否存在优化  比如我想判断年龄为18岁的学生是否存在我们往往会执行如下SQL SELECT COUNT(*) FROM student WHERE age 18 执行结果如图所示 耗时0.4s左右虽然知道学生年龄18岁存在但是没必要查询出这么多数量出来我们只要知道是否存在即可 不再使用COUNT而是改用LIMIT 1让数据库查询时遇到一条就返回这样就不要再继续查找还有多少条了优化的SQL如下 SELECT 1 FROM student WHERE age 18 LIMIT 1 执行结果如图所示 耗时0.01s左右很快就知道了。  4.9、LIMIT关键字优化  平日开发工作中我们对于分页的处理一般是这样的 SELECT * FROM student LIMIT 999910,10 执行结果如图所示  耗时0.56s但是因为我们的ID属于自增长所以我们可以在此基础上进行一定的优化优化的SQL如下  SELECT * FROM student WHERE ID 999910 LIMIT 10 执行结果如图所示 仅仅用时0.02s非常快 4.10、提升GROUP BY的效率 我们平日写SQL需要多多少少会使用GROUP BY关键字它主要的功能是去重和分组。 通常它会跟HAVING一起配合使用表示分组后再根据一定的条件过滤数据常规执行的SQL如下 SELECT age,COUNT(1) FROM student GROUP BY age HAVING age 18 执行结果如图所示 耗时总计0.53s左右不过还可以进行优化我们可以在分组之前缩小筛选的范围然后再进行分组优化的SQL如下 SELECT age,COUNT(1) FROM student where age 18 GROUP BY age  执行结果如图所示 耗时0.51s左右虽然不明显也是一种不错的思路。 4.11、避免使用!或 尽量避免使用!或操作符下面直接分析SQL SQL分析 使用EXPLAIN关键字执行这段SQL EXPLAIN SELECT * FROM student WHERE id_card ! 5031520645 执行结果如图所示 虽然我们给了id_card字段建立了索引但还是走了全表扫描 4.12、避免NULL值判断 为了确保没有NULL值我们可以设定一个默认值下面直接分析SQL SQL分析 使用EXPLAIN关键字执行这段SQL EXPLAIN SELECT * FROM student WHERE id_card IS NOT NULL 执行结果如图所示 依旧还是走了全表扫描。 使用EXPLAIN关键字执行这段SQL EXPLAIN SELECT * FROM student WHERE id_card IS NULL 执行结果如图所示 这样是走索引的 4.13、避免函数运算 在日常SQL撰写中在WHERE条件上多多少少会用到一些函数例如截取字符串执行SQL如下 分析SQL  使用EXPLAIN关键字执行这段SQL EXPLAIN SELECT * FROM student WHERE SUBSTR(id_card,0,9) 执行结果如图所示  索引失效走了全表扫描。  4.14、JOIN的表中使用索引字段 如果日常开发中使用JOIN关键字链接表后使用的ON关键字进行条件链接时如果条件没有索引则会进行全表扫描执行SQL如下 EXPLAIN SELECT * FROM student a,special_student b WHERE a.id b.stu_id 执行结果如图所示  正因为special_student表的stu_id没有建立索引则导致了全表扫描 为stu_id建立索引后执行SQL如下 CREATE INDEX stu_id_index ON special_student(stu_id);EXPLAIN SELECT * FROM student a,special_student b WHERE a.id b.stu_id 执行结果如图所示   两张表都走了索引。 4.15、用EXISTS代替IN IN关键字适合于外表大而内表小的情况EXISTS适合于外表小而内表大的情 SELECT * FROM special_student WHERE EXISTS ( SELECT 1 FROM student WHERE special_student.stu_id student.id) 况执行SQL如下 运行结果如图所示 执行效率在0.02s左右这里special_student是小表student是大表速度非常快