第4章 MySQL 性能调优
4.1 数据表设计分析
一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。下面内容中对于表设计会有一些规范会被介绍到，但是对于基于性能的数据库设计，我们并不能完全以规范化范式理论来作为唯一的指导。在设计过程中，应该从实际需求出发，以性能提升为根本目标来展开设计工作，很多时候为了尽可能提高性能，我们必须做
反范式设计
。 4.1.1 三大范式 在设计关系数据库的时候，一般来说我们都是需要遵从不同的规范要求来设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
范式
可以分为 3 大范式、BC 范式、第四范式和第五范式 ，共六大范式。通常来说满足三大范式就基本足够 。但是需要注意的是数据库的优化并非只有结构的设计，所以项目的数据库设计并不一定要完全满足与三大范式，有时候会适量的冗余让 Query 尽量减少 Join。
1. 第一范式
第一范式（1NF）
要求关系中的属性必须是原子项，即不可再分的基本类型
。集合、数组以及结构不能作为某一属性出现，严禁关系中出现 “ 表中有表 ” 的情况。在任何一个关系数据库系统中，第一范式是关系模式的一个最起码的要求。不满足第一范式的数据库模式不能称为关系数据库。 例 - 原始表：
注意：原始表中”工程地址”列还可以细分为省份，城市等。在国外，更多的程序把 ” 姓名 ” 列也分成 2 即 ” 姓 ” 和 “ 名 ”。虽然第一范式要求各列要保存原子性，不能再分，但是这种要求和我们的需求是相关联的，上例中 ”工程地址” 就可以不用再分。
2. 第二范式
第二范式（2NF）是在第一范式的基础建立起来的，即满足第二范式就必须要满足第一范式。第二范式
要求非主键字段完全依赖主键
。 所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。 例 - 原始表中描述了工程信息，员工信息等不符合第二范式的结构，这样就造成了大量数据的重复。 可以做以下改动： （1）工程信息表
（2）员工信息表
3. 第三范式
第三范式（ 3NF ）是第二范式的子集，即满足第三范式就必须满足第二范式，其含义为
任何非主属性不依赖于其它非主属性（ 在2NF基础上消除传递依赖 ）
。第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。 在第二范式中，我们对原始表做了修改，现在可以看看该表是否符合第三范式。从事实出发考虑，薪资是根据人来定的还是根据职务来定的呢？很明显是根据职务，所以 “ 员工信息表 ” 中 “ 薪资水平 ” 是通过 “职务” 间接依赖于主键。根据第三范式要求，我们应该移除这种依赖，可以做如下改动 ：
通过对比我们发现，表多了、关系复杂了、查询数据变的麻烦了、编程中的难度也提高了，但是各个表中内容更清晰了、重复的数据少了、更新和维护变的更容易了。 4.1.2 表字段分析 在设计数据库的过程中，我们要以
“ 够用原则 ”
选择合适的类型。如果一味的把数据字段范围设为最大或者默认值，会导致存储空间大量浪费，在数据量特别大的情况下，这样的设计将会造成数据库空间的严重浪费，也会对数据库的执行效率造成很大的影响。所以我们在做数据库设计的时候要谨慎选择，“ 只要最合适，不要最贵 ”。 在选择不同的类型前，需要了解不同类型的特点，接下来以字符串类型、数字类型以及时间类型为主说明。
1. 字符串类型
char 和 varchar 都是用来存储字符串类型的数据，但是他们保存和检索的方式不一样。
char 属于固定长度的字符类型，varchar 属于可变长的字符类型
。 gbk 字符集：1个字符，2个字节。
utf8 字符集：1个字符，3个字节。
由于
char
是固定长度的，所以它的处理速度比
varchar
快得多，但是其缺点是浪费存储空间，程序需要对尾行空格进行处理。所以对那些变化不大并且查询速度有较高要求的数据，可以考虑使用 char 类型来存储。尽量少用 text 等大的类型，如果感觉非用不可的话，则可以考虑分表了。
不同存储引擎的使用原则
在 MySQL 中，不同的存储引擎对 char 和 varchar 的使用原则有所不同，如下 ：
MyISAM
存储引擎： 建议使用固定长度的数列代替可变长度的数据列。
InnoDB
存储引擎： 建议使用 varchar 类型，对于 InnnoDB 数据表，内部的行存储格式没有区分固定长度和可变长度，因此使用 char 列不一定比可变长度的 varchar 性能好。
由于 char 平均占用空间多于 varchar ，因此 varchar 来处理的数据行的存储总量和磁盘 I/O 是比较好的。
另外在
添加字段时尽量不要使用 null
，如果实在有字段没有值，可以添加默认值，例如 0。
2. 数值类型
一般情况下 id 必为主键，类型为 int / bigint unsigned，单表时自增步长为 1。需要注意的是一些表因为数据量的关系导致主键会很大，可能会超出 int 的范围这个时候就比较建议使用 bigint 通常 int 即可。 注意：当一个表数据量超过了 500万 或者单表容量超过 2GB 的时候，推荐分库分表。这一步操作是需要事先对于数据量在项目上线之后的思考点。
unsigned 属性标识将数字类型无符号化，如果确定没有负数，可以使用unsigned 类型，让可存储的上限能扩大一倍，间接的使用更小的数据类型。另外 status、sexuality 相对来说在系统中我们更愿意使用数字作为表示，代替实际该字段所传单的意思，因为 char 所占用 的 3 个字节，相对来说比较适合于unsigned tinyint。 浮点数 float 在储存空间及运行效率上要优于精度数值类型 decimal，但 float 与 double 会有舍入错误而 decimal 则可以提供更加准确的小数级精确运算不会有错误产生计算更精确，适用于金融类型数据的存储。
实数精度要求不高情况，优先使用浮点类型 float 或 double。如果精度要求高，可考虑用整型转化或直接使用decimal类型。
3. 时间类型
默认情况下，当 MySQL 遇到超出范围的日期或时间类型的值或该类型的其他无效值时，它会将该值转换为 "零"。唯一的例外是超出了范围，TIME 值被裁剪到 TIME 范围。
（1）DATETIME DATETIME 用于表示 年月日 时分秒，是 DATE 和 TIME 的组合，并且记录的年份比较长久。如果实际应用中有这样的需求，就可以使用 DATETIME 类型。 DATETIME 列可以设置为多个，默认可为 null，可以手动设置其值。
DATETIME 列不可设定默认值。
DATETIME 列可以变相的设定默认值，比如通过触发器、或者在插入数据时候，将 DATETIME 字段值设置为 now( )，这样可以做到了，尤其是后者，在程序开发中常常用到。
（2）TIMESTAMP TIMESTAMP 用于表示年月日时分秒，但是记录的年份比较短暂。TIMESTAMP 列必须有默认值，默认值可以为 “ 0000-00-00 00:00:00 ”，但不能为 null。 TIMESTAMP 和时区相关，更能反映当前时间。当插入日期时，会先转换为本地时区后再存放。当查询日期时，会将日期转换为本地时区后再显示，所以不同时区的人看到的同一时间是不一样的。 表中的第一个 TIMESTAMP 列自动设置为系统时间（CURRENT_TIMESTAMP）。当插入或更新一行，但没有明确给 TIMESTAMP 列赋值，也会自动设置为当前系统时间。如果表中有第二个 TIMESTAMP 列，则默认值设置为 0000-00-00 00:00:00。TIMESTAMP 的属性受 Mysql 版本和服务器 SQLMode 的影响较大。如果记录的日期需要让不同时区的人使用，最好使用 TIMESTAMP。 注：一般建表时候，创建时间用 DATETIME，更新时间用 TIMESTAMP。
（3）DATE DATE 用于表示 年月日，如果实际应用值需要保存 年月日 就可以使用 DATE。 （4）TIME TIME 用于表示 时分秒，如果实际应用值需要保存 时分秒 就可以使用 TIME。 （5）YEAR YEAR 用于表示 年份，YEAR 有 2 位（最好使用 4 位）和 4 位格式的年（默认是 4 位）。如果实际应用只保存年份，那么 用 1 bytes 保存 YEAR 类型完全可以。不但能够节约存储空间，还能提高表的操作效率。 如果对于时间方面的运用要求不高，在考虑性能优先的情况下可以考虑使用 int 类型来存储时间。
4.1.3 不推荐的数据类型
1. 二进制多媒体数据
二进制多媒体数据将二进制多媒体数据存放在数据库中，一个问题是数据库空间资源耗用非常严重，另一个问题是这些数据的存储很消耗数据库主机的 CPU 资源。这种数据主要包括图片，音频、视频和其他一些相关的二进制文件。这些数据的处理本不是数据的优势，如果我们硬要将他们塞入数据库，肯定会造成数据库的处理资源消耗严重。
2. 流水队列数据
流水队列数据我们都知道，数据库为了保证事务的安全性（支持事务的存储引擎）以及可恢复性，都是需要记录所有变更的日志信息的。而流水队列数据的用途就决定了存放这种数据的表中的数据会不断的被 INSERT、UPDATE 和 DELETE，而每一个操作都会生成与之对应的日志信息。 在 MySQL 中，如果是支持事务的存储引擎，这个日志的产生量更是要翻倍。而如果我们通过一些成熟的第三方队列软件来实现这个 Queue 数据的处理功能，性能将会成倍的提升。
3. 超大文本数据
超大文本数据对于 5.0.3 之前的 MySQL 版本，VARCHAR 类型的数据最长只能存放 255 个字节，如果需要存储更长的文本数据到一个字段，我们就必须使用 TEXT 类型（最大可存放64KB）的字段，甚至是更大的 LONGTEXT 类型（最大 4GB ）。 TEXT 类型数据的处理性能要远比 VARCHAR 类型数据的处理性能低下很多。从 5.0.3 版本开始，VARCHAR 类型的最大长度被调整到 64KB 了，但是当实际数据小于 255Bytes 的时候，实际存储空间和实际的数据长度一样，可一旦长度超过 255Bytes 之后，所占用的存储空间就是实际数据长度的两倍。对于图片的存储，如果说是特殊情况可以使用 BLOB，但是通常来说跟推荐使用 varchar 存图片路径，而图片会放在一个文件夹中。 4.2 慢查询日志分析 MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。long_query_time 的默认值为 10，意思是运行 10S 以上的语句。 默认情况下， Mysql 数据库并不启动慢查询日志，需要我们手动来设置这个参数。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。 4.2.1 慢查询相关参数
1. slow_query_log - 是否开启慢查询日志
可以通过 show variables like "%slow_query_log%" 命令查看相关信息 ： mysql> show variables like "%slow_query_log%"; +---------------------+---------------------------------+ | Variable_name | Value | +---------------------+---------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /www/server/data/mysql-slow.log | +---------------------+---------------------------------+ slow_query_log OFF 代表慢查询日志是禁用的。
2. 慢查询日志存储路径
从上面的运行结果可以看到 slow_query_log_file （5.6及以上版本）的值，该值即为 MySQL 数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件 host_name_slow.log。如果 MySQL 版本在 5.6 以下版本，则是 log_slow_queries。
3. long_query_time - 慢查询阈值
long_query_time 是慢查询的超长时间，当超过这时间时记录日志。可以通过 show variables like "long_query_time" 命令查看该信息，默认为 10。 mysql> show variables like "long_query_time"; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+
4. log_queries_not_using_indexes - 未使用索引记录
log_queries_not_using_indexes 参数主要是用来设置未使用索引的查询也是否记录到慢查询日志中。使用 log_queries_not_using_indexes 命令查看，默认关闭 ： mysql> show variables like "log_queries_not_using_indexes"; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | OFF | +-------------------------------+-------+
5. log_output - 日志存储方式
log_output 表示日志的存储方式，log_output='FILE' 表示将日志存入文件，log_output='TABLE' 表示将日志存入数据库，这样日志信息就会被写入到 mysql.slow_log 表中，默认值是 'FILE'。 MySQL 数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。 4.2.2 慢查询日志的启动与设置 慢查询日志默认是关闭的，可以使用 set global slow_query_log = 1 命令来开启 ： mysql> set global slow_query_log=1; # 启用慢查询，需要加上 global，不然会报错 需要注意的是，这种方式只对当前数据库生效，如果 MySQL 重启后则会失效。如果要永久生效，就必须修改配置文件 my.cnf[linux] / my.ini[windows]（其它系统变量也是如此）： slow_query_log = on # 开启日志; slow_query_log_file = /www/server/data/mysql_slow.log # 记录日志的log文件 long_query_time = 1 # 最长查询的秒数; 注意 ： 以上参数需要加入到配置文件的 [mysqld] 组中。
慢查询日志的存储路径如果不指定，将默认存储到 MySQL 数据库的数据文件夹下。
慢查询日志文件名称如果不指定，默认文件名为 hostname-slow.log，hostname 是 MySQL 服务器的主机名。
long_query_time 默认值为 10，单位为 秒，日志只会记录大于的部分（等于也不记录）。
如果慢查询日志提示 ‘Permission denied’，可以切换目录存放日志文件。
4.2.3 慢查询使用 修改了慢查询日志的阀值为 1 后，可以使用 sleep 函数来测试慢查询日志 ： mysql> select sleep(5); +----------+ | sleep(5) | +----------+ | 0 | +----------+ 1 row in set (5.01 sec) mysql> show status like '%slow%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | Slow_launch_threads | 0 | | Slow_queries | 1 | +---------------------+-------+ 2 rows in set (0.00 sec) sleep 函数会在休眠 n 秒后执行，通过 show status like '%slow%' 命令可以查看到 Slow_queries 的值为 1，表示一共有一条慢查询，详细的可以查看对应的慢查询日志。
慢查询分析工具
当数据比较多，慢查询日志记录的数据也比较多，这个时候人工的分析明显是不实际的。这个时候需要借助慢查询分析工具 - mysqldumpslow。该工具是慢查询自带的分析慢查询工具，一般只要安装了 mysql，就会有该工具。 通过 --help 可以查看工具帮助 ： [root@localhost ~]# cd /usr/local/mysql/bin [root@localhost bin]# ./mysqldumpslow --help Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] ...... 常用选项： -s, 是表示按照何种方式排序: c: 访问计数 l: 锁定时间 r: 返回记录 t: 查询时间 al:平均锁定时间 ar:平均返回记录数 at:平均查询时间 -t, 是top n的意思，即为返回前面多少条的数据； -g, 后边可以写一个正则匹配模式，大小写不敏感的； 例： mysqldumpslow -s r -t 10 /www/server/data/mysql-slow.log # 得到返回记录集最多的10个SQL。 mysqldumpslow -s c -t 10 /www/server/data/mysql-slow.log #得到访问次数最多的10个SQL mysqldumpslow -s t -t 10 -g “left join” /www/server/data/mysql-slow.log #得到按照时间排序的前10条里面含有左连接的查询语句。 mysqldumpslow -s r -t 20 /www/server/data/mysql-slow.log | more # 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。 4.3 索引解析 在介绍数据库优化的资料中，基本都会提到索引，甚至很多资料第一个讲的就是索引。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大的时候，索引对性能的影响愈发重要。 4.3.1 索引简介 索引（Index）是
存储引擎用于快速找到记录的一种数据结构，也就是说索引的本质是数据结构
。在 MySQL 中的索引主要分为 B-Tree 索引、哈希索引、数据空间索引(R-Tree)、全文索引等等。我们重点关注的索引是 B-Tree 索引，如果没有特殊说明，一般的索引指的都是 B-Tree 索引。 索引种类众多的原因是因为索引是在存储引擎层实现的，而不是在服务器层实现的，因此没有统一的索引标准。不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。
索引优势
MySQL 索引的建立对于 MySQL 的运行效率是很重要的，索引可以大大提高 MySQL 的检索速度。如果说没有索引的 MySQL 是你的滑板，那么加了索引的 MySQL 就是'柯南'的滑板了。为什么会这么快呢？你可以想象你看一本有目录并且有书签的书和一本除只有正文的书。
索引缺点
使用索引也并不是一定就都是好的，如果索引使用不当，反而会适得其反。创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。所以在使用索引时，需要综合考虑。 4.3.2 创建索引
1. 索引种类与创建
索引主要可以分为主键索引、唯一索引以及普通索引这几种，其创建方式分别为 ： 主键索引：即 primary key。它是一种特殊的唯一索引，不允许有空值。可以使用 primary key ( 字段 1,字段 2....) 或者是在字段创建完毕之后用 primary key 来修饰创建。
唯一索引：即用 unique 修饰的列。其特点为索引列的值必须唯一，但允许有空值。在创建完字段之后使用 unique 修饰即可。
普通索引：即 index 或者说是 key，通过这种方式也可以创建两种 ：
1）单列索引：即索引的列只有一个，但是一个表里面可以有多个单列索引。
2）组合索引：即索引的列有多个，注意多个列组成的索引和多个索引列是不一样的。
如果对多列进行索引 (组合索引)，列的顺序非常重要，MySQL 仅能对索引最左边的前缀进行有效的查找。例如 ： 创建索引 `idx_c12(c1,c2)`， select * from t1 where c1=1 and c2=2; #能够使用该索引。 select * from t1 where c1=1; # 也能够使用该索引。 # 注意： select * from t1 where c2=2; # 不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。
2. 创建表时创建索引
mysql> create table t1 -> ( -> id int primary key, # 创建主键 -> uid int unique, # 创建唯一键 -> name varchar(20), -> index(name) # 创建普通索引 -> ); 创建完毕后，通过 ‘ show create table 表名 ’ 查看表的创建详情 ： mysql> show create table t1 \G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL, `uid` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uid` (`uid`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 注意：唯一索引和普通索引都有自己的名字，主键索引因为一个表只允许有一个主键，不需要彼此区分就不需要了。
3. 表后创建索引
语法： 普通索引：create index 索引名 [索引类型] on 表名(列名列表) 唯一索引：create unique index 索引名 [索引类型] on 表名(列名列表) # 注意，这种方式无法创建主键索引。 # 创建表，但不创建索引 create table t2 ( id int, uid int, mid int, tid int ); # 创建索引 create unique index idx_uid on t2(uid); # 唯一索引 create index idx_mt on t2(mid,tid); # 创建普通索引，并且是多字段 # 通过 show create table t2 可以查看表的信息。 删除索引使用 DROP INDEX < 索引名 > ON < 表名 > 即可。
4. 查看索引
通过 “ show create table ” 方式并不能很好的查看索引的相关信息，比较好的方式是使用 “ show index from ” 表名 查看索引相关信息。以 t1 表为例 ： mysql> show index from t2 \G; *************************** 1. row *************************** Table: t2 # 索引所在表名 Non_unique: 0 # 非唯一的索引 Key_name: idx_uid # 索引名称，如果是主键索引 则是 primary Seq_in_index: 1 # 索引中该列的位置 Column_name: uid # 索引列的名字 Collation: A # 列以什么方式存储在索引中。B+树索引是A，使用HEAP引擎，且使用HASH索引，这里会显示NULL。 Cardinality: 0 # 索引基数，因为没有插入数据 所以是0 Sub_part: NULL # 是不是列的部分索引 Packed: NULL # 关键字如何被压缩 Null: YES # 索引列是否含有NULL Index_type: BTREE # 索引的类型为 BTREE Comment: # 注释 Index_comment: # 索引注释 *************************** 2. row *************************** ...... 这里重点说明 Cardinality，它表示这个索引有多少个。一般情况下，这个数距离表的行数越近，我们的索引就越有效，这里由于我们没有向表中插入过数据，因此这里为 0。插入几条数据测试 ： mysql> select * from t2; +------+------+------+------+ | id | uid | mid | tid | +------+------+------+------+ | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | | 3 | 3 | 3 | 3 | | 4 | 4 | 4 | 4 | | 5 | 5 | 5 | 5 | +------+------+------+------+ # 对该表再次查看索引信息 mysql> show index from t2 \G; *************************** 1. row *************************** Table: t2 Non_unique: 0 Key_name: idx_uid Seq_in_index: 1 Column_name: uid Collation: A Cardinality: 5 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** ...... 此时 Cardinality 的值变成了 5。如果该索引的基数非常小，我们需要考虑是否去掉或者修改该索引了。 注意：MySQL 存储的是一个统计信息，但是这个统计信息并不一定是实时的。如果插入了 5 条数据，但是结果少于 5 。此时我们需要使用 ‘ analyze table 表名称 ’ 提醒一下 MySQL 需要重新统计信息了。
4.2.3 索引设计原则 前面有提到索引的缺点，那么什么样的索引不应该使用或者说应该删除呢 ？这个需要取决于 MySQL 对查询的了解，MySQL 会自动优化查询，接下来看看如何判定。
1. 应该建立的索引
（1）
唯一性索引的值是唯一
的，可以更快速的通过该索引来确定某条记录。 （2）在作为主键的列上创建索引，强制该列的唯一性，并组织表中数据的排列结构。 （3）经常使用的列 ：
在经常需要搜索的列上建立索引
，可以加快搜索的速度。
在经常使用表连接的列上创建索引
，这些列主要是一些外键，可以加快表连接的速度。
在经常需要根据范围进行搜索的列上创建索引
，因为索引已经排序，所以其指定的范围是连续的。
在经常需要排序的列上创建索引
，因为索引已经排序，所以查询时可以利用索引的排序，加快排序查询。
在经常使用 WHERE 子句的列上创建索引
，加快条件的判断速度。
（5）
尽量使用前缀来索引
，例如，TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。 （6）
尽量使用数据量少的索引
，如果索引的值很长，那么查询的速度会受到影响。例如，CHAR(100) 类型比 CHAR(10) 类型的字段需要的时间要多。
2. 应该避免的索引
（1）
在查询中很少使用或参考的列不应该创建索引
。因为这些列很少使用到，所以有无索引并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度，并增大了空间要求。 （2）
只有很少数据值的列不应该创建索引
。因为这些列的取值很少，例如人事表的性别列。查询结果集的数据行占了表中数据行的很大比例，增加索引并不能明显加快检索速度。 （3）
定义为 TEXT、IMAGE 和 BIT 数据类型的列不应该创建索引
。因为这些列的数据量要么相当大，要么取值很少。 （4）
当修改性能远远大于检索性能时，不应该创建索引
。因为当创建索引时，会提高检索性能，降低修改性能，反之一样。因此，当修改性能远远大于检索性能时，不应该创建索引。 （5）
应该限制索引的数目
，每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。在修改表的内容时，索引必须进行更新，有时还可能需要重构。因此，索引越多，更新表的时间就越长。 （6）
数据量小的表最好不要使用索引
，由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。 （7）
删除不再使用或者很少使用的索引
，表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。应该定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。 使用索引的最终目的是为了使查询的速度变快，上面给出的原则是最基本的准则，但不能只拘泥于上面的准则。应该在学习和工作中不断的实践，根据应用的实际情况进行分析和判断，选择最合适的索引方式。 4.3.4 其他索引类型 在上面我们主要看到的都是 BTREE 索引，一般的索引也是这个。除了 BTREE 索引外，我们再简单了解一下其他的索引类型。
1. hash 索引
hash 索引在 MySQL 中使用并不是很多，所谓 hash 索引，实际上就是通过一定的 hash 算法，将必须要索引的键值进行 hash 运算。hash 索引的速度特别快，但是 hash 索引只能用 = 来进行匹配，因为它只能用 hash 码实现精确匹配，这也是它的一个与生俱来的缺点吧。 MySQL 中的 MyISAM 表并不支持 hash 索引，InnoDB 支持自适应 hash 索引。也就是说 InnoDB 会自动监测哪些数据比较常用，然后对它们自动建立 hash 索引，这是无需我们参与并且参与也起不到实际作用。
2. 全文索引（fulltext）
全文索引，在 MySQL5.6 之前 仅有 myisam 存储引擎支持，而在 5.6及以上 的版本中开始 innodb 支持全文索引。所谓全文索引，是一种通过建立倒排索引，快速匹配文档的方式。 MySQL支持三种模式的全文检索模式： （1）自然语言模式（ IN NATURAL LANGUAGE MODE ），即通过 MATCH AGAINST 传递某个特定的字符串来进行检索。 （2） 布尔模式（ IN BOOLEAN MODE ），可以为检索的字符串增加操作符，例如 “ + ” 表示必须包含，“-” 表示不包含，“*” 表示通配符（这种情况， 即使传递的字符串较小或出现在停词中，也不会被过滤掉），其他还有很多特殊的布尔操作符，可以通过如下参数控制： mysql> show variables like '%ft_boolean_syntax%'; +-------------------+----------------+ | Variable_name | Value | +-------------------+----------------+ | ft_boolean_syntax | + -><()~*:""&| | +-------------------+----------------+ （3）查询扩展模式（WITH QUERY EXPANSION），这种模式是自然语言模式下的一个变种，会执行两次检索，第一次使用给定的短语进行检索，第二次是结合第一次相关性比较高的行进行检索。 目前 MySQL 支持在 char、varchar、text 类型的列上定义全文索引。 4.4 Query 语句写法分析 在实际的项目中，更多的可能就是在使用 Query 语句了，Query 语句对数据库性能的影响非常大。 4.4.1 定位 Query 在对 Query 进行优化时，并不是说对于所有的 Query 都要优化， 这样工程量太大。例如一个 Query 一周也只用几次，对其他的毫无影响，这样的就没有必要花费大的代价去对待他。 所以，首先需要找到更需要优化的 Query，然后针对性的进行优化。那什么样的 Query 是更需要优化呢 ？ 对于上面这个问题我们需要从对整个系统的影响来考虑。什么 Query 的优化能给系统整体带来更大的收益，就更需要优化。一般来说，高并发低消耗（相对）的 Query 对整个系统的影响远比低并发高消耗的 Query 大。 例如：一个 query 每小时执行 10000 次，每次消耗 20 IO，另一个 每小时执行 10 次，每次消耗 20000 IO。
如果想要让两者优化到相同程度，例如 150000 IO，需要将第一个的 IO 减少 5 即可，而第二个需要减少 12.5 才行。
4.4.2 定位优化对象 profiling 找到了需要优化的 Query 之后，该怎么优化？首先需要明白该 Query 有什么问题，并且为什么要优化，只有都知道了，才能针对去做优化，而不是觉得他慢就给他 n 多方式去一个一个尝试。 我们可以通过系统自带的 profiling 功能帮我们找出一个 Query 的瓶颈所在。 注意：profiling 是 MySQL5.1 中引入的，如果使用的是之前的版本，只能自己分析 Query 执行的各个步骤了。
1. profiling 设置
profiling 默认是关闭的，可以通过 select @@profiling; 查看是否开启。例如 ： mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 把这个值设置为 1 即可。示例 ： mysql> set profiling=1; 可以自行检查。
2. 结果分析
进行一次查询后，通过 show profiles; 查看结果。示例 ： mysql> show profiles; +----------+------------+---------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------+ | 1 | 0.00017825 | select @@profiling | | 2 | 0.00081250 | select * from users | +----------+------------+---------------------+ 结果中个参数含义如下 ： Query_ID 是查询的具体编号。
Duration 是查询的耗时，它是一个比较精确的数字。
Query 是具体的查询语句。
具体 query 耗时分析，通过 show profiles for query n; 即可查看对应 query 的具体耗时。示例 ： mysql> show profile cpu, block io for query 1; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000051 | 0.000000 | 0.000000 | NULL | NULL | | checking permissions | 0.000005 | 0.000000 | 0.000000 | NULL | NULL | | Opening tables | 0.001324 | 0.000000 | 0.000000 | NULL | NULL | | init | 0.000019 | 0.000000 | 0.000000 | NULL | NULL | | System lock | 0.000006 | 0.000000 | 0.000000 | NULL | NULL | | optimizing | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | | statistics | 0.000008 | 0.000000 | 0.000000 | NULL | NULL | | preparing | 0.000006 | 0.000000 | 0.000000 | NULL | NULL | | executing | 0.000001 | 0.000000 | 0.000000 | NULL | NULL | | Sending data | 0.000069 | 0.000000 | 0.000000 | NULL | NULL | | end | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | | query end | 0.000005 | 0.000000 | 0.000000 | NULL | NULL | | closing tables | 0.000005 | 0.000000 | 0.000000 | NULL | NULL | | freeing items | 0.000075 | 0.000000 | 0.000000 | NULL | NULL | | cleaning up | 0.000008 | 0.000000 | 0.000000 | NULL | NULL | +----------------------+----------+----------+------------+--------------+---------------+ 上面的例子中是获取 CPU 和 Block IO 的消耗，如果需要获取其他信息可以通过 show profiles *** for query n; 来查看。Status 所代表的含义则可以通过翻译知晓。 在对 Query 进行优化时，还需要考虑该 Query 的重要性。
如果 Query 实现的应用系统功能比较重要，即使在某些方面作出一些让步也是有必要的。比如调整 schema 设计，调整索引组成。
而如果该 Query 所实现的是一些并不是太关键的功能，我们需要尽量保证其他更重要的 Query 的性能。这种时候，即使需要调整商业需求，减少功能实现，也不得不应该作出让步。
4.5 MySQL 性能分析语句
1. 性能问题因素与 mysql 性能因素
什么是性能问题 ？ 对于用户来说：“又卡了”，“怎么死机了”，“反应太慢了”。 对于程序员来说：“程序执行的时长”，“每秒查询的次数”，“内存，CPU 的利用率”。 性能优化的目的：（合理利用可利用的资源）俗称：抠门。 吞吐量，越大越好。
延迟，越低越好 同样的资源下（前提），吞吐量越高越好，响应时间越低越好。通俗的来说就是：多快好省。
性能指标参考 ： 执行时间：一段代码从开始运行到运行结束所使用的时间。
CPU 时间：(算法)函数或者线程占用 CPU 的时间。
内存分配：程序在运行时占用的内存空间。
磁盘吞吐量：描述 I/O 的使用情况。
网络吞吐量：描述网络的使用情况。
响应时间：系统对某用户行为或者动作做出响应的时间。响应时间越短，性能好。
2. mysql 性能查询的命令
（1）show status -- 用来查看MySQL在运行过程中的属性状态 show status; show status like '%变量%'; （2）查看会话状态 -- 查看当前会话情况 show status like 'Com_%'; -- 查看全局会话情况 show global status like 'Com_%'; -- 查看针对innodb存储引擎状态的统计 show global status like 'Innodb_%'; -- 查看视图连接MySQL服务器次数 show global status like 'connections'; -- 查看服务器工作时间 show global status like 'uptime'; -- 查看锁使用情况 show global status like 'innodb_row_lock%'; （3）show processlist processlist 命令的输出结果显示了有哪些线程在运行，可以帮助识别出有问题的查询语句，两种方式使用这个命令。 show processlist; （4）show variables 查看 Mysql 系统变量 show variables; --查看当前session级别的隔离方式 show variables like '%isolation%'; -- 查看isolation帮助 help isolation -- 局部修改 set session transaction isolation level READ COMMITTED; show session variables like'%isolation%'; -- 全局修改 show global variables like '%isolation%'; set global transaction isolation level READ COMMITTED; 4.6 SQL 性能分析 在优化 sql 语句时，主要有以下几个步骤 ： 通过 show status 命令了解各种 SQL 的执行频率。
定位执行执行效率较低的 SQL 语句。
通过 explain 分析低效 SQL 的执行计划。
确定问题并采取相应的优化措施。
4.6.1 了解执行频率 我们可以通过 show [ session|global ] status 命令可以提供服务器状态信息。 如果使用 session 修饰表示当前连接，使用 global 修饰表示自数据库上次启动至今的统计结果，如果不写，默认是 session。 我们也可以添加 like 来帮助我们进一步查询，例如：show status like ‘Com_%’ Com_select ：表示执行 select 操作的次数，一次查询累加 1。
Com_insert：执行 insert 操作的次数，对于批量插入累加 1。
Com_update:执行 update 操作的次数。
Com_delete:执行 delete 操作的次数。
通过上面这些参数，我们很容易的了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的 SQL 大致的执行比例是多少。 对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会进行增加。对于事务型的应用，通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。 除了上面这些以外，我们还可以查询一些参数来了解数据库的基本情况。例如： Connections : 试图连接 MySQL 服务器的次数。
Uptime : 服务器工作时间。
Slow_queries : 慢查询的次数。
4.6.2 SQL 分析 前面有提到过慢查询日志，通过这个东西我们就可以十分清晰的查看到哪些是执行起来比较慢的，从而定位效率比较低的 sql。 注意慢查询日志在查询结束之后才能记录，所以在应用反应执行效率出现问题的时候查询慢日志并不能定位问题。可以使用 show processlist 命令查看当前 mysql 在进行的线程，包括线程的状态、是否锁表等等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。
执行计划
在定位到 sql 之后，需要分析低效 sql 的执行计划。我们可以通过 explain 或者 desc 命令获取 MySQL 如何执行 select 语句的信息，包括在 select 语句执行过程中表如何连接和连接的顺序。 格式 ： explain select 语句 \G 字段说明： 1.select_type 表示 select 的类型，常见的取值有： simpe----简单表，即不使用表连接或者子查询 primary---主查询，即外层的查询 union------union 中的第二个或者后面的查询语句 subquery--子查询中的第一个 select 2.type 表示表的连接类型，性能由好到差为： system------表中仅有一行，即常量表 const-------单表中最多只有一个匹配行，比如 unique index eq_ref------在表中只查询一条记录，多表连接使用唯一索引 ref------------使用普通索引 ref_or_null--条件中包含对 null 的查询 index_merge--索引合并优化 unique_subquery-in 后面是一个查询主键字段的子查询 index_subquery--in 的后面是查询非唯一索引字段的子查询 range------------单表查询中的范围查询 index-------------通过查询索引来得到数据 all-----------------通过全表扫描来得到数据 3.possible_keys 表示查询时，可能使用的索引。 4.key 表示实际使用的索引。 5.key_len 表示索引字段的长度。 6.ref 显示索引的那一列被使用了。 7.rows 表示扫描行的数量。 8.extra 表示执行情况的说明和描述。 示例 ： mysql> explain select name,email from users where name = 'll' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 19 filtered: 10.00 Extra: Using where 表示当前是一个对 users 表的简单查询，没有使用任何索引，进行了全表扫描，扫描了 19 行，效率很低。对该表就可以添加索引来优化查询。例如 ： mysql> create index idx_name on users(name); -- 给 name 字段创建索引 mysql> show index from users; -- 查看表的索引 ... KEY `idx_name` (`name`) ... -- 再次执行计划 mysql> explain select name,email from users where name = 'll' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: ref possible_keys: idx_name key: idx_name key_len: 802 ref: const rows: 2 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) 这次就会使用上创建的索引，类型为普通索引，扫描行数则只有 2 行。一般来说，得保证查询至少达到 range 级别，最好能达到 ref。 需要注意索引会失效的情况：详情看索引部分。
4.6.3 优化方案 对于一般人员来说，可能只需要完成一些简单实用的优化即可，更复杂的则会有专业的 DBA 完成。简单实用的方法主要有 ： 定期分析表和检查表。
定期优化表。
1. 分析表
MySQL中使用 analyze table 语句来分析表。其语法格式为 ： analyze [local|no_write_to_binlog] table 表名 1 [,表名 2]..... 该语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得 SQL 能够生成正确的执行计划。 如果用户感觉实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。 在分析表的过程中，数据库系统会对表添加一个只读锁，在分析期间只能读取而不能更新和插入数据。 示例 ： mysql> analyze table users; +---------------+---------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +---------------+---------+----------+-----------------------------+ | laravel.users | analyze | status | Table is already up to date | +---------------+---------+----------+-----------------------------+ 说明 ： Table：表示表的名称。
Op：表示执行的操作 ：
analyze 表示进行分析操作。
check 表示进行检查查找。
optimize 表示进行优化操作。
Msg_type：表示信息类型，其显示的值通常是状态、警告、错误和信息这四者之一。
Msg_text：显示信息。
对表的定期分析可以改善性能，且应该成为常规维护工作的一部分。因为通过更新表的索引信息对表进行分析，可改善数据库性能。
2. 检查表
MySQL中使用 check table 语句来检查表，检查表是为了检查一个或多个表是否有错误。其语法格式为 ： check table 表名 1 [,表名 2].... [option]; option 的值可以是 {QUICK | FAST |MEDIUM | EXTENDED |CHANGED}，其执行效率会依次降低。 注意：check table 对 MyISAM 和 InnoDB 表有作用，但是 option 选项只对 MyISAM 类型的表有效，对 InnoDB 类型的表无效。CHECK TABLE 语句在执行过程中也会给表加上只读锁。
check table 也可以检查视图是否有错误，比如在视图定义中被引用的表已经不存在。
3. 优化表
MySQL 中使用 optimize table 语句来优化表。该语句对 InnoDB 和 MyISAM 类型的表都有效。其语法格式为 ： optimize [local | no_write_to_binlog] table 表名 1 [,表名 2].... 如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表(含有 varchar，blob 或者 text 列的表)进行了很多更改，则应该使用 optimize table 命令来进行表优化。该命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费。 注意：optimize table 语句只能优化表中的 VARCHAR、BLOB 或 TEXT 类型的字段。optimize table 语句在执行过程中也会给表加上只读锁。因此一定要注意在数据库不繁忙的时候执行相关的操作。
4.6.4 优化 SQL 对于 SQL 我们也需要进行一些优化，常用的 SQL 优化有 ： 大批量插入数据。
优化 insert 语句。
优化 group by 语句。
优化 order by 语句。
优化嵌套查询。
优化 or 条件。
使用 SQL 提示。
1. 大批量插入数据
（1）提高 MyISAM 表的导入效率 当用 load 命令导入数据的时候，适当的设置可以提高导入的速度。 对于 MyISAM 存储引擎的表，可以通过以下方式快速的导入大量的数据。 alter table t1 disable keys； loading the data alter table t1 enable keys； disable keys 和 enable keys 用来 打开或者关闭 MyISAM 表非唯一索引的更新。在导入大量的数据到一个非空的 MyISAM 表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的 MyISAM 表，默认就是先导入数据然后才创建索引的，所以不用进行设置。 注意：以上是对 MyISAM 存储引擎而言的。
（2）提高 InnoDB 表的导入效率 对于 InnoDB 可以提高的方面比较多，主要包含以下方向 ：
数据的准备
：因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。
唯一性校验
：在导入数据前执行 set unique_checks =0 关闭唯一性校验，在导入结束后执行 set unique_checks=1 来恢复唯一性校验，提高效率。
自动提交
：如果应用使用自动提交的方式，建议在导入前执行 set autocommit=0 来关闭自动提交，导入结束后再执行 set autocommit=1 来打开自动提交，它也可以提高导入的效率。
2. 优化 insert 语句
当进行数据 insert 的时候，可以考虑采用以下几种优化方式 ： （1） 如果同时从同一客户插入很多行，尽量使用多个值表的 insert 语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个 insert 语句块。 示例 ： insert into users values(值1，值2..),(值1，值2..),(值1，值2..).. （2）如果从不同客户插入很多行，能通过使用 insert delayed 语句得到更高的速度，delayed 的含义是让 insert 语句马上执行，其实数据都被存放在内存的队列中，并没有真正写入磁盘，这比每条数据分别插入要快得多。示例 ： insert delayed into users .. 注意：这样的好处是，提高插入的速度，客户端不需要等待太长时间。坏处是，不能返回自动递增的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。
（3）将索引文件和数据文件分在不同的磁盘上存放，此处需要使用建表中的选项。 （4）如果进行批量插入，可以增加 bulk_insert_buffer_size 变量值的方式来提高速度，但是，这只能对 MyISAM 表使用。 （5）当从一个文本文件装载一个表时，使用 load data infile ，这通常比使用很多 insert 语句快 20 倍。
3 . 优化 group by 语句
默认情况下，MySQL 对所有 group by c1，c2....的字段进行排序，这与在查询中指定 order by c1，c2 类似，所以是否显示包含相同列对性能没有什么影响。而如果查询包括 group by，但是用户想要避免排序结果的消耗，可以指定 order by null 来禁止排序。 示例 ： 不使用 order by null mysql> explain select * from users group by id\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 19 filtered: 100.00 Extra: Using filesort 使用 order by null mysql> explain select * from users group by id order by null\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 19 filtered: 100.00 Extra: NULL 两者区别在于第一个 SQL 语句需要进行 filesort，而 filesort 往往非常耗费时间。
4. 优化 order by 语句
当 where 条件和 order by 使用相同的索引， order by 的顺序和索引顺序相同，并且 order by 的字段都是升序或者都是降序。 这种情况中，MySQL 可以使用一个索引来满足 order by 子句，而不需要额外的排序。 可以使用索引情况 ： select * from users order by key_part1 ,key_part2....; select * from users where key_part = 1 order by key_part1 desc, key_part2 desc; select * from users order by key_part1 desc,key_part2 desc; 无法使用索引情况 ： order by 的字段混合 asc 和 desc。
select * from users order by key_part1 desc，key_part2 asc;
用于查询行的关键字与 order by 中所使用的不同。
select * from users where key2= constant order by key1;
对不同的关键字使用 order by。
select * from users order by key1，key2;
5. 优化嵌套子查询
Mysql 的子查询可以一次性完成需要多次才能完成的 SQL 操作，同时也可以避免失误或者表锁死，而且写起来很容易。但是在很多情况下，子查询可以被更有效率的 join 替代。 示例 ： 在 user1 表中找到那些在 user2 表中不存信息的所有用户 ： select * from users where id not in(select user_id from user_infos); 我们可以使用连接来优化它，尤其当 t2 表中对 id 建有索引的话， select * from users u left join user_infos uf on u.id = uf.user_id where uf.user_id is null; 在数据库实现早期，查询优化器对子查询一般采用嵌套执行的方式，即父查询中的每一行，都执行一次子查询，这样子查询会执行很多次。这种执行方式效率低。而对子查询进行优化，可能带来几个数量级的查询效率的提高。子查询转变成为连接操作之后，会得到如下好处 ： 子查询不用执行很多次。
优化器可以根据统计信息来选择不同的连接方法和不同的连接顺序。
子查询中的连接条件、过滤条件分别变成了父查询的连接条件、过滤条件，优化器可以对这些条件进行下推，以提高执行效率。
6. 优化 or
对于含有 or 的查询子句，如果要利用索引，则 or 之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引。 没有索引 ： mysql> explain select * from users where id=66 or name = "Orrin Eichmann"\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 100 filtered: 10.90 Extra: Using where 此时是全表扫描，效率差。 使用索引 : -- 添加索引 mysql> create index idx_name on users(name); Query OK, 100 rows affected (0.01 sec) mysql> explain select * from users where id=66 or name = "Orrin Eichmann"\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: index_merge possible_keys: PRIMARY,idx_name key: PRIMARY,idx_name key_len: 8,767 ref: NULL rows: 2 filtered: 100.00 Extra: Using union(PRIMARY,idx_name); Using where 此时则使用了索引 PRIMARY, idx_name。另外还可以看出 MySQL 在处理含有 or 子句的时候，实际上是对 or 的各个字段分别查询后的结果进行了 union。
7. SQL 提示
SQL 提示是优化数据库的一个重要手段，简单来说就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。常用的有 use index 、ignore index、force index。 （1）use index use index 在查询语句中表名的后面，添加 use index 来提供希望 MySQL 去参考的索引列表，就可 以让 MySQL 不再考虑其他可用的索引。示例 ： mysql> explain select * from users use index(idx_name) where id = 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 100 filtered: 1.00 Extra: Using where mysql> explain select * from users where id = 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 filtered: 100.00 Extra: NULL 但是需要注意使用的是否合理，上例在指定使用的索引情况下效率会更好。与之相似的则是 force index，它会让 MySQL 强制使用指定的索引。 区别：如果使用 use index，则优化器可以使用此索引，但如果优化器认为更快，则可以使用表扫描。如果使用 force index，那么即使认为表扫描更有效，优化器也会使用此索引。只有在没有办法使用索引查找行时，优化器才会使用表扫描。
（2）ignore index 如果用户只是单纯地想让 MySQL 忽略一个或者多个索引，则可以使用 ignore index。示例 ： mysql> explain select * from users ignore index(PRIMARY) where id = 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 100 filtered: 1.00 Extra: Using where 同样反例，忽略了主键索引后，会扫描全表。 SQL 优化问题是数据库性能优化最基础也是最重要的一个问题。很多数据库性能问题都是由不合适的 SQL 语句造成的。 4.7 与elasticsearch结合 Elastic 是一个实时的分布式搜索分析引擎，它能让你以前所未有的速度和规模，去探索你的数据。 它被用作全文检索、结构化搜索、分析以及这三个功能的组合。 Elastic 的底层是开源库 Lucene。但是，你没法直接用 Lucene，必须自己写代码去调用它的接口。Elastic 是 Lucene 的封装，提供了 REST API 的操作接口，开箱即用。 4.7.1 安装运行
1 . windows 环境
（1）JDK 准备 Elastic 需要 Java 8 环境。如果你的机器还没安装 Java，可以下载 java8 ，下载好后注意要保证环境变量JAVA_HOME 正确设置（如果系统没有帮助设置），安装好后，通过 java -version 可以查看版本信息，通过这个来判断是否安装成功。 （2）Elasticsearch 安装 准备好了 java 环境之后，准备 elasticsearch 的安装。
下载
：首先需要 下载 elasticsearch，可以根据自己的需要选择版本，这里是 7.6.1 ，如果不想要最新版本可以点击下面内容选择需要的版本 ：
解压运行
：下载好需要的版本之后，进行解压，进入到解压目录下的 bin 目录。双击 elasticsearch.bat 即可运行 ：
运行后出现一大段内容，可以忽略。
查看
：虽然可以忽略一大段令人头痛的内容，但是还是需要测试是否安装成功。打开浏览器输入 http://127.0.0.1:9200/ ，可以看到以下内容则说明成功了 ：
不同版本大同小异。 （3）kibana 安装 Kibana 是一个开放源码分析和可视化平台，如果不需要可以不安装。主要可以使用 Kibana 搜索、查看并与存储在 ElasticSearch 索引中的数据进行交互。它简单、基于浏览器的界面使您能够快速创建和共享动态仪表板，实时显示对 Elasticearch 查询的更改。 Kibana 的安装与 elasticsearch 类似，如下 ：
下载
：下载 Kibana，需要注意其版本要与 elasticsearch 对应。 同样，可以在下面选择不同的版本。
解压运行
：与 elasticsearch 类似，接下来就是进入到 bin 目录双击运行了 ：
查看
：运行时也是一大段内容，忽略不计，直接进入浏览器查看。输入 http://localhost:5601 可以看到 ：
不同版本也有一些不同，不知道的可以翻译一下需要选择的内容。
2 . CentOS 环境
elasticsearch 安装
在上面提供的下载路径中存在 Linux 版本的。
（1）解压 elasticsearch-7.6.1-linux-x86_64.tar.gz 到 /usr/local/ 目录 tar -xvf elasticsearch-7.6.1-linux-x86_64.tar.gz （2）进入解压后的 elasticsearch 目录新建 data 目录 mkdir data （3）修改 config/elasticsearch.yml 配置文件 vi config/elasticsearch.yml 取消下列项注释并修改 ： cluster.name: my-application #集群名称 node.name: node-1 #节点名称 #数据和日志的存储目录 path.data: /usr/local/elasticsearch-7.6.1/data path.logs: /usr/local/elasticsearch-7.6.1/logs #设置绑定的ip，设置为0.0.0.0以后就可以让任何计算机节点访问到了 network.host: 0.0.0.0 http.port: 9200 #端口 #设置在集群中的所有节点名称，这个节点名称就是之前所修改的，当然你也可以采用默认的也行，目前 是单机，放入一个节点即可 cluster.initial_master_nodes: ["node-1"] 修改完毕后，保存退出。 （4）启动 进入 /bin 目录执行命令。 ./elasticsearch 或者是 ./elasticsearch -d
问题 1
：这里会出现错误。 Java HotSpot(TM) 64-Bit Server VM warning: INFO: os::commit_memory(0x00000000c5330000, 986513408, 0) failed; error='Cannot allocate memory' (errno=12) ## There is insufficient memory for the Java Runtime Environment to continue. # Native memory allocation (mmap) failed to map 986513408 bytes for committing reserved memory. # An error report file with more information is saved as: # logs/hs_err_pid22863.log [root@localhost bin]# 原因是 elasticsearch 使用 java 的 jvm 默认是使用 1G 的内存的，这里我们修改一下内存，直接把内存改到 256 m。 cd 到 es 目录修改 ./config/jvm.options ： vi ./config/jvm.options 修改该内容 ： -Xms256m -Xmx256m 保存并退出，再次启动 es。
问题 2
：再次启动会出现以下错误。 [2019-06-21T16:20:03,039][WARN ][o.e.b.ElasticsearchUncaughtExceptionHandler] [node-1] uncaught exception in thread [main] org.elasticsearch.bootstrap.StartupException: java.lang.RuntimeException: can not run elasticsearch as root at org.elasticsearch.bootstrap.Elasticsearch.init(Elasticsearch.java:163) ~ [elasticsearch-7.6.1.jar:7.1.1] at org.elasticsearch.bootstrap.Elasticsearch.execute(Elasticsearch.java:150) ~ [elasticsearch-7.6.1.jar:7.1.1] atorg.elasticsearch.cli.EnvironmentAwareCommand.execute(EnvironmentAwareCommand.java:86) ~[elasticsearch-7.6.1.jar:7.1.1] at org.elasticsearch.cli.Command.mainWithoutErrorHandling(Command.java:124) ~ [elasticsearch-cli-7.1.1.jar:7.1.1] at org.elasticsearch.cli.Command.main(Command.java:90) ~[elasticsearch-cli- 7.1.1.jar:7.1.1] at org.elasticsearch.bootstrap.Elasticsearch.main(Elasticsearch.java:115) ~ [elasticsearch-7.6.1.jar:7.1.1] at org.elasticsearch.bootstrap.Elasticsearch.main(Elasticsearch.java:92) ~ [elasticsearch-7.6.1.jar:7.1.1] Caused by: java.lang.RuntimeException: can not run elasticsearch as root at org.elasticsearch.bootstrap.Bootstrap.initializeNatives(Bootstrap.java:102) ~ [elasticsearch-7.6.1.jar:7.1.1] at org.elasticsearch.bootstrap.Bootstrap.setup(Bootstrap.java:169) ~[elasticsearch- 7.6.1.jar:7.1.1] at org.elasticsearch.bootstrap.Bootstrap.init(Bootstrap.java:325) ~[elasticsearch- 7.6.1.jar:7.1.1] at org.elasticsearch.bootstrap.Elasticsearch.init(Elasticsearch.java:159) ~ [elasticsearch-7.6.1.jar:7.1.1] ... 6 more [root@localhost elasticsearch-7.6.1]# 这是因为不能使用 root 用户操作，添加一个其他的用户再试试 ： [root@localhost elasticsearch-7.6.1]# useradd elasticsearch [root@localhost elasticsearch-7.6.1]# passwd elasticsearch 更改用户 elasticsearch 的密码 。 新的 密码： 无效的密码： 密码少于 8 个字符 重新输入新的 密码： passwd：所有的身份验证令牌已经成功更新。 [root@localhost elasticsearch-7.6.1]# chown -R elasticsearch /usr/local/elasticsearch- 7.6.1 vi 编辑 /etc/security/limits.conf，在末尾加上以下内容 ： elasticsearch soft nofile 65536 elasticsearch hard nofile 65536 elasticsearch soft nproc 4096 elasticsearch hard nproc 4096 vi 编辑 vi /etc/security/limits.d/20-nproc.conf，将 * 改为用户名（elasticsearch）： # Default limit for number of user's processes to prevent # accidental fork bombs. # See rhbz #432903 for reasoning. elasticsearch soft nproc 4096 root soft nproc unlimited vi 编辑 /etc/sysctl.conf，在末尾加上以下内容 ： vm.max_map_count = 655360 在 root 用户下执行 ： [root@localhost ~]# sysctl -p vm.max_map_count = 655360 [root@localhost ~]# 登录刚才新建的 elasticsearch 用户，并启动 elasticsearch [root@localhost elasticsearch-7.6.1]# su es [elasticsearch@localhost elasticsearch-7.6.1]$ ./bin/elasticsearch 完毕。 （5）测试 以上完毕后，在浏览器输入 ip:9200 即可，与 windows 类似，只是要改成自己的 ip。
3. kibana 安装
（1）解压 在 /usr/local/ 解压 kibana-7.6.1-linux-x86_64.tar.gz tar -xvf kibana-7.6.1-linux-x86_64.tar.gz （2）启动 进入 ./kibana/bin 目录启动 ./kibana Kibana should not be run as root. Use --allow-root to continue -- 不能在root用户下启动 （3）创建 kibana 用户 useradd kibana passwd kibana chown -R kibana /usr/local/kibana （4）访问 进入 kibana 用户后，启动 kibana 然后访问即可，效果同上。 4.7.2 基础使用
1. 基本概念
Elastic 本质上是一个分布式数据库，允许多台服务器协同工作，每台服务器可以运行多个 Elastic 实例。单个 Elastic 实例称为一个节点（node）。一组节点构成一个集群（cluster）。 （1）Index Elastic 会索引所有字段，经过处理后写入一个反向索引。查找数据的时候，直接查找该索引。所以，Elastic 数据管理的顶层单位就叫做 Index（索引），也就是说 索引 即是 单个数据库。每个 Index 的名字必须是小写。 通过下面命令可以查看当前节点的所有 index ： curl -X GET 'http://localhost:9200/_cat/indices?v' （2）Document Index 里面单条的记录称为 Document（文档），类似于数据库的 行（row）。许多条 Document 构成了一个 Index。Document 使用 JSON 格式表示。如下 ： { "user": "admin", "title": "超管", "desc": "数据库管理" } 同一个 Index 里面的 Document，不要求有相同的结构（scheme），但是最好保持相同，这样有利于提高搜索效率。
（3）Type Type 类似于数据库的表（table），但是它是虚拟的，是由 Document（文档）按照一定逻辑分组得到。不同的 Type 应该有相似的结构（schema），例如两张表的 id 数据类型应该相同。 通过下面命令可以列出每个 index 包含的 type ： curl 'localhost:9200/_mapping?pretty=true' 以上做对比为 ：
elasticsearch 是面向文档的，那么就意味着索引和搜索数据的最小单位是文档，elasticsearch 中，文档有几个 重要属性 : 自我包含，一篇文档同时包含字段和对应的值，也就是同时包含 key:value！
可以是层次型的，一个文档中包含自文档，复杂的逻辑实体就是这么来的！ {就是一个json对象！fastjson 进行自动转换！}
灵活的结构，文档不依赖预先定义的模式，我们知道关系型数据库中，要提前定义字段才能使用，在 elasticsearch 中，对于字段是非常灵活的，有时候，我们可以忽略该字段，或者动态的添加一个新的字段。
倒排索引
说到倒排索引则需要和另外一个对应说明 - 正向索引。那么什么是正向索引什么又是倒排索引呢？以一句话为例：PHP 是最好的语言
正向索引
：正向索引是网页与关键词一一对应的数据结构。 使用正向索引时，其对应的结果为：
例如以上是两个网页，当我们去搜索时，会到各个网页搜索相关关键字，如果有则该网页是符合条件的一个，没有则不属于。
倒排索引
：倒排索引是相对正向索引而言的，你也可以将其理解为逆向索引。它是一种关键词与网页一 一对应的数据结构。 使用倒排索引时，期对应结果为 ：
以上同样是两个网页，但是当我们去搜索关键字的时候，它并不会去每个网页搜索关键字，而是通过关键字找匹配的网页，这种情况其效率会好很多。
2. 基本操作
（1）CURD 基本命令 ： GET _cat/indices：查找所有的索引。 GET movies/_search 查看信息：movies 是索引信息。 took：消耗的时间。
hits：最外层命中的数据总数，内层是具体的数据。
value：总共数据量。
_source：实际数据本身。
（2）查 GET users/_search：查看 users 索引的所有数据。 GET movies/_doc/252：查找 id 为 252 的数据。 （3）增 创建一个 users 索引，并且添加数据 ： POST users/_doc { "firstname":"hello", "lastname":"world" } 没有指定 id ，系统会自动给一个。 指定 id ： POST users/_doc/1 { "firstname":"hello", "lastname":"kitty" } 在 _doc 后面加上 /id 的值，如果 id 相同，后面会覆盖前面。 （4）create 方式 POST users/_create/1 { "firstname":"hello", "lastname":"kitty" } 需要注意的是，create 方式插入数据，当 id 存在时，会报错。 （5）批量插入 POST users/_bulk {"index":{}} {"name":"hello","age":"16"} {"index":{"_id":6}} {"name":"hi","age":"17"} 格式 有点怪，但是需要注意 index 部分是可以写主键的，如果不写会默认给一个。 （6）删 DELETE users：删除 users 索引 删除数据 DELETE users/_doc/Sqdu2ngBMfgPCKya0Kvg _doc 后面接 id。 （7）改 post 方式 POST users/_update/1 { "doc": { "firstname":"hello", "lastname":"uiop" } } put 方式 PUT users/_doc/1 { "firstname":"hello", "lastname":"qwer" }
3. 类型指定
在添加数据的时候，其中的属性可以指定类型。其类型主要为： 字符串类型
text 、 keyword
数值类型
long, integer, short, byte, double, float, half_float, scaled_float
日期类型
date
PUT /索引名/~类型名~/文档id {请求体}
te布尔值类型
boolean
二进制类型
binary
等等
示例 ： PUT userq { "mappings": { "properties": { "name":{ "type":"text" }, "age":{ "type":"integer" } } } } 通过 GET userq 可以查看信息 ： { "userq" : { "aliases" : { }, "mappings" : { "properties" : { "age" : { "type" : "integer" }, "name" : { "type" : "text" } } }, "settings" : { "index" : { "creation_date" : "1618583270051", "number_of_shards" : "1", "number_of_replicas" : "1", "uuid" : "eb7Od9UzSUCfvhMEu_E6bg", "version" : { "created" : "7060199" }, "provided_name" : "userq" } } } }
4. URL
（1）范围查 查询所有包含 2012 的数据 ： GET movies/_search?q=2012 范查询：q 表示参数 question。 （2）指定属性 查询 tile 为 2012 的数据 ： GET movies/_search?q=2012&df=title 等价于 GET movies/_search?q=title:2012 df 表示默认属性。 （3）多关键字 查找 title 为 hello 或者 world 的数据 ： 查找 title 为 hello 或者 world 的数据 GET movies/_search?q=title:hello world 如果指定两个关键字是整体则可以： GET movies/_search?q=title:"hello world" 此时 查找 title 就是 hello world 的数据 还可以使用 + - 方式 ： GET movies/_search?q=title:(hello world) 括号中的参数都可以有 +- 两个符号，+ 代表该参数有，- 代表该参数没有。 （4）AND 查找 title 为 hello world 的数据 ： GET movies/_search?q=title:(hello AND world) 注意 AND 不能小写，小写则代表是查找 hello and world 这三个关键字。 （5）分页 从索引位置为 0 的位置开始查询 6 条数据 GET movies/_search?q=title:(hello world)&from=0&size=6 （6）范围查询 查询 year 在 2010 和 2020 质检的数据 GET movies/_search?q=year:(>=2010 AND <=2020) 另外还可以使用一下方式 : GET movies/_search?q=year:{2010 TO 2020] GET movies/_search?q=year:[2010 TO 2020] 注意: 结尾必须是闭区间，也就是 ] 。
基本 Rest 命令说明 ：
上面方式前面加上 localhost:9200 并且用对应的 GET 或者 POST 方式访问，即可执行对应语句，注意如果是有数据，则需要以 json 格式提交。
5. Ik 分词器
既然对于一段话需要根据关键字来搜索，那么这些关键字是需要进行拆分的。es 也存在一些工具能帮我们做这样的一些工作。 我们在搜索时候会填写自己需要的信息，搜索引擎则会把数据库或者索引库中的数据进行分词，然后进行一个匹配操作，默认的中文分词是将每个字看成一个词。比如："中国的花"会被分为"中"，"国"，"的"，"花"，这显然是不符合要求的，所以我们需要借助分词器来解决这个问题。如果要使用中文，建议使用 ik 分词器。
分词
：即把一段中文或者别的划分成一个个的关键字。
（1）安装 ik ik 分词器是 es 的一个插件，需要我们自己下载安装。通过以下命令可以查看 es 安装的插件： elasticsearch-7.6.1\bin>elasticsearch-plugin list 注意需要在 bin 目录下，此时是什么都不存在，接下来需要下载安装。 （2）下载 ik 点击 ik 进行下载，然后到 es 的 plugins 目录下创建 ik 目录，解压到该目录。然后，就没然后了，可以再次通过上述命令查看是否安装成功。 （3）验证 ik 分词器安装完毕后，需要重启 es 来使用，否则不会有效果。ik 提供了两个分词算法：ik_smart 和 ik_max_word，其中 ik_smart 为最少切分，ik_max_word 为最细粒度划分。什么意思呢，看两个例子。 （4）ik_smart 模式 GET _analyze { "analyzer": "ik_smart", "text": "好好学习" } 执行后得到的结果为 ： { "tokens" : [ { "token" : "好好学习", "start_offset" : 0, "end_offset" : 4, "type" : "CN_WORD", "position" : 0 } ] } 进行了一个完全匹配。 （5）ik_max_word 模式 GET _analyze { "analyzer": "ik_max_word", "text": "好好学习" } 执行后结果为 ： { "tokens" : [ { "token" : "好好学习", "start_offset" : 0, "end_offset" : 4, "type" : "CN_WORD", "position" : 0 }, { "token" : "好好学", "start_offset" : 0, "end_offset" : 3, "type" : "CN_WORD", "position" : 1 }, { "token" : "好好", "start_offset" : 0, "end_offset" : 2, "type" : "CN_WORD", "position" : 2 }, { "token" : "好学", "start_offset" : 1, "end_offset" : 3, "type" : "CN_WORD", "position" : 3 }, { "token" : "学习", "start_offset" : 2, "end_offset" : 4, "type" : "CN_WORD", "position" : 4 } ] } 结果会比上面模式多很多。但是需要注意，并不是说 ik_smart 就只会有一个结果，只是它的分词粒度比较小，它认为这个词只能分成这一个。
6. 自定义配置
有时候一些特殊的词我们自己想要添加到分词器的字典中去，这时候我们就可以定义自己的字典文件。步骤如下：
（1）加字典文件
在安装插件的配置文件目录下（plugins\ik\config），添加配置文件，这里我们写为 test.dic。
好好
好好学
好好学习
好学
好学习
学习
（2）配置字典
配置文件添加好后，需要加入到 IKAnalyzer.cfg.xml 文件中，该文件也在 config 目录中。
4.7.3 结合 Laravel
1. 扩展包
（1）下载 Elasticsearch 官方提供了 Composer 包，在引入时需要注意要指定版本，因为不同版本的 Elasticsearch 的 API 略有不同，我们用的是 7.x，因此需使用 ~7.0 来指定包版本。 composer require elasticsearch/elasticsearch=7.0 （2）配置 Elasticsearch 的配置很简单，我们只需要 Elasticsearch 服务器的 IP 和端口即可。如下：
config/database.php
[ // Elasticsearch 支持多台服务器负载均衡，因此这里是一个数组 'hosts' => explode(',', env('ES_HOSTS')), ] ?> 在 database 配置文件的最外层即可。 .env . . ES_HOSTS=localhost:9200 本地环境的 Elasticsearch 的 IP 和端口是 localhost:9200，如果端口是 9200 则可以忽略不写。
2. 注册绑定
在 laravel 中容器是一个管理实例的好地方，这里我们可以借助 laravel 的容器帮助我们管理被我们实例化的 es 对象。如下：
app/Providers/AppServiceProvider.php
app->singleton('es', function(){ // 通过配置文件读取 es 服务器列表 $builder = ClientBuilder::create()->setHosts(config('database.elasticsearch.hosts')); // 判断开发环境:开发环境 if ($this->app->environment() == 'local') { // 配置日志 $builder->setLogger(app('log')->driver()); } return $builder->build(); }); } . . 以上就配置完毕。
3. 测试使用
进入 tinker 进行测试： php artisan tinker 查看 es 信息： >>> app('es')->info() # --------- 信息 --------- => [ "name" => "DESKTOP-K9LDCLM", "cluster_name" => "elasticsearch", "cluster_uuid" => "DNQynn_9TLKXFjYmGSzeIw", "version" => [ "number" => "7.6.1", "build_flavor" => "default", "build_type" => "zip", "build_hash" => "aa751e09be0a5072e8570670309b1f12348f023b", "build_date" => "2020-02-29T00:15:25.529771Z", "build_snapshot" => false, "lucene_version" => "8.4.0", "minimum_wire_compatibility_version" => "6.8.0", "minimum_index_compatibility_version" => "6.0.0-beta1", ], "tagline" => "You Know, for Search", ] 如果没有信息，可以尝试添加端口。
4. 读取数据
通过 es 的语法即可获取到对应信息，例如查询可以使用 get 方法。如下 >>> app('es')->get(["index"=>"users","id"=>1]) # --------- 信息 --------- => [ "_index" => "users", "_type" => "_doc", "_id" => "1", "_version" => 3, "_seq_no" => 6, "_primary_term" => 1, get ( ) 方法用于读取文档，index 代表要读取的索引名称， ID 则是指定要读取的文档 ID。
