mysql索引

0 docker上运行mysql容器

docker run  -d  -p 3306:3306 \
--name mysql \
--privileged=true \
--restart=unless-stopped \
-v /usr/local/docker/myMysql/conf/mysql/conf.d:/etc/mysql/conf.d \
-v /usr/local/docker/myMysql/logs:/var/log/mysql \
-v /usr/local/docker/myMysql/data/mysql:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
mysql:8.0.20

1 先建表，插入数据，建索引，脚本如下：

--建表
create  table User(
                      id bigint auto_increment primary key,
                      name nvarchar(100),
                      age int,
                      position nvarchar(100)
);
--插入数据
INSERT INTO User (name, age, position)
SELECT
    CONCAT('User', FLOOR(RAND() * 100000000) + 1) AS name,
    FLOOR(RAND() * 100) + 1 AS age,
    CONCAT('Position', FLOOR(RAND() * 100000000) + 1) AS position
FROM
    INFORMATION_SCHEMA.COLUMNS as a
        CROSS JOIN
    INFORMATION_SCHEMA.COLUMNS as b
LIMIT 1000;
--建索引
create  index idx_nameagepos on User(name,age,position);
#具体查询
EXPLAIN select  id,name,age,position from User where name = 'User10052562' and age='38';
EXPLAIN select  id,name,age,position from User where  age='38' and position='Position57818398';
EXPLAIN select  id,name,age,position from User where  age='38';
--查看索引
show index  in User;

2 相关分析，总是只看理论，记东西，时间真的会带走一切。   

1 索引最左前缀法则。

CREATE INDEX idx_User_age_name_email on tb_User(name,age,email);


EXPLAIN select * from `tb_User` where name = '张三'; -- 走索引
EXPLAIN select * from `tb_User` where age = 12; --没走

EXPLAIN select * from `tb_User` where name='ss' and email = '44'; --只走了name索引

 

 

3 很好的总结地址：https://www.yuque.com/luxiaopeng/xswezc/7-sheng-chan-jing-yan-ru-he-dui-sheng-chan-huan-ji

4 优化：https://pan.baidu.com/s/1piS51ABGyfIRgZphaNevpw#list/path=%2Fsharelink3232509500-284484525029016%2F%E8%B5%84%E6%96%99-MySQL%E9%AB%98%E7%BA%A7%E6%95%99%E7%A8%8B&parentPath=%2Fsharelink3232509500-284484525029016

https://pan.baidu.com/s/1piS51ABGyfIRgZphaNevpw 提取码：pxuc

5 分页查询优化

--表结构 100万条数据
create table tb_user(id BIGINT AUTO_INCREMENT PRIMARY KEY,
name nvarchar(100),
age BIGINT,
status BIT,
email nvarchar(100));
--正常执行分页查询 用时3s592ms
select id,name,age,status,email from `tb_User` LIMIT 100000,10;
--优化 1s978ms 这个优化还是很明显的
select a.id,a.name,a.age,a.status,a.email
from `tb_User` as a,
(select id from `tb_User` ORDER BY id LIMIT 100000,10) as b 
where a.id=b.id;

----索引优化
--加索引

  CREATE INDEX idx_User_age_name_email on tb_User(name,age,email);

  --未走索引365ms

  select id,name,age,status,email from `tb_User` where status=1 and email='3';

  --走索引 2ms

  explain select id,name,age,status,email from `tb_User` where name='asds'

  and status=1 and email='3';