MySQL优化十大技巧

2021/8/24 19:38:02

编程Tag： 优化数据 mysql 技巧查询索引存储 innodb 主键

本文主要是介绍MySQL优化十大技巧，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

MYSQL优化主要分为以下四大方面：

设计：存储引擎，字段类型，范式与逆范式

功能：索引，缓存，分区分表。

架构：主从复制，读写分离，负载均衡。

合理SQL：测试，经验。

一、存储引擎

在创建表的时候我们使用sql语句，Create table tableName () engine=myisam|innodb;

这里就指明了存储引擎是myisam还是innodb。存储引擎是一种用来存储MySQL中对象（记录和索引）的一种特定的结构（文件结构），处于MySQL服务器的最底层，直接存储数据。导致上层的操作，依赖于存储引擎的选择。地位如下图：

网络接口层：与客户端通信，比如传输数据等等。存储引擎层：存储数据的规则，方式。

本质：存储引擎就是特定的数据存储格式（方案）。

可以使用show engines命令来查看当前MySQL支持的存储引擎列表。

1、InnoDB存储引擎介绍

Mysql版本>=5.5 默认的存储引擎，MySQL推荐使用的存储引擎。支持事务，行级锁定，外键约束。事务安全型存储引擎。更加注重数据的完整性和安全性。

（1）存储格式

数据，索引集中存储，存储于同一个表空间文件中。

数据：记录行。索引：一种检索机制，也需要一定的空间，就相当于一本字典的目录。

示例：创建一个test数据库，新建一张student表，选择存储引擎为innodb, 然后打开mysql的data下的test目录，发现有以下3个文件。

其中db.opt存放了数据库的配置信息，比如数据库的字符集还有编码格式。student.frm是表结构文件，仅存储了表的结构、元数据(meta)，包括表结构定义信息等。不论是哪个表引擎都会有一个frm文件。student.ibd是表索引文件，包括了单独一个表的数据及索引内容。

如果往表里插入了新的数据，则在mysql的data目录下会生成ibdata1文件，这个文件是存储了所有innodb表的数据。

关于innodb引擎的详细介绍：

使用innodb引擎时，需要理解独立表空间、共享表空间。

独立表空间：每个表都会生成以独立的文件方式来存储，每个表都一个.frm的描述文件，还有一个.ibd文件。其中这个文件包括了单独一个表的数据及索引内容，默认情况下它的存储在mysql指定的目录下。

独立表空间优缺点

优点：

每个表都有自己独立的表空间；每个表的数据和索引都会存储在各个独立的表空间中；可以实现单表在不同的数据进行迁移；表空间可以回收（除了drop table操作，表空不能自己回收）；drop table 操作自动回收表空间，如果对统计分析或是日值表，删除大量数据后可以通过：alter table tablename engin=innodb进行回缩不用的空间；对于使用inodb-plugin的innodb使用truncate table会使用空间收缩。；对于使用独立表空间，不管怎么删除，表空间的碎片都不会太严重。

缺点：

单表增加过大，如超过100G。对于单表增长过大的问题，如果使用共享表空间可以把文件分开，但有同样有一个问题，如果访问的范围过大同样会访问多个文件，一样会比较慢。对于独立表空间也有一个解决办法是：使用分区表，也可以把那个大的表空间移动到别的空间上然后做一个连接。其实从性能上出发，当一个表超过100个G有可能响应也是较慢了，对于独立表空间还容易发现问题早做处理。

共享表空间：某一个数据库所有的表数据，索引文件全部都放在一个文件中，默认这个共享表空间的文件路径在data目录下，默认的文件名为 ibdata1,初始化为10M。

共享表空间优缺点

优点：可以将表空间分成多个文件存放在各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上），数据和文件放在一起方便管理。

缺点：所有的数据和索引存放到一个文件中，将来会是一个很大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对一个表做了大量删除操作后表空间将有大量的空隙，特别是对统计分析、日值系统这类应用最不适合用共享表空间。

