MySQL索引简介索引优化_mysql alert index多个-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_45645534/article/details/120895393

索引简介

索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

例如：有一张person表，其中有2W条记录，记录着2W个人的信息。有一个Phone的字段记录每个人的电话号码，现在想要查询出电话号码为xxxx的人的信息。如果没有索引，那么将从表中第一条记录一条条往下遍历，直到找到该条信息为止。如果有了索引，那么会将该Phone字段，通过一定的方法进行存储，好让查询该字段上的信息时，能够快速找到对应的数据，而不必在遍历2W条数据了。

总之，索引可以提升查询效率，是一种排好序的快速查找数据结构。

结论

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础

上实现高级查找算法，这种数据结构就是索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

单值

假设有个user表

id name email phone

假如用户经常根据name查询

select * from user where name = '';

就可以建单值索引

create index idx_user_name on user(name)
--               命名规范        表   字段

复合

假如用户经常根据name和email查询

select * from user where name = '' and email = '';

就可以建复合索引

create index idx_user_nameEmail on user(name,email)

优势

提高数据检索效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势

索引本身也是一张表，该表保存了主键和索引字段，并指向实体表的记录，也会占用空间。
虽然索引大大提高了查询速度，同时却降低了更新表的速度，如INSERT,UPDATE,DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。

索引的分类

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引

索引列的值必须唯一，但允许有空值

复合索引

即一个索引包含多个列

基本语法

-- 添加索引
CREATE [UNIQUE] INDEX [索引名] ON 表名(列名(长度));
ALERT TABLE 表名 ADD [UNIQUE] [索引名] ON (列名(长度));

-- 删除
DROP INDEX [索引名] ON 表名;

-- 查看
SHOW INDEX FROM 表名；

哪些情况需要建索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引
Where条件里用不到的字段不创建索引
单键/组合索引的选择问题
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

哪些情况不适合建索引

表记录太少
经常增删改的表
如果某个数据列包含许多重复内容，那么给它简历索引就麻油太大的实际效果

索引优化

索引分析

单表

建表SQL

CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL , 
`views` INT(10) UNSIGNED NOT NULL , 
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');

案例

查询category_id为1且comments大于1的情况下，views最多的id和article_id。

mysql> select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
+----+-----------+
| id | author_id |
+----+-----------+
|  3 |         1 |
+----+-----------+
1 row in set (0.00 sec)

mysql> explain select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | article | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

-- 目标 type从ally优化，不出现Using filesort

--  开始优化
--  建索引
mysql> create index idx_article_ccv on article(category_id,comments,views);
-- type只从all优化到range，依旧有Using filesort
mysql> explain select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
| id | select_type | table   | type  | possible_keys   | key             | key_len | ref  | rows | Extra                       |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | article | range | idx_article_ccv | idx_article_ccv | 8       | NULL |    1 | Using where; Using filesort |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

按照BTree索引的工作原理，先排序category_id,如果遇到相同的category_id则再排序comments,如果遇到相同的comments则再排序

views。当comments字段在联合索引里处于中间位置时，因comments > 1条件是一个范围值(所谓range),MySQL无法利用索引再对后面

的views部分进行检索,即range类型查询字段后面的索引无效。

--  继续优化
--  删除索引
mysql> drop index idx_article_ccv on article;
--  新建索引
mysql> create index idx_article_cv on article(category_id,views);
mysql> explain select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys  | key            | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | article | ref  | idx_article_cv | idx_article_cv | 4       | const |    2 | Using where |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
--  此时type是ref，也没有using filesort

两表

建表SQL

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));

案例

左连接

mysql> explain select * from class left join  book on class.card = book.card;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL |   20 |       |
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |   20 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)

--  加索引（左？右？）
--  先加左
mysql> create index Y on class(card);
mysql> explain select * from class left join  book on class.card = book.card;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | class | index | NULL          | Y    | 4       | NULL |   20 | Using index |
|  1 | SIMPLE      | book  | ALL   | NULL          | NULL | NULL    | NULL |   20 |             |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)
--  依旧是查询20+20条记录，type从all，all到index，all

