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型別引數有什么不一樣...

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/85980.html

標籤：MySQL

上一篇：MySQL快速回顧：簡單查詢操作

下一篇：MySQL快速回顧：高級查詢操作