2.24高校培训200注册用户，数据库慢sql优化

https://sw.supermapol.com/trace

一、其中sso（查了5遍）：35-40%

1. 6-10% 0.3-0.5s

select u.id, u.nickname, u.email, u.tel, u.tel_area, u.encrypted_password, u.company, u.real_name,u.create_at,u.is_freeze,u.is_sub_user,u.sub_user_role,u.sub_user_company_name,u.sub_user_company_url,u.sub_user_company_id,u.head_url,u.main_user_id , l.sign_in_count, l.last_sign_in_at, l.current_sign_in_at, l.last_sign_in_ip, l.current_sign_in_ip from users u left join login_record l on u.id = l.user_id where tel = ? and is_exist = 1

2. 6-10%

select u.id, u.nickname, u.email, u.tel, u.tel_area, u.encrypted_password, u.company, u.real_name,u.create_at,u.is_freeze,u.is_sub_user,u.sub_user_role,u.sub_user_company_name,u.sub_user_company_url,u.sub_user_company_id,u.head_url,u.main_user_id , l.sign_in_count, l.last_sign_in_at, l.current_sign_in_at, l.last_sign_in_ip, l.current_sign_in_ip from users u left join login_record l on u.id = l.user_id where nickname = ? and is_exist = 1

3. 6-10%

select u.id, u.nickname, u.email, u.tel, u.tel_area, u.encrypted_password, u.company, u.real_name,u.create_at,u.is_freeze,u.is_sub_user,u.sub_user_role,u.sub_user_company_name,u.sub_user_company_url,u.sub_user_company_id,u.head_url,u.main_user_id , l.sign_in_count, l.last_sign_in_at, l.current_sign_in_at, l.last_sign_in_ip, l.current_sign_in_ip from users u left join login_record l on u.id = l.user_id where tel = ? and is_exist = 1

4. 6-10%

select u.id, u.nickname, u.email, u.tel, u.tel_area, u.encrypted_password, u.company, u.real_name,u.create_at,u.is_freeze,u.is_sub_user,u.sub_user_role,u.sub_user_company_name,u.sub_user_company_url,u.sub_user_company_id,u.head_url,u.main_user_id , l.sign_in_count, l.last_sign_in_at, l.current_sign_in_at, l.last_sign_in_ip, l.current_sign_in_ip from users u left join login_record l on u.id = l.user_id where tel = ? and is_exist = 1

5. 6-10%

select u.id, u.nickname, u.email, u.tel, u.tel_area, u.encrypted_password, u.company, u.real_name,u.create_at,u.is_freeze,u.is_sub_user,u.sub_user_role,u.sub_user_company_name,u.sub_user_company_url,u.sub_user_company_id,u.head_url,u.main_user_id , l.sign_in_count, l.last_sign_in_at, l.current_sign_in_at, l.last_sign_in_ip, l.current_sign_in_ip from users u left join login_record l on u.id = l.user_id where nickname = ? and is_exist = 1

解决：

1.加索引

ALTER TABLE users ADD index index_id(id) USING BTREE;

ALTER TABLE users ADD index index_nickname(nickname) USING BTREE;

ALTER TABLE users ADD index index_tel(tel) USING BTREE;

ALTER TABLE users ADD index index_email(email) USING BTREE;

(错误不该加)ALTER TABLE users ADD index index_is_exist(is_exist) USING BTREE;

ALTER TABLE login_record ADD index index_user_id(user_id) USING BTREE;

优化 0.6-0.7s

2. 减少查询次数
3. 加缓存

二、token（10%）

https://www.supermapol.com/services/security/tokens.json

做成环境变量

优化 0.5

三、portal 40%

1. 4% 02s

select username from users join user_roles on users.id = user_roles.userid join roles on user_roles.roleid = roles.id where rolename =?

select username from users join user_roles on users.id = user_roles.userid join roles on user_roles.roleid = roles.id where rolename = 'PORTAL_USER'

2. 4% 0.2s

select username from users join user_roles on users.id = user_roles.userid join roles on user_roles.roleid = roles.id where rolename =?

select username from users join user_roles on users.id = user_roles.userid join roles on user_roles.roleid = roles.id where rolename = 'NOPASSWORD'

3. 4% 0.2s

select username from users join user_roles on users.id = user_roles.userid join roles on user_roles.roleid = roles.id where rolename =?

select username from users join user_roles on users.id = user_roles.userid join roles on user_roles.roleid = roles.id where rolename = 'DATA_CENTER'

已有索引，无需加

ALTER TABLE roles ADD index index_id(id) USING BTREE;

ALTER TABLE user_roles ADD index index_userid(userid) USING BTREE;

ALTER TABLE user_roles ADD index index_roleid(roleid) USING BTREE;

ALTER TABLE users ADD index index_id(id) USING BTREE;

根本问题

是用户数据越来越大

select count(*) from users; 111713

select count(*) from user_roles; 333721

select count(*) from user_infos; 111090.

解决

sso数据库增加索引

#users

ALTER TABLE users ADD index index_id(id) USING BTREE;

ALTER TABLE users ADD index index_nickname(nickname) USING BTREE;

ALTER TABLE users ADD index index_tel(tel) USING BTREE;

ALTER TABLE users ADD index index_email(email) USING BTREE;

#login_record

ALTER TABLE login_record ADD index index_user_id(user_id) USING BTREE;

#authentications

ALTER TABLE authentications ADD index index_uid(uid) USING BTREE;

ALTER TABLE authentications ADD index index_user_id(user_id) USING BTREE;

#certificate_record

ALTER TABLE certificate_record ADD index index_user_id(user_id) USING BTREE;

#company

ALTER TABLE company ADD index index_user_id(user_id) USING BTREE;

#merge_record

ALTER TABLE merge_record ADD index index_old_user_id(old_user_id) USING BTREE;

ALTER TABLE merge_record ADD index index_new_user_id(new_user_id) USING BTREE;

查询5次

userService 195