mysql索引创建和使用细节(一)
最近困扰自己很久的膝盖积液手术终于做完，在家养伤，逛技术博客看到easyswoole开发组成员仙士可博客有关mysql索引方面的知识，自己打算重温下。
正常业务起步数据表数据量较少，不用考虑使用索引，当后期累积的数据数量非常可观时，使用索引是提升查询的一条途径，其他的像表分区，分库分表等等。
【索引创建】
索引的创建需要考虑被创建索引的字段区分度，比如一张表里面有渠道channel，渠道可期种类不超过3种，win系，安卓系，iOS系，而数据表数据量有一百万，平均下来每个渠道各是1/3也就是33万数据，这样的数据量就是否基于channel 索引区别都不会太大。
但是如果基于date字段做索引，如20200114，一年一百万，除以365天，平均下来每天300条数据。这个区分度是相当大。
同样的索引使用 33w数据查询显然效率低于300条数据。
索引可以加快mysql服务查询速度，但不是索引越多越好，因为insert或update的同时存放索引的文件也需要进行更新，会影响数据插入更新的速度，如果对数据实时性有要求的，无疑会受较大影响。
这里挑两种情况演示给大家看下。
【索引失效】
一. 单字段索引：字段是string类型，传入int类型参数。
MySQL [test_db]> show create table test_users\G; *************************** 1. row *************************** Table: test_users Create Table: CREATE TABLE `test_users` ( `uid` int(11) unsigned NOT NULL AUTO_INCREMENT, `username` char(15) NOT NULL, `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `user_id` char(11) NOT NULL DEFAULT \'0\', PRIMARY KEY (`uid`), KEY `testindex` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1306001 DEFAULT CHARSET=utf8mb4 1 row in set (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 where user_id=\'443587\'; Empty set (0.04 sec) MySQL [test_db]> select * from test_users where user_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 where user_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 in set, 1 warning (0.05 sec) MySQL [test_db]> explain select * from test_users where user_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 in set, 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 where user_id=\'443587\' | | 2 | 0.10183000 | select * from test_users where user_id=97737 | +----------+------------+-------------------------------------------------+ 2 rows in set, 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 in set, 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 in set, 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 of disk access (reads), it is often the longest-running state over the lifetime of a given query. #大意即是：线程正在为一个select语句读取和处理行，并且发送数据到客户端。因为这期间操作倾向于大量的磁盘访问（读取），所以这常是整个查询周期中运行时间最长的阶段。
未完待续，下一篇讲int类型，传入string类型参数有什么不一样…
版权声明：本文为wscsq789原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。