(This question is probably not InnoDB-specific.)
Ordinarily, I would insist on seeing the SHOW CREATE TABLE before guessing what the issues are.
More readable:
SELECT c.id AS id, lastname AS customername,
status, address, zip, city, countryid,
countries.name AS country, email,
ten, ssn, c.info AS info, message,
c.divisionid, c.paytime AS paytime,
COALESCE(b.value, 0) AS balance, COALESCE(t.value, 0) AS tariffvalue,
s.account, s.warncount,
s.online,
(CASE WHEN s.account = s.acsum THEN 1 WHEN s.acsum > 0 THEN 2 ELSE 0 END) AS nodeac,
(CASE WHEN s.warncount = s.warnsum THEN 1 WHEN s.warnsum > 0 THEN 2 ELSE 0 END) AS nodewarn
FROM customersview c
LEFT JOIN countries ON (c.countryid = countries.id)
LEFT JOIN
( SELECT SUM(value) AS value, customerid
FROM cash
GROUP BY customerid
) b ON (b.customerid = c.id)
LEFT JOIN
( SELECT customerid, SUM((1)) AS value
FROM assignments
LEFT JOIN tariffs t ON (t.id = tariffid)
LEFT JOIN liabilities l ON (l.id = liabilityid AND period != 0)
WHERE (datefrom <= 1337932817 OR datefrom = 0)
AND (dateto > 1337932817 OR dateto = 0)
GROUP BY customerid
) t ON (t.customerid = c.id)
LEFT JOIN
( SELECT ownerid, SUM(access) AS acsum, COUNT(access) AS account,
SUM(warning) AS warnsum, COUNT(warning) AS warncount,
(CASE WHEN MAX(lastonline) > 1337932817 - 0 THEN 1 ELSE 0 END) AS online
FROM nodes
WHERE ownerid > 0
GROUP BY ownerid
) s ON (s.ownerid = c.id);
The first problem I see is more than one "JOIN ( SELECT ...)". Each such subquery will generate a temporary table. They will have no indexes, nor any way to add an index, so they will have to be fully scanned, repeatedly. This is the cause of the high CPU.
Normally, I would suggest turning each subquery into a JOIN. But that is probably counter-productive since the subqueries include a GROUP BY.
(Rick's RoTs mentions some of these issues:
http://mysql.rjweb.org/doc.php/ricksrots )
There is a workaround. Do something like this for each of the three subqueries (using the first as an example):
CREATE TEMPORARY TABLE b (
PRIMARY KEY (customerid)
) ENGINE=MyISAM
SELECT SUM(value) AS value, customerid
FROM cash
GROUP BY customerid;
Then change:
LEFT JOIN
( SELECT SUM(value) AS value, customerid
FROM cash
GROUP BY customerid
) b ON (b.customerid = c.id)
-->
LEFT JOIN b ON (b.customerid = c.id)
Also, "LEFT" is used when the 'right' table might be missing rows, and you want NULLs for anything missing from it. So, remove "LEFT" if you don't need that feature.
> 6 6 DEPENDENT SUBQUERY e index userid 8 276 Using where; Using index
Am I blind, or is there no table "e" in your query?
> (datefrom <= 1337932817 OR datefrom = 0)
The OR makes it difficult to optimize.