如何开启独立表空间？

查看是否开启独产表空间：

mysql> show variables like '%per_table';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | OFF |

+-----------------------+-------+

设置开启：

在my.cnf文件中[mysqld] 节点下添加innodb_file_per_table=1

或者通过命令：set global innodb_file_per_table=1;

注：

innodb_file_per_table值来进行修改即可，但是对于之前使用过的共享表空间则不会影响，除非手动的去进行修改或者是

innodb_file_per_table=1 为使用独占表空间

innodb_file_per_table=0 为使用共享表空间

修改独占空表空间的数据存储位置

innodb_data_home_dir = "C:\mysql\data\"

innodb_log_group_home_dir = "C:\mysql\data\"

innodb_data_file_path=ibdata1:10M:autoextend

innodb_file_per_table=1

参数说明:

这个设置配置一个可扩展大小的尺寸为10MB的单独文件，名为ibdata1。没有给出文件的位置，所以默认的是在MySQL的数据目录内。【对数据来进行初始化的设置】

innodb_data_home_dir 代表为数据库文件所存放的目录

innodb_log_group_home_dir 为日志存放目录

innodb_file_per_table 是否使用共享以及独占表空间来

以上的几个参数必须在一起加入。

对于参数一些注意的地方

InnoDB不创建目录，所以在启动服务器之前请确认”所配置的路径目录”的确存在。这对你配置的任何日志文件目录来说也是真实的。使用Unix或DOS的mkdir命令来创建任何必需的目录。

通过把innodb_data_home_dir的值原原本本地部署到数据文件名，并在需要的地方添加斜杠或反斜杠，InnoDB为每个数据文件形成目录路径。

如果innodb_data_home_dir选项根本没有在my.cnf中提到，默认值是“dot”目录 ./，这意思是MySQL数据目录。

（2）数据按照主键顺序存储

插入时做排序工作，效率低。

（3）特定功能

事务、外键约束：都是为了维护数据的完整性。

并发性处理：

innodb擅长处理并发的。因为它使用了行级锁定，只该行锁了，其它行没有锁。

行级锁定：row-level locking，实现了行级锁定，在一定情况下，可以选择行级锁来提升并发性。也支持表级锁定，Innodb会自带锁，不需要我们自己设置。

多版本并发控制, MVCC，效果达到无阻塞读操作。

（4）总结：innodb擅长事务、数据的完整性及高并发处理，不擅长快速插入（插入前要排序，消耗时间）和检索。

2.MyISAM存储引擎介绍

MySQL<= 5.5 MySQL默认的存储引擎。

ISAM：Indexed Sequential Access Method(索引顺序存取方法)的缩写，是一种文件系统。

擅长与处理，高速读与写。

（1）存储方式

数据和索引分别存储于不同的文件中。

（2）数据的存储顺序为插入顺序（没有经过排序）

插入速度快，空间占用量小。

（3）功能

a.全文索引支持。（mysql>=5.6时innodb 也支持）

b.数据的压缩存储。.MYD文件的压缩存储。

压缩前，数据是25600KB：

进行压缩：使用工具 myisamPack完成压缩功能：该工具mysql自带

进入到需要压缩表的数据目录，执行压缩指令 myisampack 表名。配置环境变量。

压缩后：

注意，压缩后，需要重新修复索引：

查看结果，发现现在的数据变成12741KB了，比之前的更小了：

压缩优势：节省磁盘空间，减少磁盘IO开销。特点：压缩后的表变成了只读表，不可写。

如果需要更新数据，则需要先解压后更新。利用工具：myisamchk –unpack 表名进行解压

解压后，变成了原来的25600KB

刷新表的状态：flush table myisam_2

c.并发性：

仅仅支持表级锁定，不支持高并发。

支持并发插入。写操作中的插入操作，不会阻塞读操作（其他操作）

（4）关于Innodb 和myisam的取舍：