--  删索引
mysql> drop index Y on class;
--  新建索引，右
mysql> create index Y on book(card);

mysql> explain select * from class left join  book on class.card = book.card;
+----+-------------+-------+------+---------------+------+---------+--------------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref                | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+--------------------+------+-------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL               |   20 |             |
|  1 | SIMPLE      | book  | ref  | Y             | Y    | 4       | deptemp.class.card |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+--------------------+------+-------------+
2 rows in set (0.00 sec)
--  只查询了20+1条记录，type优化到了all和ref，比给左表建索引的效果好

在右表加索引效果好是由左连接特性决定的，left join条件用于确定如何从右表搜索行，左表数据一定都有，所以右表是关键点，一定要建索引。

三表

建表SQL

CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));

案例

三表左连接

mysql> explain select * from class left join book on class.card = book.card left join phone on book.card = phone.card;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL |   20 |       |
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |   20 |       |
|  1 | SIMPLE      | phone | ALL  | NULL          | NULL | NULL    | NULL |   20 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
3 rows in set (0.00 sec)

--  左连接在两个右表建索引
mysql> create index B on book(card);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index P on phone(card);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from class left join book on class.card = book.card left join phone on book.card = phone.card;
+----+-------------+-------+------+---------------+------+---------+--------------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref                | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+--------------------+------+-------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL               |   20 |             |
|  1 | SIMPLE      | book  | ref  | B             | B    | 4       | deptemp.class.card |    1 | Using index |
|  1 | SIMPLE      | phone | ref  | P             | P    | 4       | deptemp.book.card  |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+--------------------+------+-------------+
3 rows in set (0.00 sec)

索引失效（应该避免）

建表SQL

CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';

insert into staffs(NAME,age,pos,add_time) values('z3',22,'manager',NOW());
insert into staffs(NAME,age,pos,add_time) values('July',23,'dev',NOW());
insert into staffs(NAME,age,pos,add_time) values('2000',23,'dev',NOW());

案例（索引失效）

最佳左前缀法则
不在索引列上做任何操作（计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描。
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *
mysql在使用不等于(!=或者<>)、is null、is not null的时候无法使用索引会导致全表扫描
like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作
字符串不加单引号索引失效（因为mysql底层会进行转化，转化成字符串，因为对索引列进行了类型转换，导致索引失效）
用or连接也会使索引失效

对name字段使用了left函数，导致索引失效

mysql> explain select * from staffs where name = 'July';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 | Using where |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from staffs where left(name,4) = 'July';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

范围之后全失效

mysql> explain select * from staffs where name = 'z3' and age = 22 and pos = 'manager';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref               | rows | Extra       |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 140     | const,const,const |    1 | Using where |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from staffs where name = 'z3' and age > 21 and pos = 'manager';
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys           | key                     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78      | NULL |    1 | Using where |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-------------+
1 row in set (0.00 sec)
--  type从ref到range，效率变低

尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少select*

使用不等号使得索引失效

is null,is not null也会使得索引失效

解决like ‘%字符串%'时索引失效的方法？

方法：使用覆盖索引

建表SQL

CREATE TABLE tbl_user(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
email VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into tbl_user(NAME,age,email) values('1aa1',21,'b@163.com');
insert into tbl_user(NAME,age,email) values('2aa2',222,'a@163.com');
insert into tbl_user(NAME,age,email) values('3aa3',265,'c@163.com');
insert into tbl_user(NAME,age,email) values('4aa4',21,'d@163.com');
insert into tbl_user(NAME,age,email) values('aa',121,'e@163.com');

建索引

mysql> create index idx_tbluser_nameAge on tbl_user(Name,age);

id是主键，也可以使用索引

id，name，age自由组合都可以使用覆盖索引

多出字段email时索引失效

查询语句字符串没加单引号导致索引失效

mysql底层会给int类型的2000转换为字符串类型的’2000‘，由于进行了类型转换，所以索引失效