MySQL不建议delete删除数据的原因是什么?(四)

开课吧开课吧锤锤2021-03-08 09:57

    Java编程语言是一种简单、面向对象、分布式、解释型、健壮安全、与系统无关、可移植、高性能、多线程和动态的语言。如今Java已经广泛应用于各个领域的编程开发。

Java

    delete对SQL的影响

    未删除前的SQL执行情况

    #插入100W数据

    mysql>callinsert_user_data(1000000);

    QueryOK,0rowsaffected(35.99sec)

    #添加相关索引

    mysql>altertableuseraddindexidx_name(name),addindexidx_phone(phone);

    QueryOK,0rowsaffected(6.00sec)

    Records:0Duplicates:0Warnings:0

    #表上索引统计信息

    mysql>showindexfromuser;

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

    |Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality| Sub_part|Packed|Null|Index_type|Comment|Index_comment|

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

    |user|0|PRIMARY|1|id|A|996757|NULL|NULL||BTREE|||

    |user|1|idx_name|1|name|A|996757|NULL|NULL||BTREE|||

    |user|1|idx_phone|1|phone|A|2|NULL|NULL||BTREE|||

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

    3rowsinset(0.00sec)

    #重置状态变量计数

    mysql>flushstatus;

    QueryOK,0rowsaffected(0.00sec)

    #执行SQL语句

    mysql>selectid,age,phonefromuserwherenamelike'lyn12%';

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

    |id|age|phone|

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

    |124|3|15240540354|

    |1231|30|15240540354|

    |12301|60|15240540354|

    .............................

    |129998|37|15240540354|

    |129999|38|15240540354|

    |130000|39|15240540354|

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

    11111rowsinset(0.03sec)

    mysql>explainselectid,age,phonefromuserwherenamelike'lyn12%';

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

    |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|

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

    |1|SIMPLE|user|range|idx_name|idx_name|82|NULL|22226|Usingindexcondition|

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

    1rowinset(0.00sec)

    #查看相关状态呢变量

    mysql>select*frominformation_schema.session_statuswherevariable_namein ('Last_query_cost','Handler_read_next','Innodb_pages_read','Innodb_data_reads','Innodb_pages_read');

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

    |VARIABLE_NAME|VARIABLE_VALUE|

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

    |HANDLER_READ_NEXT|11111|#请求读的行数

    |INNODB_DATA_READS|7868409|#数据物理读的总数

    |INNODB_PAGES_READ|7855239|#逻辑读的总数

    |LAST_QUERY_COST|10.499000|#SQL语句的成本COST,主要包括IO_COST和CPU_COST。

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

    4rowsinset(0.00sec)

    删除后的SQL执行情况

    #删除50w数据

    mysql>deletefromuserlimit500000;

    QueryOK,500000rowsaffected(3.70sec)

    #分析表统计信息

    mysql>analyzetableuser;

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

    |Table|Op|Msg_type|Msg_text|

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

    |test.user|analyze|status|OK|

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

    1rowinset(0.01sec)

    #重置状态变量计数

    mysql>flushstatus;

    QueryOK,0rowsaffected(0.01sec)

    mysql>selectid,age,phonefromuserwherenamelike'lyn12%';

    Emptyset(0.05sec)

    mysql>explainselectid,age,phonefromuserwherenamelike'lyn12%';

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

    |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|

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

    |1|SIMPLE|user|range|idx_name|idx_name|82|NULL|22226|Usingindexcondition|

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

    1rowinset(0.00sec)

    mysql>select*frominformation_schema.session_statuswherevariable_namein ('Last_query_cost','Handler_read_next','Innodb_pages_read','Innodb_data_reads','Innodb_pages_read');

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

    |VARIABLE_NAME|VARIABLE_VALUE|

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

    |HANDLER_READ_NEXT|0|

    |INNODB_DATA_READS|7868409|

    |INNODB_PAGES_READ|7855239|

    |LAST_QUERY_COST|10.499000|

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

    4rowsinset(0.00sec)

    结果统计分析

操作 COST 物理读次数 逻辑读次数 扫描行数 返回行数 执行时间
初始化插入100W 10.499000 7868409 7855239 22226 11111 30ms
100W随机删除50W 10.499000 7868409 7855239 22226 0 50ms

    这也说明对普通的大表,想要通过delete数据来对表进行瘦身是不现实的,所以在任何时候不要用delete去删除数据,应该使用优雅的标记删除。

    以上内容由开课吧老师敖丙提供,更多Java教程尽在开课吧广场Java教程频道。

有用
分享