当前位置: 澳门新濠3559 > 数据库 > 正文

很简单的三个表,相关链接

时间:2019-11-09 00:17来源:数据库
很简单的三个表: MYSQL入门学习之八:数据库及表的基本操作 p248_user记录用户信息   CREATE TABLE `p248_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `list_ids` varchar(4000) NOT NULL DEFAULT '', `email` varchar(

很简单的三个表:

MYSQL入门学习之八:数据库及表的基本操作

p248_user记录用户信息

 

CREATE TABLE `p248_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`list_ids` varchar(4000) NOT NULL DEFAULT '',
`email` varchar(255) NOT NULL,
`mobile` varchar(20) NOT NULL,
`_created` datetime NOT NULL,
`_updated` datetime NOT NULL,
`hb_status` tinyint(4) DEFAULT '0',
`sb_status` tinyint(4) DEFAULT '0',
`unsubscribe_email_status` tinyint(4) DEFAULT '0',
`unsubscribe_sms_status` tinyint(4) DEFAULT '0',
`hb_time` datetime DEFAULT NULL,
`unsubscribe_email_time` datetime DEFAULT NULL,
`unsubscribe_sms_time` datetime DEFAULT NULL,
`_create_operator_name` varchar(100) DEFAULT NULL,
`_update_operator_name` varchar(100) DEFAULT NULL,
`_create_operator_email` varchar(100) DEFAULT NULL,
`_update_operator_email` varchar(100) DEFAULT NULL,
`name` varchar(255) NOT NULL DEFAULT '',
`time` varchar(255) NOT NULL DEFAULT '',
`year` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `u1` (`email`,`mobile`) USING BTREE,
KEY `_updated` (`_updated`),
KEY `mobile` (`mobile`)
) ENGINE=InnoDB AUTO_INCREMENT=5596286 DEFAULT CHARSET=utf8

相关链接:

p248_list记录组信息

MYSQL入门学习之一:基本操作

