weixin_40725706

这个屌丝很懒，什么也没留下！

热门标签

mysql bigint与md5_mysql常见的优化需要注意的点

作者：weixin_40725706 | 2024-04-07 23:30:56

踩

sql md5 bigint

1.explain分析

explian引用

索引基数

show indexes from table_name;

主键索引具有最好的基数

测试时

不走缓存

SELECT SQL_NO_CACHE id from test_null;

2.更好的索引类型

索引列尽可能的为not null ,避免在可空的列索引上进行二次扫描

要尽量避免 NULL ，关于索引列为Null的是否走索引，3.使用unique index

与常规索引比不需要进行索引范围扫描

4.使用primary key

主键是uniquekey的一种特殊形式。在innodb中,一个uniquekey是一个聚集索引(即对磁盘上数据排列的索引),当数据按照主键的次序进行检索时会极大改进性能

5.索引太多是有害的

例如，如果possible_keys 列表中有超过3个的索引,mysql优化器有太多信息而无法确定最好使用哪个索引，也就意味着有些是低效或者无用的索引

6.索引列使用最小可能的数据类型

比如在一个varchar(100)甚至更大的列上建立索引，一种改进方法是建立一个额外的列,并在包含较大的varchar(100)列的md5值的额外varchar(32)列上创建索引。

更好的方法是使用bigint来存储md5值的数字表示,数字索引更加高效

CONV(N,from_base,to_base)

mysql> select conv('a',16,10);+-----------------+

| conv('a',16,10) |

+-----------------+

| 10 |

+-----------------+

mysql> select conv(substr(md5('abc'),1,16),16,10);+-------------------------------------+

| conv(substr(md5('abc'),1,16),16,10) |

+-------------------------------------+

| 10376663631224000432 |

+-------------------------------------+

7.建立索引时

如果使用到多个列,定义多列索引

哪列的唯一性更高(基数大 show indexes from table_name),哪列优先放在多列索引的前面

覆盖索引是理性的索引 (explain 里extra的信息时using index)

覆盖索引包括所有需要的列,但是不需要读取单独的数据页,实际意味着不需要读取数据存储,只利用索引数据就可以检索到实际想要的查询的数据

在myisam表里,意味着只要读入索引就可以得到问题的记录,在innodb中索引和数据是位于同一个文件中的,但仍然会高效些,因为只需要读入索引

优化部分索引的性能

select type from tb where sid=1

建立(sid,type)的索引就是覆盖索引,比单独在sid,type上建索引要快

与其在长字符的列上定义索引,还不如只在左边的一小部分上建立索引

8.一些常见的不使用索引的情况

开始字符是通配符是,或者在索引列上使用标量函数

like "%123",upper()

字符串类型的查询不加引号

9.覆盖索引的左前缀原则

10.更详细的分析

set profiling=1;

select * from table;

show profile;

show profile source ;

mysql> select * from test_null where mark like 'aaa9999%';+------+---------+

| id | mark |

+------+---------+

| 9999 | aaa9999 |

+------+---------+

1 row in setmysql>show profile;+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 5.5E-5 |

| checking permissions | 1.1E-5 |

| Opening tables | 2E-5 |

| init | 2.4E-5 |

| System lock | 7E-6 |

| optimizing | 8E-6 |

| statistics | 1.4E-5 |

| preparing | 7E-6 |

| executing | 2E-6 |

| Sending data | 0.006271 |

| end | 5.7E-5 |

| query end | 3.6E-5 |

| closing tables | 5.1E-5 |

| freeing items | 0.000348 |

| cleaning up | 0.00011 |

+----------------------+----------+

mysql>show profile source;+----------------------+----------+-----------------------+----------------------+-------------+

+----------------------+----------+-----------------------+----------------------+-------------+

| end | 5.7E-5 | handle_query | sql_select.cc | 199 |

+----------------------+----------+-----------------------+----------------------+-------------+

mysql> set profiling=1;

Query OK,0rows affected

mysql> select * froma;+----+-----+-------+

| id | uid | phone |

+----+-----+-------+

| 1 | 1 | 22 |

