mysql调优实战
如何优化查询效率
问题引入----
首先我们想以下在一张数据表中mysql是怎样查找数据的?
假设有这么一张表students_info,字段名为 id 主键 ,name ,gender ,scor 等字段
这张表存放了学号从1-50000的学生信息,
接下来我想查询 id 为12345 的 学生信息,
查询语句------
Select * from students_info where id=12345;
这个时候mysql就会遍历整个表,一行一行的找,知道 id=12345 的时候,想想都比较费时间,
那怎么优化这种情况呢??
Mysql系统内部提供了一种方式------索引;
原理跟我们用的windows查找数据一样;在windows中如果开启了索引,则会在磁盘上开辟一块空间存储着文件目录,以便快速找出某个或多个的文件;
所以我们要在id上创建索引,那么在查找id=12345 的学,直接在索引里生信息 mysql不需要任何扫描直接在索引里找到12345就可以得知这一行的位置;
优化查询效率的方式----建立索引
索引不仅存在与操作系统中,有些软件中也会提供对应的索引来方便查找功能;
‘索引是为了方便我们查询不同的数据’--------一位IT大佬说的;
在mysql中 索引是在存储引擎中实现的,由于mysql中有不同的索引类型,因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。
所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。
MySQL中索引的存储类型有两种，即BTREE和HASH，具体和表的存储引擎相关；
MyISAM和InnoDB存储引擎只支持BTREE索引；
MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。
索引的优缺点
优点----
1,既然是索引,那就要求唯一性,通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
2,加快了查询效率—这也是创建索引的主要目的;
3,在数据参考完整性方面可以加速表之间的连接,因为一般索引是设置在主键上的;
4,在使用分组查询和排序时可以大大提高效率,减少查询和排序时间;
凡事都有两面性,
缺点----
1,创建索引需要消耗时间,数据更新的频繁,那么索引据需要重新创建;
2,索引文件也需要占用磁盘空间,随着数据的增多,索引文件也会增加(一般情况下----即非重复数据)如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
3,当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
可以查看windows索引文件----windows.edb,一般在C:\ProgramData\Microsoft\Search\Data\Applications\Windows 下,会发现该索引的大小并不是一成不变的
索引的分类—
Mysql中将索引分为-----
1,普通索引
普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
2,唯一索引
唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。
3,单列索引
单列索引即一个索引只包含单个列，一个表可以有多个单列索引。
4,组合索引
组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。
5,全文索引
全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。
6,空间索引
空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。
索引的选择—
1,索引并非越多越好,一个表中如果有 大量的索引,会影响CURD 操作;
2,选择合适的索引,监狱索引的特点,所以索引一般选择操作没有那么频繁的字段,对于经常操作的字段不适合做索引;
3,对于数据量小或者字段有大量重复值的情况下就不用建立索引.
例如有的字段就可以不用索引,比如性别,月份,星期等字段,本来字段不同的数据就很少,用不到索引,如果用了索引反而会得不偿失,降低查询速度;
4,当唯一性时某字段的要求时,可指定为唯一索引以确保数据的完整性,以提高查询效率;
5,在频繁排序或者分组的列上建立索引组合,以更快的实现排序或者分组;
创建索引的方式----
1,创建表的时候与索引一块创建;
方式为---------表级约束---->>>定义完字段后再定义
格式----
create table 表名 (
字段名1 数据类型1 约束1 ,
字段名2 数据类型2 约束2 ,
.......
index 索引别名 (索引列所在字段名)
);
---->>>索引别名可添加也可不添加,不添加则默认为(索引所在字段名)
创建普通索引----
mysql> create table testS(
-> col_id int ,
-> col_name varchar(6) ,
-> col_sex char(1) default '男' check(col_sex='男'||col_sex='女'),
-> index (col_id), primary key (col_id)
-> );
Query OK, 0 rows affected, 1 warning (0.03 sec)
-- 新版中中不建议使用|| ,而建议使用 or --
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1287
Message: '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead
1 row in set (0.00 sec)
插入数据-----查询数据
mysql> select * from tests2;
+--------+----------+---------+
| col_id | col_name | col_sex |
+--------+----------+---------+
| 1001 | null | 男 |
| NULL | null | 男 |
| 1009 | 张三 | 男 |
+--------+----------+---------+
3 rows in set (0.00 sec)
-- 查看表结构 --
mysql> show create table testS \G
*************************** 1. row ***************************
Table: testS
Create Table: CREATE TABLE `tests` (
`col_id` int NOT NULL,
`col_name` varchar(6) DEFAULT NULL,
`col_sex` char(1) DEFAULT '男',
PRIMARY KEY (`col_id`),
KEY `col_id` (`col_id`),
CONSTRAINT `tests_chk_1` CHECK (((`col_sex` = _gbk'??') or (`col_sex` = _gbk'?')))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
-- 查看索引是否正在使用 --
mysql> explain select * from tests2 where col_id=1009 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE ## 查询类型
table: tests2 ## 表名
partitions: NULL ## 分区
type: ref ##本数据表与其他数据表之间的关联关系
possible_keys: col_id ## 可选用的索引
key: col_id ##索引
key_len: 5 ##索引长度
ref: const ## 关联关系中另一个数据表里的数据列名
rows: 1 ## 数据行数
filtered: 100.00 ## 返回结果的行数占需读取行数的百分比----
Extra: NULL ##有关信息
1 row in set, 1 warning (0.00 sec)
删除索引后再次查询
alter table tests2 drop index col_id;
mysql> explain select * from tests2 where col_id=1009 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tests2
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 50.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
可以发现删除索引后, filtered的值变为了50,所以可以发现有索引和没有索引的区别----没有索引会扫描整张表,有则直接再索引中取用就可以了;
普通索引为最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度。
2,alter table 的方式在表中创建索引;
alter table 表名 add index 索引别名 (索引所在字段名);
---->>>索引别名可添加也可不添加,不添加则默认为(索引所在字段名)
紧接上表tests2,由于删除了索引,这次正好用alter的方式添加----
mysql> alter table tests2 add index coc_index (col_id) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>-- 再次删除索引----
mysql> drop index coc_index on tests2;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
3,create index的 方式创建索引;
create index 索引别名 on 表名(索引所在字段名);
再次创建索引----
mysql> CREATE INDEX coc_index ON tests2(col_id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
查看表结构----
## MYSQL 中默认不区分大小写
mysql> show create table tESts2 \G
*************************** 1. row ***************************
Table: tESts2
Create Table: CREATE TABLE `tests2` (
`col_id` int DEFAULT NULL,
`col_name` varchar(6) DEFAULT NULL,
`col_sex` char(1) DEFAULT '男',
KEY `coc_index` (`col_id`),
CONSTRAINT `tests2_chk_1` CHECK (((`col_sex` = _utf8mb4'男') or (`col_sex` = _utf8mb4'女')))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
以上为普通索引的创建方式,唯一索引,全文索引,空间索引创建的方式----触类旁通
只说一下他们之间的区别
普通索引与唯一索引之间的区别—
唯一索引------->>>
用唯一索引的主要原因是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表。
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
以上创建的索引为单列索引,还可以在多个列上添加索引;
多列索引—
单列索引是在数据表中的某一个字段上创建的索引，一个表中可以创建多个单列索引。这里是指每个列都是单独的,互不影响;
以 create index的方式来添加多列索引
mysql> create index index_name on tests2(col_name(3));
-- 这里**只有字符串可以设置索引长度**,索引长度要小于等于设定的字符串长度,由于建表时col_name 设置为 VARCHAR ( 6 ),所以索引长度最大就为6,
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
组合索引—
以alter table 的方式来添加组合索引
mysql> drop index coc_index on tests2;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table tests2 add unique index un_index (col_id,col_name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
--- 查看表信息 ----
mysql> show create table tests2 \G
*************************** 1. row ***************************
Table: tests2
Create Table: CREATE TABLE `tests2` (
`col_id` int DEFAULT NULL,
`col_name` varchar(6) DEFAULT NULL,
`col_sex` char(1) DEFAULT '男',
UNIQUE KEY `un_index` (`col_id`,`col_name`),
KEY `index_name` (`col_name`(3)),
CONSTRAINT `tests2_chk_1` CHECK (((`col_sex` = _utf8mb4'男') or (`col_sex` = _utf8mb4'女')))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
组合索引可起几个索引的作用，但是使用时并不是随便查询哪个字段都可以使用索引，而是遵从“最左前缀”：利用索引中最左边的列集来匹配行，这样的列集称为最左前缀
即遵循最左边的索引为基准,如果第一个数据一样,则比较第二个,第二个一样则比较第三个,
假设由id、name和age 3个字段构成的索引，索引行中按id、name、age的顺序存放，索引可以搜索（id,name, age）、（id, name）或者id字段组合,但是不能越过 id去搜索name,age 一句话必须要包含字段id的组合
演示-----
CREATE TABLE tests6 (
id INT,
name VARCHAR ( 6 ),
sex CHAR ( 1 ) ,
age INT,
INDEX index_zuhe( id,name, age )
)
> OK
> 时间: 0.049s
---插入数据---
insert into tests6 values
(1001,'张三','男',18),
(1008,'张三三','男',19),
(1011,'李四思','女',28),
(1004,'张三风','男',18),
(1018,'张二蛋','男',19),
(1021,'李帅','女',28),
(1031,'张王','男',18),
(1058,'张大大','男',19),
(1061,'李小小','女',28)
> Affected rows: 9
> 时间: 0.029s
-- 查询数据 --
mysql> explain select * from tests6 where id=1031 and name='张王'and age =18 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tests6
partitions: NULL
type: ref
possible_keys: index_zuhe
key: index_zuhe
key_len: 37
ref: const,const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tests6 where id=1031 and name='张王' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tests6
partitions: NULL
type: ref
possible_keys: index_zuhe
key: index_zuhe
key_len: 32
ref: const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tests6 where age='18' and name='张王' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tests6
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 9
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
可以发现当查询没有id 为索引的时候,就没有用到索引index_zuhe
全文索引----
FULLTEXT全文索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列创建索引。索引总是对整个列进行，不支持局部（前缀）索引。
创建全文索引-----
CREATE TABLE test07 ( id INT, NAME VARCHAR ( 6 ), age TINYINT, liketext LONGTEXT, FULLTEXT INDEX qw_index ( liketext ) );
mysql> show create table test07 \G
*************************** 1. row ***************************
Table: test07
Create Table: CREATE TABLE `test07` (
`id` int DEFAULT NULL,
`NAME` varchar(6) DEFAULT NULL,
`age` tinyint DEFAULT NULL,
`liketext` longtext,
FULLTEXT KEY `qw_index` (`liketext`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
有些书中说 innodb不支持全文索引,应该更新了,在mysql5.6之后就支持了,看官方文档给出的源码----
官方源码链接
mysql> CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial, we show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title | body |
+----+-------------------+------------------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)
全文索引的用途-----
FULLTEXT索引。全文索引非常适合于大型数据集，对于小的数据集，它的用处比较小。
空间索引—
mysql> create table test08 (
-> located geometry not null,
-> SPATIAL index spq_index (located)
-> );
Query OK, 0 rows affected, 1 warning (0.07 sec)
-- 警告信息---添加SRID 参考 SRID是指数据的坐标系 --
mysql> show warnings \G
*************************** 1. row ***************************
Level: Warning
Code: 3674
Message: The spatial index on column 'located' will not be used by the query optimizer since the column does not have an SRID attribute. Consider adding an SRID attribute to the column.
1 row in set (0.00 sec)
mysql> DROP TABLE TEST08 ;
Query OK, 0 rows affected (0.14 sec)
mysql> create table test08 (
-> located geometry not null srid 4456,
-> SPATIAL index spq_index (located)
-> );
Query OK, 0 rows affected (0.14 sec)
mysql>
查看表的索引信息–
mysql> show index from test08 \G
*************************** 1. row ***************************
Table: test08 -- 创建索引的表
Non_unique: 1 -- 索引为非唯一索引,0表示唯一,1表示非唯一
Key_name: spq_index--- 索引名
Seq_in_index: 1 -- 该索引所在在索引中的位置,单列索引为1,组合索引则显示处所在第几位;
Column_name: located -- 定义 索引的列
Collation: A
Cardinality: 0
Sub_part: 32 -- 索引长度
Packed: NULL
Null: ---该字段是否可为空,空白表示不能为空
Index_type: SPATIAL -- 索引类型
Comment: --- 注解
Index_comment: --- 索引注解
Visible: YES ----是否可见
Expression: NULL ----描述
1 row in set (0.04 sec)
删除索引
方式一------>>>>alter table … drop index…
mysql> alter table test08 drop index spq_index ;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
方式二------>>>>drop index … on…
mysql> drop index sqp_index on test08 ;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。
指定降序索引
我们创建索引时默认的为升序,如果需要则可以指定为降序索引
mysql> create table test0010(
-> id int ,
-> name varchar(6),
-> score int ,
-> index (id ,score desc)
-> );
Query OK, 0 rows affected (0.08 sec)
-----查看表的创建信息----
mysql> show create table test0010\G
*************************** 1. row ***************************
Table: test0010
Create Table: CREATE TABLE `test0010` (
`id` int DEFAULT NULL,
`name` varchar(6) DEFAULT NULL,
`score` int DEFAULT NULL,
KEY `id` (`id`,`score` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.04 sec)
插入数据-----
delimiter ;;
create procedure test_insert ()
begin
declare i int default 1;
while i<50000
do
insert into test0010 (id,score) select RAND()*50000,RAND()*50000;
set i=i+1;
end while ;
commit ;
end ;;
delimiter ;
call test_insert() ;
-- 降序索引
mysql> explain select * from test0010 order by id,score desc limit 6 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test0010
partitions: NULL
type: index
possible_keys: NULL
key: id
key_len: 10
ref: NULL
rows: 6
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
/* 降序索引只是对查询中特定的排序顺序有效，如果使用不当，反而查询效率更低。*/
-- 如以下查询方式 遍历了整张表,效率低---
mysql> explain select * from test0010 order by id desc , score desc limit 6 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test0010
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 50537
filtered: 100.00
Extra: Using filesort
1 row in set, 1 warning (0.00 sec)
Using filesort是MySQL里一种速度比较慢的外部排序，如果能避免是最好的结果。多数情况下，管理员可以通过优化索引来尽量避免出现Using filesort，从而提高数据库执行速度。