Innodb ：数据完整性，并发性处理，擅长更新，删除。

myisam：高速查询及插入。擅长插入和查询。

具体举例：

那么对于微博项目来看，选择哪一个存储引擎呢？

a.微博主要是插入微博和查询微博列表，较为适合MyISAM；

b.微博在更新微博和删除微博，要少的多，较为适合MyISAM；

c.对数据完整性的需求并没有那么强烈，比如用户删除微博，关联的转播和评论并不要求都做相应的行为，较为适合MyISAM；

那么对于记账财务系统，选择哪一款存储引擎呢？

a.财务系统除了读取和插入，经常要进行数据的修改和删除，较为适合InnoDB；

b.在进行财务变更的时候，如果失败需要回滚必须用到事务，较为适合InnoDB；

c.每个用户的财务数据完整性和同步性非常重要，需要外键支持，否则财务将会混乱，较为适合InnoDB。

3.其他存储引擎

（1）Archive：存档型，仅提供插入和查询操作。非常高效阻塞的插入和查询。

（2）Memory：内存型，数据存储于内存中，存储引擎。缓存型存储引擎。

（3）插件式存储引擎：用C和C++开发的存储引擎。

4.锁的概念：当客户端操作表（记录）时，为了保证操作的隔离性（多个客户端操作不能互相影响），通过加锁来处理。

操作方面：

读锁：读操作时增加的锁，也叫共享锁，S-lock。特征是阻塞其他客户端的写操作，不阻塞读操作。（并发读）

写锁：写操作时增加的锁，也叫独占锁或排他锁，X-lock。特征是阻塞其他客户端的读，写操作。

锁定粒度（范围）：

行级：提升并发性，锁本身开销大

表级：不利于并发性，锁本身开销小。

二、字段类型选择

字段类型应该要满足需求，尽量要满足以下需求。

尽可能小（占用存储空间少）、尽可能定长（占用存储空间固定）、尽可能使用整数。

1.列类型之数值

（1）整型

MySQL数据库支持五种整型类型，包括：TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT五种。

整型类型占用空间和取值范围

类型字节最小值最大值

TINYINT 1 有符号：-128 无符号：0 有符号：127 无符号：255

SMALLINT 2有符号：-32768无符号：0有符号：32767无符号：65535

MEDIUMINT 3有符号：-8388608无符号：0有符号：8388607无符号：16777215

INT/INTEGER 4有符号：-2147483648无符号：0有符号：2147483647无符号：4294967295

BIGINT 8 有符号：-9223372036854775808无符号：0 有符号：9223372036854775807无符号：18446744073709551615

五种整型的适用场景：

TINYINT，年龄，包含在0~255之间；

SMALLINT，端口号，包含在0~65535之间；

MEDIUMINT，中小型网站注册会员，1600万够用；

INT，身份证编号，42亿可以用很久；

BIGINT，Twitter微博量，几百亿

（2）浮点型(非精确)

MySQL数据库支持两种浮点类型：FLOAT(单精度)和DOUBLE(双精度)两种

浮点型(非精确)占用空间和取值范围

类型字节范围

FLOAT 4 正数范围：1.175494351E-38~3.402823466E+38，负数范围：-3.402823466E+38~-1.175494351E-38

DOUBLE 8 正数范围：1.7976931348623157E-308~2.2250738585072014E+308

负数范围：-2.2250738585072014E+308~-1.7976931348623157E-308

（3）定点型(精确)

浮点型由于内部的存储方式是数值，导致它在一定程度上取得的是近似值而非精确值。如果使用定点型，那么就可以精确取得小数部分，因为它内部存储方式是字符串形式。

定点型(精确)占用空间和取值范围

类型字节范围

DECIMAL/NUMERIC M+2 M最大65位，D最大30位。

创建一个定点型格式：DECIMAL(M,D)，表示小数点D位，整数部分M位及M位内。

2.列类型之日期

