MySQL 索引 优化

插入

Mysql插入值，插入一个空行（前提为not null的列得有默认值）

INSERT INTO award () VALUES ();

插入多列

INSERT INTO award (aty_id,nickname) VALUES (1,'suntong'),(2,'isuntong');

显示

CREATE TABLE `award` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
   `aty_id` varchar(100) NOT NULL DEFAULT '' COMMENT '活动场景id',
   `nickname` varchar(12) NOT NULL DEFAULT '' COMMENT '用户昵称',
   `is_awarded` tinyint(1) NOT NULL DEFAULT 0 COMMENT '用户是否领奖',
   `award_time` int(11) NOT NULL DEFAULT 0 COMMENT '领奖时间',
   `account` varchar(12) NOT NULL DEFAULT '' COMMENT '帐号',
   `password` char(32) NOT NULL DEFAULT '' COMMENT '密码',
   `message` varchar(255) NOT NULL DEFAULT '' COMMENT '获奖信息',
   `created_time` int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',
   `updated_time` int(11) NOT NULL DEFAULT 0 COMMENT '更新时间',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='获奖信息表';

int(10),int(11)都是装一样范围得数据，只是在显示长度上不同，取出数据都是一样的，同时必须填上zerofill 才能看到效果，mysql会分配默认得长度，

分析

ALTER TABLE award MODIFY id int(11) ZEROFILL not NULL AUTO_INCREMENT;

此时插入数据在数据库中看不出来

INSERT INTO award (id) VALUES (100);

INSERT INTO award (id) VALUES (100);

执行两次

自增id用完了怎么办

【原创】杂谈自增主键用完了怎么办

char，varchar括号内的含义

Mysql中数据类型括号中的数字代表的含义

添加注释，查看注释

MySQL 添加注释(comment)

数值类型都有默认长度，char默认长度为1，varchar不写（）会报错

CREATE TABLE `user` (
    `id` INT NOT NULL AUTO_INCREMENT COMMENT '用户id',
    `name` CHAR NOT NULL DEFAULT '' COMMENT '用户姓名',
    `phone` CHAR(11) NOT NULL DEFAULT '' COMMENT '用户电话',
    `email` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '用户邮箱',
    `address` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '用户地址',
    `sex` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '用户性别',
    `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
        ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户信息表';

INSERT INTO user (name) VALUES ('孙');
INSERT INTO user (name) VALUES ('孙彤');
INSERT INTO user (name) VALUES ('a');
INSERT INTO user (name) VALUES ('ab');

13可以，24不可以

索引

在MySQL中，我们可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

下面分别对EXPLAIN命令结果的每一列进行说明：

select_type:表示SELECT的类型，常见的取值有：

table:输出结果集的表（表别名）

type:表示MySQL在表中找到所需行的方式，或者叫访问类型。常见访问类型如下，从上到下，性能由差到最好：

• type=ALL，全表扫描，MySQL遍历全表来找到匹配行

一般是没有where条件或者where条件没有使用索引的查询语句

EXPLAIN SELECT * FROM user WHERE sex = 0;

• type=index，索引全扫描，MySQL遍历整个索引来查询匹配行，并不会扫描表

EXPLAIN SELECT id FROM user;

• type=range，索引范围扫描，常用于<、<=、>、>=、between等操作

EXPLAIN SELECT * FROM user WHERE id>=2 AND id<=3;

注意这种情况下比较的字段是需要加索引的，如果没有索引，则MySQL会进行全表扫描

• type=ref，使用非唯一索引或唯一索引的前缀扫描，返回匹配某个单独值的记录行

sex字段存在普通索引（非唯一索引）

ALTER TABLE `user` ADD INDEX index_sex(`sex`);
EXPLAIN SELECT * FROM user WHERE sex = 1;

ref类型还经常会出现在join操作中：

customer、payment表关联查询，关联字段customer.customer_id（主键），payment.customer_id（非唯一索引）。表关联查询时必定会有一张表进行全表扫描，此表一定是几张表中记录行数最少的表，然后再通过非唯一索引寻找其他关联表中的匹配行，以此达到表关联时扫描行数最少。

因为customer、payment两表中customer表的记录行数最少，所以customer表进行全表扫描，payment表通过非唯一索引寻找匹配行。

EXPLAIN SELECT * FROM customer customer INNER JOIN payment payment ON customer.customer_id = payment.customer_id;

• type=eq_ref，类似ref，区别在于使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配

eq_ref一般出现在多表连接时使用primary key或者unique index作为关联条件。

film、film_text表关联查询和上一条所说的基本一致，只不过关联条件由非唯一索引变成了主键。

EXPLAIN SELECT * from user a INNER JOIN items b ON a.id = b.id;

• type=const/system，单表中最多有一条匹配行，查询起来非常迅速，所以这个匹配行的其他列的值可以被优化器在当前查询中当作常量来处理

const/system出现在根据主键primary key或者 唯一索引 unique index 进行的查询

EXPLAIN SELECT * FROM user WHERE id = 1;

• type=NULL，MySQL不用访问表或者索引，直接就能够得到结果

EXPLAIN SELECT 1-2;

possible_keys: 表示查询可能使用的索引

key: 实际使用的索引

key_len: 使用索引字段的长度

ref: 使用哪个列或常数与key一起从表中选择行。

rows: 扫描行的数量

filtered: 存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例(百分比)

Extra: 执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息

最主要有以下三种

（达到排序效果）

什么是ICP？
MySQL5.6引入了Index Condition Pushdown（ICP）的特性，进一步优化了查询。Pushdown表示操作下放，某些情况下的条件过滤操作下放到存储引擎。

EXPLAIN SELECT * FROM rental WHERE rental_date='2005-05-25' AND customer_id>=300 AND customer_id<=400;

在5.6版本之前：

优化器首先使用复合索引idx_rental_date过滤出符合条件rental_date='2005-05-25'的记录，然后根据复合索引idx_rental_date回表获取记录，最终根据条件customer_id>=300 AND customer_id<=400过滤出最后的查询结果（在服务层完成）。

在5.6版本之后：

MySQL使用了ICP来进一步优化查询，在检索的时候，把条件customer_id>=300 AND customer_id<=400也推到存储引擎层完成过滤，这样能够降低不必要的IO访问。Extra为Using index condition就表示使用了ICP优化。