orders 表150万数据
包含三个索引
(root@localhost) [dbt3]> show create table orders\G
*************************** 1. row ***************************
Table: orders
Create Table: CREATE TABLE `orders` (
`o_orderkey` int(11) NOT NULL,
`o_custkey` int(11) DEFAULT NULL,
`o_orderstatus` char(1) DEFAULT NULL,
`o_totalprice` double DEFAULT NULL,
`o_orderDATE` date DEFAULT NULL,
`o_orderpriority` char(15) DEFAULT NULL,
`o_clerk` char(15) DEFAULT NULL,
`o_shippriority` int(11) DEFAULT NULL,
`o_comment` varchar(79) DEFAULT NULL,
PRIMARY KEY (`o_orderkey`),
KEY `i_o_orderdate` (`o_orderDATE`),
KEY `i_o_custkey` (`o_custkey`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`o_custkey`) REFERENCES `customer` (`c_custkey`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
(root@localhost) [dbt3]>
使用索引查询数据，还是很快 150万里查询一条数据
(root@localhost) [dbt3]> select * from orders where o_orderkey =1;
+------------+-----------+---------------+--------------+-------------+-----------------+-----------------+----------------+--------------------------------------------------------------------+
| o_orderkey | o_custkey | o_orderstatus | o_totalprice | o_orderDATE | o_orderpriority | o_clerk | o_shippriority | o_comment |
+------------+-----------+---------------+--------------+-------------+-----------------+-----------------+----------------+--------------------------------------------------------------------+
| 1 | 36901 | O | 173665.47 | 1996-01-02 | 5-LOW | Clerk#000000951 | 0 | blithely final dolphins solve-- blithely blithe packages nag blith |
+------------+-----------+---------------+--------------+-------------+-----------------+-----------------+----------------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
看执行计划
(root@localhost) [dbt3]> explain select * from orders where o_orderkey =1;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | orders | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
使用到的索引是PRIMARY 主键
为一张150万条记录的表添加索引用了4秒钟。不同服务器也不同
(root@localhost) [sys]> select * from statement_analysis;
*************************** 7. row ***************************
query: EXPLAIN SELECT * FROM `orders` WHERE `o_orderDATE` = ?
db: dbt3 #数据库
full_scan:
exec_count: 8 #执行的次数
err_count: 0
warn_count: 8
total_latency: 1.64 ms 所有的执行时间
max_latency: 225.51 us 最大执行时间
avg_latency: 205.06 us 平均执行时间
lock_latency: 583.00 us
rows_sent: 8
rows_sent_avg: 1
rows_examined: 0
rows_examined_avg: 0
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 0
tmp_disk_tables: 0
rows_sorted: 0
sort_merge_passes: 0
digest: 60ad6d9ac5e484874c5c27b898b1a6ae
first_seen: 2019-11-15 20:38:24
last_seen: 2019-11-15 20:38:39
（a,b,c）对a 排序，对ab 排序， abc排序，，其他不排序
