bitsCN.com
我们的项目中有好多不等于的情况。今天写这篇文章简单的分析一下怎么个优化法。
这里的分表逻辑是根据t_group表的user_name组的个数来分的。
因为这种情况单独user_name字段上的索引就属于烂索引。起不了啥名明显的效果。
1、试验PROCEDURE.
DELIMITER $$
Drop PROCEDURE `t_girl`.`sp_split_table`$$
Create PROCEDURE `t_girl`.`sp_split_table`()
BEGIN
declare done int default 0;
declare v_user_name varchar(20) default '';
declare v_table_name varchar(64) default '';
-- Get all users' name.
declare cur1 cursor for select user_name from t_group group by user_name;
-- Deal with error or warnings.
declare continue handler for 1329 set done = 1;
-- Open cursor.
open cur1;
while done 1
do
fetch cur1 into v_user_name;
if not done then
-- Get table name.
set v_table_name = concat('t_group_',v_user_name);
-- Create new extra table.
set @stmt = concat('create table ',v_table_name,' like t_group');
prepare s1 from @stmt;
execute s1;
drop prepare s1;
-- Load data into it.
set @stmt = concat('insert into ',v_table_name,' select * from t_group where user_name = ''',v_user_name,'''');
prepare s1 from @stmt;
execute s1;
drop prepare s1;
end if;
end while;
-- Close cursor.
close cur1;
-- Free variable from memory.
set @stmt = NULL;
END$$
DELIMITER ;
2、试验表。
我们用一个有一千万条记录的表来做测试。
mysql> select count(*) from t_group;
+----------+
| count(*) |
+----------+
| 10388608 |
+----------+
1 row in set (0.00 sec)
表结构。
mysql> desc t_group;
+-------------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+-------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| money | decimal(10,2) | NO | | | |
| user_name | varchar(20) | NO | MUL | | |
| create_time | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------------+------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)
索引情况。
mysql> show index from t_group;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
|Table | Non_unique | Key_name | Seq_in_index | Column_name |Collation | Cardinality | Sub_part | Packed | Null | Index_type |Comment |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
|t_group | 0 | PRIMARY | 1 | id |A | 10388608 | NULL | NULL | | BTREE | |
| t_group | 1 | idx_user_name | 1 | user_name | A | 8 | NULL | NULL | |BTREE | |
| t_group | 1 | idx_combination1| 1 | user_name | A | 8 | NULL |NULL | | BTREE | |
| t_group | 1 |idx_combination1 | 2 | money | A | 3776| NULL | NULL | | BTREE | |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)
PS:
idx_combination1 这个索引是必须的,因为要对user_name来GROUP BY。此时属于松散索引扫描!当然完了后你可以干掉她。
idx_user_name 这个索引是为了加快单独执行constant这种类型的查询。
我们要根据用户名来分表
mysql> select user_name from t_group where 1 group by user_name;
+-----------+
| user_name |
+-----------+
| david |
| leo |
| livia |
| lucy |
| sarah |
| simon |
| sony |
| sunny |
+-----------+
8 rows in set (0.00 sec)
所以结果表应该是这样的。
mysql> show tables like 't_group_%';
+------------------------------+
| Tables_in_t_girl (t_group_%) |
+------------------------------+
| t_group_david |
| t_group_leo |
| t_group_livia |
| t_group_lucy |
| t_group_sarah |
| t_group_simon |
| t_group_sony |
| t_group_sunny |
+------------------------------+
8 rows in set (0.00 sec)
3、对比结果。
mysql> select count(*) from t_group where user_name = 'david';
+----------+
| count(*) |
+----------+
| 1298576 |
+----------+
1 row in set (1.71 sec)
执行了将近2秒。
mysql> select count(*) from t_group_david;
+----------+
| count(*) |
+----------+
| 1298576 |
+----------+
1 row in set (0.00 sec)
几乎是瞬间的。
mysql> select count(*) from t_group where user_name 'david';
+----------+
| count(*) |
+----------+
| 9090032 |
+----------+
1 row in set (9.26 sec)
执行了将近10秒,可以想象,这个是实际的项目中是不能忍受的。
mysql> select (select count(*) from t_group) - (select count(*) from t_group_david) as total;
+---------+
| total |
+---------+
| 9090032 |
+---------+
1 row in set (0.00 sec)
几乎是瞬间的。
我们来看看聚集函数。
对于原表的操作。
mysql> select min(money),max(money) from t_group where user_name = 'david';
+------------+------------+
| min(money) | max(money) |
+------------+------------+
| -6.41 | 500.59 |
+------------+------------+
1 row in set (0.00 sec)
最小,最大值都是FULL INDEX SCAN。所以是瞬间的。
mysql> select sum(money),avg(money) from t_group where user_name = 'david';
+--------------+------------+
| sum(money) | avg(money) |
+--------------+------------+
| 319992383.84 | 246.417910 |
+--------------+------------+
1 row in set (2.15 sec)
其他聚集函数的结果就不是FULL INDEX SCAN了。耗时2.15秒。
对于小表的操作。
mysql> select min(money),max(money) from t_group_david;
+------------+------------+
| min(money) | max(money) |
+------------+------------+
| -6.41 | 500.59 |
+------------+------------+
1 row in set (1.50 sec)
最大最小值完全是FULL TABLE SCAN,耗时1.50秒,不划算。以此看来。
mysql> select sum(money),avg(money) from t_group_david;
+--------------+------------+
| sum(money) | avg(money) |
+--------------+------------+
| 319992383.84 | 246.417910 |
+--------------+------------+
1 row in set (1.68 sec)
取得这两个结果也是花了快2秒,快了一点。
我们来看看这个小表的结构。
mysql> desc t_group_david;
+-------------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+-------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| money | decimal(10,2) | NO | | | |
| user_name | varchar(20) | NO | MUL | | |
| create_time | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------------+------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)
明显的user_name属性是多余的。那么就干掉它。
mysql> alter table t_group_david drop user_name;
Query OK, 1298576 rows affected (7.58 sec)
Records: 1298576 Duplicates: 0 Warnings: 0
现在来重新对小表运行查询
mysql> select min(money),max(money) from t_group_david;
+------------+------------+
| min(money) | max(money) |
+------------+------------+
| -6.41 | 500.59 |
+------------+------------+
1 row in set (0.00 sec)
此时是瞬间的。
mysql> select sum(money),avg(money) from t_group_david;
+--------------+------------+
| sum(money) | avg(money) |
+--------------+------------+
| 319992383.84 | 246.417910 |
+--------------+------------+
1 row in set (0.94 sec)
这次算是控制在一秒以内了。
mysql> Aborted
小总结一下:分出的小表的属性尽量越少越好。大胆的去干吧
第零空间版权所有
bitsCN.com

InnoDB使用redologs和undologs确保数据一致性和可靠性。1.redologs记录数据页修改,确保崩溃恢复和事务持久性。2.undologs记录数据原始值,支持事务回滚和MVCC。

EXPLAIN命令的关键指标包括type、key、rows和Extra。1)type反映查询的访问类型,值越高效率越高,如const优于ALL。2)key显示使用的索引,NULL表示无索引。3)rows预估扫描行数,影响查询性能。4)Extra提供额外信息,如Usingfilesort提示需要优化。

Usingtemporary在MySQL查询中表示需要创建临时表,常见于使用DISTINCT、GROUPBY或非索引列的ORDERBY。可以通过优化索引和重写查询避免其出现,提升查询性能。具体来说,Usingtemporary出现在EXPLAIN输出中时,意味着MySQL需要创建临时表来处理查询。这通常发生在以下情况:1)使用DISTINCT或GROUPBY时进行去重或分组;2)ORDERBY包含非索引列时进行排序;3)使用复杂的子查询或联接操作。优化方法包括:1)为ORDERBY和GROUPB

MySQL/InnoDB支持四种事务隔离级别:ReadUncommitted、ReadCommitted、RepeatableRead和Serializable。1.ReadUncommitted允许读取未提交数据,可能导致脏读。2.ReadCommitted避免脏读,但可能发生不可重复读。3.RepeatableRead是默认级别,避免脏读和不可重复读,但可能发生幻读。4.Serializable避免所有并发问题,但降低并发性。选择合适的隔离级别需平衡数据一致性和性能需求。

MySQL适合Web应用和内容管理系统,因其开源、高性能和易用性而受欢迎。1)与PostgreSQL相比,MySQL在简单查询和高并发读操作上表现更好。2)相较Oracle,MySQL因开源和低成本更受中小企业青睐。3)对比MicrosoftSQLServer,MySQL更适合跨平台应用。4)与MongoDB不同,MySQL更适用于结构化数据和事务处理。

MySQL索引基数对查询性能有显着影响:1.高基数索引能更有效地缩小数据范围,提高查询效率;2.低基数索引可能导致全表扫描,降低查询性能;3.在联合索引中,应将高基数列放在前面以优化查询。

MySQL学习路径包括基础知识、核心概念、使用示例和优化技巧。1)了解表、行、列、SQL查询等基础概念。2)学习MySQL的定义、工作原理和优势。3)掌握基本CRUD操作和高级用法,如索引和存储过程。4)熟悉常见错误调试和性能优化建议,如合理使用索引和优化查询。通过这些步骤,你将全面掌握MySQL的使用和优化。

MySQL在现实世界的应用包括基础数据库设计和复杂查询优化。1)基本用法:用于存储和管理用户数据,如插入、查询、更新和删除用户信息。2)高级用法:处理复杂业务逻辑,如电子商务平台的订单和库存管理。3)性能优化:通过合理使用索引、分区表和查询缓存来提升性能。


热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

AI Hentai Generator
免费生成ai无尽的。

热门文章

热工具

Dreamweaver CS6
视觉化网页开发工具

安全考试浏览器
Safe Exam Browser是一个安全的浏览器环境,用于安全地进行在线考试。该软件将任何计算机变成一个安全的工作站。它控制对任何实用工具的访问,并防止学生使用未经授权的资源。

SublimeText3 Linux新版
SublimeText3 Linux最新版

螳螂BT
Mantis是一个易于部署的基于Web的缺陷跟踪工具,用于帮助产品缺陷跟踪。它需要PHP、MySQL和一个Web服务器。请查看我们的演示和托管服务。

WebStorm Mac版
好用的JavaScript开发工具