MySQL数据库中有五个可用的日期时间数据类型，分别为：DATE、DATETIME、TIME、YEAR、TIMESTAMP。

日期时间类型占用空间和取值范围

类型字节最小值最大值

YEAR 1 1901 2155

TIME 3 -838:59:59838:59:59

DATE 4 1000-01-01 9999-12-31

TIMESTAMP 4 1970-01-01 00:00:00 2038-01-19 03:14:07

DATETIME 8 1000-01-01 00:00:00 9999-12-31 23:59:59

TIMESTAMP有几个特点：

a.当更新一条数据的时候，设置此类型根据当前系统更新可自动更新时间；

b.如果插入一条NULL，也会自动插入当前系统时间；

c.创建字段时，系统会自动给一个默认值；

d.会根据当前时区来存储和查询时间，存储时对当前时区进行转换，查询时再转换为当前的时区。

//查看当前时区

SHOW VARIABLES LIKE 'time_zone';

//设置为东九区，查询时间就会加1小时

SET time_zone='+9:00';

DATE占用3个字节，包含年月日，范围和DATETIME一样。DATE长度是0，无法设置。

YEAR占用1个字节，包年年份，长度默认为4位，无法设置。

TIME占用3个字节，包含时分秒，长度0到6之间，用于设置微秒。对于TIME的范围的时是-838到838的原因，是因为TIME类型不但可以保存一天的时，还可以包含时间之间的间隔。

综上考虑：使用datetime，当然也可以使用int（11）来保存时间戳。

关于INT(11)存放时间戳的优点如下：

a.INT占4个字节，DATETIME占8个字节；

b.INT存储索引的空间比DATETIME小，查询快，排序效率高；

c.在计算机时间差等范围问题，比较方便。

3.列类型之字符

字符集校对规则utf8_general_ci表示校对时不区分大小写，相对的cs表示区分大小写。还有一个bin结尾的是字节比较。而general是地区名，这里是通用，utf8表示编码。如果是gbk，可以使用gbk_chinese_ci，如果是utf8则用utf8_general。MySQL提供了多种对字符数据的存储类型，包括：CHAR、VARCHAR、VARBINARY、BLOB、TEXT、ENUM和SET等多种字符类型。

（1）CHAR是保存定长字符串，而VARCHAR则是保存变长字符串。CHAR(5)表示必须保存5个字符，而VARCHAR(5)则表示最大保存字符为5。如果是UTF8编码下，长度为5的CHAR类型，最多可以存储15字节，也就是5个汉字的内容。因为一个汉字占3个字节。

由于CHAR类型是定长，MySQL会根据定义的长度进行分配空间，在处理速度上比VARCHAR快的多，所以适合存储例如手机、身份证这种定长的字符，否则就会造成浪费。那么CHAR类型最大可以插入255个字符，最多可以存储765个字节。

（2）BINARY和VARBINARY是采用二进制存储的，没有字符集概念，意义在于防止字符集的问题导致数据丢失，存储中文会占用两个字符，会乱码，半截会问号。因为是采用二进制存储，在比较字符和排序的时候，都是二进制进行的，所以只有需要操作二进制时才需要使用。

（3）八种适合文本内容的大数据类型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOG、BLOB、MEDIUMTEXT、LONGTEXT。

综上：短文本定长用char，变长用varchar，长文本用text

4.列类型之属性

无符号(UNSIGNED)和填充零(ZEROFILL)，还有是否为空、默认值、主键、自动编号。

严格模式

我们使用的是WAMP集成环境，默认安装的情况下，是非严格模式，用于部署阶段。而开发调试阶段，强烈建议使用严格模式，方便开发中调试将问题及时暴露出来。因为在非严格模式下将NULL插入NOTNULL等非法操作都是被运行的。设置严格模式只要打开my.ini文件，在末尾添加一句：

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

然后，重启服务器即可。检查SQL_MODE状态

SELECT @@global.sql_mode;

三、范式与逆范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

