一、索引
索引是快速查询的关键,MySQL索引的建立对于MySQL的高效运行是很重要的,对于较少的数据量,有没有索引可能没什么影响,但是当数据量较大时,查询性能会急剧下降,特别是进行条件查询的时候
二、索引的类型
- 1、主键索引 PRIMARY KEY
主键索引是一种特殊的唯一索引,它不允许有空值,一般在创建表的时候创建,而且一个表只能有一个主键
创建表的时候创建主键
CREATE TABLE test(
id int(0) NOT NULL AUTO_INCREMENT,
hotelName varchar(255) DEFAULT NULL,
PRIMARY KEY('id')
);
使用ALTER命令添加主键
ALTER TABLE test ADD PRIMARY KEY('id');
- 2、普通索引
这是最基本的索引,没有做任何限制
创建表的时候指定
CREATE TABLE test(
id int(0) NOT NULL AUTO_INCREMENT,
hotelName varchar(255) DEFAULT NULL,
PRIMARY KEY('id'),
INDEX `hotelName`(`hotelName`)
);
使用ALTER命令添加普通索引
ALTER TABLE test ADD INDEX hotelName(hotelName)
- 3、唯一索引 UNIQUE
唯一索引列的值必须是唯一的,可以有空值,如果是组合索引,那么列值的组合必须是唯一的
创建表的时候指定
CREATE TABLE test(
id int(0) NOT NULL AUTO_INCREMENT,
hotelName varchar(255) DEFAULT NULL,
PRIMARY KEY('id'),
UNIQUE INDEX `hotelName`(`hotelName`)
);
使用ALTER命令添加唯一索引
ALTER TABLE test ADD UNIQUE hotelName(hotelName)
- 4、组合索引
就是一个索引包含多个列
使用ALTER命令添加添加组合索引
ALTER TABLE test ADD INDEX indexName(列1, 列2, 列3....)
- 5、全文索引 FULLTEXT
全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。
创建表的时候指定
CREATE TABLE test(
id int(0) NOT NULL AUTO_INCREMENT,
hotelName varchar(255) DEFAULT NULL,
PRIMARY KEY('id'),
FULLTEXT INDEX `hotelName`(`hotelName`)
);
使用ALTER命令添加添加全文索引
ALTER TABLE test ADD FULLTEXT (hotelName)
三、创建索引的一般原则
- 1、对于查询频率高的字段创建索引
索引的主要目的就是为了提升查询的所读,所以在查询频率高的字段上要创建索引
-
2、对排序、分组、联合查询频率高的字段创建索引
-
3、索引的数量尽量要少,不要创建不必要的索引
没创建一个索引都会占用相应的物理控件
过多的索引会导致insert、update、delete语句的执行效率降低
-
4、如果需要将多列设置为索引时,可以采用多列索引
-
5、选择唯一性索引
唯一性索引的值时唯一的,可以更快速的通过该索引来确定某条记录
- 6、尽量使用数据量少的索引
如果索引的值很长,那么查询速度会受到影响
- 7、尽量使用前缀来索引
如果索引字段的值很长,最好使用值的前缀来索引
- 8、删除不在使用或者很少使用的索引
对于一些不经常使用的索引,应当及时的删除,从而减少索引对更新操作的影响
四、执行计划 EXPLAIN
通过使用MySQL的执行计划来查看sql语句的执行效率,分析执行计划的各个显示内容,找出查询效率低的原因
EXPLAIN SELECT hotelID,c.city FROM hotelInfoApi as h, (SELECT city FROM hotelInfoApi GROUP BY city) as c WHERE h.city=c.city
- 执行计划的id
select 查询的序列号,表示执行的顺序
- id相同,执行顺序由上至下
- id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
- 执行计划的select_type
查询的类型,主要用于区分普通查询、联合查询、子查询等
- SIMPLE:简单的select查询,查询中不包含子查询或者union
- PRIMARY:查询中包含子查询,最外层查询则被标记为primary
- SUBQUERY/MATERIALIZED:SUBQUERY表示在select或where列表中包含了子查询，MATERIALIZED：表示 where 后面 in 条件的子查询
- UNION:表示 union 中的第二个或后面的 select 语句
- UNION RESULT: union的结果
- DERIVED:派生表
- 执行计划的table
查询涉及到的表
- 直接显示表名或者表的别名
- <unionM,N> 由 ID 为 M，N 查询 union 产生的结果
由 ID 为 N 查询产生的结果
- 执行计划的type
访问类型, SQL查询优化中的一个很重要的指标,结果值从好到坏依次是:system > const > eq_ref > ref > range > index > ALL。
- system:系统表，少量数据，往往不需要进行磁盘IO
- const:常量连接
- eq_ref:主键索引（primary key）或者非空唯一索引（unique not null）等值扫描
- ref:非主键非唯一索引等值扫描
- range:范围扫描
- index:索引树扫描
- ALL:全表扫描(full table scan)
-
执行计划possible_keys
查询过程中有可能用到的索引 -
执行计划key
实际使用的索引,如果为NULL,则没有使用索引 -
执行计划rows
根据表统计信息或者索引选用情况,大致估算出找到所需的记录所需读取的行数 -
执行计划filtered
表示返回的行数需读取行数的百分比,filtered的值越大越好. -
执行计划Extra
额外信息
- Using filesort:MySQL 对数据使用一个外部的文件内容进行了排序，而不是按照表内的索引进行排序读取。
- Using temporary:使用临时表保存中间结果，也就是说 MySQL 在对查询结果排序时使用了临时表，常见于order by 或 group by。
- Using index:表示 SQL 操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高。
- Using index condition:表示 SQL 操作命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。
- Using where:表示 SQL 操作使用了 where 过滤条件。
- Select tables optimized away:基于索引优化 MIN/MAX 操作或者 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化。
- Using join buffer(Block Nested Loop):表示 SQL 操作使用了关联查询或者子查询，且需要进行嵌套循环计算。
五、慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，MySQLl数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。
- 查看是否开启慢查询功能
show variables like 'slow_query%';
# slow_query_log 慢查询开启状态
# slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
show variables like 'long_query_time';
# long_query_time 查询超过多少秒才记录
- 开启慢查询
临时开启
set global show_query_log='ON';
set global slow_query_log_file='/usr/local/var/mysql/192-slow.log';
set global long_query_time=2;
永久开启,需要修改配置文件(mysql.cnf)
[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/var/mysql/192-slow.log
long_query_time = 2
END