mysql索引的分析和优化

准备建表语句

?

1 2	//创建数据库 CREATE DATABASE Mysql_Study;

　　但是在创建表的时候，对于int类型，会有int(2),int(4)，int(11)这些类型选择

?

1 2 3 4 5 6

创建表 CREATE TABLE customer_message(   customer_message_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',   customer_message_name VARCHAR(20) COMMENT '客户姓名',   customer_message_num INT(4) COMMENT '信息查询次数' )

给表加上注释

ALTER TABLE customer_message COMMENT '客户信息'

修改表中字段的注释

alter table 表名 modify column 字段名 字段类型 comment '修改后的字段注释';

?

1 2 3 4 5 6

INSERT INTO customer_message(customer_message_name,customer_message_num)VALUE ('小白',1000), ('小红',2000), ('小六',2001), ('小黑',2002), ('小绿',2003)

　　sql语句分析

 

 select_type字段解释

 

type表示存储引擎查询数据时使用的方式

 

 

 key表示查询时真正使用到的索引，显示的是索引名称

其中possible_keys表示mysql分析时可能使用的索引(可能使用不一定会使用)，key表示实际用到的索引

rows表示查询结果需要扫描多少行，原则上是越少越好

key_len表示使用索引的字节数量，可以判断是否使用了组合索引

 

引入复合索引(组合索引)

组合索引创建sql

?

1	ALTER TABLE customer_message ADD INDEX(customer_message_name,customer_message_num)

　　

?

1 2	EXPLAIN SELECT * FROM customer_message WHERE customer_message_name='小六' AND customer_message_num=2001

　　

 

 like查询和索引相关

先删除前期创建的组合索引

?

1 2 3 4

#删除customer_message表的customer_message_name索引 DROP INDEX customer_message_name ON customer_message #删除customer_message表的customer_message_num索引 DROP INDEX customer_message_num ON customer_message

　重新创建索引

?

1	ALTER TABLE customer_message ADD INDEX(customer_message_name)

　索引判断sql

?

1	EXPLAIN SELECT * FROM customer_message WHERE customer_message_name LIKE '白%'

 

?

1	EXPLAIN SELECT * FROM customer_message WHERE customer_message_name LIKE '%小'

?

1	EXPLAIN SELECT * FROM customer_message WHERE customer_message_name LIKE '%小%'

 

 

?

1	EXPLAIN SELECT * FROM customer_message WHERE customer_message_name LIKE '小%'

　　

 索引搜索的数据最好是不要每条记录都带有的关键字，不然依旧是全表扫描

新增数据

?

1 2 3 4 5 6

INSERT INTO customer_message(customer_message_name,customer_message_num)VALUE ('寂寞寂寞就好',3001), ('不爱代码的程序员',3002), ('同学两亿岁',3003), ('不起眼',3004), ('未知空间',3005)

　　

?

1	EXPLAIN SELECT * FROM customer_message WHERE customer_message_name LIKE '寂寞%'

　　

 

 

?

1	EXPLAIN SELECT * FROM customer_message WHERE customer_message_name LIKE '不%'

　　

 

 

?

1	EXPLAIN SELECT * FROM customer_message WHERE customer_message_name LIKE '%就好'

　　

 mysql支持filesort和index两种方式的排序

如果我们Explain分析SQL，结果中Extra属性显示Using filesort，表示使用了filesort排序方式，需要优化。

?

1	EXPLAIN SELECT customer_message_id  FROM  customer_message  ORDER BY  customer_message_id