第一范式1NF，原子性

第二范式2NF，消除部分依赖

第三范式3NF，消除传递依赖

1、范式

（1）第一范式：具有原子性，确保每列保持原子性。

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式。
（2）第二范式：主键列与非主键列遵循完全函数依赖关系，确保表中的每列都和主键相关。

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
（3）第三范式：非主键列之间没有传递函数依赖关系索引，确保每列都和主键列直接相关,而不是间接相关。

所谓传递函数依赖，指的是如果存在"A→B→C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：

关键字段→非关键字段x→非关键字段y

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

先满足第一范式，再满足第二范式，才能满足第三范式。

2、逆范式

逆范式是指打破范式，通过增加冗余或重复的数据来提高数据库的性能。

示例：假如有一个商品表Goods：

字段有Goods_id(商品表), goods_name(商品名称), cat_id(所属类别的id)。

还有一个分类表Category：

字段有Cat_id(类别id), cat_name(类别名称)。

现在要查询类别id为3的商品的数量，例如分类列表查询：

分类ID 分类名称商品数量

3 计算机 567

可以使用下列sql语句：

Select c.*, count(g.goods_id) as goods_count from category as c left join goods as g c.cat_id=g.cat_id group by c.cat_id;

但是，假如商品数量较大，那么就比较耗性能了。这时，我们可以考虑重新设计Category表：增加存当前分类下商品数量的字段。

Cat_id, cat_name, goods_count

每当商品改动时，修改对应分类的数量信息。

再查询分类列表时：Select * from category;

此时额外的消耗，出现在维护该字段的正确性上，保证商品的任何更新都正确的处理该数量才可以。

四、索引

1．索引概述

利用关键字，就是记录的部分数据（某个字段，某些字段，某个字段的一部分），建立与记录位置的对应关系，就是索引。索引的关键字一定是排序的。索引本质上是表字段的有序子集，它是提高查询速度最有效的方法。一个没有建立任何索引的表，就相当于一本没有目录的书，在每次查询时就会进行全表扫描，这样会导致查询效率极低、速度也极慢。如果建立索引，那么就好比一本添加的目录，通过目录的指引，迅速翻阅到指定的章节，提升的查询性能，节约了查询资源。

测试查询，添加索引前后比对执行时间：

2．索引种类

从索引的定义方式和用途中来看：主键索引，唯一索引，普通索引，全文索引。

无论任何类型，都是通过建立关键字与位置的对应关系来实现的。索引是通过关键字找对应的记录的地址。

以上类型的差异：对索引关键字的要求不同。

关键字：记录的部分数据（某个字段，某些字段，某个字段的一部分）。

普通索引,index：对关键字没有要求。

唯一索引,unique index：要求关键字不能重复。同时增加唯一约束。

主键索引,primary key：要求关键字不能重复，也不能为NULL。同时增加主键约束。

全文索引,fulltext key：关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词。

关键字含义：可以是某个字段，也可以是某些字段。如果一个索引通过在多个字段上提取的关键字，称之为复合索引。命令:alter table exp add index (field1, field2);

PS：这里主键索引和唯一索引的区别在于：主键索引不能为空值，唯一索引允许空值；主键索引在一张表内只能创建一个，唯一索引可以创建多个。主键索引肯定是唯一索引，但唯一索引不一定是主键索引。

3.索引操作

(1)创建主键索引

创建一个无符号整型且自动增长的列，然后设置成主键即可。

//通过EXPLAIN语句查看索引状态

EXPLAIN SELECT * FROM think_user WHERE id=1;

(2)创建普通或唯一索引

直接进入navicat设计表的第二栏，选择一个字段(比如user字段)，添加一个Nomral(普通索引)或Unique(唯一索引)。

//通过EXPLAIN语句查看索引状态

EXPLAIN SELECT * FROM think_user WHERE user='蜡笔老新';

//查看表所有索引情况

SHOW INDEX FROM think_user;