| 2 | 2 | 33 |

| 3 | 3 | 33 |

| 4 | 4 | 22 |

| 5 | 5 | 22 |

+----+-----+-------+

5 rows in setmysql> select * fromb;+-------+

| phone |

+-------+

| 1111 |

| 2222 |

| 3333 |

| 4444 |

+-------+

4 rows in setmysql>show profiles;+----------+------------+-----------------+

| Query_ID | Duration | Query |

+----------+------------+-----------------+

| 1 | 0.00025225 | select * from a |

| 2 | 0.0009805 | select * from b |

+----------+------------+-----------------+

2 rows in setmysql> show profile for query 2;+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000106 |

| checking permissions | 1.4E-5 |

| Opening tables | 3.3E-5 |

| init | 3E-5 |

| System lock | 2E-5 |

| optimizing | 0.000259 |

| statistics | 4.5E-5 |

| preparing | 2.5E-5 |

| executing | 4E-6 |

| Sending data | 0.000358 |

| end | 7E-6 |

| query end | 6E-6 |

| closing tables | 8E-6 |

| freeing items | 5.4E-5 |

| cleaning up | 1.4E-5 |

+----------------------+----------+

15 rows in set

View Code

优化update

换成select使用explain

优化delete

mysql> select * fromparent;+----+------+

| id | name |

+----+------+

| 1 | pa |

| 2 | pb |

| 3 | pc |

| 4 | pd |

+----+------+

4 rows in setmysql> select * fromchild;+-----------+----------+

| parent_id | child_id |

+-----------+----------+

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

| 1 | 4 |

| 1 | 5 |

| 2 | 6 |

| 0 | 7 |

| 5 | 8 |

| 6 | 9 |

| 5 | 10 |

+-----------+----------+

删除child中parent_id不在parent表的记录

一般的写法是

delete from child where parent_id not in(select id from parent);

更加高效的是使用连接查询

通过以下来验证

set profiling=1;

select * from child where parent_id not in(select id from parent);

select child.* from child left join parent on child.parent_id=parent.id where parent.id is null;

select query_id,count(*) as '#ops' ,sum(duration) from information_schema.profiling group by query_id;

select * from information_schema.profiling ;

演示结果

mysql> set profiling=1;

Query OK,0rows affected

mysql> select * from child where parent_id not in(select id fromparent);+-----------+----------+

| parent_id | child_id |

+-----------+----------+

| 0 | 7 |

| 5 | 8 |

| 6 | 9 |

| 5 | 10 |

+-----------+----------+

4 rows in setmysql> select child.* from child left join parent on child.parent_id=parent.id where parent.id is null;+-----------+----------+

| parent_id | child_id |

+-----------+----------+

| 0 | 7 |

| 5 | 8 |

| 6 | 9 |

| 5 | 10 |

+-----------+----------+

4 rows in setmysql> select query_id,count(*) as '#ops' ,sum(duration) from information_schema.profiling group byquery_id;+----------+------+---------------+

| query_id | #ops | sum(duration) |

+----------+------+---------------+

| 1 | 23 | 0.000749 |

| 2 | 16 | 0.000388 |

+----------+------+---------------+

优化器显示第二个用了更少的操作

优化Insert，同一表的多条类似的多个insert改写成1条减少数据库的网络往返

例外一个好处是mysql只需为insert语句产生一次执行计划,可以在多个值上利用同一个执行计划

当批量插入时,如果单个插入失败,多个value子句说明的记录都无法插入成功

优化insert ...on duplicate key update

replace在内部是使用delete和insert来实现的,因而其效率并不高

使用insert ...on duplicate key update

如果存在同样主键值的记录,而其它列与现在存指定的记录有所不同,就更新该记录,如果记录不存在就插入该记录,如果记录存在而且没有任何值发生改变

就不做任何操作,优于replace

mysql> desca;+-------+----------+------+-----+---------+----------------+

+-------+----------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| sid | int(11) | YES | | NULL | |

+-------+----------+------+-----+---------+----------------+

mysql> select * froma;+----+-----+------+

