1. Старайтесь не использовать select * для запроса SQL, а выбирать определенные поля.
- 反例子：
- select * from employee;
- 正例子：
- select id，name from employee;
причина:
Заполняйте только обязательные поля, чтобы сэкономить ресурсы и снизить нагрузку на сеть. Когда вы запрашиваете select *, вполне вероятно, чтоИндекс покрытия не будет использоваться, это вызовет запрос таблицы возврата.
2. Если вы знаете, что есть только один результат запроса или только максимальная/минимальная запись, рекомендуется использовать ограничение 1
Предположим, есть таблица сотрудников, и вы хотите найти человека по имени Джей.
CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`date` datetime DEFAULT NULL,
`sex` int(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
反例：
select id，name from employee where name='jay'
正例
select id，name from employee where name='jay' limit 1;
причина:
После добавления предела 1, пока соответствующая запись будет найдена, сканирование не будет продолжаться, и эффективность будет значительно повышена. Конечно,Если имя является уникальным индексом, нет необходимости добавлять ограничение 1, потому что наличие ограничения в основном предотвращает полное сканирование таблицы, тем самым повышая производительность, если сам оператор можно предсказать без полного сканирования таблицы, ограничений нет, разница в производительности невелика.
3. Старайтесь не использовать или в предложении where для соединения условий
新建一个user表，它有一个普通索引userId，表结构如下：
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`age` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
假设现在需要查询userid为1或者年龄为18岁的用户，很容易有以下sql
反例:
select * from user where userid=1 or age =18
正例：
//使用union all
select * from user where userid=1
union all
select * from user where age = 18
//或者分开两条sql写：
select * from user where userid=1
select * from user where age = 18
причина:
Использование или может сделать индекс недействительным, что приведет к полному сканированию таблицы.
В случае or+age без индекса предположим, что он проходит через индекс userId, но когда он достигает условия запроса возраста, он должен выполнить полное сканирование таблицы, то есть трехэтапный процесс: полное сканирование таблицы + сканирование индекса + слияние, если оно В начале выполняется полное сканирование таблицы, причем сканирование выполняется напрямую. В MySQL есть оптимизатор, который считается с точки зрения эффективности и стоимости, в случае или условия индекс может дать сбой, что кажется разумным.
4. Оптимизация лимита пейджинга
Когда мы выполняем ежедневные требования к подкачке, мы обычно используем ограничение для его достижения, но когда смещение особенно велико, эффективность запроса становится низкой.
反例：
select id，name，age from employee limit 10000，10
正例：
//方案一 ：返回上次查询的最大记录(偏移量)
select id，name from employee where id>10000 limit 10.
//方案二：order by + 索引
select id，name from employee order by id limit 10000，10
//方案三：在业务允许的情况下限制页数：
причина:
Когда смещение наибольшее, эффективность запроса будет ниже, потому что Mysql не пропускает смещение и напрямую извлекает следующие данные, а сначала добавляет смещение + количество элементов для выборки, а затем извлекает переднее смещение Данные на этом участке смены отбрасываются и возвращаются. Если используется схема оптимизации 1, возвращается последняя самая большая запись запроса (смещение), так что смещение можно пропустить, и эффективность значительно повышается. Схема 2 использует порядок по индексу +, что также может повысить эффективность запросов. В случае третьего плана рекомендуется обсудить с бизнесом, нужно ли после этого проверять пейджинг. Потому что подавляющее большинство пользователей не прокручивают слишком много страниц назад.
5. Оптимизируйте лайки
В повседневной разработке, если вы используете нечеткий запрос по ключевым словам, легко думать о подобном, но похожее, скорее всего, сделает ваш индекс недействительным.
反例：
select userId，name from user where userId like '%123';
正例：
select userId，name from user where userId like '123%';
причина:
Поставьте % впереди и не переходите к индексу, как показано ниже:Поставьте % после ключевого слова, и индекс все равно будет взят. следующее:
6. Используйте условия where, чтобы ограничить запрашиваемые данные, чтобы избежать возврата избыточных строк.
Предположим, бизнес-сценарий таков: запросите, является ли пользователь участником. Видели старый код реализации, как это. . .
反例：
List<Long> userIds = sqlMap.queryList("select userId from user where isVip=1");
boolean isVip = userIds.contains(userId);
正例：
Long userId = sqlMap.queryObject("select userId from user where userId='userId' and isVip='1' ")
boolean isVip = userId！=null;
причина:
Проверьте, какие данные необходимы, чтобы избежать возврата ненужных данных и сократить накладные расходы.
7. Старайтесь избегать использования встроенных функций MySQL для индексных столбцов.
Бизнес-требования: запрашивать пользователей, которые вошли в систему за последние семь дней (при условии, что loginTime индексируется).
反例：
select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();
正例：
explain select userId,loginTime from loginuser where loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);
причина:
В столбце индекса используется встроенная функция mysql, а индекс недействителен.изображение
Если в столбец индекса не добавляются встроенные функции, индекс все равно пойдет.
8. Старайтесь избегать операций с выражениями над полями в предложении where, что заставит систему отказаться от использования индексов и выполнить полное сканирование таблицы.
反例：
select * from user where age-1 =10；
正例：
select * from user where age =11；
причина:
Хотя возраст добавляет индекс, из-за операции над ним индекс напрямую теряется. . .
9. Внутреннее соединение, левое соединение, правое соединение, сначала используйте внутреннее соединение, если это левое соединение, результат левой таблицы должен быть как можно меньше.
- Внутреннее соединение внутреннее соединение, когда две таблицы объединяются и запрашиваются, сохраняются только те наборы результатов, которые точно совпадают в двух таблицах.
- Когда левое соединение выполняет запрос на соединение между двумя таблицами, оно возвращает все строки в левой таблице, даже если в правой таблице нет совпадающих записей.
- Когда правое соединение объединяет две таблицы, возвращаются все строки в правой таблице, даже если в левой таблице нет совпадающих записей.
Исходя из того, что все требования SQL выполнены, рекомендуется сначала использовать внутреннее соединение.Если используется левое соединение, результат данных в левой таблице должен быть как можно меньше, и, если возможно, попытаться обработать это слева.
反例:
select * from tab1 t1 left join tab2 t2 on t1.size = t2.size where t1.id>2;
正例：
select * from (select * from tab1 where id >2) t1 left join tab2 t2 on t1.size = t2.size;
причина:
Если внутреннее соединение является равным соединением, количество возвращаемых строк может быть относительно небольшим, поэтому производительность будет относительно выше. Точно так же, если используется левое соединение, результат данных в левой таблице должен быть как можно меньше, а условия должны располагаться как можно дальше слева, что означает, что количество возвращаемых строк может быть относительно маленький.
10. Старайтесь избегать использования оператора != или в предложении where, иначе движок откажется от использования индекса и выполнит полное сканирование таблицы.
反例：
select age,name from user where age <>18;
正例：
//可以考虑分开两条sql写
select age,name from user where age <18;
select age,name from user where age >18;
причина:
Использование != и , скорее всего, сделает индекс недействительным.
11. При использовании объединенного индекса обратите внимание на порядок столбцов индекса и, как правило, придерживайтесь принципа сопоставления самого левого.
Структура таблицы: (есть сводный индекс idx_userid_age, впереди userId, сзади age)
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`age` int(11) DEFAULT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_userid_age` (`userId`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
反例：
select * from user where age = 10;
正例：
//符合最左匹配原则
select * from user where userid=10 and age =10；
//符合最左匹配原则
select * from user where userid =10;
причина:
Когда мы создаем совместный индекс, такой как (k1, k2, k3), это эквивалентно созданию трех индексов (k1), (k1, k2) и (k1, k2, k3), что является самым левым принципом сопоставления. Совместный индекс не соответствует крайнему левому принципу, и индекс вообще не будет работать, но это также связано с оптимизатором Mysql.
12. Чтобы оптимизировать запрос, вам следует подумать о создании индексов для столбцов, связанных с параметрами «где» и «упорядочить по», и попытаться избежать полного сканирования таблицы.
反例：
select * from user where address ='深圳' order by age ;
正例：
添加索引
alter table user add index idx_address_age (address,age)
13. Если вставляется слишком много данных, рассмотрите возможность пакетной вставки.
反例：
for(User u :list){
INSERT into user(name,age) values(#name#,#age#)
}
正例：
//一次500批量插入，分批进行
insert into user(name,age) values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.name},#{item.age})
</foreach>
причина:
Производительность массовой вставки хорошая, что экономит время Аналогия: если вам нужно переместить 10 000 кирпичей на крышу, у вас есть лифт, лифт может вместить соответствующее количество кирпичей за раз (до 500), вы можете транспортировать по одному кирпичу или 500. за раз, как вы думаете, что занимает больше всего времени?
14. При необходимости используйте покрывающие индексы.
Покрывающий индекс может сделать так, что вашему оператору SQL не нужно будет возвращаться к таблице, просто получите доступ к индексу, чтобы получить все необходимые данные, что значительно повышает эффективность запроса.
反例：
// like模糊查询，不走索引了
select * from user where userid like '%123%';
正例：
//id为主键，那么为普通索引，即覆盖索引登场了。
select id,name from user where userid like '%123%';
15. Осторожно используйте ключевое слово different
Отличительное ключевое слово обычно используется для фильтрации повторяющихся записей, чтобы получить уникальные записи. При использовании в случае запроса одного поля или нескольких полей он обеспечивает оптимизацию запроса. Однако его использование при большом количестве полей значительно снизит эффективность запроса.
反例：
SELECT DISTINCT * from user;
正例：
select DISTINCT name from user;
причина:
Процессорное время и время заполнения оператора с отличными данными выше, чем у операторов без отдельных. Потому что при запросе многих полей, если используются отдельные, механизм базы данных будет сравнивать данные и отфильтровывать повторяющиеся данные.Однако этот процесс сравнения и фильтрации будет занимать системные ресурсы и процессорное время.
16. Удалите избыточные и повторяющиеся индексы
反例：
KEY `idx_userId` (`userId`)
KEY `idx_userId_age` (`userId`,`age`)
正例:
//删除userId索引，因为组合索引（A，B）相当于创建了（A）和（A，B）索引
KEY `idx_userId_age` (`userId`,`age`)
причина:
Дублирующиеся индексы необходимо поддерживать, и оптимизатору необходимо учитывать один за другим при оптимизации запросов, что повлияет на производительность.
17. Если объем данных велик, оптимизируйте операторы изменения/удаления.
Избегайте одновременного изменения или удаления слишком большого количества данных, так как это вызовет высокую загрузку ЦП, что повлияет на доступ других людей к базе данных.
反例：
//一次删除10万或者100万+？
delete from user where id <100000;
//或者采用单一循环操作，效率低，时间漫长
for（User user：list）{
delete from user；
}
正例：
//分批进行删除,如每次500
delete user where id<500
delete product where id>=500 and id<1000；
причина:
Если за один раз удаляется слишком много данных, может возникнуть ошибка превышения времени ожидания блокировки, поэтому рекомендуется работать в пакетном режиме.
18. Рассмотрите возможность использования значения по умолчанию вместо null в предложении where.
反例：
select * from user where age is not null;(未使用索引)
正例：
//设置0为默认值
select * from user where age>0;(使用索引age)
причина:
Это не значит, что если вы используете null или не null, индекс не будет взят, это связано с версией MySQL и стоимостью запроса. Если оптимизатор mysql обнаружит, что стоимость использования индекса выше, чем стоимость неиспользования индекса, он обязательно откажется от индекса, эти условия! =, > is null, is not null часто считаются недействительными для индекса. На самом деле, это связано с тем, что в целом стоимость запроса высока, и оптимизатор автоматически сдается.
еслиЗамените нулевое значение значением по умолчанию, что часто дает возможность перейти к индексу, при этом выражение будет относительно ясным.
19. Не используйте более 5 подключений к таблицам
- Чем больше связанных таблиц, тем больше время компиляции и накладные расходы.
- Разбиение таблицы соединений на более мелкие исполнения более удобочитаемо.
- Если вам нужно объединить много таблиц, чтобы получить данные, это означает плохой дизайн.
20. Разумное использование существующих и в
(in состоит в том, чтобы пройти вложенный обход B в A, а существование состоит в том, чтобы пройти вложенный обход B в B)
Предполагая, что таблица A представляет таблицу сотрудников предприятия, а таблица B представляет таблицу отделов, для запроса всех сотрудников всех отделов легко получить следующий SQL:
выберите * из A, где deptId в (выберите deptId из B); Написание этого эквивалентно:
Таблица отдела первого запроса B
select deptId from B
Затем используйте deptId отдела, чтобы запросить сотрудников A
select * from A where A.deptId = B.deptId
Его можно абстрагировать в такой цикл:
Списокрезультат;
for(int i=0;i выберите * из A, где существует (выберите 1 из B, где A.deptId = B.deptId);
Поскольку понимание существующего запроса заключается в том, что сначала выполняется основной запрос, а после получения данных он помещается в подзапрос для условной проверки, а результат данных основного запроса определяется в соответствии с результатом проверки (правда или ложь). Тогда запись этого эквивалентна: выберите * из A, первый цикл из таблицы A выберите * из B, где A.deptId = B.deptId, а затем выполните цикл из таблицы B. Точно так же его можно абстрагировать в такой цикл: Списокрезультат;
for(int i=0;i Следовательно, нам нужно выбрать самый внешний цикл, который является небольшим, то есть, если количество данных в B меньше, чем у A, он подходит для использования в, и если количество данных в B больше, чем у A A, это подходит для выбора существует. Если в результатах поиска нет повторяющихся записей, рекомендуется заменить union на union all. причина: Если используется объединение, независимо от того, дублируются ли результаты поиска, они будут пытаться объединиться, а затем отсортироваться перед выводом окончательного результата. Если известно, что в результатах поиска нет повторяющихся записей, используйте union all вместо union, что повысит эффективность. причина: По сравнению с числовыми полями типы символов могут снижать производительность запросов и соединений и повышатьнакладные расходы на хранение. Поскольку оптимизатор SQL оптимизирует запрос на основе объема данных в таблице, если в столбце индекса имеется большое количество повторяющихся данных, оптимизатор запросов Mysql оценивает, что стоимость неиспользования индекса ниже, и это Вероятно, отказаться от индекса. Предположим, бизнес-требование состоит в том, что пользователь запрашивает просмотр данных прямых трансляций, которые они смотрели за последний год. причина: причина: Почему первый оператор не попадает в индекс без добавления одинарных кавычек? Это связано с тем, что когда одинарные кавычки не добавляются, это сравнение между строками и числами, и их типы не совпадают.MySQL будет выполнять неявное преобразование типов, преобразовать их в числа с плавающей запятой и выполнить сравнение. При ежедневной разработке и написании SQL постарайтесь выработать привычку. Используйте объяснение для анализа написанного вами SQL, особенно индекса. Оригинальная ссылка Наггетс:nuggets.capable/post/684490…Оригинальная ссылка:Tickets.WeChat.QQ.com/Yes?__Author=MЭто…21. Попробуйте заменить union на union all
反例：
select * from user where userid=1
union
select * from user where age = 10
正例：
select * from user where userid=1
union all
select * from user where age = 10
22. Индекса не должно быть слишком много, как правило, меньше 5.
23. Старайтесь использовать числовые поля Если поля содержат только числовую информацию, постарайтесь не оформлять их как символы.
反例：
king_id` varchar（20） NOT NULL COMMENT '守护者Id'
正例：
`king_id` int(11) NOT NULL COMMENT '守护者Id'`
24. Индексы не подходят для полей с большим объемом повторяющихся данных, таких как поля гендерной базы данных.
25. Старайтесь не возвращать слишком много данных клиенту.
反例：
//一次性查询所有数据回来
select * from LivingInfo where watchId =useId and watchTime >= Date_sub(now(),Interval 1 Y)
正例：
//分页查询
select * from LivingInfo where watchId =useId and watchTime>= Date_sub(now(),Interval 1 Y) limit offset，pageSize
//如果是前端分页，可以先查询前两百条记录，因为一般用户应该也不会往下翻太多页，
select * from LivingInfo where watchId =useId and watchTime>= Date_sub(now(),Interval 1 Y) limit 200 ;
26. При соединении нескольких таблиц в операторе SQL используйте псевдоним таблицы и добавляйте префикс псевдонима к каждому столбцу, чтобы семантика была более ясной.
反例：
select * from A inner
join B on A.deptId = B.deptId;
正例：
select memeber.name,deptment.deptName from A member inner
join B deptment on member.deptId = deptment.deptId;
27. Максимально используйте varchar/nvarchar вместо char/nchar.
反例：
`deptName` char(100) DEFAULT NULL COMMENT '部门名称'
正例：
`deptName` varchar(100) DEFAULT NULL COMMENT '部门名称'
28. Чтобы повысить эффективность группировки по оператору, вы можете отфильтровать ненужные записи перед выполнением оператора.
反例：
select job，avg（salary） from employee group by job having job ='president'
or job = 'managent'
正例：
select job，avg（salary） from employee where job ='president'
or job = 'managent' group by job；
29. Если тип поля — строка, то where необходимо заключить в кавычки, иначе индекс будет неверным.
反例：
select * from user where userid =123;
正例：
select * from user where userid ='123';
30. Используйте объяснение для анализа вашего плана SQL
explain select * from user where userid =10086 or age =18;