CREATE TABLE `p248_list` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`status` enum('active','delete') DEFAULT 'active',
`_created` datetime NOT NULL,
`_updated` datetime NOT NULL,
`user_count` int(11) DEFAULT '0',
`lock_status` int(11) NOT NULL DEFAULT '0',
`lock_reason` varchar(100) DEFAULT NULL,
`lock_time` datetime DEFAULT NULL,
`import_percent` int(11) DEFAULT NULL,
`hb_count` int(11) DEFAULT '0',
`sb_count` int(11) DEFAULT '0',
`unsubscribe_email_count` int(11) DEFAULT '0',
`unsubscribe_sms_count` int(11) DEFAULT '0',
`_create_operator_name` varchar(100) DEFAULT NULL,
`_update_operator_name` varchar(100) DEFAULT NULL,
`_create_operator_email` varchar(100) DEFAULT NULL,
`_update_operator_email` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `_updated` (`_updated`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8

p248_user_list是个多对多的表,记录用户属于哪些组

MYSQL入门学习之二:使用正则表达式搜索

CREATE TABLE `p248_user_list` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`list_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_list_id` (`user_id`,`list_id`),
KEY `list_id` (`list_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5646298 DEFAULT CHARSET=utf8

 

MYSQL入门学习之三:全文本搜索

 

p248_user有200万条记录, p248_user_list有1000万条记录。

MYSQL入门学习之四:MYSQL的数据类型

 

现在要找出属于29分组,并且手机号码不为空,并且没有退订的用户。这样的用户大约有100万个。现在要把这些用户按照4000个一批放到一群临时的记录集里。

MYSQL入门学习之五:MYSQL的字符集

 

这个要用到分页了,一开始的想法:

MYSQL入门学习之六:MYSQL的运算符

第一页:

SELECT `id`, `email`, `mobile`, `_created`, `_updated`, `_create_operator_name`, `_update_operator_name`, `name`, `time`, `year` FROM `p248_user` WHERE 1 = 1 AND id IN (SELECT DISTINCT user_id FROM `p248_user_list` WHERE list_id IN (29)) AND unsubscribe_sms_status = 0 AND mobile <> '' LIMIT 0, 4000;

MYSQL入门学习之七:MYSQL常用函数

第二页就LIMIT 4000, 4000。第三页就LIMIT 8000, 4000。依次类推。

结果这个SQL查询耗时用了整整5秒。

 

 

一、操作数据库  www.2cto.com  

分析一下这个查询:

 1、查看数据库

mysql> explain SELECT `id`, `email`, `mobile`, `_created`, `_updated`, `_create_operator_name`, `_update_operator_name`, `name`, `time`, `year` FROM `p248_user` WHERE 1 = 1 AND id IN (SELECT DISTINCT user_id FROM `p248_user_list` WHERE list_id IN (29)) AND unsubscribe_sms_status = 0 AND mobile <> '' LIMIT 0, 4000;
+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+--------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+--------+------------------------------------+
| 1 | SIMPLE | p248_user | range | PRIMARY,mobile | mobile | 62 | NULL | 934446 | Using index condition; Using where |
| 1 | SIMPLE | p248_user_list | eq_ref | user_list_id,list_id | user_list_id | 8 | contacts.p248_user.id,const | 1 | Using index |
+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+--------+------------------------------------+
2 rows in set (0.00 sec)

         show databases [ like ''];

可以看到用户表扫描了93万行,几乎是全表扫描了。也就是把所有符合条件的结果都取了出来然后再取前4000条。

         示例:

 

         mysql> show databases;

 

         +--------------------+

把上面的查询加上了ORDER BY `id`,结果查询耗时仅0.01秒,查询速度足足提高了500倍。

         | Database           |

为什么会这样呢?

         +--------------------+

分析一下新的查询:

         | information_schema |

mysql> explain SELECT `id`, `email`, `mobile`, `_created`, `_updated`, `_create_operator_name`, `_update_operator_name`, `name`, `time`, `year` FROM `p248_user` WHERE 1 = 1 AND id IN (SELECT DISTINCT user_id FROM `p248_user_list` WHERE list_id IN (29)) AND unsubscribe_sms_status = 0 AND mobile <> '' ORDER BY `id` LIMIT 0, 4000;
+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+------+-------------+
| 1 | SIMPLE | p248_user | index | PRIMARY,mobile | PRIMARY | 4 | NULL | 7999 | Using where |
| 1 | SIMPLE | p248_user_list | eq_ref | user_list_id,list_id | user_list_id | 8 | contacts.p248_user.id,const | 1 | Using index |
+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+------+-------------+
2 rows in set (0.00 sec)

         | luomian            |

这次用户表仅扫描了8000行。也就是查询先使用了主键索引,扫描完前4000条符合条件的记录就直接结束了。

         | mydb               |

 

         | mysql              |

 

         | net80576314        |

那取第二页呢:

         | phpcms_uat         |

mysql> explain SELECT `id`, `email`, `mobile`, `_created`, `_updated`, `_create_operator_name`, `_update_operator_name`, `name`, `time`, `year` FROM `p248_user` WHERE 1 = 1 AND id IN (SELECT DISTINCT user_id FROM `p248_user_list` WHERE list_id IN (29)) AND unsubscribe_sms_status = 0 AND mobile <> '' ORDER BY `id` LIMIT 4000, 4000;
+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+-------+-------------+
| 1 | SIMPLE | p248_user | index | PRIMARY,mobile | PRIMARY | 4 | NULL | 15999 | Using where |
| 1 | SIMPLE | p248_user_list | eq_ref | user_list_id,list_id | user_list_id | 8 | contacts.p248_user.id,const | 1 | Using index |
+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+-------+-------------+
2 rows in set (0.00 sec)

         | phpcmsv9           |

这次就要扫描16000行了,因为前4000条是第一页的没用扔掉了。

         | phpcmsv9_new       |

 

         | rutiao             |

 

         | szwalkers          |

这样的话页数越大查询就会越耗时。

         | test               |

 

         | v9test1            |

 

         +--------------------+

但实际上可以换个方法:

         mysql>  show databases like 'php%';

第一次查询结束时,得到最后一条记录的user id, 比如是6500。

         +-----------------+

第二次查询的时候用这个user_id作为条件去匹配

         | Database (php%) |

SELECT `id`, `email`, `mobile`, `_created`, `_updated`, `_create_operator_name`, `_update_operator_name`, `name`, `time`, `year` FROM `p248_user` WHERE id > 6500 AND id IN (SELECT DISTINCT user_id FROM `p248_user_list` WHERE list_id IN (29)) AND unsubscribe_sms_status = 0 AND mobile <> '' ORDER BY `id` LIMIT 0, 4000;

         +-----------------+

这样扫描的行数和第一页依然是一样的。

         | phpcms_uat      |

直到最后一页也是如此,耗时不会有任何明显的下降。

         | phpcmsv9        |

 

         | phpcmsv9_new    |

         +-----------------+

 2、创建数据库

         create database [if not exists] dbname;

         示例:

         mysql> create database if not exists mydb;

 3、选择需要的数据库

         use dbname

         示例:

         mysql> use mydb;

         Database changed

 4、删除数据库

         drop database [if exists] dbname;

         示例:

         mysql> drop database if exists mydb;

 二、操作表

 1、显示表

         show tables;

         示例:

         mysql> show tables;

         +-----------------------+

         | Tables_in_test        |

         +-----------------------+

         | newname               |

         | productnotes          |

         | test_char             |

         | test_inn              |

         | test_inn2             |

         | test_priority         |

         | test_trans            |

         | test_view             |

         +-----------------------+  

 2、创建表

         示例:

         mysql> create table user(

             -> id   int(10) not null auto_increment primary key,

             -> name varchar(50) default 'N/A' not null,

             -> sex  char(1) null

             -> )engine=InnDB;

 3、复制表

         示例:

         mysql> create table student select * from user;

         mysql> create table teacher like user;

 4、重命名表

         mysql> rename table teacher to senior_teacher;

         mysql> alter table student rename to senior_student;

 5、删除表

         mysql> drop table if exists senior_teacher;

 6、查看创建表语句

         mysql> show create table student;

         +---------+-------------------------------------

         | Table   | Create Table

         +---------+-------------------------------------

         | student | CREATE TABLE `student` (

           `id` int(10) NOT NULL DEFAULT '0',

           `name` varchar(50) NOT NULL DEFAULT 'N/A',

           `sex` char(1) DEFAULT NULL

         ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

         +---------+-------------------------------------

 7、查看表结构

         mysql> desc student;

         +-------+-------------+------+-----+---------+-------+

         | Field | Type        | Null | Key | Default | Extra |

         +-------+-------------+------+-----+---------+-------+

         | id    | int(10)     | NO   |     | 0       |       |

         | name  | varchar(50) | NO   |     | N/A     |       |

         | sex   | char(1)     | YES  |     | NULL    |       |

         +-------+-------------+------+-----+---------+-------+

 8、修改表结构  www.2cto.com  

         mysql> alter table student add bithday date null;

         mysql> alter table student modify bithday datetime;

         mysql> alter table student change bithday birt datetime;

         mysql> alter table student drop column bithday;

 9、操作表中的数据

         mysql> select * from student;

         +----+------+------+---------------------+

         | id | name | sex  | birt                |

         +----+------+------+---------------------+

         |  0 | jack | 1    | 2012-12-13 00:00:00 |

         +----+------+------+---------------------+

         mysql> insert into senior_student select * from student;

         mysql> insert into student(name,sex,birt) values('jack','1',current_date());

         mysql> update student set sex = 0 where name = 'jack';

         mysql> delete from student where name = 'jack';

 10、创建及查看索引

         mysql> create index idx_student_name on student(name); 

         mysql> show index from student;

         +---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

         | Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

         +---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

         | student |          1 | idx_student_name |            1 | name        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |

         +---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

         

相关链接: MYSQL入门学习之一:基本操作 MYSQL入门学习之二...

编辑:数据库 本文来源:很简单的三个表,相关链接

关键词:

  • 上一篇:没有了
  • 下一篇:没有了