SQL Server Column Collation和Database collation不一致数据库collation

转载

mob6454cc79ab13 2024-01-03 19:00:58

一、数据库介绍

概念：数据库(DataBase简称DB)就是按照数据结构来组织、存储和管理数据的仓库。

特性：(1)原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。比如在同一个事务中的SQL语句，要么全部执行成功，要么全部执行失败。
(2)一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
(3)隔离性（Isolation）：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
(4)持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

分类：通常分为关系型数据库(SQL)和非关系型数据库(NoSQL)。 SQL 和 NoSQL 是一个互补的关系，应用在不同的场景中。
(1) 关系型数据库：Oracle、MySQL、SQL Server等
优点：易于维护；使用方便；多表复杂操作友好度高
缺点：大批量数据的读写效率差；表结构固定，灵活度稍欠
(2) 非关系型数据库：Redis、MongoDB等
优点：存储数据的格式灵活(键值、文档、图片等)；速度快效率高；可扩展性强
缺点：不支持或有限支持sql；数据结构相对复杂；学习和使用的成本高

二、语法介绍（以MYSQL为例，不同的数据库语法大同小异）

(1) 创建数据库

CREATE DATABASE database_name

(2) 删除数据库

DROP DATABASE database_name

(3) 创建表

CREATE TABLE table_name (column_name column_type)

(4) 删除表

DROP TABLE table_name

(5) 添加表字段

ALTER TABLE table_name ADD new_column DATATYPE

(6) 修改表字段类型

ALTER TABLE table_name MODIFY column_name NEW_DATATYPE

(7) 修改表字段名称

ALTER TABLE table_name CHANGE old_column_name new_column_name DATATYPE

(8) 删除表字段

ALTER TABLE table_name DROP old_column

(9) 插入数据

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

(10) 更新数据

UPDATE table_name SET column1=value1, column2=value2 WHERE 条件

(11) 查询数据

SELECT * FROM table_name WHERE 条件

SELECT column1, column2, column3...columnN FROM table_name

SELECT * FROM table_name LIMIT 3

SELECT * FROM table_name LIMIT 2,1

(12) 删除数据

DELECE FROM table_name WHERE 条件

(13) Like

SELECT * FROM table_name WHERE column1 like '%xxx%'

LIKE子句中使用%号来表示任意字符

(14) Union

SELECT column1, column2, column3...columnN FROM table_a

　　[WHERE condition]

　　UNION [ALL | DISTINCT]

　　SELECT column1, column2, column3...columnN FROM table_b

　　[WHERE condition]

　　UNION的作用的连接两个查询结果集

　　DISTINCT的作用是对两个结果集进行去重处理，默认情况下已经是DISTINCT的结果了

　　ALL的作用的不对两个结果集进行去重处理

(15) ORDER BY

SELECT * FROM table_name ORDER BY column1 [ASC | DESC]

ASC：将结果集按column1升序排列，默认情况下使用升序排序

DESC：将结果集按column1降序排列

(16) GROUP BY

SELECT column1, COUNT(*) FROM table_name GROUP BY column1

把数据按照指定列（可以是一列或者多列）进行分组

(17) 表连接

INNER JOIN

LEFT JOIN

RIGHT JOIN

三、索引

1、索引分类

(1)普通索引（最基本的索引类型，没有唯一性之类的限制）

--直接创建索引
create index 索引名 on 表名 (列名[(length)]);
--修改表方式创建
alter table 表名 add index 索引名 (列名);
--创建表的时候指定索引
create table 表名 (字段1 数据类型，字段2 数据类型,index 索引名 (列名));

(2)唯一索引

①与普通索引类似，但区别是唯一索引列的每个值都唯一
②唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一
③添加唯一键将自动创建唯一索引

--直接创建
create unique index 索引名 on 表名 (列名);
--修改表方式创建索引
alter table 表名 add unique 索引名 (列名);
--创建表的时候指定索引
create table 表名 (字段1 数据类型，字段2 数据类型,unique 索引名 (列名));

(3)主键索引

①是一种特殊的唯一索引，必须指定为“PRIMARY KEY”
②一个表只能有一个主键，不允许有空值。添加主键将自动创建主键索引

--创建表的时候指定索引
create table 表名 (字段1 数据类型，字段2 数据类型,primary key (列名));
--修改表方式创建索引
alter table 表名 add primary key (列名);

(4)组合索引

①在多列上创建的索引
②一个表只能有一个主键，不允许有空值。添加主键将自动创建主键索引

create table 表名 (字段1 数据类型，字段2 数据类型,index 索引名 (列名1，列名2));

(5)全文索引

①适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息
②全文索引可以在 char、varchar 或者 text 类型的列上创建，每个表只允许有一个全文索引

