MySQL 字符集、索引、视图

字符集

查看MySQL支持的字符集

  1. show character set;
  2. 查看information_schema.character_set
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
MariaDB [information_schema]> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
... ...

MariaDB [information_schema]> select * from information_schema.character_sets;
+--------------------+----------------------+-----------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+-----------------------------+--------+
| big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 |
| dec8 | dec8_swedish_ci | DEC West European | 1 |
| cp850 | cp850_general_ci | DOS West European | 1 |
| hp8 | hp8_english_ci | HP West European | 1 |
| koi8r | koi8r_general_ci | KOI8-R Relcom Russian | 1 |
... ...

MySQL字符集包括字符集(CHARACTER)和校对规则(COLLATION)两个概念。 字符集定义MySQL存储字符串的方式。 校对规则定义比较字符串的方式。字符串和校对规则一对多,每个字符集至少对应一个校对规则。
使用SHOW COLLATION; 或查看那information_schema.COLLATIONS表查看所有校对规则

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
MariaDB [information_schema]> show COLLATION;
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
| dec8_bin | dec8 | 69 | | Yes | 1 |
... ...

MariaDB [information_schema]> select * from information_schema.COLLATIONS;
+--------------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------------+--------------------+-----+------------+-------------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
| dec8_bin | dec8 | 69 | | Yes | 1 |
| cp850_general_ci | cp850 | 4 | Yes | Yes | 1 |
| cp850_bin | cp850 | 80 | | Yes | 1 |
... ...

校对规则命名约定:以与其相关的字符集名开始,包含语言名,并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin(二元,即比较基于字符编码的值而与language无关)结束。

字符集设置

MySQL的字符集和校对规则有4个默认级别:服务器级、数据库级、表级、字段级。

服务器字符集和校对规则

服务器字符集和校对规则在MySQL服务启动的时候确定。

  1. my.cnf中设置

    1
    2
    [mysqld]
    default-character-set=gbk
  2. 启动选项中指定

    1
    mysqld --default-character_set=gdk
  3. 编译时指定

    1
    ./configure --with-charset=gbk

如果没有特别指定服务器字符集,默认使用latin1作为服务器字符集。如未指定校对规则,则使用字符集默认的校对规则。
使用show variables like ‘character_set_server’查看服务器字符集
使用show variables like ‘collation_server’查看服务器校对规则

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [(none)]> show variables like 'character_set_server';
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| character_set_server | latin1 |
+----------------------+--------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'collation_server';
+------------------+-------------------+
| Variable_name | Value |
+------------------+-------------------+
| collation_server | latin1_swedish_ci |
+------------------+-------------------+
1 row in set (0.00 sec)

数据库字符集和校对规则

数据库字符集和校对规则在创建数据库时指定,也可通过alter database命令修改。如果数据库中已存在数据,修改字符集并不能将已有数据按照新的字符集存储
设置数据库字符集的规则:

  • 如果指定了字符集和校对规则,则使用指定的字符集和校对规则;
  • 如果指定了字符集没有指定校对规则,则使用指定字符集的默认校对规则;
  • 如果没有指定字符集和校对规则,则使用服务器字符集和校对规则作为数据库的字符集和校对规则
    使用show variables like ‘character_set_database’查看数据库字符集
    使用show variables like ‘collation_database’查看数据库校对规则
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    MariaDB [test]> show variables like 'character_set_database';
    +------------------------+--------+
    | Variable_name | Value |
    +------------------------+--------+
    | character_set_database | latin1 |
    +------------------------+--------+
    1 row in set (0.00 sec)

    MariaDB [test]> show variables like 'collation_database';
    +--------------------+-------------------+
    | Variable_name | Value |
    +--------------------+-------------------+
    | collation_database | latin1_swedish_ci |
    +--------------------+-------------------+

表字符集和校对规则

表的字符集和校对规则在创建表的时候指定,可以通过 alter table 命令进行修改。如果表中已有记录,修改字符集对原有的记录并没有影响,不会按照新的字符集进行存放。表的字段仍然使用原来的字符集。
设置表字符集的规则:

  1. 如果指定了字符集和校对规则,使用指定的字符集和校对规则;
  2. 如果指定了字符集没有指定校对规则,使用指定字符集的默认校对规则;
  3. 如果没有指定字符集和校对规则,使用数据库字符集和校对规则作为表的字符集和校对规则。
    推荐在创建表的时候明确指定字符集和校对规则,避免受到默认值的影响。要显示表的字符集和校对规则,可以使用 show create table 命令查看

