Оптимизация операторов SQL

база данных MySQL

SQL语句的优化

Как запрашивать каналы для SQL с проблемами производительности

  1. Получите SQL с проблемами производительности из отзывов пользователей

  2. Получите SQL с проблемами производительности через журнал медленной проверки

  3. Получите SQL в реальном времени с проблемами производительности

Введение в журналы медленных запросов

  • slow_quey_log = при запуске записи журнала медленных запросов

  • slow_query_log_file указывает путь хранения и файл журнала медленных запросов (по умолчанию он хранится в каталоге данных MySQL)

  • long_query_time указывает порог для записи медленного выполнения sql журнала запросов (по умолчанию 10 секунд, обычно более подходящим является 0,001 секунды)

  • log_queries_not_using_indexes, следует ли регистрировать SQL неиспользуемых индексов

    set global sql_query_log=on;

    sysbench --test=./oltp.lua --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=tests --mysql-user=sbtest --mysql-password=123456 --oltp-tables-count=10 --mysql-socket=/usr/local/mysql/data/mysql.sock run

Инструмент анализа журнала медленных запросов

mysqldumpslow

  • Суммируйте тот же самый SQL, за исключением условий запроса, и выведите результаты анализа в порядке, указанном в параметрах.

    mysqldumpslow -s r -t 10 slow-mysql.log

    -s order(c,t,l,r,at,al,ar)[указать, в каком порядке выводить результаты]

    1. c сортировать по количеству запросов
    2. t отсортировано по общему времени запроса
    3. l Сортировка по времени блокировки в запросе
    4. r сортировать по общему количеству строк, возвращенных в запросе
    5. at, al, ar среднее число для сортировки

    -t top [укажите, что первые несколько будут конечными выводами]

pt-query-digest

pt-query-digest \

--explain h=127.0.0.1,u=root,p=p@ssWord \

slow-mysql.log

pt-query-digest --explain h=127.0.0.1 slow-mysql.log > slow.rep

Получите SQL в реальном времени с проблемами производительности

select id,user,host,db,command,time,state,info FROM information_schema.processlist WHERE time>=60

Почему скорость запроса такая низкая?

  1. Клиент отправляет запрос SQL на сервер
  2. Сервер проверяет, можно ли найти SQL в кеше запросов.
  3. Сервер выполняет синтаксический анализ SQL, предварительную обработку, а оптимизатор генерирует соответствующий план выполнения.
  4. В соответствии с планом выполнения вызовите API механизма хранения для запроса данных.
  5. вернуть результат клиенту

》 Для системы с частыми операциями чтения и записи использование кеша запросов, вероятно, снизит эффективность обработки запросов. Рекомендуется не использовать кеш запросов.

2.其中涉及的参数：
	query_cache_type 设置查询缓存是否可用[ON,OFF,DEMAND]

		DEMAND表示只有在查询语句中使用了SQL_CACHE和SQL_NO_CACHE来控制是否需要进行缓存

	query_cache_size 设置查询缓存的内存的大小

	query_cache_limit 设置查询缓存可用的存储的最大值（加上SQL_NO_CACHE可以提高效率）			
	
	query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据

	query_cache_min_res_unit 设置查询缓存分配的内存块最小单位

3.MySQL依照这个执行计划和存储引擎进行交互
	解析SQL，预处理。优化SQL的查询计划

	语法解析阶段是通过关键字对MySQL语句进行解析，并生成一颗对应的解析树
		MySQL解析器将使用MySQL语法规则验证和解析查询，包括检查语法是否使用了正确的关键走；关键字的顺序是否正确等等；

	预处理阶段是根据MySQL规则进一步检查解析树是否合法	
		检查查询中所涉及的表和数据列是否存在及名字或别名是否存在歧义等等
		语法检查通过了，查询优化器就可以生成查询计划了

	优化器SQL的查询计划阶段对上一步所生成的执行计划进行选择基于成本模型的最优的执行计划【下面是影响选择最优的查询计划的7因素】
		1.统计信息不准确
		2.执行计划中的成本估算不等于实际的执行计划的成本
		3.MySQL优化器认为的最优的可能与你认为最优的不一样【基于成本模型选择最优的执行计划】
		4.MySQL从不考虑其他的并发的查询，这可能会影响当前查询的速度
		5.MySQL有时候也会基于一些固定的规则来生成执行计划
		6.MySQL不会考虑不受其控制的成本
	查询优化器在目前的版本中可以进行优化的SQL的类型：
		1.重新定义表的关联顺序
		2.将外连接转化为内连接
		3.使用等价变换规则
		4.优化count(),min()和max()[select tables optimozed away]
		5.将一个表达式转化为一个常数表达式
		6.子查询优化
		7.提前终止查询
		8.对in()条件进行优化

