mysql索引的最左前缀原则
来源:互联网 发布:网络虚拟手机号申请 编辑:程序博客网 时间:2024/05/21 13:54
联合索引有一个最左前缀原则,所以建立联合索引的时候,这个联合索引的字段顺序非常重要
下面写了例子说明这个:
CREATE TABLE `test_myisam` ( `id` int(11) NOT NULL AUTO_INCREMENT, `conference_id` varchar(200) NOT NULL, `account` varchar(100) DEFAULT NULL, `status` int(2) DEFAULT NULL COMMENT '0:invite, 1:cancel_invite, 2:decline, 3:connect', `duration` bigint(20) unsigned DEFAULT NULL, `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=myisam AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
以上表结构,我想通过三列进行查询 account ,status,create_time进行查询统计。
如何建立索引?
因为我们有可能按照acccount单独统计,或者按照account status,或者是account,status,create_time进行统计,如何建立索引???
下面是建立索引前后的对比600万数据
如何生成:执行如下脚本,account和日期不同还有status不同,分别生成一百万。
CREATE PROCEDURE `add_data_myisam_cp_27`() begin declare v_rows int(10) default 1000000; declare v_count int(10) default 0; id_loop:LOOP insert into test_myisam values(null,round(rand()*1000000000),'cloudp',round(rand()*3),round(rand()*100000),'2016-07-27 00:00:22'); set v_count= v_count + 1; if v_count>v_rows then leave id_loop; end if; end loop id_loop; end;
测试结果利用建立的索引性能提高了三倍:
MariaDB [prf]> select count(1) from test_myisam where account='cloudp' and status =3 and date(create_time)='2016-07-27';+----------+| count(1) |+----------+| 167400 |+----------+1 row in set (1.28 sec)MariaDB [prf]> create index as_index on test_myisam(account,status,create_time);Query OK, 6000006 rows affected (31.60 sec)Records: 6000006 Duplicates: 0 Warnings: 0MariaDB [prf]> select count(1) from test_myisam where account='cloudp' and status =3 and date(create_time)='2016-07-27';+----------+| count(1) |+----------+| 167400 |+----------+1 row in set (0.42 sec)MariaDB [prf]> explain select count(1) from test_myisam where account='cloudp' and status =3 and date(create_time)='2016-07-27';+------+-------------+-------------+------+---------------+----------+---------+-------------+--------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+-------------+------+---------------+----------+---------+-------------+--------+--------------------------+| 1 | SIMPLE | test_myisam | ref | as_index | as_index | 308 | const,const | 520216 | Using where; Using index |+------+-------------+-------------+------+---------------+----------+---------+-------------+--------+--------------------------+1 row in set (0.00 sec)
从1.28秒下降到0.42秒
但是这个date(create_time)会对每一列都会转换后对比,这里会比较消耗性能;
如何利用上索引??
修改为:
MariaDB [prf]> explain select count(1) from test_myisam where account='cloudp' and status =3 and date(create_time)='2016-07-27';+------+-------------+-------------+------+---------------+----------+---------+-------------+--------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+-------------+------+---------------+----------+---------+-------------+--------+--------------------------+| 1 | SIMPLE | test_myisam | ref | as_index | as_index | 308 | const,const | 520216 | Using where; Using index |+------+-------------+-------------+------+---------------+----------+---------+-------------+--------+--------------------------+1 row in set (0.00 sec)MariaDB [prf]> select count(1) from test_myisam where account='cloudp' and status =3 and create_time between '2016-07-27' and '2016-07-28';+----------+| count(1) |+----------+| 167400 |+----------+1 row in set (0.15 sec)MariaDB [prf]> explain select count(1) from test_myisam where account='cloudp' and status =3 and create_time between '2016-07-27' and '2016-07-28';+------+-------------+-------------+-------+---------------+----------+---------+------+--------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+-------------+-------+---------------+----------+---------+------+--------+--------------------------+| 1 | SIMPLE | test_myisam | range | as_index | as_index | 312 | NULL | 174152 | Using where; Using index |+------+-------------+-------------+-------+---------------+----------+---------+------+--------+--------------------------+1 row in set (0.00 sec)
如上效率又提高了三倍,是因为扫描的数据行数减少了,最后一个create_time如果不用索引需要扫描52016行,如果使用了索引扫描174152行,命中的行数为:167400行,命中率非常高了。
这里有个疑问:
如果按照天进行统计,create_time作为联合索引的第一列,如何使用上这个索引呢????
至今没有想清楚,如果这一列是date类型可以直接用上索引,如果在oracle中可以date(create_time)建立函数式索引。但是mysql貌似不支持函数式索引。
一个解决方式是: create_time定义为 date类型,在每一列存入的时候,通过触发器自动把这一行修改为date类型。
如果有好的注意欢迎留言探讨,目前没有好的方式加上create_time,可以从业务上解决,就是每天的统计计算完成以后,直接把数据推到历史表中,统计结果单独存放。
- mysql索引的最左前缀原则
- MySQL 的索引和最左前缀原则
- mysql正确建立索引以及最左前缀原则
- MySql最左前缀原则
- MySql最左前缀原则
- MySql最左前缀原则
- 索引——最左前缀原则
- 最左前缀原则
- mysql组合索引最左前缀
- 正确建立索引以及最左前缀原则
- 正确建立索引以及最左前缀原则
- mysql索引最左匹配原则的理解
- mysql索引最左匹配原则的理解?
- mysql索引最左匹配原则的理解?
- MySQL 联合索引最左匹配原则
- MySQL联合索引最左原则
- mysql 索引 大于等于 走不走索引 最左前缀
- 【mysql】mysql查询优化之索引类型、最左前缀
- 12个免费的 Twitter Bootstrap 后台模板
- Android开发之调用相机拍照和调用系统相册
- 针对本科和硕士应届生的算法面试题
- linux进程总结
- Android逆向修改工具简单讲解-Androidkiller-Reflector
- mysql索引的最左前缀原则
- 微信支付—关于sgin签名的坑
- Cpp环境【NOIP practice】【BSOJ1878】【Vijos1172】 山头狙击战
- 关于编写html代码那些事
- Java千百问_02基本使用(014)_mac系统如何管理环境变量
- mysql timestamp 和 date比较
- HDOJ1242Rescue(BFS+优先队列)
- n位数中取m位的组合
- javaScript 时间日期对比