首页 > 代码库 > Mysql之表的操作&索引&explain&profile
Mysql之表的操作&索引&explain&profile
创建一个表create table(help create table)
=>rename table A to B 更改表名
=>alter table A rename to B 更改表
=>drop table A 删除表
mysql> show create database gtms; #查看建库语句 +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | gtms | CREATE DATABASE `gtms` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+---------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> create table student ( -> id int(4) not null auto_increment, -> name char(20) not null, -> age tinyint(2) not null default ‘0‘, -> dept varchar(16) default null, -> primary key(id), -> key index_name(name) -> ); mysql> show create table student\G #查看建表语句 *************************** 1. row *************************** Table: student Create Table: CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT ‘0‘, `dept` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`), #主键索引，唯一，一般建表时添加 KEY `index_name` (`name`) #普通索引 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
显示表结构desc或show columns
mysql> desc student; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | MUL | NULL | | | age | tinyint(2) | NO | | 0 | | | dept | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> show columns from student; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | MUL | NULL | | | age | tinyint(2) | NO | | 0 | | | dept | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
字段的增加与删除
alter table 表名 add[drop,modify,change] 字段 类型 其他选项;
mysql> alter table student add phonenum char(11) after name; #如插到第一列为first mysql> desc student; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | MUL | NULL | | | phonenum | char(11) | YES | | NULL | | | age | tinyint(2) | NO | | 0 | | | dept | varchar(16) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ mysql> alter table student drop column phonenum; mysql> desc student; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | MUL | NULL | | | age | tinyint(2) | NO | | 0 | | | dept | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+
字段名称和属性的更改
=>谨慎修改字段属性，可能导致原有数据出错
=>谨慎修改长度，需保证不短于已有数据
mysql> alter table student change name names char(20); mysql> alter table student change names names char(30); mysql> desc student; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | names | char(30) | YES | MUL | NULL | | | phonenum | char(11) | YES | | NULL | | | age | tinyint(2) | NO | | 0 | | | dept | varchar(16) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+
mysql> alter table student modify column phonenum char(12); #也可以使用modify column进行更改
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
数据插入
=>delete from A; 一次全部删除表内数据，逻辑清除，按行处理。所以要加条件（多级审核）
=>truncate table A; 也是直接清空一张表，物理删除（速度快）
mysql> insert into student values(1,"张三","13701800003",23,"经理"),(2,"李四","13701800004",24,"助 理"); mysql> insert into student (name,phonenum,age,dept)values ("王五","13701800005",25,"员工"); mysql> select * from student; +----+--------+-------------+-----+--------+ | id | name | phonenum | age | dept | +----+--------+-------------+-----+--------+ | 1 | 张三 | 13701800003 | 23 | 经理 | | 2 | 李四 | 13701800004 | 24 | 助理 | | 3 | 王五 | 13701800005 | 25 | 员工 | +----+--------+-------------+-----+--------+
表数据修改
=>更改表数据一定要加上where条件，严重案例：所有记录都被更改
防止人为误操作：
a、mysql登陆命令加-U选项，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序拒绝执行
b、linux别名设置# alias mysql=‘mysql -U‘ ，加到/etc/profile
mysql> update student set qq="87014247" where id=1; mysql> select * from student; +----+--------+-------------+-----+--------+----------+ | id | names | phonenum | age | dept | qq | +----+--------+-------------+-----+--------+----------+ | 1 | 张三 | 13701800003 | 23 | 经理 | 87014247 | | 2 | 李四 | 13701800004 | 24 | 助理 | 12344754 | | 3 | 王五 | 13701800005 | 25 | 员工 | 5414754 | +----+--------+-------------+-----+--------+----------+
创建索引基本知识
1、索引类似书的目录，会加快查询速度
2、在表的列（字段）上创建索引
3、索引会加快查询速度，但是也会影响更新速度，因为更新会维护索引数据
4、索引不是越多越好，一般选择查询频繁的where条件的字段上创建
5、小表或重复值很多的列上一般不建索引，要在大表及重复值少的条件列上创建索引
6、多个列联合索引有前缀生效特性
7、当字段内容前n个字符已经接近唯一时，可以对字段前n个字符创建索引
8、索引从工作方式分，有主键，唯一，普通索引
9、索引类型有BTREE（默认）和hash（适合缓存，内存数据库）等
索引列的创建及生效条件
问题1?既然索引可以加快查询速度，那么就给所有列建索引吧？
解答：索引不但占用系统空间，更新数据库时还需要维护索引数据，因此索引是一把双刃剑，不是越多越好。几十到几百行的小表无需建立索引，写多读少的业务少建索引。
问题2？到底在哪些列上建立索引呢
secect user,host from mysql.user where host=...索引一定要创建在查询频繁条件列，而不是selcect后的选择数据的列，另外我们要尽量选择在唯一值多的大表上的列建立索引。select count(distinct列) from table
查看索引（默认BTREE索引）
mysql> show index from student; +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | student | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | student | 1 | index_name | 1 | names | A | 3 | NULL | NULL | YES | BTREE | | | +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
索引的增加与删除
=>增加索引在访问低谷时进行，几百万条需要几分钟
=>uniq index唯一索引，（用来约束字段值唯一，比如网站注册时用的email唯一，查询速度会快些）
mysql> drop index index_name on student; mysql> create index index_names on student(names); #在names字段上增加名为index_names的索引 mysql> alter table student drop index index_names; mysql> alter table student add index index_names(names); #在names字段上增加名为index_names的索引
对字段的前n个字符创建联合索引
mysql> alter table student add column qq char(20); mysql> update student set qq=9485754 where id=1; mysql> update student set qq=12344754 where id=2; mysql> update student set qq=5414754 where id=3; mysql> select * from student; +----+--------+-------------+-----+--------+----------+ | id | names | phonenum | age | dept | qq | +----+--------+-------------+-----+--------+----------+ | 1 | 张三 | 13701800003 | 23 | 经理 | 9485754 | | 2 | 李四 | 13701800004 | 24 | 助理 | 12344754 | | 3 | 王五 | 13701800005 | 25 | 员工 | 5414754 | +----+--------+-------------+-----+--------+----------+ mysql> show index from student\G *************************** 3. row *************************** Table: student Non_unique: 1 Key_name: index_qq4 Seq_in_index: 1 Column_name: qq Collation: A Cardinality: 3 Sub_part: 4 #对qq字段的前4个字符创建的索引 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment:
创建联合索引
/*按照列查询数据时，联合索引是有前缀生效特性的
/*index（a,b,c）仅仅a,ab,abc三个查询条件列可以走索引，其他不走索引，所以把经常用于条件查询的放前面
mysql> create index names_qq_index on student(names,qq(4)); *************************** 4. row *************************** Table: student Non_unique: 1 Key_name: names_qq_index Seq_in_index: 1 Column_name: names Collation: A Cardinality: 3 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 5. row *************************** Table: student Non_unique: 1 Key_name: names_qq_index Seq_in_index: 2 Column_name: qq Collation: A Cardinality: 3 Sub_part: 4 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment:
MYSQL查询优化 explain
使用explain查看sql语句执行计划
mysql> explain select * from student where qq="5414754";
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+
| 1 | SIMPLE | student | ref | index_qq4 | index_qq4 | 13 | const | 1 | Using where |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+
mysql> drop index index_qq4 on student; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from student where qq="5414754"; #没有索引之后执行select扫描了3行 +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | student | ALL | NULL | NULL | NULL | NULL | 3 | Using where | +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
使用explain优化SQL语句的基本流程（一般让开发去优化）
1、抓慢查询，然后使用explain语句检查索引执行情况
a、mysql> show full processlist\G (现场抓）或#mysql -uroot -prootabcd -e "show processlist;" | grep -i select（效果更好）
b、分析慢查询日志（平时运维）使用mysqlsla工具分析（自带的mysqldumpslow比较简陋）
long_query_time=1 超过1秒以上的查询记录下来
log-slow-queries=/data/slow.log
log_queries_not_using_indexes 没走索引的也记录
对slow.log,进行切割脚本分析 切割慢查询(写成脚本，使用mysqlsla分析)
cd /data/ &&
/bin/mv slow.log /back/$(date +%F)_slow.log
mysqladmin -uroot -prootabcd flush-logs (也会刷新binlog)
mysqlsla /back/$(date +%F)_slow.log > new_/back/$(date +%F)_slow.log
mail -s "logname" mailadress </bakup/ new_/back/$(date +%F)_slow.log
2、explain语句检查索引执行情况
3、对需要建索引的列建立索引
使用explain优化sql语句场景案例
优化起因
1、网站慢，浏览慢，假定查出是数据库问题
2、数据库服务器查看uptime,负载很高（load average:8.01 6.30,5.58 超过核数数属于高的）
3、登陆数据库show full processlist===>再用explain 检查语句是否走索引，查看建表语句或show index等，
4、慢查询语句或日志
5、select count(distinct列) from table 查看字段唯一数 根据这个和开发沟通创建相关索引，如果都不多考虑联合索引，如果大表，创建需要几分钟，建议在业务低谷执行
MYSQL查询优化 profile（==>可以详细看到每个执行语句整个详细执行步骤）
使用help show profile可以详细的查看此功能的帮助，
mysql> help show profile; Name: ‘SHOW PROFILE‘ Description: Syntax: SHOW PROFILE [type [, type] ... ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]] type: ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS The SHOW PROFILE and SHOW PROFILES statements display profiling information that indicates resource usage for statements executed during the course of the current session. Profiling is controlled by the profiling session variable, which has a default value of 0 (OFF). Profiling is enabled by setting profiling to 1 or ON: mysql> SET profiling = 1; SHOW PROFILES displays a list of the most recent statements sent to the server. The size of the list is controlled by the profiling_history_size session variable, which has a default value of 15. The maximum value is 100. Setting the value to 0 has the practical effect of disabling profiling. All statements are profiled except SHOW PROFILE and SHOW PROFILES, so you will find neither of those statements in the profile list. Malformed statements are profiled. For example, SHOW PROFILING is an illegal statement, and a syntax error occurs if you try to execute it, but it will show up in the profiling list. SHOW PROFILE displays detailed information about a single statement. Without the FOR QUERY n clause, the output pertains to the most recently executed statement. If FOR QUERY n is included, SHOW PROFILE displays information for statement n. The values of n correspond to the Query_ID values displayed by SHOW PROFILES. The LIMIT row_count clause may be given to limit the output to row_count rows. If LIMIT is given, OFFSET offset may be added to begin the output offset rows into the full set of rows. By default, SHOW PROFILE displays Status and Duration columns. The Status values are like the State values displayed by SHOW PROCESSLIST, although there might be some minor differences in interpretion for the two statements for some status values (see http://dev.mysql.com/doc/refman/5.5/en/thread-information.html). Optional type values may be specified to display specific additional types of information: o ALL displays all information o BLOCK IO displays counts for block input and output operations o CONTEXT SWITCHES displays counts for voluntary and involuntary context switches o CPU displays user and system CPU usage times o IPC displays counts for messages sent and received o MEMORY is not currently implemented o PAGE FAULTS displays counts for major and minor page faults o SOURCE displays the names of functions from the source code, together with the name and line number of the file in which the function occurs o SWAPS displays swap counts Profiling is enabled per session. When a session ends, its profiling information is lost. URL: http://dev.mysql.com/doc/refman/5.5/en/show-profile.html Examples: mysql> SELECT @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) mysql> SET profiling = 1; Query OK, 0 rows affected (0.00 sec) mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE T1 (id INT); Query OK, 0 rows affected (0.01 sec) mysql> SHOW PROFILES; +----------+----------+--------------------------+ | Query_ID | Duration | Query | +----------+----------+--------------------------+ | 0 | 0.000088 | SET PROFILING = 1 | | 1 | 0.000136 | DROP TABLE IF EXISTS t1 | | 2 | 0.011947 | CREATE TABLE t1 (id INT) | +----------+----------+--------------------------+ 3 rows in set (0.00 sec) mysql> SHOW PROFILE; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | checking permissions | 0.000040 | | creating table | 0.000056 | | After create | 0.011363 | | query end | 0.000375 | | freeing items | 0.000089 | | logging slow query | 0.000019 | | cleaning up | 0.000005 | +----------------------+----------+ 7 rows in set (0.00 sec) mysql> SHOW PROFILE FOR QUERY 1; +--------------------+----------+ | Status | Duration | +--------------------+----------+ | query end | 0.000107 | | freeing items | 0.000008 | | logging slow query | 0.000015 | | cleaning up | 0.000006 | +--------------------+----------+ 4 rows in set (0.00 sec) mysql> SHOW PROFILE CPU FOR QUERY 2; +----------------------+----------+----------+------------+ | Status | Duration | CPU_user | CPU_system | +----------------------+----------+----------+------------+ | checking permissions | 0.000040 | 0.000038 | 0.000002 | | creating table | 0.000056 | 0.000028 | 0.000028 | | After create | 0.011363 | 0.000217 | 0.001571 | | query end | 0.000375 | 0.000013 | 0.000028 | | freeing items | 0.000089 | 0.000010 | 0.000014 | | logging slow query | 0.000019 | 0.000009 | 0.000010 | | cleaning up | 0.000005 | 0.000003 | 0.000002 | +----------------------+----------+----------+------------+ 7 rows in set (0.00 sec)
mysql> set profiling=1; #打开profile功能 mysql> SELECT @@profiling; +-------------+ | @@profiling | +-------------+ | 1 | +-------------+ mysql> use gtms Database changed mysql> select * from student; +----+--------+-------------+-----+--------+----------+ | id | names | phonenum | age | dept | qq | +----+--------+-------------+-----+--------+----------+ | 1 | 张三 | 13701800003 | 23 | 经理 | 9485754 | | 2 | 李四 | 13701800004 | 24 | 助理 | 12344754 | | 3 | 王五 | 13701800005 | 25 | 员工 | 5414754 | +----+--------+-------------+-----+--------+----------+ mysql> select * from student where qq=5414754; +----+--------+-------------+-----+--------+---------+ | id | names | phonenum | age | dept | qq | +----+--------+-------------+-----+--------+---------+ | 3 | 王五 | 13701800005 | 25 | 员工 | 5414754 | +----+--------+-------------+-----+--------+---------+ mysql> show profiles; #可以查看到执行的sql语句及执行时间 +----------+------------+----------------------------------------+ | Query_ID | Duration | Query | +----------+------------+----------------------------------------+ | 1 | 0.00013425 | SELECT @@profiling | | 2 | 0.00017100 | SELECT DATABASE() | | 3 | 0.05092200 | select * from student | | 4 | 0.00043800 | select * from student where qq=5414754 | +----------+------------+----------------------------------------+ mysql> show profile for query 3; #查看Query_ID为3的sql语句极为详细的过程及执行时间 +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000084 | | checking permissions | 0.000006 | | Opening tables | 0.050367 | | System lock | 0.000010 | | init | 0.000017 | | optimizing | 0.000020 | | statistics | 0.000048 | | preparing | 0.000049 | | executing | 0.000004 | | Sending data | 0.000175 | | end | 0.000006 | | query end | 0.000004 | | closing tables | 0.000005 | | freeing items | 0.000103 | | logging slow query | 0.000025 | | cleaning up | 0.000002 | +----------------------+----------+
mysql> show profile CPU for query 3; #查看CPU_user CPU_system使用信息
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting | 0.000084 | 0.000000 | 0.000000 |
| checking permissions | 0.000006 | 0.000000 | 0.000000 |
| Opening tables | 0.050367 | 0.001000 | 0.000000 |
| System lock | 0.000010 | 0.000000 | 0.000000 |
| init | 0.000017 | 0.000000 | 0.000000 |
| optimizing | 0.000020 | 0.000000 | 0.000000 |
| statistics | 0.000048 | 0.000000 | 0.000000 |
| preparing | 0.000049 | 0.000000 | 0.000000 |
| executing | 0.000004 | 0.000000 | 0.000000 |
| Sending data | 0.000175 | 0.000000 | 0.000000 |
| end | 0.000006 | 0.000000 | 0.000000 |
| query end | 0.000004 | 0.000000 | 0.000000 |
| closing tables | 0.000005 | 0.000000 | 0.000000 |
| freeing items | 0.000103 | 0.000000 | 0.000000 |
| logging slow query | 0.000025 | 0.000000 | 0.000000 |
| cleaning up | 0.000002 | 0.000000 | 0.000000 |
+----------------------+----------+----------+------------+
Mysql之表的操作&索引&explain&profile
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。