最近困扰自己很久的膝盖积液手术终于做完,在家养伤,逛技术博客看到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 '',
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='';
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='' ;
+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| 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='' |
| 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类型参数有什么不一样...

最新文章

  1. JavaScript动画-磁性吸附
  2. 关于 Java(TM) Platform SE binary 已停止工作 的解决方法
  3. mongo学习笔记(一):增删改查
  4. UVA 10564 Paths through the Hourglass[DP 打印]
  5. iOS改变字母的大小写
  6. [读书笔记]java中的volatile关键词
  7. 遥感影像滤波处理软件 — timesat3.2
  8. POJ1384Piggy-Bank[完全背包]
  9. Android开发——自动生成Android屏幕适配的dimens.xml文件
  10. getline和get的区别
  11. Ajax是什么(转)
  12. Snapman设计中的思考
  13. 关于Websockets问题:
  14. bzoj4034[HAOI2015]树上操作 树链剖分+线段树
  15. EtherCAT主站对PHY有要求?
  16. 深入浅出 JavaScript 关键词 -- this
  17. DNN模型训练词向量原理
  18. 4.4 C++虚析构函数
  19. 037 SparkSQL ThriftServer服务的使用和程序中JDBC的连接
  20. WebClient请求帮助类

热门文章

  1. Android Button点击效果(按钮背景变色、文字变色)
  2. Python--day67--Django的路由系统
  3. 移动端遇到的bug (长期更新)
  4. java 菜单
  5. 9月29更新美版T-mobile版本iPhone7代和7P有锁机卡贴解锁方法
  6. 实体Bean
  7. eslint的使用和配置
  8. Priest John's Busiest Day (2-sat)
  9. poj2826 An Easy Problem?!(计算几何)
  10. 【2016常州一中夏令营Day7】