| id | sid | type |

+----+-----+------+

| 1 | 11 | aa |

| 2 | 1 | b |

| 3 | 2 | c |

| 4 | 3 | d |

+----+-----+------+

mysql> insert into a(`id`,`type`) values(1,'a1');1062 - Duplicate entry '1' for key 'PRIMARY'mysql> insert into a(`id`,`type`) values(1,'a1') on duplicate key update type='a1';

Query OK,2rows affected

注意改变的是2行

mysql> insert into a(`id`,`type`) values(5,'a5') on duplicate key update type='a5';

Query OK,1row affected

mysql> select * froma;+----+------+------+

| id | sid | type |

+----+------+------+

| 1 | 11 | a1 |

| 2 | 1 | b |

| 3 | 2 | c |

| 4 | 3 | d |

| 5 | NULL | a5 |

+----+------+------+

11.优化group by 在sakila

explain select actor.first_name,actor.last_name,count(*) fromfilm_actorINNER JOIN actor USING(actor_id) GROUP BYfilm_actor.actor_id;

explainSELECT actor.first_name,actor.last_name,c.cnt from actor INNER JOIN(SELECT actor_id ,count(actor_id) as cnt from film_actor GROUP BYactor_id)as c USING(actor_id);

小表全表扫描效率更高

主键可以与外键构成参照完整性约束,防止数据不一致,唯一索引不行

覆盖索引

复合索引前缀规则

like %不能在前面

column is null可以使用索引

如果mysql估计使用索引比全表扫描慢,会放弃使用索引 (比如100条数据,查 where id >1 and id <100)

如果or前的的条件的列有索引,后面的没有,索引都不会用到 (where a=1 or b=2 a有索引,b没有,则都不会用到索引)

列类型是字符串类型,查询时一定要给值加引号,否则索引会失效 (name varchar(10) 存个100 where name=100 会索引失效)

关联更新

tb1 (id,sex,par,c1,c2)

tb2 (id ,age,c1,c2)update A,B set tb1.c1=tb2.c1,tb1.c2=tb2.c2 where tb1.id=tb2.id and tb2.age>50

update tb1 inner join tb2 on tb1.id=tb2.idset tb1.c1=tb2.c1,tb1.c2=tb2.c2where tb2.age>50

show status

返回一些计数器,show global status查看服务器级别的所有计数

show processlist

观察是否有大量的线程处于不正常状态

mysql>show processlist;+----+------+-----------------+------+---------+------+----------+------------------+

+----+------+-----------------+------+---------+------+----------+------------------+

| 3 | root | localhost:50044 | yii2 | Sleep | 1019 | | NULL |

+----+------+-----------------+------+---------+------+----------+------------------+

其它需要注意的小细节

范式修改

优化长难的查询语句

Mysql内部每秒可扫描内存中上百万行数据,相比之下,相应数据给客户端就要慢得多

使用尽可能少的查询

有时将一个大查询分解为多个小的查询时有必要的(方便缓存)

切分查询

将一个大查询分解为多个小的相同查询

一次性删除10000万的数据比一次删除1万暂停一会的方案更加损耗服务器开销

分解关联查询

将一条关联语句分解成多条sql语句来执行

让缓存效率更高

执行单个查询可以减少锁的竞争

在应用层做关联查询可以更容易对数据库进行拆分

优化特定类型查询语句

count(*) 会忽略所有列,直接统计所有列数,因此不要使用count(列名)

在myisam中,没有任何where条件的count(*)非常快

有where的话就不一定比其它的引擎快

可以使用explain查询近似值,用近似值代替count(*)

增加汇总表,缓存

优化关联查询

确定on或者using子句列上有索引

确保group by 和order by中只有一个表中的列,mysql才可能使用到索引

使用标识列更快

优化子查询

使用关联查询替代

优化group by和distinct

如果不需要order by进行group by时使用order by null,mysql不再进行文件排序

with rollup超级聚合,可以挪到应用程序处理

优化limit分页(加条件比如 id>上次最后一个id)

优化union

union all效率高于union

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/weixin_40725706/article/detail/381379