列字符集和校对规则

列字符集和校对规则的定义可以在创建表时指定,或者在修改表时调整,如果在创建表的时候没有特别指定字符集和校对规则,则默认使用表的字符集和校对规则。

连接字符集和校对规则

MySQL提供三个参数:character_set_client、character_set_connection 和 character_set_results,分别代表客户端、连接和返回结果的字符集。通常3个字符集都是相同的,保证用户写入数据正确读出。

字符集修改步骤

  1. 导出表结构。mysqldump
  2. 修改表结构中字符集。
  3. 确保数据不再更新,导出所有记录。mysqlduump
  4. 修改导入SQL文件中的字符集。SET NAMES。
  5. 使用新字符集创建数据库。
  6. 创建表。
  7. 导入数据。

索引

索引是数据库中用来提高性能的最常用工具。

索引概述

所用MySQL列类型都可以被索引, 对相关列使用索引是提高select操作性能的最佳途径。

索引创建

  1. 直接创建索引

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] ...

    index_col_name:
    col_name [(length)] [ASC | DESC]

    index_option:
    KEY_BLOCK_SIZE [=] value
    | index_type
    | WITH PARSER parser_name
    | COMMENT 'string'

    index_type:
    USING {BTREE | HASH}
  2. 创建/修改表创建索引

删除索引

DROP INDEX index_name on tbl_name;