Как определить время, затраченное на различные этапы обработки запроса

  • Использовать профиль [устарело и будет удалено из mysql в будущем]

    1. установить профилирование = 1; [начальный профиль, это конфигурация уровня сеанса]
    2. выполнить запрос
    3. показать профили; [просмотреть информацию об общем времени, затраченном на каждый запрос]
    4. показать профиль для запроса N; [время, затраченное на каждый этап запроса]
    5. показать профиль ЦП для запроса N; [Просмотреть информацию о времени и информацию о потреблении ЦП на каждом этапе]

  • использовать представление_схемы

    1. Информация таблицы мониторинга и истории, необходимая для запуска

      update setup_instruments set enabled='yes',timed='yes' where name like 'stage%';

      update setup_consumers set enabled='yes' where name like 'events%';

    2. SELECT a.thread_id, sql_text, c.event_name, (c.timer_end - c.timer_start) / 1000000000 AS 'duration(ms)' FROM events_statements_history_long a JOIN threads b on a.thread_id=b.thread_id JOIN events_stages_history_long c ON c.thread_id=b.thread_id AND c.event_id between a.event_id and a.end_event_id WHERE b.processlist_id=CONNECTION_ID() AND a.event_name='statement/sql/select' ORDER BY a.thread_id,c.event_id

Специальные оптимизации SQL-запросов

  • Обновления и удаления больших таблиц

      delimiter ?
  use 'imooc'?
  drop procedure if exists 'p_delete_rows'?
  create definer='root'@'127.0.0.1' procedure 'p_delete_rows'()
  begin 
  declare v_rows int;
  set v_rows int,
  while v_rows=1,
  while v_rows>0
  do 
  	delete from test where id>=9000 and id<=19000 limit 5000;
  	select row_count() into v_rows;
  	select sleep(5);
  end while;
  end ?
  delimiter;

  • Как изменить структуру таблицы большой таблицы

    1. Изменение типа поля столбца в таблице по-прежнему блокирует таблицу при изменении ширины поля.

    2. Не удалось решить проблему задержки базы данных master-slave.

    Модифицированный метод:

      pt-online-schema-change 
  --alter="modify c varchar(150) not null default''" 
  --user=root --password=PassWord D=testDataBaseName,t=tesTableName 
  --charset=utf-8 --execute

  • Как оптимизировать запросы not in и 

      #原始的SQL语句
  SELECT
  	customer_id,
  	first_name,
  	last_name,
  	email
  FROM
  	customer
  WHERE
  	customer_id NOT IN (
  		SELECT
  			customer_id
  		FROM
  			payment
  	)
   
  #优化后的SQL语句
  	SELECT
  		a.customer_id,
  		a,
  		first_name,
  		a.last_name,
  		a.email
  	FROM
  		customer a
  	LEFT JOIN payment b ON a.customer_id = b.customer_id
  	WHERE
  		b.customer_id IS NULL

  • Оптимизация методом сводных таблиц #Подсчитаем количество комментариев к продукту (если записей сотни миллионов, очень медленно выполнять полное сканирование таблицы) [SQL перед оптимизацией] выберите count(*) из product_comment, где product_id=999;

      #汇总表就是提前以要统计的数据进行汇总并记录到数据库中以备后续的查询使用

  create table product_comment_cnt(product_id int,cnt int);

  #统计商品的评论数[优化后的SQL]
  #查询出每个商品截止到前一天的累计评论数+当天的评论数
  select sum(cnt) from(
  	select cnt from product_comment_cnt where product_id=999
  	union all
  	select count(*) from product_comment where product_id=999
  	and timestr>DATE(NOW())
  ) a
Категории