-- 创建user_name列上的索引
mysql>createindex idx_user_name on users(user_name);-- 显示添加主键id创建索引
mysql>createindex idx_user_name_id on users(user_name,id);-- 对比两个索引的统计数据
mysql>select a.space as tbl_spaceid, a.table_id, a.name as table_name, row_format, space_type, b.index_id , b.name as index_name, n_fields, page_no, b.typeas index_type from information_schema.INNODB_TABLES a leftjoin information_schema.INNODB_INDEXES b on a.table_id =b.table_id where a.name ='test/users';+-------------+----------+------------+------------+------------+----------+------------------+----------+------| tbl_spaceid | table_id | table_name | row_format | space_type | index_id | index_name | n_fields | page_no | index_type |+-------------+----------+------------+------------+------------+----------+------------------+----------+------|518|1586| test/users | Dynamic | Single |1254|PRIMARY|9|4|3||518|1586| test/users | Dynamic | Single |4003| idx_user_name |2|5|0||518|1586| test/users | Dynamic | Single |4004| idx_user_name_id |2|45|0|
mysql>select index_name, last_update, stat_name, stat_value, stat_description from mysql.innodb_index_stats where index_name in('idx_user_name','idx_user_name_id');+------------------+---------------------+--------------+------------+-----------------------------------+| index_name | last_update | stat_name | stat_value | stat_description |+------------------+---------------------+--------------+------------+-----------------------------------+ | idx_user_name |2021-01-0217:14:48| n_leaf_pages |1358| Number of leaf pages in the index|| idx_user_name |2021-01-0217:14:48| size |1572| Number of pages in the index|| idx_user_name_id |2021-01-0217:14:48| n_leaf_pages |1358| Number of leaf pages in the index|| idx_user_name_id |2021-01-0217:14:48| size |1572| Number of pages in the index|
表trade_info上有索引idx_status_create_time(status,create_time),通过上面分析知道,等价于索引**(status,create_time,id)**,对于典型的分页limit m, n来说,越往后翻页越慢,也就是m越大会越慢,因为要定位m位置需要扫描的数据越来越多,导致IO开销比较大,这里可以利用辅助索引的覆盖扫描来进行优化,先获取id,这一步就是索引覆盖扫描,不需要回表,然后通过id跟原表trade_info进行关联,改写后的SQL如下:
select*from trade_info a ,(select id from trade_info wherestatus=0and create_time >='2020-10-01 00:00:00'and create_time <='2020-10-07 23:59:59'orderby id desclimit102120,20)as b //这一步走的是索引覆盖扫描,不需要回表where a.id = b.id;
在Oracle里更新500w数据是很快,因为可以利用多个cpu core去执行,但是MySQL就需要注意了,一个SQL只能使用一个cpu core去处理,如果SQL很复杂或执行很慢,就会阻塞后面的SQL请求,造成活动连接数暴增,MySQL CPU 100%,相应的接口Timeout,同时对于主从复制架构,而且做了业务读写分离,更新500w数据需要5分钟,Master上执行了5分钟,binlog传到了slave也需要执行5分钟,那就是Slave延迟5分钟,在这期间会造成业务脏数据,比如重复下单等。
mysql>explainselectmin(id) min_id,max(id) max_id from coupons wherestatus=0and create_time >='2020-10-01 00:00:00'and create_time <='2020-10-07 23:59:59';+----+-------------+-------+------------+-------+------------------------+------------------------+---------+---| id | select_type |table| partitions |type| possible_keys |key| key_len | ref |rows| filtered | Extra |+----+-------------+-------+------------+-------+------------------------+------------------------+---------+---|1|SIMPLE| users |NULL| range | idx_status_create_time | idx_status_create_time |6|NULL|180300|100.00|Usingwhere;Usingindex|
1
2
3
4
5
1
2
3
4
5
Extra=Using where; Using index使用了索引idx_status_create_time,同时需要的数据都在索引中能找到,所以不需要回表查询数据。
以每次1000条commit一次进行循环update,主要代码如下:
current_id = min_id;for current_id < max_id do
update coupons set status =1 where id >=current_id and id <= current_id +1000;//通过主键id更新1000条很快
commit;
current_id +=1000;
done