高性能mysql(2)分析几种sql

准备工作，建立一张表
CREATE TABLE t_books2 (
id bigint(20) NOT NULL AUTO_INCREMENT,
uuid varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ‘案件编号’,
book_name varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
class_name varchar(23) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (id),
)
首先在uuid上建立索引。
explain select * from t_books2 where uuid in(‘2’,’3’,’4’) 利用到索引 ;
explain select * from t_books2 where uuid not in(‘2’,’3’,’4’) 没有;
explain select * from t_books2 where uuid<>‘2’；没有
explain select * from t_books2 where uuid =1 没有;
explain select * from t_books2 where uuid like ‘1%’ 有;
explain select * from t_books2 where uuid like ‘%1’ 没有;
接着在book_name建立索引

explain select * from t_books2 where uuid like ‘1’ or book_name=’2’;利用到了
explain select * from t_books2 where uuid = ‘1’ or class_name=’2’ 没有;
explain select * from t_books2 where uuid = ‘1’ union
select * from t_books2 where class_name=’3’;部分使用

3删除之前建立的索引，建立组合索引：
（uuid，book_name，class_name）
select * from t_books where uuid=’1’ and class_name=’2’;只利用到了uuid的索引
select * from t_books where uuid like ‘1’%’ and class_name=’2’;只利用到了uuid的索引

select * from t_books where uuid = ‘1’ and class_name=’2’;只利用到了uuid的索引
select * from t_books where uuid = ‘1’ and book_name=’2’ and class_name=’33’;三列索引都用上

select * from t_books where uuid = ‘1’ and class_name=’2’ order(group) by book_name;
三列索引都用上
select * from t_books where uuid = ‘1’ gourp by class_name，book_name;只用上uuid

select * from t_books where uuid = ‘1’ gourp by book_name，book_name; 三列索引都用上