（3）使用sql语句的方式建立索引----建表时就创建索引

注意：索引可以起名字，但是主键索引不能起名字，因为一个表仅仅可以有一个主索引，其他索引可以出现多个。名字可以省略，mysql会默认生成，通常使用字段名来充当。

（4）使用sql语句的方式建立索引----更新表时创建索引

注意：如果表中存在数据，数据符合唯一或主键的约束才可能创建成功。auto_increment属性，依赖于一个KEY。

（5）使用sql语句的方式删除索引，auto_increment依赖于KEY。

（6）Explain 执行计划

可以通过在select语句前使用 explain，来获取该查询语句的执行计划，而不是真正执行该语句。

删除索引时，再看执行计划：

从查询的行数可知，有索引时查询会快的多，因为它只需要查找一行，而没有索引时，会造成全表扫描。

注意：select语句才能获取到执行计划。（新版本5.6会扩展其他语句的执行计划的获取）

4.索引原则

如果索引不遵循使用原则，则可能导致索引无效。

（1）列独立

如果需要某个字段上使用索引，则需要在字段参与的表达中，保证字段独立在一侧。

第三个语句 empno-1就不是列独立：就不能用索引。类似函数等等。（write_time < unix_timestamp()-$gc_maxlifetime）

其他两个列独立可以使用：

（2）左原则

Like：匹配模式必须要左边确定不能以通配符开头。

假如业务逻辑上出现: field like ‘%keywork%’;类似查询，需要使用全文索引。

复合索引：一个索引关联多个字段，仅仅针对左边字段有效果。

示例：添加复合索引

对Ename的查询，使用了索引，结果如下：

Empno的查询没有使用索引，结果如下：

（3）OR的使用

必须要保证 OR 两端的条件都存在可以用的索引，该查询才可以使用索引。

为后面的条件增加可以使用的索引后，再查看执行计划：

（4）MySQL智能选择

即使满足了上面说原则，MySQL也能弃用索引：如下图

弃用索引的主要原因：

查询即使使用索引，会导致出现大量的随机IO，相对于从数据记录的第一条遍历到最后一条的顺序IO开销，还要大。

综上归纳：

a、不要过度索引。索引越多，占用空间越大，反而性能变慢；

b.只对WHERE子句中频繁使用的建立索引；

c.尽可能使用唯一索引，重复值越少，索引效果越强；

d.使用短索引，如果char(255)太大，应该给它指定一个前缀长度，大部分情况下前10位或20位值基本是唯一的，那么就不要对整个列进行索引；

e.充分利用左前缀，这是针对复合索引，因为WHERE语句如果有AND并列，只能识别一个索引(获取记录最少的那个)，索引需要使用复合索引，那么应该将WHERE最频繁的放置在左边。

f.索引存在，如果没有满足使用原则，也会导致索引无效：

5.索引的使用场景

（1）索引检索：检索数据时使用索引。

（2）索引排序

如果order by 排序需要的字段上存在索引，则可能使用到索引。

例如，按照ename字段排序查询：

此时，没有任何索引。在ename字段上建立索引后：

不会用到查询检索索引是因为没有用where条件查询，而真实执行时，就会用到排序索引。

Tip：对比以上两个执行计划：

extra位置：

其中：extra额外信息。加了索引后就不用使用文件排序了。

Using filesort，表示使用文件排序（外部排序，内存外部）。

（3）索引覆盖

索引拥有的关键字内容，覆盖了查询所需要的全部数据，此时，就不需要在数据区获取数据，仅仅在索引区即可。覆盖就是直接在索引区获取内容，而不需要在数据区获取。

例如，利用名字检索：

可以在ename字段建立索引：

分析执行：

再增加一个索引：

完成相同的查询：

查询的字段刚好是复合索引包含的字段。所以就使用了复合索引。

说明，不是非要查询用到，才可以索引覆盖，只要满足要求都可以覆盖！

