免费推广网站入口2023燕/舆情分析系统
都在sakila数据库中执行
优化流程：
1.打开慢查询日志，打开未使用索引的查询
--打开所有没有使用索引的查询
show variables like "log_queries_not_using_indexes";
set global log_queries_not_using_indexes=on;
--打开慢查询
show variables like "long_query_time";
set global slow_query_log=on;
2.分析慢查询日志
mysqldumpslow -s c -t 10 DELL-670024CA3F-slow.log
DELL-670024CA3F-slow.log
3.找出待优化语句分析
explain select * from address \G
mysql> desc select * from address \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: address 查询的是那张表
type: ALL 重要的列，查询使用了何种类型。最好到最差const(常数;主键索引、唯一索引)、 eq_reg(使用有唯一性索引查找)、ref(非唯一性索引访问)、range(以范围的形式扫描索引)、index(按索引次序扫描表，就是先读索引，再读实际的行，其实还是全表扫描。主要优点是避免了排序，因为索引是排好序的。)、ALL(全表扫描，MySQL 从头到尾扫描整张表查找行。)
possible_keys: NULL 可能用到的索引有那些
key: NULL 实际用到的索引
key_len: NULL 使用的索引长度,越短越好;索引列类型的长度
ref: NULL 显示索引的那一列被使用了，常数最好
rows: 603 影响的行数
Extra: NULL
1 row in set (0.00 sec)
explain select max(payment_date) from payment \G
count不包含null的值
select count(*) '全部',count(id) as '非null' from t;数据条数不一样
select count(release_year='2006' or null) as '2006年',count(release_year='2007' or null) as '2007年' from film;
子查询优化
select title,release_year,LENGTH
from film where
film_id in(select film_id from film_actor where actor_id
in(select actor_id from actor where first_name='sandra' ))
group by优化
优化前
select actor.first_name,actor.last_name,count(*)
from sakila.film_actor inner join sakila.actor using(actor_id) group by film_actor.actor_id
优化后
select actor.first_name,actor.last_name,c.cnt
FROM sakila.actor INNER JOIN(
SELECT actor_id,COUNT(*) AS cnt FROM sakila.film_actor GROUP BY
actor_id) AS c USING (actor_id);
limit优化
优化前
SELECT film_id ,description FROM sakila.film ORDER BY title LIMIT 50,5;
优化步骤1:使用有索引的列进行order by操作
explain SELECT film_id ,description FROM sakila.film ORDER BY film_id LIMIT 50,5 \G
--越往后 扫描行数越多
explain SELECT film_id ,description FROM sakila.film ORDER BY film_id LIMIT 500,5 \G
--记录上一次的id 不用全表扫面
explain SELECT film_id ,description FROM sakila.film where film_id>500 and film_id<=550 ORDER BY film_id LIMIT 1,5 \G
合适的列建立索引
例:
select * from payment where staff_id=2 and customer_id=574;
在 staff_id 建立还是在 customer_id建立
create index idx_payment_1 on payment (staff_id,customer_id)
create index idx_payment_1 on payment (customer_id,staff_id)
字段唯一值越多，离散度越好
mysql> select count(distinct customer_id),count(distinct staff_id) from payment;
+-----------------------------+--------------------------+
| count(distinct customer_id) | count(distinct staff_id) |
+-----------------------------+--------------------------+
| 599 | 2 |
+-----------------------------+--------------------------+
customer_id离散度大
create index idx_payment_1 on payment (customer_id,staff_id)
重复冗余索引
create table test(
id int not null primary key,--主键索引
name varchar(10) not null,
title varchar(50) not null,
unique(id) --唯一索引
)engine=innodb;
create table test(
id int not null primary key,
name varchar(10) not null,
title varchar(50) not null,
unique(id)
)engine=innodb;
--检查重复索引 在information_schema数据库中
SELECT a.table_schema as '数据名'
,a.table_name as '表名'
,a.index_name as '索引1'
,b.INDEX_NAME as '索引2'
,a.COLUMN_NAME AS '重复列名'
from STATISTICS a join STATISTICS b on
a.table_schema=b.table_schema and a.table_name=b.table_name
and a.seq_in_index=b.seq_in_index and a.column_name=
b.column_name where a.seq_in_index=1 and a.index_name<>b.index_name
检查冗余索引
pt_duplicate-key-checker
选择适合的数据类型,使用int来存储日期时间，利用FROM_UNIXTIME(),UNIX_TIMESTAMP()两个函数进行转换
日期 :
用int 存日期 用UNIX_TIMESTAMP存 用FROM_UNIXTIME取
CREATE TABLE test(id INT AUTO_INCREMENT NOT NULL,timestr INT,PRIMARY KEY(id));
INSERT INTO test(timestr)VALUES(UNIX_TIMESTAMP('2014-06-01 13:12:00'));
SELECT FROM_UNIXTIME(timestr) FROM test;
IP地址:
用bigint存储ip地址 用INET_ATON存 用INET_NTOA取
CREATE TABLE sessions(id INT AUTO_INCREMENT NOT NULL,ipaddress BIGINT,PRIMARY KEY(id));
INSERT INTO sessions(ipaddress) VALUES(INET_ATON('192.168.0.1'));
SELECT INET_NTOA(ipaddress) FROM sessions;
设计范式
第一范式：一张表中不能有重复的列
第二范式: 每列都和主键相关
第三范式：都和主键直接相关
反范式化
增加冗余用磁盘换性能
表的垂直拆分
把大字段不经常用的表字段，独立到另一个新表中
表的水平拆分
按照字段分区
面临困难，跨分区表查询。统计等困难