--直接创建
create fulltext index 索引名 on 表名 (列名);
--修改表方式指定全文索引
alter table 表名 add fulltext 索引名 (列名);
--创建表的时候指定全文索引
create table 表名 (字段1 数据类型，字段2 数据类型,fulltext 索引名 (列名));

--使用方式
select * from 表名 where match（列名） against（'查询内容'）；

2、索引的优缺点

优点：(1)可以快速定位，也可以加快表与表之间的连接，提高查询速度，这是创建所有的最主要的原因
(2)可以降低数据库的IO成本
(3)通过创建唯一性索引，可以保证数据表中每一行数据的唯一性
(4)在使用分组和排序时，可大大减少分组和排序的时间

缺点：(1)索引需要占用额外的磁盘空间
(2)在插入和修改数据时要花费更多的时间
(3)创建索引和维护索引要耗费时间

3、索引失效的情况(注意：索引失效情况较复杂，该文章后面有测试)

(1)查询条件包含or
(2)like查询时，“%”在前面会导致当前索引及右边的索引失效
(3)查询时的条件列不包含联合索引中的第一个列，则索引失效
(4)在索引列上使用mysql的内置函数
(5)对索引列运算
(6)索引字段上使用（！= 或者 < >，not in）时
(7)索引字段上使用 is not null，可能导致索引失效
(8)范围会导致索引失效

4、口诀

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写星；
<>,not in还有or，索引失效要少用。

四、测试百万数据加索引前后查询速度

1、创建测试表

-- 创建表
CREATE TABLE `app_user`(
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) DEFAULT '' COMMENT '昵称',
    `email` VARCHAR(50) DEFAULT NULL COMMENT "邮箱",
    `phone` VARCHAR(20) DEFAULT NULL COMMENT "手机号",
    `gender` TINYINT(4) DEFAULT NULL COMMENT "性别 0-男, 1-女",
    `password` VARCHAR(100) NOT NULL COMMENT "密码",
    `age` TINYINT(4) NOT NULL COMMENT "年龄",
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='app用户表';

2、插入测试数据

-- 插入百万数据
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT

BEGIN
    DECLARE num INT DEFAULT 1000000;
    DECLARE i INT DEFAULT 0;
    
    WHILE i < num DO
        -- 插入语句
        INSERT INTO `school`.`app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES
        (CONCAT('用户',i), '123456@qq.com', CONCAT('18', FLOOR(RAND()*((999999999-100000000)+100000000))),
        FLOOR(RAND()*2), UUID(), FLOOR(RAND()*100));
        SET i = i+1;
    END WHILE;
    RETURN i;
    
END;

执行可能会出现This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary报错。

如果出现，可以先执行set global log_bin_trust_function_creators=TRUE ，然后再重新执行上面语句。

执行成功后，再执行：SELECT mock_data();

3、测试查询速度

(1)不加索引，查询一条

SELECT * FROM `app_user` WHERE `name`='用户9999'

EXPLAIN分析sql执行的情况，可以看到语句查询了99W+的数据

EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999'

SQL Server Column Collation和Database collation不一致数据库collation_数据类型

给数据加个常规索引

CREATE INDEX id_app_user_name ON app_user(`name`);

然后在执行上面的查询步骤，发现0.3s就返回了查询结果

SQL Server Column Collation和Database collation不一致数据库collation_数据类型_02

分析sql执行情况，也是只查询了一条，精准定位到了我们想要的数据

五、分析索引的使用情况

1、创建测试表

create table test(
id int primary key auto_increment,
c1 varchar(10),
c2 varchar(10),
c3 varchar(10),
c4 varchar(10),
c5 varchar(10)
) ENGINE=INNODB default CHARSET=utf8;

insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

2、创建复合索引

-- 创建索引
create index idx_test on test(c1,c2,c3,c4);
-- 查看索引信息
show index from test;

SQL Server Column Collation和Database collation不一致数据库collation_数据类型_03

3、分析执行情况

3.1

EXPLAIN SELECT * FROM test WHERE c1='a1' and c2='a2' and c3='a3' and c4='a4';
EXPLAIN SELECT * FROM test WHERE c1='a1' and c3='a3' and c2='a2' and c4='a4';
EXPLAIN SELECT * FROM test WHERE c1='a1' and c4='a4' and c3='a3' and c2='a2';
EXPLAIN SELECT * FROM test WHERE c4='a4' and c3='a3' and c2='a2' and c1='a1';

结论：执行结果一致，在执行常量等值查询时，改变索引列的顺序并不会更改explain的执行结果，因为mysql底层优化器会进行优化，但是推荐按照索引顺序列编写sql语句

3.2

SQL Server Column Collation和Database collation不一致数据库collation_表名_04