建立索引索引时，不要仅仅考虑where检索，同时考虑其他的使用场景。（在所有的where字段上增加索引，就是不合理的）

6.前缀索引

前缀索引是建立索引关键字一种方案。通常会使用字段的整体作为索引关键字。有时，即使使用字段前部分数据，也可以去识别某些记录。就比如一个班级里，我要找王xx，假如姓王的只有1个人，那么就可以建一个前缀索引，就是王。

语法：

Index `index_name` (`index_field`(N))使用index_name前N个字符建立的索引。

那么N究竟是多少？使用N长度所达到的辨识度，极限接近于使用全部长度的辨识度即可！

先计算最大的辨识度M：

公式：先计算总的记录数m，再求该字段不重复的记录数q，那么M=m/q。然后依次取得前N个字符，N逐步增加，进行对比，直到找到极限接近于M的，那么最后的N就是我们要找的N。

求得辨识度为1.4774.，也就是说一个前缀索引可以对应1.4774条记录。

然后依次取得前N个字符，进行对比，找到极限接近的：

可见，9 时，已经极限接近，提高长度，不能明显提升辨识度，因此可以使用前9个字符：

Tip：前缀索引不能用于索引覆盖！

7.全文索引

该类型的索引特殊在：关键字的创建上。是为了解决 like‘%keyword%’这类查询的匹配问题。（mysql的全文索引几乎不用，因为它不支持中文，我们应该使用sphinx全文索引）。

示例：

假如有一张表，表中有标题和内容两个字段，现在要查询标题或者内容包含 “database” 关键字的记录。

补充：text和varchar的区别是text的数据不存在记录里，一条记录的最大空间是65535.

形成的SQL如下：

Select * from articles where title like ‘%database%’ or body like ‘%database%’;

此时不能建立普通索引，查询不符合左原则，建立了也使用不了。

此时全文索引就可以发挥其作用了：

直接使用上面的SQL，需要使用特殊的全文索引匹配语法才可以生效： Match() against();

Tip: 该MYSQL提供的全文索引，不能对中文起作用！

使用Match() against() 返回关键字的匹配度（关键字与记录的关联程度）。

停止词 in：

发现in这个词，是不能被全文索引所检索到的。因为in这个词是不可以用在全文索引的关键词里的，没有谁会在一段文本里检索这样一个词。

思考：与 like %in% 是否相同？不同。

原因何在呢？全文索引，索引的的关键字，不是整个字段数据，而是从数据中提取的关键词。

8.索引结构-b-tree介绍

Hash、B-Tree（B树）两种数据结构。指的是mysql存储索引所采用的数据结构。其中，用户所维护的所有的索引结构 B-Tree结构。

B-Tree的结构如下：

每个节点，存储多个关键字。关键字也会对应记录地址

以上设计为了解决一次性磁盘IO开销，可以读取到更多的关键字数量。

每个关键字之间，存在子节点指针：

如果是复合索引：

关键字的排序先排左侧字段，在左侧字段相同的情况下，再排序右侧字段：

9.聚集索引(聚簇索引)

B+Tree(B-Tree的变种)

在innodb的存储引擎上，主键索引是与数据记录存储在一起的（聚簇在一起的）。

带来的问题：

Innodb的其他索引，非主键索引（二级索引）：

关键字对应的不再是记录的地址，而是记录的主键。

可见，检索需要二次检索。先检索到主键ID，再检索记录。

五、查询缓存query_cache

将select的结果，存取起来共二次使用的缓存区域：

MySQL提供的缓存区：

未开启前：

两次查询时间消耗一致。

开启查询缓存，通过变量控制：

开启并设置大小：

再次执行查询：

可见，第二次查询，使用了开启的缓存！

注意事项：查询缓存存在判断是严格依赖于select语句本身的：严格保证SQL一致。

如果查询时包含动态数据，则不能被缓存。