显示索引

  1. show create table tbl_name查看表结构,查看概况
  2. show index from tbl_name只查看索引,查看详情
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
MariaDB [test]> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`name` varchar(64) DEFAULT NULL,
`age` int(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MariaDB [test]> create index name_index on student (name(20)) comment "名称索引";
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0


MariaDB [test]> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`name` varchar(64) DEFAULT NULL,
`age` int(4) DEFAULT NULL,
KEY `name_index` (`name`(20)) COMMENT '名称索引'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MariaDB [test]> 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 | 1 | name_index | 1 | name | A | 7 | 20 | NULL | YES | BTREE | | 名称索引 |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

设计索引原则

  1. 搜索的列不一定是所要选择的列。(对where子语句或连接子句中指定的列添加索引,而不是select关键字后的列添加索引)
  2. 使用唯一索引。(考虑某列中值的分布。索引的列的基数越大,索引的效果越好。)
  3. 使用短索引。对字符串索引,应指定前缀长度。
  4. 利用最左前缀。
  5. 不要过度使用索引。(索引占用额外空间、降低写操作性能)
  6. 对于InnoDB存储引擎的表,系统会按照一定顺序保存,如果有明确定义的主键,则按照主键顺序保存。如果没有主键,但是有唯一索引,那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引,那么表中会自动生成一个内部列,按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的,所以 InnoDB 表尽量自己指定主键,当表中同时有几个列都是唯一的,都可以作为主键的时候,要选择最常作为访问条件的列作为主键,提高查询的效率。另外,还需要注意,InnoDB 表的普通索引都会保存主键的键值,所以主键要尽可能选择较短的数据类型,可以有效地减少索引的磁盘占用,提高索引的缓存效果。

索引存储分类

索引在MySQL的存储引擎层实现,而不是在服务器层实现,所以每种存储引擎的索引不一定完全相同,也不是所有的存储引擎支持所有的索引类型.
MySQL支持的索引:

  1. B-Tree索引:最常见的索引类型,大部分引擎都支持B数索引
  2. HASH索引:只有Memory/Heap引擎支持,使用场景简单
  3. R-Tree索引(空间索引):空间索引是MyISAM的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少
  4. Full-text(全文索引):全文索引也是MyISAM的一个特殊索引类型,主要用于全文索引,InnoDB从MySQL5.6版本开始提供对全文索引的支持

MySQL目前不支持函数索引,但能对列的前面某一部分进行索引,可以大大缩小索引文件的大小,但在排序和分组操作的时候无法使用

HASH 索引有一些重要的特征需要在使用的时候特别注意:

  1. 只用于使用=或<=>操作符的等式比较。
  2. 优化器不能使用 HASH 索引来加速 ORDER BY 操作。
  3. MySQL 不能确定在两个值之间大约有多少行。如果将一个 MyISAM 表改为 HASH 索引的 MEMORY 表,会影响一些查询的执行效率。
  4. 只能使用整个关键字来搜索一行。

对于 BTREE 索引,当使用>、<、>=、<=、BETWEEN、!=或者<>,或者 LIKE ‘pattern’(其中’pattern’不以通配符开始)操作符时,都可以使用相关列上的索引
当对索引字段进行范围查询的时候,只有BTREE索引可以通过索引访问,而HASH索引实际上是全表扫描的

MySQL如何使用索引

MySQL中能够使用索引的经典场景

  1. 匹配全值,对索引中所有列都指定具体值,即是对索引中的所有列都有等值匹配的条件
  2. 匹配值的范围查询,对索引的值能够进行范围查找
  3. 匹配最左前缀,仅仅使用索引中的最左边列进行查找
  4. 仅仅对索引进行查询,当查询的列都在索引的字段中时,查询的效率更高
  5. 匹配列前缀,仅仅使用索引中的第一列,并且只包含索引第一列的开头一部分进行查询
  6. 能够实现索引匹配部分精确而其他部分进行范围索引
  7. 如果列名是索引,使用column_name is null就会使用索引
  8. MySQL5.6引入Index Condition Pushdown(ICP)特性,进一步优化查询,Pushdown表示操作下放,某些情况下的条件过滤操作下放到存储引擎

MySQL存在索引但不能使用索引的经典场景

  1. 以%开头的LIKE查询不能够利用B-Tree索引,执行计划中key的值为NULL表示没有使用索引
  2. 数据类型中出现隐式转换的时候也不能使用索引,特别是当列类型是字符串
  3. 复合索引的情况下,假如查询条件不包含索引列最左部分,即不满足最左原则Leftmost,是不会使用复合索引的
  4. 如果MySQL估计使用索引比全表扫描慢,则不使用索引
  5. 用or分割的条件,如果or前面的条件中的列有索引,而后面的列中没有索引,则涉及的列都不会被使用(因为or后面的条件列中没有索引,那么后面的查询走全表扫描,在存在全表扫描的情况下,没必要多一次索引扫描增加I/O访问,一次全表扫描过滤条件就足够了)

查看索引使用情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [(none)]> show status like 'Handler_read%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_deleted | 0 |
| Handler_read_rnd_next | 0 |
+--------------------------+-------+
8 rows in set (0.00 sec)

索引正在工作则Handler_read_key的值将很高,该值表示一个行被索引值读的次数,值很低表明增加索引得到的性能改善不高,因为索引并不经常使用.
Handler_read_rnd_next的值很高则意味着查询运行进行低效,并且应该建立索引补救.

视图

视图简介

视图是虚拟存在的表,对于使用视图的用户来说基本上是透明的。视图并不在数据库中实际存在,行和列数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的。
视图相对普通表优势:

  1. 简单,使用视图的用户完全不用关心后面对应的表的结构、关联条件和筛选条件,对用户来说是已经是过滤好的符合条件的结果集。
  2. 安全,使用视图的用户只能访问被允许查询的结果集,对表的权限管理并不能限制到某个行某个列,但是通过视图就可以简单地实现。
  3. 数据独立,视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图无影响;源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响。

视图操作

创建、修改视图

创建视图需要有CREATE VIEW的权限,对查询涉及的列有SELECT权限。
使用CREATE OR REPLACE 或 ALTER修改视图,需要该视图的DROP权限。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

ALTER
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

视图的可更新性和视图的定义有关系,以下类型视图是不可更新的:

  1. 包含一下关键字的SQL语句:聚合函数(SUM、MIN、MAX、COUNT等)、DISTINCT、GROUP BY、HAVING、UNION或者UNION NULL
  2. 常量视图
  3. SELECT中包含子查询
  4. JION
  5. FROM一个不能更新的视图
  6. WHERE子句的子查询引用了FROM子句中的表

[WITH [CASCADED | LOCAL] CHECK OPTION] 决定是否允许更新数据使记录不再满足视图的条件。
LOCAL只要满足本视图的条件就可更新。
CASCADED必须满足所有针对该视图的所有视图的条件才尅更新。
如果没有明确指定LOCAL或者CASCADED则默认为CASCADED

删除视图

有视图的DROP权限可一次删除一个或多个视图

1
DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT|CASCADE];

查看视图定义

  1. show tables; 查看表名也会展示视图名
  2. show table status [from db_name][like ‘pattern’]; 查看表状态也会展示视图状态
  3. show create iew view_name; 查看视图定义
  4. select * from information_schema.views where table_name=view_name; 通过系统表information_schema.views查看视图相关信息