mysql索引创建和使用细节(一)

  • Z时代
  • 2024-01-10
  • 分类：综合

database

最近困扰自己很久的膝盖积液手术终于做完，在家养伤，逛技术博客看到easyswoole开发组成员仙士可博客有关mysql索引" title="mysql索引">mysql索引方面的知识，自己打算重温下。

正常业务起步数据表数据量较少，不用考虑使用索引，当后期累积的数据数量非常可观时，使用索引是提升查询的一条途径，其他的像表分区，分库分表等等。

 

【索引创建】

索引的创建需要考虑被创建索引的字段区分度，比如一张表里面有渠道channel，渠道可期种类不超过3种，win系，安卓系，iOS系，而数据表数据量有一百万，平均下来每个渠道各是1/3也就是33万数据，这样的数据量就是否基于channel 索引区别都不会太大。

但是如果基于date字段做索引，如20200114，一年一百万，除以365天，平均下来每天300条数据。这个区分度是相当大。

同样的索引使用 33w数据查询显然效率低于300条数据。

索引可以加快mysql服务查询速度，但不是索引越多越好，因为insert或update的同时存放索引的文件也需要进行更新，会影响数据插入更新的速度，如果对数据实时性有要求的，无疑会受较大影响。

 

【索引失效】

一. 单字段索引：字段是string类型，传入int类型参数。

MySQL [test_db]> show createtable test_usersG;
***************************1. row ***************************
Table: test_users
CreateTable: CREATETABLE `test_users` (
  `uid` int(11) unsigned NOTNULL AUTO_INCREMENT,
  `username` char(15) NOTNULL,
  `created_time` timestampNOTNULLDEFAULTCURRENT_TIMESTAMP,
  `user_id` char(11) NOTNULLDEFAULT"0",
PRIMARYKEY (`uid`),
KEY `testindex` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1306001DEFAULT CHARSET=utf8mb4
1 row inset (0.04 sec)
ERROR: No query specified
#开启profile
MySQL [test_db]>set profiling=1;
Query OK, 0 rows affected, 1 warning (0.03 sec)
#开始查询
MySQL [test_db]>select*from test_users whereuser_id="443587";
Empty set (0.04 sec)
MySQL [test_db]>select*from test_users whereuser_id=97737;
Empty set (0.14 sec)
#关闭profile
MySQL [test_db]>set profiling=0;
Query OK, 0 rows affected, 1 warning (0.03 sec)
#explain查看一下
MySQL [test_db]> explain select*from test_users whereuser_id="443587" ;
+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type |table| partitions | type | possible_keys |key| key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|1| SIMPLE      | test_users |NULL| ref  | testindex     | testindex |44| const |1|100.00|NULL|
+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row inset, 1 warning (0.05 sec)
MySQL [test_db]> explain select*from test_users whereuser_id=97737;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type |table| partitions | type | possible_keys |key| key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|1| SIMPLE      | test_users |NULL|ALL| testindex     |NULL|NULL|NULL|306078|10.00| Using where|
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row inset, 3 warnings (0.04 sec)
#以上可见当使用user_id匹配int类型时，key=null，索引失效
#再看profile分析结果，可见加单引号比起不加单引号快上10倍左右
MySQL [test_db]> show profiles;
+----------+------------+-------------------------------------------------+
| Query_ID | Duration   | Query                                           |
+----------+------------+-------------------------------------------------+
|1|0.01234100|select*from test_users whereuser_id="443587"|
|2|0.10183000|select*from test_users whereuser_id=97737|
+----------+------------+-------------------------------------------------+
2 rows inset, 1 warning (0.04 sec)
#再看更详细的分析
MySQL [test_db]> show profile cpu,block io,swaps for query 1;
+----------------------+----------+----------+------------+--------------+---------------+-------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+----------------------+----------+----------+------------+--------------+---------------+-------+
| starting             |0.000088|0.000000|0.000000|0|0|0|
| checking permissions|0.000006|0.000000|0.000000|0|0|0|
| Opening tables       |0.000021|0.000000|0.000000|0|0|0|
| init                 |0.003386|0.001000|0.000000|240|0|0|
| System lock          |0.000027|0.000000|0.000000|0|0|0|
| optimizing           |0.000011|0.000000|0.000000|0|0|0|
|statistics|0.007039|0.000000|0.000000|592|0|0|
| preparing            |0.000023|0.000000|0.000000|0|0|0|
| executing            |0.000003|0.000000|0.000000|0|0|0|
| Sending data         |0.001661|0.000000|0.000000|176|0|0|
|end|0.000008|0.000000|0.000000|0|0|0|
| query end|0.000011|0.000000|0.000000|0|0|0|
| closing tables       |0.000012|0.000000|0.000000|0|0|0|
| freeing items        |0.000044|0.000000|0.000000|0|0|0|
| cleaning up          |0.000003|0.000000|0.000000|0|0|0|
+----------------------+----------+----------+------------+--------------+---------------+-------+
15 rows inset, 1 warning (0.03 sec)
MySQL [test_db]> show profile cpu,block io,swaps for query 2;
+----------------------+----------+----------+------------+--------------+---------------+-------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+----------------------+----------+----------+------------+--------------+---------------+-------+
| starting             |0.000081|0.000000|0.000000|0|0|0|
| checking permissions|0.000006|0.000000|0.000000|0|0|0|
| Opening tables       |0.000022|0.000000|0.000000|0|0|0|
| init                 |0.002129|0.000000|0.000000|72|0|0|
| System lock          |0.000010|0.000000|0.000000|0|0|0|
| optimizing           |0.000009|0.000000|0.000000|0|0|0|
|statistics|0.000028|0.000000|0.000000|0|0|0|
| preparing            |0.000014|0.000000|0.000000|0|0|0|
| executing            |0.000002|0.000000|0.000000|0|0|0|
| Sending data         |0.099419|0.092986|0.000000|400|0|0|
|end|0.000016|0.000000|0.000000|0|0|0|
| query end|0.000012|0.000000|0.000000|0|0|0|
| closing tables       |0.000026|0.001000|0.000000|0|0|0|
| freeing items        |0.000054|0.000000|0.000000|0|0|0|
| cleaning up          |0.000003|0.000000|0.000000|0|0|0|
+----------------------+----------+----------+------------+--------------+---------------+-------+
15 rows inset, 1 warning (0.04 sec)
#通过对比可以发现主要耗时在sending data，而其他地方相差不大
#mysql官网对sending data对解释
#Sending data：The thread is reading and processing rows for a SELECT statement, and sending data to the client. 
#Because operations occurring during this state tend to perform large amounts ofdisk access (reads), it is often the longest-running state over the lifetime of a given query.
#大意即是：线程正在为一个select语句读取和处理行，并且发送数据到客户端。因为这期间操作倾向于大量的磁盘访问（读取），所以这常是整个查询周期中运行时间最长的阶段。 

未完待续，下一篇讲int类型，传入string类型参数有什么不一样...

 

 

     

以上是 mysql索引创建和使用细节(一) 的全部内容， 来源链接： utcz.com/z/532052.html

回到顶部