Mysql常用sql&索引失效场景

目录

• 数据库基础操作
  • • 1.命令结束符号
    • 2.使用\c清除本次操作
    • 展示数据库
    • 创建数据库
    • 切换当前数据库
    • 查看当前使用的数据库
    • 删除数据库
    • 创建表
    • 展示当前数据库中的表
    • 简单的表操作语句
    • 删除表
    • 约束性条件（列的属性）
    • 非空约束
    • 主键&唯一性约束
• 索引
  • • 外键
    • 自增
• Explain
• 全值匹配
• 匹配最左边的列
• =、in自动优化顺序
• 匹配列前缀
• 匹配范围值
• 精确匹配某一列并范围匹配另一列
• 用于排序
• 用于分组
• 索引不会包含有NULL值的列
• 数据类型出现隐式转换的时候也不会使用索引
• 何时使用聚簇索引或非聚簇索引

数据库基础操作

1.命令结束符号

• ;
• \g
• \G
  \G有一点特殊，它并不以表格的形式返回查询数据，而是以垂直的形式展现查询数据，这包含两个方面的意思：
  • 如果查询结果中包含多个行的数据，各个行的数据之间会用一堆一星号*隔开。
  • 每行中的每一个列的数据将按照列名: 列值的形式给出。

2.使用\c清除本次操作

如果你想放弃本次编写的命令，可以使用\c来清除，比如这样：
mysql> SELECT NOW()\c

展示数据库

mysql> SHOW DATABASES;

创建数据库

mysql> CREATE DATABASE 数据库名;

切换当前数据库

mysql> USE 数据库名称;

查看当前使用的数据库

mysql> select database();

删除数据库

mysql> DROP DATABASE 数据库名;

创建表

mysql> CREATE TABLE first_table (
   ->     first_column INT COMMENT '列的注释',
   ->     second_column VARCHAR(100)
   -> ) COMMENT '表的注释';

展示当前数据库中的表

mysql> SHOW TABLES;

简单的表操作语句

SELECT * FROM 表名;
INSERT INTO 表名(列1, 列2, ...) VALUES(列1的值，列2的值, ...);
INSERT INTO 表名(列1,列2, ...) VAULES(列1的值，列2的值, ...), (列1的值，列2的值, ...), (列1的值，列2的值, ...), ...;
UPDATE 表名 SET 列1 = 列1的值,... WHERE 条件...;

删除表

DROP TABLE IF EXISTS 表名;

约束性条件（列的属性）

mysql> CREATE TABLE first_table (
   ->     first_column INT,
   ->     second_column VARCHAR(100) DEFAULT 'abc'
   -> );

非空约束

mysql> CREATE TABLE first_table (
   ->     first_column INT NOT NULL,
   ->     second_column VARCHAR(100) DEFAULT 'abc'
   -> );

主键&唯一性约束

CREATE TABLE student_info (
   number INT PRIMARY KEY,
   name VARCHAR(5),
   sex ENUM('男', '女'),
   id_number CHAR(18) UNIQUE,
   department VARCHAR(30),
   major VARCHAR(30),
   enrollment_time DATE
);
CREATE TABLE student_info (
   number INT,
   name VARCHAR(5),
   sex ENUM('男', '女'),
   id_number CHAR(18),
   department VARCHAR(30),
   major VARCHAR(30),
   enrollment_time DATE
   PRIMARY KEY (number)
   UNIQUE KEY (id_number)
);

PRIMARY KEY (列名1, 列名2, ...) | UNIQUE [约束名称] (列名1, 列名2, ...) | UNIQUE KEY [约束名称] (列名1, 列名2, ...)
主键和唯一性约束都能保证某个列或者列组合的唯一性，但是：

• 一张表中只能定义一个主键，却可以定义多个唯一性约束！
• 主键列不允许存放NULL值，而普通的唯一性约束列可以存放NULL值！

索引