一旦开启查询缓存，MySQL会将所有可以被缓存的select语句都缓存。如果存在不想使用缓存的SQL执行，则可以使用 SQL_NO_CACHE语法提示达到目的：

注意：这里的缓存仅当数据表的记录改变时，缓存才会被删除。而不是依靠过期时间的。

六、分区分表

日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。

分区，partition，分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

其实每个分区，就是独立的表。都要存储该分区数据的数据，索引等信息。

创建分区：在创建表时，指定分区的选项：

Create table table_name (定义)

Partition by 分区算法 (参数) 分区选项。

例如：Partition by key (id) partitions 5;

采用key取余算法，根据id的值进行取余，即对5取余，然后分配到5个区里。

分区结果如下：myisam下

Innodb下

Tip：分区与存储引擎无关，是MySQL逻辑层完成的。

可以通过变量查看当前mysql是否支持分区：

1.分区算法

MySQL提供4种分区算法：取余：Key，hash 条件：List，range 。

参与分区的参数字段需要为主键的一部分。

（1）KEY – 取余，按照某个字段进行取余

分成5个区，就是对5取余。将id对5取余。

（2）Hash – 取余，按照某个表达式的值进行取余

示例：学生表分区，按照生日的月份，划分到12个表中。

注意：Key，hash都是取余算法，要求分区参数（括号里的），返回的数据必须为整数。

（3）List – 条件 – 列表，需要指定的每个分区数据的存储条件。

示例：按照生日中的月份，分成春夏秋冬四个分区。

List，条件依赖的数据是列表形式。

（4）Range - 条件 – 范围，条件依赖的数据是一个条件表达式。

逻辑：按照生日的年份分成不同的年龄段。

2.分区的管理与选择

（1）取余：key，hash

增加分区数量： add partition partitions N

减少分区数量： COALESCE partition N

采用取余算法的分区数量的修改，不会导致已有分区数据的丢失，因为会重新分配数据到新的分区。

（2）条件：list，range

添加分区

删除分区：

Drop partition partition_name;

注意：删除条件算法的分区，会导致分区数据丢失。添加分区不会。

（3）选择分区算法

平均分配：就按照主键进行key(primary key)即可(非常常见)

按照某种业务逻辑分区：选择那种最容易被筛选的字段，整数型

3.分表

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。（需要手动分表）

分表是分区之前用的，MYSQL5.1后，就开始用分区代替分表了。分表很少用了。

（1）水平分表

创建结构相同的N个表；

再创建用于管理学生ID的表student_id：(该表是为了提供自增的ID)

PHP客户端逻辑：

Merge，mrg_myisam

是MySQL提供一个可以将多个结构相同的myisam表，合并到一起的存储引擎：

（2）垂直分表

一张表中存在多个字段。这些字段可以分为常用字段和非常用字段，为了提高查表速度，我们可以把这两类字段分开来存储。主要目的，减少每条记录的长度。

通常我们按以下原则进行垂直拆分:把不常用的字段单独放在一张表;把text，blog等大字段拆分出来放在附表中;经常组合查询的列放在一张表中;

例如学生表可以分成：

基础表(Student_base)和额外表(Student_extra)，两张表中记录为1:1的关系。

基础信息表Student_base

Id name age

额外信息表Student_extra

Id 籍贯政治面貌

七、服务器架构介绍

服务器架构，不仅仅是用一台MySQL

主从复制：

Mysql服务器内部支持复制功能，仅仅需要通过配置完成下面的拓扑结构。一主多从典型结果：主服务器负责写数据。从服务器负责读数据。复制功能mysql会自带。

读写分离，负载均衡：

php不再操作MYSQL数据库服务器，而是去操作读写分离、负载均衡服务器，只要服务器安装了mysql proxy或Ameoba软件就可以实现读写分离和负载均衡，读写分离是指该服务器会判断客户端的操作是读还是写，从而选择操作mysql主服务器还是从服务器。负载均衡算法是指，客户端读操作时，该服务器会根据取余算法去选择一台从服务器。