mysql索引的分析和优化

准备建表语句

//创建数据库
CREATE DATABASE Mysql_Study;

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

创建表
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 '修改后的字段注释';

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

ALTER TABLE customer_message ADD INDEX(customer_message_name,customer_message_num)

　　

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

　　

 

 like查询和索引相关

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

#删除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

　重新创建索引

ALTER TABLE customer_message ADD INDEX(customer_message_name)

　索引判断sql

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

 

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

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

 

 

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

　　

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

新增数据

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

　　

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

　　

 

 

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

　　

 

 

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

　　

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

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

EXPLAIN SELECT customer_message_id  FROM  customer_message  ORDER BY  customer_message_id