InnoDB和MyISAM会自动为主键或者声明为UNIQUE的列去自动建立B+树索引
在创建表的时候指定需要建立索引的单个列或者建立联合索引的多个列：

CREATE TALBE 表名 (
   各种列的信息 ··· , 
   [KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
)

其中的KEY和INDEX是同义词，任意选用一个就可以
修改表结构的时候添加索引
ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);
在修改表结构的时候删除索引
ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;

外键

CONSTRAINT [外键名称] FOREIGN KEY(列1, 列2, ...) REFERENCES 父表名(父列1, 父列2, ...);

自增

mysql> CREATE TABLE first_table (
   ->     id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   ->     first_column INT,
   ->     second_column VARCHAR(100) DEFAULT 'abc'
   -> );

在使用递增属性的时候需要注意这几点：

• 一个表中最多有一个递增列。
• 一般只为整数类型的列定义递增属性，浮点数类型基本不用递增属性。
• 具有AUTO_INCREMENT属性的列必须建立索引。主键和具有唯一性约束的列会自动建立索引，至于什么是索引，我们后边会详细唠叨。
• 一般递增列都是作为主键的属性，来自动生成唯一标识一个记录的主键值。
• 因为具有AUTO_INCREMENT属性的列是从1开始递增的，所以最好用UNSIGNED来修饰这个列，可以提升正数的表示范围。

Explain

MySQL 性能优化神器 Explain 使用分析

全值匹配

假设我们有联合索引idx_name_birthday_phone_number，索引列的顺序为：name、birthday、phone_number
如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配，比方说下边这个查找语句：
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';

如果搜索条件中的列的顺序和索引列的顺序不一致就不太好了，比方说先对birthday列的值进行匹配的话，由于B+树中的数据页和记录是先按name列的值进行排序的，不能直接使用二分法快速定位记录，所以只能扫描所有的记录页。

匹配最左边的列

搜索条件中的列可以不用包含全部联合索引中的列，只需要包含左边的就行，也可以包含多个，但是列的顺序必须和索引列的定义顺序一致

=、in自动优化顺序

等于（=）和in 可以乱序。比如，a = 1 AND b = 2 AND c = 3 建立（a,b,c）索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的模式。

匹配列前缀

匹配列前缀如：name like 'As%'，这样的搜索条件是会用到索引的，如果要匹配的是后缀或者中间的某个子串，而类似：name like '%As%'，这是不会用到索引的
以%开头的like查询不能够利用B-tree索引，执行计划中key的值为NULL表示没有使用索引。

匹配范围值

所有记录都是按照索引列的值从小到大的顺序排好序的，所以查找索引列的值在某个范围内的记录是使用了索引的，不过进行范围查找的时候需要注意，如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引

精确匹配某一列并范围匹配另一列

对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找

用于排序

ORDER BY的子句后边的列的顺序须按照索引列的顺序给出，这是能够使用索引的

用于分组

与用于排序的情况类似
单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引
由于MySQL查询只使用一个索引，因此如果WHERE子句中已经使用了索引的话，那么ORDER BY中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下，不要使用排序操作；尽量不要包含多个列的排序，如果需要，最好给这些列也创建组合索引

索引不会包含有NULL值的列

只要列中包含有NULL值，都将不会被包含在索引中，组合索引中只要有一列有NULL值，那么这一列对于此条组合索引就是无效的。所以我们在数据库设计时，不要让索引字段的默认值为NULL

数据类型出现隐式转换的时候也不会使用索引

比如，FROM_UNIXTIME(create_time) = '2016-06-06' 就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ： create_time = UNIX_TIMESTAMP('2016-06-06')

何时使用聚簇索引或非聚簇索引

使用动作描述	使用聚簇索引	使用非聚簇索引
列经常被分组排序	√	√
返回某范围内的数据	√	×
一个或极少不同的值	×	×
小数目不同的值	√	×
大数目不同的值	×	√
频繁更新的列	×	√
外键列	√	√
主键列	√	√
频繁修改索引列	×	√