以上述业务为背景,与 PHP 程序员沟通,优化 该 SQL 语句。
select * from students where `姓名` = '$name' or `手机号码` = '$vipid'
1、调整 select 的范围
2、对常用条件字段,建立索引
3、尽可能的让 查找的内容,被 子索引 覆盖, 产生覆盖扫描。
4、能不能只用 一个 多列 索引,解决以上问题。
explain 执行计划分析 : PHP 程序触发, 从 SQL 语句出发
show status like ‘last_query_cost’; 参照该值。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sql语句的优化 ###########################
1、尽量避免 select * ~~~~~~~~~~~~~~~~~~~~~~~~~~ Mysql 半双工 A
======>Mysql
在数据量少并且访问量不大的情况下,select * 没有什么影响,但是量级达到一定级别的时候,在执行效率上将会大打折扣。
2、尽量少做计算 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Mysql的作用是用来存取数据的,不是做计算的,做计算的话可以用其他方法去实现,mysql做计算是很耗资源的。
3、尽量不要使用内置的计算函数,和 正则表达式 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4、 当确定我们的目标结果只要一行数据时使用 LIMIT 1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
加上LIMIT 1可以增加性能。
MySQL数据库引擎会在查找到一条数据后停止搜索,而不是继续往后查询下一条符合条件的数据记录
5、为搜索字段建立 合适索引 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
索引不一定就是给主键或者是唯一的字段,如果在表中,有某个字段经常用来做搜索,需要将其建立索引。
6、永远为 每张表设置一个 ID主键 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
我们应该为数据库里的每张表都设置一个ID做为其主键,而且最好的是一个 INT 型的(推荐使用UNSIGNED),
并设置上自动增加的 AUTO_INCREMENT 标志。
就算是我们 users 表有一个叫 “email” 的字段,我们也别让它成为主键。
使用 VARCHAR 类型来当主键会使用得性能下降。 不如char
7、使用 ENUM 而不是 VARCHAR ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串。
这样一来,用这个字段来做一些选项列表变得相当的完美。
如果我们有一个字段,比如“性别”,“国家”,“民族”,“状态”或“部门”, 我们知道这些字段的取值是有限而且固定的,
那么,我们应该使用 ENUM 而不是 VARCHAR。
8、尽可能的不要赋值为 NULL ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
如果不是特殊情况,尽可能的不要使用NULL。 ## is null is not null name=""
在MYSQL中对于INT类型而言,EMPTY是0,而NULL是空值。
而在Oracle中 NULL和EMPTY的字符串是一样的。
NULL也需要占用存储空间,并且会使我们的程序判断时更加复杂。
9、固定长度的表会更快 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 设计范式
如果表中的所有字段都是 “固定长度”的,整个表会被认为是 “static” 或 “fixed-length” 。
例如,表中没有如下类型的字段: VARCHAR,TEXT,BLOB。
只要我们包括了其中一个这些字段,那么这个表就不是 “固定长度静态表”了,这样,MySQL 引擎会用另一种方法来处理。
固定长度的表会提高性能,因为MySQL搜寻得会更快一些,因为这些固定的长度是很容易计算下一个数据的偏移量的,所以读取的自然也会很快。
而如果字段不是定长的,那么,每一次要找下一条的话,需要程序找到主键。
并且,固定长度的表也更容易被缓存和重建。
不过,唯一的副作用是,固定长度的字段会浪费一些空间,因为定长的字段无论我们用不用,他都是要分配那么多的空间。
另外在取出值的时候要使用trim去除空格
10、垂直分割 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
用户表 住址表
id 姓名 性别 手机 身份证号码 id 用户id 用户住址
“垂直分割”是一种把数据库中的表按列变成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。
11、越小的列会越快 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
年龄 int tinyint
对于大多数的数据库引擎来说,硬盘操作可能是最重大的瓶颈。
所以,把我们的数据变得紧凑会对这种情况非常有帮助,因为这减少了对硬盘的访问。
参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。
如果一个表只会有几列(比如说字典表,配置表),那么,我们就没有理由使用 INT 来做主键,使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT
会更经济一些。如果我们不需要记录时间,使用 DATE 要比 DATETIME 好得多。
12、简单的数据类型在计算时需要更少的CPU周期,比如,整型 就比 字符 操作代价低,因而会使用整型来存储ip地址,
使用DATETIME来存储时间,而不是使用字符串。
13、尽量少排序 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
排序操作会消耗较多的 CPU 资源,所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL的响应时间。
对于MySQL来说,减少排序有多种办法,比如:
通过利用索引来排序的方式进行优化
减少参与排序的记录条数
非必要不对数据进行排序
举例: 存在这样的一张表test,它有一个自增的id作为主索引。现在要查询 id号处于某一个范围内的记录,可以使用如下SQL语句:
查找 id 大于 208838 的 50条记录
SELECT * FROM `test` order by id asc limit 208838,50
这条SQL语句的意思是从id号为208888的记录开始向后取50条记录。
在一个30万条记录的数据库中测试,在主索引都已经建立好的情况下,执行这条语句的时间为 40~50 秒。
SELECT * FROM `test` WHERE id BETWEEN 208838 AND 208888
这条语句使用了一个条件进行过滤,在实际中测试的执行时间约为0.06秒。
究其原因,是因为虽然id属性上已经有索引了,但是排序仍然是一个非常高代价的操作,要慎用。
14、优先 优化 高并发的 SQL ( 核心SQL ),而不是执行频率低某些 “大” SQL ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
对于破坏性来说,高并发的 SQL 总是会比低频率的来得大,因为高并发的 SQL 一旦出现问题,
甚至不会给我们任何喘息的机会就会将系统压跨。而对于一些虽然需要消耗大量 IO 而且响应很慢的 SQL,由于频率低,即使遇到,
最多就是让整个系统响应慢一点,但至少可能撑一会儿,让我们有缓冲的机会。
15、避免类型转换 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
这里所说的“类型转换”是指 where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换:
如果我们传入的数据类型和字段类型不一致,同时我们又没有做任何类型转换处理,MySQL 可能会自己对我们的数据进行类型转换操作,
也可能不进行处理而交由存储引擎去处理,这样一来,就会出现索引无法使用的情况而造成执行计划问题。
16、尽量早过滤 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
这一优化策略其实最常见于索引的优化设计中 (将过滤性更好的字段放得更靠前)。
在 SQL 编写中同样可以使用这一原则来优化一些 Join 的 SQL。
比如我们在多个表进行分页数据查询的时候,我们最好是能够在一个表上先过滤好数据分好页,
然后再用分好页的结果集与另外的表 Join,这样可以尽可能多的减少不必要的 IO 操作,大大节省 IO 操作所消耗的时间。
mysql安全机制
最新推荐文章于 2022-10-30 10:54:25 发布